Khắc phục lỗi MariaDB / MySQL sử dụng CPU cao trên Plesk

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:vi /etc/my.cnf
  • for Debian/Ubuntu:vi /etc/mysql/my.cnf
  1. 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/

  2. Save the changes and close the file.
  3. Restart the MySQL service:
  • for CentOS/RHEL:systemctl restart mariadb
  • for Debian/Ubuntu:systemctl restart mysqld
  1. Monitor CPU usage.If CPU consumption increases again, consider reviewing the SQL code of a database that has slow queries.
  2. 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)

  3. Check the MySQL error logfile /var/log/mysqld.log for errors.
  4. Check RAM and free disk space:# free -h
    # df -h
  5. 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.
  6. Analyze and optimize all tables in all databases:
  • for Linux:# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -uadmin –optimize –all-databases
  1. Install and run the mysqltuner utility. This utility will analyze the current MySQL server instance and provide recommendations for adjusting appropriate parameters.