How to troubleshoot slow performance of MySQL on a Plesk server?

Allocate RAM to the MySQL server:

1. Connect to the Plesk server via SSH.

2. Open the MySQL configuration file my.cnf or my.ini in any text editor. Locations of the file are:

• for CentOS/RHEL:

  /etc/my.cnf

• for Debian/Ubuntu:

  /etc/mysql/my.cnf

3. 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

   Note: 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/

4. Save the changes and close the file.

5. Restart the MySQL service:

• for CentOS/RHEL:

  # systemctl restart mariadb

• for Debian/Ubuntu:

  # systemctl restart mysqld

6. Monitor CPU usage.

   If CPU consumption increases again, consider reviewing the SQL code of a database that has slow queries.

7. 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)

8. Check the MySQL error logfile /var/log/mysqld.log for errors.

9. Check RAM and free disk space:

   # free -h
   # df -h

10. 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.

11. Analyze and optimize all tables in all databases:

• for Linux:

  # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -uadmin --optimize --all-databases

12. 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:

1. Connect to the Plesk server via RDP.

2. 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

3. 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

4. Save the changes and close the file.

5. Open Task Manager > Services and restart MySQL56 (or MySQL51) service:
   

6. 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-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.

7. 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.

8. Install and run the mysqltuner utility. 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