• 技术文章 >数据库 >mysql教程

    MySQL数据库执行analyze采集信息

    coldplay.xixicoldplay.xixi2020-11-19 17:08:08转载265

    MySQL教程栏目执行analyze采集信息。

    故障简介

    之前,有开发找到我,说应用的某个功能查询比以前慢了很多,让开发提供了慢的SQL语句,去对应的MySQL数据库看了一下执行计划,发现执行计划不正确,第一反应就是其中的一个表的统计信息不准确,导致了SQL语句的执行计划不对,从高效的查询SQL变成了慢SQL。定位到问题之后,自然是 analyze 一下,重新采集信息,这个时候,却发现 analyze 表上的所有 select 突然卡住了,不返回任何结果,然后应用就炸了,各种告警短信。

    故障复盘

    当时执行analyze操作的是一个slave库,受影响基本是select查询,所以在这里模拟的是查询操作。

    创建模拟表

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

    模拟慢查询,由于这里数据量不够,所以用sleep代替 session1:模拟慢查询

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

    session2:模拟收集表的统计信息

    mysql> analyze table t_test_1;复制代码

    session3:模拟执行analyze命令之后,在t_test_1表上执行一次select查询

    mysql> select * from t_test_1 where id=5;复制代码

    session4:查询所有会话信息

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

    从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进行统计信息采集。

    更多相关免费学习推荐:mysql教程(视频)

    以上就是MySQL数据库执行analyze采集信息的详细内容,更多请关注php中文网其它相关文章!

    本文转载于:juejin,如有侵犯,请联系a@php.cn删除
    专题推荐:analyze MySQL
    上一篇:mysql中光标如何书写 下一篇:总结MySQL中float、double、decimal三个浮点类型的区别
    第14期线上培训班

    相关文章推荐

    • 生产环境MySQL表的维护:check、optimize和analyze_MySQL• Oracle 工具:Analyze• MySQL 优化脚本(analyze)引起的应用崩溃• 如何使用IKAnalyzer分词器自定义扩展词典

    全部评论我要评论

  • 取消发布评论发送
  • 1/1

    PHP中文网