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

Symptoms

• The mysqld or mariadbd service consumes a great percentage of the overall CPU resources on the server as it can be observed on top -c command's output.
• Plesk and its websites may not be accessible or be working slow.

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

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

  Note: A normal query is completed in a few milliseconds (for example, 5 to 50 ms). The TIME value is shown in seconds, so a healthy query is displayed as TIME: 0, while values of several seconds or more, like the ones above, indicate slow queries.

Cause

The MySQL or MariaDB server is not tuned for the server’s resources, or slow queries from non performant customer databases keep it busy, which generates high CPU usage.

Resolution

Note: Database performance tuning is out of the scope of Plesk Free Technical Support. This serves as a general-purpose guide.

1. Apply the recommended MySQL/MariaDB settings in Tools & Settings > Performance Booster > Serverwide by following steps 1 to 5 in the Plesk for Linux section of this article
2. Only if the high CPU usage persists or comes back, continue from step 3 to troubleshoot specific non performant databases.
3. Log in to Plesk server via SSH

4. Identify the slowest queries. A normal query finishes in milliseconds and is displayed with Time: 0, so focus on queries that run for seconds or longer:

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

5. Kill the slow queries by their ID from the previous step:

   # plesk db "kill 4047"

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

   # 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 |
   +----------------------------+

7. Consider to temporarily suspend the related subscription to decrease the CPU load.
8. Contact website developers to review slow SQL queries, and share MySQL Performance: Identifying Long Queries and MySQL Performance Tuning as reference guides.