关于mysql:技术分享-Update更新慢死锁等问题的排查思路分享

欢送来到 GreatSQL 社区分享的 MySQL 技术文章，如有疑难或想学习的内容，能够在下方评论区留言，看到后会进行解答

在开始排错之前咱们须要晓得 Update 在 MySQL 中的生命周期是什么，MySQL 如何执行一个事务的。

了解了如何执行，咱们才晓得如何去排查故障。

Server 层阶段

客户端发动一个 TCP 申请后，MySQL Server 端会负责通信协议解决、线程解决、账号认证、安全检查。

MySQL Server 端对一个 SQL 申请进行词法剖析（辨认 select、from），而后会对语法进行分析判断语法是否正确。

优化器会剖析 SQL 语句，抉择适合的索引，依据预后果集判断是否应用全表扫描。

InnoDB 引擎层阶段

1）申请进入 InnoDB 引擎后，首先判断该事务波及到的数据页是否在 BP 中，不存在则会从磁盘中加载此事务波及的数据页到 BP 缓冲池中，并对相应的索引数据页加锁

思考？

数据是如何从磁盘加载到 BP 中的?
BP 中的新老生代是如何交替及回收?
如何对相应数据加?

解答：

通过 B+Tree 读取到磁盘的索引页加载到 BP 缓冲池中。

1、通过 space id 和 page no 哈希计算之后把索引页加载到指定的 buffer pool instance 中。2、判断 Free List 是否有闲暇页可用（innodb_buffer_pool_pages_free、innodb_buffer_pool_wait_free），没有 则淘汰脏页或 LRU List 的 old。3、将数据页加载到 Free List 中，而后加载到 LRU List 的 old 区的 midpoint（头部）。4、通过二分查找法，找该页对应的记录，试图给该事物波及到的行记录加上排他锁。(1) 判断该事物以后记录的行锁被其余事物占用的话，须要进入锁期待。(2) 进入锁期待后，同时判断会不会因为本人的退出导致了死锁。(3) 检测到没有锁期待和不会造成死锁后，行记录加上排他锁。

2）将批改前的数据写入到 Undo 中，批改后将回滚针执行 Undo log 中批改前的行

思考？

为什么要写 Undo 日志?
Undo 的存储形式是什么?\

解答：

Undo log 个别是逻辑日志，记录每行记录。有两个作用：提供回滚和 MVCC。

 事务因为某些起因须要回滚时，能够借助 Undo 日志进行回滚，保障事务的一致性 在事务的不同隔离级别须要通过 Undo log 实现。当读取某一行加锁的数据时，能够通过 Undo log 实现（比 如：RR 级别），事务不完结，Undo log 就不删除

Undo log 的存储形式是用段（segment）记录在表空间中。

InnoDB 存储引擎对 Undo 采纳段形式治理，rollack segment 称为回滚段，每个回滚段有 1024 个。Undo log segment，5.6 之后能够通过 innodb_undo_logs 自定义多少个回滚段，默认 128 个。Undo log 默认存储在共享表空间中，开启了 innodb_file_per_table 将存在独立表空间中。

3）写 redo log buffer 在 BP 中对数据进行批改操作，并将批改后的值写入到 redo log buffer 中期待异步 sync 到磁盘

思考？

什么时候写入 redo log buffer？
commit 后 log buffer 如何落盘到 redo log？
日志刷盘规定是什么？

解答：

什么时候写入 redo log buffer。

1、先通过状态值 Innodb_log_waits 判断 redo log buffer 是否够用，不够用就期待。2、在 BP 缓冲池的 LRU List 中 old 区的 midpont 中对改数据页的行记录的字段值做更新操作。3、把批改之后的字段值写入到 redo log buffer 中，并给 LSN 加上以后 redo log 写入的长度（写入长度为 length 的 redo log，LSN 就会加上 length）。4、因为 redo group commit，事务所产生的 redo log buffer 可能会和其余事务一起 flush 并且 sync 到磁盘上。5、字段值在 BP 缓冲池更新胜利后，对应的数据页就是脏页。

什么时候落盘到 redo log 中。

