Applicable to:
- Plesk
Question
How to troubleshoot slow performance of MySQL 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.
Warning: Increasing values may affect server performance. Make sure that there is enough RAM.
Allocate RAM to the MySQL server:
-
Connect to the Plesk server via SSH.
-
Open the MySQL configuration file
my.cnformy.iniin any text editor. Locations of the file are:
-
for CentOS/RHEL:
/etc/my.cnf -
for Debian/Ubuntu:
/etc/mysql/my.cnf
-
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=64MNote: Refer to the official documentation in order to determine the correct value for the server-specific needs https://mariadb.com/kb/en/innodb-buffer-pool/
-
Save the changes and close the file.
-
Restart the MySQL service:
-
for CentOS/RHEL:
# systemctl restart mariadb
-
for Debian/Ubuntu:
# systemctl restart mysqld
-
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 error logfile
/var/log/mysqld.logfor errors. -
Check RAM and free disk space:
# free -h
# df -h -
Find databases that cause slow MySQL performance by enabling the MySQL slow query log.
Note: With Repair Kit, it is possible to see the list of processes currently being handled by the local MySQL server. To do so, log into Plesk and go to Tools & Settings > MySQL Process List (Beta) (under Assistance and Troubleshooting).
Click a MySQL process to see its details: what queries are being processed and how much resources they consume. -
Analyze and optimize all tables in all databases:
-
for Linux:
# 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 server instance and provide recommendations for adjusting appropriate parameters.
- Linux version is available at: https://github.com/major/MySQLTuner-perl
Allocate RAM to the MySQL server:
-
Connect to the Plesk server via RDP.
-
Open the MySQL 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 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 performance by enabling the MySQL slow query log.
With Repair Kit, it is possible to see the list of processes currently being handled by the local MySQL server. To do so, log into Plesk and go to Tools & Settings > MySQL Process List (Beta) (under Assistance and Troubleshooting).
Click a MySQL 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 server instance and provide recommendations for adjusting appropriate parameters.- Windows version is available at: https://mysqltuner.codeplex.com
Comments
23 comments
I received the below when I tried to restart the service
Redirecting to /bin/systemctl restart mysqld.service
Failed to restart mysqld.service: Unit not found.
@Hany Mesbah Gadalla, Hi! your case is being investigated in a support ticket already. Let's continue the discussion in the ticket.
Hi Bulat,
The problem has been solved. You have to remove the space before and after the equal sign (example below). Please update the page.
innodb_buffer_pool_size=1024M
query_cache_size=64M
Thank You
@Hany Mesbah Gadalla, thanks for the note. Updated.
Link for Professional Service team cannot access :)
@Atthawut Prathumrat
Hi!
The link is updated. Thank you for notice)
CentOS doesn't have my.cnf directives in /etc/my.cnf. What directory is the correct location for my.cnf on CentOS running mariadb which is its default database server.
Hello @Raheel,
What is the version of CentOS you are using?
I have double-checked it in the test environment, the default location for my.cnf file on CentOS 7 and CentOS 6 with MariaDB is /etc/my.cnf.
Open it in any text editor and add the following directives under the [mysqld] section:
innodb_buffer_pool_size=1024M
query_cache_size=64M
I added the directives mentioned in this post and it worked. Thank you for that but it didn't resolve the issue I was having which I thought was related to mysql. Turns out the reason behind it was the open_basedir directive for php.
Hello @Raheel,
Thank you for letting us know.
If you need assistance, feel free to submit a ticket to us.
As I can see, in the past, we have already had a couple from you.
I have used following steps solved my problem thank you so much
The problem has been solved. You have to remove the space before and after the equal sign (example below). Please update the page.
innodb_buffer_pool_size=1024M
query_cache_size=64M
Thank You
@Yogesh Gangurde
You are right, there should be no additional spaces in these directives. Glad to know that you have figured it out!
May I safely update the my.ini filles of the Plesk db? It's little slow
@Pier Paolo Ronzino
Yes, just create a backup of my.ini file=)
Please note that configuration for Plesk SQL service may be overwritten during the updare.
BTW, if you feel that Plesk SQL server is working slow and causes slowness in the panel - feel free to create a ticket to our support.
A very useful page. Thank you Plesk
Just one question? Re: Step 10 above, not the slow log link, but the acces to processes via: Tools & Settings > MySQL Process List (Beta) (under Assistance and Troubleshooting).
We have the Plesk Repair Kit Extention installed and it works fine, with no problems, but if we follow this ^^ advised route within the Plesk panel and select that option, the MySQL process list is not shown. Instead, this box error is shown; "Request failed with status code 500"
If we then check in /var/log/mysql/error.log we can see the confirmation (logging in as Plesk user admin or root makes no difference, it's the same, identical error wording):
[Warning] Access denied for user 'admin'@'localhost' (using password: NO)
How can we rectify this?
Hello @Learning Curve,
It looks like you've encountered the known bug.
Here's the article, which should help: https://support.plesk.com/hc/en-us/articles/360009652040-MySQL-Process-list-fails-with-Request-failed-with-status-code-500
Thanks Ivan Postnikov
We waited until after today's Obsidian upgrade to 18.0.21, just in case the Plesk bug #PPPM-11086 was one of the fixes that was included. It wasn't, so we've run the fix on the page that you provided the link for above and that's completely solved the problem. It works perfectly now.
migrated to new server upgraded to obsidian
now mysqltuner does not work!
# perl mysqltuner.pl
>> MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
Due to security reasons the option '-show-password' is no longer supported. Use '--get-login-link' to generate a one-time login link.
[!!] Attempted to use login credentials from Plesk and Plesk 10+, but they failed.
Hi,
I checked that on default installation it works fine and from what I see our team also helped in in ticket to sort out the issue.
I want to be able to use the MySQL tuning software for Windows linked above, but it is asking for admin login to MySQL and I don't know that password. How do I determine the MySQL admin password so that I can run tuning software?
Hi Eric Merkel please refer here https://support.plesk.com/hc/en-us/articles/360004150253-How-to-retrieve-administrator-s-Plesk-database-password-on-Plesk-17-8-for-Windows-
I do the recommended changes, restart mysql with service mysql restart and it works perfect!
But when i restart the VPS, the value of innodb_buffer_pool_size is again 2M and not thee 1024M i have set.
Debian 8.11, Plesk Onyx 17.8.11 Actualización #88
Hi Martin Schenk, please make sure the value is not taken from another file: 'grep -r innodb_buffer_pool_size /etc/mysql/'.
I also recommend upgrading to Debian 9 because Debian 8 is not supported anymore. The appropriate procedure is described here https://support.plesk.com/hc/en-us/articles/213410369.
Please sign in to leave a comment.