[大数据]ShardingSphere实践（5）——性能测试

目录

一、安装Sysbench

二、分场景测试

1. 直连主库

2. 单路由

3. 读写分离

4. 数据分片

参考：

? ? ? ? 从业务角度考虑，分为直连、单路由、主从、分库分表四个基本应用场景，对 ShardingSphere-Proxy 和 MySQL 进行性能对比。ShardingSphere官方文档中说明支持Sysbench和BenchmarkSQL 5.0，但是BenchmarkSQL 5.0本身不支持MySQL数据库（需要自行修改源码重新编译），因此别无选择只能使用Sysbench进行性能基准测试。

? ? ? ? 本次测试使用上篇“二、用例测试”的环境。BenchmarkSQL基准测试属于压测，为尽量减小复制延迟，将两个从库的刷盘参数设置为0，并开启组提交与多线程复制。事先在两个MySQL从库上执行如下设置：

set global sync_binlog=0;
set global innodb_flush_log_at_trx_commit=0;
set global slave_parallel_type = LOGICAL_CLOCK;
set global slave_parallel_workers = 8;

然后在主库创建测试库：

create database sysbench_test;

一、安装Sysbench

# 安装依赖
yum install automake libtool –y

? ? ? ? 从下面地址下载Sysbench源码包：
https://github.com/akopytov/sysbench/archive/refs/heads/master.zip

# 解压
unzip sysbench-master.zip
cd sysbench-master

# 编译、安装
./autogen.sh
./configure
make
make install

# 查看版本
$sysbench --version
sysbench 1.1.0

二、分场景测试

1. 直连主库

? ? ? ? 首先不通过Proxy，直连主库进行基准测试，用以结果数据对比。

? ? ? ? 准备测试数据，创建16张表，每张表一百万条数据。

sysbench \
--db-driver=mysql \
--mysql-user=wxy \
--mysql_password=mypass \
--mysql-db=sysbench_test \
--mysql-host=172.18.26.198 \
--mysql-port=3306 \
--tables=16 \
--table-size=1000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua prepare

? ? ? ? 执行测试，预热一分钟，压测5分钟，每秒输出一行报告。

sysbench \
--db-driver=mysql \
--mysql-user=wxy \
--mysql_password=mypass \
--mysql-db=sysbench_test \
--mysql-host=172.18.26.198 \
--mysql-port=3306 \
--tables=16 \
--table-size=1000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua run

? ? ? ? ?测试结果：

SQL statistics:?? ??? ?# SQL统计
? ? queries performed:
? ? ? ? read: ? ? ? ? ? ? ? ? ? ? ? ? ? ?7978602?? ??? ??? ??? ??? ? ? # 读总数
? ? ? ? write: ? ? ? ? ? ? ? ? ? ? ? ? ? 2279673?? ??? ??? ??? ??? ? ? # 写总数
? ? ? ? other: ? ? ? ? ? ? ? ? ? ? ? ? ? 1139812?? ??? ??? ??? ??? ? ? # SELECT、INSERT、UPDATE、DELETE之外的其他操作，如COMMIT等
? ? ? ? total: ? ? ? ? ? ? ? ? ? ? ? ? ? 11398087?? ??? ??? ??? ??? ? ?# 总数
? ? transactions: ? ? ? ? ? ? ? ? ? ? ? ?569922 (1899.66 per sec.)?? ? # 总事务数(每秒事务数)TPS
? ? queries: ? ? ? ? ? ? ? ? ? ? ? ? ? ? 11398087 (37992.07 per sec.) ?# 总请求数(每秒请求数)QPS
? ? ignored errors: ? ? ? ? ? ? ? ? ? ? ?0 ? ? ?(0.00 per sec.) ? ? ? ?# 忽略的总错误数(每秒忽略的错误数)
? ? reconnects: ? ? ? ? ? ? ? ? ? ? ? ? ?0 ? ? ?(0.00 per sec.)?? ??? ?# 重连总数(每秒重连数)