1、每次会将 log buffer 中的日志写入到 log file（这里指 os buffer），而后在调用零碎的 fsync 操作进行落盘。在 commit 之后，通过 innodb_flush_log_at_trx_commit 来决定什么时候将 log buffer 刷盘。2、值为 1（默认为 1）：事务每次提交会写入 log buffer--> 而后写入 os buffer---> 调用零碎 fsync 刷到 log file on disk。3、值为 0：事务提交是先写入 log buffer--> 每秒写入 os buffer 并调用 fsync 落盘（最多失落 1s 数据）。4、值为 2：每次提交只写入 os buffer，而后每秒调用 fsync() 将 os buffer 的日志写入到 log file on disk（最多失落 1s 数据）。

日志刷盘规定。

 默认状况下事务每次提交会刷盘，是因为 innodb_flush_log_at_trx_commit 的值为 1。这只是 InnoDB 在有 commit 动作后才会将日志刷盘，属于 InnoDB 存储引擎刷盘规定之一。日志刷盘的几种规定：1、收回 commit 动作之后。由 innodb_flush_log_at_trx_commit 管制。2、每秒刷一次，刷新频率由 innodb_flush_log_at_timeout 值决定，默认为 1，刷日志频率与 commit 动作无关。3、当 log buffer 中应用内存超过一半。4、当有 checkpoint 时，checkpoint（数据页刷盘）在肯定水平上代表刷盘时日志所处的 LSN 地位。

Checkpoint 刷盘规定。

InnoDB 中，数据刷盘的规定只有一个：checkpoint，触发 checkpoint 后，会将 BP 中脏数据和脏日志页都刷新到磁盘。Checkpoint 分为两种：Sharp checkpoint：在重用 redo log 文件时（切割日志），将所有记录到 redo log 中对应的脏数据刷新到磁盘。Fuzzy checkpoint：一次刷新一小部分日志到磁盘，并非所有脏日志。1、master thread checkpoint：master 线程每秒或每 10 秒刷肯定比例脏页到磁盘。2、Flush_lru_list checkpoint：5.6 之后通过 innodb_page_cleaners 变量指定 page cleaner 线程个数。3、Async/sync/ flush checkpoint，同步刷盘还是异步刷盘。4、Dirty page too much checkpoint：脏页怠惰强制触发检查点，保障缓存空间闲暇，由变量 innodb_max_dirty_pages_pct 管制。

4）写 binlog cache

同时将批改的信息依照 event 格局记录到 binlog_cache 中，期待落盘。

如果 binlog cache 不够用时，会写入到 binlog 临时文件。

思考？

事务 binlog event 的写入流程是什么？

解答：

一旦有事务提交，binlog cache 和 binlog 临时文件都会开释（曾经写入 binlog file）同一事务蕴含多个 DML 会共用同一个 binlog cache 和 binlog 临时文件。

1、事务开启。2、执行 dml 语句，dml 语句第一次执行时会调配 binlog cache。3、执行 dml 语句期间生成的 event 一直写入 binlog cache。4、binlog cache 满了事务还没执行完，会将 binlog cache 中的数据写入到 binlog 临时文件同时清空 binlog cache，临时文件大小大于 max_binlog_cache_size 则报 error 1197。5、事务提交，整个 binlog cache 和 binlog 临时文件数据全副写入 binlog file，开释 binlog cache（IO_CACHE）和 binlog 临时文件 binlog 临时文件大小为 0，保留文件描述符。6、断开连接，开释 IO_CACHE。

5）写 change buffer

如果这个事务须要在二级索引上做批改，写入到 change buffer page 中，期待之后，事务须要读取该二级索引时进行 merge。

思考？

什么时候会用到 change buffer？
为什么仅实用于一般索引页？
哪些场景会触发刷新 change buffer？
什么业务不适宜 / 适宜开启 change buffer？
change buffer 相干参数有哪些？

解答：

什么时候会用到 change buffer。

MySQL 5.5 之前叫 insert buffer，只针对 insert，之后叫 change buffer 对 delete 和 Update 也无效。

 在对一般索引数据页不在 BP 中，对页进行写操作，不会将磁盘数据加载到缓冲池中，仅仅记录缓冲变更（能够了解为只记录操作变更，不做实在数据操作）。期待数据被读取时，将数据 merge 到 BP 中，目标是升高写操作磁盘 IO，进步性能。

为什么仅实用于一般索引页。

 惟一索引或主键索引每次批改操作时，InnoDB 必须进行唯一性查看。即便索引页不在缓冲池，也会去读取磁盘页，一次随机 IO（通过 B+tree 查找数据页），一次程序 IO（写 redo log）防止不了。

那些场景会触发刷新 change buffer。

