精选留言(52)

• 
  某、人 置顶

  2019-01-03

   5

  最近才发生了个案列:
  由于一个delete大事务导致磁盘空间满了,数据库hang住,连接不上,所以无法kill掉该大事务
  当时的观察到的现象是:
  binlog有一个文件已经达到50多G
  lsof | grep delete 该tmp文件100多G
  redo log还是只有4个组,每个文件1G
  undo log大概有100来G
  由于数据库连不上,只有把连接切到从库,kill掉主库的进程。过了几分钟,binlog文件才缩小为原来的大小。把主库启起来,但是recovery非常慢。后面kill掉,又以innodb_force_recovery=3恢复,recovery也是半天没反应。由于这个库也不是重要的库,就把新的主库的备份文件重做了之前的主库,以从库启起来
  
  通过最近的学习+测试分析了下,为什么binlog达到了50多G。tmp文件100多G.
  由于binlog_cache不够用,把binlog写进了tmp文件中,binlog文件50多G,说明事务已经执行完成,是binlog在fsync阶段,把空间占满了。fsync并不是一个move而是相当于copy。要等binlog完全落盘以后,才会删除之前的tmp文件。redo log由于是循环写,而且在事务执行过程中,就会把redo log分为mtx落地到磁盘上。所以没有一次性暴增,还是以1G的大小持续写.
  我也是后续做测试,观察在事务进行中,redo log文件一直都有变化。binlog没有变化
  binlog是在事务执行完以后,才一次性fsync到磁盘
  但是为什么recovery=3的情况下,还比较耗时。我估计是之前脏页较多,而redo log又全部被覆盖掉,
  需要先通过binlog来恢复redo log,然后再通过redo log来恢复数据页。
  
  请问老师有没有更好的办法来处理这种hang住的情况?
  如果在操作系统层面kill掉执行的线程,就好了。
  昨天提到的问题3,我也没有测试出来Sending to client这个状态.是之前别人问到的,我也挺懵

  展开

  作者回复: 先说明下，binlog是没有“恢复redolog”的能力的哈。其它部分分析得很好👍🏿
  
  Binlog 这么大，说明是大事务，崩溃恢复的时候要处理的redolog 很多，估计耗时间耗在这。
  
  这种磁盘空间满的情况，以前我的处理方法是把最老的binlog移动到别的盘（如果确定日志已经备份到备份系统了就删掉），目的是腾出空间让这个事务执行完成。
  后面可以考虑这种方案，强制重启还是有点伤的，不过核心还是做好监控，不让出现磁盘100%写满的情况