Throughput:?? ??? ??? ?# 吞吐量
? ? events/s (eps): ? ? ? ? ? ? ? ? ? ? ?1899.6626?? ??? ??? ??? ??? ? # 每秒事务数
? ? time elapsed: ? ? ? ? ? ? ? ? ? ? ? ?300.0125s?? ??? ??? ??? ??? ? # 总耗时
? ? total number of events: ? ? ? ? ? ? ?569922?? ??? ??? ??? ??? ??? ?# 共发生多少事务数

Latency (ms):?? ??? ?  # 响应时间
? ? ? ? ?min: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?3.76?? ??? ??? ??? ? ?# 最小耗时
? ? ? ? ?avg: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 16.84?? ??? ??? ??? ? ?# 平均耗时
? ? ? ? ?max: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2071.09?? ??? ??? ??? ? ?# 最大耗时
? ? ? ? ?95th percentile: ? ? ? ? ? ? ? ? ? ? ? 15.27?? ??? ??? ??? ? ?# 95%的测试平均耗时
? ? ? ? ?sum: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?9599728.85?? ??? ??? ??? ? ?# 总耗时

Threads fairness:?? ?  # 线程公平性
? ? events (avg/stddev): ? ? ? ? ? 17809.9375/460.26?? ??? ??? ??? ? ? # 事务(平均值/偏差)
? ? execution time (avg/stddev): ? 299.9915/0.00?? ??? ??? ??? ??? ? ? # 执行时间(平均值/偏差)

? ? ? ? 执行清理：

sysbench \
--db-driver=mysql \
--mysql-user=wxy \
--mysql_password=mypass \
--mysql-db=sysbench_test \
--mysql-host=172.18.26.198 \
--mysql-port=3306 \
--tables=16 \
--table-size=1000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua cleanup

2. 单路由

? ? ? ? 连接Proxy实例：

mysql -u root -h 172.18.10.66 -P 3307 -p123456

? ? ? ? 创建逻辑库并在其中添加资源：

create database sysbench_test;
use sysbench_test;
add resource?
sysbench_ds (host=172.18.26.198, port=3306, db=sysbench_test, user=wxy, password=mypass);

? ? ? ? 准备测试数据：

sysbench \
--db-driver=mysql \
--mysql-user=root \
--mysql_password=123456 \
--mysql-db=sysbench_test \
--mysql-host=172.18.10.66 \
--mysql-port=3307 \
--tables=16 \
--table-size=1000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua prepare

? ? ? ? 除了数据库连接改成Proxy外，其他参数不变。准备数据阶段会在当前资源（唯一数据源）中创建表并插入测试数据。在“1. 单表”中讲过，对于没有对应规则的表，创建的是单表，即所有的分片数据源中仅唯一存在的表，可用show single tables语句确认。

mysql> show single tables;
+------------+---------------+
| table_name | resource_name |
+------------+---------------+
| sbtest11 ? | sysbench_ds ? |
| sbtest9 ? ?| sysbench_ds ? |
| sbtest10 ? | sysbench_ds ? |
| sbtest13 ? | sysbench_ds ? |
| sbtest12 ? | sysbench_ds ? |
| sbtest15 ? | sysbench_ds ? |
| sbtest14 ? | sysbench_ds ? |
| sbtest16 ? | sysbench_ds ? |
| sbtest2 ? ?| sysbench_ds ? |
| sbtest1 ? ?| sysbench_ds ? |
| sbtest4 ? ?| sysbench_ds ? |
| sbtest3 ? ?| sysbench_ds ? |
| sbtest6 ? ?| sysbench_ds ? |
| sbtest5 ? ?| sysbench_ds ? |
| sbtest8 ? ?| sysbench_ds ? |
| sbtest7 ? ?| sysbench_ds ? |
+------------+---------------+
16 rows in set (0.03 sec)

? ? ? ? Proxy自动生成了单表规则：

mysql> count schema rules;
+--------------------------+-------+
| rule_name ? ? ? ? ? ? ? ?| count |
+--------------------------+-------+
| single_table ? ? ? ? ? ? | 16 ? ?|
| sharding_table ? ? ? ? ? | 0 ? ? |
| sharding_binding_table ? | 0 ? ? |
| sharding_broadcast_table | 0 ? ? |
| sharding_scaling ? ? ? ? | 0 ? ? |
| readwrite_splitting ? ? ?| 0 ? ? |
| db_discovery ? ? ? ? ? ? | 0 ? ? |
| encrypt ? ? ? ? ? ? ? ? ?| 0 ? ? |
| shadow ? ? ? ? ? ? ? ? ? | 0 ? ? |
+--------------------------+-------+
9 rows in set (0.13 sec)