1、数据页被拜访。2、master thread 每隔 10s 会进行操作。3、数据库 BP 不够用时。4、数据库失常敞开时。5、redo log 写满时（简直不会呈现，redo log 被写满数据库处于无奈写入状态）。

什么业务不适宜 / 适宜开启 change buffer。

 不适宜：1、数据库都是惟一索引。2、写入一个数据后，立即读取上述场景，在写操作之后，原本就要进入 BP 中，此时 change buffer 反而成了累赘。适宜：1、数据库大部分是非惟一索引。2、业务是写多读少，或者写后不是立即读。3、读写拆散下主库能够应用。能够应用 change buffer，缩小一次随机 IO，优化定期批量写磁盘。

change buffer 相干参数有哪些。

show global variables like '%innodb_change_buffer%';

innodb_change_buffer_max_size；配置写缓冲的大小，占整个缓冲池的比例，默认值是 25%，最大值是 50%（写多读少才需调大，读多写少 25% 就够）。innodb_change_buffering;

配置那些写操作启用写缓冲，能够设置成 all/none/inserts/deletes 等。

关上 binlog 选项之后，执行事务提交会进入二阶段提交模式（prepare 阶段和 commit 阶段。

两阶段波及两个参数（sync_binlog 和 innodb_flush_log_at_trx_commit）。

1）事务提交分为 prepare 阶段与 commit 阶段（两阶段提交）

 事务的 commit 操作在存储引擎和 server 层采纳外部 XA。两阶段提交协定保障事务的一致性，次要保障 redo log 和 binlog 的原子性。

2）Redo log prepare

 写入 redo log 处于 prepare 阶段，并且写入事务的 xid。将 redo log buffer 刷新到 redo log 磁盘文件中，用于解体复原。刷盘的形式由 innodb_flush_log_at_trx_commit 决定。

3）Binlog write&fync: 执行器把 binlog cache 里的残缺事务和 redo log prepare 中的 xid 写入到 binlog 中

Dump 线程会从 binlog cache 里把 event 被动发送给 slave 的 I/O 线程，同时执行 fsync 刷盘（大事务的话比 较耗时）并清空 binlog cache。Binlog 刷盘的形式由 sync_binlog 决定。binlog 写入实现，事务就算胜利。总结：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写入到 binlog file 中。当 sync_binlog 为 1 时，binlog 落盘后才会告诉 dump thread 进行主从复制。

4）Redo log commit commit

 提交阶段中，该事务产生的 redo log 曾经 sync 到磁盘中，在 redo log 里标记 commit，阐明事务提交胜利。

5）事务提交，开释行记录持有的排它锁

6）Binlog 和 redo log 落盘后触发刷新脏页操作

 先把该脏页复制到 doublewrite buffer 里，再把 dobulewrite buffer 里的数据，刷新到共享表空间 (ibdata)，而后脏页刷新到磁盘中，此时内存页和数据页统一。

思考？

BP 中的脏页刷盘机制是什么？

解答：

当 InnoDB 中脏页比例超过 innodh_max_dirty_pages_pct_lwm 的值时，开始刷盘。

因为零碎异样或显示回滚，所有数据变更会变成原来的，通过回滚日志中数据进行复原。

对于 in-place（原地）更新，将数据回滚到最老版本；对于 delete+insert 形式，标记删除的记录清理删除标记，同时把插入的汇集索引和二级索引记录也会删除。

1、RR 模式下 insert 锁期待 gap lock 锁期待导致。2、Insert 期待 MDL 锁导致。3、Table lock。

1、慢 sql 导致 io 高。2、其余程序占用比价高。3、BP 命中率比拟低。4、并发导致。5、innodb buffer pool 不够用。6、Update、delete 更新数据行数大（>W）。

1、redo log buffer 是否够用通过 Innodb_log_waits 确认。2、Redo log buffer 刷盘形式通过 innodb_flush_log_at_trx_commit。3、Binlog cache 是否够用，创立临时文件、耗费 IO。4、Change buffer 是否够用。

