写点什么

ProxySQL + MySQL MGR 自拆书架的 Sysbench 只读压测报告

  • 2021-12-02
  • 本文字数:5532 字

    阅读完需:约 18 分钟

ProxySQL + MySQL MGR 自拆书架的Sysbench 只读压测报告

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

前言

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


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

  2. ProxySQL 于 2015 年被推出,是一个开源、高可用性、协议探索的 MySQL 代理。

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

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

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

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

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

2) 可降低大量的综合访问,提高应用系统的处理能力。

  1. Sysbench 是一个开源的、线程探索的测试、跨平台的多性能工具。

一、压测目的

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

  1. 建立读写读写性能数据;

  2. 验证读写架构在大量并发读请求场景下的宣讲;

  3. 分析各模块和参数对拆解资产业绩的影响。

二、压测结论

2.1. 读写读写读写性能数据

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



ps/”表示由于 Sysbench 机器 CPU 完成,完成测试,无实验结果。

2.2. 只读场景下编写的文档

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





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

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


  1. 但实际上,如要保证应用系统高可用,则需要增加负载均衡、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%,还需要 57% 的时间才能进一步有效利用 ProxySQL。


  1. 根据 https://github.com/sysown/ProxySQL/issues/1724,参考 CPU 核数增加 ProxySQL 的 mysql-threads 变量值,即增加 ProxySQL 用于处理 MySQL 流量的后台线程数,能有效提升 QPS(如将)线程数 4 增加至 16,QPS 从提升了 3.3 倍),但目前尚未压测出 ProxySQL 的 CPU 持续提升到 100% 的场景。


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


  1. 将 7 层负载均衡实际 4 层,由在应用层进行流量分配改成在传输层,降低网络性能,在实验中提升了 1 倍 QPS。


  1. 可知 https://ProxySQL.com/blog/benchmarking-ProxySQL-144/,本身 ProxySQL 的 mysql-max_stmts_per_connection 变量值(20 增加至 100),让单独可以处理更多的准备语句,但实验中影响 QPS。

三、压测详情

3.1. 压测环境


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

3.2. 压测指标

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


• 95% Latency (ms) 95% 请求的延迟时间完全查询请求中的 95% 在发出到接收结果的平均时间。延迟越小延迟。

3.3. 实验设置

为减少干净,每轮实验重复 3 次。每次任务执行完之后,等待 300 秒,让系统及时处理未完成任务,进入下管道压测。压测后除了利用 Sysbench 自带的清理才清理数据,还额外把垃圾桶清理干净,清洁磁盘空间影响一次压测。模块设置下可以看到变。

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);
复制代码

• mysqls 表的 max_connections:允许连接到该实例的最大连接数,不能超过 MySQL 设置的 max_connections,因此设为 3000。


• mysql_group_replication_hostgroups 表:配置 MGR writerhostgroup、readerhostgroup 的 hostgroupidProxy。SQL 会通过视图来监控 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,0,'-test,1r ;


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



•查询规则:根据 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;显示变量,如'mysql-threads';


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


运行工作量:

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=dbtest --tables=1 --表大小=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 运行


清理数据:

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 清理


普通变量:

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

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


重点变量:

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

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

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

•  sysbench 默认使用准备好的语句,因为本实验需要测试使用准备好的语句的情况,因此在此不作关闭该功能的参数说明。

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

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

3.3.4 实验场景设置

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



3.4. 实验结果分析

实验一:[MGR] vs [Master-Master]



实验目的:

获取应用(sysbench)直连 MGR 的 2 个只读实例数引发的 QPS 服务,确认该服务和应用直连 mysql Master-Master 中 1 台的 QPS 差异。


实验结果:




ps/”表示由于 Sysbench 机器 CPU 完成,完成测试,无实验结果。

• MGR QPS 物业约 100w,约 Master-Master 的 2.67 倍。


实验二:[单 ProxySQL+MGR] vs [MGR]



实验目的:

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

实验结果:



ps/”表示由于 Sysbench 机器 CPU 完成,完成测试,无实验结果。

• 单个 ProxySQL+MGR QPS 最大 48w,大约 MGR 的 48%。

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


实验结论:

在该实验中,ProxySQL Cluster 带来了约 48% 的损失,但此时 ProxySQL 的 CPU 占用率还不算高,性能探索可能进一步有效利用 ProxySQL。


实验三:【ProxySQL Cluster+MGR】对比【单个 ProxySQL+MGR】



实验目的:

确认横向扩展 ProxySQL 实例化可能进一步提升 QPS

实验结果:




ps/”表示由于 Sysbench 机器 CPU 完成,完成测试,无实验结果。

• ProxySQL+MGR QPS 集群约 89w,约为单个 ProxySQL+MGR 的 1.85 倍。

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


实验结论:

横向拓展代理 SQL 实例集合可以进一步提升 QPS 至 89w,相对接近 MGR 的小区 100w。