• 
  Long

  2019-01-02

   10

  不是专业DBA，遇到过几次数据库问题，有的能解决，有的好像除了重启或者干等着没啥好办法。
  MySQL5.6版本遇到的部分问题：
  
  1. 几个线程处于killed状态一直kill不掉（1天），然后备份的时候MySQL backup flush all tables with read lock的时候被阻塞，后面的线程只能等待flush table, kill backup以后也没有办法kill那几个killed状态的语句（processlist显示的killed状态的语句的就是show columns, show create table这样的），后面没办法，重启了server。（看到老师后面第25讲有关于kill的解释，非常期待新知识）
  
  2. 一个非常大（大几百万行）的表truncate，结果后面所有的线程都阻塞了，类似于下面这个MySQL bug的场景，结果就是等这个truncate结束。没有继续干预。
  https://bugs.mysql.com/bug.php?id=80060
  
  3. 某个新功能上线以后，一个记录操作人员操作页面操作时间KPI的功能，由于sql性能不好，在业务上线跑了3天后数据量增多到临界值，突然影响了整个系统性能。数据库发现是大量的sql执行状态是converting heap to MyISAM，sql写法类似 select (select * from table) where id(有索引)= xxxx order by yyyy
  DBA以及他们团队要求重启。但是分析了几分钟后提供了几个意见给"DBA"，并解释重启解决不了问题：首先这个问题重启是解决不了，因为每次这个sql查询全表，查询分配的临时表空间不足了，需要把结果集转到磁盘上，重启了sql动作没变，参数没变所以重启解决不了问题。
  页面查询也没法屏蔽，页面查询也无法过滤条件，
  （1）和研发确认后，表数据删除不影响功能，只影响客户的KPI报表，先备份表，然后删除，后面等功能修复了再补回去。
  （2）调整max_heap_table_size，tmp_table_size，扩大几倍
  （3）给这个sql的唯一的一个order by字段加个索引。
  同时催促研发提供hotfix。最终选择了最简单有效的（1）问题解决，研发迅速后面也发了hotfix解决了。
  
  4. 某个消费高峰时间段，高频查询被触发，一天几十万次执行，由于存量数据越来越多，查询性能越来越慢，主要是索引没有很好规划，导致CPU资源使用飙升，后面的sql执行越来越慢。 最后尝试了给2个字段添加单独的索引，解决了50%的问题，看到执行计划，extra里面，索引合并使用了intersect，性能还是慢，然后立马drop原先的2个单独索引，创建两个字段的联合索引，问题解决了。
  
  5. 死锁回滚，导致的MySQL hang住了，当时刚入门，只能简单复现死锁，没有保留所有的日志，现在想查也查不了了。。。
  感觉大部分都是慢sql和高频事务导致的。
  
  （当然后面的慢sql监控分析，项目上就很重视了。。）
  
  
  今天看了这期专栏，发现5.7的这个功能，query_rewrite，受教了。等我们升到5.7以后，可以实际操练下。上面的问题3，也可以用这个功能了（因为是新业务，新表，特殊sql，完全可以起到hotfix的作用）。
  
  
  请老师帮忙看下上面几次故障是否有更好，更专业的解决方案。多谢

  展开

  作者回复: 1. Kill 掉备份线程在当时是最好的办法了。不过我之前确实也没碰到过show create table 不能kill的情况，我看下代码，如果能复现出来加入那篇文章中
  2. 嗯，80060这个问题是因为要truncate，所以要回收脏页导致慢，不过这个问题在5.5.23就优化掉了哦，看你使用的是5.6，可能是别的原因。truncate如果不是被锁，而是已经在执行了，确实还是别做别的事情，等结束最好；
  3. 这个语句是因为子查询要用临时表，跟order by 无关的（你看到的阶段还没开始order by 操作）。这个语句的临时表都能多到把磁盘打满，增加tmp_table_size是没用的。
  就是说这三个方法里面2和3其实都无效。你们当时的选择很精准呀。
  而且前面提出“重启无效”的这个人值得团队内大力表扬（是不是就是你😄）
  另外这个语句，看着像有机会优化的样子，核心方向是去掉临时表
  4.可以只删掉其中一个独立索引，再加一个联合索引，就是变成(a,b)和(b)这两种索引，也就是把(a)改成(a,b)，这样是加法，相对比较安全。删除索引是一个要很小心的操作，少删一个多一份安全，之后再通过观察索引b的使用情况，确定没必要再删。interset确实一般都比较慢。
  5. 正常回滚很快的，是不是大事务回滚？这种还是得从消除大事务入手
  
  

• 
  Tony Du

  2019-01-02

   8

  对于上期问题的解答，有一点不是特别理解，
  因为order by desc，在索引c上向左遍历，对于（15， 25）这段区间没有问题，
  然后，扫描到c=10才停下来，理论上把（10，15]这个区间锁上就应该是完备的了呀。（5，10]这段区间是否锁上对结果应该没有影响呀，为什么会需要（5，10] 这段的next-key lock ?
  

  展开

  作者回复: 就是这么实现的😓
  
  C=10还是要锁的，如果不锁可能被删除

• 
  某、人

  2019-01-02

   4

  老师,我有几个问题:
  1.如果把order by去掉或者order by c asc,往右扫描,为什么没有加[25,30)next-key lock?
  2.执行session A,为什么slow log里的Rows_examined为2?按照答案来讲不应该是为3嘛
  3.thread states里sending data包括sending data to the client,
  另外还有一种state是Sending to client(5.7.8之前叫Writing to net)是writing a packet to the client.
  请问针对发送数据给客户端,这两种状态有什么区别？

  展开

  作者回复: 1. Next-key lock是前开后闭区间呀，有扫描到25，所以(20,25]
  
  2. Rows_examined 是server层统计的，这个不满足的值没返回给server
  
  3. 你show processlist 结果发我看下，代码中没搜到😓

