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

Log into Plesk

Go to Tools & Settings > Performance Booster > Serverwide

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

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.

Monitor CPU usage.

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

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)

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

Check RAM and free disk space:

# free -h
# df -h

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.

Analyze and optimize all tables in all databases:

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

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

Connect to the Plesk server via RDP

Open the MySQL/MariaDB configuration file my.cnf or my.ini in any text editor. Locations of the file are:

%plesk\_dir%Databases\\MySQL\\my.ini

Add the following directives under the [mysqld] section or increase the values if these directives are already defined:

innodb_buffer_pool_size=1024M
query_cache_size=64M

Save the changes and close the file.

Open Task Manager > Services and restart MySQL56 (or MySQL51) service:

Analyze and optimize all tables in all databases:

%plesk_dir%\MySQL\bin\mysqlcheck.exe -uadmin -P3306 --optimize --all-databases

Note: For accessing MySQL/MariaDB in Windows, it is required to add the line skip-grant-tables right under [mysqld] in %plesk_dir%Databases\MySQL\my.ini and restart MySQL service, as it is shown above. Do not forget to remove it and restart again after all is done.

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

With Repair Kit, 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 > Database Process List (under Assistance and Troubleshooting).
Click a MySQL/MariaDB process to see its details: what queries are being processed and how much resources they consume.

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

• Windows version is available at: https://mysqltuner.codeplex.com

Note: This is a third party utility and should be used at one's own risk.