Database slows breaking site every night at midnight
Posted: Mon Jun 26, 2006 12:15 am
Hey, I'm actually a PHP developer and I'm trying to help a client troubleshoot this problem. Recently, every night at 12 midnight, the site struggles to load. The database resides on it's own server. We've disabled cron jobs and I don't notice any strange access in the apache access logs. Slow log is fine-- nothing. Even the regular MySQL access log just has the same things it has all other times of the day. I can't seem to figure out what is going on.
I logged on to the site tonight, and watched in phpMyAdmin periodically refreshing the "processes" option which I believe is just a SHOW FULL PROCESSLIST and the problem occurs severely at midnight, and then slowly gets better and better. By a little after 12:35 or so the problem is non-existant.
The process list pretty early on looked like this:
As you can see, there's quite a pile up of queries waiting to execute... up to 115 seconds! Usually if I can catch them in the process list, they show 0 for time and are gone by the next refresh. They're "sleep"? They show up in the MySQL access log as connecting, initializing db, executing, and quitting all within one second. So why are they waiting so long to execute?
Anybody have any suggestions for how I can track this down?
I logged on to the site tonight, and watched in phpMyAdmin periodically refreshing the "processes" option which I believe is just a SHOW FULL PROCESSLIST and the problem occurs severely at midnight, and then slowly gets better and better. By a little after 12:35 or so the problem is non-existant.
The process list pretty early on looked like this:
Code: Select all
ID User Host Database Command Time Status SQL-query
636320 web_user my.server.com:1424 my_database Sleep 115 --- ---
636361 web_user my.server.com:1500 my_database Sleep 143 --- ---
636384 web_user my.server.com:1544 my_database Sleep 128 --- ---
636414 web_user my.server.com:1602 my_database Sleep 88 --- ---
636441 web_user my.server.com:1654 my_database Sleep 30 --- ---
636459 web_user my.server.com:1689 my_database Sleep 48 --- ---
636467 web_user my.server.com:1702 my_database Sleep 38 --- ---
636473 web_user my.server.com:1715 my_database Sleep 18 --- ---
636479 web_user my.server.com:1724 my_database Sleep 27 --- ---
636485 web_user my.server.com:1735 my_database Sleep 20 --- ---
636498 web_user my.server.com:1760 my_database Sleep 14 --- ---
636503 web_user my.server.com:1771 my_database Sleep 11 --- ---
636510 web_user my.server.com:1784 None Query 0 --- SHOW PROCESSLISTAnybody have any suggestions for how I can track this down?