mysql主从配置压力测试,MySQL 主从同步延迟的测试与重复数据的探讨 (ab 压力测试)...

背景

如果，初次配置完成了 MySQL 数据库的读写分离操作

那么，后面遇到稍大流量访问时；

首先遭遇到的便是 “主从同步延迟” 造成的后果

环境

Linux系统： CentOS7.2

mySQL版本： mySQL5.7.32

MySQL 数据库主从同步延迟原理 (摘抄经验)

DDL : 数据定义语言, DML :数据操纵语言

MySQL的主从复制都是单线程的操作，

主库对所有 DDL 和 DML 产生的日志写进 binlog，由于 binlog 是顺序写，所以效率很高。

Slave 的 SQL Thread 线程将主库的 DDL 和 DML 操作事件在slave中重放。

DML 和 DDL 的 IO 操作是随即的，不是顺序的，成本高很多。

另一方面，由于SQL Thread 也是单线程的，

当主库的并发较高时，产生的 DML 数量超过 slave 的 SQL Thread 所能处理的速度，

或者当 slave 中有大型 query 语句产生了锁等待那么延时就产生了

有朋友会问：“主库上那个相同的 DDL 也需要执行 10 分，为什么 slave 会延时？”

答案是 : master 可以并发，Slave_SQL_Running 线程却不可以

常见原因：Master 负载过高、Slave 负载过高、网络延迟、机器性能太低、MySQL 配置不合理

☛ 主从延时排查方法

第一种方法

1. show master status\G; # 查看主库的 position 号记录到多少了.

2. 从库中执行 show slave status\G; # 查看从库现在获取到哪个 position 号了.

3. 如果从库的 postion 号远小于主库的 position 号,则表示主库 dump 线程传送二进制出问题了.

第二种方法(推荐)

通过监控 "show slave status" 命令，输出的 "Seconds_Behind_Master" 参数的值来判断

NULL，表示 io_thread 或是 sql_thread 有任何一个发生故障；

0，该值为零，表示主从复制良好；

正值，表示主从已经出现延时，数字越大表示从库延迟越严重。

为了再现这种高并发时刻，测试指令为 ： ab -c 12 -n 10000 http://tp5pro.com/index/test

☛ Master-Slave 高并发情景模拟

简单理解为，主库遇到了高并发情况，比如短时间内大量用户触发注册逻辑

▷ 测试代码

执行代码如下：

function testMysql(){

$randID = rand(300,500);

$exitTag = Db::name('xtest_logs')

->where('open_id',"$randID")->count('id');

if ($exitTag){

return 0;

}else{

$op_id = Db::name('xtest_logs')

->insertGetId(['open_id'=>"$randID",'add_time'=>time()]);

try{

//TODO 查询数据库里是否存在这个新加入的 记录

$count = Db::name('xtest_logs')

->where('id',intval($op_id))

->count('id');

//判断是否存在 $op_id 的记录，都更新到 name 字段

$msg = ($count>0) ? $op_id: "--$count--";

}catch (\Exception $e){

$msg = substr($e->getMessage(),0,300);

}

Db::name('xtest_logs')

->where('id',$op_id)

->update(['name' => $msg]);

return 1;

}

}

▷ ab 模拟并发场景

此时使用 ab 工具进行模拟高并发状态: ab -c 15 -n 10000 http://tp5pro.com/index/test

ab 压力测试参数解释，可参考文章 —— 【ab 压力测试】

Server Software: nginx/1.15.11

Server Hostname: tp5pro.com

Server Port: 80

Document Path: /index/test

Document Length: 37957 bytes

Concurrency Level: 15

Time taken for tests: 134.282 seconds

Complete requests: 10000

Failed requests: 9919

(Connect: 0, Receive: 0, Length: 9919, Exceptions: 0)

Total transferred: 376049112 bytes

HTML transferred: 374419112 bytes