• 
  Moby

  2019-01-21

   2

  丁奇老师好，不好意思，学渣看得比较慢。关于前两期的问题，我有一点没搞懂。就是你说的："session A 在 select 语句锁的范围是 1.... ; 2.在主键索引上id=10、15、20三个行锁”，经我测试(MySQL版本：5.7.17-log; 隔离级别：可重复读)：“session
  A: begin; select * from t where c>=15 and c<=20 order by c desc lock in share mode;"、"session B: update t set c=1010 where id=10; Query ok"、”session C: update t set c=1515 where id=15;block...“。即：为什么id=10这一行可以更新数据？而id=15、20这两行更新数据就被阻塞？

  展开

  作者回复: 这没问题呀
  
  begin; select * from t where c>=15 and c<=20 order by c desc lock in share mode;
  锁的范围是这样的：
  索引c上，next-key lock: (5，10],(10,15],(15,20];
  索引id上，行锁: id=15和id=20

• 
  Ryoma

  2019-01-03

   2

  我之前的描述有点问题，其实想问的是：为什么加了 order by c desc，第一个定位c=20 的行，会加上间隙锁 (20,25) 和 next-key lock (15,20]？
  
  如果没有order by c desc，第一次命中c=15时，只会加上next-key lock(10.15]；
  而有了order by c desc，我的理解是第一次命中c=20只需要加上next-key lock (15,20]
  
  当然最后(20,25)还是加上了锁，老师的结论是对的，我也测试过了，但是我不知道如何解释。
  唯一能想到的解释是order by c desc 并不会改变优化2这个原则：即等值查询时，会向右遍历且最后一个值不满足等值条件；同时order by c desc 带来一个类似于优化2的向左遍历原则。
  进而导致最后的锁范围是(5,25)；而没有order by c desc的范围是(10,25]。
  

  展开

  作者回复: 因为执行c=20的时候，由于要order by c desc, 就要先找到“最右边第一个c=20的行”，
  这个怎么找呢，只能向右找到25，才能知道它左边那个20是“最右的20”
  
  

• 
  Tony Du

  2019-01-03

   2

  对于上期问题的解答，有一点不是特别理解，
  因为order by desc，在索引c上向左遍历，对于（15， 25）这段区间没有问题，
  然后，扫描到c=10才停下来，理论上把（10，15]这个区间锁上就应该是完备的了呀。（5，10]这段区间是否锁上对结果应该没有影响呀，为什么会需要（5，10] 这段的next-key lock ?
  2019-01-02
   作者回复
  就是这么实现的😓
  
  C=10还是要锁的，如果不锁可能被删除
  
  我的回复：
  所以，如果把sql改成
  select * from t where c>=15 and c<=20 order by c asc lock in share mode;
  那锁的范围就应该是索引c上（10，25）了吧。
  同样查询条件，不同的order顺序，锁的范围不一样，稍微感觉有一点奇怪...
  

  展开

  作者回复: 嗯，因为执行索引遍历的顺序不一样，其实锁范围不一样也算合理啦😄

• 
  Long

  2019-01-02

   2

  老师好，看到有的同学在讨论锁的释放问题。
  
  之前分析过一个锁表异常，很多用workbench或者类似客户端的同学可能会遇到，
  复现方式：
  Step 1：显示的打开一个事务，或者把autocommit=0，或者mysql workbench中把自动提交的置灰按钮打开以后
  Step 2: 执行一个sql（比如，update或者delete之类的），然后sql还没有返回执行结果的中途点击workbench 自带的那个stop的红色的按钮。
  这个时候很多人可能就不再做其他操作，大多会认为执行已经结束了。但是实际上，锁还在继续锁着的并不会释放。
  
  系统日志记录：
  （1）processlist的状态是sleep，info为null
  （2）innodb_trx的状态是running，trx_query为null
  （3）performance_schema.events_statements_current表中的，
  sql_text，digest_text：是有正确的sql的。---这个5.6以后就有了，如果ps打开的话，应该是可以看到的。
  message_text ：Query execution was interrupted
  （4）inoodb_locks，lock_waits，以及show engine innodb status，只有出现锁等待的时候才会记录，如果只有一个事务的记录行锁，或者表锁，是不会记录的。（不知道是否有参考控制，还是默认的）
  （5）关于行锁记录数的问题，从测试的结果看，inoodb_trx的locked rows，当我点停止的时候，锁定行数保持不变了，当我继续点击执行的时候，锁定记录行数会在之前的记录上向上累加，并不是从0开始。
  
  然后查了audit log以后发现，客户端（mysqlworkbench）送给server端的是KILL QUERY thread_id，而不是Kill thread_id，
  所以MySQL只是终止了事务中的statement执行，但是并不会释放锁，因为目前的琐的获取和释放都是基于事务结束的（提交或者回滚）。
  这里面关于kill query/ thread_id的区别解释
  https://dev.mysql.com/doc/refman/5.6/en/kill.html
  
  解决方法：
  自己解决：kill 对应的thread_id，或者关闭执行窗口（这个时候会送个quit给server端）。
  别人解决：有super权限的人kill thread_id。
  
  关于kill的那个文章，其实对所有DDL，DML的操作释放过程，还没有全部搞清楚，期待老师的第25讲。

  展开

  作者回复: 总结的非常好，而且现象很全面。
  核心的一个点是：kill query 只是终止当前执行语句，并不会让事务回滚👍🏿

