Sysbench 性能测试

目标

对ShardingSphere-JDBC，ShardingSphere-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 表

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

测试场景配置

ShardingSphere-JDBC 使用与 ShardingSphere-Proxy 一致的配置，MySQL 直连一个库用作性能对比，下面为四个场景的具体配置：

单路由配置

  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.     standard:
  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

主从配置

  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

主从+加密+分库分表配置

  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

全路由

  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

测试结果验证

压测语句

  1. INSERT+UPDATE+DELETE语句：
  2. INSERT INTO tbl(k, c, pad) VALUES(1, '###-###-###', '###-###');
  3. UPDATE tbl SET c='####-####-####', pad='####-####' WHERE id=?;
  4. DELETE FROM tbl WHERE id=?
  6. 全路由查询语句：
  7. SELECT max(id) FROM tbl WHERE id%4=1
  9. 单路由查询语句：
  10. SELECT id, k FROM tbl ignore index(`PRIMARY`) WHERE id=1 AND k=1
  12. INSERT+SELECT+DELETE语句：
  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=?

压测类

参考shardingsphere-benchmark实现，注意阅读其中的注释

编译

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

压测执行

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

压测结果处理

注意修改为上一步生成的result.jtl的位置。

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

历史压测数据展示

正在进行中，请等待。