The fault description

, development and find me, said that the application of a function query is much slower than before, make development provides a slow SQL statements, went to the corresponding MySQL database to see the execution plan, found the execution plan is not correct, the first reaction is one of the tables of statistics is not accurate, led to the SQL statement execution plan is wrong, From efficient query SQL to slow SQL. Select * from the analyze table (select * from the analyze table); select * from the analyze table (select * from the analyze table);

Fault checking

At that time, the analyze operation is performed by a slave library, which is basically affected by the SELECT query. Therefore, the query operation is simulated here.

Create mock table

mysql> select * from t_test_1; +----+--------+-------+--------+ | id | name | name2 | status | +----+--------+-------+--------+ | 1 | name1 | 1001 | 0 | | 2 | name1 | 1002 | 1 | | 3 | name1 | 1003 | 1 | | 4 | name1 | 1004 | 0 | | 5 | name1 | 1005 | 1 | | 6 | name1 | 1006  | 0 | | 7 | name1 | 1007 | 2 | | 8 | name1 | 1008 | 0 | | 9 | name1 | 1009 | 1 | | 10 | name10 | 1001 | 0 | +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + 10 rows in the set (0.00 SEC)Copy the code

Session1: simulate slow query, because there is not enough data, so use sleep instead of session1: simulate slow query

mysql> select sleep(1000) from t_test_1;
Copy the code

Session2: Simulate collect table statistics

mysql> analyze table t_test_1;
Copy the code

Session3: Simulate a select query on the T_TEST_1 table after executing the analyze command

mysql> select * from t_test_1 where id=5;
Copy the code

Session4: Queries information about all sessions

mysql> select * from processlist order by time desc; +----+------+-----------+--------------------+---------+------+-------------------------+------------------------------- ---------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----+------+-----------+--------------------+---------+------+-------------------------+------------------------------- ---------------+ | 21 | root | localhost | testdb | Query | 242 | User sleep | select sleep(1000) from t_test_1 | | 23 |  root | localhost | testdb | Query | 180 | Waiting for table flush | analyze table t_test_1 | | 24 | root | localhost | testdb | Query | 3 | Waiting for table flush | select * from t_test_1 where id=5 | | 22 | root | localhost | information_schema | Query | 0 | executing | select * from processlist order by time desc | +----+------+-----------+--------------------+---------+------+-------------------------+------------------------------- ---------------+ 4 rows in set (0.00 SEC)Copy the code

In session4, two sessions are in “Waiting for table flush” state.

Waiting for table flush Cause

MySQL database FLUSH TABLES TBL_NAME, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE This results in the need to close the table in memory, reopen the table, and load a new table structure into memory. But close table, needs to wait for all at the end of this operation on the table (including the select, insert, update, the lock table, etc.), so when there is a special slow select has been executed, analyze table command has not ended.

The solution

Waiting for table flush Waiting for table flush As you can see here, we are collecting THE T_TEST_1 table, so we need to query the session involving t_TEST_1 which is slow and takes longer to execute than the ANALYZE table T_TEST_1.

mysql> select * from processlist where info like '%t_test_1%' and time >=(select time from processlist where id=23) order by time desc; +----+------+-----------+--------+---------+------+-------------------------+----------------------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | + - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 21 root | localhost | testdb | Query | 1187 | User sleep | select sleep(1000) from t_test_1 | | 23 | root | localhost | testdb | Query | 1125 | Waiting for table flush | analyze table t_test_1 | +----+------+-----------+--------+---------+------+-------------------------+----------------------------------+ 2 rows In the set (0.37 SEC)Copy the code

The analyze table t_test_1 is dead, so kill session 21.

mysql> kill 21; Query OK, 0 rows affected (0.01sec) mysql> show full processlist; +----+------+-----------+--------------------+---------+------+----------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+--------------------+---------+------+----------+-----------------------+ | 22 | root | localhost | information_schema | Query | 0 | starting | show full processlist | | 23 | root | localhost | testdb | Sleep  | 1205 | | NULL | | 24 | root | localhost | testdb | Sleep | 1028 | | NULL | + - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.00) sec)Copy the code

Kill the session and the fault is removed.

advice

Production Executing the Analyze Table Suggestion 1. Before executing the analyze table, estimate the amount of data in the table, estimate the elapsed time based on experience, and check whether slow SQL or long transactions of the information table are being executed.

2. Do not run the Analyze table to collect statistics during peak hours.

Focus on

1. If you like this article, please give it a thumbs up and forward.

2. If you especially like it, please pay attention.