2022年01月11日发布MySQL数据库执行analyze采集信息

xhjyxxw

2023-12-05 帮助1人

知行礼动

大家好，今日小科来聊聊一篇关于2022年01月11日整理发布：MySQL数据库执行analyze采集信息的文章，现在让我们往下看看吧！

MySQL教程专栏执行分析收集信息。

在引入故障之前，有一个开发发现了应用程序的某个功能。查询比以前慢得多，因此开发人员向相应的MySQL数据库提供了缓慢的SQL语句。看执行计划，发现执行计划不正确。第一个反应是其中一个表的统计信息不准确，导致了SQL语句的执行计划不正确，从高效的查询SQL变成了慢速的SQL。找到问题后，自然要对信息进行分析和重新收集。这时发现分析表上所有的select突然卡住，不返回任何结果。然后应用程序会爆炸各种警报消息。

当时的analyze操作是由一个从库执行的，基本上是一个select查询，所以这里模拟了查询操作。

创建模拟表

mysql从t_test_1中选择*；

- - - -

| id |姓名|姓名2 |状态|

- - - -

| 1 |名称1 | 1001 | 0 |

| 2 |名称1 | 1002 | 1 |

| 3 |名称1 | 1003 | 1 |

| 4 |名称1 | 1004 | 0 |

| 5 |名称1 | 1005 | 1 |

| 6 |名称1 | 1006 | 0 |

| 7 |名称1 | 1007 | 2 |

| 8 |名称1 | 1008 | 0 |

| 9 |名称1 | 1009 | 1 |

| 10 | name10 | 1001 | 0 |

- - - -

10行一组(0.00秒)复制代码以模拟慢速查询。因为这里没有足够的数据，所以用睡眠代替。

会话1:模拟慢速查询

mysql从t_test_1中选择sleep(1000)；复制代码会话2:模拟收集表的统计信息。

mysql分析表t _ test _ 1；复制代码会话3:在模拟分析命令后，对t_test_1表执行选择查询。

mysql从t_test_1中选择*其中id=5；复制代码会话4:查询所有会话信息

mysql按时间从processlist订单中选择* desc；

- - - - - - - -

|标识|用户|主机|数据库|命令|时间|状态|信息|

- - - - - - - -

| 22 | root | localhost | information _ schem

a | Query | 0 | executing | select * from processlist order by time desc | ---- ------ ----------- -------------------- --------- ------ ------------------------- ---------------------------------------------- 4 rows in set (0.00 sec)复制代码

从session4获取的所有会话信息中可以看到有2个会话的状态是“Waiting for table flush”。

Waiting for table flush原因

当MySQL数据库做FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE这些操作时会导致需要关闭内存中的表并重新打开表加载新的表结构到内存中。但是关闭表需要等待所有的在这个表上的操作执行结束（包括select,insert,update,lock table等）所以当有一个特别慢的select一直在执行时analyze table命令就一直无法结束。

解决方案

既然知道什么原因导致的Waiting for table flush就开始定位慢sql语句。在这里可以看到我们执行的是采集t_test_1表所以需要查询涉及t_test_1表的慢查询并且执行时间比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)复制代码

用上面的sql语句很容易就定位到id=21的会话导致analyze table t_test_1卡死所以需要kill掉会话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)复制代码

杀掉会话故障解除。

建议

生产执行analyze table建议1.执行之前先估算一下表的数据量根据经验预估需要消耗的时间同时查看是否有采集信息表的慢SQL长事务在执行。

2.避免在业务高峰期执行analyze table进行统计信息采集。

这篇好文章是转载于：知行礼动