最近才发生了个案列: 由于一个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这个状态.是之前别人问到的,我也挺懵

不是专业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的作用）。 请老师帮忙看下上面几次故障是否有更好，更专业的解决方案。多谢

我之前的描述有点问题，其实想问的是：为什么加了 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]。

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

老师好，看到有的同学在讨论锁的释放问题。 之前分析过一个锁表异常，很多用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讲。

分享一个主从切换时遇到的问题，主从切换前主库要改为只读，设置只读后，show master status发现binlog一直在变化，当时应用没断开。 主库并不是其他库的从库，怎么搞的呢？ 检查业务用户权限发现拥有super权限，查看授权语句原来是grant all on *.* to user，这里要说的是*.* 权限就太大了，而且这个也很容易被误解，需要特别注意。

对于上期问题的解答，有一点不是特别理解， 因为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顺序，锁的范围不一样，稍微感觉有一点奇怪...

qps(查询语句)突然增大的情况，我们的实践是: 1. 账号、接口级别的限流。 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这两行更新数据就被阻塞？

看完了《算法导论》那本书的前20章，看到了动态规划。再来看老师的专栏，发现我终于可以无障碍get到本专栏的知识了。