性能测试(sysbench)

环境

推荐硬件环境

  1. CPU: 32 Cores
  2. RAM: 128 GB
  3. NIC: 10Gb Ethernet

至少需要5台机器：

  1. Jenkins * 1: ${host-jenkins}
  2. Sysbench * 1: ${host-sysbench}
  3. ShardingSphere-Proxy * 1: ${host-proxy}
  4. MySQL Server * 2: ${host-mysql-1}, ${host-mysql-2}

可以适当降低Jenkins和Sysbench机器的硬件标准

软件环境

  1. Jenins: 最新版本
  2. Sysbench: 1.0.20
  3. ShardingSphere-Proxy: master分支代码打包
  4. MySQL Server: 5.7.28

测试方案

根据以上的硬件环境，配置参数如下，参数应根据硬件环境改变而调整

ShardingSphere-Proxy配置

  1. Proxy运行在${host-proxy}机器
  2. 版本包括：Master分支版本、4.1.1版本、3.0.0版本
  3. 场景包括：config-sharding、config-replica-query、config-sharding-replica-query、config-encrypt
  4. 配置文件详细内容：见附录1

MySQL Server配置

两个MySQL实例分别运行在${host-mysql-1}和${host-mysql-2}机器

  1. 需要提前在两个实例上创建sbtest数据库
  2. 设置参数max_prepared_stmt_count = 500000
  3. 设置参数max_connections = 2000

Jenkins配置

创建6个Jenkins任务，每个任务依次调用下一个任务：（运行在${host-jenkins}机器）

  1. 1. sysbench_install: 拉取最新代码，打包Proxy压缩包

以下任务通过Jenkins slave运行在单独的Sysbench发压机器：（运行在${host-sysbench}机器）

  1. 2. sysbench_sharding: 
  2.    a. 远程部署各版本Proxy的分片场景
  3.    b. 执行Sysbench命令压测Proxy
  4.    c. 执行Sysbench命令压测MySQL Server
  5.    d. 保存Sysbench压测结果
  6.    e. 使用画图脚本生成性能曲线和表格（画图脚本见附录2）
  7. 3. sysbench_master_slave:
  8.    a. 远程部署各版本Proxy的读写分离场景
  9.    b. 执行Sysbench命令压测Proxy
  10.    c. 执行Sysbench命令压测MySQL Server
  11.    d. 保存Sysbench压测结果
  12.    e. 使用画图脚本生成性能曲线和表格
  13. 4. sysbench_sharding_master_slave:
  14.    a. 远程部署各版本Proxy的分片+读写分离场景
  15.    b. 执行Sysbench命令压测Proxy
  16.    c. 执行Sysbench命令压测MySQL Server
  17.    d. 保存Sysbench压测结果
  18.    e. 使用画图脚本生成性能曲线和表格
  19. 5. sysbench_encrypt:
  20.    a. 远程部署各版本Proxy的加密场景
  21.    b. 执行Sysbench命令压测Proxy
  22.    c. 执行Sysbench命令压测MySQL Server
  23.    d. 保存Sysbench压测结果
  24.    e. 使用画图脚本生成性能曲线和表格
  25. 6. sysbench_result_aggregation:
  26.    a. 重新对所有任务的压测结果执行画图脚本
  27.       python3 plot_graph.py sharding
  28.       python3 plot_graph.py ms
  29.       python3 plot_graph.py sharding_ms
  30.       python3 plot_graph.py encrypt
  31.    b. 使用Jenkins的Publish HTML reports插件将所有图片整合到一个HTML页面中

测试过程

以sysbench_sharding为例（其他场景类似）

进入sysbench压测结果目录

  1. cd /home/jenkins/sysbench_res/sharding

创建本次构建的文件夹

  1. mkdir $BUILD_NUMBER

取最后14次构建，保存到隐藏文件中

  1. ls -v | tail -n14 > .build_number.txt

部署及压测

步骤1 执行远程部署脚本，部署Proxy到${host-proxy}

./deploy_sharding.sh

  1. #!/bin/sh
  3. rm -fr apache-shardingsphere-*-shardingsphere-proxy-bin
  4. tar zxvf apache-shardingsphere-*-shardingsphere-proxy-bin.tar.gz
  6. sh stop_proxy.sh
  8. cp -f prepared_conf/mysql-connector-java-5.1.47.jar apache-shardingsphere-*-shardingsphere-proxy-bin/lib
  9. cp -f prepared_conf/start.sh apache-shardingsphere-*-shardingsphere-proxy-bin/bin
  10. cp -f prepared_conf/config-sharding.yaml prepared_conf/server.yaml apache-shardingsphere-*-shardingsphere-proxy-bin/conf
  12. ./apache-shardingsphere-*-shardingsphere-proxy-bin/bin/start.sh
  14. sleep 30

步骤2 执行sysbench脚本

  1. # master
  3. cd /home/jenkins/sysbench_res/sharding
  4. cd $BUILD_NUMBER
  6. sysbench oltp_read_only --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=10 --time=3600 --threads=10 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --rand-type=uniform --range_selects=off --auto_inc=off cleanup
  7. sysbench oltp_read_only --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=10 --time=3600 --threads=10 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --rand-type=uniform --range_selects=off --auto_inc=off prepare
  9. sysbench oltp_read_only        --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30  --time=180 --threads=256 --max-requests=0 --percentile=99  --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run
  10. sysbench oltp_read_only        --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30  --time=180 --threads=256 --max-requests=0 --percentile=99  --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_read_only.master.txt
  11. sysbench oltp_point_select     --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30  --time=180 --threads=256 --max-requests=0 --percentile=99  --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_point_select.master.txt
  12. sysbench oltp_read_write       --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30  --time=180 --threads=256 --max-requests=0 --percentile=99  --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_read_write.master.txt
  13. sysbench oltp_write_only       --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30  --time=180 --threads=256 --max-requests=0 --percentile=99  --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_write_only.master.txt
  14. sysbench oltp_update_index     --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30  --time=180 --threads=256 --max-requests=0 --percentile=99  --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_update_index.master.txt
  15. sysbench oltp_update_non_index --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30  --time=180 --threads=256 --max-requests=0 --percentile=99  --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_update_non_index.master.txt
  16. sysbench oltp_delete           --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30  --time=180 --threads=256 --max-requests=0 --percentile=99  --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_delete.master.txt
  18. sysbench oltp_read_only --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=10 --time=3600 --threads=10 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --rand-type=uniform --range_selects=off --auto_inc=off cleanup

