只读压测报告 MGR ProxySQL 自拆书架的Sysbench MySQL (压测报告包含什么内容)

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

前言

1）可通过节点的只读值，自动调整它们是属于读组还是写组；

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

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

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

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

一、压测目的

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

二、压测结论

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

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

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

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

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

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

2.3.各模块和参数对剥离架构绩效的影响

三、压测详情

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 运行

清理数据：

普通变量：

• 时间：压测总持续时间（秒），后任务未完也会被终止。

• 线程：一起压测的线程数。取值范围[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。

top="6043">3.3.4 实验场景设置

共同设计了 6 个实验场景（架构图查看实验结果分析），实验目的如下：