logo

ProxySQL + MySQL MGR读写分离架构的Sysbench只读压测报告

作者:呆萌没商量2021.12.14 14:36浏览量:826

简介:负载均衡 + ProxySQL Cluster + MySQL MGR 的读写分离架构调研。

作者 | 雷宏婧
编辑 | 冬梅

在大量并发读请求、读多写少的业务场景下,本文利用 Sysbench 性能测试工具,调研基于【负载均衡 + ProxySQL Cluster + MySQL MGR 的读写分离架构】能否有效利用横向扩展的 MySQL 实例的读能力,并最终提高应用系统 QPS。

前 言

1.在大量并发读请求、读多写少的业务场景下,本文利用 Sysbench 性能测试工具,调研基于【负载均衡 + ProxySQL Cluster + MySQL MGR 的读写分离架构】能否有效利用横向扩展的 MySQL 实例的读能力,并最终提高应用系统 QPS。

2.MySQL Group Replication(MGR)于 2016 年 12 月被推出,提供了高可用、高扩展、高可靠的 MySQL 集群服务。但其仅解决了数据同步问题和集群内部的自动故障转移。当 Master 宕机,应用系统可能需要修改数据库连接地址,才能保证服务的可用性。为解决上述问题,可在 MRG 上层增加代理层,例如 ProxySQL。

3.ProxySQL 于 2015 年被推出,是一个开源、高性能、高可用性、协议感知的 MySQL 代理。

1) 可通过每个节点的 read_only 值,自动调整它们是属于读组还是写组;

2) 可定制基于用户、基于 schema、基于语句的规则对 SQL 语句进行路由,实现读写分离;

3) 支持搭建 ProxySQL Cluster 来达到高可用,节点之间的配置可自动同步。

4.负载均衡是将流量分发至多台节点设备上处理的服务。

1) 可通过消除单点故障,提升应用系统的可用性;

2) 可减缓大量的并发访问,提高应用系统的处理能力。

Sysbench 是一个开源的、模块化的、跨平台的多线程性能测试工具。

1. 压测目的

基于 Sysbench 的 oltpreadonly 压测模式,对比【负载均衡 + ProxySQL Cluster + MGR 的读写分离】和【应用直连 MySQL Master】这两种架构的只读性能:

  1. 建立读写分离架构的只读性能基线数据;

  2. 验证读写分离架构在大量并发读请求场景下的有效性;

  3. 分析各模块和参数对读写分离架构性能的影响。

2. 压测结论

2.1. 读写分离架构的只读性能基线数据

在 Sysbench oltpreadonly 压测模式下,【4 层负载均衡 +ProxySQL Cluster+MGR 读写分离】架构的 QPS 与并发线程数关系如下表所示。

图片.jpg

ps:“/”表示由于 Sysbench 机器 CPU 耗尽,未能完成测试,无实验结果。

2.2. 只读场景下读写分离架构的有效性

首先简单浏览下实验对比架构和结果,

图片.jpg

图片.jpg

图片.jpg

ps:“/”表示由于 Sysbench 机器 CPU 耗尽,未能完成测试,无实验结果。实验结果表明:

  • 在不引入负载均衡、ProxySQL Cluster 等中间件的理想情况下,【应用直连 MGR 2 个只读实例】QPS 最大值能达到 100w,为【应用直连 MySQL Master】的只读 QPS 最大值 37w 的 2.7 倍。该结果验证了 MGR 架构在大量并发读请求场景下的有效性。

但实际上,如要保证应用系统高可用,则需引入负载均衡、ProxySQL Cluster 等中间件,而这些中间件或多或少会带来性能损失。实验发现,【4 层负载均衡 +ProxySQL Cluster+MGR 读写分离】架构的只读 QPS 最大值为 89w,约为【应用直连 MySQL Master】的只读 QPS 最大值 37w 的 2.4 倍,该结果验证了【4 层负载均衡 +ProxySQL Cluster+MGR 读写分离】架构在大量并发读请求场景下的有效性。