4.1.1、3.0.0、直连MySQL这三个场景，重复上面步骤1和步骤2

执行停止Proxy脚本

./stop_proxy.sh

  1. #!/bin/sh
  3. ./3.0.0_sharding-proxy/bin/stop.sh 
  4. ./4.1.1_apache-shardingsphere-4.1.1-sharding-proxy-bin/bin/stop.sh
  5. ./apache-shardingsphere-*-shardingsphere-proxy-bin/bin/stop.sh

生成压测曲线图片

  1. # Generate graph
  3. cd /home/jenkins/sysbench_res/
  4. python3 plot_graph.py sharding

利用Jenkins的 Publish HTML reports插件 将图片发布到页面里

  1. HTML directory to archive: /home/jenkins/sysbench_res/graph/
  2. Index page[s]: 01_sharding.html
  3. Report title: HTML Report

sysbench测试用例分析

oltp_point_select

  1. Prepare Statement (ID = 1): SELECT c FROM sbtest1 WHERE id=?
  2. Execute Statement: ID = 1

oltp_read_only

  1. Prepare Statement (ID = 1): 'COMMIT'
  2. Prepare Statement (ID = 2): SELECT c FROM sbtest1 WHERE id=?
  4. Statement: 'BEGIN'
  5. Execute Statement: ID = 2
  6. Execute Statement: ID = 2
  7. Execute Statement: ID = 2
  8. Execute Statement: ID = 2
  9. Execute Statement: ID = 2
  10. Execute Statement: ID = 2
  11. Execute Statement: ID = 2
  12. Execute Statement: ID = 2
  13. Execute Statement: ID = 2
  14. Execute Statement: ID = 2
  15. Execute Statement: ID = 1

oltp_write_only

  1. Prepare Statement (ID = 1): 'COMMIT'
  2. Prepare Statement (ID = 2): UPDATE sbtest1 SET k=k+1 WHERE id=?
  3. Prepare Statement (ID = 3): UPDATE sbtest6 SET c=? WHERE id=?
  4. Prepare Statement (ID = 4): DELETE FROM sbtest1 WHERE id=?
  5. Prepare Statement (ID = 5): INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)
  7. Statement: 'BEGIN'
  8. Execute Statement: ID = 2
  9. Execute Statement: ID = 3
  10. Execute Statement: ID = 4
  11. Execute Statement: ID = 5
  12. Execute Statement: ID = 1

oltp_read_write

  1. Prepare Statement (ID = 1): 'COMMIT'
  2. Prepare Statement (ID = 2): SELECT c FROM sbtest1 WHERE id=?
  3. Prepare Statement (ID = 3): UPDATE sbtest3 SET k=k+1 WHERE id=?
  4. Prepare Statement (ID = 4): UPDATE sbtest10 SET c=? WHERE id=?
  5. Prepare Statement (ID = 5): DELETE FROM sbtest8 WHERE id=?
  6. Prepare Statement (ID = 6): INSERT INTO sbtest8 (id, k, c, pad) VALUES (?, ?, ?, ?)
  8. Statement: 'BEGIN'
  9. Execute Statement: ID = 2
  10. Execute Statement: ID = 2
  11. Execute Statement: ID = 2
  12. Execute Statement: ID = 2
  13. Execute Statement: ID = 2
  14. Execute Statement: ID = 2
  15. Execute Statement: ID = 2
  16. Execute Statement: ID = 2
  17. Execute Statement: ID = 2
  18. Execute Statement: ID = 2
  19. Execute Statement: ID = 3
  20. Execute Statement: ID = 4
  21. Execute Statement: ID = 5
  22. Execute Statement: ID = 6
  23. Execute Statement: ID = 1

oltp_update_index

  1. Prepare Statement (ID = 1): UPDATE sbtest1 SET k=k+1 WHERE id=?
  3. Execute Statement: ID = 1

oltp_update_non_index

  1. Prepare Statement (ID = 1): UPDATE sbtest1 SET c=? WHERE id=?
  3. Execute Statement: ID = 1

oltp_delete

  1. Prepare Statement (ID = 1): DELETE FROM sbtest1 WHERE id=?
  3. Execute Statement: ID = 1

附录1

Master branch version

server.yaml

  1. users:
  2.   - root@%:root
  3.   - sharding@:sharding
  5. props:
  6.   max-connections-size-per-query: 10
  7.   executor-size: 128  # Infinite by default.
  8.   proxy-frontend-flush-threshold: 128  # The default value is 128.
  9.     # LOCAL: Proxy will run with LOCAL transaction.
  10.     # XA: Proxy will run with XA transaction.
  11.     # BASE: Proxy will run with B.A.S.E transaction.
  12.   proxy-transaction-type: LOCAL
  13.   proxy-opentracing-enabled: false
  14.   proxy-hint-enabled: false
  15.   sql-show: false
  16.   check-table-metadata-enabled: false
  17.   lock-wait-timeout-milliseconds: 50000 # The maximum time to wait for a lock

