This is an automated email from the ASF dual-hosted git repository.
panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new b7111e5 Revert : revert the sysbench doc for previous version (#13004)
b7111e5 is described below
commit b7111e57a8a6e18ce19c00e1e5f7e59f3d6d48fa
Author: 孙念君 Sun Nianjun <[email protected]>
AuthorDate: Tue Oct 12 20:36:28 2021 +0800
Revert : revert the sysbench doc for previous version (#13004)
---
.../test-engine/performance-test-sysbench.cn.md | 1566 ++++++++++++++++++--
.../test-engine/performance-test-sysbench.en.md | 1564 +++++++++++++++++--
2 files changed, 2802 insertions(+), 328 deletions(-)
diff --git
a/docs/document/content/features/test-engine/performance-test-sysbench.cn.md
b/docs/document/content/features/test-engine/performance-test-sysbench.cn.md
index 7e903ab..1e3ff77 100644
--- a/docs/document/content/features/test-engine/performance-test-sysbench.cn.md
+++ b/docs/document/content/features/test-engine/performance-test-sysbench.cn.md
@@ -4,104 +4,490 @@ title = "性能测试(sysbench)"
weight = 5
+++
-## 目标
+## 环境
-本文旨在测试 ShardingSphere-JDBC 及 ShardingSphere-Proxy 在分片场景下与 MySQL、PostgreSQL
的性能对比
+#### 推荐硬件环境
-## 环境
+```
+CPU: 32 Cores
+RAM: 128 GB
+NIC: 10Gb Ethernet
+```
+
+至少需要5台机器：
+
+```
+Jenkins * 1: ${host-jenkins}
+Sysbench * 1: ${host-sysbench}
+ShardingSphere-Proxy * 1: ${host-proxy}
+MySQL Server * 2: ${host-mysql-1}, ${host-mysql-2}
+```
+
+可以适当降低Jenkins和Sysbench机器的硬件标准
+
+#### 软件环境
+
+```
+Jenins: 最新版本
+Sysbench: 1.0.20
+ShardingSphere-Proxy: master分支代码打包
+MySQL Server: 5.7.28
+```
+
+## 测试方案
+
+根据以上的硬件环境，配置参数如下，参数应根据硬件环境改变而调整
+
+#### ShardingSphere-Proxy配置
+
+```
+Proxy运行在${host-proxy}机器
+版本包括：Master分支版本、4.1.1版本、3.0.0版本
+场景包括：config-sharding、config-replica-query、config-sharding-replica-query、config-encrypt
+配置文件详细内容：见附录1
+```
+
+#### MySQL Server配置
+
+两个MySQL实例分别运行在${host-mysql-1}和${host-mysql-2}机器
+```
+需要提前在两个实例上创建sbtest数据库
+设置参数max_prepared_stmt_count = 500000
+设置参数max_connections = 2000
+```
+
+#### Jenkins配置
+
+创建6个Jenkins任务，每个任务依次调用下一个任务：（运行在${host-jenkins}机器）
+
+```
+1. sysbench_install: 拉取最新代码，打包Proxy压缩包
+```
-### 软件版本
+以下任务通过Jenkins slave运行在单独的Sysbench发压机器：（运行在${host-sysbench}机器）
+```
+2. sysbench_sharding:
+ a. 远程部署各版本Proxy的分片场景
+ b. 执行Sysbench命令压测Proxy
+ c. 执行Sysbench命令压测MySQL Server
+ d. 保存Sysbench压测结果
+ e. 使用画图脚本生成性能曲线和表格（画图脚本见附录2）
+3. sysbench_master_slave:
+ a. 远程部署各版本Proxy的读写分离场景
+ b. 执行Sysbench命令压测Proxy
+ c. 执行Sysbench命令压测MySQL Server
+ d. 保存Sysbench压测结果
+ e. 使用画图脚本生成性能曲线和表格
+4. sysbench_sharding_master_slave:
+ a. 远程部署各版本Proxy的分片+读写分离场景
+ b. 执行Sysbench命令压测Proxy
+ c. 执行Sysbench命令压测MySQL Server
+ d. 保存Sysbench压测结果
+ e. 使用画图脚本生成性能曲线和表格
+5. sysbench_encrypt:
+ a. 远程部署各版本Proxy的加密场景
+ b. 执行Sysbench命令压测Proxy
+ c. 执行Sysbench命令压测MySQL Server
+ d. 保存Sysbench压测结果
+ e. 使用画图脚本生成性能曲线和表格
+6. sysbench_result_aggregation:
+ a. 重新对所有任务的压测结果执行画图脚本
+ python3 plot_graph.py sharding
+ python3 plot_graph.py ms
+ python3 plot_graph.py sharding_ms
+ python3 plot_graph.py encrypt
+ b. 使用Jenkins的Publish HTML reports插件将所有图片整合到一个HTML页面中
+```
+
+## 测试过程
+
+以sysbench_sharding为例（其他场景类似）
+
+#### 进入sysbench压测结果目录
+
+```bash
+cd /home/jenkins/sysbench_res/sharding
+```
+
+#### 创建本次构建的文件夹
+
+```bash
+mkdir $BUILD_NUMBER
+```
+
+#### 取最后14次构建，保存到隐藏文件中
+
+```bash
+ls -v | tail -n14 > .build_number.txt
+```
+
+#### 部署及压测
+
+步骤1 执行远程部署脚本，部署Proxy到${host-proxy}
+
+./deploy_sharding.sh
+
+```bash
+#!/bin/sh
-| 名称 | 版本 |
-| ------------------ | ------- |
-| CentOS | 7.3.1 |
-| MySQL | 5.7 |
-| PostgreSQL | 10.0 |
-| ShardingSphere-JDBC | 5.0.0-RC1 |
-| ShardingSphere-Proxy | 5.0.0-RC1 |
+rm -fr apache-shardingsphere-*-shardingsphere-proxy-bin
+tar zxvf apache-shardingsphere-*-shardingsphere-proxy-bin.tar.gz
-### 硬件配置
+sh stop_proxy.sh
+
+cp -f prepared_conf/mysql-connector-java-5.1.47.jar
apache-shardingsphere-*-shardingsphere-proxy-bin/lib
+cp -f prepared_conf/start.sh
apache-shardingsphere-*-shardingsphere-proxy-bin/bin
+cp -f prepared_conf/config-sharding.yaml prepared_conf/server.yaml
apache-shardingsphere-*-shardingsphere-proxy-bin/conf
+
+./apache-shardingsphere-*-shardingsphere-proxy-bin/bin/start.sh
+
+sleep 30
+```
+
+步骤2 执行sysbench脚本
+
+```bash
+# master
-| **名称** | **配置** | **作用**
|
-| ----------------- | ------- |
------------------------------------------- |
-| Sysbench | 32C 64G | 发压机，通过 sysbench 对响应数据库进行测试，单独安装 |
-| ShardingSphere-Proxy | 32C 64G | 5.0.0-RC1 版本的 ShardingSphere-Proxy，单独部署 |
-| MySQL | 32C 64G | 要测试的 MySQL，与 PostgreSQL 安装在同一台机器 |
-| PostgreSQL | 32C 64G | 要测试的 PostgreSQL，与 MySQL 安装在同一台机器 |
+cd /home/jenkins/sysbench_res/sharding
+cd $BUILD_NUMBER
+
+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
+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
+
+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
+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
+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
+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
+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
+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
+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
+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
+
+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
+
+```bash
+#!/bin/sh
+
+./3.0.0_sharding-proxy/bin/stop.sh
+./4.1.1_apache-shardingsphere-4.1.1-sharding-proxy-bin/bin/stop.sh
+./apache-shardingsphere-*-shardingsphere-proxy-bin/bin/stop.sh
+```
+
+#### 生成压测曲线图片
+
+```bash
+# Generate graph
+
+cd /home/jenkins/sysbench_res/
+python3 plot_graph.py sharding
+```
+
+#### 利用Jenkins的 Publish HTML reports插件 将图片发布到页面里
+
+```
+HTML directory to archive: /home/jenkins/sysbench_res/graph/
+Index page[s]: 01_sharding.html
+Report title: HTML Report
+```
+
+## sysbench测试用例分析
+
+#### oltp_point_select
+
+```
+Prepare Statement (ID = 1): SELECT c FROM sbtest1 WHERE id=?
+Execute Statement: ID = 1
+```
+
+#### oltp_read_only
+
+```
+Prepare Statement (ID = 1): 'COMMIT'
+Prepare Statement (ID = 2): SELECT c FROM sbtest1 WHERE id=?
+
+Statement: 'BEGIN'
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 1
+```
+
+#### oltp_write_only
+
+```
+Prepare Statement (ID = 1): 'COMMIT'
+Prepare Statement (ID = 2): UPDATE sbtest1 SET k=k+1 WHERE id=?
+Prepare Statement (ID = 3): UPDATE sbtest6 SET c=? WHERE id=?
+Prepare Statement (ID = 4): DELETE FROM sbtest1 WHERE id=?
+Prepare Statement (ID = 5): INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?,
?, ?)
+
+Statement: 'BEGIN'
+Execute Statement: ID = 2
+Execute Statement: ID = 3
+Execute Statement: ID = 4
+Execute Statement: ID = 5
+Execute Statement: ID = 1
+```
+
+#### oltp_read_write
+
+```
+Prepare Statement (ID = 1): 'COMMIT'
+Prepare Statement (ID = 2): SELECT c FROM sbtest1 WHERE id=?
+Prepare Statement (ID = 3): UPDATE sbtest3 SET k=k+1 WHERE id=?
+Prepare Statement (ID = 4): UPDATE sbtest10 SET c=? WHERE id=?
+Prepare Statement (ID = 5): DELETE FROM sbtest8 WHERE id=?
+Prepare Statement (ID = 6): INSERT INTO sbtest8 (id, k, c, pad) VALUES (?, ?,
?, ?)
+
+Statement: 'BEGIN'
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 3
+Execute Statement: ID = 4
+Execute Statement: ID = 5
+Execute Statement: ID = 6
+Execute Statement: ID = 1
+```
+
+#### oltp_update_index
+
+```
+Prepare Statement (ID = 1): UPDATE sbtest1 SET k=k+1 WHERE id=?
+
+Execute Statement: ID = 1
+```
+
+#### oltp_update_non_index
+
+```
+Prepare Statement (ID = 1): UPDATE sbtest1 SET c=? WHERE id=?
+
+Execute Statement: ID = 1
+```
+
+#### oltp_delete
+
+```
+Prepare Statement (ID = 1): DELETE FROM sbtest1 WHERE id=?
+
+Execute Statement: ID = 1
+```
-## 性能测试
+## 附录1
-准备好相关配置留作测试之用（以下配置以 MySQL 为例）
+#### Master branch version
-### ShardingSphere-Proxy 分片
+server.yaml
```yaml
-schemaName: sharding_db
+users:
+ - root@%:root
+ - sharding@:sharding
+
+props:
+ max-connections-size-per-query: 10
+ kernel-executor-size: 128 # Infinite by default.
+ proxy-frontend-flush-threshold: 128 # The default value is 128.
+ proxy-opentracing-enabled: false
+ proxy-hint-enabled: false
+ sql-show: false
+ check-table-metadata-enabled: false
+ lock-wait-timeout-milliseconds: 50000 # The maximum time to wait for a lock
+```
+
+config-sharding.yaml
+
+```yaml
+
+schemaName: sbtest
dataSources:
ds_0:
- url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 50
- minPoolSize: 1
+ maxPoolSize: 256
+ minPoolSize: 256
ds_1:
- url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 50
- minPoolSize: 1
+ maxPoolSize: 256
+ minPoolSize: 256
rules:
- !SHARDING
tables:
- t_order:
- actualDataNodes: ds_${0..1}.t_order_${0..1}
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_1
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_2
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_3
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_4
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_5
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
tableStrategy:
standard:
- shardingColumn: order_id
- shardingAlgorithmName: t_order_inline
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_6
keyGenerateStrategy:
- column: order_id
+ column: id
keyGeneratorName: snowflake
- t_order_item:
- actualDataNodes: ds_${0..1}.t_order_item_${0..1}
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
tableStrategy:
standard:
- shardingColumn: order_id
- shardingAlgorithmName: t_order_item_inline
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_7
keyGenerateStrategy:
- column: order_item_id
+ column: id
keyGeneratorName: snowflake
- bindingTables:
- - t_order,t_order_item
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_8
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_9
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_10
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+
defaultDatabaseStrategy:
standard:
- shardingColumn: user_id
+ shardingColumn: id
shardingAlgorithmName: database_inline
- defaultTableStrategy:
- none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
- algorithm-expression: ds_${user_id % 2}
- t_order_inline:
+ algorithm-expression: ds_${id % 2}
+ table_inline_1:
type: INLINE
props:
- algorithm-expression: t_order_${order_id % 2}
- t_order_item_inline:
+ algorithm-expression: sbtest1_${id % 100}
+ table_inline_2:
type: INLINE
props:
- algorithm-expression: t_order_item_${order_id % 2}
-
+ algorithm-expression: sbtest2_${id % 100}
+ table_inline_3:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest3_${id % 100}
+ table_inline_4:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest4_${id % 100}
+ table_inline_5:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest5_${id % 100}
+ table_inline_6:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest6_${id % 100}
+ table_inline_7:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest7_${id % 100}
+ table_inline_8:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest8_${id % 100}
+ table_inline_9:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest9_${id % 100}
+ table_inline_10:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest10_${id % 100}
keyGenerators:
snowflake:
type: SNOWFLAKE
@@ -109,155 +495,1007 @@ rules:
worker-id: 123
```
-### ShardingSphere-JDBC 分片
+config-replica-query.yaml
```yaml
-mode:
- type: Standalone
- repository:
- type: File
- overwrite: true
+schemaName: sbtest
dataSources:
ds_0:
- dataSourceClassName: com.zaxxer.hikari.HikariDataSource
- driverClassName: com.mysql.jdbc.Driver
- jdbcUrl:
jdbc:mysql://localhost:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
username: root
password:
- ds_1:
- dataSourceClassName: com.zaxxer.hikari.HikariDataSource
- driverClassName: com.mysql.jdbc.Driver
- jdbcUrl:
jdbc:mysql://localhost:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 128
+ minPoolSize: 128
+
+rules:
+- !READWRITE_SPLITTING
+ dataSources:
+ pr_ds:
+ primaryDataSourceName: ds_0
+ replicaDataSourceNames:
+ - ds_0
+ - ds_0
+```
+
+config-sharding-replica-query.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ primary_ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
username: root
password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+ minPoolSize: 256
+ primary_ds_1:
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+ minPoolSize: 256
rules:
- !SHARDING
tables:
- t_order:
- actualDataNodes: ds_${0..1}.t_order
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_1
keyGenerateStrategy:
- column: order_id
+ column: id
keyGeneratorName: snowflake
- t_order_item:
- actualDataNodes: ds_${0..1}.t_order_item
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_2
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_3
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_4
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_5
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_6
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_7
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_8
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_9
keyGenerateStrategy:
- column: order_item_id
+ column: id
keyGeneratorName: snowflake
- bindingTables:
- - t_order,t_order_item
- broadcastTables:
- - t_address
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_10
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+
defaultDatabaseStrategy:
standard:
- shardingColumn: user_id
+ shardingColumn: id
shardingAlgorithmName: database_inline
- defaultTableStrategy:
- none:
-
+
shardingAlgorithms:
database_inline:
type: INLINE
props:
- algorithm-expression: ds_${user_id % 2}
-
+ algorithm-expression: ds_${id % 2}
+ table_inline_1:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest1_${id % 100}
+ table_inline_2:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest2_${id % 100}
+ table_inline_3:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest3_${id % 100}
+ table_inline_4:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest4_${id % 100}
+ table_inline_5:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest5_${id % 100}
+ table_inline_6:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest6_${id % 100}
+ table_inline_7:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest7_${id % 100}
+ table_inline_8:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest8_${id % 100}
+ table_inline_9:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest9_${id % 100}
+ table_inline_10:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest10_${id % 100}
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
- worker-id: 123
+ worker-id: 123
+
+- !READWRITE_SPLITTING
+ dataSources:
+ ds_0:
+ primaryDataSourceName: primary_ds_0
+ replicaDataSourceNames:
+ - primary_ds_0
+ - primary_ds_0
+ ds_1:
+ name: ds_1
+ primaryDataSourceName: primary_ds_1
+ replicaDataSourceNames:
+ - primary_ds_1
+ - primary_ds_1
+```
+
+config-encrypt.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+ minPoolSize: 256
+
+rules:
+- !ENCRYPT
+ encryptors:
+ md5_encryptor:
+ type: MD5
+ tables:
+ sbtest1:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest2:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest3:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest4:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest5:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest6:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest7:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest8:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest9:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest10:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+```
+
+#### 4.1.1 version
+
+server.yaml
+
+```yaml
+authentication:
+ users:
+ root:
+ password: root
+ sharding:
+ password: sharding
+ authorizedSchemas: sharding_db
props:
- sql-show: false
+ max.connections.size.per.query: 10
+ acceptor.size: 256 # The default value is available processors count * 2.
+ executor.size: 128 # Infinite by default.
+ proxy.frontend.flush.threshold: 128 # The default value is 128.
+ # LOCAL: Proxy will run with LOCAL transaction.
+ # XA: Proxy will run with XA transaction.
+ # BASE: Proxy will run with B.A.S.E transaction.
+ proxy.transaction.type: LOCAL
+ proxy.opentracing.enabled: false
+ proxy.hint.enabled: false
+ query.with.cipher.column: true
+ sql.show: false
+ allow.range.query.with.inline.sharding: false
+```
+
+config-sharding.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+ ds_1:
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+
+shardingRule:
+ tables:
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest1_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest2_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest3_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest4_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest5_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest6_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest7_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest8_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest9_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest10_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+
+ defaultDatabaseStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: ds_${id % 2}
```
-[](https://shardingsphere.apache.org/document/current/img/stress-test/sysbench_test_process.jpg)
+config-master_slave.yaml
-Sysbench 是一款基于 LuaJIT 的可编写脚本的多线程基准测试工具。它最常用于数据库基准测试。
-Sysbench 自带的脚本，包含了很多常见的场景，可以非常有效的对数据库的性能进行测试。
+```yaml
+schemaName: sbtest
-| 脚本名称 | 执行sql
|
-| ----------------- |
------------------------------------------------------------ |
-| oltp\_point\_select | SELECT c FROM sbtest1 WHERE id=?
|
-| oltp\_read\_only | COMMIT <br> SELECT c FROM sbtest1 WHERE id=? |
-| oltp\_write\_only | COMMIT <br> UPDATE sbtest1 SET k=k+1 WHERE id=?
<br> UPDATE sbtest6 SET c=? WHERE id=? <br> DELETE FROM sbtest1 WHERE id=?
<br> INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) <br> BEGIN |
-| oltp\_read\_write | COMMIT <br> SELECT c FROM sbtest1 WHERE id=?
<br> UPDATE sbtest3 SET k=k+1 WHERE id=? <br> UPDATE sbtest10 SET c=? WHERE
id=? <br> DELETE FROM sbtest8 WHERE id=? <br> INSERT INTO sbtest8 (id, k, c,
pad) VALUES (?, ?, ?, ?) <br> BEGIN |
-| oltp\_update\_index | UPDATE sbtest1 SET k=k+1 WHERE id=? |
-| oltp\_update\_non\_index | UPDATE sbtest1 SET c=? WHERE id=? |
-| oltp\_delete | DELETE FROM sbtest1 WHERE id=? |
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
-通过 sysbench 分别测试 `Proxy + Database + 分片`、`直连 Database` 进行横向对比。
+masterSlaveRule:
+ name: ms_ds
+ masterDataSourceName: ds_0
+ slaveDataSourceNames:
+ - ds_0
+ - ds_0
+```
-如下脚本为 sysbench 压测 proxy 的相应命令：
+config-sharding-master_slave.yaml
-```bash
-# clean and prepare the test data for sysbench. need to create a schema called
sbtest before execute the following command
-sysbench oltp\_read\_only --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--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
-sysbench oltp\_read\_only --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--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
-
-# start to test by corresponding script
-sysbench oltp\_read\_only --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run
-sysbench oltp\_read\_only --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_read\_only.txt
-sysbench oltp\_point\_select --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_point\_select.txt
-sysbench oltp\_read\_write --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_read\_write.txt
-sysbench oltp\_write\_only --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_write\_only.txt
-sysbench oltp\_update\_index --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_update\_index.txt
-sysbench oltp\_update\_non\_index --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_update\_non\_index.txt
-sysbench oltp\_delete --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_delete.txt
-```
-
-## 测试结果
-
-`point_select` 作为最基础的测试用例，这里我们以 `point_select` 为基础测试脚本，横向对比不同数据库以及
ShardingSphere 的性能。如下即为对应的数据库以及 ShardingSphere 产品的 QPS
-
-| 线程数 | MySQL | ShardingSphere-Proxy(分片) | ShardingSphere-JDBC(分片 by JMH) |
-| :----- | :------ | :-------------------------- |
:------------------------------- |
-| 20 | 154,408 | 50,042 | 101,687
|
-| 100 | 283,918 | 107,488 | 245,676
|
-| 200 | 281,902 | 110,278 | 252,621
|
-
-> Sysbench 是由 C 语言编写的，所以无法直接测试 ShardingSphere-JDBC，这里对 ShardingSphere-JDBC
的测试使用的是 OpenJDK 自带的压测工具 JMH
-
-其他测试结果
-
-### MySQL 的测试结果：
-| MySQL | oltp\_read\_only | oltp\_point\_select | oltp\_read\_write |
oltp\_write\_only | oltp\_update\_index | oltp\_update\_non\_index |
oltp\_delete |
-| --------- | -------------- | ----------------- | --------------- |
--------------- | ----------------- | --------------------- | ----------- |
-| thread20 | 172,640 | 154,408 | 63,520 | 33,890
| 12,779 | 14,256 | 24,318 |
-| thread100 | 308,513 | 283,918 | 107,942 | 50,664
| 18,659 | 18,350 | 29,799 |
-| thread200 | 309,668 | 281,902 | 125,311 | 64,977
| 21,181 | 20,587 | 34,745 |
-
-### ShardingSphere-Proxy + MySQL + 分片的测试结果：
-| ShardingSphere-Proxy\_Sharding\_MySQL | oltp\_read\_only |
oltp\_point\_select | oltp\_read\_write | oltp\_write\_only |
oltp\_update\_index | oltp\_update\_non\_index | oltp\_delete |
-| ----------------------------------- | -------------- | ----------------- |
--------------- | --------------- | ----------------- | --------------------- |
----------- |
-| thread20 | 53,953 | 50,042 |
41,929 | 36,395 | 21,700 | 23,863 |
34,000 |
-| thread100 | 117,897 | 107,488 |
104,338 | 74,393 | 38,222 | 39,742 |
93,573 |
-| thread200 | 113,608 | 110,278 |
110,829 | 84,354 | 46,583 | 45,283 |
104,681 |
-
-### ShardingSphere-JDBC + MySQL + 分片的测试结果：
-| ShardingSphere-JDBC\_Sharding\_MySQL | oltp\_point\_select |
-| ---------------------------------- | ----------------- |
-| thread20 | 101,687 |
-| thread100 | 245,676 |
-| thread200 | 252,621 |
-
-### PostgreSQL 的测试结果：
-| PostgreSQL | oltp\_read\_only | oltp\_point\_select | oltp\_read\_write |
oltp\_write\_only | oltp\_update\_index | oltp\_update\_non\_index |
oltp\_delete |
-| ---------- | -------------- | ----------------- | --------------- |
--------------- | ----------------- | --------------------- | ----------- |
-| thread100 | 364,045 | 302,767 | 3,300 | 1,469
| 704 | 1,236 | 1,460 |
-| thread200 | 347,426 | 280,177 | 3,261 | 1,575
| 688 | 1,209 | 1,518 |
-
-### ShardingSphere-Proxy + PostgreSQL + 分片的测试结果：
-| ShardingSphere-Proxy\_Sharding\_PostgreSQL | oltp\_read\_only |
oltp\_point\_select | oltp\_read\_write | oltp\_write\_only |
oltp\_update\_index | oltp\_update\_non\_index | oltp\_delete |
-| ---------------------------------------- | -------------- |
----------------- | --------------- | --------------- | ----------------- |
--------------------- | ----------- |
-| thread20 | 52,831 | 56,259
| 2,666 | 1,233 | 583 | 826
| 989 |
-| thread100 | 121,476 | 126,167
| 3,187 | 1,160 | 555 | 827
| 1,053 |
-| thread200 | 118,351 | 122,423
| 3,254 | 1,125 | 544 | 785
| 1,016 |
-
-### ShardingSphere-JDBC + PostgreSQL + 分片的测试结果：
-| ShardingSphere-JDBC\_Sharding\_PostgreSQL | oltp\_point\_select |
-| --------------------------------------- | ----------------- |
-| thread20 | 112,977 |
-| thread100 | 280,439 |
-| thread200 | 284,474 |
+```yaml
+schemaName: sbtest
+
+dataSources:
+ primary_ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+ primary_ds_1:
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+
+shardingRule:
+ tables:
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest1_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest2_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest3_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest4_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest5_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest6_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest7_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest8_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest9_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest10_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+
+ defaultDatabaseStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: ds_${id % 2}
+
+ masterSlaveRules:
+ ds_0:
+ masterDataSourceName: primary_ds_0
+ slaveDataSourceNames: [primary_ds_0, primary_ds_0]
+ loadBalanceAlgorithmType: ROUND_ROBIN
+ ds_1:
+ masterDataSourceName: primary_ds_1
+ slaveDataSourceNames: [primary_ds_1, primary_ds_1]
+ loadBalanceAlgorithmType: ROUND_ROBIN
+```
+
+config-encrypt.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+
+encryptRule:
+ encryptors:
+ encryptor_md5:
+ type: md5
+ tables:
+ sbtest1:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest2:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest3:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest4:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest5:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest6:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest7:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest8:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest9:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest10:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+```
+
+#### 3.0.0 version
+
+server.yaml
+
+```yaml
+authentication:
+ username: root
+ password: root
+
+props:
+ max.connections.size.per.query: 10
+ acceptor.size: 256 # The default value is available processors count * 2.
+ executor.size: 128 # Infinite by default.
+ proxy.frontend.flush.threshold: 128 # The default value is 128.
+ # LOCAL: Proxy will run with LOCAL transaction.
+ # XA: Proxy will run with XA transaction.
+ # BASE: Proxy will run with B.A.S.E transaction.
+ proxy.transaction.type: LOCAL
+ proxy.opentracing.enabled: false
+ sql.show: false
+```
+
+config-sharding.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ autoCommit: true
+ connectionTimeout: 30000
+ idleTimeout: 60000
+ maxLifetime: 1800000
+ maximumPoolSize: 256
+ ds_1:
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ autoCommit: true
+ connectionTimeout: 30000
+ idleTimeout: 60000
+ maxLifetime: 1800000
+ maximumPoolSize: 256
+
+shardingRule:
+ tables:
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest1_${id % 100}
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest2_${id % 100}
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest3_${id % 100}
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest4_${id % 100}
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest5_${id % 100}
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest6_${id % 100}
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest7_${id % 100}
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest8_${id % 100}
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest9_${id % 100}
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest10_${id % 100}
+
+ defaultDatabaseStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: ds_${id % 2}
+```
+
+config-master_slave.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ autoCommit: true
+ connectionTimeout: 30000
+ idleTimeout: 60000
+ maxLifetime: 1800000
+ maximumPoolSize: 256
+
+masterSlaveRule:
+ name: ms_ds
+ masterDataSourceName: ds_0
+ slaveDataSourceNames:
+ - ds_0
+ - ds_0
+```
+
+config-sharding-master_slave.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ primary_ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ autoCommit: true
+ connectionTimeout: 30000
+ idleTimeout: 60000
+ maxLifetime: 1800000
+ maximumPoolSize: 256
+ primary_ds_1:
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ autoCommit: true
+ connectionTimeout: 30000
+ idleTimeout: 60000
+ maxLifetime: 1800000
+ maximumPoolSize: 256
+
+shardingRule:
+ tables:
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest1_${id % 100}
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest2_${id % 100}
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest3_${id % 100}
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest4_${id % 100}
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest5_${id % 100}
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest6_${id % 100}
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest7_${id % 100}
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest8_${id % 100}
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest9_${id % 100}
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest10_${id % 100}
+
+ defaultDatabaseStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: ds_${id % 2}
+
+ masterSlaveRules:
+ ds_0:
+ masterDataSourceName: primary_ds_0
+ slaveDataSourceNames: [primary_ds_0, primary_ds_0]
+ loadBalanceAlgorithmType: ROUND_ROBIN
+ ds_1:
+ masterDataSourceName: primary_ds_1
+ slaveDataSourceNames: [primary_ds_1, primary_ds_1]
+ loadBalanceAlgorithmType: ROUND_ROBIN
+```
+
+config-encrypt.yaml
+
+```
+不支持
+```
+
+## 附录2
+
+plot_graph.py
+
+```python
+import sys
+import matplotlib.pyplot as plt
+import numpy as np
+
+
+def generate_graph(path, case_name):
+ dataset = {
+ 'build_num': [],
+ 'master_version': [],
+ 'master_xa': [],
+ '4.1.1_version': [],
+ '3.0.0_version': [],
+ 'mysql_server': []
+ }
+ with open(path + '/.build_number.txt') as builds:
+ for line in builds:
+ dataset['build_num'].append(int(line))
+ generate_data(path, case_name, dataset)
+ print(dataset)
+ fig, ax = plt.subplots()
+ ax.grid(True)
+ plt.title(case_name)
+
+ 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:]]
+ columns = dataset['build_num'][-7:]
+ rows = ['master', 'xa', '4.1.1', '3.0.0', 'mysql']
+ rcolors = plt.cm.BuPu(np.full(len(rows), 0.1))
+ ccolors = plt.cm.BuPu(np.full(len(columns), 0.1))
+ the_table = plt.table(cellText=data, rowLabels=rows, colLabels=columns,
rowColours=rcolors, colColours=ccolors,
+ loc='bottom', bbox=[0.0, -0.50, 1, .28])
+ plt.subplots_adjust(left=0.15, bottom=0.3, right=0.98)
+
+ plt.xticks(range(14))
+ ax.set_xticklabels(dataset['build_num'])
+ plt.plot(dataset['master_version'], 'o-', color='magenta',
label='master_version')
+ plt.plot(dataset['master_xa'], 'o-', color='darkviolet', label='master_xa')
+ plt.plot(dataset['4.1.1_version'], 'r--', color='blue',
label='4.1.1_version')
+ plt.plot(dataset['3.0.0_version'], 'r--', color='orange',
label='3.0.0_version')
+ plt.plot(dataset['mysql_server'], 'r--', color='lime',
label='mysql_server')
+ plt.xlim()
+ plt.legend()
+ plt.xlabel('build_num')
+ plt.ylabel('transactions per second')
+ plt.savefig('graph/' + path + '/' + case_name)
+ plt.show()
+
+
+def generate_data(path, case_name, dataset):
+ for build in dataset['build_num']:
+ fill_dataset(build, case_name, dataset, path, 'master_version',
'.master.txt')
+ fill_dataset(build, case_name, dataset, path, 'master_xa', '.xa.txt')
+ fill_dataset(build, case_name, dataset, path, '4.1.1_version',
'.4_1_1.txt')
+ fill_dataset(build, case_name, dataset, path, '3.0.0_version',
'.3_0_0.txt')
+ fill_dataset(build, case_name, dataset, path, 'mysql_server',
'.mysql.txt')
+
+
+def fill_dataset(build, case_name, dataset, path, version, suffix):
+ try:
+ with open(path + '/' + str(build) + '/' + case_name + suffix) as
version_master:
+ value = 0
+ for line in version_master:
+ if 'transactions:' in line:
+ items = line.split('(')
+ value = float(items[1][:-10])
+ dataset[version].append(value)
+ except FileNotFoundError:
+ dataset[version].append(0)
+
+
+if __name__ == '__main__':
+ path = sys.argv[1]
+ generate_graph(path, 'oltp_point_select')
+ generate_graph(path, 'oltp_read_only')
+ generate_graph(path, 'oltp_write_only')
+ generate_graph(path, 'oltp_read_write')
+ generate_graph(path, 'oltp_update_index')
+ generate_graph(path, 'oltp_update_non_index')
+ generate_graph(path, 'oltp_delete')
+```
+
+目前在 ShardingSphere 的 benchmark 项目
[shardingsphere-benchmark](https://github.com/apache/shardingsphere-benchmark)
中已经共享了 sysbench 的使用方式 : [sysbench
压测工具](https://github.com/apache/shardingsphere-benchmark/blob/master/sysbench/README_ZH.md)
diff --git
a/docs/document/content/features/test-engine/performance-test-sysbench.en.md
b/docs/document/content/features/test-engine/performance-test-sysbench.en.md
index cb8dadf..923cfbb 100644
--- a/docs/document/content/features/test-engine/performance-test-sysbench.en.md
+++ b/docs/document/content/features/test-engine/performance-test-sysbench.en.md
@@ -4,104 +4,490 @@ title = "Performance Test(sysbench)"
weight = 5
+++
-## Target
+## Environment
-This pressure test is for the performance compare between
ShardingSphere-JDBC,ShardingSphere-Proxy in Sharding Rule to MySQL,PostgreSQL
+#### Recommended Hardware
-## Environment
+```
+CPU: 32 Cores
+RAM: 128 GB
+NIC: 10Gb Ethernet
+```
+
+At least 5 machines are required:
+
+```
+Jenkins * 1: ${host-jenkins}
+Sysbench * 1: ${host-sysbench}
+ShardingSphere-Proxy * 1: ${host-proxy}
+MySQL Server * 2: ${host-mysql-1}, ${host-mysql-2}
+```
+
+The hardware standards of Jenkins and Sysbench machines can appropriately
lower.
+
+#### Software Environment
+
+```
+Jenins: The latest version
+Sysbench: 1.0.20
+ShardingSphere-Proxy: package from master branch
+MySQL Server: 5.7.28
+```
+
+## Test Program
+
+According to the above hardware environment, the configuration parameters are
as follows,
+and the parameters should be adjusted according to the changes in the hardware
environment.
+
+#### ShardingSphere-Proxy Configuration
+
+```
+Proxy runs on ${host-proxy}
+Version includes: Master branch, 4.1.1, 3.0.0
+Scenarios: config-sharding, config-replica-query,
config-sharding-replica-query, config-encrypt
+Configurations: Refer to Appendix 1
+```
+
+#### MySQL Server Configuration
+
+Two MySQL instances runs on `${host-mysql-1}` and `${host-mysql-2}` machines
respectively.
+```
+Need to create the 'sbtest' database on both instances in advance.
+Set parameter: max_prepared_stmt_count = 500000
+Set parameter: max_connections = 2000
+```
+
+#### Jenkins Configuration
+
+Create 6 Jenkins tasks, and each task calls the next task in turn: (runs on
the `${host-jenkins}` machine).
+```
+1. sysbench_install: Pull the latest code, package the Proxy compression
package
+```
+
+The following tasks are run on a separate Sysbench pressure generating machine
via Jenkins slave: (runs on the `{host-sysbench}` machine)
+```
+2. sysbench_sharding:
+ a. Sharding scenarios for remote deployment of various versions of Proxy
+ b. Execute Sysbench command to pressure test Proxy
+ c. Execute Sysbench command to pressure test MySQL Server
+ d. Save Sysbench stress test results
+ e. Use drawing scripts to generate performance curves and tables (see
Appendix 2 for drawing scripts)
+3. sysbench_master_slave:
+ a. Read and write separation scenarios for remote deployment of various
versions of Proxy
+ b. Execute Sysbench command to pressure test Proxy
+ c. Execute Sysbench command to pressure test MySQL Server
+ d. Save Sysbench stress test results
+ e. Use drawing scripts to generate performance curves and tables
+4. sysbench_sharding_master_slave:
+ a. Remote deployment of sharding + read-write splitting scenarios of
various versions of Proxy
+ b. Execute Sysbench command to pressure test Proxy
+ c. Execute Sysbench command to pressure test MySQL Server
+ d. Save Sysbench stress test results
+ e. Use drawing scripts to generate performance curves and tables
+5. sysbench_encrypt:
+ a. Encryption scenarios for remote deployment of various versions of Proxy
+ b. Execute Sysbench command to pressure test Proxy
+ c. Execute Sysbench command to pressure test MySQL Server
+ d. Save Sysbench stress test results
+ e. Use drawing scripts to generate performance curves and tables
+6. sysbench_result_aggregation:
+ a. Re-execute the drawing script for the pressure test results of all tasks
+ python3 plot_graph.py sharding
+ python3 plot_graph.py ms
+ python3 plot_graph.py sharding_ms
+ python3 plot_graph.py encrypt
+ b. Use Jenkins "Publish HTML reports" plugin to integrate all images into
one HTML page
+```
+
+## Testing Process
+
+Take sysbench sharding as an example (other scenarios are similar)
+
+#### Enter the Sysbench pressure test result directory
+
+```bash
+cd /home/jenkins/sysbench_res/sharding
+```
+
+#### Create the folder for this build
+
+```bash
+mkdir $BUILD_NUMBER
+```
+
+#### Take the last 14 builds and save them in a hidden file
+
+```bash
+ls -v | tail -n14 > .build_number.txt
+```
+
+#### Deployment and stress testing
+
+Step 1: Execute remote deployment script to deploy Proxy to `{host-proxy}`
+
+./deploy_sharding.sh
+
+```bash
+#!/bin/sh
+
+rm -fr apache-shardingsphere-*-shardingsphere-proxy-bin
+tar zxvf apache-shardingsphere-*-shardingsphere-proxy-bin.tar.gz
+
+sh stop_proxy.sh
+
+cp -f prepared_conf/mysql-connector-java-5.1.47.jar
apache-shardingsphere-*-shardingsphere-proxy-bin/lib
+cp -f prepared_conf/start.sh
apache-shardingsphere-*-shardingsphere-proxy-bin/bin
+cp -f prepared_conf/config-sharding.yaml prepared_conf/server.yaml
apache-shardingsphere-*-shardingsphere-proxy-bin/conf
+
+./apache-shardingsphere-*-shardingsphere-proxy-bin/bin/start.sh
+
+sleep 30
+```
+
+Step 2: Execute the sysbench script
+
+```bash
+# master
+
+cd /home/jenkins/sysbench_res/sharding
+cd $BUILD_NUMBER
+
+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
+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
+
+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
+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
+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
+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
+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
+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
+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
+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
+
+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, three scenarios of direct connection to MySQL, repeat steps 1
and 2 above.
+
+#### Execute stop proxy script
+
+./stop_proxy.sh
+
+```bash
+#!/bin/sh
+
+./3.0.0_sharding-proxy/bin/stop.sh
+./4.1.1_apache-shardingsphere-4.1.1-sharding-proxy-bin/bin/stop.sh
+./apache-shardingsphere-*-shardingsphere-proxy-bin/bin/stop.sh
+```
+
+#### Generate pressure test curve picture
+
+```bash
+# Generate graph
+
+cd /home/jenkins/sysbench_res/
+python3 plot_graph.py sharding
+```
+
+#### Use Jenkins Publish HTML reports plugin to publish pictures to the page
+
+```
+HTML directory to archive: /home/jenkins/sysbench_res/graph/
+Index page[s]: 01_sharding.html
+Report title: HTML Report
+```
+
+## sysbench test case describe
+
+#### oltp_point_select
+
+```
+Prepare Statement (ID = 1): SELECT c FROM sbtest1 WHERE id=?
+Execute Statement: ID = 1
+```
+
+#### oltp_read_only
+
+```
+Prepare Statement (ID = 1): 'COMMIT'
+Prepare Statement (ID = 2): SELECT c FROM sbtest1 WHERE id=?
+
+Statement: 'BEGIN'
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 1
+```
+
+#### oltp_write_only
+
+```
+Prepare Statement (ID = 1): 'COMMIT'
+Prepare Statement (ID = 2): UPDATE sbtest1 SET k=k+1 WHERE id=?
+Prepare Statement (ID = 3): UPDATE sbtest6 SET c=? WHERE id=?
+Prepare Statement (ID = 4): DELETE FROM sbtest1 WHERE id=?
+Prepare Statement (ID = 5): INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?,
?, ?)
+
+Statement: 'BEGIN'
+Execute Statement: ID = 2
+Execute Statement: ID = 3
+Execute Statement: ID = 4
+Execute Statement: ID = 5
+Execute Statement: ID = 1
+```
+
+#### oltp_read_write
+
+```
+Prepare Statement (ID = 1): 'COMMIT'
+Prepare Statement (ID = 2): SELECT c FROM sbtest1 WHERE id=?
+Prepare Statement (ID = 3): UPDATE sbtest3 SET k=k+1 WHERE id=?
+Prepare Statement (ID = 4): UPDATE sbtest10 SET c=? WHERE id=?
+Prepare Statement (ID = 5): DELETE FROM sbtest8 WHERE id=?
+Prepare Statement (ID = 6): INSERT INTO sbtest8 (id, k, c, pad) VALUES (?, ?,
?, ?)
+
+Statement: 'BEGIN'
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 2
+Execute Statement: ID = 3
+Execute Statement: ID = 4
+Execute Statement: ID = 5
+Execute Statement: ID = 6
+Execute Statement: ID = 1
+```
+
+#### oltp_update_index
+
+```
+Prepare Statement (ID = 1): UPDATE sbtest1 SET k=k+1 WHERE id=?
+
+Execute Statement: ID = 1
+```
+
+#### oltp_update_non_index
+
+```
+Prepare Statement (ID = 1): UPDATE sbtest1 SET c=? WHERE id=?
+
+Execute Statement: ID = 1
+```
+
+#### oltp_delete
-### Software
+```
+Prepare Statement (ID = 1): DELETE FROM sbtest1 WHERE id=?
+
+Execute Statement: ID = 1
+```
-| **Name** | **Version** |
-| ---------------------- | --------- |
-| CentOS | 7.3.1 |
-| MySQL | 5.7 |
-| PostgreSQL | 10.0 |
-| ShardingSphere-JDBC | 5.0.0-RC1 |
-| ShardingSphere-Proxy | 5.0.0-RC1 |
+## Appendix 1
-### Hardware
+#### Master branch version
-| **Name** | **Hardware** | **Comment**
|
-| ------------------- | ------------ |
------------------------------------------------------- |
-| Sysbench | 32C 64G | the machine send request by
sysbench,deployed separately |
-| ShardingSphere-Proxy | 32C 64G | 5.0.0-RC1 版本的
ShardingSphere-Proxy,deployed separately |
-| MySQL | 32C 64G | MySQL, installed with PostgreSQL on
the same machine |
-| PostgreSQL | 32C 64G | MySQL, installed with PostgreSQL on
the same machine |
+server.yaml
-## Performance Test
+```yaml
+users:
+ - root@%:root
+ - sharding@:sharding
-Prepare the config for ShardingSphere-Proxy and ShardingSphere-JDBC for
testing(following configs are for MySQL)
+props:
+ max-connections-size-per-query: 10
+ kernel-executor-size: 128 # Infinite by default.
+ proxy-frontend-flush-threshold: 128 # The default value is 128.
+ proxy-opentracing-enabled: false
+ proxy-hint-enabled: false
+ sql-show: false
+ check-table-metadata-enabled: false
+ lock-wait-timeout-milliseconds: 50000 # The maximum time to wait for a lock
+```
-### ShardingSphere-Proxy Sharding
+config-sharding.yaml
```yaml
-schemaName: sharding_db
+
+schemaName: sbtest
dataSources:
ds_0:
- url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 50
- minPoolSize: 1
+ maxPoolSize: 256
+ minPoolSize: 256
ds_1:
- url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 50
- minPoolSize: 1
+ maxPoolSize: 256
+ minPoolSize: 256
rules:
- !SHARDING
tables:
- t_order:
- actualDataNodes: ds_${0..1}.t_order_${0..1}
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_1
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_2
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_3
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_4
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_5
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
tableStrategy:
standard:
- shardingColumn: order_id
- shardingAlgorithmName: t_order_inline
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_6
keyGenerateStrategy:
- column: order_id
+ column: id
keyGeneratorName: snowflake
- t_order_item:
- actualDataNodes: ds_${0..1}.t_order_item_${0..1}
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
tableStrategy:
standard:
- shardingColumn: order_id
- shardingAlgorithmName: t_order_item_inline
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_7
keyGenerateStrategy:
- column: order_item_id
+ column: id
keyGeneratorName: snowflake
- bindingTables:
- - t_order,t_order_item
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_8
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_9
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_10
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+
defaultDatabaseStrategy:
standard:
- shardingColumn: user_id
+ shardingColumn: id
shardingAlgorithmName: database_inline
- defaultTableStrategy:
- none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
- algorithm-expression: ds_${user_id % 2}
- t_order_inline:
+ algorithm-expression: ds_${id % 2}
+ table_inline_1:
type: INLINE
props:
- algorithm-expression: t_order_${order_id % 2}
- t_order_item_inline:
+ algorithm-expression: sbtest1_${id % 100}
+ table_inline_2:
type: INLINE
props:
- algorithm-expression: t_order_item_${order_id % 2}
-
+ algorithm-expression: sbtest2_${id % 100}
+ table_inline_3:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest3_${id % 100}
+ table_inline_4:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest4_${id % 100}
+ table_inline_5:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest5_${id % 100}
+ table_inline_6:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest6_${id % 100}
+ table_inline_7:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest7_${id % 100}
+ table_inline_8:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest8_${id % 100}
+ table_inline_9:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest9_${id % 100}
+ table_inline_10:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest10_${id % 100}
keyGenerators:
snowflake:
type: SNOWFLAKE
@@ -109,155 +495,1005 @@ rules:
worker-id: 123
```
-### ShardingSphere-JDBC Sharding
+config-replica-query.yaml
```yaml
-mode:
- type: Standalone
- repository:
- type: File
- overwrite: true
+schemaName: sbtest
dataSources:
ds_0:
- dataSourceClassName: com.zaxxer.hikari.HikariDataSource
- driverClassName: com.mysql.jdbc.Driver
- jdbcUrl:
jdbc:mysql://localhost:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
username: root
password:
- ds_1:
- dataSourceClassName: com.zaxxer.hikari.HikariDataSource
- driverClassName: com.mysql.jdbc.Driver
- jdbcUrl:
jdbc:mysql://localhost:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 128
+ minPoolSize: 128
+
+rules:
+- !READWRITE_SPLITTING
+ dataSources:
+ pr_ds:
+ primaryDataSourceName: ds_0
+ replicaDataSourceNames:
+ - ds_0
+ - ds_0
+```
+
+config-sharding-replica-query.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ primary_ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
username: root
password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+ minPoolSize: 256
+ primary_ds_1:
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+ minPoolSize: 256
rules:
- !SHARDING
tables:
- t_order:
- actualDataNodes: ds_${0..1}.t_order
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_1
keyGenerateStrategy:
- column: order_id
+ column: id
keyGeneratorName: snowflake
- t_order_item:
- actualDataNodes: ds_${0..1}.t_order_item
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_2
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_3
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_4
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_5
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_6
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_7
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_8
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_9
keyGenerateStrategy:
- column: order_item_id
+ column: id
keyGeneratorName: snowflake
- bindingTables:
- - t_order,t_order_item
- broadcastTables:
- - t_address
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_10
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+
defaultDatabaseStrategy:
standard:
- shardingColumn: user_id
+ shardingColumn: id
shardingAlgorithmName: database_inline
- defaultTableStrategy:
- none:
-
+
shardingAlgorithms:
database_inline:
type: INLINE
props:
- algorithm-expression: ds_${user_id % 2}
-
+ algorithm-expression: ds_${id % 2}
+ table_inline_1:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest1_${id % 100}
+ table_inline_2:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest2_${id % 100}
+ table_inline_3:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest3_${id % 100}
+ table_inline_4:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest4_${id % 100}
+ table_inline_5:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest5_${id % 100}
+ table_inline_6:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest6_${id % 100}
+ table_inline_7:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest7_${id % 100}
+ table_inline_8:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest8_${id % 100}
+ table_inline_9:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest9_${id % 100}
+ table_inline_10:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest10_${id % 100}
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
- worker-id: 123
+ worker-id: 123
+
+- !READWRITE_SPLITTING
+ dataSources:
+ ds_0:
+ primaryDataSourceName: primary_ds_0
+ replicaDataSourceNames:
+ - primary_ds_0
+ - primary_ds_0
+ ds_1:
+ name: ds_1
+ primaryDataSourceName: primary_ds_1
+ replicaDataSourceNames:
+ - primary_ds_1
+ - primary_ds_1
+```
+
+config-encrypt.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+ minPoolSize: 256
+
+rules:
+- !ENCRYPT
+ encryptors:
+ md5_encryptor:
+ type: MD5
+ tables:
+ sbtest1:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest2:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest3:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest4:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest5:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest6:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest7:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest8:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest9:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+ sbtest10:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptorName: md5_encryptor
+```
+
+#### 4.1.1 version
+
+server.yaml
+
+```yaml
+authentication:
+ users:
+ root:
+ password: root
+ sharding:
+ password: sharding
+ authorizedSchemas: sharding_db
props:
- sql-show: false
+ max.connections.size.per.query: 10
+ acceptor.size: 256 # The default value is available processors count * 2.
+ executor.size: 128 # Infinite by default.
+ proxy.frontend.flush.threshold: 128 # The default value is 128.
+ # LOCAL: Proxy will run with LOCAL transaction.
+ # XA: Proxy will run with XA transaction.
+ # BASE: Proxy will run with B.A.S.E transaction.
+ proxy.transaction.type: LOCAL
+ proxy.opentracing.enabled: false
+ proxy.hint.enabled: false
+ query.with.cipher.column: true
+ sql.show: false
+ allow.range.query.with.inline.sharding: false
+```
+
+config-sharding.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+ ds_1:
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+
+shardingRule:
+ tables:
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest1_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest2_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest3_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest4_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest5_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest6_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest7_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest8_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest9_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest10_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+
+ defaultDatabaseStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: ds_${id % 2}
```
-[](https://shardingsphere.apache.org/document/current/img/stress-test/sysbench_test_process.jpg)
+config-master_slave.yaml
-Sysbench is a scriptable multi-threaded benchmark tool based on LuaJIT. It is
most frequently used for database benchmarks
-The scripts contained in sysbench, covered a lot of database test situation,
it's very easy to test database performance.
+```yaml
+schemaName: sbtest
-| Script | SQL
|
-| ----------------------- |
------------------------------------------------------------ |
-| oltp\_point\_select | SELECT c FROM sbtest1 WHERE id=?
|
-| oltp\_read\_only | COMMIT <br> SELECT c FROM sbtest1 WHERE id=? |
-| oltp\_write\_only | COMMIT <br> UPDATE sbtest1 SET k=k+1 WHERE id=?
<br> UPDATE sbtest6 SET c=? WHERE id=? <br> DELETE FROM sbtest1 WHERE id=?
<br> INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) <br> BEGIN |
-| oltp\_read\_write | COMMIT <br> SELECT c FROM sbtest1 WHERE id=?
<br> UPDATE sbtest3 SET k=k+1 WHERE id=? <br> UPDATE sbtest10 SET c=? WHERE
id=? <br> DELETE FROM sbtest8 WHERE id=? <br> INSERT INTO sbtest8 (id, k, c,
pad) VALUES (?, ?, ?, ?) <br> BEGIN |
-| oltp\_update\_index | UPDATE sbtest1 SET k=k+1 WHERE id=? |
-| oltp\_update\_non\_index | UPDATE sbtest1 SET c=? WHERE id=? |
-| oltp\_delete | DELETE FROM sbtest1 WHERE id=? |
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
-By sysbench, test `Proxy + Database +Sharding`、`Direct to Database` and
compare these result
-Following is the test script for proxy by sysbench:
+masterSlaveRule:
+ name: ms_ds
+ masterDataSourceName: ds_0
+ slaveDataSourceNames:
+ - ds_0
+ - ds_0
+```
-```bash
-# clean and prepare the test data for sysbench. need to create a schema called
sbtest before execute the following command
-sysbench oltp\_read\_only --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--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
-sysbench oltp\_read\_only --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--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
-
-# start to test by corresponding script
-sysbench oltp\_read\_only --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run
-sysbench oltp\_read\_only --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_read\_only.txt
-sysbench oltp\_point\_select --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_point\_select.txt
-sysbench oltp\_read\_write --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_read\_write.txt
-sysbench oltp\_write\_only --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_write\_only.txt
-sysbench oltp\_update\_index --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_update\_index.txt
-sysbench oltp\_update\_non\_index --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_update\_non\_index.txt
-sysbench oltp\_delete --mysql-host=${SHARDINGSPHERE_PROXY_IP}
--mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest
--tables=10 --table-size=1000000 --report-interval=5 --time=1800
--threads=${THREADS} --max-requests=0 --percentile=99
--mysql-ignore-errors="all" --range_selects=off --rand-type=uniform
--auto_inc=off run | tee oltp\_delete.txt
-```
-
-## Test Result
-
-`point_select` as most base test case, it's very obvious to test the
performance between different databases and product in ShardingSphere.Following
is the QPS result for MySQL and ShardingSphere
-
-| Thread | MySQL | ShardingSphere-Proxy(Sharding) |
ShardingSphere-JDBC(Sharding by JMH) |
-| :----- | :------ | :----------------------------- |
:----------------------------------- |
-| 20 | 154,408 | 50,042 | 101,687
|
-| 100 | 283,918 | 107,488 | 245,676
|
-| 200 | 281,902 | 110,278 | 252,621
|
-
-> Sysbench is written by C language, so it could not test
ShardingSphere-JDBC.Here by, the tool for testing ShardingSphere-JDBC, is a
test tool from OpenJDK, called JMH
-Other Test Result
-
-### MySQL Test Result :
-| MySQL | oltp\_read\_only | oltp\_point\_select | oltp\_read\_write |
oltp\_write\_only | oltp\_update\_index | oltp\_update\_non\_index |
oltp\_delete |
-| --------- | -------------- | ----------------- | --------------- |
--------------- | ----------------- | --------------------- | ----------- |
-| thread20 | 172,640 | 154,408 | 63,520 | 33,890
| 12,779 | 14,256 | 24,318 |
-| thread100 | 308,513 | 283,918 | 107,942 | 50,664
| 18,659 | 18,350 | 29,799 |
-| thread200 | 309,668 | 281,902 | 125,311 | 64,977
| 21,181 | 20,587 | 34,745 |
-
-### ShardingSphere-Proxy + MySQL + Sharding Test Result :
-| ShardingSphere-Proxy\_Sharding\_MySQL | oltp\_read\_only |
oltp\_point\_select | oltp\_read\_write | oltp\_write\_only |
oltp\_update\_index | oltp\_update\_non\_index | oltp\_delete |
-| ----------------------------------- | -------------- | ----------------- |
--------------- | --------------- | ----------------- | --------------------- |
----------- |
-| thread20 | 53,953 | 50,042 |
41,929 | 36,395 | 21,700 | 23,863 |
34,000 |
-| thread100 | 117,897 | 107,488 |
104,338 | 74,393 | 38,222 | 39,742 |
93,573 |
-| thread200 | 113,608 | 110,278 |
110,829 | 84,354 | 46,583 | 45,283 |
104,681 |
-
-### ShardingSphere-JDBC + MySQL + Sharding Test Result :
-| ShardingSphere-JDBC\_Sharding\_MySQL | oltp\_point\_select |
-| ---------------------------------- | ----------------- |
-| thread20 | 101,687 |
-| thread100 | 245,676 |
-| thread200 | 252,621 |
-
-### PostgreSQL Test Result :
-| PostgreSQL | oltp\_read\_only | oltp\_point\_select | oltp\_read\_write |
oltp\_write\_only | oltp\_update\_index | oltp\_update\_non\_index |
oltp\_delete |
-| ---------- | -------------- | ----------------- | --------------- |
--------------- | ----------------- | --------------------- | ----------- |
-| thread20 | 198,943 | 179,174 | 3,594 | 1,504
| 669 | 1,240 | 1,502 |
-| thread100 | 364,045 | 302,767 | 3,300 | 1,469
| 704 | 1,236 | 1,460 |
-| thread200 | 347,426 | 280,177 | 3,261 | 1,575
| 688 | 1,209 | 1,518 |
-
-### ShardingSphere-Proxy + PostgreSQL + Sharding Test Result :
-| ShardingSphere-Proxy\_Sharding\_PostgreSQL | oltp\_read\_only |
oltp\_point\_select | oltp\_read\_write | oltp\_write\_only |
oltp\_update\_index | oltp\_update\_non\_index | oltp\_delete |
-| ---------------------------------------- | -------------- |
----------------- | --------------- | --------------- | ----------------- |
--------------------- | ----------- |
-| thread20 | 52,831 | 56,259
| 2,666 | 1,233 | 583 | 826
| 989 |
-| thread100 | 121,476 | 126,167
| 3,187 | 1,160 | 555 | 827
| 1,053 |
-| thread200 | 118,351 | 122,423
| 3,254 | 1,125 | 544 | 785
| 1,016 |
-
-### ShardingSphere-JDBC + PostgreSQL + Sharding Test Result :
-| ShardingSphere-JDBC\_Sharding\_PostgreSQL | oltp\_point\_select |
-| --------------------------------------- | ----------------- |
-| thread20 | 112,977 |
-| thread100 | 280,439 |
-| thread200 | 284,474 |
+config-sharding-master_slave.yaml
+```yaml
+schemaName: sbtest
+
+dataSources:
+ primary_ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+ primary_ds_1:
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+
+shardingRule:
+ tables:
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest1_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest2_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest3_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest4_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest5_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest6_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest7_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest8_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest9_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest10_${id % 100}
+ keyGenerator:
+ type: SNOWFLAKE
+ column: id
+
+ defaultDatabaseStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: ds_${id % 2}
+
+ masterSlaveRules:
+ ds_0:
+ masterDataSourceName: primary_ds_0
+ slaveDataSourceNames: [primary_ds_0, primary_ds_0]
+ loadBalanceAlgorithmType: ROUND_ROBIN
+ ds_1:
+ masterDataSourceName: primary_ds_1
+ slaveDataSourceNames: [primary_ds_1, primary_ds_1]
+ loadBalanceAlgorithmType: ROUND_ROBIN
+```
+
+config-encrypt.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 256
+
+encryptRule:
+ encryptors:
+ encryptor_md5:
+ type: md5
+ tables:
+ sbtest1:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest2:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest3:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest4:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest5:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest6:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest7:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest8:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest9:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+ sbtest10:
+ columns:
+ pad:
+ cipherColumn: pad
+ encryptor: encryptor_md5
+```
+
+#### 3.0.0 version
+
+server.yaml
+
+```yaml
+authentication:
+ username: root
+ password: root
+
+props:
+ max.connections.size.per.query: 10
+ acceptor.size: 256 # The default value is available processors count * 2.
+ executor.size: 128 # Infinite by default.
+ proxy.frontend.flush.threshold: 128 # The default value is 128.
+ # LOCAL: Proxy will run with LOCAL transaction.
+ # XA: Proxy will run with XA transaction.
+ # BASE: Proxy will run with B.A.S.E transaction.
+ proxy.transaction.type: LOCAL
+ proxy.opentracing.enabled: false
+ sql.show: false
+```
+
+config-sharding.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ autoCommit: true
+ connectionTimeout: 30000
+ idleTimeout: 60000
+ maxLifetime: 1800000
+ maximumPoolSize: 256
+ ds_1:
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ autoCommit: true
+ connectionTimeout: 30000
+ idleTimeout: 60000
+ maxLifetime: 1800000
+ maximumPoolSize: 256
+
+shardingRule:
+ tables:
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest1_${id % 100}
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest2_${id % 100}
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest3_${id % 100}
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest4_${id % 100}
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest5_${id % 100}
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest6_${id % 100}
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest7_${id % 100}
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest8_${id % 100}
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest9_${id % 100}
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest10_${id % 100}
+
+ defaultDatabaseStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: ds_${id % 2}
+```
+
+config-master_slave.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ autoCommit: true
+ connectionTimeout: 30000
+ idleTimeout: 60000
+ maxLifetime: 1800000
+ maximumPoolSize: 256
+
+masterSlaveRule:
+ name: ms_ds
+ masterDataSourceName: ds_0
+ slaveDataSourceNames:
+ - ds_0
+ - ds_0
+```
+
+config-sharding-master_slave.yaml
+
+```yaml
+schemaName: sbtest
+
+dataSources:
+ primary_ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ autoCommit: true
+ connectionTimeout: 30000
+ idleTimeout: 60000
+ maxLifetime: 1800000
+ maximumPoolSize: 256
+ primary_ds_1:
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
+ autoCommit: true
+ connectionTimeout: 30000
+ idleTimeout: 60000
+ maxLifetime: 1800000
+ maximumPoolSize: 256
+
+shardingRule:
+ tables:
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest1_${id % 100}
+ sbtest2:
+ actualDataNodes: ds_${0..1}.sbtest2_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest2_${id % 100}
+ sbtest3:
+ actualDataNodes: ds_${0..1}.sbtest3_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest3_${id % 100}
+ sbtest4:
+ actualDataNodes: ds_${0..1}.sbtest4_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest4_${id % 100}
+ sbtest5:
+ actualDataNodes: ds_${0..1}.sbtest5_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest5_${id % 100}
+ sbtest6:
+ actualDataNodes: ds_${0..1}.sbtest6_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest6_${id % 100}
+ sbtest7:
+ actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest7_${id % 100}
+ sbtest8:
+ actualDataNodes: ds_${0..1}.sbtest8_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest8_${id % 100}
+ sbtest9:
+ actualDataNodes: ds_${0..1}.sbtest9_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest9_${id % 100}
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
+ tableStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: sbtest10_${id % 100}
+
+ defaultDatabaseStrategy:
+ inline:
+ shardingColumn: id
+ algorithmExpression: ds_${id % 2}
+
+ masterSlaveRules:
+ ds_0:
+ masterDataSourceName: primary_ds_0
+ slaveDataSourceNames: [primary_ds_0, primary_ds_0]
+ loadBalanceAlgorithmType: ROUND_ROBIN
+ ds_1:
+ masterDataSourceName: primary_ds_1
+ slaveDataSourceNames: [primary_ds_1, primary_ds_1]
+ loadBalanceAlgorithmType: ROUND_ROBIN
+```
+
+config-encrypt.yaml
+
+```
+Unsupported
+```
+
+## Appendix 2
+
+plot_graph.py
+
+```python
+import sys
+import matplotlib.pyplot as plt
+import numpy as np
+
+
+def generate_graph(path, case_name):
+ dataset = {
+ 'build_num': [],
+ 'master_version': [],
+ 'master_xa': [],
+ '4.1.1_version': [],
+ '3.0.0_version': [],
+ 'mysql_server': []
+ }
+ with open(path + '/.build_number.txt') as builds:
+ for line in builds:
+ dataset['build_num'].append(int(line))
+ generate_data(path, case_name, dataset)
+ print(dataset)
+ fig, ax = plt.subplots()
+ ax.grid(True)
+ plt.title(case_name)
+
+ 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:]]
+ columns = dataset['build_num'][-7:]
+ rows = ['master', 'xa', '4.1.1', '3.0.0', 'mysql']
+ rcolors = plt.cm.BuPu(np.full(len(rows), 0.1))
+ ccolors = plt.cm.BuPu(np.full(len(columns), 0.1))
+ the_table = plt.table(cellText=data, rowLabels=rows, colLabels=columns,
rowColours=rcolors, colColours=ccolors,
+ loc='bottom', bbox=[0.0, -0.50, 1, .28])
+ plt.subplots_adjust(left=0.15, bottom=0.3, right=0.98)
+
+ plt.xticks(range(14))
+ ax.set_xticklabels(dataset['build_num'])
+ plt.plot(dataset['master_version'], 'o-', color='magenta',
label='master_version')
+ plt.plot(dataset['master_xa'], 'o-', color='darkviolet', label='master_xa')
+ plt.plot(dataset['4.1.1_version'], 'r--', color='blue',
label='4.1.1_version')
+ plt.plot(dataset['3.0.0_version'], 'r--', color='orange',
label='3.0.0_version')
+ plt.plot(dataset['mysql_server'], 'r--', color='lime',
label='mysql_server')
+ plt.xlim()
+ plt.legend()
+ plt.xlabel('build_num')
+ plt.ylabel('transactions per second')
+ plt.savefig('graph/' + path + '/' + case_name)
+ plt.show()
+
+
+def generate_data(path, case_name, dataset):
+ for build in dataset['build_num']:
+ fill_dataset(build, case_name, dataset, path, 'master_version',
'.master.txt')
+ fill_dataset(build, case_name, dataset, path, 'master_xa', '.xa.txt')
+ fill_dataset(build, case_name, dataset, path, '4.1.1_version',
'.4_1_1.txt')
+ fill_dataset(build, case_name, dataset, path, '3.0.0_version',
'.3_0_0.txt')
+ fill_dataset(build, case_name, dataset, path, 'mysql_server',
'.mysql.txt')
+
+
+def fill_dataset(build, case_name, dataset, path, version, suffix):
+ try:
+ with open(path + '/' + str(build) + '/' + case_name + suffix) as
version_master:
+ value = 0
+ for line in version_master:
+ if 'transactions:' in line:
+ items = line.split('(')
+ value = float(items[1][:-10])
+ dataset[version].append(value)
+ except FileNotFoundError:
+ dataset[version].append(0)
+
+
+if __name__ == '__main__':
+ path = sys.argv[1]
+ generate_graph(path, 'oltp_point_select')
+ generate_graph(path, 'oltp_read_only')
+ generate_graph(path, 'oltp_write_only')
+ generate_graph(path, 'oltp_read_write')
+ generate_graph(path, 'oltp_update_index')
+ generate_graph(path, 'oltp_update_non_index')
+ generate_graph(path, 'oltp_delete')
+```