2.3. 各模块和参数对读写分离架构性能的影响

  1. 【4 层负载均衡 +ProxySQL Cluster+MGR 读写分离】架构的 QPS 最大值约为【直连 MGR 2 个只读实例】QPS 最大值 100w 的 89%。其中,ProxySQL Cluster 带来约 11% 的性能损失,负载均衡几乎没有带来性能损失。但是 ProxySQL 的 CPU 占用率最高仅 57%,还需后续探索能否进一步有效利用 ProxySQL。
  2. 根据 https://github.com/sysown/ProxySQL/issues/1724,参考 CPU 核数增加 ProxySQL 的 mysql-threads 变量值,即增加 ProxySQL 用于处理 MySQL 流量的后台线程数,能有效提升 QPS(如将线程数从 4 增加至 16,QPS 提升了 3.3 倍),但目前还没压测出 ProxySQL 的 CPU 利用率提升到 100% 的场景。
  3. 横向拓展 ProxySQL 实例数目,能有效提升 QPS(实例数从 1 增加至 2,QPS 提升 1 倍)。
  4. 将 7 层负载均衡换成 4 层,由在应用层进行流量分发改成在传输层,降低网络性能损耗,在实验中提升了 1 倍 QPS。
  5. 根据 https://ProxySQL.com/blog/benchmarking-ProxySQL-144/,增大 ProxySQL 的 mysql-max_stmts_per_connection 变量值(20 增加至 100),让单个连接可以处理更多的 prepared 语句,但实验中未能影响 QPS。

3. 压测详情

3.1. 压测环境

图片.jpg

此外,还安装了 nodeexporter、mysqlexporter、proxysql_exporter 来监控 OS、MySQL 和 ProxySQL,方便定位问题。

3.2. 压测指标

  • 每秒执行请求数 QPS(Queries Per Second)数据库每秒执行的 SQL 数,包含 INSERT、SELECT、UPDATE、DETELE、COMMIT 等。QPS 值越高越好。

  • 95% Latency (ms) 95% 请求的延迟时间表征的是全部 query 请求中的 95% 在发出至收到结果的平均往返时间。延迟越小越好。

3.3. 实验设置

为减少误差,每轮实验重复 3 次。每次任务执行完之后,等待 300s,让系统及时处理未完成任务,才进入下一轮压测。压测后除了利用 Sysbench 自带的 cleanup 清理数据,还额外把 binlog 清理干净,以防磁盘空间变少而影响下一次压测。其他模块设置见下文。

3.3.1 MySQL 设置

  • MGR:单主模式。共 3 个节点,其中 1 个只写节点,2 个只读节点。max_connection 设为 3000。

  • Master-Master:主主同步,仅其中 1 个 Master 提供读写服务。max_connection 设为 3000。

3.3.2 ProxySQL 设置

  • mysql_user 表的 transaction_persistent 字段:设置为 1,表示在某节点内启动的事务将保留在该节点内,而与其他转发规则无关。用于避免以下问题:一个事务有混合的读操作和写操作组成,事务未提交前,如果事务中的读操作和写操作路由到不同节点,读取到的结果是脏数据。
 insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('MGR','MGR',1,1);
  • mysql_servers 表的 max_connections:允许连接到该后端实例的最大连接数,不能大于 MySQL 设置的 max_connections,因此设为 3000。

  • mysql_group_replication_hostgroups 表:配置 MGR writerhostgroup、readerhostgroup 等组别对应的 hostgroupid。ProxySQL 会通过视图来监控 MGR 节点是否正常,是否开启了只读、挤压事务数等来调整单个 MGR 节点所属的 hostgroupid,具体调整结果可在 runtime_mysql_servers 中查看。 insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind,comment) values(1,2,3,4,1,1,0,100,’mgr-test’);

— 可以看出有写组有 1 个节点,读组有 2 个节点,均在正常工作

图片.jpg

  • 查询规则配置:根据 SQL 的正则表达式匹配,读请求转发至读组,写请求转发至写组。

— 将写请求转发到写组

INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (200,1,’mgr’,’^SELECT.*FOR UPDATE$’,1,1);

— 将读请求转发到读组

INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (201,1,’mgr’,’^SELECT’,3,1);

  • 全局变量 mysql-threads:是 ProxySQL 用于处理 MySQL 流量的后台线程数。默认值为 4,实验中发现,增加值至 16 可大幅提升 QPS,因此除了该变量的参数调优实验,其他实验中该变量值均为 16。

set mysql-threads=16;show variables like ‘mysql-threads’;

图片.jpg

3.3.3 Sysbench 设置

  • 实验基于 Sysbench 的 oltpreadonly 只读模式。该模式下,一个事务包含 14 个读 SQL(10 条主键点查询、4 条范围查询)。

  • oltpreadonly 模式的压测命令

