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.

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
share|improve this question

3 Answers 3

up vote 2 down vote accepted

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?

share|improve this answer
    
what I am curious is why is the cpu percentage is quite low but the load is still heavy is it that my cpus are not working to the max any reason for it? I did not have time to install the cacti quite a waste cause i was concentrating on the index. Could this be a hardware problem? –  user111196 Apr 2 '12 at 15:30
1  
Low CPU with high load average is usually caused by high disk utlisation (this is how much the processes want to read from and write to the disks, not how much is stored on them). This can be found with vmstat 1. vmstat shows raw numbers and doesn't show any history from before the problem but the wa column shows how much the CPUs are waiting on disk access. If that's high, disk access is a problem for you. –  Ladadadada Apr 2 '12 at 15:41
    
from the top which I put can we say that the cpu usage is low or high? Here is one of the vmstat vmstat procs -----------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 –  user111196 Apr 2 '12 at 16:04
    
You have 32GB of RAM, 22GB of it is in use and MySQL is only using 4.6GB. What else is running on this box? And have you tried using mysqltuner.pl to tune your MySQL? It may be a lot happier with more of the RAM. Which config parameters you change depend on what engine you are using. –  Ladadadada Apr 2 '12 at 16:21
    
do you think the wa is high not quite soo right? I have not tried mysqltuner.pl u need perl for that right? Nothing else is running on this box except for mysql. So how to know what is eating up the ram then? –  user111196 Apr 2 '12 at 16:35

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.

share|improve this answer
1  
@kondbas yes there is some slow queries. but those queries I are scanning through few thousand lines too. Secondly how to check/analyse table? –  user111196 Apr 2 '12 at 15:04
    
Slow queries are those that haven't appropriate indices or use indices that broken. In that cases query begins to "filesort" that means slow plainsearch in tables instead of fast search by some kind of tree structure. That also may happens when your query is bad designed - read Mysql manual chapter 7 for hints about query optimization. –  Kondybas Apr 2 '12 at 15:43
    
query CHECK TABLE some_table; shows you status of table. If something goes wrong you'll get a message that REPAIR needed. –  Kondybas Apr 2 '12 at 15:47
    
there is one which I suspected so now I am repairing why it keeping getting problem is it due to its big size is it? but what is eating up my memory cause mysql if you see is just taking up 4.6 gb out of the 32Gb memory? –  user111196 Apr 3 '12 at 0:44

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

share|improve this answer
    
@montassar ok I will try to install I tried yum install mtop but it does tell me no package to install either. –  user111196 Apr 2 '12 at 16:54
    
mtop is not available via package managers, you need to install it from sources, it's quite simple and should run straight forward without problem if you follow instruction in the README file –  m0ntassar Apr 2 '12 at 17:12
    
@montassar Far more useful tools are Mysql Workbench and HeidiSQL. I prefer HeidiSQL inspite it is win-app, it works quite well in wine. –  Kondybas Apr 2 '12 at 17:25
    
@kondybas indeed / you just need to mention not to install wine on a production server running MySQL :) –  m0ntassar Apr 2 '12 at 17:28
    
I would use innotop instead of mtop. You don't even need to install it; it's a single file you can just execute. –  Baron Schwartz Apr 2 '12 at 18:04

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.