Re: Innodb bogs down on complicated query(ies)
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
|
+------+------+-----------+-------------+---------+------+----------------------+---------------------------------------------------------------------
---------------------------------+