Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a server dedicated to MySQL. In the middle of the night its CPU usage just shot up for no apparent reason. Here're the graphs:

enter image description here

As you can see CPU usage just shot up at Fri 3:30am. Most of the increase is in system, not user. The MySQL server wasn't doing any more queries at all. The CPU load just exploded for no apparent reasons.

The first jump of CPU load that happened at Thu 3:30pm was due to pushing new code to the web server. The code was reverted to previous version at Thu 8pm and that caused the CPU load to drop back to normal. But something happened at Fri 3:30am. There wasn't any code change. I don't know what it was.

Do you have any idea what might have happened?

EDIT

Here's the portion of /var/log/cron around Thu 15:30:

Mar 3 15:25:01 db crond[8174]: (munin) CMD (/usr/bin/munin-cron)
Mar 3 15:25:01 db crond[8175]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 3 15:26:01 db crond[8816]: (root) CMD (/usr/local/cpanel/whostmgr/bin/dnsqueue > /dev/null 2>&1)
Mar 3 15:27:01 db crond[8947]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 3 15:30:01 db crond[9411]: (root) CMD (/etc/apf/apf --refresh >> /dev/null 2>&1 &)
Mar 3 15:30:01 db crond[9412]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Mar 3 15:30:01 db crond[9413]: (root) CMD (/usr/local/lp/apps/autoupdate/lp-autoupdate.pl > /dev/null 2>&1)
Mar 3 15:30:01 db crond[9414]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 3 15:30:01 db crond[9415]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 3 15:30:01 db crond[9417]: (munin) CMD (/usr/bin/munin-cron)
Mar 3 15:33:01 db crond[10502]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 3 15:35:01 db crond[10895]: (munin) CMD (/usr/bin/munin-cron)
Mar 3 15:35:01 db crond[10896]: (root) CMD (/usr/bin/test -x /usr/local/cpanel/bin/tail-check && /usr/local/cpanel/bin/tail-check)
Mar 3 15:35:01 db crond[10897]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 3 15:36:01 db crond[11494]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 3 15:39:01 db crond[12028]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 3 15:40:01 db crond[12451]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Mar 3 15:40:01 db crond[12452]: (root) CMD (/etc/apf/apf --refresh >> /dev/null 2>&1 &)
Mar 3 15:40:01 db crond[12453]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 3 15:40:01 db crond[12456]: (munin) CMD (/usr/bin/munin-cron)
Mar 3 15:41:01 db crond[13384]: (root) CMD (/usr/local/cpanel/whostmgr/bin/dnsqueue > /dev/null 2>&1)
Mar 3 15:42:01 db crond[13561]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 3 15:45:02 db crond[14266]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 3 15:45:02 db crond[14270]: (root) CMD (/usr/local/lp/apps/autoupdate/lp-autoupdate.pl > /dev/null 2>&1)
Mar 3 15:45:02 db crond[14272]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 3 15:45:02 db crond[14273]: (munin) CMD (/usr/bin/munin-cron)
Mar 3 15:48:01 db crond[15314]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 3 15:50:01 db crond[15835]: (munin) CMD (/usr/bin/munin-cron)
Mar 3 15:50:01 db crond[15836]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Mar 3 15:50:01 db crond[15834]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 3 15:50:02 db crond[15843]: (root) CMD (/etc/apf/apf --refresh >> /dev/null 2>&1 &)
Mar 3 15:51:01 db crond[16734]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 3 15:54:01 db crond[17251]: (root) CMD (/usr/local/sbin/bfd -q)

And here's the proportion of /var/log/cron around Fri 3:30:

