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.

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

share|improve this question
1  
Anything in the logs? Anything in mtop? –  DaDaDom May 5 '10 at 7:30
    
I can't install mtop, the server is still running on FC4. :) I can't change that. Still waithing for a new server, but the bosses are not ready to spend money and time on it ... –  Radu Maris May 5 '10 at 8:51
add comment

4 Answers

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

share|improve this answer
add comment

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

share|improve this answer
    
Yesterday I started slow_query_log, restarted mysql to start loging, but no query was registrated so far, and the server Yestedrday at about 4:30 PM was realy slow until 4:50 PM, but no query was slow in this time, like I said, the longest query time is about 5 sec (from loging to sever, wainting for table to get unlocked, lock the table, run the query, unlock the table and disconnect from server, all in 5 sec MAX). Something is going wrong, but I can't figure out what. –  Radu Maris May 6 '10 at 7:10
    
faulty hardware?<br> check SMART and your RAID for consitency –  Paul May 11 '10 at 19:01
add comment

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.

share|improve this answer
    
I think that a slow query has to appear in "show processlist", i used watch to run "show processlist" every 1 sec, and no query last longer de 5 sec, and there are usuali 5 query's (max 20 ) running at once on the same table (the table is changed monthly and at the end of the month reaches about 3500k (3,5 milions) rows, but the month just started and now has just 462575 rows), there are other query's on other tables but there are rare and very fast (thouse I made myself in the last 5 months). The problem started about 2 - 3 weeks ago. –  Radu Maris May 5 '10 at 8:16
    
started log-slow-queries but no query was loged so far. –  Radu Maris May 5 '10 at 12:56
add comment

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.

share|improve this answer
add comment

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.