How to troubleshoot High CPU usage by MySQL?

Created:

2016-11-16 13:06:26 UTC

Modified:

2017-04-24 11:37:08 UTC

12

Was this article helpful?


Have more questions?

Submit a request

How to troubleshoot High CPU usage by MySQL?

Applicable to:

  • Plesk 12.5 for Linux
  • Plesk Onyx for Linux
  • Plesk 11.0 for Linux
  • Plesk 11.5 for Linux
  • Plesk 12.0 for Linux
  • Plesk 12.5 for Windows

Question

  1. How to troubleshoot High CPU usage by MySQL?

  2. High CPU usage by MySQL process. How to verify what is going on the server?

    # top
    top - 13:35:48 up 20:19, 4 users, load average: 0.07, 0.12, 0.15
    Tasks: 471 total, 1 running, 470 sleeping, 0 stopped, 0 zombie
    Cpu(s): 0.7%us, 0.1%sy, 0.0%ni, 99.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
    Mem: 49390148k total, 7656948k used, 41733200k free, 323404k buffers
    Swap: 24764408k total, 0k used, 24764408k free, 5839768k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    1455 mysql 20 0 1720m 110m 7332 S 10.9 0.2 34:53.63 mysqld
    3235 apache 20 0 349m 26m 4724 S 3.3 0.1 1:14.40 httpd

Answer

  1. Check what queries are running and status of the server with with SHOW FULL PROCESSLIST in mysql server and process status. Example:

    mysql> SHOW FULL PROCESSLIST;
    +-----+-------+-----------+------+---------+------+-------+-----------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +-----+-------+-----------+------+---------+------+-------+-----------------------+
    | 12 | admin | localhost | psa | Sleep | 6763 | | NULL |
    | 17 | admin | localhost | psa | Sleep | 152 | | NULL |
    | 100 | admin | localhost | psa | Query | 0 | NULL | SHOW FULL PROCESSLIST |
    +-----+-------+-----------+------+---------+------+-------+-----------------------+
    3 rows in set (0.00 sec)
  2. Check the mysql error log /var/log/mysqld.log .

  3. Check general status of the server regarding RAM memory and available disk space: free -m , df -h .

  4. Check slow query log by enabling slow query log .

If the slow query is performed to customer's database, contact administrator of the database or administrator of the server.

Contact server administrator to improve server performance.

Have more questions? Submit a request
Please sign in to leave a comment.