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 😛 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
Best Answer
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