实验 4:【7 层负载均衡+ProxySQL Cluster+MGR】对比【ProxySQL Cluster+MGR】



实验目的:

增加读写架构中的需求均衡服务,并确认其带来的性能损失

实验结果:




ps/”表示由于 Sysbench 机器 CPU 完成,完成测试,无实验结果。

• 增加负载均衡后,QPS 时间为 42w,约 MGR 服务的 50%,1 主-主.135 倍。


实验结论:

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


实验 5:【4 层负载均衡+ProxySQL Cluster+MGR】对比【7 层负载均衡+ProxySQL Cluster+MGR】

实验目的:

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

晚上,7 层负载均衡会带来更多的网络性能。因此喂饱为 4 层负载均衡,以减少性能损失。

实验结果:




ps/”表示由于 Sysbench 机器 CPU 完成,完成测试,无实验结果。


实验结论:

将 7 层负载均衡立即 4 层负载均衡后,QPS 观看为 89w,几乎没有带来性能损失。


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

实验目的:

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

结实验果:




ps/”表示由于 Sysbench 机器 CPU 完成,完成测试,无实验结果。


实验结论:

可知机器 CPU 核数来增加 ProxySQL 的 mysql-thread 变量值,可更进一步提升 QPS。

四、总结

  1. 【4 层负载均衡+代理 SQL 集群+MGR 读写分离】适用于大量读请求场景,只读 QPS 能达到 89w,左右【应用直连 MySQL Master】的只读 QPS 37w 的 2.4 倍。

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

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

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

  5. 次次实验中,ProxySQL 集群带来约 11% 的性能损失,几乎没有带来性能损失。但是 ProxySQL 的 CPU 占用率最高只有 57%,还需要汽车探索才能进一步有效利用 ProxySQL。


参考文献:

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

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

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

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

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

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


作者简介:

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

2021-12-02 15:351491
用户头像
李冬梅 加V:busulishang4668

发布了 578 篇内容, 共 214.3 次阅读, 收获喜欢 752 次。

关注

评论

发布
暂无评论
发现更多内容

GaiaX开源解读 | 给Stretch(Rust编写的Flexbox布局引擎)新增特性,我掉了好多头发

阿里巴巴文娱技术

开源 rust 后端 rust语言 GaiaX

公交车LED电子路牌宣传效果更好吗

Dylan

LED显示屏 户外LED显示屏 led显示屏厂家

每日一题之请描述Vue组件渲染流程

bb_xiaxia1998

Vue

【12.9-12.16】写作社区优秀技术博文一览

InfoQ写作社区官方

热门活动

中移链合约常用开发介绍 (一)开发基本流程

BSN研习社

中移链

前端leetcde算法面试套路之堆

js2030code

JavaScript LeetCode

前端leetcde算法面试套路之回溯

js2030code

JavaScript LeetCode

能不能手写Vue响应式?前端面试进阶

bb_xiaxia1998

Vue

React Context源码是怎么实现的呢

flyzz177

React

用javascript分类刷leetcode23.并查集(图文视频讲解)

js2030code

JavaScript LeetCode

2022前端高频react面试题集锦

beifeng1996

React

实现Promise的原型方法--前端面试能力提升

helloworld1024fd

JavaScript

RocketMQ Connect 构建流式数据处理平台

Apache RocketMQ

RocketMQ Connect

深度剖析 | 【JVM深层系列】[HotSpotVM研究系列] JVM调优的"标准参数"的各种陷阱和坑点分析(攻克盲点及混淆点)「 1 」

洛神灬殇

JVM 12 月 PK 榜 调优参数 调优技术

深入react源码看setState究竟做了什么?

flyzz177

React

手写一个Redux,深入理解其原理-面试进阶

beifeng1996

React

几个常见的js手写题,你能写出来几道

helloworld1024fd

JavaScript

腾讯前端常考react面试题(持续更新中)

beifeng1996

React

必会vue面试题总结

bb_xiaxia1998

Vue

前端二面高频手写面试题

helloworld1024fd

JavaScript

细说react源码中的合成事件

flyzz177

React

阿里前端常考面试题集锦

loveX001

JavaScript

你是如何使用React高阶组件的?

beifeng1996

React

【敏捷转型,效能提升】敏捷转型实践系列分享

京东科技开发者

敏捷 敏捷团队 效能度量 效能提升 #DevOps

js事件循环与macro&micro任务队列-前端面试进阶

loveX001

JavaScript

2022面试官常考的前端面试题

loveX001

JavaScript

百度前端一面必会vue面试题合集

bb_xiaxia1998

Vue

js异步编程面试题你能答上来几道

loveX001

JavaScript

社招前端常考手写面试题总结

helloworld1024fd

JavaScript

Github上获赞59.8K的面试神技—1658页《Java面试突击核心讲》

架构师之道

编程 程序员 java面试

ProxySQL + MySQL MGR 自拆书架的Sysbench 只读压测报告_文化 & 方法_雷宏婧_InfoQ精选文章