1、sync_binlog 参数。2、binlog_group_commit_sync_delay 参数。3、innodb_flush_commit 参数。4、查看 innodb_buffer_pool 的命中率, 查看脏页刷新频率成果。

 如果 CPU 高、IO 高、wa 大：先排查慢 SQL，再查以后并发数，个别是大量并发慢 SQL 导致。如果 CPU 高、IO 中、wa 小：排查慢 SQL，在查看以后并发数，个别是单个计算 SQL 导致。如果 CPU 低、IO 高、wa 低：排查以后占用 io 高的线程，有可能是 page clean 导致或日志刷新频繁导致。

 查看 mysql porcesslist，查看以后是否有 wait lock（表锁，行锁，meata lock 等）。查看 mysql processlist，是否有大量 send data、init、commit、clean up 状态。查看 mysql processlist，计算并发，排查是否有并发压力。查看 innodb buffer pool 命中率，排查 buffer 是否够用。查看 mysql tmp，是否够用，open tables 是否等于 table_open_cache。

 通过 explain 剖析 SQL 的执行状况，是否走索引，是否存在 union。通过 explain 剖析 SQL 的执行状况，是否存在大表驱动小表，多表 join。查看 SQL 是否存在产生额定长期表。应用 profile 剖析单条 SQL 语句。

 察看是单个 SQL 执行慢，还是所有语句都慢。慢的 SQL 的工夫是否有法则，有助于排查 MysSQL 的相干参数。

 应用 tcpdump 进行抓包，剖析是 MySQL 返回慢，还是网络慢。应用 strace 剖析 MySQL 外部哪里慢，哪个函数导致的。

1、Update 全表更新一个字段，数据量为 10w，更新特地慢。

2、Update 引起死锁问题。

3、Update 几百条数据耗费了 10s。

4、Update 同一个表，有些更新快，有些更新慢。

Update 的问题还不止于此，通过浏览本篇文章，置信您对如何发现、排查、解决 Update 可能引发的问题，有了更进一步的意识。

在本大节中，列举了局部常见的案例，心愿对您学习有所帮忙。此外，也举荐您本人入手搭建测试环境，发现新的问题。

Enjoy GreatSQL :)

技术分享 | MGR 最佳实际 (MGR Best Practice)
https://mp.weixin.qq.com/s/66…

技术分享 | 万里数据库 MGR Bug 修复之路
https://mp.weixin.qq.com/s/Ia…

Macos 零碎编译 percona 及局部函数在 Macos 零碎上运算差别
https://mp.weixin.qq.com/s/jA…

技术分享 | 利用 systemd 治理 MySQL 单机多实例
https://mp.weixin.qq.com/s/iJ…

产品 | GreatSQL，打造更好的 MGR 生态
https://mp.weixin.qq.com/s/By…

产品 | GreatSQL MGR 优化参考
https://mp.weixin.qq.com/s/5m…

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

Gitee:
https://gitee.com/GreatSQL/Gr…

GitHub:
https://github.com/GreatSQL/G…

微信 &QQ 群：

可搜寻增加 GreatSQL 社区助手微信好友，发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群：533341697
微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！

关于mysql:技术分享-Update更新慢死锁等问题的排查思路分享

一、简介

二、Update 生命周期

2.1 连接器

2.2 分析器

2.3 优化器

2.4 执行器

2.4.1 事务执行阶段

2.4.2 事务提交阶段

2.4.3 假如事务 ROLLBACK

三、影响事务提交提早的几种状况

在事务执行阶段：

1、锁期待

2、IO 方面

3、Buffer 方面

4、落盘提早

四、Update 更新慢的排查思路

排查思路:

1、查看过后实例零碎性能状况（IO、CPU、memory），排除零碎性能烦扰

2、查看 MySQL 状态

3、剖析 SQL 语句

4、剖析应用程序执行 SQL 慢的工夫

5、抓包及 strace 剖析

五、常见问题

文章举荐：

对于 GreatSQL

Just My Socks（注册教程内含优惠码）

关于mysql:技术分享-Update更新慢死锁等问题的排查思路分享

一、简介

二、Update 生命周期

2.1 连接器

2.2 分析器

2.3 优化器

2.4 执行器

2.4.1 事务执行阶段

2.4.2 事务提交阶段

2.4.3 假如事务 ROLLBACK

三、影响事务提交提早的几种状况

在事务执行阶段：

1、锁期待

2、IO 方面

3、Buffer 方面

4、落盘提早

四、Update 更新慢的排查思路

排查思路:

1、查看过后实例零碎性能状况（IO、CPU、memory），排除零碎性能烦扰

2、查看 MySQL 状态

3、剖析 SQL 语句

4、剖析应用程序执行 SQL 慢的工夫

5、抓包及 strace 剖析

五、常见问题

文章举荐：

对于 GreatSQL

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）