Shulou(Shulou.com)06/01 Report--

This article is about MySQL database implementation analyze collected information sample content to share. Xiao Bian thinks it is quite practical, so share it for everyone to make a reference. Let's follow the editor and have a look.

fault profile

Before, a developer came to me and said that a certain function query of the application was much slower than before. He asked the developer to provide a slow SQL statement. He 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 execution plan of the SQL statement being incorrect. From efficient query SQL to slow SQL. After locating the problem, he naturally analyzed it and collected the information again. At this time, he found that all the select items on the analyze table were suddenly stuck and did not return any results. Then, the application exploded with various alarm messages.

fault recovery

At that time, the analyze operation was performed by a slave library, and the selected query was basically affected, so the query operation was simulated here.

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) Copy Code

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

mysql> select sleep(1000) from t_test_1; copy code

Session2: Simulate the statistics of the collection table

mysql> analyze table t_test_1; copy code

Session3: Simulate executing the analyze command and executing a select query on the t_test_1 table

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

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) Copy Code

From all the session information obtained from session4, we can see that there are two sessions whose state is "Waiting for table flush".

Waiting for table flush

When a MySQL database does 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 the new table structure into memory. But closing a table requires waiting for all operations on the table to finish (including select,insert,update,lock table, etc.), so the analyze table command never finishes when there is a particularly slow select running.

solutions

Now that you know what causes Waiting for table flush, start locating slow sql statements. Here you can see that we are collecting the t_test_1 table, so we need to query a slow query involving the t_test_1 table, which takes longer to execute than 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 |+----+------+----------------------------------

Using the sql statement above, it is easy to locate the session id=21, causing the analyze table t_test_1 to be stuck, so you need to kill the 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) Copy Code

Kill the session, the fault is resolved.

recommendations

1. Before execution, estimate the data volume of the table, estimate the time required according to experience, and check whether there is slow SQL for collecting information tables. Long transactions are being executed.

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

Thank you for reading! The example of MySQL database execution analyze collecting information is shared here. I hope the above content can be of some help to everyone, so that everyone can learn more knowledge. If you think the article is good, you can share it so that more people can see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.