? ? ? ? 执行测试：

sysbench \
--db-driver=mysql \
--mysql-user=root \
--mysql_password=123456 \
--mysql-db=sysbench_test \
--mysql-host=172.18.10.66 \
--mysql-port=3307 \
--tables=16 \
--table-size=1000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua run

? ? ? ? 测试结果：

SQL statistics:
? ? queries performed:
? ? ? ? read: ? ? ? ? ? ? ? ? ? ? ? ? ? ?100431
? ? ? ? write: ? ? ? ? ? ? ? ? ? ? ? ? ? 28770
? ? ? ? other: ? ? ? ? ? ? ? ? ? ? ? ? ? 14356
? ? ? ? total: ? ? ? ? ? ? ? ? ? ? ? ? ? 143557
? ? transactions: ? ? ? ? ? ? ? ? ? ? ? ?7194 ? (23.93 per sec.)
? ? queries: ? ? ? ? ? ? ? ? ? ? ? ? ? ? 143557 (477.47 per sec.)
? ? ignored errors: ? ? ? ? ? ? ? ? ? ? ?0 ? ? ?(0.00 per sec.)
? ? reconnects: ? ? ? ? ? ? ? ? ? ? ? ? ?0 ? ? ?(0.00 per sec.)

Throughput:
? ? events/s (eps): ? ? ? ? ? ? ? ? ? ? ?23.9271
? ? time elapsed: ? ? ? ? ? ? ? ? ? ? ? ?300.6630s
? ? total number of events: ? ? ? ? ? ? ?7194

Latency (ms):
? ? ? ? ?min: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?666.70
? ? ? ? ?avg: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1339.63
? ? ? ? ?max: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 3006.86
? ? ? ? ?95th percentile: ? ? ? ? ? ? ? ? ? ? 2120.76
? ? ? ? ?sum: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?9637273.80

Threads fairness:
? ? events (avg/stddev): ? ? ? ? ? 224.8125/16.08
? ? execution time (avg/stddev): ? 301.1648/0.42

? ? ? ? 与直连数据库对比，我们得到了“惊人”的结论：TPS从1899.66降为23.93，QPS从37992.07降为477.47，平均相应时间从16.84毫秒增加到1339.63毫秒，分别都降低了80倍。由于所有测试的SQL语句都为单路由，和直连的区别仅仅是多了一层Proxy，但性能下降如此之多大大出乎意料。

? ? ? ? 执行清理：

sysbench \
--db-driver=mysql \
--mysql-user=root \
--mysql_password=123456 \
--mysql-db=sysbench_test \
--mysql-host=172.18.10.66 \
--mysql-port=3307 \
--tables=16 \
--table-size=1000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua cleanup

3. 读写分离

? ? ? ? 重建逻辑库：

drop database if exists sysbench_test;
create database sysbench_test;
-- 切换当前数据库
use sysbench_test;
-- 添加资源
add resource?
write_ds (host=172.18.26.198, port=3306, db=sysbench_test, user=wxy, password=mypass),
read_ds1 (host=172.18.10.66, port=3306, db=sysbench_test, user=wxy, password=mypass),
read_ds2 (host=172.18.18.102, port=3306, db=sysbench_test, user=wxy, password=mypass);
-- 创建单表规则
create default single table rule resource = write_ds;

? ? ? ? 准备阶段，在主库上建表并生成测试数据。

sysbench \
--db-driver=mysql \
--mysql-user=root \
--mysql_password=123456 \
--mysql-db=sysbench_test \
--mysql-host=172.18.10.66 \
--mysql-port=3307 \
--tables=16 \
--table-size=1000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua prepare

? ? ? ? 创建读写分离规则，一主负责写，两从负责读，读负载均衡采用4比1的权重策略（从1比从2的磁盘快）。

