Troubleshooting slow performance of the MySQL on Plesk server – Plesk Help Center

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.

Connect to the Plesk server via SSH or RDP.
Open the MySQL configuration file my.cnf or my.ini in 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.log for 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.

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-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.
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: http://mysqltuner.com
- Windows version is available at: https://mysqltuner.codeplex.com

Comments

10 comments

Hany Mesbah Gadalla January 03, 2018 13:06

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.

0

Bulat Tsydenov January 05, 2018 13:45

@Hany Mesbah Gadalla, Hi! your case is being investigated in a support ticket already. Let's continue the discussion in the ticket.

0

Hany Mesbah Gadalla January 06, 2018 15:47

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

0

Alexandr Tumanov January 09, 2018 09:46

@Hany Mesbah Gadalla, thanks for the note. Updated.

0

Atthawut Prathumrat January 29, 2018 15:28

Link for Professional Service team cannot access :)

0

Ivan Postnikov January 30, 2018 07:27

@Atthawut Prathumrat

Hi!
The link is updated. Thank you for notice)

0

Raheel Ansari April 21, 2019 11:28

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.

0

Ivan Postnikov April 22, 2019 09:11

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

0

Raheel Ansari April 23, 2019 14:21

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.

0

Ivan Postnikov April 24, 2019 07:32

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.

0