ShardingSphere 4.x Test engine-Performance Test

Apache ShardingSphere
5 min readSep 4, 2020

Target

The performance of Sharding-JDBC,Sharding-Proxy and MySQL would be compared here. INSERT & UPDATE & DELETE which regarded as a set of associated operation and SELECT which focus on sharding optimization are used to evaluate performance for the basic scenarios (single route, master slave & encrypt & sharding, full route). While another set of associated operation, INSERT & SELECT & DELETE, is used to evaluate performance for master slave. To achieve the result better, these tests are performed with jmeter which based on a certain amount of data with 20 concurrent threads for 30 minutes, and one MySQL has been deployed on one machine, while the scenario of MySQL used for comparison is deployed on one machine with one instance.

Test Scenarios

Single Route

On the basis of one thousand data volume, four databases that are deployed on the same machine and each contains 1024 tables with id used for database sharding and k used for table sharding are designed for this scenario,single route select sql statement is chosen here. While as a comparison, MySQL runs with INSERT & UPDATE & DELETE statement and single route select sql statement on the basis of one thousand data volume.

Master Slave

One master database and one slave database, which are deployed on different machines, are designed for this scenario based on ten thousand data volume. While as a comparison, MySQL runs with INSERT & SELECT & DELETE sql statement on the basis of ten thousand data volume.

Master Slave & Encrypt & Sharding

On the basis of one thousand data volume, four databases that are deployed on different machines and each contains 1024 tables with id used for database sharding, k used for table sharding, c encrypted with aes and pad encrypted with md5 are designed for this scenario, single route select sql statement is chosen here. While as a comparison, MySQL runs with INSERT & UPDATE & DELETE statement and single route select sql statement on the basis of one thousand data volume.

Full Route

On the basis of one thousand data volume, four databases that are deployed on different machines and each contains one table are designed for this scenario, field id is used for database sharding and k is used for table sharding, full route select sql statement is chosen here. While as a comparison, MySQL runs with INSERT & UPDATE & DELETE statement and full route select sql statement on the basis of one thousand data volume.

Testing Environment

Table Structure of Database

The structure of table here refer to sbtest in sysbench

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`)
);

Test Scenarios Configuration

The same configurations are used for Sharding-JDBC and Sharding-Proxy, while MySQL with one database connected is designed for comparision. The details for these scenarios are shown as follows.

Single Route Configuration

schemaName: sharding_dbdataSources:
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:

Master Slave Configuration

schemaName: sharding_dbdataSources:
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

Master Slave & Encrypt & Sharding Configuration

schemaName: sharding_dbdataSources:
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

Full Route Configuration

schemaName: sharding_dbdataSources:
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:

Test Result Verification

SQL Statement

INSERT+UPDATE+DELETE sql statements:
INSERT INTO tbl(k, c, pad) VALUES(1, '###-###-###', '###-###');
UPDATE tbl SET c='####-####-####', pad='####-####' WHERE id=?;
DELETE FROM tbl WHERE id=?
SELECT sql statement for full route:
SELECT max(id) FROM tbl WHERE id%4=1
SELECT sql statement for single route:
SELECT id, k FROM tbl ignore index(`PRIMARY`) WHERE id=1 AND k=1
INSERT+SELECT+DELETE sql statements:
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=?

Jmeter Class

Consider the implementation of shardingsphere-benchmark Notes: the notes in shardingsphere-benchmark/README.md should be taken attention to

Compile & Build

git clone https://github.com/apache/shardingsphere-benchmark.git
cd shardingsphere-benchmark/shardingsphere-benchmark
mvn clean install

Perform Test

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 example:https://github.com/apache/shardingsphere-benchmark/tree/master/report/script/test_plan/test.jmx

Process Result Data

Make sure the location of result.jtl file is correct.

sh shardingsphere-benchmark/report/script/gen_report.sh

Display of Historical Performance Test Data

The data of benchmark platform is show daily

--

--

Apache ShardingSphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database. https://linktr.ee/ApacheShardingSphere