MySQL database executes analyze to collect information

MySQL Tutorial column executes analyze to collect information.

Fault Introduction

Before, a developer came to me and said that a certain function query in the application was much slower than before, so I asked the developer to provide For slow SQL statements, I checked the execution plan in the corresponding MySQL database and found that the execution plan was incorrect. The first reaction was that the statistical information of one of the tables was inaccurate, which caused the execution plan of the SQL statement to be incorrect. From the efficient SQL query It becomes slow SQL. After locating the problem, it was natural to analyze and collect the information again. At this time, I found that all the selects on the analyze table were suddenly stuck and did not return any results. Then the application exploded with various alarm messages.

Fault review

The analyze operation was performed on a slave library, and the affected ones were basically select queries, so what is simulated here is the query operation.

Create 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. Since the amount of data here is not enough, sleep is used instead. session1: Simulate slow query

mysql> select sleep(1000) from t_test_1;复制代码

session2: Simulate collecting table statistics

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 | 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)复制代码

From 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 performs operations such as FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE, it will cause the need to close the memory table and reopen the table to load the new table structure into memory. However, closing a table requires waiting for all operations on the table to end (including select, insert, update, lock table, etc.). Therefore, when a particularly slow select is being executed, the analyze table command will never end.

Solution

Now that we know what causes Waiting for table flush, we can start locating the slow SQL statement. Here you can see that what we are executing is to collect the t_test_1 table, so we need to query a slow query involving the t_test_1 table, and the execution time is longer than the execution time of 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 set (0.37 sec)复制代码

Using the above sql statement, it is easy to locate the session with id=21, causing analyze table t_test_1 to get stuck, so we 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 | 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 set (0.00 sec)复制代码

Kill the session, failure Lift.

Suggestions

Production execution analyze table suggestions 1. Before execution, estimate the data volume of the table and estimate the time required based on experience. At the same time, check whether there is a slow SQL that collects the information table and long transactions are being executed.

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

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of MySQL database executes analyze to collect information. For more information, please follow other related articles on the PHP Chinese website!