MySQL Forums
Forum List  »  InnoDB

Re: Innodb bogs down on complicated query(ies)
Posted by: Lee Krawczyk
Date: May 05, 2006 11:12AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Innodb bogs down on complicated query(ies)
1844
May 05, 2006 11:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.