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