config-sharding.yaml

  2. schemaName: sbtest
  4. dataSources:
  5.   ds_0:
  6.     url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
  7.     username: root
  8.     password:
  9.     connectionTimeoutMilliseconds: 30000
  10.     idleTimeoutMilliseconds: 60000
  11.     maxLifetimeMilliseconds: 1800000
  12.     maxPoolSize: 256
  13.     minPoolSize: 256
  14.     maintenanceIntervalMilliseconds: 30000
  15.   ds_1:
  16.     url: jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
  17.     username: root
  18.     password:
  19.     connectionTimeoutMilliseconds: 30000
  20.     idleTimeoutMilliseconds: 60000
  21.     maxLifetimeMilliseconds: 1800000
  22.     maxPoolSize: 256
  23.     minPoolSize: 256
  24.     maintenanceIntervalMilliseconds: 30000
  26. rules:
  27. - !SHARDING
  28.   tables:
  29.     sbtest1:
  30.       actualDataNodes: ds_${0..1}.sbtest1_${0..99}
  31.       tableStrategy:
  32.         standard:
  33.           shardingColumn: id
  34.           shardingAlgorithmName: table_inline_1
  35.       keyGenerateStrategy:
  36.         column: id
  37.         keyGeneratorName: snowflake
  38.     sbtest2:
  39.       actualDataNodes: ds_${0..1}.sbtest2_${0..99}
  40.       tableStrategy:
  41.         standard:
  42.           shardingColumn: id
  43.           shardingAlgorithmName: table_inline_2
  44.       keyGenerateStrategy:
  45.         column: id
  46.         keyGeneratorName: snowflake
  47.     sbtest3:
  48.       actualDataNodes: ds_${0..1}.sbtest3_${0..99}
  49.       tableStrategy:
  50.         standard:
  51.           shardingColumn: id
  52.           shardingAlgorithmName: table_inline_3
  53.       keyGenerateStrategy:
  54.         column: id
  55.         keyGeneratorName: snowflake
  56.     sbtest4:
  57.       actualDataNodes: ds_${0..1}.sbtest4_${0..99}
  58.       tableStrategy:
  59.         standard:
  60.           shardingColumn: id
  61.           shardingAlgorithmName: table_inline_4
  62.       keyGenerateStrategy:
  63.         column: id
  64.         keyGeneratorName: snowflake
  65.     sbtest5:
  66.       actualDataNodes: ds_${0..1}.sbtest5_${0..99}
  67.       tableStrategy:
  68.         standard:
  69.           shardingColumn: id
  70.           shardingAlgorithmName: table_inline_5
  71.       keyGenerateStrategy:
  72.         column: id
  73.         keyGeneratorName: snowflake
  74.     sbtest6:
  75.       actualDataNodes: ds_${0..1}.sbtest6_${0..99}
  76.       tableStrategy:
  77.         standard:
  78.           shardingColumn: id
  79.           shardingAlgorithmName: table_inline_6
  80.       keyGenerateStrategy:
  81.         column: id
  82.         keyGeneratorName: snowflake
  83.     sbtest7:
  84.       actualDataNodes: ds_${0..1}.sbtest7_${0..99}
  85.       tableStrategy:
  86.         standard:
  87.           shardingColumn: id
  88.           shardingAlgorithmName: table_inline_7
  89.       keyGenerateStrategy:
  90.         column: id
  91.         keyGeneratorName: snowflake
  92.     sbtest8:
  93.       actualDataNodes: ds_${0..1}.sbtest8_${0..99}
  94.       tableStrategy:
  95.         standard:
  96.           shardingColumn: id
  97.           shardingAlgorithmName: table_inline_8
  98.       keyGenerateStrategy:
  99.         column: id
  100.         keyGeneratorName: snowflake
  101.     sbtest9:
  102.       actualDataNodes: ds_${0..1}.sbtest9_${0..99}
  103.       tableStrategy:
  104.         standard:
  105.           shardingColumn: id
  106.           shardingAlgorithmName: table_inline_9
  107.       keyGenerateStrategy:
  108.         column: id
  109.         keyGeneratorName: snowflake
  110.     sbtest10:
  111.       actualDataNodes: ds_${0..1}.sbtest10_${0..99}
  112.       tableStrategy:
  113.         standard:
  114.           shardingColumn: id
  115.           shardingAlgorithmName: table_inline_10
  116.       keyGenerateStrategy:
  117.         column: id
  118.         keyGeneratorName: snowflake
  120.   defaultDatabaseStrategy:
  121.     standard:
  122.       shardingColumn: id
  123.       shardingAlgorithmName: database_inline
  125.   shardingAlgorithms:
  126.     database_inline:
  127.       type: INLINE
  128.       props:
  129.         algorithm-expression: ds_${id % 2}
  130.     table_inline_1:
  131.       type: INLINE
  132.       props:
  133.         algorithm-expression: sbtest1_${id % 100}
  134.     table_inline_2:
  135.       type: INLINE
  136.       props:
  137.         algorithm-expression: sbtest2_${id % 100}
  138.     table_inline_3:
  139.       type: INLINE
  140.       props:
  141.         algorithm-expression: sbtest3_${id % 100}
  142.     table_inline_4:
  143.       type: INLINE
  144.       props:
  145.         algorithm-expression: sbtest4_${id % 100}
  146.     table_inline_5:
  147.       type: INLINE
  148.       props:
  149.         algorithm-expression: sbtest5_${id % 100}
  150.     table_inline_6:
  151.       type: INLINE
  152.       props:
  153.         algorithm-expression: sbtest6_${id % 100}
  154.     table_inline_7:
  155.       type: INLINE
  156.       props:
  157.         algorithm-expression: sbtest7_${id % 100}
  158.     table_inline_8:
  159.       type: INLINE
  160.       props:
  161.         algorithm-expression: sbtest8_${id % 100}
  162.     table_inline_9:
  163.       type: INLINE
  164.       props:
  165.         algorithm-expression: sbtest9_${id % 100}
  166.     table_inline_10:
  167.       type: INLINE
  168.       props:
  169.         algorithm-expression: sbtest10_${id % 100}
  170.   keyGenerators:
  171.     snowflake:
  172.       type: SNOWFLAKE
  173.       props:
  174.         worker-id: 123

config-replica-query.yaml

  1. schemaName: sbtest
  3. dataSources:
  4.   ds_0:
  5.     url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
  6.     username: root
  7.     password:
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 128
  12.     minPoolSize: 128
  13.     maintenanceIntervalMilliseconds: 30000
  15. rules:
  16. - !READWRITE_SPLITTING
  17.   dataSources:
  18.     pr_ds:
  19.       primaryDataSourceName: ds_0
  20.       replicaDataSourceNames:
  21.         - ds_0
  22.         - ds_0

