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

Symptoms

• Plesk and its websites are not accessible or working slow due to high MySQL/MariaDB processes CPU load observed on top -c command's output.

• Slow long running database queries are found with the following command as well as under Tools & Settings > Database Process List:

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

Cause

Slow queries on non performant customer databases generate high MySQL/MariaDB 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. Consider to temporarily suspend the related subscription to decrease the CPU load.

6. Contact website developers to review slow SQL queries. This article can be suggested.