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

Allocate RAM to the MySQL server:

1. Connect to the Plesk server via SSH.

2. Open the MySQL/MariaDB 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/MariaDB 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/MariaDB 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/MariaDB server. To do so, log into Plesk and go to Tools & Settings > MySQL Process List (Beta) (under Assistance and Troubleshooting).
    Click a MySQL/MariaDB 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/MariaDB 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/MariaDB server:

1. Connect to the Plesk server via RDP.

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

   %plesk_dir%DatabasesMySQLmy.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…