Plesk and websites slow or not accessible due to high CPU load MySQL processes

Symptoms

• Plesk and websites are not accessible or working slow due to high CPU load by MySQL processes observed with top -c command.

• Slow long running MySQL queries are shown with the following command and under Tools & Settings > Database Process List:

  # plesk db "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY TIME ASC G;" | grep TIME:
  TIME: 455
  TIME: 300

• On CloudLinux, dbtop utility shows a user as restricted:

  restricted 196/237/203 10761/11833/32292 0/2362/2936 R/c:cpu/30

Cause

Slow MySQL queries generate high CPU usage on the server.

Resolution

1. Log in to Plesk server via SSH

2. Identify the slowest queries:

   # plesk db "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY TIME ASC G;"
   Id: 4047
   User: johndoe
   Host: localhost
   db: ExampleDB
   Command: Query
   Time: 300
   State: Sending data

3. Kill the slow queries by their ID from step 2:

   # plesk db "kill 4047"

4. Identify the subscription associated with the database from the previous step:

   # plesk db "select d.name from domains as d, data_bases as db where db.dom_id=d.id and db.name like '%ExampleDB%';"
   +----------------------------+
   | name |
   +----------------------------+
   | example.com |
   +----------------------------+

5. Temporarily suspend the subscription the associated subscription to decrease CPU load.

6. Contact website developers to review slow SQL queries and fix the issue.

Additional information

Troubleshooting slow performance of the MySQL on Plesk server