Troubleshoot High CPU usage by MYSQL

How to troubleshoot high CPU usage by MySQL?

1. Cek query yang running  pada server :

SHOW FULL PROCESSLIST;

2. Cek MySQL error log :

/var/log/mysqld.log

3. Check general status of the server: RAM and available disk space:

# free -h
# df -h

4. Check slow query log by enabling MySQL slow query log.

a. Open the file /etc/my.cnf (rpm base system) or /etc/mysql/my.cnf (dpkg base system) any editor and add the lines below to the [mysqld] section:

slow_query_log = 1
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 10
log-queries-not-using-indexes

where long_query_time is a time taken by an sql query to execute.

b. Create the file /var/log/mysql-slow.log

# touch /var/log/mysql-slow.log

c. Set correct permissions on the file:

# chown mysql:mysql /var/log/mysql-slow.log

d. Restart the mysqld service:

# service mysqld restart

Sumber ;
https://support.plesk.com/hc/en-us/articles/213393329-How-to-troubleshoot-high-CPU-usage-by-MySQL-
https://support.plesk.com/hc/en-us/articles/115002888409