• 
  曾剑

  2019-01-02

   2

  老师，关于上期遗留问题的解答，我有一点疑惑：
  解答中的1中，第一个要定位的是索引 c 上“最右边的”c=20 的行，为啥只会加上间隙锁（20,25）和next-key lock(15,20]呢，为啥不是两个next-key lock(15,20]和(20,25]呢？25上的行锁难道是退化的？老师上一篇文章中说到加锁的基本原则中第一点是加锁的基本单位是next-key lock，而退化都是基于索引上的等值查询才会发生呀？盼老师指点迷津。
  

  展开

  作者回复: 就是优化2，找第一个值的时候是等值查询

• 
  devil

  2019-03-22

   1

  上期问题的答案有点不明白，为什么（5，10]会被加锁？第一个不满足条件的不是10吗？退化到间隙锁的情况也只会加锁（10，15],不退化应该是[10,15]
  
  另外主键索引到底锁了几行，上面一句写的锁了10，15，20。下面写的锁了15，20

  展开
• 
  不二

  2019-01-04

   1

  老师，曾剑同学的问题
  关于上期遗留问题的解答，我有一点疑惑：
  解答中的1中，第一个要定位的是索引 c 上“最右边的”c=20 的行，为啥只会加上间隙锁（20,25）和next-key lock(15,20]呢，为啥不是两个next-key lock(15,20]和(20,25]呢？25上的行锁难道是退化的？老师上一篇文章中说到加锁的基本原则中第一点是加锁的基本单位是next-key lock，而退化都是基于索引上的等值查询才会发生呀？盼老师指点迷津。
  您给回答是定位到c=20的时候，是等值查询，所以加的是(20,25)的间隙锁，25的行锁退化了，那么在上一期中的案例五：唯一索引范围锁 bug，那id<=15,不也是先定位到id=15，然后向右扫描，那应该也是等值查询，那么应该加的是（15，20）间隙锁，那为啥你说的加的是（15，20],为啥这个id=20的行锁也加上了呢，为啥同样是范围查询，一个行锁退化了，一个没有退化呢，求老师指点迷津
  
  

  展开

  作者回复: 1. 第一次就是找c=20,这个就是一次等值查找
  2. 案例5那个，等值查的是id=10,然后向右遍历。这两个，一个是有order by desc,索引的扫描方向不一样，“找第一个”的值也是不一样的

• 
  Ryoma

  2019-01-03

   1

  select * from t where c>=15 and c<=20 order by c desc lock in share mode; 这条语句在索引C上锁的范围是(5,25)；
  如果把order by c desc去掉，锁的范围即(10,25]；、
  请问老师为什么加上order by c desc后差异这么大，第一次定位到c=20的时候，为什么还要继续往后再查一次呢；而没有order by c desc后，第一次定位到10，就不会往前再查一次，这个差异是什么导致的？

  展开

  作者回复: 第一次是定位到15吧？不用往回去再找10了，第一次取的就是“最左边的15”了