config-sharding-replica-query.yaml

  1. schemaName: sbtest
  3. dataSources:
  4.   primary_ds_0:
  5.     url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
  6.     username: root
  7.     password:
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 256
  12.     minPoolSize: 256
  13.     maintenanceIntervalMilliseconds: 30000
  14.   primary_ds_1:
  15.     url: jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
  16.     username: root
  17.     password:
  18.     connectionTimeoutMilliseconds: 30000
  19.     idleTimeoutMilliseconds: 60000
  20.     maxLifetimeMilliseconds: 1800000
  21.     maxPoolSize: 256
  22.     minPoolSize: 256
  23.     maintenanceIntervalMilliseconds: 30000
  25. rules:
  26. - !SHARDING
  27.   tables:
  28.     sbtest1:
  29.       actualDataNodes: ds_${0..1}.sbtest1_${0..99}
  30.       tableStrategy:
  31.         standard:
  32.           shardingColumn: id
  33.           shardingAlgorithmName: table_inline_1
  34.       keyGenerateStrategy:
  35.         column: id
  36.         keyGeneratorName: snowflake
  37.     sbtest2:
  38.       actualDataNodes: ds_${0..1}.sbtest2_${0..99}
  39.       tableStrategy:
  40.         standard:
  41.           shardingColumn: id
  42.           shardingAlgorithmName: table_inline_2
  43.       keyGenerateStrategy:
  44.         column: id
  45.         keyGeneratorName: snowflake
  46.     sbtest3:
  47.       actualDataNodes: ds_${0..1}.sbtest3_${0..99}
  48.       tableStrategy:
  49.         standard:
  50.           shardingColumn: id
  51.           shardingAlgorithmName: table_inline_3
  52.       keyGenerateStrategy:
  53.         column: id
  54.         keyGeneratorName: snowflake
  55.     sbtest4:
  56.       actualDataNodes: ds_${0..1}.sbtest4_${0..99}
  57.       tableStrategy:
  58.         standard:
  59.           shardingColumn: id
  60.           shardingAlgorithmName: table_inline_4
  61.       keyGenerateStrategy:
  62.         column: id
  63.         keyGeneratorName: snowflake
  64.     sbtest5:
  65.       actualDataNodes: ds_${0..1}.sbtest5_${0..99}
  66.       tableStrategy:
  67.         standard:
  68.           shardingColumn: id
  69.           shardingAlgorithmName: table_inline_5
  70.       keyGenerateStrategy:
  71.         column: id
  72.         keyGeneratorName: snowflake
  73.     sbtest6:
  74.       actualDataNodes: ds_${0..1}.sbtest6_${0..99}
  75.       tableStrategy:
  76.         standard:
  77.           shardingColumn: id
  78.           shardingAlgorithmName: table_inline_6
  79.       keyGenerateStrategy:
  80.         column: id
  81.         keyGeneratorName: snowflake
  82.     sbtest7:
  83.       actualDataNodes: ds_${0..1}.sbtest7_${0..99}
  84.       tableStrategy:
  85.         standard:
  86.           shardingColumn: id
  87.           shardingAlgorithmName: table_inline_7
  88.       keyGenerateStrategy:
  89.         column: id
  90.         keyGeneratorName: snowflake
  91.     sbtest8:
  92.       actualDataNodes: ds_${0..1}.sbtest8_${0..99}
  93.       tableStrategy:
  94.         standard:
  95.           shardingColumn: id
  96.           shardingAlgorithmName: table_inline_8
  97.       keyGenerateStrategy:
  98.         column: id
  99.         keyGeneratorName: snowflake
  100.     sbtest9:
  101.       actualDataNodes: ds_${0..1}.sbtest9_${0..99}
  102.       tableStrategy:
  103.         standard:
  104.           shardingColumn: id
  105.           shardingAlgorithmName: table_inline_9
  106.       keyGenerateStrategy:
  107.         column: id
  108.         keyGeneratorName: snowflake
  109.     sbtest10:
  110.       actualDataNodes: ds_${0..1}.sbtest10_${0..99}
  111.       tableStrategy:
  112.         standard:
  113.           shardingColumn: id
  114.           shardingAlgorithmName: table_inline_10
  115.       keyGenerateStrategy:
  116.         column: id
  117.         keyGeneratorName: snowflake
  119.   defaultDatabaseStrategy:
  120.     standard:
  121.       shardingColumn: id
  122.       shardingAlgorithmName: database_inline
  124.   shardingAlgorithms:
  125.     database_inline:
  126.       type: INLINE
  127.       props:
  128.         algorithm-expression: ds_${id % 2}
  129.     table_inline_1:
  130.       type: INLINE
  131.       props:
  132.         algorithm-expression: sbtest1_${id % 100}
  133.     table_inline_2:
  134.       type: INLINE
  135.       props:
  136.         algorithm-expression: sbtest2_${id % 100}
  137.     table_inline_3:
  138.       type: INLINE
  139.       props:
  140.         algorithm-expression: sbtest3_${id % 100}
  141.     table_inline_4:
  142.       type: INLINE
  143.       props:
  144.         algorithm-expression: sbtest4_${id % 100}
  145.     table_inline_5:
  146.       type: INLINE
  147.       props:
  148.         algorithm-expression: sbtest5_${id % 100}
  149.     table_inline_6:
  150.       type: INLINE
  151.       props:
  152.         algorithm-expression: sbtest6_${id % 100}
  153.     table_inline_7:
  154.       type: INLINE
  155.       props:
  156.         algorithm-expression: sbtest7_${id % 100}
  157.     table_inline_8:
  158.       type: INLINE
  159.       props:
  160.         algorithm-expression: sbtest8_${id % 100}
  161.     table_inline_9:
  162.       type: INLINE
  163.       props:
  164.         algorithm-expression: sbtest9_${id % 100}
  165.     table_inline_10:
  166.       type: INLINE
  167.       props:
  168.         algorithm-expression: sbtest10_${id % 100}
  169.   keyGenerators:
  170.     snowflake:
  171.       type: SNOWFLAKE
  172.       props:
  173.         worker-id: 123
  175. - !READWRITE_SPLITTING
  176.   dataSources:
  177.     ds_0:
  178.       primaryDataSourceName: primary_ds_0
  179.       replicaDataSourceNames:
  180.         - primary_ds_0
  181.         - primary_ds_0
  182.     ds_1:
  183.       name: ds_1
  184.       primaryDataSourceName: primary_ds_1
  185.       replicaDataSourceNames:
  186.         - primary_ds_1
  187.         - primary_ds_1

