MySQL 主从延迟的常见原因及解决方法
承蒙大家的支持，刚上市的《MySQL实战》已经跃居京东自营数据库图书热卖榜第 1 名，收到的反馈也普遍不错。对该书感兴趣的童鞋可通过右边的链接购买。目前，京东自营有活动，只需 5 折。主从延迟作为 MySQL 的痛点已经存在很多年了，以至于大家都有一种错觉：有 MySQL 复制的地方就有主从延迟。
对于主从延迟的原因，很多人将之归结为从库的单线程重放。
但实际上，这个说法比较片面，因为很多场景，并行复制方案也解决不了，譬如从库 SQL 线程被阻塞了，从库磁盘 IO 存在瓶颈等。
很多童鞋在分析此类问题时缺乏一个系统的方法论，以致无法准确地定位出主从延迟的根本原因。
下面就如何分析主从延迟做一个系统、全面的总结。
本文主要包括以下两方面的内容。
[*]如何分析主从延迟。
[*]主从延迟的常见原因及解决方法。
下一篇文章会介绍如何监控主从延迟，包括如何解读 Seconds_Behind_Master、Seconds_Behind_Master 的局限性、pt-heartbeat 及 MySQL 8.0 原生的解决方案，敬请留意。
如何分析主从延迟
分析主从延迟一般会采集以下三类信息。
从库服务器的负载情况
为什么要首先查看服务器的负载情况呢？因为软件层面的所有操作都需要系统资源来支撑。
常见的系统资源有四类：CPU、内存、IO、网络。对于主从延迟，一般会重点关注 CPU 和 IO 。
分析 CPU 是否达到瓶颈，常用的命令是 top，通过 top 我们可以直观地看到主机的 CPU 使用情况。以下是 top 中 CPU 相关的输出。
Cpu(s): 0.2%us, 0.2%sy, 0.0%ni, 99.5%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st下面我们看看各个指标的具体含义。
[*]us：处理用户态（ user ）任务的 CPU 时间占比。
[*]sy：处理内核态（ system ）任务的 CPU 时间占比。
[*]ni：处理低优先级进程用户态任务的 CPU 时间占比。
进程的优先级由 nice 值决定，nine 的范围是 -20 ～ 19 ，值越大，优先级越低。其中，1 ～ 19 称之为低优先级。
[*]id：处于空闲状态（ idle ）的 CPU 时间占比。
[*]wa：等待 IO 的 CPU 时间占比。
[*]hi：处理硬中断（ irq ）的 CPU 时间占比。
[*]si：处理软中断（ softirq ）的 CPU 使用率。
[*]st：当系统运行在虚拟机中的时候，被其它虚拟机占用（ steal ）的 CPU 时间占比。
一般来说，当 CPU 使用率 （ 1 - 处于空闲状态的 CPU 时间占比 ）超过 90% 时，需引起足够关注。毕竟，对于数据库应用来说，CPU 很少是瓶颈，除非有大量的慢 SQL 。
接下来看看 IO。
查看磁盘 IO 负载情况，常用的命令是 iostat 。
# iostat -xm 1
avg-cpu: %user %nice %system %iowait %steal %idle
4.21 0.00 1.77 0.35 0.00 93.67
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 841.00 3234.00 13.14 38.96 26.19 0.60 0.15 0.30 0.11 0.08 32.60命令中指定了 3 个选项，其中，
[*]-x：打印扩展信息。
[*]-m：指定吞吐量的单位是 MB/s ，默认是 KB/s 。
[*]1：每隔 1s 打印一次。
下面看看输出中各指标的具体含义。
[*]rrqm/s：每秒被合并的读请求的数量。
[*]wrqm/s：每秒被合并的写请求的数量。
[*]r/s：每秒发送给磁盘的读请求的数量。
[*]w/s：每秒写入磁盘的写请求的数量。注意，这里的请求是合并后的请求。r/s + w/s 等于 IOPS 。
[*]rMB/s：每秒从磁盘读取的数据量。
[*]wMB/s：每秒写入磁盘的数据量。rMB/s + wMB/s 等于吞吐量。
[*]avgrq-sz：I/O 请求的平均大小，单位是扇区，扇区的大小是 512 字节。一般而言，I/O 请求越大，耗时越长。
[*]avgqu-sz：队列里的平均 I/O 请求数量。
[*]await：I/O 请求的平均耗时，包括磁盘的实际处理时间及队列中的等待时间，单位 ms 。
其中，r_await 是读请求的平均耗时，w_await 是写请求的平均耗时。
[*]svctm：I/O 请求的平均服务时间，单位 ms 。注意，这个指标已弃用，在后续版本会移除。
[*]%util：磁盘饱和度。反映了一个采样周期内，有多少时间在做 I/O 操作。
一般来说，我们会重点关注 await 和 %util。
对于只能串行处理 I/O 请求的设备来说，%util 接近 100% ，就意味着设备饱和。但对于 RAID、SSD 等设备，因为它能并行处理，故该值参考意义不大，即使达到了 100% ，也不意味着设备出现了饱和。至于是否达到了性能上限，需参考性能压测下的 IOPS 和吞吐量。
主从复制状态
对于主库，执行 SHOW MASTER STATUS 。
mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000004 | 1631495 | | | bd6b3216-04d6-11ec-b76f-000c292c1f7b:1-5588 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)SHOW MASTER STATUS 的输出中重点关注 File 和 Position 这两个指标的值。
对于从库，执行 SHOW SLAVE STATUS 。
mysql> show slave status\G
*************************** 1. row ***************************
...
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1631495
...
Relay_Master_Log_File: mysql-bin.000004
...
Exec_Master_Log_Pos: 1631495
...SHOW SLAVE STATUS 的输出中重点关注 Master_Log_File，Read_Master_Log_Pos，Relay_Master_Log_File，Exec_Master_Log_Pos 这四个指标的值。
接下来，重点比较以下两对值。
第一对：( File , Position ) & ( Master_Log_File , Read_Master_Log_Pos )
这里面，
[*]( File , Position ) 记录了主库 binlog 的位置。
[*]( Master_Log_File , Read_Master_Log_Pos ) 记录了 IO 线程当前正在接收的二进制日志事件在主库 binlog 中的位置。
如果 ( File , Position ) 大于 ( Master_Log_File , Read_Master_Log_Pos ) ，则意味着 IO 线程存在延迟。
第二对：( Master_Log_File , Read_Master_Log_Pos ) & ( Relay_Master_Log_File , Exec_Master_Log_Pos )
这里面，( Relay_Master_Log_File, Exec_Master_Log_Pos ) 记录了 SQL 线程当前正在重放的二进制日志事件在主库 binlog 的位置。
如果 ( Relay_Master_Log_File, Exec_Master_Log_Pos ) < ( Master_Log_File, Read_Master_Log_Pos ) ，则意味着 SQL 线程存在延迟。
主库 binlog 的写入量
主要是看主库 binlog 的生成速度，比如多少分钟生成一个。
主从延迟的常见原因及解决方法
下面分别从 IO 线程和 SQL 线程这两个方面展开介绍。
IO 线程存在延迟
下面看看 IO 线程出现延迟的常见原因及解决方法。
[*]网络延迟。
判断是否为网络带宽限制。如果是，可开启 slave_compressed_protocol 参数，启用 binlog 的压缩传输。或者从 MySQL 8.0.20 开始，通过 binlog_transaction_compression 参数开启 binlog 事务压缩。
[*]磁盘 IO 存在瓶颈 。
可调整从库的双一设置或关闭 binlog。
注意，在 MySQL 5.6 中，如果开启了 GTID ，则会强制要求开启 binlog ，MySQL 5.7 无此限制。
[*]网卡存在问题。
这种情况不多见，但确实碰到过。当时是一主两从的架构，发现一台主机上的所有从库都延迟了，但这些从库对应集群的其它从库却没有延迟，后来通过 scp 远程拷贝文件进一步确认了该台主机的网络存在问题，最后经系统组确认，网卡存在问题。
一般情况下，IO 线程很少存在延迟。
SQL 线程存在延迟
下面看看 SQL 线程出现延迟的常见原因及解决方法。
主库写入量过大，SQL 线程单线程重放
具体体现如下：
[*]从库磁盘 IO 无明显瓶颈。
[*]Relay_Master_Log_File , Exec_Master_Log_Pos 也在不断变化。
[*]主库写入量过大。如果磁盘使用的是 SATA SSD，当 binlog 的生成速度快于 5 分钟一个时，从库重放就会有瓶颈。
这个是 MySQL 软件层面的硬伤。要解决该问题，可开启 MySQL 5.7 引入的基于 LOGICAL_CLOCK 的并行复制。
关于 MySQL 并行复制方案，可参考：MySQL 并行复制方案演进历史及原理分析
STATEMENT 格式下的慢 SQL
具体体现，在一段时间内 Relay_Master_Log_File , Exec_Master_Log_Pos 没有变化。
看下面这个示例，对 1 张千万数据的表进行 DELETE 操作，表上没有任何索引，在主库上执行用了 7.52s，观察从库的 Seconds_Behind_Master，发现它最大达到了 7s 。
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> select count(*) from sbtest.sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.41 sec)
mysql> show create table sbtest.sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int NOT NULL,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> delete from sbtest.sbtest1 where id <= 100;
Query OK, 100 rows affected (7.52 sec)磁盘 IO 存在瓶颈
这个时候可调整从库的双一设置或关闭 binlog。
总结
综合上面的分析，主从延迟的常见原因及解决方法如下图所示。
来源:https://www.cnblogs.com/ivictor/p/17331981.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]