Mysql high CPU usage

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.

 

Mysql high CPU usage

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

  1. Solution 1: To speed up the operation periodically perform alter table operation which causes rebuild the table.
    ALTER TABLE tbl_name ENGINE=INNODB
  2.  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
  3. Solution 3: Run the OPTIMIZE TABLE command

Mysql slow Queries :

  1. 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.
  2. 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 ( employee_id, login,’logout’) SELECT l.employee_id,l.login,l.logout FROM attendance;

# 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:

  1. 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.
  2. 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.
  3. Using INFORMATION_SCHEMA.INNODB_TRXINNODB_TRX table provides information about all currently executing InnoDB transactions that are not read-only transactions
  4. Using INFORMATION_SCHEMA.INNODB_LOCKS

INNODB_LOCKS table provides information about locks that an InnoDB transaction has requested but hasn’t received.

         5.Using INFORMATION_SCHEMA.INNODB_LOCK_WAITS:
INNODB_LOCK_WAITS table provides one or more rows for each blocked InnoDB transaction.

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.

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *