Applicable to:
- Plesk
Symptoms
-
Server is slow due to high CPU usage caused by the "mysql" process.
-
According to this KB article there are multiple slow queries.
-
Websites that are using MySQL databases operate slow.
-
Database dump takes too much time to complete.
Cause
The MySQL server is not tuned for optimal performance.
Troubleshooting
General Troubleshooting
Note: Database performance tuning is out of the scope of Plesk Free Technical Support. This serves as a general-purpose guide.
Note: Increasing values may affect server performance. Make sure that there is enough RAM.
Allocate RAM to the MySQL server:
-
Open the MySQL configuration file
my.cnformy.iniin any text editor. Locations of the file are:-
for CentOS/RHEL:
CONFIG_TEXT: /etc/my.cnf
-
for Debian/Ubuntu:
CONFIG_TEXT: /etc/mysql/my.cnf
-
For Windows:
CONFIG_TEXT: %plesk_dir%Databases\MySQL\my.ini
-
-
Add the following directives under the
[mysqld]section or increase the values if these directives are already defined:CONFIG_TEXT: innodb_buffer_pool_size=1024M
query_cache_size=64M -
Save the changes and close the file.
-
Restart the MySQL service:
-
for CentOS/RHEL:
# service mysqld restart
-
for Debian/Ubuntu:
# service mysql restart
-
for Windows:
Open Task Manager > Services and restart MySQL56 (or MySQL51) service:
-
-
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.
Advanced troubleshooting
-
Analyze and optimize all tables in all databases:
-
for Linux:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -uadmin --optimize --all-databases
-
for Windows:
C:\> %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.
-
-
Install and run the
mysqltunerutility. This utility will analyze the current MySQL server instance and provide recommendations for adjusting appropriate parameters.- Linux version is available at: http://mysqltuner.com
- Windows version is available at: https://mysqltuner.codeplex.com
Comments
21 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-
Please sign in to leave a comment.