Applicable to:
- Plesk
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.
-
Go to Tools & Settings > Performance Booster > Serverwide
-
Once MariaDB appears (this may take around a minute), press show values to be optimized
-
Press Apply
-
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.logfor 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-tablesright under[mysqld]in%plesk_dir%Databases\MySQL\my.iniand 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 > 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. -
Install and run the
mysqltunerutility. 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
Comments
1 comment
The link to mysqltuner no longer works
Please sign in to leave a comment.