Re: Innodb bogs down on complicated query(ies)
The update statements are very simple using primary keys. The reason for the instance of the long key is due to composite keys that increase as the table hierarchy is traversed. Nothing I can do about that without rewriting.
There are no ON UPDATE CASCADEs, but I do use ON DELETE CASCADE
For every update there are between 1 and 10 rows affected spread over the table hierarchy. Again, the system is lightning quick with no deadlocks or lock waits. It is only when I run this query (substituting different dates and plan ids) that I get into trouble. I think I may be thrashing. Is it possible that a particular buffer is set too low? Could this have something to do with having dual processors (we're running RH 9 SMP Intel x86)?
I'm also still wondering about the output of SHOW STATUS that says:
---TRANSACTION 0 823807, ACTIVE 10 sec, process no 13044, OS thread id 77844
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 3169, query id 1017115 localhost root Table lock
update STATEMENT ON INNODB TABLE (CONTENT CENSORED)
Why does it say 'Table lock' when the update (and all updates that follow in this transaction) are on InnoDB tables? Again, Autocommit is 0 and innodb_table_locks = 0. By the way, it seems that when I have innodb_table_locks = 1 I run into lock waiting problems without having to run the query, but no deadlocks.
Thanks again.
Lee