How to troubleshoot slow performance of MySQL/MariaDB on a Plesk server?

Question

How to troubleshoot slow performance of MySQL/MariaDB on a Plesk server?

Answer

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

Plesk for Linux

1. Log into Plesk

2. Go to Tools & Settings > Performance Booster > Serverwide

3. Once MariaDB appears (this may take around a minute), press show values to be optimized

4. Press Apply

   Applying the changes will restart the database server. Make sure no long queries are ongoing first as MariaDB will need to finish all queries before it can restart.

5. Monitor CPU usage.

   If CPU consumption increases again, consider reviewing the SQL code of a database that has slow queries.

6. During a high level of CPU usage, find queries that are currently running and taking a lot of time:

   # plesk db "SHOW FULL PROCESSLIST"
   +-----+-------+-----------+------+---------+------+-------+-----------------------+
   | Id | User | Host | db | Command | Time | State | Info |
   +-----+-------+-----------+------+---------+------+-------+-----------------------+
   | 12 | admin | localhost | psa | Sleep | 6763 | | NULL |
   | 100 | admin | localhost | psa | Query | 0 | NULL | SHOW FULL PROCESSLIST |
   +-----+-------+-----------+------+---------+------+-------+-----------------------+
   2 rows in set (0.00 sec)

7. Check the MySQL/MariaDB error logfile /var/log/mysqld.log for errors.

8. Check RAM and free disk space:

   # free -h
   # df -h

9. Find databases that cause slow MySQL/MariaDB performance by enabling the MySQL slow query log.

   Note: It is possible to see the list of processes currently being handled by the local MySQL/MariaDB server. To do so, log into Plesk and go to Tools & Settings > MySQL Process List (Beta) (under Assistance and Troubleshooting).
   Click a MySQL/MariaDB process to see its details: what queries are being processed and how much resources they consume.

10. Analyze and optimize all tables in all databases:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -uadmin --optimize --all-databases

11. Install and run the mysqltuner utility. This utility will analyze the current MySQL/MariaDB server instance and provide recommendations for adjusting appropriate parameters.

    • Linux version is available at: https://github.com/major/MySQLTuner-perl

Plesk for Windows

1. Connect to…