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

主从配置

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

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

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

全路由

  1. schemaName: sharding_db
  2. dataSources:
  3. ds_0:
  4. url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  5. username: test
  6. password:
  7. connectionTimeoutMilliseconds: 30000
  8. idleTimeoutMilliseconds: 60000
  9. maxLifetimeMilliseconds: 1800000
  10. maxPoolSize: 200
  11. ds_1:
  12. url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  13. username: test
  14. password:
  15. connectionTimeoutMilliseconds: 30000
  16. idleTimeoutMilliseconds: 60000
  17. maxLifetimeMilliseconds: 1800000
  18. maxPoolSize: 200
  19. ds_2:
  20. url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  21. username: test
  22. password:
  23. connectionTimeoutMilliseconds: 30000
  24. idleTimeoutMilliseconds: 60000
  25. maxLifetimeMilliseconds: 1800000
  26. maxPoolSize: 200
  27. ds_3:
  28. url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
  29. username: test
  30. password:
  31. connectionTimeoutMilliseconds: 30000
  32. idleTimeoutMilliseconds: 60000
  33. maxLifetimeMilliseconds: 1800000
  34. maxPoolSize: 200
  35. rules:
  36. - !SHARDING
  37. tables:
  38. tbl:
  39. actualDataNodes: ds_${0..3}.tbl1
  40. tableStrategy:
  41. standard:
  42. shardingColumn: k
  43. shardingAlgorithmName: tbl_table_inline
  44. keyGenerateStrategy:
  45. column: id
  46. keyGeneratorName: snowflake
  47. defaultDatabaseStrategy:
  48. standard:
  49. shardingColumn: id
  50. shardingAlgorithmName: default_database_inline
  51. defaultTableStrategy:
  52. none:
  53. shardingAlgorithms:
  54. default_database_inline:
  55. type: INLINE
  56. props:
  57. algorithm-expression: ds_${id % 4}
  58. tbl_table_inline:
  59. type: INLINE
  60. props:
  61. algorithm-expression: tbl1
  62. keyGenerators:
  63. snowflake:
  64. type: SNOWFLAKE
  65. props:
  66. 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=?
  5. 全路由查询语句:
  6. SELECT max(id) FROM tbl WHERE id%4=1
  7. 单路由查询语句:
  8. SELECT id, k FROM tbl ignore index(`PRIMARY`) WHERE id=1 AND k=1
  9. INSERT+SELECT+DELETE语句:
  10. INSERT INTO tbl1(k, c, pad) VALUES(1, '###-###-###', '###-###');
  11. SELECT count(id) FROM tbl1;
  12. SELECT max(id) FROM tbl1 ignore index(`PRIMARY`);
  13. 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

历史压测数据展示

正在进行中,请等待。