Performance Test with Sysbench

Target

The performance of ShardingSphere-JDBC, ShardingSphere-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, readwrite-splitting & encrypt & sharding, full route). While another set of associated operation, INSERT & SELECT & DELETE, is used to evaluate performance for readwrite-splitting. 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.

Readwrite-splitting

One primary database and one replica 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.

Readwrite-splitting & 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

  1. CREATE TABLE `tbl` (
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  3.   `k` int(11) NOT NULL DEFAULT 0,
  4.   `c` char(120) NOT NULL DEFAULT '',
  5.   `pad` char(60) NOT NULL DEFAULT '',
  6.   PRIMARY KEY (`id`)
  7. );

Test Scenarios Configuration

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

Single Route Configuration

  1. schemaName: sharding_db
  3. dataSources:
  4.   ds_0:
  5.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  6.     username: test
  7.     password: 
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 200
  12.   ds_1:
  13.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  14.     username: test
  15.     password: 
  16.     connectionTimeoutMilliseconds: 30000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 200
  20.   ds_2:
  21.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  22.     username: test 
  23.     password: 
  24.     connectionTimeoutMilliseconds: 30000
  25.     idleTimeoutMilliseconds: 60000
  26.     maxLifetimeMilliseconds: 1800000
  27.     maxPoolSize: 200
  28.   ds_3:
  29.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  30.     username: test
  31.     password:
  32.     connectionTimeoutMilliseconds: 30000
  33.     idleTimeoutMilliseconds: 60000
  34.     maxLifetimeMilliseconds: 1800000
  35.     maxPoolSize: 200
  36. rules:
  37. - !SHARDING
  38.   tables:
  39.     tbl:
  40.       actualDataNodes: ds_${0..3}.tbl${0..1023}
  41.       tableStrategy:
  42.         standard:
  43.           shardingColumn: k
  44.           shardingAlgorithmName: tbl_table_inline
  45.       keyGenerateStrategy:
  46.           column: id
  47.           keyGeneratorName: snowflake
  48.   defaultDatabaseStrategy:
  49.     inline:
  50.       shardingColumn: id
  51.       shardingAlgorithmName: default_db_inline
  52.   defaultTableStrategy:
  53.     none:
  54.   shardingAlgorithms:
  55.     tbl_table_inline:
  56.       type: INLINE
  57.       props:
  58.         algorithm-expression: tbl${k % 1024}
  59.     default_db_inline:
  60.       type: INLINE
  61.       props:
  62.         algorithm-expression: ds_${id % 4}
  63.   keyGenerators:
  64.     snowflake:
  65.       type: SNOWFLAKE
  66.       props:
  67.         worker-id: 123

Readwrite-splitting Configuration

  1. schemaName: sharding_db
  3. dataSources:
  4.   primary_ds:
  5.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  6.     username: test
  7.     password:
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 200
  12.   replica_ds_0:
  13.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  14.     username: test
  15.     password:
  16.     connectionTimeoutMilliseconds: 30000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 200
  20. rules:
  21. - !READWRITE_SPLITTING
  22.   dataSources:
  23.     pr_ds:
  24.       writeDataSourceName: primary_ds
  25.       readDataSourceNames:
  26.         - replica_ds_0

