The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

runaway mysql - server crashes

Discussion in 'General Discussion' started by elleryjh, Dec 15, 2006.

  1. elleryjh

    elleryjh Well-Known Member

    Joined:
    Apr 12, 2003
    Messages:
    479
    Likes Received:
    0
    Trophy Points:
    16
    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:

    Code:
     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
    
     
  2. jayh38

    jayh38 Well-Known Member

    Joined:
    Mar 3, 2006
    Messages:
    1,215
    Likes Received:
    0
    Trophy Points:
    36
    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.
     
  3. jsnape

    jsnape Well-Known Member

    Joined:
    Mar 11, 2002
    Messages:
    174
    Likes Received:
    0
    Trophy Points:
    16
    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
     
  4. mikelegg

    mikelegg Well-Known Member

    Joined:
    Mar 29, 2005
    Messages:
    330
    Likes Received:
    0
    Trophy Points:
    16
    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.
     
  5. budway

    budway Well-Known Member

    Joined:
    Apr 16, 2003
    Messages:
    186
    Likes Received:
    0
    Trophy Points:
    16
    Just use spri from r-fx.net.

    You can set-up all programs priority
     
Loading...

Share This Page