• 
  龙江源

  2019-05-15

  

  老师，您好。看了您的专栏收获非常大，加锁实际上跟索引的遍历方式关系非常紧密，就是老师讲的用到哪里，锁到哪里。
  讲一下我试验过程中发现的几个例子：
  1.select * from t where c > 10 for update;
  这条SQL的锁范围，初看应该是索引C (10,∞]，主键索引id=15,20,25的行；但实验发现在sessionB不能插入(3,3,3)，和预期不一致；
  查看执行计划，发现mysql选择了使用主键索引遍历，所以锁定了所有行和间隙。（这个其实和d=5是一样的，只不过这个案例是有索引但是mysql没有选择使用）
  mysql> explain select * from t where c > 10 for update;
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  | 1 | SIMPLE | t1 | NULL | ALL | c | NULL | NULL | NULL | 6 | 50.00 | Using where |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)
  接下来又想了一个问题，这个时候索引C有没有被锁定？
  想到可以利用覆盖查询来实现只锁二级索引，所以执行select id from t where c=15 lock in share mode来验证猜想。
  mysql> select id from t1 where c=15 lock in share mode\g
  +----+
  | id |
  +----+
  | 15 |
  +----+
  可以执行成功，说明这个时候索引C是没有锁定的，符合“用到哪儿，锁到哪儿”这么一个原则。
  
  2.select * from t where c <= 10 for update;
  这条SQL的锁范围，我初看猜想应该是索引C(-∞,15)，主键索引id=0,5,10的行；
  但实际发现执行 update t set d=d+1 where c=15会被阻塞，经过实验，锁的范围是，索引C(-∞,15]，主键索引id=0,5,10的行；
  
  差别在于索引C c=15 这个点是闭区间还是开区间？
  
  我本来的想法是，c<10锁定(-∞,10]这个范围；c=10锁定(5,15)这个范围；然后合并一下，锁定(-∞,15)。
  
  结合老师今天答疑的分析，我有点想明白了，说说看我的理解哈，加锁是要结合实际执行过程来分析的，c<=10的执行方式应该是遍历索引C，直到找到一个大于10的值，所以加锁的过程应该如下：
  先找到索引C的第一个值0，锁定(-∞,0]；
  继续遍历下一个值5，锁定(0,5]；
  继续遍历下一个值10，锁定(5,10]；
  继续遍历下一个值15，锁定(10,15]；由于15大于10，终止遍历索引；
  所以最终锁定的范围是索引C (-∞,15]
  

  展开
• 
  zws

  2019-04-24

  

  老师，如果不是专业的dba看着专栏是不是有点太深了。 老师可不可以把文章分下类，哪部分可以适合业务开发人员看。

  展开

  作者回复: 业务开发基本都能用上的哈😆

• 
  Geek_6064d...

  2019-04-13

  

  老师您好，有一个问题不解，还望老师帮忙解答。就是在上期问题中，如果sessionA中的select语句是order by c asc的话，sessionB就不会阻塞，这是为什么呢？排序方式还会对加锁范围有影响吗？
• 
  唯她命

  2019-04-03

  

  索引c上，next-key lock: (5，10],(10,15],(15,20];
  索引id上，行锁: id=15和id=20
  
  为啥索引id 没有id=10的行锁？
• 
  Long

  2019-03-13

  

  
  【原始问题】2. 一个非常大（大几百万行）的表truncate，结果后面所有的线程都阻塞了，类似于下面这个MySQL bug的场景，结果就是等这个truncate结束。没有继续干预。
  https://bugs.mysql.com/bug.php?id=80060
  【老师回复】2. 嗯，80060这个问题是因为要truncate，所以要回收脏页导致慢，不过这个问题在5.5.23就优化掉了哦，看你使用的是5.6，可能是别的原因。truncate如果不是被锁，而是已经在执行了，确实还是别做别的事情，等结束最好；
  
  【再次更新】我今天做change的时候和别人解释的时候又翻了下这个记录，发现应该还是5.7以后的版本才有。
  80060这个bug最后的更新：
  Fixed as of the upcoming 5.7.18, 8.0.1 release。不过在87884这个bug中，5.7.18的同学又report了，MySQLbug工作人员让升级到5.7.19。:-(
  
  不过现在在删除超级大的全表，并且可能写入buffer_pool的话，如果有人想用truncate，一般都建议drop+create。

  展开
• 
  liao xueq...

  2019-03-13

  

  每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了，只会 write 到文件系统的 page cache 中就够了。
  
  单独拎出这句话的话，还是没能理解这意思。这句话的前提是

  展开
• 
  黄继立

  2019-03-02

  

  老师您好：
       首先要感谢您的分享，您以上的例子在我的线上环境都出现过。 一般情况都是慢sql 语句没有使用索引，我们所有线上的数据库，全部部署了实时kill 脚本，针对查询语句全部进行一个阀值的制定，例如是5秒，超过以后自动kill，这样会保证线上的稳定。 二就是在测试环境严格把控没有使用索引的语句。

  展开

  作者回复: 👍
  
  尤其是第二点，可以在测试环境就发现问题，是最好

• 
  念你如昔

  2019-03-01

  

  为什么会扫描到c=10呢？10应该是开区间啊，即使是倒序，为什么要给10这一行加锁呢