Database slows breaking site every night at midnight

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
MicahCarrick
Forum Newbie
Posts: 23
Joined: Sat Apr 09, 2005 5:40 pm

Database slows breaking site every night at midnight

Post by MicahCarrick »

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:

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 PROCESSLIST
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?
User avatar
bmcewan
Forum Commoner
Posts: 55
Joined: Wed Jun 02, 2004 7:19 am
Location: West Yorkshire, UK.

Post by bmcewan »

It could be that your server is running its backup routine. Depending on amount & size of accounts and backup configuration this can spike the server load.

Also any automatic updates can have an effect on performance.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Assuming this is a Linux box, log in via ssh and run "top" and watch what happens at midnight.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Those are NOT queries waiting to execute. If they were then the SQL-query column would show the query that was waiting to be executed.

Sleeping just means the process is waiting for another query to be sent to it and will eventually time out. It is sitting there doing nothing.

If you have queries waiting to be processed you will usually see something like this...

Code: Select all

ID              User            Host                    Database        Command         Time    Status  SQL-query 
636320          web_user        my.server.com:1424      my_database     Query          115     Locked     SELECT * mytable...
That would mean the query is just sitting there waiting to be executed.

What you are seeing is usually caused by PHP not closing out the mysql connection after it is finished with the data. When a PHP program finishes PHP is supposed to automatically send a close to any MySql database queries that were left open but some versions of PHP do not do this all of the time. Basically you need to upgrade to a newer version of PHP. This problem was fixed somewhere around version 4.3 of PHP. Programmers should issue a close command to any mysql query when they are finished with it anyway to free up memory from the PHP process but most rely on PHP doing that for them when the program finishes execution.

Mysql has a default time out and will close those internal processes on it's own. It's really quite normal and does not cause any load on the database server at all.

If you are seeing the site struggle to load this is definitely NOT the problem. You have something else causing the loading problem. You need to check all of the running processes to see if something is running at that time of day such as a backup to or from the server to or from another server eating up your bandwidth.

I can tell you that is isn't a database problem.
Post Reply