MySQL Forums
Forum List  »  InnoDB

Re: Innodb bogs down on complicated query(ies)
Posted by: Lee Krawczyk
Date: August 10, 2006 12:09PM

It's been awhile, but I thought I would post a sample of a processlist that occurs when we get a bottleneck.

The first query is a select statement that contains stored functions. There is no writing.

Why do I get a locked state on the subsequent writes to innodb tables that are apparantly waiting for my select statement to finish? If I understand correctly, these updates should not be waiting for anything.

The only time I can catch a process list that looks like this is when one of these types of queries containing stored functions is running. After it has completed or been killed everything goes. Also, once the query has successfully completed it generally will run again with little to no delay unless a long amount of time has passed since it was last run. Query cache is off.

+------+------+-----------+-------------+---------+------+----------------------+---------------------------------------------------------------------
---------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+------+------+-----------+-------------+---------+------+----------------------+---------------------------------------------------------------------
---------------------------------+
| 3940 | root | localhost | utilization | Query | 57 | Copying to tmp table | select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appe |
| 3943 | root | localhost | utilization | Query | 47 | Locked | update cases set participant_id = '115930',insured_id = '115930',ent
ry_date = str_to_date('10-Aug-20 |
| 3949 | root | localhost | utilization | Query | 14 | Locked | insert into cases (participant_id,insured_id,entry_date,street,init_
by,state,addr2,city,review_date, |
| 3953 | root | localhost | utilization | Query | 8 | Locked | update cases c set
c.disposition = 'REVIEW',
c |
| 3955 | root | localhost | | Query | 0 | | show processlist
|
+------+------+-----------+-------------+---------+------+----------------------+---------------------------------------------------------------------
---------------------------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Innodb bogs down on complicated query(ies)
1899
August 10, 2006 12:09PM


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.