create readwrite_splitting rule ms_group_1 (
write_resource=write_ds,
read_resources(read_ds1, read_ds2),
type(name=weight, properties(read_ds1=4,read_ds2=1)));

? ? ? ? 执行测试：

sysbench \
--db-driver=mysql \
--mysql-user=root \
--mysql_password=123456 \
--mysql-db=sysbench_test \
--mysql-host=172.18.10.66 \
--mysql-port=3307 \
--tables=16 \
--table-size=1000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua run

? ? ? ? 测试结果：

SQL statistics:
? ? queries performed:
? ? ? ? read: ? ? ? ? ? ? ? ? ? ? ? ? ? ?99842
? ? ? ? write: ? ? ? ? ? ? ? ? ? ? ? ? ? 28574
? ? ? ? other: ? ? ? ? ? ? ? ? ? ? ? ? ? 14262
? ? ? ? total: ? ? ? ? ? ? ? ? ? ? ? ? ? 142678
? ? transactions: ? ? ? ? ? ? ? ? ? ? ? ?7147 ? (23.77 per sec.)
? ? queries: ? ? ? ? ? ? ? ? ? ? ? ? ? ? 142678 (474.48 per sec.)
? ? ignored errors: ? ? ? ? ? ? ? ? ? ? ?0 ? ? ?(0.00 per sec.)
? ? reconnects: ? ? ? ? ? ? ? ? ? ? ? ? ?0 ? ? ?(0.00 per sec.)

Throughput:
? ? events/s (eps): ? ? ? ? ? ? ? ? ? ? ?23.7676
? ? time elapsed: ? ? ? ? ? ? ? ? ? ? ? ?300.7036s
? ? total number of events: ? ? ? ? ? ? ?7147

Latency (ms):
? ? ? ? ?min: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?706.29
? ? ? ? ?avg: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1348.25
? ? ? ? ?max: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2998.17
? ? ? ? ?95th percentile: ? ? ? ? ? ? ? ? ? ? 2120.76
? ? ? ? ?sum: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?9635958.07

Threads fairness:
? ? events (avg/stddev): ? ? ? ? ? 223.3438/17.75
? ? execution time (avg/stddev): ? 301.1237/0.70

? ? ? ? 本场景得出的TPS为23.77，QPS为474.48，平均相应时间为1348.25毫秒，与单路由场景基本一致，并没有看到预想的主库写性能提升，从库读性能提升的读写分离的效果。

? ? ? ? 执行清理：

sysbench \
--db-driver=mysql \
--mysql-user=root \
--mysql_password=123456 \
--mysql-db=sysbench_test \
--mysql-host=172.18.10.66 \
--mysql-port=3307 \
--tables=16 \
--table-size=1000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua cleanup

4. 数据分片

-- 创建逻辑库
drop database if exists sysbench_test;
create database sysbench_test;
-- 切换当前数据库
use sysbench_test;
-- 添加资源
add resource?
resource_1 (host=172.18.10.66, port=3306, db=db1, user=wxy, password=mypass),
resource_2 (host=172.18.10.66, port=3306, db=db2, user=wxy, password=mypass),
resource_3 (host=172.18.18.102, port=3306, db=db1, user=wxy, password=mypass),
resource_4 (host=172.18.18.102, port=3306, db=db2, user=wxy, password=mypass);
# 创建自动分片规则
create sharding table rule sbtest1 (
resources(resource_1,resource_2,resource_3,resource_4),
sharding_column=id,type(name=hash_mod,properties("sharding-count"=16)),
key_generate_strategy(column=id,type(name=snowflake)));

? ? ? ? 还有一个工作，编辑 /usr/local/share/sysbench/oltp_common.lua 文件172行：

id_def = "INTEGER NOT NULL AUTO_INCREMENT"

改为

id_def = "bigint NOT NULL AUTO_INCREMENT"

? ? ? ? Sysbench使用数据库自增生成主键，insert语句不带主键字段，因此生成snowflake分布式全局主键，得把主键数据类型改为bigint。

? ? ? ? 准备测试数据，建一个测试表，插入一千六百万行。按照规则，会在四个数据源中使用hash_mod算法平均自动分成16个分表，每个数据源4个分表，每个分表近似一百万数据。前面的数据准备阶段分分钟就执行完了，这回跑了四个小时。