Mar 4 03:25:01 db crond[10399]: (munin) CMD (/usr/bin/munin-cron)
Mar 4 03:25:01 db crond[10400]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 4 03:26:01 db crond[11056]: (root) CMD (/usr/local/cpanel/whostmgr/bin/dnsqueue > /dev/null 2>&1)
Mar 4 03:27:01 db crond[11258]: (mailman) CMD (/usr/local/cpanel/3rdparty/bin/python -S /usr/local/cpanel/3rdparty/mailman/cron/nightly_gzip)
Mar 4 03:27:01 db crond[11259]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 4 03:30:01 db crond[11506]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 4 03:30:01 db crond[11507]: (root) CMD (/etc/apf/apf --refresh >> /dev/null 2>&1 &)
Mar 4 03:30:01 db crond[11508]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Mar 4 03:30:01 db crond[11509]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 4 03:30:01 db crond[11510]: (munin) CMD (/usr/bin/munin-cron)
Mar 4 03:30:01 db crond[11515]: (root) CMD (/usr/local/lp/apps/autoupdate/lp-autoupdate.pl > /dev/null 2>&1)
Mar 4 03:33:01 db crond[12595]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 4 03:35:01 db crond[12735]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 4 03:35:01 db crond[12736]: (munin) CMD (/usr/bin/munin-cron)
Mar 4 03:35:01 db crond[12737]: (root) CMD (/usr/bin/test -x /usr/local/cpanel/bin/tail-check && /usr/local/cpanel/bin/tail-check)
Mar 4 03:36:01 db crond[13355]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 4 03:39:01 db crond[13822]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 4 03:40:01 db crond[13975]: (root) CMD (/etc/apf/apf --refresh >> /dev/null 2>&1 &)
Mar 4 03:40:01 db crond[13976]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 4 03:40:01 db crond[13977]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Mar 4 03:40:01 db crond[13978]: (munin) CMD (/usr/bin/munin-cron)
Mar 4 03:41:01 db crond[14796]: (root) CMD (/usr/local/cpanel/whostmgr/bin/dnsqueue > /dev/null 2>&1)
Mar 4 03:42:02 db crond[15068]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 4 03:45:01 db crond[15441]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 4 03:45:01 db crond[15443]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 4 03:45:01 db crond[15442]: (munin) CMD (/usr/bin/munin-cron)
Mar 4 03:45:01 db crond[15444]: (root) CMD (/usr/local/lp/apps/autoupdate/lp-autoupdate.pl > /dev/null 2>&1)
Mar 4 03:48:01 db crond[16499]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 4 03:50:01 db crond[16705]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Mar 4 03:50:01 db crond[16706]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)
Mar 4 03:50:01 db crond[16707]: (root) CMD (/etc/apf/apf --refresh >> /dev/null 2>&1 &)
Mar 4 03:50:01 db crond[16708]: (munin) CMD (/usr/bin/munin-cron)
Mar 4 03:51:01 db crond[17574]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 4 03:54:01 db crond[18086]: (root) CMD (/usr/local/sbin/bfd -q)
Mar 4 03:55:01 db crond[18222]: (root) CMD (/usr/local/cpanel/bin/dcpumon >/dev/null 2>&1)

Any ideas?

And if it was a cron job, shouldn't the CPU load went back down after the cron job is done. The CPU load has stayed persistently high, and most of it is used by MySQL. Here's the output of top:

top - 13:51:49 up 22 days, 21:35,  1 user,  load average: 17.10, 17.67, 17.67
Tasks: 432 total,   1 running, 431 sleeping,   0 stopped,   0 zombie
Cpu(s): 14.5%us, 49.2%sy,  0.0%ni, 36.2%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8177596k total,  6886860k used,  1290736k free,   545776k buffers
Swap:  4096564k total,        0k used,  4096564k free,  5264320k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                         
 1200 mysql     15   0 2258m 341m 4352 S 2036.1  4.3   1658:23 mysqld                                                                                        
 4269 root      15   0 13008 1364  808 R  0.7  0.0   0:00.39 top                                                                                             
 4636 root      10  -5     0    0    0 S  0.3  0.0  13:22.79 kjournald                                                                                       
    1 root      15   0 10352  704  588 S  0.0  0.0   0:07.19 init             
share|improve this question
    
I thinks its an good idea when you make a SHOW PROCESSLIST; when this happen. Then you can see which processes are running. But at this time its really possible that crawler goes through and idexing the site. Then you have a lot of traffic on your Website. –  Stony Mar 4 '11 at 19:12

4 Answers 4

Does this MySQL server manage a database for a public facing website?

Middle of the night CPU spikes can sometimes correlate to search engine bots indexing a large number of pages. Check to see if your access logs/analytics on your web servers match up.

share|improve this answer

Without more information, no, I don't have any idea what might have happened. CPU usage clearly spiked in the specified ranges, but I don't know what your system might have been doing then: syslog would be the first place I'd start looking. A spike that re-occurs exactly twelve hours apart makes me suspicious of a regular cron task, maybe a backup or re-index. If you can provide a bit more context, I'd be happy to help you dig into this.

share|improve this answer
    
Thanks. Please see my EDIT for more info –  Continuation Mar 4 '11 at 18:55

A cron task was my first thought as well.

Check your logs and see if there is anything in there (syslog or cron). Also if you are using the default cron structure, check your cron.daily folder for a new script that might have been installed by a new package recently.

EDIT: also check your backup scripts or logs and see if they are having a hard time completing or accessing files on the system.

share|improve this answer
    
Thanks. Please see my EDIT for more info –  Continuation Mar 4 '11 at 18:56

If mysqld is causing your CPU woes still, you may want to check the output of SHOW PROCESSLIST;

I'd also check the mysql logs for some indication of what was going on when the trouble started.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.