技术分享 | Update更新慢、死锁等问题的排查思路分享

Bertha 。 2022-09-11 13:18 235阅读 0赞

本文来源：原创投稿

\* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

# 内容提纲 #

一、简介

二、Update 生命周期

*  2.1 连接器
 *  2.2 分析器
 *  2.3 优化器
 *  2.4 执行器
    
     *  2.4.1 事务执行阶段
     *  2.4.2 事务提交阶段
     *  2.4.3 假设事务 ROLLBACK

三、影响事务提交延迟的几种情况

四、Update 更新慢的排查思路

五、常见问题

# 一、简介    #

在开始排错之前我们需要知道 Update 在 MySQL 中的生命周期是什么，MySQL 如何执行一个事务的。

理解了如何执行，我们才知道如何去排查故障。

# 二、Update 生命周期 #

## Server 层阶段 ##

### 2.1 连接器 ###

客户端发起一个 TCP 请求后，MySQL Server 端会负责通信协议处理、线程处理、账号认证、安全检查。

### 2.2 分析器 ###

MySQL Server 端对一个 SQL 请求进行词法分析（识别 select、from），然后会对语法 进行分析判断语法是否正确。

### 2.3 优化器 ###

优化器会分析 SQL 语句，选择合适的索引，根据预结果集判断是否使用全表扫描。

### 2.4 执行器 ###

#### InnoDB 引擎层阶段 ####

#### 2.4.1 事务执行阶段 ####

##### 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 等。

#### 2.4.2 事务提交阶段 ####

打开 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 的值时，开始刷盘。

#### 2.4.3 假设事务 ROLLBACK ####

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

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

# 三、影响事务提交延迟的几种情况 #

## 在事务执行阶段： ##

### 1、锁等待 ###

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

### 2、IO 方面 ###

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

### 3、Buffer 方面 ###

> 1、redo log buffer 是否够用通过 Innodb\_log\_waits 确认。
> 
> 2、Redo log buffer 刷盘方式通过 innodb\_flush\_log\_at\_trx\_commit。
> 
> 3、Binlog cache 是否够用，创建临时文件、消耗 IO。
> 
> 4、Change buffer 是否够用。

### 4、落盘延迟 ###

> 1、sync\_binlog 参数。
> 
> 2、binlog\_group\_commit\_sync\_delay 参数。
> 
> 3、innodb\_flush\_commit 参数。
> 
> 4、查看 innodb\_buffer\_pool 的命中率,查看脏页刷新频率效果。

# 四、Update 更新慢的排查思路 #

## 排查思路: ##

### 1、查看当时实例系统性能情况（IO、CPU、memory），排除系统性能干扰 ###

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

### 2、检查 MySQL状态 ###

> 查看 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。

### 3、分析 SQL 语句 ###

> 通过 explain 分析 SQL 的执行情况，是否走索引，是否存在 union。
> 
> 通过 explain 分析 SQL 的执行情况，是否存在大表驱动小表，多表 join。
> 
> 检查 SQL 是否存在产生额外临时表。
> 
> 使用 profile 分析单条 SQL 语句。

### 4、分析应用程序执行 SQL 慢的时间 ###

> 观察是单个 SQL 执行慢，还是所有语句都慢。
> 
> 慢的 SQL 的时间是否有规律，有助于排查 MysSQL 的相关参数。

### 5、抓包及 strace 分析 ###

> 使用 tcpdump 进行抓包，分析是 MySQL 返回慢，还是网络慢。
> 
> 使用 strace 分析 MySQL 内部哪里慢，哪个函数导致的。

# 五、常见问题 #

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

2、Update 引起死锁问题。

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

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

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

在本小节中，列举了部分常见的案例，希望对您学习有所帮助。此外，也推荐您自己动手搭建测试环境，发现新的问题。

Enjoy GreatSQL :)

--------------------

**文章推荐：**

