MySQL database executes analyze to collect information

MySQL TutorialThe column executes analyze to collect information.

Introduction to the fault

Before, a developer came to me and asked about a certain function of the application It was much slower than before, so the development provided slow SQL statements. I went to the corresponding MySQL database to look at the execution plan and found that the execution plan was incorrect. The first reaction was that the statistical information of one of the tables was inaccurate, resulting in the SQL statement The execution plan is wrong, from efficient query SQL to slow SQL. After locating the problem, it is natural to analyze and collect information again. At this time, all the selects on the analyze table suddenly get stuck and do not return any results. Then the application explodes, and various alarm messages are sent.

Fault recovery

At that time, the analyze operation was performed by a slave library, which was basically affected by the select query, so the query is simulated here operate.

Create a simulation 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 set (0.00 sec) 

Simulate slow query, because the amount of data here is not enough, so use sleep instead
session1: simulate slow query

mysql> select sleep(1000) from t_test_1; 

session2: simulate the statistics of the collection table

mysql> analyze table t_test_1; 

session3: After simulating the execution of the analyze command, execute a select query on the t_test_1 table

mysql> select * from t_test_1 where id=5; 

session4: query all session information

mysql> select * from processlist order by time desc;
 +----+------+-----------+--------------------+----  -----+------+-------------------------+-----------  --------------------------------------+
 | ID | USER | HOST | DB | COMMAND |
 +----+------+-----------+--------------------+----  -----+------+-------------------------+-----------  --------------------------------------+
 | 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) 

Among all the session information obtained from session4, you can see that the status of 2 sessions is “Waiting for table flush”.

Waiting for table flush reason

When the MySQL database does FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE These operations will result in the need to close the table in memory, and reopen the table, and load the new table structure into memory. But to close the table, you need to wait for all the operations on this table to finish (including select, insert, update, lock table, etc.), so when there is a particularly slow select that has been executing, the analyze table command has not been able to end.

Solution

Now that you know what caused the Waiting for table flush, start to locate the slow SQL statement. Here we can see that what we are executing is to collect the t_test_1 table, so we need to query the slow query involving the t_test_1 table, and the execution time is longer than the execution time of the analyze table t_test_1 session.

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 |
 +----+------+-----------+--------+---------+------  +-----------------------+-----------------------  -----------+
 | 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 set (0.37 sec) 

Using the above sql statement, it is easy to locate the session with id=21, causing the analyze table t_test_1 to be stuck, so you need to kill session 21.

mysql> kill 21;
 Query OK, 0 rows affected (0.01 sec)

 mysql> show full processlist;
 +----+------+-----------+--------------------+----  -----+------+----------+-----------------------+
 | Id | User | Host | db | Command |
 +----+------+-----------+--------------------+----  -----+------+----------+-----------------------+
 | 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 set (0.00 sec) 

Kill the session, and the fault is resolved.

Suggestion

Production execution analyze table suggestion
1. Before execution, estimate the amount of data in the table, estimate the time required based on experience, and check whether there is slow SQL for collecting information tables, and long transactions are being executed.

2. Avoid executing analyze table to collect statistical information during peak business hours.

More related free learning recommendations: mysql tutorial(video)

The above is the detailed content of the information collected by the MySQL database, please pay attention to other related articles on 1024programmer.com for more information!