You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by zh...@apache.org on 2021/10/09 11:13:29 UTC
[shardingsphere] branch master updated: Add : add document for
sysbench presure test (#12963)
This is an automated email from the ASF dual-hosted git repository.
zhangliang 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 c7f2d49 Add : add document for sysbench presure test (#12963)
c7f2d49 is described below
commit c7f2d497184d6ed98b5ed4063a749cb00fe1df64
Author: 孙念君 Sun Nianjun <su...@sphere-ex.com>
AuthorDate: Sat Oct 9 19:12:48 2021 +0800
Add : add document for sysbench presure test (#12963)
* Add : add document for sysbench presure test
* Refactor : remove some other test
---
.../test-engine/performance-test-sysbench.cn.md | 1566 ++------------------
.../test-engine/performance-test-sysbench.en.md | 1564 ++-----------------
.../img/stress-test/sysbench_test_process.jpg | Bin 0 -> 46964 bytes
3 files changed, 328 insertions(+), 2802 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 1e3ff77..7e903ab 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,490 +4,104 @@ title = "性能测试(sysbench)"
weight = 5
+++
-## 环境
-
-#### 推荐硬件环境
-
-```
-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
-
-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
-
-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
+本文旨在测试 ShardingSphere-JDBC 及 ShardingSphere-Proxy 在分片场景下与 MySQL、PostgreSQL 的性能对比
-```
-Prepare Statement (ID = 1): DELETE FROM sbtest1 WHERE id=?
+## 环境
-Execute Statement: ID = 1
-```
+### 软件版本
-## 附录1
+| 名称 | 版本 |
+| ------------------ | ------- |
+| CentOS | 7.3.1 |
+| MySQL | 5.7 |
+| PostgreSQL | 10.0 |
+| ShardingSphere-JDBC | 5.0.0-RC1 |
+| ShardingSphere-Proxy | 5.0.0-RC1 |
-#### Master branch version
+### 硬件配置
-server.yaml
+| **名称** | **配置** | **作用** |
+| ----------------- | ------- | ------------------------------------------- |
+| Sysbench | 32C 64G | 发压机，通过 sysbench 对响应数据库进行测试，单独安装 |
+| ShardingSphere-Proxy | 32C 64G | 5.0.0-RC1 版本的 ShardingSphere-Proxy，单独部署 |
+| MySQL | 32C 64G | 要测试的 MySQL，与 PostgreSQL 安装在同一台机器 |
+| PostgreSQL | 32C 64G | 要测试的 PostgreSQL，与 MySQL 安装在同一台机器 |
-```yaml
-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
-```
+准备好相关配置留作测试之用（以下配置以 MySQL 为例）
-config-sharding.yaml
+### ShardingSphere-Proxy 分片
```yaml
-
-schemaName: sbtest
+schemaName: sharding_db
dataSources:
ds_0:
- url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 256
- minPoolSize: 256
+ maxPoolSize: 50
+ minPoolSize: 1
ds_1:
- url: jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 256
- minPoolSize: 256
+ maxPoolSize: 50
+ minPoolSize: 1
rules:
- !SHARDING
tables:
- 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}
+ t_order:
+ actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_6
+ shardingColumn: order_id
+ shardingAlgorithmName: t_order_inline
keyGenerateStrategy:
- column: id
+ column: order_id
keyGeneratorName: snowflake
- sbtest7:
- actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ t_order_item:
+ actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_7
+ shardingColumn: order_id
+ shardingAlgorithmName: t_order_item_inline
keyGenerateStrategy:
- column: id
+ column: order_item_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: id
- keyGeneratorName: snowflake
- sbtest10:
- actualDataNodes: ds_${0..1}.sbtest10_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_10
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
-
+ bindingTables:
+ - t_order,t_order_item
defaultDatabaseStrategy:
standard:
- shardingColumn: id
+ shardingColumn: user_id
shardingAlgorithmName: database_inline
+ defaultTableStrategy:
+ none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
- 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:
+ algorithm-expression: ds_${user_id % 2}
+ t_order_inline:
type: INLINE
props:
- algorithm-expression: sbtest8_${id % 100}
- table_inline_9:
+ algorithm-expression: t_order_${order_id % 2}
+ t_order_item_inline:
type: INLINE
props:
- algorithm-expression: sbtest9_${id % 100}
- table_inline_10:
- type: INLINE
- props:
- algorithm-expression: sbtest10_${id % 100}
+ algorithm-expression: t_order_item_${order_id % 2}
+
keyGenerators:
snowflake:
type: SNOWFLAKE
@@ -495,1007 +109,155 @@ rules:
worker-id: 123
```
-config-replica-query.yaml
+### ShardingSphere-JDBC 分片
```yaml
-schemaName: sbtest
+mode:
+ type: Standalone
+ repository:
+ type: File
+ overwrite: true
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: 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
+ 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
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
+ 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
username: root
password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 256
- minPoolSize: 256
rules:
- !SHARDING
tables:
- sbtest1:
- actualDataNodes: ds_${0..1}.sbtest1_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_1
+ t_order:
+ actualDataNodes: ds_${0..1}.t_order
keyGenerateStrategy:
- column: id
+ column: order_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: 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
+ t_order_item:
+ actualDataNodes: ds_${0..1}.t_order_item
keyGenerateStrategy:
- column: id
+ column: order_item_id
keyGeneratorName: snowflake
- sbtest10:
- actualDataNodes: ds_${0..1}.sbtest10_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_10
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
-
+ bindingTables:
+ - t_order,t_order_item
+ broadcastTables:
+ - t_address
defaultDatabaseStrategy:
standard:
- shardingColumn: id
+ shardingColumn: user_id
shardingAlgorithmName: database_inline
-
+ defaultTableStrategy:
+ none:
+
shardingAlgorithms:
database_inline:
type: INLINE
props:
- 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}
+ algorithm-expression: ds_${user_id % 2}
+
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
- 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:
- 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}
-```
-
-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:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 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:
- 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
+ worker-id: 123
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
-
-```
-不支持
+ sql-show: false
```
-## 附录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)
+[](https://shardingsphere.apache.org/document/current/img/stress-test/sysbench_test_process.jpg)
- 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)
+Sysbench 是一款基于 LuaJIT 的可编写脚本的多线程基准测试工具。它最常用于数据库基准测试。
+Sysbench 自带的脚本，包含了很多常见的场景，可以非常有效的对数据库的性能进行测试。
- 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()
+| 脚本名称 | 执行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=? |
+通过 sysbench 分别测试 `Proxy + Database + 分片`、`直连 Database` 进行横向对比。
-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')
+如下脚本为 sysbench 压测 proxy 的相应命令：
-
-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)
+```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 |
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 6cbf8c9..cb8dadf 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,490 +4,104 @@ title = "Performance Test(sysbench)"
weight = 5
+++
-## Environment
-
-#### Recommended Hardware
-
-```
-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
-```
+## Target
-#### oltp_delete
+This pressure test is for the performance compare between ShardingSphere-JDBC,ShardingSphere-Proxy in Sharding Rule to MySQL,PostgreSQL
-```
-Prepare Statement (ID = 1): DELETE FROM sbtest1 WHERE id=?
+## Environment
-Execute Statement: ID = 1
-```
+### Software
-## Appendix 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 |
-#### Master branch version
+### Hardware
-server.yaml
+| **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 |
-```yaml
-users:
- - root@%:root
- - sharding@:sharding
+## Performance Test
-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
-```
+Prepare the config for ShardingSphere-Proxy and ShardingSphere-JDBC for testing(following configs are for MySQL)
-config-sharding.yaml
+### ShardingSphere-Proxy Sharding
```yaml
-
-schemaName: sbtest
+schemaName: sharding_db
dataSources:
ds_0:
- url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 256
- minPoolSize: 256
+ maxPoolSize: 50
+ minPoolSize: 1
ds_1:
- url: jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 256
- minPoolSize: 256
+ maxPoolSize: 50
+ minPoolSize: 1
rules:
- !SHARDING
tables:
- 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}
+ t_order:
+ actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_6
+ shardingColumn: order_id
+ shardingAlgorithmName: t_order_inline
keyGenerateStrategy:
- column: id
+ column: order_id
keyGeneratorName: snowflake
- sbtest7:
- actualDataNodes: ds_${0..1}.sbtest7_${0..99}
+ t_order_item:
+ actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_7
+ shardingColumn: order_id
+ shardingAlgorithmName: t_order_item_inline
keyGenerateStrategy:
- column: id
+ column: order_item_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: id
- keyGeneratorName: snowflake
- sbtest10:
- actualDataNodes: ds_${0..1}.sbtest10_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_10
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
-
+ bindingTables:
+ - t_order,t_order_item
defaultDatabaseStrategy:
standard:
- shardingColumn: id
+ shardingColumn: user_id
shardingAlgorithmName: database_inline
+ defaultTableStrategy:
+ none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
- 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:
+ algorithm-expression: ds_${user_id % 2}
+ t_order_inline:
type: INLINE
props:
- algorithm-expression: sbtest8_${id % 100}
- table_inline_9:
+ algorithm-expression: t_order_${order_id % 2}
+ t_order_item_inline:
type: INLINE
props:
- algorithm-expression: sbtest9_${id % 100}
- table_inline_10:
- type: INLINE
- props:
- algorithm-expression: sbtest10_${id % 100}
+ algorithm-expression: t_order_item_${order_id % 2}
+
keyGenerators:
snowflake:
type: SNOWFLAKE
@@ -495,1005 +109,155 @@ rules:
worker-id: 123
```
-config-replica-query.yaml
+### ShardingSphere-JDBC Sharding
```yaml
-schemaName: sbtest
+mode:
+ type: Standalone
+ repository:
+ type: File
+ overwrite: true
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: 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
+ 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
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
+ 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
username: root
password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 256
- minPoolSize: 256
rules:
- !SHARDING
tables:
- sbtest1:
- actualDataNodes: ds_${0..1}.sbtest1_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_1
+ t_order:
+ actualDataNodes: ds_${0..1}.t_order
keyGenerateStrategy:
- column: id
+ column: order_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: 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
+ t_order_item:
+ actualDataNodes: ds_${0..1}.t_order_item
keyGenerateStrategy:
- column: id
+ column: order_item_id
keyGeneratorName: snowflake
- sbtest10:
- actualDataNodes: ds_${0..1}.sbtest10_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_10
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
-
+ bindingTables:
+ - t_order,t_order_item
+ broadcastTables:
+ - t_address
defaultDatabaseStrategy:
standard:
- shardingColumn: id
+ shardingColumn: user_id
shardingAlgorithmName: database_inline
-
+ defaultTableStrategy:
+ none:
+
shardingAlgorithms:
database_inline:
type: INLINE
props:
- 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}
+ algorithm-expression: ds_${user_id % 2}
+
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
- 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
+ worker-id: 123
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
- 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}
-```
-
-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:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 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:
- 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
+ sql-show: false
```
-## Appendix 2
-
-plot_graph.py
-
-```python
-import sys
-import matplotlib.pyplot as plt
-import numpy as np
-
+[](https://shardingsphere.apache.org/document/current/img/stress-test/sysbench_test_process.jpg)
-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)
+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.
- 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)
+| 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=? |
- 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)
+By sysbench, test `Proxy + Database +Sharding`、`Direct to Database` and compare these result
+Following is the test script for proxy by sysbench:
+```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 |
-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')
-```
diff --git a/docs/document/static/img/stress-test/sysbench_test_process.jpg b/docs/document/static/img/stress-test/sysbench_test_process.jpg
new file mode 100644
index 0000000..8abfe1e
Binary files /dev/null and b/docs/document/static/img/stress-test/sysbench_test_process.jpg differ