mysqld causes high CPU load

Question

My mysqld goes to use 99.9% of CPU for variable time (between 2 - 20 minutes), and then goes back to normal 0.1% - 5%. Checked processlist: all is normal, 1 to 20 inserts or updates that last 2 to 5 sec, and about 20 process that are in Sleep Mode (maybe because the scripts don't close the mysql connection, but are they are closed in about 5 - 10 secs, I didn't make the scripts :P but the server was running fine the last 2 years, since is was made):

| 15375 | root                 | localhost             | stoc  | Query   |    0 | NULL             | show processlist                                                                                     | 
| 79480 | pppoe                | localhost | pppoe | Sleep   |    4 | NULL             | NULL                                                                                                 | 
| 79481 | pppoe                | localhost | pppoe | Sleep   |    4 | NULL             | NULL                                                                                                 | 
| 79482 | pppoe                | localhost | pppoe | Sleep   |    4 | NULL             | NULL                                                                                                 | 
| 79483 | pppoe                | localhost | pppoe | Query   |    0 | init             | UPDATE acc SET InputOctets="0", OutputOctets="0", InputPackets="unknown", OutputPackets="User | 
| 79484 | pppoe                | localhost | pppoe | Sleep   |    5 | NULL             | NULL                                                                                                 | 
| 79485 | pppoe                | localhost | pppoe | Sleep   |    5 | NULL             | NULL                                                                                                 | 
| 79486 | pppoe                | localhost | pppoe | Sleep   |    5 | NULL             | NULL                                                    

Checked raid, seemns OK:

[root@db2]# cat /proc/mdstat 
Personalities : [raid5] [raid4] [raid1] 
md0 : active raid1 sdd1[3] sdc1[2] sdb1[0] sda1[1]
      136448 blocks [4/4] [UUUU]

md1 : active raid5 sdd2[3] sdc2[2] sdb2[0] sda2[1]
      12023808 blocks level 5, 256k chunk, algorithm 2 [4/4] [UUUU]

md3 : active raid5 sda4[1] sdd4[3] sdc4[2] sdb4[0]
      203647488 blocks level 5, 256k chunk, algorithm 2 [4/4] [UUUU]

md2 : active raid5 sda3[1] sdd3[3] sdc3[2] sdb3[0]
      24024576 blocks level 5, 256k chunk, algorithm 2 [4/4] [UUUU]

unused devices: <none>
[root@db2]# 

top sees my mysqld cpu load, but nothing else seems to be wrong:

[root@db2]# top

top - 17:56:05 up 7 days,  3:55,  3 users,  load average: 32.93, 24.72, 22.70
Tasks:  75 total,   4 running,  71 sleeping,   0 stopped,   0 zombie
Cpu(s): 63.4% us, 36.6% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.0% si,  0.0% st
Mem:   1988824k total,  1304776k used,   684048k free,    99588k buffers
Swap: 12023800k total,        0k used, 12023800k free,   951028k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                         
 5754 mysql     19   0  236m  57m 5108 R 99.9  2.9  21:58.76 mysqld                                                                                          
    1 root      16   0  7216  700  580 S  0.0  0.0   0:00.39 init                                                                                            
    2 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0     

Repaired all mysql databases, reindexed raid ... I'm running out of ideeas ...

Anyone has an ideea what can go wrong with this server ?

Thank you

Answer 1

You can consider using a software like Monyog(1) to monitor and analyze your DB. It will profile and inform you of situations you might want to look at and make suggestions on how to fix your issue. It can also take into account things like IO and detect deadlocks.

You can also use Cacti (2) to do this, but you have to homebrew (3) more of the modules to get the information you need.

mysqlreport (4) is great for quick diagnosis, but should analyze databases that have been running for at least 8 hours under load.

Many of these tools will also be aided by introducing things like slow query log, and sometimes general log.

Apparently I can't post links to the all resources, so here is a single hyperlink to pastebin for resources to all the links

Answer 2

Get the list of slow queries, based on this list verify if you have proper indexes for tables.
Indexes can slow down dramatically if missing. In case you need mysql tuning you will need to do a mysql profiling, search google for this. HTH

Answer 3

Have you checked the slow query log? You might even want to enable the general query log and see if you can identify what is going on when the slow-down occurs. It could be that some of the 'scripts' that you say where written over two years ago make some assumptions about the size of the database and the size has grown over the last couple years.

Answer 4

I think you need to optimize your MySQL queries. Do you use "indexes"? please provide the output of the "show status" command to see if your server do a lot of table scan or not. Also log the slow query to help you to identify the problem.