config-encrypt.yaml

  1. schemaName: sbtest
  3. dataSources:
  4.   ds_0:
  5.     url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
  6.     username: root
  7.     password:
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 256
  12.     minPoolSize: 256
  13.     maintenanceIntervalMilliseconds: 30000
  15. rules:
  16. - !ENCRYPT
  17.   encryptors:
  18.     md5_encryptor:
  19.       type: MD5
  20.   tables:
  21.     sbtest1:
  22.       columns:
  23.         pad:
  24.           cipherColumn: pad
  25.           encryptorName: md5_encryptor
  26.     sbtest2:
  27.       columns:
  28.         pad:
  29.           cipherColumn: pad
  30.           encryptorName: md5_encryptor
  31.     sbtest3:
  32.       columns:
  33.         pad:
  34.           cipherColumn: pad
  35.           encryptorName: md5_encryptor
  36.     sbtest4:
  37.       columns:
  38.         pad:
  39.           cipherColumn: pad
  40.           encryptorName: md5_encryptor
  41.     sbtest5:
  42.       columns:
  43.         pad:
  44.           cipherColumn: pad
  45.           encryptorName: md5_encryptor
  46.     sbtest6:
  47.       columns:
  48.         pad:
  49.           cipherColumn: pad
  50.           encryptorName: md5_encryptor
  51.     sbtest7:
  52.       columns:
  53.         pad:
  54.           cipherColumn: pad
  55.           encryptorName: md5_encryptor
  56.     sbtest8:
  57.       columns:
  58.         pad:
  59.           cipherColumn: pad
  60.           encryptorName: md5_encryptor
  61.     sbtest9:
  62.       columns:
  63.         pad:
  64.           cipherColumn: pad
  65.           encryptorName: md5_encryptor
  66.     sbtest10:
  67.       columns:
  68.         pad:
  69.           cipherColumn: pad
  70.           encryptorName: md5_encryptor

4.1.1 version

server.yaml

  1. authentication:
  2.   users:
  3.     root:
  4.       password: root
  5.     sharding:
  6.       password: sharding
  7.       authorizedSchemas: sharding_db
  9. props:
  10.   max.connections.size.per.query: 10
  11.   acceptor.size: 256  # The default value is available processors count * 2.
  12.   executor.size: 128  # Infinite by default.
  13.   proxy.frontend.flush.threshold: 128  # The default value is 128.
  14.     # LOCAL: Proxy will run with LOCAL transaction.
  15.     # XA: Proxy will run with XA transaction.
  16.     # BASE: Proxy will run with B.A.S.E transaction.
  17.   proxy.transaction.type: LOCAL
  18.   proxy.opentracing.enabled: false
  19.   proxy.hint.enabled: false
  20.   query.with.cipher.column: true
  21.   sql.show: false
  22.   allow.range.query.with.inline.sharding: false

config-sharding.yaml

  1. schemaName: sbtest
  3. dataSources:
  4.   ds_0:
  5.     url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
  6.     username: root
  7.     password:
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 256
  12.   ds_1:
  13.     url: jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
  14.     username: root
  15.     password:
  16.     connectionTimeoutMilliseconds: 30000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 256
  21. shardingRule:
  22.   tables:
  23.     sbtest1:
  24.       actualDataNodes: ds_${0..1}.sbtest1_${0..99}
  25.       tableStrategy:
  26.         inline:
  27.           shardingColumn: id
  28.           algorithmExpression: sbtest1_${id % 100}
  29.       keyGenerator:
  30.         type: SNOWFLAKE
  31.         column: id
  32.     sbtest2:
  33.       actualDataNodes: ds_${0..1}.sbtest2_${0..99}
  34.       tableStrategy:
  35.         inline:
  36.           shardingColumn: id
  37.           algorithmExpression: sbtest2_${id % 100}
  38.       keyGenerator:
  39.         type: SNOWFLAKE
  40.         column: id
  41.     sbtest3:
  42.       actualDataNodes: ds_${0..1}.sbtest3_${0..99}
  43.       tableStrategy:
  44.         inline:
  45.           shardingColumn: id
  46.           algorithmExpression: sbtest3_${id % 100}
  47.       keyGenerator:
  48.         type: SNOWFLAKE
  49.         column: id
  50.     sbtest4:
  51.       actualDataNodes: ds_${0..1}.sbtest4_${0..99}
  52.       tableStrategy:
  53.         inline:
  54.           shardingColumn: id
  55.           algorithmExpression: sbtest4_${id % 100}
  56.       keyGenerator:
  57.         type: SNOWFLAKE
  58.         column: id
  59.     sbtest5:
  60.       actualDataNodes: ds_${0..1}.sbtest5_${0..99}
  61.       tableStrategy:
  62.         inline:
  63.           shardingColumn: id
  64.           algorithmExpression: sbtest5_${id % 100}
  65.       keyGenerator:
  66.         type: SNOWFLAKE
  67.         column: id
  68.     sbtest6:
  69.       actualDataNodes: ds_${0..1}.sbtest6_${0..99}
  70.       tableStrategy:
  71.         inline:
  72.           shardingColumn: id
  73.           algorithmExpression: sbtest6_${id % 100}
  74.       keyGenerator:
  75.         type: SNOWFLAKE
  76.         column: id
  77.     sbtest7:
  78.       actualDataNodes: ds_${0..1}.sbtest7_${0..99}
  79.       tableStrategy:
  80.         inline:
  81.           shardingColumn: id
  82.           algorithmExpression: sbtest7_${id % 100}
  83.       keyGenerator:
  84.         type: SNOWFLAKE
  85.         column: id
  86.     sbtest8:
  87.       actualDataNodes: ds_${0..1}.sbtest8_${0..99}
  88.       tableStrategy:
  89.         inline:
  90.           shardingColumn: id
  91.           algorithmExpression: sbtest8_${id % 100}
  92.       keyGenerator:
  93.         type: SNOWFLAKE
  94.         column: id
  95.     sbtest9:
  96.       actualDataNodes: ds_${0..1}.sbtest9_${0..99}
  97.       tableStrategy:
  98.         inline:
  99.           shardingColumn: id
  100.           algorithmExpression: sbtest9_${id % 100}
  101.       keyGenerator:
  102.         type: SNOWFLAKE
  103.         column: id
  104.     sbtest10:
  105.       actualDataNodes: ds_${0..1}.sbtest10_${0..99}
  106.       tableStrategy:
  107.         inline:
  108.           shardingColumn: id
  109.           algorithmExpression: sbtest10_${id % 100}
  110.       keyGenerator:
  111.         type: SNOWFLAKE
  112.         column: id
  114.   defaultDatabaseStrategy:
  115.     inline:
  116.       shardingColumn: id
  117.       algorithmExpression: ds_${id % 2}

config-master_slave.yaml

  1. schemaName: sbtest
  3. dataSources:
  4.   ds_0:
  5.     url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
  6.     username: root
  7.     password:
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 256
  13. masterSlaveRule:
  14.   name: ms_ds
  15.   masterDataSourceName: ds_0
  16.   slaveDataSourceNames:
  17.     - ds_0
  18.     - ds_0

