Home / server / Questions / 375888
Accepted
user111196
Asked: 2012-04-03 04:59:18 +0800 CST

Load average is have been high over some period

  • 772

We have a dedicated MySQL server and below is the a snapshot of the top. The load average has been staying at nearly 100 for an hour plus ready.

top - 20:54:28 up  7:31,  2 users,  load average: 83.08, 96.88, 106.23
Tasks: 278 total,   2 running, 274 sleeping,   2 stopped,   0 zombie
Cpu0  : 18.8%us, 10.2%sy,  0.0%ni, 70.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  : 51.2%us,  4.3%sy,  0.0%ni, 44.2%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st
Cpu2  :  9.0%us, 10.3%sy,  0.0%ni, 80.6%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  : 18.8%us,  7.4%sy,  0.0%ni, 73.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu4  :  7.8%us,  8.8%sy,  0.0%ni, 83.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu5  : 10.3%us,  8.4%sy,  0.0%ni, 81.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu6  :  6.2%us,  7.5%sy,  0.0%ni, 86.2%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu7  :  6.2%us,  6.2%sy,  0.0%ni, 87.3%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st
Cpu8  :  8.8%us, 10.4%sy,  0.0%ni, 80.5%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st
Cpu9  : 63.7%us,  4.6%sy,  0.0%ni, 12.2%id,  0.0%wa,  4.3%hi, 15.2%si,  0.0%st
Cpu10 :  9.2%us, 10.2%sy,  0.0%ni, 80.6%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu11 : 17.3%us,  5.9%sy,  0.0%ni, 76.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu12 :  8.0%us,  8.7%sy,  0.0%ni, 83.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu13 : 10.9%us,  7.4%sy,  0.0%ni, 81.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu14 :  6.2%us,  6.9%sy,  0.0%ni, 86.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu15 :  4.8%us,  6.1%sy,  0.0%ni, 89.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st

Mem:  33009800k total, 23174396k used,  9835404k free,   120604k buffers
Swap: 35061752k total,        0k used, 35061752k free, 16459540k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 3341 mysql     20   0 14.3g 4.6g 4240 S 417.8 14.5   1673:51 mysqld
24406 root      20   0 15008 1292  876 R  0.3  0.0   0:00.19 top
    1 root      20   0  4080  852  608 S  0.0  0.0   0:01.92 init
    2 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 kthreadd
    3 root      RT  -5     0    0    0 S  0.0  0.0   0:00.32 migration/0
    4 root      15  -5     0    0    0 S  0.0  0.0   0:00.29 ksoftirqd/0
    5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
    6 root      RT  -5     0    0    0 S  0.0  0.0   0:03.21 migration/1
    7 root      15  -5     0    0    0 S  0.0  0.0   0:00.07 ksoftirqd/1
    8 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1
    9 root      RT  -5     0    0    0 S  0.0  0.0   0:00.17 migration/2
   10 root      15  -5     0    0    0 S  0.0  0.0   0:00.03 ksoftirqd/2
   11 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2
   12 root      RT  -5     0    0    0 S  0.0  0.0   0:00.32 migration/3
   13 root      15  -5     0    0    0 S  0.0  0.0   0:00.02 ksoftirqd/3
   14 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/3
   15 root      RT  -5     0    0    0 S  0.0  0.0   0:00.10 migration/4
   16 root      15  -5     0    0    0 S  0.0  0.0   0:00.04 ksoftirqd/4
   17 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/4
   18 root      RT  -5     0    0    0 S  0.0  0.0   0:00.35 migration/5

We have also tried to run this command. What else command can help us diagnose the exact problem of this high load? 

netstat -nat |grep 3306 | awk '{print $6}' | sort | uniq -c | sort -n
      1 LISTEN
      1 SYN_RECV
    410 ESTABLISHED
    964 TIME_WAIT

Output of vmstat 1:

---------------memory--------------- --swap-- --io-- --system-- -----cpu------
 r b swpd  free     buff    cache    si so    bi  bo in  cs     us sy id wa st
 2 0 0     12978936 30944   15172360 0  0     259 3  184 265    6  6  77 12 0
centos mysql

3 Answers

  1. Best Answer

    Here are some things to get you started. Try these in order:

    1. Run SHOW FULL PROCESSLIST; in MySQL. Look for the queries that have been running the longest.
    2. Turn the MySQL slow log on, set the long_query_time to an appropriate value and use either mysqldumpslow -s at <logfile> or mysqldumpslow -s t <logile>. (Replace <logfile> with the path to your logfile.)
    3. Turn log_queries_not_using_indexes on and repeat the above mysqldumpslow -s t command. Since your problem is CPU, this is less likely to be the root cause, but it could be if you are seeing a lot of simple queries or a few very complex queries over a small table. Beware: Depending on your indexes, this logging option can cause a lot of writes to the disk which can cause your load average to climb even higher.
    4. Get some monitoring going using Cacti or Zabbix and the Better Cacti Templates for MySQL(now called the Percona Monitoring Project) or the Zabbix Appaloosa Templates for MySQL. Since you won't have any idea of what the graphs looked like before the problem started, this won't be quite as useful as it could have been if you had installed it right from the start.

    It sounds like this is related to your older question. Have you tried what was suggested there? How did it go?

    • 3

  2. Looks like a massive slow queries somewhere in your bases. Try to turn "ON" slow_query_log for mysqld and look what happens. May be some index is broken. Try to check/analyze tables.

    • 1

  3. install and use mtop, it's a top like program that show queries instead of processes and it will give you a realtime insight about what queries are runinnig and those who are blocking the server. then you may use mysqltuner script available at mysqltuner.com to run a diagnostic on your server and see if there are some params to tune

    • 0