Requests per second: 74.47 [#/sec] (mean)

Time per request: 201.423 [ms] (mean)

Time per request: 13.428 [ms] (mean, across all concurrent requests)

Transfer rate: 2734.81 [Kbytes/sec] received

Connection Times (ms)

min mean[+/-sd] median max

Connect: 0 0 0.3 0 15

Processing: 38 201 97.4 190 912

Waiting: 19 79 85.0 63 823

Total: 38 201 97.4 190 912

Percentage of the requests served within a certain time (ms)

50% 190

66% 218

75% 233

80% 241

90% 264

95% 295

98% 616

99% 702

100% 912 (longest request)

最终，数据表记录展示如下图：

分析可知，以上出现了 主从数据库同步延迟 的问题，业务繁多时可能延迟十几分钟几个小时不等！

▷ 总结

针对这个举例，也可归入 重复数据写入 问题，简单说一下我的思路，道行尚浅，欢迎补充 …

①. 尽量避免这种数据表设计思路

建议使用唯一索引，比如 微信用户的 open_id 就是一个例子

其次，对于数据的操作

最好也要使用 "try-catch"语句进行异常捕获的处理

②. 前端事件入口优化

常见的方法，可以建议设置连续点击时间不能短于2 秒

前端触发提交按钮后，禁用按钮，等待处理结果返回后，才可继续点击

③. redis 中间件的拓展优化 …

以常作推荐使用的 Redis 来说

可以使用 Redis 缓存计数器(注意更新计数时的加锁操作)流程图如下：

④. 使用 Token 令牌，避免恶意的 ajax 请求

☛ 主从同步的延迟解决方案

▷. 架构方面

1.业务的持久化层的实现采用分库架构，mysql 服务可平行扩展，分散压力。

2.单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。

3.服务的基础架构在业务和 mysql 之间加入 memcache 或者 redis 的 cache 层。降低 mysql 的读压力。

4.不同业务的 mysql 操作，放在不同机器，分散压力。

5.使用比主库更好的硬件设备作为 slave

▷. 硬件方面

1.采用好服务器，比如 4u 比 2u 性能明显好

2.存储用 ssd 或者盘阵或者 san，提升随机写的性能。

3.主从间保证处在同一个交换机下面，并且是万兆环境

4.优化网络，光纤、带宽等

▷. 当前配置信息的变动(牺牲性能)

主库配置 sync_binlog=1，innodb_flush_log_at_trx_commit=1

sync_binlog 的默认值是 0

MySQL 不会将 binlog 同步到磁盘，

其值表示每写多少 binlog 同步一次磁盘。

innodb_flush_log_at_trx_commit 为 1 表示每一次事务提交或事务外的指令都需要把日志 flush 到磁盘

▷. 考虑 PXC 集群的使用(牺牲性能)

毕竟最大的优势：强一致性，无同步延迟

☞ 对 PXC 集群方案 “无同步延迟“ 说法的一个疑问

所有的文章中都在说， “PXC 强一致性，无同步延迟”

我这两天配置了 PXC 集群，然后使用 mycat 作为中间件

核心配置信息如下：

测试代码依然如前面 【情景模拟】中的一样

使用 ab 测试：ab -c 15 -n 10000 http://tp5pro.com/index/test

Server Software: nginx/1.15.11

Server Hostname: tp5pro.com

Server Port: 80

Document Path: /index/test

Document Length: 37962 bytes

Concurrency Level: 15

Time taken for tests: 146.288 seconds

Complete requests: 10000

Failed requests: 9998

(Connect: 0, Receive: 0, Length: 9998, Exceptions: 0)

Total transferred: 376055076 bytes

HTML transferred: 374425076 bytes

Requests per second: 68.36 [#/sec] (mean)

Time per request: 219.431 [ms] (mean)

Time per request: 14.629 [ms] (mean, across all concurrent requests)

Transfer rate: 2510.41 [Kbytes/sec] received

Connection Times (ms)

min mean[+/-sd] median max

Connect: 0 0 0.3 0 8

Processing: 35 219 93.8 210 857

Waiting: 18 103 85.8 83 790

Total: 35 219 93.7 210 857

Percentage of the requests served within a certain time (ms)

50% 210

66% 235

75% 251

80% 260

90% 282

95% 313

98% 617

99% 694

100% 857 (longest request)

最终，PXC 集群中得到的数据

发现头一天，出现了几条 "--0--"的记录

各种测试无果

认为可能真的有同步延迟，纠结一天后

今天，重启虚拟机

开启三个 PXC 节点

再次正常测试，竟然不在出现 "--0--" 的记录了

本来都计划整理这个疑问了

今天突然不能重现情景

直接怀疑人生！

【那么问题来了...】

我直接懵了

为什么会得到这种结果呢？

但是，我注意到了一点

头一天，在开启 PXC 集群的情况下，原先的主从配置是不能启用的

但是，今天，却发现，我这三台虚拟机

同时支持了 主从同步配置、PXC 集群部署

最新的结果，确实证明了 不存在同步延迟！

如今只能初步归咎为虚拟机异常运行，后期再做测试 …

这个困扰我的问题，实在没头绪，希望知道问题所在的可以帮忙解释下，无比感激 ...