准备数据:

sysbench —db-driver=mysql —mysql-host=XXX —mysql-port=XXX —mysql-user=XXX —mysql-password=XXX —mysql-db=dbtest —tables=1 —table-size=10000000 —report-interval=1 —threads=XXX —rand-type=uniform —time=120 —auto-inc=on /usr/local/share/sysbench/oltp_read_only.lua prepare

运行 workload:

sysbench —db-driver=mysql —mysql-host=XXX —mysql-port=XXX —mysql-user=XXX —mysql-password=XXX —mysql-db=dbtest —tables=1 —table-size=10000000 —report-interval=1 —threads=XXX —rand-type=uniform —time=120 —auto-inc=on —skip_trx=on /usr/local/share/sysbench/oltp_read_only.lua run

清理数据:

sysbench —db-driver=mysql —mysql-host=XXX —mysql-port=XXX —mysql-user=XXX —mysql-password=XXX —mysql-db=dbtest —tables=1 —table-size=10000000 —report-interval=1 —threads=XXX —rand-type=uniform —time=120 —auto-inc=on /usr/local/share/sysbench/oltp_read_only.lua cleanup

普通变量:

  • time:压测总持续时间(秒),超时后任务未完也会被终止。

  • threads:并发压测的线程数。取值范围[16,32,64,128,256,512,1024,1500,2048,2500,3000,3500,4096]。

重点变量:

  • skip_trx[=on|off]:默认为 off,即启动显式事务;值为 on 时,不启动显式事务,以 AUTOCOMMIT 模式执行所有查询。

  • 压测时设置:sysbench —skiptrx=on;ProxySQL 的 mysqluser 表的 transaction_persistent=1。原因如下:

  • ProxySQL 的 mysqluser 表的 transactionpersistent 字段设为 1 时,在某节点内启动的事务将保留在该节点内,而与其他转发规则无关。用于避免以下问题:一个事务有混合的读操作和写操作组成,事务未提交前,如果事务中的读操作和写操作路由到不同节点,读取到的结果是脏数据。因此,如果不开启 skip_trx,sysbench 所有请求都会被 ProxySQL 转发到写组,如此便测不了读写分离的性能。

  • sysbench 默认使用 prepared statements,因本实验需要测试使用 prepared statements 的情况,故在此不作关闭该功能的参数说明。

  • 设置变量 —mysql-host=[host1,host2,…,hostN],即可对多个 MySQL 同时发起读请求。可用于并发压测多个 MySQL 实例时的 QPS。

https://github.com/akopytov/sysbench/issues/19

3.3.4 实验场景设置

总共设计了 6 个实验场景(架构图详见实验结果分析),实验目的如下:

图片.jpg

3.4. 实验结果分析

实验 1: [MGR] vs [Master-Master]
图片.jpg
实验目的:

获取通过应用(sysbench)直连 MGR 的 2 个只读实例数所能带来的 QPS 上限,确认该上限和应用直连 mysql Master-Master 中其中 1 台的 QPS 差异。

实验结果:

图片.jpg

图片.jpg

ps:“/”表示由于 Sysbench 机器 CPU 耗尽,未能完成测试,无实验结果。

  • MGR QPS 上限约 100w,约为 Master-Master 的 2.67 倍。

    实验 2: [single ProxySQL+MGR] vs [MGR]
    图片.jpg
    实验目的:

在尽可能减少应用和 ProxySQL 之间网络延迟的情况下,确认增加 ProxySQL 中间件会带来的性能损失

实验结果:

图片.jpg

图片

ps:“/”表示由于 Sysbench 机器 CPU 耗尽,未能完成测试,无实验结果。

  • single ProxySQL+MGR QPS 最大 48w,约为 MGR 的 48%。

  • single ProxySQL+MGR QPS 最大时,ProxySQL 实例 CPU 占用率最高为 57%。

实验结论:

在该实验中,ProxySQL Cluster 带来约 48% 的性能损失,但此时 ProxySQL 的 CPU 占用率并不算很高,值得后续探索能否进一步有效利用 ProxySQL。

实验 3: [ProxySQL Cluster+MGR] vs [single ProxySQL+MGR]

图片.jpg

实验目的:

确认横向拓展 ProxySQL 实例数目能否进一步提升 QPS

实验结果:

图片.jpg

图片.jpg

