阿里、微众、PingCAP专家分享如何解决可观测性带来的埋点成本上升、观测数据割裂等挑战。戳 了解详情
写点什么

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:351210
用户头像

发布了 389 篇内容, 共 114.5 次阅读, 收获喜欢 530 次。

关注

评论

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

深入浅出分布式存储性能优化方案

焱融科技

云计算 分布式 高性能 云存储 超融合

测试开发网络篇-网络协议简介

禅道项目管理

软件测试 自动化测试 测试开发

kafka基本概念

杨四正

大数据 kafka 架构设计 消息队列 消息队列架构

SparkStreaming知识点总结

五分钟学大数据

大数据 5月日更

私有云解决方案

anyRTC开发者

音视频 WebRTC RTC sdk

飞桨前沿升级、顶级开源项目、产教融合育人,WAVE SUMMIT论坛内容先睹为快!

百度大脑

深度学习 飞桨

多线程 VS 多进程(一)

若尘

多线程 多进程 Python编程 5月日更

2、kafka 2.8.0 源码环境搭建

杨四正

大数据 kafka 消息队列 kafka2.8

架构实战营模块3课后作业-基于“自研集群+MySQL存储”的消息队列架构设计方案

吴建中

架构实战营

Flume的负载均衡load balancer

大数据技术指南

flume 5月日更

Serverless:这真的是未来吗?(二)

Serverless Devs

Serverless 运维 云原生 后端 无服务器

如何成为云原生技术高阶玩家?华为云最近做了这件事

华为云开发者联盟

容器 DevOps 微服务 云原生 华为云

让人工智能成为保险行业科技基因的一部分!

百度大脑

人工智能 保险

详解JQuery框架的五大选择器

华为云开发者联盟

jquery 选择器 层级选择器 属性选择器 过滤选择器

🚄【Redis 干货领域】从底层彻底吃透 AOF 重写 (源码篇)

浩宇天尚

redis aof Redis 协议 Redis 核心技术与实战 5月日更

丰田汽车选用Mobileye和采埃孚的安全技术

新闻科技资讯

HTTP/3 初体验

运维研习社

nginx 运维 HTTP3.0 5月日更

看MindSpore加持下,如何「炼出」首个千亿参数中文预训练语言模型?

华为云开发者联盟

框架 mindspore 盘古 NLP 大模型 中文预训练模型

支付中心设计

try catch

支付 支付中心

JavaScript+TensorFlow.js让你在视频中瞬间消失

不脱发的程序猿

JavaScript 人工智能 开源 TensorFlow.js

论Http、Socket、WebSocket、WebService(SOAP)之间的区别

Xiao8

5月日更

【LeetCode】找出第 K 大的异或坐标值Java题解

HQ数字卡

算法 LeetCode 5月日更

前端实操案例丨如何实现JS向Vue传值

华为云开发者联盟

Vue 大前端 js Promise Vuex state

java性能分析与问题定位 实战

try catch

Java 性能分析

测试开发专题-开篇

禅道项目管理

软件测试 自动化测试 测试开发

人人都在谈的图数据库到底是个啥?

华为云开发者联盟

大数据 数据结构 数据 图数据库 华为云图引擎图数据库GES

千万级学生管理系统考试试卷存储方案设计

Hesher

架构 Architecture 架构实战营 存储系统

iOS开发底层原理技术~RAC深度解析

ios cocoa 程序员 移动开发

从酷睿双核到Tiger Lake-H,英特尔如何帮游戏笔记本完成蜕变

新闻科技资讯

DEMO WORLD分论坛聊些啥?高端制造、未来出行、皮肤科技、未来产业……

创业邦

创新

智能视频云3.0全景图来了!深度融合视频应用共创行业新生态

百度大脑

云智一体 智能视频 云智技术

Techo Day 腾讯技术开放日「轻量级云开发与云应用」

Techo Day 腾讯技术开放日「轻量级云开发与云应用」

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