Plesk and websites are not accessible due to high CPU load by MySQL processes

Symptoms

• Plesk and websites are not accessible or working slow due to high CPU load by MySQL processes
• Slow MySQL queries are shown by the following command:
  

  # plesk db "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY TIME ASC \G"
  ID: 444047
  USER: johndoe
  HOST: localhost
  DB: ExampleDB
  COMMAND: Query
  TIME: 300

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

  CONFIG_TEXT: 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: 444047
   User: johndoe
   Host: localhost
   db: ExampleDB
   Command: Query
   Time: 300
   State: Sending data

3. Log in to Plesk and go to Tools & Settings > Database servers > click on the number of databases:
   
4. Identify the subscription associated with the database from step 3
5. Kill the slow queries by their ID from step 3:
   

   # plesk db "kill ID"

6. Suspend the subscription where the identified database is registered to decrease CPU load
7. Contact website developers to review slow SQL queries and fix the issue

Additional information

Troubleshooting slow performance of the MySQL on Plesk server