Khắc phục lỗi MariaDB / MySQL sử dụng CPU cao trên Plesk
Allocate RAM to the MySQL server:
- Connect to the Plesk server via SSH.
- 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
- 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=64MNote: 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/
- Save the changes and close the file.
- Restart the MySQL service:
- for CentOS/RHEL:systemctl restart mariadb
- for Debian/Ubuntu:systemctl restart mysqld
- 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.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. - Analyze and optimize all tables in all databases:
- for Linux:# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -uadmin –optimize –all-databases
- 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
Xem thêm: