MySQL主从同步延时高问题排查

背景

环境: 基于GTID搭建的主从mysql数据库,版本5.7.32,服务器配置8C 16G 500G数据盘<SSD盘> 现象: 在最近这两天,prometheus监控告警频发mysql从库,主从同步延时过高,配置的延时阈值是300秒,通过监控和在从数据库服务器上执行show slave status\G命令后,发现主从延时高达4784150秒,这严重的超过了阈值,而且执行的是delete删表语句,且该表数据只有50w左右,这么大的延迟而且数据量不大的情况下执行delete都这么长时间,那么相当于从服务器早已夯死,只有一个主库平稳运行。为了解决这个主从延时过高问题,引出了sysbench性能压测工具。同时也会给出对应的排查过程

数据库性能指标

指标	英文含义	说明
QPS	Query Per Second	数据库每秒执行的SQL数，包含insert、select、update、delete等。
TPS	Transaction Per Second	数据库每秒执行的事务数，每个事务中包含18条SQL语句。

1、sysbench工具安装

1.1、yum方式在线安装

yum -y install sysbench

1.2、离线安装

在可以出公网的机器上使用以下命令进行下载,将下载好的rpm包保存到本地后再上传到对应的数据库服务器上。rpm包如下所示
yum -y install sysbench --downloadonly --downloaddir=/tmp/

1.3、sysbench简介

sysbench 支持以下几种测试模式:
1、CPU 运算性能
2、磁盘 IO 性能
3、调度程序性能
4、内存分配及传输速度
5、POSIX 线程性能–互斥基准测试
6、数据库性能(OLTP 基准测试) #本次使用OLTP基准测试

sysbench其余相关测试方法详解可见下方地址:https://blog.csdn.net/oschina_41731918/article/details/128000593

2、主从延时过高问题排查流程

2.1、登录从库查看从库状态、事务及相关进程

mysql > show slave status\G;
....
Seconds Behind Master: 4784150 #主从同步延时时间(秒)
....

mysgl> select * from information_schema.innodb_trx\G; #查看是否存在大事务
trx_id: 444902200
trx_state: RUNNING #事务执行状态
trx_started: 2024-09-10 08:26:26 #事务开始执行时间
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 137910
trx_mysql_thread_id: 15
trx_query: DELETE FROM wggl_sjgdxq #事务执行的具体sql
trx_operation_state: NULL
trx_tables_in_use: 1 
trx_tables_locked: 1
trx_lock_structs: 1111
trx_lock_memory_bytes: 123088
trx_rows_locked: 136799
trx_rows_modified: 136799
trx_concurrency_tickets: 0
trx_isolation_level: READ CoMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive hash_timeout: 0
trx is_read only: 0
trx autocommit_ non_locking: 0
1 row in set (0.00 sec)
ERROR:
No query specified

mysql > select * from information_schema.processlist where info is not null; #查看不为空的进程

mysql >  select count(*) from wggl_sjgdxq; #数据量大约为50万条

从上述排查看到,主从同步延时是因为这个大事务导致的
但是从这个事务开始执行的时间到问题排查时间,也远远达不到4784150秒
这个时间对不上,因此可能还存在其他问题,因此继续走排查流程

2.2、查看服务器磁盘、cpu等信息

[root@mysql2 ~]#  iostat -xmt 3[root@mysql2 ~]#  top

从服务器资源使用情况看,8C只用满了1C,cpu使用也正常
但是iostat 显示磁盘的读写为0,怀疑是磁盘问题,要不就是当前没有任何读写操作
排除了服务器cpu的问题,接着往下排查数据盘性能问题

2.3、使用fio命令压测数据盘性能

2.3.1、安装fio工具

#二进制安装
[root@mysql2 ~]# tar -zxvf fio-3.17.tar.gz
[root@mysql2 ~]# yum install gcc
[root@mysql2 ~]# yum install libaio-devel
[root@mysql2 ~]# cd fio-3.17
[root@mysql2 ~]# ./configure
[root@mysql2 ~]# make && make install#yum在线安装
[root@mysql2 ~]# yum -y install fio

2.3.2、执行磁盘随机读写压测命令

[root@mysql2 ~]# time fio -filename=/export/test_randreadwrite.out -direct=1 -iodepth 1 -thread -rw=randrw -rwmixread=70 -ioengine=psync -bs=16k -size=10G -numjobs=10 -runtime=60 -group_reporting -name iops_randwrite#这个 fio 命令将执行一个性能测试，随机读写模式下的测试文件大小为 10 GB，块大小为 16 KB，测试持续 60 秒，使用直接 I/O，且每个线程会处理单个 I/O 请求。测试会以 70% 的读取和 30% 的写入比例来模拟真实的应用负载，并汇总所有线程的性能数据。

总结: fio 结果表明:读取吞吐量速度: 107 MiB/s (112 MB/s)，总共读取了 6392 MiB (6703 MB)。写入吞吐量速度: 45.7 MiB/s (47.9 MB/s)，总共写入了 2743 MiB (2877 MB)。磁盘利用率: 99.91%,表明磁盘几乎处于满负荷运行状态。读取 IOPS: 从 vdb 的统计信息中可以计算读取 IOPS。总读取 I/O 操作次数为 408,652 次，运行时间为 60 秒，因此读取 IOPS 为 408562/60=6810(IOPS) 写入 IOPS: 总写入 I/O 操作次数为 179,942 次，运行时间同样为 60 秒，因此写入 IOPS 为 179942/60=2,665 IOPS延迟: 通常不是直接从 fio 输出中获取的，但可以通过 fio 的 latency 输出或其他工具（如 iostat）来获得。由于你的测试结果中没有直接显示延迟，我们可以从吞吐量和 IOPS 间接推测：如果吞吐量较高且 IOPS 较低，通常意味着延迟较高，因为每个 I/O 操作花费的时间较长。如果 IOPS 高且吞吐量也高，则延迟通常较低，因为磁盘能迅速处理每个 I/O 操作。从分析来看读取吞吐量较高，但写入吞吐量相对较低。这个不平衡可能表明写入操作的延迟较高。但是从主从同步延时的sql语句查看,执行的也是insert或update操作,而是delete操作,因此,写入操作的延迟较高对数据库的延时高问题并没有直接的关系,因此,也可以排除磁盘性能问题。接着往下排查。命令参数解释:
-filename=/export/test_randreadwrite.out: 指定测试文件的路径和名称。在这个例子中，测试文件将被创建或使用在 /export/test_randreadwrite.out。-direct=1: 使用直接 I/O 模式。这意味着 I/O 操作不会经过操作系统的缓存，从而提供更准确的性能测试。-iodepth 1: 设置 I/O 队列深度为 1。这表示每个线程同时只能处理一个 I/O 请求。-thread: 启用线程模式，这样每个 numjobs 参数指定的作业将以线程的形式执行，而不是独立的进程。-rw=randrw: 设置读写模式为随机读写。即在测试中会进行随机读取和随机写入操作。-rwmixread=70: 设置读写混合比例。70 表示 70% 的操作是随机读取，30% 是随机写入。-ioengine=psync: 使用 psync I/O 引擎，它通过系统调用 pwrite 和 pread 来进行同步 I/O 操作。-bs=16k: 设置块大小为 16 KB。这是每次读写操作的数据块大小。-size=10G: 设置测试文件的大小为 10 GB。-numjobs=10: 设置同时运行的作业数为 10。每个作业都是一个独立的线程，能够并发执行 I/O 操作。-runtime=60: 设置测试的运行时间为 60 秒。在这段时间内，fio 将持续执行测试。-group_reporting: 汇总并报告所有作业的性能结果，而不是分别报告每个作业的结果。-name iops_randwrite: 设置测试的名称为 iops_randwrite，这有助于在生成的报告中识别该测试。

2.4、查看从库相关参数

mysql> show variables like '%para%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK | #行级别多线程
| slave_parallel_workers | 4             | #线程数量
+------------------------+---------------+
2 rows in set (0.00 sec)mysql> show variables like '%thread%';
+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| innodb_purge_threads                    | 4                         |
| innodb_read_io_threads                  | 4                         |
| innodb_thread_concurrency               | 0                         |
| innodb_thread_sleep_delay               | 10000                     |
| innodb_write_io_threads                 | 4                         |
| max_delayed_threads                     | 20                        |
| max_insert_delayed_threads              | 20                        |
| myisam_repair_threads                   | 1                         |
| performance_schema_max_thread_classes   | 50                        |
| performance_schema_max_thread_instances | -1                        |
| pseudo_thread_id                        | 103475012                 |
| thread_cache_size                       | 512                       |
| thread_handling                         | one-thread-per-connection |
| thread_stack                            | 196608                    |
+-----------------------------------------+---------------------------+
14 rows in set (0.00 sec)

经过DBA同事结合服务器配置进行参数分析,发现参数也没有问题,那么这个主从同步延时高在哪里呢？接着排查

2.5、使用sysbench工具对mysql从服务器压测

2.5.1、在从数据库,创建测试库

mysql> create database dbtest;
Query OK, 1 row affected (0.00 sec)mysql> grant all on dbtest.* to dbtest@'%' identified by 'dbtest';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.5.2、准备测试数据

#执行模式为complex，使用了10个表，每个表有10万条数据，客户端的并发线程数为10，执行时间为120秒，每10秒生成一次报告。
[root@mysql2 ~]# sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=dbtest --mysql-password=dbtest --mysql-db=dbtest --mysql-storage-engine=innodb --tables=10 --table-size=500000 /usr/share/sysbench/oltp_common.lua --forced-shutdown=1 --threads=16 --time=600 --report-interval=1 prepare

#参数解释
--db-driver=mysql: 使用 MySQL 数据库驱动。
--mysql-host=127.0.0.1: 数据库服务器地址，127.0.0.1 表示本地服务器。
--mysql-port=3306: 数据库服务器端口，3306 是 MySQL 的默认端口。
--mysql-user=dbtest: 数据库用户。
--mysql-password=dbtest: 数据库密码。
--mysql-db=dbtest: 要连接的数据库名。
--mysql-storage-engine=innodb: 使用 InnoDB 存储引擎。
--tables=10: 创建 10 个表。
--table-size=500000: 每个表包含 500,000 行数据。
/usr/share/sysbench/oltp_common.lua: 指定测试脚本文件，执行准备操作。
--forced-shutdown=1: 强制关闭所有进程和线程，确保测试环境干净。
--threads=16: 使用 16 个线程进行数据准备。
--time=600: 数据准备时间为 600 秒（10 分钟）。
--report-interval=1: 每 1 秒报告一次进度。

2.5.3、执行测试命令

#场景：10张表，每张表个1000万行，16线程,花费300秒进行读写测试, 结果导出到文件中，便于后续分析。
[root@mysql2 ~]# sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=dbtest --mysql-password=dbtest --mysql-db=dbtest --mysql-storage-engine=innodb --tables=10 --table-size=500000 oltp_read_write --forced-shutdown=1 --threads=16 --time=300 --report-interval=10 run

#参数解释:
--db-driver=mysql: 使用 MySQL 数据库驱动。
--mysql-host=127.0.0.1: 数据库服务器地址，127.0.0.1 表示本地服务器。
--mysql-port=3306: 数据库服务器端口，3306 是 MySQL 的默认端口。
--mysql-user=dbtest: 数据库用户。
--mysql-password=dbtest: 数据库密码。
--mysql-db=dbtest: 要连接的数据库名。
--mysql-storage-engine=innodb: 使用 InnoDB 存储引擎。
--tables=10: 测试使用 10 个表。
--table-size=500000: 每个表包含 500,000 行数据。
oltp_read_write: 指定测试类型为 oltp_read_write，表示执行 OLTP（在线事务处理）读写混合负载测试。这是一个测试脚本，通常包含对数据库的读写操作。
--forced-shutdown=1: 强制关闭所有进程和线程，确保测试环境干净。
--threads=16: 使用 16 个线程来执行测试。
--time=300: 测试持续时间为 300 秒（5 分钟）。
--report-interval=10: 每 10 秒报告一次测试进度。

从测试后的结果看,数据库性能也不存在问题,那主从同步延时高这个问题就很诡异了 测试了磁盘性能、测试了数据库性能,都没有发现问题,而且查看数据库服务器的messages信息,也没有异常~~到这里就给我整不会了.

2.5.4、清理测试数据

[root@mysql2 ~]# sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=dbtest --mysql-password=dbtest --mysql-db=dbtest --mysql-storage-engine=innodb --tables=10 --table-size=500000 oltp_read_write --threads=16 cleanup

#参数解释:
--db-driver=mysql: 使用 MySQL 数据库驱动。
--mysql-host=127.0.0.1: 数据库服务器地址，127.0.0.1 表示本地服务器。
--mysql-port=3306: 数据库服务器端口，3306 是 MySQL 的默认端口。
--mysql-user=dbtest: 数据库用户。
--mysql-password=dbtest: 数据库密码。
--mysql-db=dbtest: 要连接的数据库名。
--mysql-storage-engine=innodb: 使用 InnoDB 存储引擎。
--tables=10: 指定有 10 个表。
--table-size=500000: 每个表包含 500,000 行数据。
oltp_read_write: 指定测试类型为 oltp_read_write，这通常是一个测试脚本，包含对数据库的读写操作。
--threads=16: 使用 16 个线程来执行测试。
cleanup: 执行清理操作，这通常会删除在测试准备阶段创建的表和数据，恢复数据库到清理前的状态。
总结来说，cleanup 命令用于删除之前由 sysbench 创建的测试表和数据，以便清理测试环境或准备进行新的测试。

2.6、重新做主从关系

最后仅剩重新做主从方法看是否能解决此问题,重新做主从在此处不再过多描述。
经过重新做主从后
分别执行
show slave status\G; 
select * from information_schema.innodb_trx\G; 
发现从库还是存在刚开始的大事务,而且主从延时Seconds Behind Master时间也在不断增长,trx_rows_modified数据变化速度也逐渐降低。到此,心态崩了~~~~

2.7、模拟测试

在测试环境MySQL上构造了500w的假数据,然后执行删除操作,发现1-2分钟左右sql执行完成,如下图所示,没有任务异常.两者对比就很明显了。现在大概率怀疑到生产环境从数据库服务器存在问题,后续等封网结束后,找个时间重启从数据库服务器,然后再观察看是否还会存在该问题

总结

到此时,这个主从延时高的问题还没有解决.就等两天后重启从库服务器最后一个方法了,如果还不行,那只能另辟蹊径换服务器重新搭建数据库了。再次有大佬有排查思路可以再评论区分享一下，让小弟学习学习解决一下问题，感谢!!!