An example of high MySQL Load

Problems Found
The top command checks the server load and finds that mysql often has more than 90% CPUs. The cause of this high Mysql load is probably mainly caused by index problems and some abnormal SQL statements.

Troubleshooting
1. determine the type of high load. The top command determines whether the high load is CPU or IO.
2. Check the slow query log. If the front-end has new code, the slow query may cause high load.
3. Check whether hardware problems are caused by disk faults.
4. Check the monitoring platform and compare the loads of the machine at different times.

Slow query of records
Edit the Mysql configuration file (my. cnf) and add the following lines in the [mysqld] field:
Log_slow_queries =/usr/local/mysql/var/slow_queries.log # Slow query Log Path
Long_query_time = 5 # statements that record SQL queries for more than 5s
Log-queries-not-using-indexes = 1 # records SQL statements that do not use indexes

View slow query logs

1. Tail/usr/local/mysql/var/slow_queries.log

# Time: 130308 7:56:39
# User @ Host: user [xxxx] @ xxxx []
# Query_time: 0.001118 Lock_time: 0.000301 Rows_sent: 26 Rows_examined: 52
SET timestamp = 1362711399;
SELECT * FROM com WHERE iSUSEd = 1 AND category_id IN ('1') order by vieworder ASC;
Four Parameters
Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 54
The query time lock time indicates the number of rows scanned in the query result.
It mainly refers to the statements that scan for a large number of rows, then add the corresponding index to the database, and then optimize abnormal SQL statements.

In extreme cases, kill the SQL Process
Find the SQL statement that occupies the cpu too long and execute the following command in mysql:

1. Show processlist;

After confirming that the last SQL statement is in the Query state and the Time is too long, lock its ID and execute the following command:

1. Kill QUERY 269815764;

Note: killing the SQL process may lead to data loss. Therefore, the importance of data should be measured before execution.