config-sharding-master_slave.yaml

  1. schemaName: sbtest
  3. dataSources:
  4.   primary_ds_0:
  5.     url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
  6.     username: root
  7.     password:
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 256
  12.   primary_ds_1:
  13.     url: jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
  14.     username: root
  15.     password:
  16.     connectionTimeoutMilliseconds: 30000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 256
  21. shardingRule:
  22.   tables:
  23.     sbtest1:
  24.       actualDataNodes: ds_${0..1}.sbtest1_${0..99}
  25.       tableStrategy:
  26.         inline:
  27.           shardingColumn: id
  28.           algorithmExpression: sbtest1_${id % 100}
  29.       keyGenerator:
  30.         type: SNOWFLAKE
  31.         column: id
  32.     sbtest2:
  33.       actualDataNodes: ds_${0..1}.sbtest2_${0..99}
  34.       tableStrategy:
  35.         inline:
  36.           shardingColumn: id
  37.           algorithmExpression: sbtest2_${id % 100}
  38.       keyGenerator:
  39.         type: SNOWFLAKE
  40.         column: id
  41.     sbtest3:
  42.       actualDataNodes: ds_${0..1}.sbtest3_${0..99}
  43.       tableStrategy:
  44.         inline:
  45.           shardingColumn: id
  46.           algorithmExpression: sbtest3_${id % 100}
  47.       keyGenerator:
  48.         type: SNOWFLAKE
  49.         column: id
  50.     sbtest4:
  51.       actualDataNodes: ds_${0..1}.sbtest4_${0..99}
  52.       tableStrategy:
  53.         inline:
  54.           shardingColumn: id
  55.           algorithmExpression: sbtest4_${id % 100}
  56.       keyGenerator:
  57.         type: SNOWFLAKE
  58.         column: id
  59.     sbtest5:
  60.       actualDataNodes: ds_${0..1}.sbtest5_${0..99}
  61.       tableStrategy:
  62.         inline:
  63.           shardingColumn: id
  64.           algorithmExpression: sbtest5_${id % 100}
  65.       keyGenerator:
  66.         type: SNOWFLAKE
  67.         column: id
  68.     sbtest6:
  69.       actualDataNodes: ds_${0..1}.sbtest6_${0..99}
  70.       tableStrategy:
  71.         inline:
  72.           shardingColumn: id
  73.           algorithmExpression: sbtest6_${id % 100}
  74.       keyGenerator:
  75.         type: SNOWFLAKE
  76.         column: id
  77.     sbtest7:
  78.       actualDataNodes: ds_${0..1}.sbtest7_${0..99}
  79.       tableStrategy:
  80.         inline:
  81.           shardingColumn: id
  82.           algorithmExpression: sbtest7_${id % 100}
  83.       keyGenerator:
  84.         type: SNOWFLAKE
  85.         column: id
  86.     sbtest8:
  87.       actualDataNodes: ds_${0..1}.sbtest8_${0..99}
  88.       tableStrategy:
  89.         inline:
  90.           shardingColumn: id
  91.           algorithmExpression: sbtest8_${id % 100}
  92.       keyGenerator:
  93.         type: SNOWFLAKE
  94.         column: id
  95.     sbtest9:
  96.       actualDataNodes: ds_${0..1}.sbtest9_${0..99}
  97.       tableStrategy:
  98.         inline:
  99.           shardingColumn: id
  100.           algorithmExpression: sbtest9_${id % 100}
  101.       keyGenerator:
  102.         type: SNOWFLAKE
  103.         column: id
  104.     sbtest10:
  105.       actualDataNodes: ds_${0..1}.sbtest10_${0..99}
  106.       tableStrategy:
  107.         inline:
  108.           shardingColumn: id
  109.           algorithmExpression: sbtest10_${id % 100}
  110.       keyGenerator:
  111.         type: SNOWFLAKE
  112.         column: id
  114.   defaultDatabaseStrategy:
  115.     inline:
  116.       shardingColumn: id
  117.       algorithmExpression: ds_${id % 2}
  119.   masterSlaveRules:
  120.     ds_0:
  121.       masterDataSourceName: primary_ds_0
  122.       slaveDataSourceNames: [primary_ds_0, primary_ds_0]
  123.       loadBalanceAlgorithmType: ROUND_ROBIN
  124.     ds_1:
  125.       masterDataSourceName: primary_ds_1
  126.       slaveDataSourceNames: [primary_ds_1, primary_ds_1]
  127.       loadBalanceAlgorithmType: ROUND_ROBIN

config-encrypt.yaml

  1. schemaName: sbtest
  3. dataSources:
  4.   ds_0:
  5.     url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
  6.     username: root
  7.     password:
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 256
  13. encryptRule:
  14.   encryptors:
  15.     encryptor_md5:
  16.       type: md5
  17.   tables:
  18.     sbtest1:
  19.       columns:
  20.         pad:
  21.           cipherColumn: pad
  22.           encryptor: encryptor_md5
  23.     sbtest2:
  24.       columns:
  25.         pad:
  26.           cipherColumn: pad
  27.           encryptor: encryptor_md5
  28.     sbtest3:
  29.       columns:
  30.         pad:
  31.           cipherColumn: pad
  32.           encryptor: encryptor_md5
  33.     sbtest4:
  34.       columns:
  35.         pad:
  36.           cipherColumn: pad
  37.           encryptor: encryptor_md5
  38.     sbtest5:
  39.       columns:
  40.         pad:
  41.           cipherColumn: pad
  42.           encryptor: encryptor_md5
  43.     sbtest6:
  44.       columns:
  45.         pad:
  46.           cipherColumn: pad
  47.           encryptor: encryptor_md5
  48.     sbtest7:
  49.       columns:
  50.         pad:
  51.           cipherColumn: pad
  52.           encryptor: encryptor_md5
  53.     sbtest8:
  54.       columns:
  55.         pad:
  56.           cipherColumn: pad
  57.           encryptor: encryptor_md5
  58.     sbtest9:
  59.       columns:
  60.         pad:
  61.           cipherColumn: pad
  62.           encryptor: encryptor_md5
  63.     sbtest10:
  64.       columns:
  65.         pad:
  66.           cipherColumn: pad
  67.           encryptor: encryptor_md5

