1

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.

Improve this question

4 Answers 4

Reset to default
3

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.

Improve this answer
4
  • 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, 2012 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, 2012 at 23:23
  • I have too many sites to find all queries. I will start by logging slow queries. Thanks.
    – Andrew
    Jan 30, 2012 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, 2012 at 4:28
1

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

Improve this answer
1
  • Yes, it might be an issue. But now disk reads are around 40Mb/s. So, disk should not be a problem.
    – Andrew
    Jan 30, 2012 at 2:59
1

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.

Improve this answer
2
  • 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, 2012 at 3:14
  • 1
    Yes, it means something is requesting and reading a huge amount of data.
    – uesp
    Jan 30, 2012 at 3:30
0

Run this command to check if any query is running in background and consuming CPU resources:

SHOW FULL PROCESSLIST

Then kill the rogue process using Identifier

Kill Id

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .