Applicable to:
- Plesk for Linux
- Plesk for Windows
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. Contact Professional Service team in order to perform this task.
Allocate RAM to the MySQL server:
Note: Increasing values may affect server performance. Make sure that there is enough RAM.
-
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, 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
12 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!
Please sign in to leave a comment.