sysbench \
--db-driver=mysql \
--mysql-user=root \
--mysql_password=123456 \
--mysql-db=sysbench_test \
--mysql-host=172.18.10.66 \
--mysql-port=3307 \
--tables=1 \
--table-size=16000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua prepare

? ? ? ? 执行测试：

sysbench \
--db-driver=mysql \
--mysql-user=root \
--mysql_password=123456 \
--mysql-db=sysbench_test \
--mysql-host=172.18.10.66 \
--mysql-port=3307 \
--tables=1 \
--table-size=16000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua run

? ? ? ? 测试结果：

SQL statistics:
? ? queries performed:
? ? ? ? read: ? ? ? ? ? ? ? ? ? ? ? ? ? ?24476
? ? ? ? write: ? ? ? ? ? ? ? ? ? ? ? ? ? 1775
? ? ? ? other: ? ? ? ? ? ? ? ? ? ? ? ? ? 8843
? ? ? ? total: ? ? ? ? ? ? ? ? ? ? ? ? ? 35094
? ? transactions: ? ? ? ? ? ? ? ? ? ? ? ?1775 ? (5.85 per sec.)
? ? queries: ? ? ? ? ? ? ? ? ? ? ? ? ? ? 35094 ?(115.73 per sec.)
? ? ignored errors: ? ? ? ? ? ? ? ? ? ? ?0 ? ? ?(0.00 per sec.)
? ? reconnects: ? ? ? ? ? ? ? ? ? ? ? ? ?0 ? ? ?(0.00 per sec.)

Throughput:
? ? events/s (eps): ? ? ? ? ? ? ? ? ? ? ?5.8532
? ? time elapsed: ? ? ? ? ? ? ? ? ? ? ? ?303.2524s
? ? total number of events: ? ? ? ? ? ? ?1775

Latency (ms):
? ? ? ? ?min: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 3259.44
? ? ? ? ?avg: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 5514.72
? ? ? ? ?max: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?10245.87
? ? ? ? ?95th percentile: ? ? ? ? ? ? ? ? ? ? 6835.96
? ? ? ? ?sum: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?9788623.59

Threads fairness:
? ? events (avg/stddev): ? ? ? ? ? 55.4688/0.83
? ? execution time (avg/stddev): ? 305.8945/1.70

? ? ? ? 不出所料，因为分片算法和全局主键所增加的复杂度，性能进一步下降。TPS降为5.85、QPS降为115.73，平均相应时间达到了5514.72毫秒，与直连数据库对比，下降了将近330倍。我已经开始怀疑自己的测试方法或结论分析不对，所以又用tpcc-mysql做了一遍，结果一样。我在这里斗胆预测，要用分库分表这条路解决OLTP问题，从性能上看至少目前技术是无法接受的，还是考虑使用分布式数据库靠谱些。在以前做的tpcc-mysql对比测试中（“5.6.6 消费延迟监控”），Greenplum与MySQL的QPS相差2.75倍，虽然也有差距，但总不像ShardingSphere这般夸张。

? ? ? ? 执行清理：

sysbench \
--db-driver=mysql \
--mysql-user=root \
--mysql_password=123456 \
--mysql-db=sysbench_test \
--mysql-host=172.18.10.66 \
--mysql-port=3307 \
--tables=1 \
--table-size=16000000 \
--threads=32 \
--warmup-time=60 \
--time=300 \
--events=0 \
--report-interval=1 \
/usr/local/share/sysbench/oltp_read_write.lua cleanup

? ? ? ? 本专栏后面部分还会继续演示从单实例MySQL到ShardingSphere-Proxy的弹性伸缩（数据迁移与扩容）、数据加密、影子库等功能，权当学习了。实话实说，看到性能如此让人失望，也许分库分表中间件作为AP解决方案还有点可能性，TP我是不建议用到线上使用。

参考：

https://shardingsphere.apache.org/document/current/cn/reference/test/performance-test/performance-test/

开发者涨薪指南

48位大咖的思考法则、工作方式、逻辑体系