问题描述
线上出现一个根据索引update字段,却出现全表扫描,并锁定表的case。
监控显示
现象
1. 慢查询日志中出现
# Query_time: 424.767255 Lock_time: 0.000029 Rows_sent: 0 Rows_examined: 142439284SET timestamp=1515479326;UPDATE `Table_XXX` SET `Field_XXX` = 1 WHERE `Key_XXX` = 'YYYYYYY'
2. 表上的其他更新都被这个update blocked,持续7分钟。
实际上字段Key_XXX上有索引,而且从show index上看,结果区分度很好,与主键几乎是1:1的区分度。
说明
1. 出现问题的版本是5.5.39
2. InnoDB 5.6之前的版本,没有Persistent Statistics,因此索引统计信息是动态修改的。触发条件:
1) counter > 2000000000 或者
2) counter > 16 + table->stat_n_rows / 16
3. 这个实例从19号凌晨1点开始到出问题的时候,总共删除了4000w行数据,占表总行数的1/3,因此必然触发了索引统计(不止一次索引统计)
4. MySQL 5.7.18之前,存在这样的bug: 在做索引重建过程中,会先清空统计信息。由于锁控制问题,可能会导致优化器在获取索引统计的时候,得到一个“空的统计信息”
参考https://bugs.mysql.com/bug.php?id=82968
5. 这样优化器认为只能做全表扫描。如果是一个update语句,就会锁全表。
6. 考虑到这个实例当天删除的数据量,触发这个bug的概率提升。
(说明,以上为间接分析。由于MySQL做索引统计信息重建时并没有明确记录,因此没有找到直接证据。)
解决方案
1. 升级到5.7.18及以上版本
2. 5.6 版本可以在执行批量删除期间,打开Persistent Statistics(innodb_stats_persistent)
3. 对于暂时无法升级的5.5版本
1) 一种方式是通过监控,kill掉异常的update。但是需要人为接入,kill update线程是危险操作
2) 控制删除的量。在开始大量删除之前,利用innodb_stats_on_metadata 主动做一次统计,然后每删除一个批量,在还没有达到1/16的时候,再主动做一次。这样可以确保删除期间不会出现索引重建。当然手动重建时,也是要观察是否影响update。好处只是由于是定点操作,可以主动检查。
根源还是删除导致,这个表一天内删除了1/2的数据,这个设计本身不够合理。这种情况应该将这个表按照天分表,之后直接drop table的形式来做。
查询过程问题记录
查询问题过程中,需要去查看slow log,访问日志(这里是看proxy的访问日志),期间要看各种访问的时序关系,遇到的几个疑问,在这里记录一下。
问题一:
主从相同的事务 xid不同?
同一个事务,主库binlog的xid和从库relay log的xid是相同的,但是和同一个事务从库上的xid确实不一定相同
MySQL内部xid格式:MYSQL_XID_PREFIX + server_id + my_xid
MYSQL_XID_PREFIX:MySQLXid(源码写死)8 bytes
问题二:
slow记录的语句在binlog里面没有找到记录, 具有和binlog不相同的logid(这里的logid是和业务约定,在每一个语句后面加一个标识,查询问题时候容易在日志中定位具体的SQL语句)?
slow log中记录的并不一定是执行成功的,一个执行失败,或者执行过程中被kill的语句,只要终止的时候时间超过了slowlog设置的阈值,就会记录到slow log。
问题三:
在查询日志的时候我们看到749xxxx这个请求(真正锁表的请求)确实是先发的, 而750xxxx的语句(实际执行成功的语句)则是较晚发送的
因此时间序列是这样的:
时刻A: client1 发起update(749xxxx),触发全表锁
5.03s后:client1 超时关闭
客户端重试,client2: 发起update (750xxxx)
大概0.1s后,client2 执行完成。
7分钟后, 全表锁的语句被kill,记录slow log,并退出。在这个过程中部分的update被锁住没法执行,部分的update是执行成功的。
问题:既然client1 的事务是7分钟才完成,client2为什么没有被block住?
问题解释:
1.MySQL的行锁是要对于满足过滤条件的行,一行行读取并锁定的。
2.client2的update,由于用上索引,因此很快定位到行。
3.client1 的update由于触发bug走全表扫描,表又比较大(>1亿行),因此锁需要时间才能遍历到行。
4. 也就是说client2 ”后发先至“,加锁、执行、写日志完成时,client1的事务还没有锁到这行。
因此client2的执行没有被client1的update锁住。
PS:从上面序列中发现,在客户端关闭请求的时候,db端并没有停止执行。其实既然客户端已经关闭了,继续执行意义不大,尤其是对于select语句,MySQL客户端在这种时候会主动发一个kill去把执行的命令停止掉。因此建议proxy能够模拟MySQL这个行为。
问题四:
开启innodb表持续统计的一些参数
(1) stats_persistent 对于innodb表是否保证持续统计ALTER TABLE table_name stats_persistent=1默认是由innodb_stats_persistent选项决定的(2) stats_auto_recalc 对于innodb表是否自动计算持续统计默认是由innodb_stats_auto_recalc 选项决定的,为1 时,当有10%的数据发生改变时,就重新计算,按照我的测试大概超过10%(3) stats_sample_pages 指定随机索引页的数量