high cpu usage mysql

Question

I've started to see very high cpu usage on mysql on my CentOS server.

It used to run at between 20%-30% but now will spike into the 400% at times.

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
9169 mysql     20   0 2232m  66m 6816 S 140.6  0.9  17:37.03 mysqld                      

when i do a show processlist i dont see much running

mysql> show processlist;
+-------+------+-----------+----------+---------+------+-------+------------------+
| Id    | User | Host      | db       | Command | Time | State | Info             |
+-------+------+-----------+----------+---------+------+-------+------------------+
|    53 | sqluser | localhost | mydns    | Sleep   |    0 |       | NULL             |
|   449 | sqluser | localhost | DB1  | Sleep   |    0 |       | NULL             |
| 16151 | sqluser | localhost | DB2 | Sleep   |  105 |       | NULL             |
| 16473 | sqluser | localhost | DB2 | Sleep   |   61 |       | NULL             |    
| 16641 | sqluser | localhost | DB2 | Sleep   |   39 |       | NULL             |
| 16729 | sqluser | localhost | DB2 | Sleep   |   29 |       | NULL             |
| 16733 | sqluser | localhost | DB2 | Sleep   |   29 |       | NULL             |
| 16815 | sqluser | localhost | DB2 | Sleep   |   15 |       | NULL             |
| 16867 | sqluser | localhost | DB2 | Sleep   |    9 |       | NULL             |
| 16886 | sqluser | localhost | NULL     | Query   |    0 | NULL  | show processlist |
| 16910 | sqluser | localhost | DB2 | Sleep   |    5 |       | NULL             |
| 16919 | sqluser | localhost | DB1  | Sleep   |    4 |       | NULL             |
| 16935 | sqluser | localhost | DB1  | Sleep   |    2 |       | NULL             |
| 16937 | sqluser | localhost | DB1  | Sleep   |    2 |       | NULL             |
| 16939 | sqluser | localhost | DB1  | Sleep   |    1 |       | NULL             |
| 16941 | sqluser | localhost | DB1  | Sleep   |    1 |       | NULL             |
| 16951 | sqluser | localhost | DB1  | Sleep   |    1 |       | NULL             |
| 16952 | sqluser | localhost | DB2 | Sleep   |    1 |       | NULL             |
| 16955 | sqluser | localhost | DB1  | Sleep   |    1 |       | NULL             |
| 16960 | sqluser | localhost | DB1  | Sleep   |    0 |       | NULL             |
| 16965 | sqluser | localhost | DB1  | Sleep   |    0 |       | NULL             |
+-------+------+-----------+----------+---------+------+-------+------------------+
21 rows in set (0.00 sec)

Is there anything else i can do to see what is causing such high usage?

Answer 1

First of all, you need to get some more data, in order to do that I usually use some tools like:

• Mytop
• Innotop

Mytop has some great ways of viewing active connections and queries. Innotop has great ways of seeing what is using your table spaces, buffer pools, etc.

You don't have to use these tools to gather the data. You could use the Percona Toolkit tools, they have some great ones for really drilling down into the knitty gritty of MySQL usage.

Don't forget that there are logs to check. Specifically you should check on the slow log. To find if and where that is being stored, you can run following:

First make sure it's turned on:

MySQL> SHOW VARIABLES LIKE 'slow_query_log';

Then check where it's located on your file system:

MySQL> SHOW VARIABLES LIKE 'slow_query_log_file';

Using the Percona tool pt-query-digest on the slow log has proved to be helpful for me. It gives a cohesive list of what queries are using the most time (which usually means resource intensive queries). You can view the usage of the tool here: PT-Query-Digest You may find some really slow queries, if so I would evaluate how they are being used and see if adding an index, or optimizing them is needed.

Once you gather these data pieces you should have a good picture of what's happening to your MySQL instance. Of course there's many more tools and techniques, but I find these to be sufficient to form a good baseline of performance.