The MySQL high CPU usage is caused due to some of the common reasons like table fragmentation, slow queries, lock waits, etc. We will see some common ways to minimize CPU usage percent.
The MySQL high CPU usage
Table fragmentation issue:
- The table has many time random insertion and deletion cause index to fragmented.
- Physical ordering of index page is not close to index ordering of the record
- One symptom of the fragmentation table takes more space than it “should” take.
- Another symptom of fragmentation is table scan such as this takes more time than it “should” take.
The solution to the issue is to perform a defragmentation operation
- Solution 1: To speed up the operation periodically perform alter table operation which causes rebuild the table.
ALTER TABLE tbl_name ENGINE=INNODB
- Solution 2: Another way is to defragmentation
Another way of defragmentation operation is to use mysqldump take backup and drop table and import it again
- Solution 3: Run the OPTIMIZE TABLE command
Mysql slow Queries :
- Check the DB engine to innodb if it is not then make it innodb it depends on READ and WRITE operation. Because when the engine is MyISAM because most of the queries seem with Table-level lock
so they lock the queries. While executing the concurrent select and insert queries.
- We can maintain a slow query log
For maintaining the slow query log we have to set /etc/my.cnf with following parameters log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
Query_time, Lock_time, Rows_sent, Rows_examined
If we check the query we will find the log as follow…
INSERT IGNORE INTO employee (
login,’logout’) SELECT l.employee_id,l.login,l.logout FROM
# Time: 190504 7:54:52
# User@Host: root[root] @ localhost 
# Query_time: 3757.884074 Lock_time: 0.000228 Rows_sent: 0 Rows_examined: 162133
Now Lock_time is negligible 0.000228 for checking the query which is good for optimization or not.
Rows Sent / Rows Examined if it is high then it is good for optimization.
Detect the cause of CPU utilization using queries:
- Check the slow query logsWhen we check the slow query log we will find some key things like what amount of time query has taken, Query_time, Lock_time, Rows_sent, and Rows_examined.
- Use of SHOW FULL PROCESSLIST We can identify problematic queries using show full processlist
This command shows you which threads are running currently on your MySQL instance.
- Using INFORMATION_SCHEMA.INNODB_TRXINNODB_TRX table provides information about all currently executing InnoDB transactions that are not read-only transactions
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
- Using INFORMATION_SCHEMA.INNODB_LOCKS
INNODB_LOCKS table provides information about locks that an InnoDB transaction has requested but hasn’t received.
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
INNODB_LOCK_WAITS table provides one or more rows for each blocked InnoDB transaction.
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
6.SHOW ENGINE INNODB STATUS;
The SHOW ENGINE INNODB STATUS query provides information from the standard InnoDB monitor about the state of the InnoDB storage engine.
SHOW ENGINE INNODB STATUS;