Personally I feel that I have a pretty good understanding on MySQL locking,
however I am confused by the output I've received from "SHOW PROCESSLIST" at
seemingly random times.
At the end of this message is the output from "SHOW FULL PROCESSLIST." ALL
queries from output are shown. ALL WHERE clauses in all currently running
queries are using an index and normally run in under 0.01 sec.
My questions are:
1. How can Id 172387 (row 1) be in state 'end' for 5 seconds (it was already
in this state at Time=1)
2. 172388 id "updating" and it appears 172407,172409,172415, and 172426 are
all locked waiting for 172388 to finish. It has been in "updating" state for
5+ seconds since Time=1. How can it take so long when no other queries or
using table v
3. Similarly 172404 has been "preparing" and 172406 has been "statistics"
and 172406 has been "updating" since time=1. How can preparing or statistics
take 5 seconds? Note again that these queries are not locked or waiting on
other queries Remember normally these queries take < 0.01 sec. Everything
else is locked waiting for the first 5 unlocked queries
The server is loaded enough that these first 5 'frozen' queries quickly lock
everything else out and the database completely freezes. 3 seconds later
than what is shown below, SHOW PROCESSLIST has hundreds of locked processes.
*************************** 1. row ***************************
Id: 172387
Time: 5
State: end
Info: UPDATE si SET val=val-5 WHERE id=317675
*************************** 2. row ***************************
Id: 172388
Time: 5
State: updating
Info: UPDATE v SET val=val+5,cnt=cnt+1 WHERE id=233822
*************************** 3. row ***************************
Id: 172404
Time: 5
State: preparing
Info: SELECT * FROM s WHERE id=321653
*************************** 4. row ***************************
Id: 172406
Time: 5
State: statistics
Info: SELECT * FROM ad WHERE z='t' ORDER BY seq LIMIT 1
*************************** 5. row ***************************
Id: 172407
Time: 5
State: Locked
Info: UPDATE v SET val=val+5,cnt=cnt+1 WHERE id=236404
*************************** 6. row ***************************
Id: 172408
Time: 5
State: update
Info: INSERT INTO hist VALUES(261366,236313)
*************************** 7. row ***************************
Id: 172409
Time: 5
State: Locked
Info: UPDATE v SET val=val+5,cnt=cnt+1 WHERE id=233823
*************************** 8. row ***************************
Id: 172410
Time: 5
State: Locked
Info: INSERT INTO hist VALUES(265095,233822)
*************************** 9. row ***************************
Id: 172412
Time: 4
State: statistics
Info: SELECT * FROM ad WHERE z='l' ORDER BY seq LIMIT 1
*************************** 10. row **************************
Id: 172414
Time: 3
State: Locked
Info: INSERT INTO hist VALUES(287716,233818)
*************************** 11. row **************************
Id: 172415
Time: 3
State: Locked
Info: UPDATE v SET val=val+5,cnt=cnt+1 WHERE id=233822
*************************** 12. row **************************
Id: 172418
Time: 3
State: Locked
Info: INSERT INTO hist VALUES(306395,234632)
*************************** 13. row **************************
Id: 172420
Time: 2
State: Sorting result
Info: SELECT * FROM idx WHERE id=10523652 ORDER BY cnt
*************************** 14. row **************************
Id: 172421
Time: 2
State: Sorting result
Info: SELECT * FROM idx WHERE id=10523652 ORDER BY cnt
*************************** 15. row **************************
Id: 172423
Time: 1
State: Locked
Info: INSERT INTO hist VALUES(304442,5928601)
*************************** 16. row **************************
Id: 172424
Time: 1
State: statistics
Info: SELECT * FROM ad WHERE z='t' ORDER BY seq LIMIT 1
*************************** 17. row **************************
Id: 172425
Time: 1
State: Locked
Info: INSERT INTO hist VALUES(282924,5681301)
*************************** 18. row **************************
Id: 172426
Time: 1
State: Locked
Info: UPDATE v SET val=val+5,cnt=cnt+1 WHERE id=101086
*************************** 19. row **************************
Id: 172427
Time: 1
State: Sending data
Info: SELECT * FROM plan p
LEFT JOIN hist h ON p.id=h.id
LEFT JOIN hist h2 ON p.id=h2.id AND h2.stat='i'
LEFT JOIN s ON s.id=p.id
WHERE h.stat IS NULL AND p.id=288135
ORDER BY seq LIMIT 1
*************************** 20. row **************************
Id: 172428
Time: 1
State: Locked
Info: UPDATE plan SET stat='L' WHERE id=282924
*************************** 21. row **************************
Id: 172430
Time: 1
State: Locked
Info: SELECT * FROM plan p
LEFT JOIN hist h ON p.id=h.id
LEFT JOIN hist h2 ON p.id=h2.id AND h2.stat='i'
LEFT JOIN s ON s.id=p.id
WHERE h.stat IS NULL AND p.id=355506
ORDER BY seq LIMIT 1
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