3.0.0 version

server.yaml

  1. authentication:
  2.   username: root
  3.   password: root
  5. props:
  6.   max.connections.size.per.query: 10
  7.   acceptor.size: 256  # The default value is available processors count * 2.
  8.   executor.size: 128  # Infinite by default.
  9.   proxy.frontend.flush.threshold: 128  # The default value is 128.
  10.     # LOCAL: Proxy will run with LOCAL transaction.
  11.     # XA: Proxy will run with XA transaction.
  12.     # BASE: Proxy will run with B.A.S.E transaction.
  13.   proxy.transaction.type: LOCAL
  14.   proxy.opentracing.enabled: false
  15.   sql.show: false

config-sharding.yaml

  1. schemaName: sbtest
  3. dataSources:
  4.   ds_0:
  5.     url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
  6.     username: root
  7.     password:
  8.     autoCommit: true
  9.     connectionTimeout: 30000
  10.     idleTimeout: 60000
  11.     maxLifetime: 1800000
  12.     maximumPoolSize: 256
  13.   ds_1:
  14.     url: jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
  15.     username: root
  16.     password:
  17.     autoCommit: true
  18.     connectionTimeout: 30000
  19.     idleTimeout: 60000
  20.     maxLifetime: 1800000
  21.     maximumPoolSize: 256
  23. shardingRule:
  24.   tables:
  25.     sbtest1:
  26.       actualDataNodes: ds_${0..1}.sbtest1_${0..99}
  27.       tableStrategy:
  28.         inline:
  29.           shardingColumn: id
  30.           algorithmExpression: sbtest1_${id % 100}
  31.     sbtest2:
  32.       actualDataNodes: ds_${0..1}.sbtest2_${0..99}
  33.       tableStrategy:
  34.         inline:
  35.           shardingColumn: id
  36.           algorithmExpression: sbtest2_${id % 100}
  37.     sbtest3:
  38.       actualDataNodes: ds_${0..1}.sbtest3_${0..99}
  39.       tableStrategy:
  40.         inline:
  41.           shardingColumn: id
  42.           algorithmExpression: sbtest3_${id % 100}
  43.     sbtest4:
  44.       actualDataNodes: ds_${0..1}.sbtest4_${0..99}
  45.       tableStrategy:
  46.         inline:
  47.           shardingColumn: id
  48.           algorithmExpression: sbtest4_${id % 100}
  49.     sbtest5:
  50.       actualDataNodes: ds_${0..1}.sbtest5_${0..99}
  51.       tableStrategy:
  52.         inline:
  53.           shardingColumn: id
  54.           algorithmExpression: sbtest5_${id % 100}
  55.     sbtest6:
  56.       actualDataNodes: ds_${0..1}.sbtest6_${0..99}
  57.       tableStrategy:
  58.         inline:
  59.           shardingColumn: id
  60.           algorithmExpression: sbtest6_${id % 100}
  61.     sbtest7:
  62.       actualDataNodes: ds_${0..1}.sbtest7_${0..99}
  63.       tableStrategy:
  64.         inline:
  65.           shardingColumn: id
  66.           algorithmExpression: sbtest7_${id % 100}
  67.     sbtest8:
  68.       actualDataNodes: ds_${0..1}.sbtest8_${0..99}
  69.       tableStrategy:
  70.         inline:
  71.           shardingColumn: id
  72.           algorithmExpression: sbtest8_${id % 100}
  73.     sbtest9:
  74.       actualDataNodes: ds_${0..1}.sbtest9_${0..99}
  75.       tableStrategy:
  76.         inline:
  77.           shardingColumn: id
  78.           algorithmExpression: sbtest9_${id % 100}
  79.     sbtest10:
  80.       actualDataNodes: ds_${0..1}.sbtest10_${0..99}
  81.       tableStrategy:
  82.         inline:
  83.           shardingColumn: id
  84.           algorithmExpression: sbtest10_${id % 100}
  86.   defaultDatabaseStrategy:
  87.     inline:
  88.       shardingColumn: id
  89.       algorithmExpression: ds_${id % 2}

config-master_slave.yaml

  1. schemaName: sbtest
  3. dataSources:
  4.   ds_0:
  5.     url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
  6.     username: root
  7.     password:
  8.     autoCommit: true
  9.     connectionTimeout: 30000
  10.     idleTimeout: 60000
  11.     maxLifetime: 1800000
  12.     maximumPoolSize: 256
  14. masterSlaveRule:
  15.   name: ms_ds
  16.   masterDataSourceName: ds_0
  17.   slaveDataSourceNames:
  18.     - ds_0
  19.     - ds_0

