目录
一、安装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的数据迁移、Proxy扩容(包括限流与熔断)、数据加密、影子库等功能,权当学习了。实话实说,看到性能如此让人失望,也许分库分表中间件作为AP解决方案还有点可能性,TP我是不建议用到线上使用。
参考:
https://shardingsphere.apache.org/document/current/cn/reference/test/performance-test/performance-test/



