Mysql – How to troubleshoot high CPU usage on a server

MySQL

On my cloud server, the MySQL CPU usage jumps to over 100% on Sundays at 1PM GMT.

How can I troubleshoot the cause of this?

I have been told to run the cmd 'top' on the server but this only tells me the following:

PID    USER    PR   NI  VIRT    RES    SHR   S  %CPU  %MEM    TIME+     COMMAND           
2326   mysql   20   0   2182m   128m   5264  S  99.8  1.3     1610:50   msqld

Which I understand as MySQL is using the CPU. After running:

SHOW FULL PROCESS LIST;

I found this (*** is private info):

| Id     | User        | Host      | db          | Command | Time  | State                | Info                                                                                                 |
| 288484 | ***         | localhost | ***         | Query   | 93110 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_***_posts.* FROM wp_***_posts  INNER JOIN wp_***_postmeta ON (wp_h |

With time being in seconds, it makes sense that this process started at 1PM Sunday from when I ran the command.

Best Answer

As it states in the manual:

If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

If you use "one of the databases" you'll likely have insufficient privileges, you should probably use the "root" account or grant that privilege to yourself. That way you'll see what is happening in all databases in a single window.

Second that sounds like a scheduled job, a first level approach would be to check the scheduler (cron). On linux systems typically /var/log/cron ought to have record of what was started around 1 PM on Sunday by cron.

Related Topic