config-sharding-master_slave.yaml

  1. schemaName: sbtest
  3. dataSources:
  4.   primary_ds_0:
  5.     url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
  6.     username: root
  7.     password:
  8.     autoCommit: true
  9.     connectionTimeout: 30000
  10.     idleTimeout: 60000
  11.     maxLifetime: 1800000
  12.     maximumPoolSize: 256
  13.   primary_ds_1:
  14.     url: jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
  15.     username: root
  16.     password:
  17.     autoCommit: true
  18.     connectionTimeout: 30000
  19.     idleTimeout: 60000
  20.     maxLifetime: 1800000
  21.     maximumPoolSize: 256
  23. shardingRule:
  24.   tables:
  25.     sbtest1:
  26.       actualDataNodes: ds_${0..1}.sbtest1_${0..99}
  27.       tableStrategy:
  28.         inline:
  29.           shardingColumn: id
  30.           algorithmExpression: sbtest1_${id % 100}
  31.     sbtest2:
  32.       actualDataNodes: ds_${0..1}.sbtest2_${0..99}
  33.       tableStrategy:
  34.         inline:
  35.           shardingColumn: id
  36.           algorithmExpression: sbtest2_${id % 100}
  37.     sbtest3:
  38.       actualDataNodes: ds_${0..1}.sbtest3_${0..99}
  39.       tableStrategy:
  40.         inline:
  41.           shardingColumn: id
  42.           algorithmExpression: sbtest3_${id % 100}
  43.     sbtest4:
  44.       actualDataNodes: ds_${0..1}.sbtest4_${0..99}
  45.       tableStrategy:
  46.         inline:
  47.           shardingColumn: id
  48.           algorithmExpression: sbtest4_${id % 100}
  49.     sbtest5:
  50.       actualDataNodes: ds_${0..1}.sbtest5_${0..99}
  51.       tableStrategy:
  52.         inline:
  53.           shardingColumn: id
  54.           algorithmExpression: sbtest5_${id % 100}
  55.     sbtest6:
  56.       actualDataNodes: ds_${0..1}.sbtest6_${0..99}
  57.       tableStrategy:
  58.         inline:
  59.           shardingColumn: id
  60.           algorithmExpression: sbtest6_${id % 100}
  61.     sbtest7:
  62.       actualDataNodes: ds_${0..1}.sbtest7_${0..99}
  63.       tableStrategy:
  64.         inline:
  65.           shardingColumn: id
  66.           algorithmExpression: sbtest7_${id % 100}
  67.     sbtest8:
  68.       actualDataNodes: ds_${0..1}.sbtest8_${0..99}
  69.       tableStrategy:
  70.         inline:
  71.           shardingColumn: id
  72.           algorithmExpression: sbtest8_${id % 100}
  73.     sbtest9:
  74.       actualDataNodes: ds_${0..1}.sbtest9_${0..99}
  75.       tableStrategy:
  76.         inline:
  77.           shardingColumn: id
  78.           algorithmExpression: sbtest9_${id % 100}
  79.     sbtest10:
  80.       actualDataNodes: ds_${0..1}.sbtest10_${0..99}
  81.       tableStrategy:
  82.         inline:
  83.           shardingColumn: id
  84.           algorithmExpression: sbtest10_${id % 100}
  86.   defaultDatabaseStrategy:
  87.     inline:
  88.       shardingColumn: id
  89.       algorithmExpression: ds_${id % 2}
  91.   masterSlaveRules:
  92.     ds_0:
  93.       masterDataSourceName: primary_ds_0
  94.       slaveDataSourceNames: [primary_ds_0, primary_ds_0]
  95.       loadBalanceAlgorithmType: ROUND_ROBIN
  96.     ds_1:
  97.       masterDataSourceName: primary_ds_1
  98.       slaveDataSourceNames: [primary_ds_1, primary_ds_1]
  99.       loadBalanceAlgorithmType: ROUND_ROBIN

config-encrypt.yaml

  1. 不支持

附录2

plot_graph.py

  1. import sys
  2. import matplotlib.pyplot as plt
  3. import numpy as np
  6. def generate_graph(path, case_name):
  7.     dataset = {
  8.         'build_num': [],
  9.         'master_version': [],
  10.         'master_xa': [],
  11.         '4.1.1_version': [],
  12.         '3.0.0_version': [],
  13.         'mysql_server': []
  14.     }
  15.     with open(path + '/.build_number.txt') as builds:
  16.         for line in builds:
  17.             dataset['build_num'].append(int(line))
  18.     generate_data(path, case_name, dataset)
  19.     print(dataset)
  20.     fig, ax = plt.subplots()
  21.     ax.grid(True)
  22.     plt.title(case_name)
  24.     data = [dataset['master_version'][-7:], dataset['master_xa'][-7:], dataset['4.1.1_version'][-7:], dataset['3.0.0_version'][-7:], dataset['mysql_server'][-7:]]
  25.     columns = dataset['build_num'][-7:]
  26.     rows = ['master', 'xa', '4.1.1', '3.0.0', 'mysql']
  27.     rcolors = plt.cm.BuPu(np.full(len(rows), 0.1))
  28.     ccolors = plt.cm.BuPu(np.full(len(columns), 0.1))
  29.     the_table = plt.table(cellText=data, rowLabels=rows, colLabels=columns, rowColours=rcolors, colColours=ccolors,
  30.                           loc='bottom', bbox=[0.0, -0.50, 1, .28])
  31.     plt.subplots_adjust(left=0.15, bottom=0.3, right=0.98)
  33.     plt.xticks(range(14))
  34.     ax.set_xticklabels(dataset['build_num'])
  35.     plt.plot(dataset['master_version'], 'o-', color='magenta', label='master_version')
  36.     plt.plot(dataset['master_xa'], 'o-', color='darkviolet', label='master_xa')
  37.     plt.plot(dataset['4.1.1_version'], 'r--', color='blue', label='4.1.1_version')
  38.     plt.plot(dataset['3.0.0_version'], 'r--', color='orange', label='3.0.0_version')
  39.     plt.plot(dataset['mysql_server'], 'r--', color='lime', label='mysql_server')
  40.     plt.xlim()
  41.     plt.legend()
  42.     plt.xlabel('build_num')
  43.     plt.ylabel('transactions per second')
  44.     plt.savefig('graph/' + path + '/' + case_name)
  45.     plt.show()
  48. def generate_data(path, case_name, dataset):
  49.     for build in dataset['build_num']:
  50.         fill_dataset(build, case_name, dataset, path, 'master_version', '.master.txt')
  51.         fill_dataset(build, case_name, dataset, path, 'master_xa', '.xa.txt')
  52.         fill_dataset(build, case_name, dataset, path, '4.1.1_version', '.4_1_1.txt')
  53.         fill_dataset(build, case_name, dataset, path, '3.0.0_version', '.3_0_0.txt')
  54.         fill_dataset(build, case_name, dataset, path, 'mysql_server', '.mysql.txt')
  57. def fill_dataset(build, case_name, dataset, path, version, suffix):
  58.     try:
  59.         with open(path + '/' + str(build) + '/' + case_name + suffix) as version_master:
  60.             value = 0
  61.             for line in version_master:
  62.                 if 'transactions:' in line:
  63.                     items = line.split('(')
  64.                     value = float(items[1][:-10])
  65.             dataset[version].append(value)
  66.     except FileNotFoundError:
  67.         dataset[version].append(0)
  70. if __name__ == '__main__':
  71.     path = sys.argv[1]
  72.     generate_graph(path, 'oltp_point_select')
  73.     generate_graph(path, 'oltp_read_only')
  74.     generate_graph(path, 'oltp_write_only')
  75.     generate_graph(path, 'oltp_read_write')
  76.     generate_graph(path, 'oltp_update_index')
  77.     generate_graph(path, 'oltp_update_non_index')
  78.     generate_graph(path, 'oltp_delete')

目前在 ShardingSphere 的 benchmark 项目 shardingsphere-benchmark 中已经共享了 sysbench 的使用方式 : sysbench 压测工具