MySQL数据库施行analyze采集信息
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采集信息的细致内容,更多请关注 百分百源码网 其它相干文章!