对Sharding-JDBC,Sharding-Proxy及MySQL进行性能对比。从业务角度考虑,在基本应用场景(单路由,主从+脱敏+分库分表,全路由)下,INSERT+UPDATE+DELETE通常用作一个完整的关联操作,用于性能评估,而SELECT关注分片优化可用作性能评估的另一个操作;而主从模式下,可将INSERT+SELECT+DELETE作为一组评估性能的关联操作。 为了更好的观察效果,设计在一定数据量的基础上,使用jmeter 20并发线程持续压测半小时,进行增删改查性能测试,且每台机器部署一个MySQL实例,而对比MySQL场景为单机单实例部署。
在1000数据量的基础上分库分表,根据id
分为4个库,部署在同一台机器上,根据k
分为1024个表,查询操作路由到单库单表;
作为对比,MySQL运行在1000数据量的基础上,使用INSERT+UPDATE+DELETE和单路由查询语句。
基本主从场景,设置一主库一从库,部署在两台不同的机器上,在10000数据量的基础上,观察读写性能; 作为对比,MySQL运行在10000数据量的基础上,使用INSERT+SELECT+DELETE语句。
在1000数据量的基础上,根据id
分为4个库,部署在四台不同的机器上,根据k
分为1024个表,c
使用aes加密,pad
使用md5加密,查询操作路由到单库单表;
作为对比,MySQL运行在1000数据量的基础上,使用INSERT+UPDATE+DELETE和单路由查询语句。
在1000数据量的基础上,分库分表,根据id
分为4个库,部署在四台不同的机器上,根据k
分为1个表,查询操作使用全路由。
作为对比,MySQL运行在1000数据量的基础上,使用INSERT+UPDATE+DELETE和全路由查询语句。
此处表结构参考sysbench的sbtest表
CREATE TABLE `tbl` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT 0,
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
);
Sharding-JDBC使用与Sharding-Proxy一致的配置,MySQL直连一个库用作性能对比,下面为四个场景的具体配置:
schemaName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
ds_1:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
ds_2:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
ds_3:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
shardingRule:
tables:
tbl:
actualDataNodes: ds_${0..3}.tbl${0..1023}
tableStrategy:
inline:
shardingColumn: k
algorithmExpression: tbl${k % 1024}
keyGenerator:
type: SNOWFLAKE
column: id
defaultDatabaseStrategy:
inline:
shardingColumn: id
algorithmExpression: ds_${id % 4}
defaultTableStrategy:
none:
schemaName: sharding_db
dataSources:
master_ds:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
slave_ds_0:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
masterSlaveRule:
name: ms_ds
masterDataSourceName: master_ds
slaveDataSourceNames:
- slave_ds_0
schemaName: sharding_db
dataSources:
master_ds_0:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
slave_ds_0:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
master_ds_1:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
slave_ds_1:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
master_ds_2:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
slave_ds_2:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
master_ds_3:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
slave_ds_3:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
shardingRule:
tables:
tbl:
actualDataNodes: ms_ds_${0..3}.tbl${0..1023}
databaseStrategy:
inline:
shardingColumn: id
algorithmExpression: ms_ds_${id % 4}
tableStrategy:
inline:
shardingColumn: k
algorithmExpression: tbl${k % 1024}
keyGenerator:
type: SNOWFLAKE
column: id
bindingTables:
- tbl
defaultDataSourceName: master_ds_1
defaultTableStrategy:
none:
masterSlaveRules:
ms_ds_0:
masterDataSourceName: master_ds_0
slaveDataSourceNames:
- slave_ds_0
loadBalanceAlgorithmType: ROUND_ROBIN
ms_ds_1:
masterDataSourceName: master_ds_1
slaveDataSourceNames:
- slave_ds_1
loadBalanceAlgorithmType: ROUND_ROBIN
ms_ds_2:
masterDataSourceName: master_ds_2
slaveDataSourceNames:
- slave_ds_2
loadBalanceAlgorithmType: ROUND_ROBIN
ms_ds_3:
masterDataSourceName: master_ds_3
slaveDataSourceNames:
- slave_ds_3
loadBalanceAlgorithmType: ROUND_ROBIN
encryptRule:
encryptors:
encryptor_aes:
type: aes
props:
aes.key.value: 123456abc
encryptor_md5:
type: md5
tables:
sbtest:
columns:
c:
plainColumn: c_plain
cipherColumn: c_cipher
encryptor: encryptor_aes
pad:
cipherColumn: pad_cipher
encryptor: encryptor_md5
schemaName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
ds_1:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
ds_2:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
ds_3:
url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
username: test
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 200
shardingRule:
tables:
tbl:
actualDataNodes: ds_${0..3}.tbl1
tableStrategy:
inline:
shardingColumn: k
algorithmExpression: tbl1
keyGenerator:
type: SNOWFLAKE
column: id
defaultDatabaseStrategy:
inline:
shardingColumn: id
algorithmExpression: ds_${id % 4}
defaultTableStrategy:
none:
INSERT+UPDATE+DELETE语句:
INSERT INTO tbl(k, c, pad) VALUES(1, '###-###-###', '###-###');
UPDATE tbl SET c='####-####-####', pad='####-####' WHERE id=?;
DELETE FROM tbl WHERE id=?
全路由查询语句:
SELECT max(id) FROM tbl WHERE id%4=1
单路由查询语句:
SELECT id, k FROM tbl ignore index(`PRIMARY`) WHERE id=1 AND k=1
INSERT+SELECT+DELETE语句:
INSERT INTO tbl1(k, c, pad) VALUES(1, '###-###-###', '###-###');
SELECT count(id) FROM tbl1;
SELECT max(id) FROM tbl1 ignore index(`PRIMARY`);
DELETE FROM tbl1 WHERE id=?
参考shardingsphere-benchmark实现,注意阅读其中的注释
git clone https://github.com/apache/incubator-shardingsphere-benchmark.git
cd incubator-shardingsphere-benchmark/shardingsphere-benchmark
mvn clean install
cp target/shardingsphere-benchmark-1.0-SNAPSHOT-jar-with-dependencies.jar apache-jmeter-4.0/lib/ext
jmeter –n –t test_plan/test.jmx
test.jmx参考https://github.com/apache/incubator-shardingsphere-benchmark/tree/master/report/script/test_plan/test.jmx
注意修改为上一步生成的result.jtl的位置。
sh shardingsphere-benchmark/report/script/gen_report.sh
Benchmark性能平台是数据以天粒度展示