QPS surge will lead to increased CPU usage
analyze
You can use the monitoring tool to view the CPU utilization curve chart and the QPS curve chart for comparison. If the fluctuation of the CPU graph is basically consistent with the fluctuation of the QPS graph, it can be clearly stated that the QPS rises when the CPU rises. On the contrary, if the CPU curve chart compares with the QPS curve chart, there is an asynchronous peak jitter, which means that when the QPS does not surge significantly, the CPU occupancy is too high, which is likely to be related to slow SQL, and subsequent slow SQL can be checked.
Note: If there is no monitoring tool to assist in analysis, you can use the show global status like ‘Questions’ and show global status like ‘Uptime’ query, and dividing the two into the QPS value.
show global status like 'Questions'; show global status like 'Uptime';
Solve QPS surge:
- To determine whether it is caused by the surge in actual business. If it is caused by the surge in actual business, it can be used to expand resources to meet the business needs of the surge in business;
- Find the interface of the application system corresponding to the specific surge in queries and perform current limit control to protect the concurrent database access;
- The application adopts read-write separation to reduce the pressure of single-point access;
- Check whether there is loop insertion or update of SQL, and change it to batch operations to reduce the number of executions.
Slow SQL will cause CPU to rise
analyze
Use show processlist to view the current MySQL thread operation status, mainly analyze slow SQL through Time connection time and State's current SQL state. Generally speaking, if there is Sending data, it means that the query is slow. You can copy the SQL in info and view the detailed execution plan through explain for analysis.
show processlist;
It can be analyzed through the slow SQL logging function that comes with MySql:
Turn on the slow SQL query log through root logging in to the database, set global slow_query_log = 'ON', and set the slow SQL filtering time set global long_query_time = 1 (being considered slow SQL after more than 1 second), and specify the slow SQL log file storage path set global slow_query_log_file = '/var/lib/mysql/test_1116.log'. By continuously observing the slow SQL log file, find out the specific slow SQL copy and view the detailed execution plan through explain for analysis.
Solve slow SQL:
- Emergency processing can first relieve the problem by killing the corresponding thread (kill process id);
- Create a new valid index or optimize SQL statement when there is no index or index failure;
- There are a large number of aggregation operations in SQL: simplify SQL and extract logic into business code; aggregation operations are asynchronous or preprocessed;
- SQL returns too much data and changes it to pagination query;
- There are many reading and writing locks, and the competition is fierce, and the database is divided into tables or reading and writing is separated.
A large number of idle connections can cause CPU to rise
show full processlist;
analyze
Check that Command has a large number of Sleeps through show full processlist, and then check whether the loading time is very long based on Time. If a large number of long-term sleeping threads occupy database connections, it will cause the database throughput to decrease, which will lead to blockage and also cause the CPU to float high.
solve:
- Modify the wait_timeout idle waiting time in the MySql configuration file. The smaller the value, the fewer the relatively idle threads will be. However, it is necessary to restart MySql before it will take effect;
- Hot modify this parameter by setting global wait_timeout = XX.
Common commands for troubleshooting MySql problem
Sql execution plan, perform SQL execution analysis
explain + sql;
explain select * from user;
Check the current execution thread status of the database
show full processlist;
The key fields include Command, Time, State, and Info, which can view commands, duration, status, and corresponding SQL information in all current connection threads.
Query all things currently running
select * from information_schema.INNODB_TRX ;
In the execution result, you can see whether there is a table lock waiting or deadlock. If a deadlock occurs, you can use the following command to kill the currently running transaction: KILL trx_mysql_thread_id.
View the currently appearing lock
select * from information_schema.INNODB_LOCKS;
In the change result, you can see the lock type, transaction ID, lock level, lock mode and other information.
Check out the deadlock
SELECT b.trx_state, , , AS block_state, AS block_time, a.requesting_trx_id, a.requested_lock_id, b.trx_query, b.trx_mysql_thread_id, a.blocking_trx_id, a.blocking_lock_id, c.trx_query AS block_trx_query, c.trx_mysql_thread_id AS block_trx_mysql_tread_id FROM information_schema.INNODB_LOCK_WAITS a LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = ORDER BY a.requesting_trx_id;
Check InnoDB status
SHOW ENGINE INNODB STATUS;
This is the end of this article about the reasons for the MySql CPU surge. For more information about the reasons for the MySql CPU surge, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!