Readwrite-splitting & Encrypt & Sharding Configuration

  1. schemaName: sharding_db
  3. dataSources:
  4.   primary_ds_0:
  5.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  6.     username: test
  7.     password:
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 200
  12.   replica_ds_0:
  13.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  14.     username: test
  15.     password:
  16.     connectionTimeoutMilliseconds: 30000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 200
  20.   primary_ds_1:
  21.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  22.     username: test
  23.     password:
  24.     connectionTimeoutMilliseconds: 30000
  25.     idleTimeoutMilliseconds: 60000
  26.     maxLifetimeMilliseconds: 1800000
  27.     maxPoolSize: 200
  28.   replica_ds_1:
  29.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  30.     username: test
  31.     password:
  32.     connectionTimeoutMilliseconds: 30000
  33.     idleTimeoutMilliseconds: 60000
  34.     maxLifetimeMilliseconds: 1800000
  35.     maxPoolSize: 200
  36.   primary_ds_2:
  37.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  38.     username: test
  39.     password:
  40.     connectionTimeoutMilliseconds: 30000
  41.     idleTimeoutMilliseconds: 60000
  42.     maxLifetimeMilliseconds: 1800000
  43.     maxPoolSize: 200
  44.   replica_ds_2:
  45.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  46.     username: test
  47.     password:
  48.     connectionTimeoutMilliseconds: 30000
  49.     idleTimeoutMilliseconds: 60000
  50.     maxLifetimeMilliseconds: 1800000
  51.     maxPoolSize: 200
  52.   primary_ds_3:
  53.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  54.     username: test
  55.     password:
  56.     connectionTimeoutMilliseconds: 30000
  57.     idleTimeoutMilliseconds: 60000
  58.     maxLifetimeMilliseconds: 1800000
  59.     maxPoolSize: 200
  60.   replica_ds_3:
  61.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  62.     username: test
  63.     password:
  64.     connectionTimeoutMilliseconds: 30000
  65.     idleTimeoutMilliseconds: 60000
  66.     maxLifetimeMilliseconds: 1800000
  67.     maxPoolSize: 200
  68. rules:
  69. - !SHARDING
  70.   tables:
  71.     tbl:
  72.       actualDataNodes: pr_ds_${0..3}.tbl${0..1023}
  73.       databaseStrategy:
  74.         standard:
  75.           shardingColumn: id
  76.           shardingAlgorithmName: tbl_database_inline
  77.       tableStrategy:
  78.         standard:
  79.           shardingColumn: k
  80.           shardingAlgorithmName: tbl_table_inline
  81.       keyGenerateStrategy:
  82.         column: id
  83.         keyGeneratorName: snowflake
  84.   bindingTables:
  85.     - tbl
  86.   defaultDataSourceName: primary_ds_1
  87.   defaultTableStrategy:
  88.     none:
  89.   shardingAlgorithms:
  90.     tbl_database_inline:
  91.       type: INLINE
  92.       props:
  93.         algorithm-expression: pr_ds_${id % 4}
  94.     tbl_table_inline:
  95.       type: INLINE
  96.       props:
  97.         algorithm-expression: tbl${k % 1024}
  98.   keyGenerators:
  99.     snowflake:
  100.       type: SNOWFLAKE
  101.       props:
  102.           worker-id: 123
  103. - !READWRITE_SPLITTING
  104.   dataSources:
  105.     pr_ds_0:
  106.       writeDataSourceName: primary_ds_0
  107.       readDataSourceNames:
  108.         - replica_ds_0
  109.       loadBalancerName: round_robin
  110.     pr_ds_1:
  111.       writeDataSourceName: primary_ds_1
  112.       readDataSourceNames:
  113.         - replica_ds_1
  114.       loadBalancerName: round_robin
  115.     pr_ds_2:
  116.       writeDataSourceName: primary_ds_2
  117.       readDataSourceNames:
  118.         - replica_ds_2
  119.       loadBalancerName: round_robin
  120.     pr_ds_3:
  121.       writeDataSourceName: primary_ds_3
  122.       readDataSourceNames:
  123.         - replica_ds_3
  124.       loadBalancerName: round_robin
  125.   loadBalancers:
  126.     round_robin:
  127.       type: ROUND_ROBIN
  128. - !ENCRYPT:
  129.   encryptors:
  130.     aes_encryptor:
  131.       type: AES
  132.       props:
  133.         aes-key-value: 123456abc
  134.     md5_encryptor:
  135.       type: MD5
  136.   tables:
  137.     sbtest:
  138.       columns:
  139.         c:
  140.           plainColumn: c_plain
  141.           cipherColumn: c_cipher
  142.           encryptorName: aes_encryptor
  143.         pad:
  144.           cipherColumn: pad_cipher
  145.           encryptorName: md5_encryptor

Full Route Configuration

  1. schemaName: sharding_db
  3. dataSources:
  4.   ds_0:
  5.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  6.     username: test
  7.     password:
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 200
  12.   ds_1:
  13.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  14.     username: test
  15.     password:
  16.     connectionTimeoutMilliseconds: 30000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 200
  20.   ds_2:
  21.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  22.     username: test
  23.     password:
  24.     connectionTimeoutMilliseconds: 30000
  25.     idleTimeoutMilliseconds: 60000
  26.     maxLifetimeMilliseconds: 1800000
  27.     maxPoolSize: 200
  28.   ds_3:
  29.     url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  30.     username: test
  31.     password:
  32.     connectionTimeoutMilliseconds: 30000
  33.     idleTimeoutMilliseconds: 60000
  34.     maxLifetimeMilliseconds: 1800000
  35.     maxPoolSize: 200
  36. rules:
  37. - !SHARDING
  38.   tables:
  39.     tbl:
  40.       actualDataNodes: ds_${0..3}.tbl1
  41.       tableStrategy:
  42.         standard:
  43.           shardingColumn: k
  44.           shardingAlgorithmName: tbl_table_inline
  45.       keyGenerateStrategy:
  46.         column: id
  47.         keyGeneratorName: snowflake
  48.   defaultDatabaseStrategy:
  49.     standard:
  50.       shardingColumn: id
  51.       shardingAlgorithmName: default_database_inline
  52.   defaultTableStrategy:
  53.     none:  
  54.   shardingAlgorithms:
  55.     default_database_inline:
  56.       type: INLINE
  57.       props:
  58.         algorithm-expression: ds_${id % 4}
  59.     tbl_table_inline:
  60.       type: INLINE
  61.       props:
  62.         algorithm-expression: tbl1    
  63.   keyGenerators:
  64.     snowflake:
  65.       type: SNOWFLAKE
  66.       props:
  67.         worker-id: 123

Test Result Verification

SQL Statement

  1. INSERT+UPDATE+DELETE sql statements:
  2. INSERT INTO tbl(k, c, pad) VALUES(1, '###-###-###', '###-###');
  3. UPDATE tbl SET c='####-####-####', pad='####-####' WHERE id=?;
  4. DELETE FROM tbl WHERE id=?
  6. SELECT sql statement for full route:
  7. SELECT max(id) FROM tbl WHERE id%4=1
  9. SELECT sql statement for single route:
  10. SELECT id, k FROM tbl ignore index(`PRIMARY`) WHERE id=1 AND k=1
  12. INSERT+SELECT+DELETE sql statements：
  13. INSERT INTO tbl1(k, c, pad) VALUES(1, '###-###-###', '###-###');
  14. SELECT count(id) FROM tbl1;
  15. SELECT max(id) FROM tbl1 ignore index(`PRIMARY`);
  16. 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

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

Perform Test

  1. cp target/shardingsphere-benchmark-1.0-SNAPSHOT-jar-with-dependencies.jar apache-jmeter-4.0/lib/ext
  2. jmeter –n –t test_plan/test.jmx
  3. 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.

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

Display of Historical Performance Test Data

In progress, please wait.