ps:“/”表示由于 Sysbench 机器 CPU 耗尽,未能完成测试,无实验结果。

  • ProxySQL Cluster+MGR QPS 上限约 89w,约为 single ProxySQL+MGR 的 1.85 倍。

  • ProxySQL Cluster+MGR QPS 最大时,ProxySQL 实例 CPU 占用率最高为 56%。

实验结论:

横向拓展 ProxySQL 实例数目可以进一步提升 QPS 至 89w,相对接近 MGR 的上限 100w。

实验 4: [7 层负载均衡 +ProxySQL Cluster+MGR] vs [ProxySQL Cluster+MGR]

图片.jpg

实验目的:

增加读写分离架构中必不可少的负载均衡服务,并确认其带来的性能损失

实验结果:

图片.jpg

图片.jpg

ps:“/”表示由于 Sysbench 机器 CPU 耗尽,未能完成测试,无实验结果。

  • 增加负载均衡后,QPS 最大值为 42w,约 MGR 上限的 50%,仅为 Master-Master 的 1.135 倍。

实验结论:

增加负载均衡导致性能损失近 50%,可能是因为网络、配置问题,需要进一步排查。

实验 5: [4 层负载均衡 +ProxySQL Cluster+MGR] vs [7 层负载均衡 +ProxySQL Cluster+MGR]
实验目的:

4 层负载均衡工作在 OSI 模型的传输层(基于 IP+ 端口),7 层工作在应用层(基于 URL)。

理论上,7 层负载均衡会带来更多的网络性能损耗。因此尝试调整为 4 层负载均衡,以减少性能损失。

实验结果:

图片.jpg

图片.jpg

ps:“/”表示由于 Sysbench 机器 CPU 耗尽,未能完成测试,无实验结果。

实验结论:

将 7 层负载均衡换成 4 层负载均衡后,QPS 最大值为 89w,负载均衡几乎没带来性能损失。

实验 6: ProxySQL 参数调优:分析 mysql-threads 参数值对性能的影响
实验目的:

根据https://github.com/sysown/ProxySQL/issues/1724,mysql-threads 变量是 ProxySQL 用于处理 MySQL 流量的后台线程数,理论上,根据机器 CPU 核数来调整该变量,可提升 ProxySQL 性能。因此尝试分析该参数对性能的影响。

实验结果:

图片.jpg

图片.jpg

ps:“/”表示由于 Sysbench 机器 CPU 耗尽,未能完成测试,无实验结果。

实验结论:

根据机器 CPU 核数来增加 ProxySQL 的 mysql-threads 变量值,可一定程度上提升 QPS。

4. 总 结

  1. 【4 层负载均衡 + ProxySQL Cluster + MGR 读写分离】架构适用于在大量并发读请求场景,只读 QPS 最大能达到 89w,约为【应用直连 MySQL Master】的只读 QPS 最大值 37w 的 2.4 倍。

  2. 参考机器的 CPU 核数增加 ProxySQL 的 mysql-threads 变量值,即增加 ProxySQL 用于处理 MySQL 流量的后台线程数,能有效提升 QPS(如将线程数从 4 增加至 16,QPS 提升了 3.3 倍)。

  3. 横向拓展 ProxySQL 实例数目,能有效提升 QPS(实例数从 1 增加至 2,QPS 提升 1 倍)。

  4. 将 7 层负载均衡换成 4 层,由在应用层进行流量分发改成在传输层,能降低网络性能损耗并提升 QPS。

  5. 本次实验中,ProxySQL Cluster 带来约 11% 的性能损失,负载均衡几乎没有带来性能损失。但是 ProxySQL 的 CPU 占用率最高仅 57%,还需后续探索能否进一步有效利用 ProxySQL。

参考文献:

https://dev.MySQL.com/doc/refman/5.7/en/group-replication.html

https://ProxySQL.com/documentation/ProxySQL-Threads/

https://ProxySQL.com/blog/ProxySQL-vs-maxscale-persistent-connection-response-time/

https://www.percona.com/blog/2020/08/28/ProxySQL-overhead-explained-and-measured/

https://github.com/sysown/ProxySQL/issues/1724

https://www.percona.com/blog/2017/04/10/ProxySQL-rules-do-i-have-too-many/

作者简介:

雷宏婧,网易游戏 技术部高级数据库系统工程师。参与海量玩家数据库生产环境故障排查和优化,热衷于研究MySQL技术原理、灾难备份和高可用方案。
来源:InfoQ

相关文章推荐

发表评论