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.

I have recently noticed that my MySQL server consumes abnormally large amount of CPU resources:

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
2427 mysql     20   0 2198m 161m 5568 S 1600.0  4.4  5121391h /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql

I am attaching two files - one with my MySQL configuration and another with the mysqlreport output. MySQL error logs are clean. mytop shows few records but they are changed all of the time and it's difficult to understand if such overload is due to those records. Sometimes, the processes list is even empty.

I am also attaching my server details. I am going to think that top shows wrong info ). Appreciate any help.

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

Stop looking at the system and start looking at what's happening to MySQL. Even the briefest glance at the report makes it clear that you have some incredibly inefficient queries. Work on proper indexing and optimisation and I believe your problems will go away.

share|improve this answer
    
May I know what section of the report have you looked at to make such decision? )) I have about 1k sites, so it will take some time to log slow queries and investigate them. –  Andrew Jan 29 '12 at 23:11
1  
@lush, the selects are taking very very long. Sure it might take some time to work out which queries need work but there's no magic wand to fix these things. If possible, find all the queries used by your site (only need generic, not specific) and run an explain on them to get a first look at where the problems are. –  John Gardeniers Jan 29 '12 at 23:23
    
I have too many sites to find all queries. I will start by logging slow queries. Thanks. –  Andrew Jan 30 '12 at 3:28
1  
@lush, start with slow query logging and as you find and improve queries shorten then slow query time till you get acceptable results. On my site I started at 10 seconds and and am now at 2 seconds, with very few queries being logged as slow. I'm hoping to go down to 1 second. –  John Gardeniers Jan 30 '12 at 4:28
add comment

I once saw my HDD in my mysql server went bad and then cpu shootup.

share|improve this answer
    
Yes, it might be an issue. But now disk reads are around 40Mb/s. So, disk should not be a problem. –  Andrew Jan 30 '12 at 2:59
add comment

A few things I noticed which you may wish to look into in more detail:

__ Bytes _______________________________________________________________
Sent          743.25M  960.3k/s
Received        9.79M   12.6k/s

This takes you to around 8Mbps (Mega bits/second) which, plus overhead, would bring you pretty close to maxing out a typical 10Mbps connection.

__ InnoDB Buffer Pool __________________________________________________
Reads           9.81M   12.7k/s
__ InnoDB Data, Pages, Rows ____________________________________________
Rows
  Read         73.43M   94.9k/s

I'm not sure what exactly you're doing but these seem very, very high and likely beyond what a low end dedicated server can handle comfortably. If you do indeed need this amount of traffic you're going to have to look at more and/or bigger hardware. I would include some basic profiling and benchmarking to figure out exactly how much you need and how much your current hardware can support.

share|improve this answer
    
Bytes Sent is showing as 7.6G 320 k/s now and keeps growing up. Does it mean that some databases are queried very often and large amount of data is selected and send to the clients? –  Andrew Jan 30 '12 at 3:14
1  
Yes, it means something is requesting and reading a huge amount of data. –  uesp Jan 30 '12 at 3:30
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.