runaway mysql - server crashes

[elleryjh]

Hi everyone. I'm having a problem where every day or so mysqld starts to hog all the CPU and the load skyrockets. I'm a bit puzzled on how to troubleshoot this (lots of clients with mysql on the server) and how to prevent mysql from killing the server. Any suggestions? Thanks.

Here's top from just before the server stops responding:

 10:02:39  up  6:34,  1 user,  load average: 72.59, 32.04, 13.29
309 processes: 227 sleeping, 62 running, 18 zombie, 2 stopped
CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
           total   48.0%    0.0%   51.8%   0.0%     0.0%    0.0%    0.0%
Mem:  1028908k av,  989020k used,   39888k free,       0k shrd,   81864k buff
       707512k active,             175100k inactive
Swap: 2048276k av,       0k used, 2048276k free                  287608k cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
 2863 mysql     23   6  187M  73M  3064 R N  99.6  7.3  17:14   0 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --p
31708 root      15   0  4352 1340   908 R     0.1  0.1   0:15   0 top -d 10
    1 root      18   0  1592  496   436 R     0.0  0.0   0:01   0 init [3]
    2 root      34  19     0    0     0 RWN   0.0  0.0   0:00   0 ksoftirqd/0
    3 root      RT   0     0    0     0 SW    0.0  0.0   0:00   0 watchdog/0
    4 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   0 events/0
    5 root      14  -5     0    0     0 SW<   0.0  0.0   0:00   0 khelper
    6 root      11  -5     0    0     0 SW<   0.0  0.0   0:00   0 kthread
    9 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   0 kblockd/0
   10 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   0 kseriod
   39 root      15   0     0    0     0 SW    0.0  0.0   0:00   0 kapmd
   61 root      15   0     0    0     0 SW    0.0  0.0   0:00   0 pdflush
   62 root      15   0     0    0     0 DW    0.0  0.0   0:00   0 pdflush
   63 root      10  -5     0    0     0 SW<   0.0  0.0   0:09   0 kswapd0
   64 root      20  -5     0    0     0 SW<   0.0  0.0   0:00   0 aio/0
  705 root      11  -5     0    0     0 SW<   0.0  0.0   0:00   0 kpsmoused
  720 root      10  -5     0    0     0 SW<   0.0  0.0   0:09   0 kjournald
 1056 root      15  -5     0    0     0 SW<   0.0  0.0   0:00   0 khubd
 1446 root      10  -5     0    0     0 SW<   0.0  0.0   0:00   0 kjournald
 1452 root      10  -5     0    0     0 SW<   0.0  0.0   0:01   0 kjournald
 1819 root      11  -5     0    0     0 SW<   0.0  0.0   0:00   0 khpsbpkt
 2139 root      15   0  1652  528   448 S     0.0  0.0   0:02   0 syslogd -m 0
 2143 root      15   0  1604  368   308 S     0.0  0.0   0:00   0 klogd -x
 2179 root      22   0  1588  368   308 S     0.0  0.0   0:00   0 /usr/sbin/courierlogger -pid=/var/spool/authdaemon/pid -facility=mail
 2180 root      18   0  1892  580   492 S     0.0  0.0   0:00   0 /usr/libexec/courier-authlib/authdaemond
 2181 root      15   0  1892  332   240 S     0.0  0.0   0:00   0 /usr/libexec/courier-authlib/authdaemond
 2182 root      15   0  1892  328   236 S     0.0  0.0   0:00   0 /usr/libexec/courier-authlib/authdaemond
 2183 root      15   0  1892  332   240 S     0.0  0.0   0:00   0 /usr/libexec/courier-authlib/authdaemond
 2184 root      15   0  1892  332   240 S     0.0  0.0   0:00   0 /usr/libexec/courier-authlib/authdaemond
 2185 root      15   0  1892  328   236 S     0.0  0.0   0:00   0 /usr/libexec/courier-authlib/authdaemond
 2257 root      18   0  7448 1648  1248 R     0.0  0.1   0:00   0 cupsd
 2474 root      20   4  3724 1020   768 S N   0.0  0.0   0:00   0 /usr/sbin/sshd

[jayh38]

You could start with some extra ram. 300 plus processes is pushing it for a busy
server with only 1GB ram. Funny, I do not see a swap file created but never the less,
you will gain an overall improvement.

You may even need a httpd.conf and my.cnf tuning as well. But the ram, I think
you definitely need now.

[jsnape]

Good luck troubleshooting in by tweaking settings. In my experience it doesn't matter what you do, and once mysql starts crashing the server it doesn't stop. How I eventually fixed my problem was change from a 3 year old opteron server to a dual woodcrest 5160.

top - 12:33:29 up 58 days, 3:53, 4 users, load average: 0.19, 0.24, 0.29

[mikelegg]

You could try typing "renice +19 u mysql" at the command line. This will give mysql the lowest priority over cpu usage. This means that other necessary processes will have enough resources to keep running and the server won't crash.

Having said that, "renice" only works on current processes, so the problem will keep re-occurring when new mysql processes are started.

Creating a shell script and running it regularly via a cron may help.

This is not the ultimate solution, but it may help.

[budway]

Just use spri from r-fx.net.

You can set-up all programs priority