*  [MGR最佳实践(MGR Best Practice)][MGR_MGR Best Practice]
 *  [万里数据库MGR Bug修复之路][MGR Bug]
 *  [《叶问》36期，MySQL最多只能用到128个逻辑CPU，是真的吗][36_MySQL_128_CPU]
 *  [GreatSQL重磅特性，InnoDB并行并行查询优化测试][GreatSQL_InnoDB]
 *  [一周碎碎念，2021.8.8，正在筹备MGR专题课程][2021.8.8_MGR]
 *  [金融应用场景下跨数据中心的MGR架构方案][MGR]  
    
 *  [InnoDB Cluster+GreatSQL快速部署MGR集群，并实现读写分离和故障自动转移][InnoDB Cluster_GreatSQL_MGR]
 *  [利用GreatSQL部署MGR集群，并完成添加新节点 、滚动升级、切主][GreatSQL_MGR_]
 *  [《叶问》33期，MGR最佳配置参考，PFS里的监测指标要全开吗，mysqld进程占用内存过高怎么排查][33_MGR_PFS_mysqld]

--------------------

扫码加入GreatSQL/MGR交流**QQ群**

![832b86154985ffdd340b51f04ab866a6.png][]

点击文末“**阅读原文**”直达老叶专栏

[MGR_MGR Best Practice]: http://mp.weixin.qq.com/s?__biz=MzkzMTIzMDgwMg==&mid=2247484174&idx=1&sn=4f795e889e3778876b09143c4ffdc770&chksm=c26f6633f518ef25f19309e4b01ce5d4527d945a2a030b66a9d9bbaa598cb668eca30cb91a0f&scene=21#wechat_redirect
[MGR Bug]: http://mp.weixin.qq.com/s?__biz=MzkzMTIzMDgwMg==&mid=2247483798&idx=1&sn=21fc1b1797074e002e1c684263b731e8&chksm=c26f64abf518edbdb278f1c21e98e39dc73b78397d01c2d583450a1da6d72a4ff32a0ec6db9c&scene=21#wechat_redirect
[36_MySQL_128_CPU]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653936499&idx=1&sn=f2efa2f3ae920b74039b6a9afa820238&chksm=bd3b41198a4cc80f18e90a0ad1b5550efbf20791e9512cd6c43dec9a0689da0d54fab5754749&scene=21#wechat_redirect
[GreatSQL_InnoDB]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653936494&idx=1&sn=d3f96046483d991f4fae46db3d2ee618&chksm=bd3b41048a4cc8127aa2e87146b35d610f57188971e3f5bcf587cb2ac2faecd5f192f9c72653&scene=21#wechat_redirect
[2021.8.8_MGR]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653936226&idx=1&sn=023012d2d287607c8fc8813c42b7ec73&chksm=bd3b40088a4cc91eff534623ce915f646e5dc3a904ae79e5df5ff303b59390e36e39268344ef&scene=21#wechat_redirect
[MGR]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653935994&idx=1&sn=91c596c81944aabd396d33f4924aac26&chksm=bd3b43108a4cca06841d2adf3a63dfe6af99df09b09164e8676d99cb08fc5e569bfc543a0ccf&scene=21#wechat_redirect
[InnoDB Cluster_GreatSQL_MGR]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653935962&idx=1&sn=38f2acfb147f16ae8daaa6a48a3102ae&chksm=bd3b43308a4cca26c2db9912a9ce5e2d73df1a10e7415f83538742b92e5475a0b11b88e39f4c&scene=21#wechat_redirect
[GreatSQL_MGR_]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653935951&idx=1&sn=ec8794371cd6f7677a5355b7e7ac39ea&chksm=bd3b43258a4cca336b0260c6b9cfc363f10596c775bdd0408d89dca855f3c7949911aa50ec8f&scene=21#wechat_redirect
[33_MGR_PFS_mysqld]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653935873&idx=1&sn=7511853d63145b7d72ae533586b6ad81&chksm=bd3b436b8a4cca7ddf138db1d2342aa19f0452bff97beb2f5015cf0eb377a7d2a6899d1c3c05&scene=21#wechat_redirect
[832b86154985ffdd340b51f04ab866a6.png]: /images/20220828/0eea5454607e407ea943b1d88b4afb7c.png