You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by wu...@apache.org on 2022/06/14 09:28:39 UTC
[shardingsphere] branch master updated: update article for performance test (#18353)
This is an automated email from the ASF dual-hosted git repository.
wuweijie 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 954bebf8dc0 update article for performance test (#18353)
954bebf8dc0 is described below
commit 954bebf8dc038b7bbb37a1a53c62b31c293d99a5
Author: 王善民 <86...@users.noreply.github.com>
AuthorDate: Tue Jun 14 17:28:32 2022 +0800
update article for performance test (#18353)
* update article for performance test
* change file name to 'tool-product-function-test.lang.md'
* remove Outdated documents
* change title for sysbench-proxy-norules-test.cn.md
* remove useless documents
* delete useless single quote
* change "读" to "执行语句"
---
...n.md => benchmarksql-proxy-sharding-test.cn.md} | 6 +-
.../test/performance-test/performance-test.cn.md | 431 ------
.../test/performance-test/performance-test.en.md | 434 ------
.../sysbench-proxy-norules-test.cn.md | 141 ++
.../test/performance-test/sysbench-test.en.md | 1483 --------------------
5 files changed, 146 insertions(+), 2349 deletions(-)
diff --git a/docs/document/content/reference/test/performance-test/benchmarksql-test.cn.md b/docs/document/content/reference/test/performance-test/benchmarksql-proxy-sharding-test.cn.md
similarity index 99%
rename from docs/document/content/reference/test/performance-test/benchmarksql-test.cn.md
rename to docs/document/content/reference/test/performance-test/benchmarksql-proxy-sharding-test.cn.md
index bb50f083e0f..574a4ba1dfe 100644
--- a/docs/document/content/reference/test/performance-test/benchmarksql-test.cn.md
+++ b/docs/document/content/reference/test/performance-test/benchmarksql-proxy-sharding-test.cn.md
@@ -1,8 +1,12 @@
+++
-title = "BenchmarkSQL 性能测试"
+title = "BenchmarkSQL ShardingSphere Proxy 分片性能测试"
weight = 2
+++
+## 测试目的
+
+使用 BenchmarkSQL 工具测试 ShardingSphere Proxy 的分片性能。
+
## 测试方法
ShardingSphere Proxy 支持通过 [BenchmarkSQL 5.0](https://sourceforge.net/projects/benchmarksql/) 进行 TPC-C 测试。
diff --git a/docs/document/content/reference/test/performance-test/performance-test.cn.md b/docs/document/content/reference/test/performance-test/performance-test.cn.md
deleted file mode 100644
index c5ad5372be8..00000000000
--- a/docs/document/content/reference/test/performance-test/performance-test.cn.md
+++ /dev/null
@@ -1,431 +0,0 @@
-+++
-title = "Sysbench 性能测试"
-weight = 1
-+++
-
-## 目标
-
-对 ShardingSphere-JDBC，ShardingSphere-Proxy 及 MySQL 进行性能对比。从业务角度考虑，在基本应用场景（单路由，主从+加密+分库分表，全路由）下，INSERT+UPDATE+DELETE 通常用作一个完整的关联操作，用于性能评估，而 SELECT 关注分片优化可用作性能评估的另一个操作；而主从模式下，可将 INSERT+SELECT+DELETE 作为一组评估性能的关联操作。
-为了更好的观察效果，设计在一定数据量的基础上，使用 jmeter 20 并发线程持续压测半小时，进行增删改查性能测试，且每台机器部署一个 MySQL 实例，而对比 MySQL 场景为单机单实例部署。
-
-## 测试场景
-
-### 单路由
-
-在 1000 数据量的基础上分库分表，根据 `id` 分为 4 个库，部署在同一台机器上，根据 `k` 分为 1024 个表，查询操作路由到单库单表；
-作为对比，MySQL 运行在 1000 数据量的基础上，使用 INSERT+UPDATE+DELETE 和单路由查询语句。
-
-### 主从
-
-基本主从场景，设置一主库一从库，部署在两台不同的机器上，在 10000 数据量的基础上，观察读写性能；
-作为对比，MySQL 运行在10000数据量的基础上，使用 INSERT+SELECT+DELETE 语句。
-
-### 主从+加密+分库分表
-
-在 1000 数据量的基础上，根据 `id` 分为 4 个库，部署在四台不同的机器上，根据 `k` 分为 1024 个表，`c` 使用 aes 加密，`pad` 使用 md5 加密，查询操作路由到单库单表；
-作为对比，MySQL 运行在 1000 数据量的基础上，使用 INSERT+UPDATE+DELETE 和单路由查询语句。
-
-### 全路由
-
-在 1000 数据量的基础上，分库分表，根据 `id` 分为 4 个库，部署在四台不同的机器上，根据 `k` 分为 1 个表，查询操作使用全路由。
-作为对比，MySQL 运行在 1000 数据量的基础上，使用 INSERT+UPDATE+DELETE 和全路由查询语句。
-
-## 测试环境搭建
-
-### 数据库表结构
-
-此处表结构参考 sysbench 的 sbtest 表。
-
-```shell
-CREATE TABLE `tbl` (
- `id` bigint(20) NOT NULL AUTO_INCREMENT,
- `k` int(11) NOT NULL DEFAULT 0,
- `c` char(120) NOT NULL DEFAULT '',
- `pad` char(60) NOT NULL DEFAULT '',
- PRIMARY KEY (`id`)
-);
-```
-
-### 测试场景配置
-
-ShardingSphere-JDBC 使用与 ShardingSphere-Proxy 一致的配置，MySQL 直连一个库用作性能对比，下面为四个场景的具体配置：
-
-#### 单路由配置
-
-```yaml
-schemaName: sharding_db
-
-dataSources:
- ds_0:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_1:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_2:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_3:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
-rules:
-- !SHARDING
- tables:
- tbl:
- actualDataNodes: ds_${0..3}.tbl${0..1023}
- tableStrategy:
- standard:
- shardingColumn: k
- shardingAlgorithmName: tbl_table_inline
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- defaultDatabaseStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: default_db_inline
- defaultTableStrategy:
- none:
- shardingAlgorithms:
- tbl_table_inline:
- type: INLINE
- props:
- algorithm-expression: tbl${k % 1024}
- default_db_inline:
- type: INLINE
- props:
- algorithm-expression: ds_${id % 4}
- keyGenerators:
- snowflake:
- type: SNOWFLAKE
-```
-
-#### 主从配置
-
-```yaml
-schemaName: sharding_db
-
-dataSources:
- primary_ds:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- replica_ds_0:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
-rules:
-- !READWRITE_SPLITTING
- dataSources:
- readwrite_ds:
- type: Static
- props:
- write-data-source-name: primary_ds
- read-data-source-names: replica_ds_0
-```
-
-#### 主从+加密+分库分表配置
-
-```yaml
-schemaName: sharding_db
-
-dataSources:
- primary_ds_0:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- replica_ds_0:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- primary_ds_1:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- replica_ds_1:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- primary_ds_2:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- replica_ds_2:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- primary_ds_3:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- replica_ds_3:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
-rules:
-- !SHARDING
- tables:
- tbl:
- actualDataNodes: readwrite_ds_${0..3}.tbl${0..1023}
- databaseStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: tbl_database_inline
- tableStrategy:
- standard:
- shardingColumn: k
- shardingAlgorithmName: tbl_table_inline
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- bindingTables:
- - tbl
- defaultDataSourceName: primary_ds_1
- defaultTableStrategy:
- none:
- shardingAlgorithms:
- tbl_database_inline:
- type: INLINE
- props:
- algorithm-expression: readwrite_ds_${id % 4}
- tbl_table_inline:
- type: INLINE
- props:
- algorithm-expression: tbl${k % 1024}
- keyGenerators:
- snowflake:
- type: SNOWFLAKE
-- !READWRITE_SPLITTING
- dataSources:
- readwrite_ds_0:
- type: Static
- props:
- write-data-source-name: primary_ds_0
- read-data-source-names: replica_ds_0
- readwrite_ds_1:
- type: Static
- props:
- write-data-source-name: primary_ds_1
- read-data-source-names: replica_ds_1
- loadBalancerName: round_robin
- readwrite_ds_2:
- type: Static
- props:
- write-data-source-name: primary_ds_2
- read-data-source-names: replica_ds_2
- loadBalancerName: round_robin
- readwrite_ds_3:
- type: Static
- props:
- write-data-source-name: primary_ds_3
- read-data-source-names: replica_ds_3
- loadBalancerName: round_robin
- loadBalancers:
- round_robin:
- type: ROUND_ROBIN
-- !ENCRYPT:
- encryptors:
- aes_encryptor:
- type: AES
- props:
- aes-key-value: 123456abc
- md5_encryptor:
- type: MD5
- tables:
- sbtest:
- columns:
- c:
- plainColumn: c_plain
- cipherColumn: c_cipher
- encryptorName: aes_encryptor
- pad:
- cipherColumn: pad_cipher
- encryptorName: md5_encryptor
-```
-
-#### 全路由
-
-```yaml
-schemaName: sharding_db
-
-dataSources:
- ds_0:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_1:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_2:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_3:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
-rules:
-- !SHARDING
- tables:
- tbl:
- actualDataNodes: ds_${0..3}.tbl1
- tableStrategy:
- standard:
- shardingColumn: k
- shardingAlgorithmName: tbl_table_inline
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- defaultDatabaseStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: default_database_inline
- defaultTableStrategy:
- none:
- shardingAlgorithms:
- default_database_inline:
- type: INLINE
- props:
- algorithm-expression: ds_${id % 4}
- tbl_table_inline:
- type: INLINE
- props:
- algorithm-expression: tbl1
- keyGenerators:
- snowflake:
- type: SNOWFLAKE
-```
-
-## 测试结果验证
-
-### 压测语句
-
-```shell
-INSERT+UPDATE+DELETE 语句：
-INSERT INTO tbl(k, c, pad) VALUES(1, '###-###-###', '###-###');
-UPDATE tbl SET c='####-####-####', pad='####-####' WHERE id=?;
-DELETE FROM tbl WHERE id=?
-
-全路由查询语句：
-SELECT max(id) FROM tbl WHERE id%4=1
-
-单路由查询语句：
-SELECT id, k FROM tbl ignore index(`PRIMARY`) WHERE id=1 AND k=1
-
-INSERT+SELECT+DELETE 语句：
-INSERT INTO tbl1(k, c, pad) VALUES(1, '###-###-###', '###-###');
-SELECT count(id) FROM tbl1;
-SELECT max(id) FROM tbl1 ignore index(`PRIMARY`);
-DELETE FROM tbl1 WHERE id=?
-```
-
-### 压测类
-
-参考 [ shardingsphere-benchmark ](https://github.com/apache/shardingsphere-benchmark/tree/master/shardingsphere-benchmark) 实现，注意阅读其中的注释。
-
-### 编译
-
-```shell
-git clone https://github.com/apache/shardingsphere-benchmark.git
-cd shardingsphere-benchmark/shardingsphere-benchmark
-mvn clean install
-```
-
-### 压测执行
-
-```shell
-cp target/shardingsphere-benchmark-1.0-SNAPSHOT-jar-with-dependencies.jar apache-jmeter-4.0/lib/ext
-jmeter –n –t test_plan/test.jmx
-test.jmx 参考 https://github.com/apache/shardingsphere-benchmark/tree/master/report/script/test_plan/test.jmx
-```
-
-### 压测结果处理
-
-注意修改为上一步生成的 result.jtl 的位置。
-```shell
-sh shardingsphere-benchmark/report/script/gen_report.sh
-```
-
-### 历史压测数据展示
-
-正在进行中，请等待。
-<!--
-[Benchmark 性能平台](https://shardingsphere.apache.org/benchmark/#/overview)是数据以天粒度展示
--->
diff --git a/docs/document/content/reference/test/performance-test/performance-test.en.md b/docs/document/content/reference/test/performance-test/performance-test.en.md
deleted file mode 100644
index 532cf36d980..00000000000
--- a/docs/document/content/reference/test/performance-test/performance-test.en.md
+++ /dev/null
@@ -1,434 +0,0 @@
-+++
-title = "Performance Test"
-weight = 1
-+++
-
-## Target
-
-The performance of ShardingSphere-JDBC, ShardingSphere-Proxy and MySQL would be compared here. INSERT & UPDATE & DELETE which regarded as a set of associated operation and SELECT which focus on sharding optimization are used to evaluate performance for the basic scenarios (single route, readwrite-splitting & encrypt & sharding, full route). While another set of associated operation, INSERT & SELECT & DELETE, is used to evaluate performance for readwrite-splitting.
-To achieve the result better, these tests are performed with jmeter which based on a certain amount of data with 20 concurrent threads for 30 minutes, and one MySQL has been deployed on one machine, while the scenario of MySQL used for comparison is deployed on one machine with one instance.
-
-## Test Scenarios
-
-### Single Route
-
-On the basis of one thousand data volume, four databases that are deployed on the same machine and each contains 1024 tables with `id` used for database sharding and `k` used for table sharding are designed for this scenario, single route select sql statement is chosen here.
-While as a comparison, MySQL runs with INSERT & UPDATE & DELETE statement and single route select sql statement on the basis of one thousand data volume.
-
-### Readwrite-splitting
-
-One primary database and one replica database, which are deployed on different machines, are designed for this scenario based on ten thousand data volume.
-While as a comparison, MySQL runs with INSERT & SELECT & DELETE sql statement on the basis of ten thousand data volume.
-
-### Readwrite-splitting & Encrypt & Sharding
-
-On the basis of one thousand data volume, four databases that are deployed on different machines and each contains 1024 tables with `id` used for database sharding, `k` used for table sharding, `c` encrypted with aes and `pad` encrypted with md5 are designed for this scenario, single route select sql statement is chosen here.
-While as a comparison, MySQL runs with INSERT & UPDATE & DELETE statement and single route select sql statement on the basis of one thousand data volume.
-
-### Full Route
-
-On the basis of one thousand data volume, four databases that are deployed on different machines and each contains one table are designed for this scenario, field `id` is used for database sharding and `k` is used for table sharding, full route select sql statement is chosen here.
-While as a comparison, MySQL runs with INSERT & UPDATE & DELETE statement and full route select sql statement on the basis of one thousand data volume.
-
-## Testing Environment
-
-### Table Structure of Database
-
-The structure of table here refer to `sbtest` in `sysbench`.
-
-```shell
-CREATE TABLE `tbl` (
- `id` bigint(20) NOT NULL AUTO_INCREMENT,
- `k` int(11) NOT NULL DEFAULT 0,
- `c` char(120) NOT NULL DEFAULT '',
- `pad` char(60) NOT NULL DEFAULT '',
- PRIMARY KEY (`id`)
-);
-```
-
-### Test Scenarios Configuration
-
-The same configurations are used for ShardingSphere-JDBC and ShardingSphere-Proxy, while MySQL with one database connected is designed for comparision.
-The details for these scenarios are shown as follows.
-
-#### Single Route Configuration
-
-```yaml
-schemaName: sharding_db
-
-dataSources:
- ds_0:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_1:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_2:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_3:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
-rules:
-- !SHARDING
- tables:
- tbl:
- actualDataNodes: ds_${0..3}.tbl${0..1023}
- tableStrategy:
- standard:
- shardingColumn: k
- shardingAlgorithmName: tbl_table_inline
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- defaultDatabaseStrategy:
- inline:
- shardingColumn: id
- shardingAlgorithmName: default_db_inline
- defaultTableStrategy:
- none:
- shardingAlgorithms:
- tbl_table_inline:
- type: INLINE
- props:
- algorithm-expression: tbl${k % 1024}
- default_db_inline:
- type: INLINE
- props:
- algorithm-expression: ds_${id % 4}
- keyGenerators:
- snowflake:
- type: SNOWFLAKE
-```
-
-#### Readwrite-splitting Configuration
-
-```yaml
-schemaName: sharding_db
-
-dataSources:
- primary_ds:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- replica_ds_0:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
-rules:
-- !READWRITE_SPLITTING
- dataSources:
- readwrite_ds:
- type: Static
- props:
- write-data-source-name: primary_ds
- read-data-source-names: replica_ds_0
-```
-
-#### Readwrite-splitting & Encrypt & Sharding Configuration
-
-```yaml
-schemaName: sharding_db
-
-dataSources:
- primary_ds_0:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- replica_ds_0:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- primary_ds_1:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- replica_ds_1:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- primary_ds_2:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- replica_ds_2:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- primary_ds_3:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- replica_ds_3:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
-rules:
-- !SHARDING
- tables:
- tbl:
- actualDataNodes: readwrite_ds_${0..3}.tbl${0..1023}
- databaseStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: tbl_database_inline
- tableStrategy:
- standard:
- shardingColumn: k
- shardingAlgorithmName: tbl_table_inline
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- bindingTables:
- - tbl
- defaultDataSourceName: primary_ds_1
- defaultTableStrategy:
- none:
- shardingAlgorithms:
- tbl_database_inline:
- type: INLINE
- props:
- algorithm-expression: readwrite_ds_${id % 4}
- tbl_table_inline:
- type: INLINE
- props:
- algorithm-expression: tbl${k % 1024}
- keyGenerators:
- snowflake:
- type: SNOWFLAKE
-- !READWRITE_SPLITTING
- dataSources:
- readwrite_ds_0:
- type: Static
- props:
- write-data-source-name: primary_ds_0
- read-data-source-names: replica_ds_0
- loadBalancerName: round_robin
- readwrite_ds_1:
- type: Static
- props:
- write-data-source-name: primary_ds_1
- read-data-source-names: replica_ds_1
- loadBalancerName: round_robin
- readwrite_ds_2:
- type: Static
- props:
- write-data-source-name: primary_ds_2
- read-data-source-names: replica_ds_2
- loadBalancerName: round_robin
- readwrite_ds_3:
- type: Static
- props:
- write-data-source-name: primary_ds_3
- read-data-source-names: replica_ds_3
- loadBalancerName: round_robin
- loadBalancers:
- round_robin:
- type: ROUND_ROBIN
-- !ENCRYPT:
- encryptors:
- aes_encryptor:
- type: AES
- props:
- aes-key-value: 123456abc
- md5_encryptor:
- type: MD5
- tables:
- sbtest:
- columns:
- c:
- plainColumn: c_plain
- cipherColumn: c_cipher
- encryptorName: aes_encryptor
- pad:
- cipherColumn: pad_cipher
- encryptorName: md5_encryptor
-```
-
-#### Full Route Configuration
-
-```yaml
-schemaName: sharding_db
-
-dataSources:
- ds_0:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_1:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_2:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_3:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
-rules:
-- !SHARDING
- tables:
- tbl:
- actualDataNodes: ds_${0..3}.tbl1
- tableStrategy:
- standard:
- shardingColumn: k
- shardingAlgorithmName: tbl_table_inline
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- defaultDatabaseStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: default_database_inline
- defaultTableStrategy:
- none:
- shardingAlgorithms:
- default_database_inline:
- type: INLINE
- props:
- algorithm-expression: ds_${id % 4}
- tbl_table_inline:
- type: INLINE
- props:
- algorithm-expression: tbl1
- keyGenerators:
- snowflake:
- type: SNOWFLAKE
-```
-
-## Test Result Verification
-
-### SQL Statement
-
-```shell
-INSERT+UPDATE+DELETE sql statements:
-INSERT INTO tbl(k, c, pad) VALUES(1, '###-###-###', '###-###');
-UPDATE tbl SET c='####-####-####', pad='####-####' WHERE id=?;
-DELETE FROM tbl WHERE id=?
-
-SELECT sql statement for full route:
-SELECT max(id) FROM tbl WHERE id%4=1
-
-SELECT sql statement for single route:
-SELECT id, k FROM tbl ignore index(`PRIMARY`) WHERE id=1 AND k=1
-
-INSERT+SELECT+DELETE sql statements：
-INSERT INTO tbl1(k, c, pad) VALUES(1, '###-###-###', '###-###');
-SELECT count(id) FROM tbl1;
-SELECT max(id) FROM tbl1 ignore index(`PRIMARY`);
-DELETE FROM tbl1 WHERE id=?
-```
-
-### Jmeter Class
-
-Consider the implementation of [shardingsphere-benchmark](https://github.com/apache/shardingsphere-benchmark/tree/master/shardingsphere-benchmark)
-Notes: the notes in shardingsphere-benchmark/README.md should be taken attention to
-
-### Compile & Build
-
-```shell
-git clone https://github.com/apache/shardingsphere-benchmark.git
-cd shardingsphere-benchmark/shardingsphere-benchmark
-mvn clean install
-```
-
-### Perform Test
-
-```shell
-cp target/shardingsphere-benchmark-1.0-SNAPSHOT-jar-with-dependencies.jar apache-jmeter-4.0/lib/ext
-jmeter –n –t test_plan/test.jmx
-test.jmx example:https://github.com/apache/shardingsphere-benchmark/tree/master/report/script/test_plan/test.jmx
-```
-
-### Process Result Data
-
-Make sure the location of result.jtl file is correct.
-```shell
-sh shardingsphere-benchmark/report/script/gen_report.sh
-```
-
-### Display of Historical Performance Test Data
-
-In progress, please wait.
-<!--
-The data of [benchmark platform](https://shardingsphere.apache.org/benchmark/#/overview) is show daily
--->
diff --git a/docs/document/content/reference/test/performance-test/sysbench-proxy-norules-test.cn.md b/docs/document/content/reference/test/performance-test/sysbench-proxy-norules-test.cn.md
new file mode 100644
index 00000000000..a5421abba2a
--- /dev/null
+++ b/docs/document/content/reference/test/performance-test/sysbench-proxy-norules-test.cn.md
@@ -0,0 +1,141 @@
++++
+title = "Sysbench ShardingSphere Proxy 空 Rules 性能测试"
+weight = 1
++++
+
+## 测试目的
+
+对 ShardingSphere-Proxy 及 MySQL 进行性能对比
+1. sysbench 直接压测 MySQL 性能
+2. sysbench 压测 ShardingSphere-Proxy(底层透传 MySQL)
+
+基于以上两组实验，得到使用 ShardingSphere-Proxy 对于 MySQL 的损耗。
+
+## 测试环境搭建
+
+### 服务器信息
+
+1. DB 相关配置：推荐内存大于压测的数据量，使得数据均在内存热块中，其余可自行调整；
+2. ShardingSphere-Proxy 相关配置：推荐使用高性能多核 CPU，其余可自行调整；
+3. 压测涉及服务器均关闭 swap 分区。
+
+### 数据库
+
+```shell
+[mysqld]
+innodb_buffer_pool_size=${MORE_THAN_DATA_SIZE}
+innodb-log-file-size=3000000000
+innodb-log-files-in-group=5
+innodb-flush-log-at-trx-commit=0
+innodb-change-buffer-max-size=40
+back_log=900
+innodb_max_dirty_pages_pct=75
+innodb_open_files=20480
+innodb_buffer_pool_instances=8
+innodb_page_cleaners=8
+innodb_purge_threads=2
+innodb_read_io_threads=8
+innodb_write_io_threads=8
+table_open_cache=102400
+log_timestamps=system
+thread_cache_size=16384
+transaction_isolation=READ-COMMITTED
+
+# 可参考进行适当调优，旨在放大底层 DB 性能，不让实验受制于 DB 性能瓶颈。
+
+```
+
+### 压测工具
+
+可通过 [ sysbench ](https://github.com/akopytov/sysbench) 官网自行获取
+
+### ShardingSphere-Proxy
+
+#### bin/start.sh
+
+```shell
+ -Xmx16g -Xms16g -Xmn8g # 调整 JVM 相关参数
+```
+
+#### config.yaml
+
+```yaml
+schemaName: sharding_db
+
+dataSources:
+ ds_0:
+ url: jdbc:mysql://***.***.***.***:****/test?serverTimezone=UTC&useSSL=false # 参数可适当调整
+ username: test
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 200 # 最大链接池设为 ${压测并发数} 与压测并发数保持一致，屏蔽压测过程中额外的链接带来的影响
+ minPoolSize: 200 # 最小链接池设为 ${压测并发数} 与压测并发数保持一致，屏蔽压测过程中初始化链接带来的影响
+
+rules: []
+
+```
+
+## 测试阶段
+
+### 环境准备
+
+```shell
+sysbench oltp_read_write --mysql-host=${DB_IP} --mysql-port=${DB_PORT} --mysql-user=${USER} --mysql-password=${PASSWD} --mysql-db=test --tables=10 --table-size=1000000 --report-interval=10 --time=100 --threads=200 cleanup
+sysbench oltp_read_write --mysql-host=${DB_IP} --mysql-port=${DB_PORT} --mysql-user=${USER} --mysql-password=${PASSWD} --mysql-db=test --tables=10 --table-size=1000000 --report-interval=10 --time=100 --threads=200 prepare
+```
+
+### 压测命令
+
+```shell
+sysbench oltp_read_write --mysql-host=${DB/PROXY_IP} --mysql-port=${DB/PROXY_PORT} --mysql-user=${USER} --mysql-password=${PASSWD} --mysql-db=test --tables=10 --table-size=1000000 --report-interval=10 --time=100 --threads=200 run
+```
+
+### 压测报告分析
+
+```shell
+sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
+Running the test with following options:
+Number of threads: 200
+Report intermediate results every 10 second(s)
+Initializing random number generator from current time
+Initializing worker threads...
+Threads started!
+# 每 10 秒钟报告一次测试结果，tps、每秒读、每秒写、95% 以上的响应时长统计
+[ 10s ] thds: 200 tps: 11161.70 qps: 223453.06 (r/w/o: 156451.76/44658.51/22342.80) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
+...
+[ 120s ] thds: 200 tps: 11731.00 qps: 234638.36 (r/w/o: 164251.67/46924.69/23462.00) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
+SQL statistics:
+ queries performed:
+ read: 19560590 # 读总数
+ write: 5588740 # 写总数
+ other: 27943700 # 其他操作总数 (COMMIT 等)
+ total: 27943700 # 全部总数
+ transactions: 1397185 (11638.59 per sec.) # 总事务数 ( 每秒事务数 )
+ queries: 27943700 (232771.76 per sec.) # 执行语句总数 ( 每秒执行语句次数 )
+ ignored errors: 0 (0.00 per sec.) # 忽略错误数 ( 每秒忽略错误数 )
+ reconnects: 0 (0.00 per sec.) # 重连次数 ( 每秒重连次数 )
+
+General statistics:
+ total time: 120.0463s # 总共耗时
+ total number of events: 1397185 # 总共发生多少事务数
+
+Latency (ms):
+ min: 5.37 # 最小延时
+ avg: 17.13 # 平均延时
+ max: 109.75 # 最大延时
+ 95th percentile: 24.83 # 超过 95% 平均耗时
+ sum: 23999546.19
+
+Threads fairness:
+ events (avg/stddev): 6985.9250/34.74 # 平均每线程完成 6985.9250 次 event，标准差为 34.74
+ execution time (avg/stddev): 119.9977/0.01 # 每个线程平均耗时 119.9977 秒，标准差为 0.01
+
+```
+
+### 压测过程中值得关注的点
+
+1. ShardingSphere-Proxy 所在服务器 CPU 利用率，充分利用 CPU 为佳；
+2. DB 所在服务器磁盘 IO，物理读越低越好；
+3. 压测中涉及服务器的网络 IO。
diff --git a/docs/document/content/reference/test/performance-test/sysbench-test.en.md b/docs/document/content/reference/test/performance-test/sysbench-test.en.md
deleted file mode 100644
index f34553ff87b..00000000000
--- a/docs/document/content/reference/test/performance-test/sysbench-test.en.md
+++ /dev/null
@@ -1,1483 +0,0 @@
-+++
-title = "Sysbench Test"
-weight = 1
-+++
-
-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
-
-```
-Jenkins: The latest version
-Sysbench: 1.0.20
-ShardingSphere-Proxy: package from master branch
-MySQL Server: 5.7.28
-```
-
-## Test Program
-
-According to the above hardware environment, the configuration parameters are as follows,
-and the parameters should be adjusted according to the changes in the hardware environment.
-
-#### ShardingSphere-Proxy Configuration
-
-```
-Proxy runs on ${host-proxy}
-Version includes: Master branch, 4.1.1, 3.0.0
-Scenarios: config-sharding, config-replica-query, config-sharding-replica-query, config-encrypt
-Configurations: Refer to Appendix 1
-```
-
-#### MySQL Server Configuration
-
-Two MySQL instances runs on `${host-mysql-1}` and `${host-mysql-2}` machines respectively.
-```
-Need to create the 'sbtest' database on both instances in advance.
-Set parameter: max_prepared_stmt_count = 500000
-Set parameter: max_connections = 2000
-```
-
-#### Jenkins Configuration
-
-Create 6 Jenkins tasks, and each task calls the next task in turn: (runs on the `${host-jenkins}` machine).
-```
-1. sysbench_install: Pull the latest code, package the Proxy compression package
-```
-
-The following tasks are run on a separate Sysbench pressure generating machine via Jenkins slave: (runs on the `{host-sysbench}` machine)
-```
-2. sysbench_sharding:
- a. Sharding scenarios for remote deployment of various versions of Proxy
- b. Execute Sysbench command to pressure test Proxy
- c. Execute Sysbench command to pressure test MySQL Server
- d. Save Sysbench stress test results
- e. Use drawing scripts to generate performance curves and tables (see Appendix 2 for drawing scripts)
-3. sysbench_master_slave:
- a. Read and write separation scenarios for remote deployment of various versions of Proxy
- b. Execute Sysbench command to pressure test Proxy
- c. Execute Sysbench command to pressure test MySQL Server
- d. Save Sysbench stress test results
- e. Use drawing scripts to generate performance curves and tables
-4. sysbench_sharding_master_slave:
- a. Remote deployment of sharding + read-write splitting scenarios of various versions of Proxy
- b. Execute Sysbench command to pressure test Proxy
- c. Execute Sysbench command to pressure test MySQL Server
- d. Save Sysbench stress test results
- e. Use drawing scripts to generate performance curves and tables
-5. sysbench_encrypt:
- a. Encryption scenarios for remote deployment of various versions of Proxy
- b. Execute Sysbench command to pressure test Proxy
- c. Execute Sysbench command to pressure test MySQL Server
- d. Save Sysbench stress test results
- e. Use drawing scripts to generate performance curves and tables
-6. sysbench_result_aggregation:
- a. Re-execute the drawing script for the pressure test results of all tasks
- python3 plot_graph.py sharding
- python3 plot_graph.py ms
- python3 plot_graph.py sharding_ms
- python3 plot_graph.py encrypt
- b. Use Jenkins "Publish HTML reports" plugin to integrate all images into one HTML page
-```
-
-## Testing Process
-
-Take sysbench sharding as an example (other scenarios are similar)
-
-#### Enter the Sysbench pressure test result directory
-
-```bash
-cd /home/jenkins/sysbench_res/sharding
-```
-
-#### Create the folder for this build
-
-```bash
-mkdir $BUILD_NUMBER
-```
-
-#### Take the last 14 builds and save them in a hidden file
-
-```bash
-ls -v | tail -n14 > .build_number.txt
-```
-
-#### Deployment and stress testing
-
-Step 1: Execute remote deployment script to deploy Proxy to `{host-proxy}`
-
-./deploy_sharding.sh
-
-```bash
-#!/bin/sh
-rm -fr apache-shardingsphere-*-shardingsphere-proxy-bin
-tar zxvf apache-shardingsphere-*-shardingsphere-proxy-bin.tar.gz
-sh stop_proxy.sh
-cp -f prepared_conf/mysql-connector-java-5.1.47.jar apache-shardingsphere-*-shardingsphere-proxy-bin/lib
-cp -f prepared_conf/start.sh apache-shardingsphere-*-shardingsphere-proxy-bin/bin
-cp -f prepared_conf/config-sharding.yaml prepared_conf/server.yaml apache-shardingsphere-*-shardingsphere-proxy-bin/conf
-./apache-shardingsphere-*-shardingsphere-proxy-bin/bin/start.sh
-sleep 30
-```
-
-Step 2: Execute the sysbench script
-
-```bash
-# master
-cd /home/jenkins/sysbench_res/sharding
-cd $BUILD_NUMBER
-sysbench oltp_read_only --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=10 --time=3600 --threads=10 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --rand-type=uniform --range_selects=off --auto_inc=off cleanup
-sysbench oltp_read_only --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=10 --time=3600 --threads=10 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --rand-type=uniform --range_selects=off --auto_inc=off prepare
-sysbench oltp_read_only --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30 --time=180 --threads=256 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run
-sysbench oltp_read_only --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30 --time=180 --threads=256 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_read_only.master.txt
-sysbench oltp_point_select --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30 --time=180 --threads=256 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_point_select.master.txt
-sysbench oltp_read_write --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30 --time=180 --threads=256 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_read_write.master.txt
-sysbench oltp_write_only --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30 --time=180 --threads=256 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_write_only.master.txt
-sysbench oltp_update_index --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30 --time=180 --threads=256 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_update_index.master.txt
-sysbench oltp_update_non_index --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30 --time=180 --threads=256 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_update_non_index.master.txt
-sysbench oltp_delete --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=30 --time=180 --threads=256 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off run | tee oltp_delete.master.txt
-sysbench oltp_read_only --mysql-host=${host-proxy} --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=1000000 --report-interval=10 --time=3600 --threads=10 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --rand-type=uniform --range_selects=off --auto_inc=off cleanup
-```
-
-4.1.1, 3.0.0, three scenarios of direct connection to MySQL, repeat steps 1 and 2 above.
-
-#### Execute stop proxy script
-
-./stop_proxy.sh
-
-```bash
-#!/bin/sh
-./3.0.0_sharding-proxy/bin/stop.sh
-./4.1.1_apache-shardingsphere-4.1.1-sharding-proxy-bin/bin/stop.sh
-./apache-shardingsphere-*-shardingsphere-proxy-bin/bin/stop.sh
-```
-
-#### Generate pressure test curve picture
-
-```bash
-# Generate graph
-cd /home/jenkins/sysbench_res/
-python3 plot_graph.py sharding
-```
-
-#### Use Jenkins Publish HTML reports plugin to publish pictures to the page
-
-```
-HTML directory to archive: /home/jenkins/sysbench_res/graph/
-Index page[s]: 01_sharding.html
-Report title: HTML Report
-```
-
-## sysbench test case describe
-
-#### oltp_point_select
-
-```
-Prepare Statement (ID = 1): SELECT c FROM sbtest1 WHERE id=?
-Execute Statement: ID = 1
-```
-
-#### oltp_read_only
-
-```
-Prepare Statement (ID = 1): 'COMMIT'
-Prepare Statement (ID = 2): SELECT c FROM sbtest1 WHERE id=?
-Statement: 'BEGIN'
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 1
-```
-
-#### oltp_write_only
-
-```
-Prepare Statement (ID = 1): 'COMMIT'
-Prepare Statement (ID = 2): UPDATE sbtest1 SET k=k+1 WHERE id=?
-Prepare Statement (ID = 3): UPDATE sbtest6 SET c=? WHERE id=?
-Prepare Statement (ID = 4): DELETE FROM sbtest1 WHERE id=?
-Prepare Statement (ID = 5): INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)
-Statement: 'BEGIN'
-Execute Statement: ID = 2
-Execute Statement: ID = 3
-Execute Statement: ID = 4
-Execute Statement: ID = 5
-Execute Statement: ID = 1
-```
-
-#### oltp_read_write
-
-```
-Prepare Statement (ID = 1): 'COMMIT'
-Prepare Statement (ID = 2): SELECT c FROM sbtest1 WHERE id=?
-Prepare Statement (ID = 3): UPDATE sbtest3 SET k=k+1 WHERE id=?
-Prepare Statement (ID = 4): UPDATE sbtest10 SET c=? WHERE id=?
-Prepare Statement (ID = 5): DELETE FROM sbtest8 WHERE id=?
-Prepare Statement (ID = 6): INSERT INTO sbtest8 (id, k, c, pad) VALUES (?, ?, ?, ?)
-Statement: 'BEGIN'
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 2
-Execute Statement: ID = 3
-Execute Statement: ID = 4
-Execute Statement: ID = 5
-Execute Statement: ID = 6
-Execute Statement: ID = 1
-```
-
-#### oltp_update_index
-
-```
-Prepare Statement (ID = 1): UPDATE sbtest1 SET k=k+1 WHERE id=?
-Execute Statement: ID = 1
-```
-
-#### oltp_update_non_index
-
-```
-Prepare Statement (ID = 1): UPDATE sbtest1 SET c=? WHERE id=?
-Execute Statement: ID = 1
-```
-
-#### oltp_delete
-
-```
-Prepare Statement (ID = 1): DELETE FROM sbtest1 WHERE id=?
-Execute Statement: ID = 1
-```
-
-## Appendix 1
-
-#### Master branch version
-
-server.yaml
-
-```yaml
-rules:
- -!AUTHORITY
- users:
- - root@%:root
- - sharding@:sharding
- provider:
- type: ALL_PERMITTED
-props:
- max-connections-size-per-query: 1
- kernel-executor-size: 16 # Infinite by default.
- proxy-frontend-flush-threshold: 128 # The default value is 128.
- proxy-hint-enabled: false
- sql-show: false
- check-table-metadata-enabled: false
- show-process-list-enabled: false
- proxy-backend-query-fetch-size: -1
- check-duplicate-table-enabled: false
- proxy-frontend-executor-size: 0
- proxy-backend-executor-suitable: OLAP
- proxy-frontend-max-connections: 0
- sql-federation-enabled: 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
- minPoolSize: 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
- minPoolSize: 256
-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}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_6
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest7:
- actualDataNodes: ds_${0..1}.sbtest7_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_7
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest8:
- actualDataNodes: ds_${0..1}.sbtest8_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_8
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest9:
- actualDataNodes: ds_${0..1}.sbtest9_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_9
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest10:
- actualDataNodes: ds_${0..1}.sbtest10_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_10
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- defaultDatabaseStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: database_inline
- 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}
- keyGenerators:
- snowflake:
- type: SNOWFLAKE
-```
-
-config-readwrite-splitting.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: 128
- minPoolSize: 128
-rules:
-- !READWRITE_SPLITTING
- dataSources:
- readwrite_ds:
- primaryDataSourceName: ds_0
- replicaDataSourceNames:
- - ds_0
- - ds_0
-```
-
-config-shadow.yaml
-
-```yaml
-schemaName: sbtest
-dataSources:
- primary_ds_0:
- url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
- username: root
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 256
- minPoolSize: 256
- primary_ds_1:
- url: jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
- username: root
- password:
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 256
- minPoolSize: 256
-rules:
-- !SHARDING
- tables:
- sbtest1:
- actualDataNodes: ds_${0..1}.sbtest1_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_1
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest2:
- actualDataNodes: ds_${0..1}.sbtest2_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_2
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest3:
- actualDataNodes: ds_${0..1}.sbtest3_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_3
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest4:
- actualDataNodes: ds_${0..1}.sbtest4_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_4
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest5:
- actualDataNodes: ds_${0..1}.sbtest5_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_5
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest6:
- actualDataNodes: ds_${0..1}.sbtest6_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_6
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest7:
- actualDataNodes: ds_${0..1}.sbtest7_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_7
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest8:
- actualDataNodes: ds_${0..1}.sbtest8_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_8
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest9:
- actualDataNodes: ds_${0..1}.sbtest9_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_9
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
- sbtest10:
- actualDataNodes: ds_${0..1}.sbtest10_${0..99}
- tableStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: table_inline_10
- keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
-
- defaultDatabaseStrategy:
- standard:
- shardingColumn: id
- shardingAlgorithmName: database_inline
- 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}
- keyGenerators:
- snowflake:
- type: SNOWFLAKE
-- !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
-```
-
-config-database-discovery.yaml
-
-```yaml
-schemaName: sbtest
-dataSources:
- ds_0:
- url: jdbc:postgresql://127.0.0.1:5432/demo_primary_ds
- username: postgres
- password: postgres
- connectionTimeoutMilliseconds: 3000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 50
- minPoolSize: 1
- ds_1:
- url: jdbc:postgresql://127.0.0.1:5432/demo_replica_ds_0
- username: postgres
- password: postgres
- connectionTimeoutMilliseconds: 3000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 50
- minPoolSize: 1
- ds_2:
- url: jdbc:postgresql://127.0.0.1:5432/demo_replica_ds_1
- username: postgres
- password: postgres
- connectionTimeoutMilliseconds: 3000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 50
- minPoolSize: 1
-rules:
-- !DB_DISCOVERY
- dataSources:
- readwrite_ds:
- dataSourceNames:
- - ds_0
- - ds_1
- - ds_2
- discoveryHeartbeatName: mgr-heartbeat
- discoveryTypeName: mgr
- discoveryHeartbeats:
- mgr-heartbeat:
- props:
- keep-alive-cron: '0/5 * * * * ?'
- discoveryTypes:
- mgr:
- type: MySQL.MGR
- props:
- group-name: 92504d5b-6dec-11e8-91ea-246e9612aaf1
-```
-
-#### 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
-props:
- max.connections.size.per.query: 10
- acceptor.size: 256 # The default value is available processors count * 2.
- executor.size: 128 # Infinite by default.
- proxy.frontend.flush.threshold: 128 # The default value is 128.
- # LOCAL: Proxy will run with LOCAL transaction.
- # XA: Proxy will run with XA transaction.
- # BASE: Proxy will run with B.A.S.E transaction.
- proxy.transaction.type: LOCAL
- proxy.opentracing.enabled: false
- sql.show: false
-```
-
-config-sharding.yaml
-
-```yaml
-schemaName: sbtest
-dataSources:
- ds_0:
- url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
- username: root
- password:
- autoCommit: true
- connectionTimeout: 30000
- idleTimeout: 60000
- maxLifetime: 1800000
- maximumPoolSize: 256
- ds_1:
- url: jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
- username: root
- password:
- autoCommit: true
- connectionTimeout: 30000
- idleTimeout: 60000
- maxLifetime: 1800000
- maximumPoolSize: 256
-shardingRule:
- tables:
- sbtest1:
- actualDataNodes: ds_${0..1}.sbtest1_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest1_${id % 100}
- sbtest2:
- actualDataNodes: ds_${0..1}.sbtest2_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest2_${id % 100}
- sbtest3:
- actualDataNodes: ds_${0..1}.sbtest3_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest3_${id % 100}
- sbtest4:
- actualDataNodes: ds_${0..1}.sbtest4_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest4_${id % 100}
- sbtest5:
- actualDataNodes: ds_${0..1}.sbtest5_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest5_${id % 100}
- sbtest6:
- actualDataNodes: ds_${0..1}.sbtest6_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest6_${id % 100}
- sbtest7:
- actualDataNodes: ds_${0..1}.sbtest7_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest7_${id % 100}
- sbtest8:
- actualDataNodes: ds_${0..1}.sbtest8_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest8_${id % 100}
- sbtest9:
- actualDataNodes: ds_${0..1}.sbtest9_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest9_${id % 100}
- sbtest10:
- actualDataNodes: ds_${0..1}.sbtest10_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest10_${id % 100}
- defaultDatabaseStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: ds_${id % 2}
-```
-
-config-master_slave.yaml
-
-```yaml
-schemaName: sbtest
-dataSources:
- ds_0:
- url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
- username: root
- password:
- autoCommit: true
- connectionTimeout: 30000
- idleTimeout: 60000
- maxLifetime: 1800000
- maximumPoolSize: 256
-masterSlaveRule:
- name: ms_ds
- masterDataSourceName: ds_0
- slaveDataSourceNames:
- - ds_0
- - ds_0
-```
-
-config-sharding-master_slave.yaml
-
-```yaml
-schemaName: sbtest
-dataSources:
- primary_ds_0:
- url: jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
- username: root
- password:
- autoCommit: true
- connectionTimeout: 30000
- idleTimeout: 60000
- maxLifetime: 1800000
- maximumPoolSize: 256
- primary_ds_1:
- url: jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
- username: root
- password:
- autoCommit: true
- connectionTimeout: 30000
- idleTimeout: 60000
- maxLifetime: 1800000
- maximumPoolSize: 256
-shardingRule:
- tables:
- sbtest1:
- actualDataNodes: ds_${0..1}.sbtest1_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest1_${id % 100}
- sbtest2:
- actualDataNodes: ds_${0..1}.sbtest2_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest2_${id % 100}
- sbtest3:
- actualDataNodes: ds_${0..1}.sbtest3_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest3_${id % 100}
- sbtest4:
- actualDataNodes: ds_${0..1}.sbtest4_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest4_${id % 100}
- sbtest5:
- actualDataNodes: ds_${0..1}.sbtest5_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest5_${id % 100}
- sbtest6:
- actualDataNodes: ds_${0..1}.sbtest6_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest6_${id % 100}
- sbtest7:
- actualDataNodes: ds_${0..1}.sbtest7_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest7_${id % 100}
- sbtest8:
- actualDataNodes: ds_${0..1}.sbtest8_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest8_${id % 100}
- sbtest9:
- actualDataNodes: ds_${0..1}.sbtest9_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest9_${id % 100}
- sbtest10:
- actualDataNodes: ds_${0..1}.sbtest10_${0..99}
- tableStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: sbtest10_${id % 100}
- defaultDatabaseStrategy:
- inline:
- shardingColumn: id
- algorithmExpression: ds_${id % 2}
- masterSlaveRules:
- ds_0:
- masterDataSourceName: primary_ds_0
- slaveDataSourceNames: [primary_ds_0, primary_ds_0]
- loadBalanceAlgorithmType: ROUND_ROBIN
- ds_1:
- masterDataSourceName: primary_ds_1
- slaveDataSourceNames: [primary_ds_1, primary_ds_1]
- loadBalanceAlgorithmType: ROUND_ROBIN
-```
-
-config-encrypt.yaml
-
-```
-Unsupported
-```
-
-## Appendix 2
-
-plot_graph.py
-
-```python
-import sys
-import matplotlib.pyplot as plt
-import numpy as np
-def generate_graph(path, case_name):
- dataset = {
- 'build_num': [],
- 'master_version': [],
- 'master_xa': [],
- '4.1.1_version': [],
- '3.0.0_version': [],
- 'mysql_server': []
- }
- with open(path + '/.build_number.txt') as builds:
- for line in builds:
- dataset['build_num'].append(int(line))
- generate_data(path, case_name, dataset)
- print(dataset)
- fig, ax = plt.subplots()
- ax.grid(True)
- plt.title(case_name)
- data = [dataset['master_version'][-7:], dataset['master_xa'][-7:], dataset['4.1.1_version'][-7:], dataset['3.0.0_version'][-7:], dataset['mysql_server'][-7:]]
- columns = dataset['build_num'][-7:]
- rows = ['master', 'xa', '4.1.1', '3.0.0', 'mysql']
- rcolors = plt.cm.BuPu(np.full(len(rows), 0.1))
- ccolors = plt.cm.BuPu(np.full(len(columns), 0.1))
- the_table = plt.table(cellText=data, rowLabels=rows, colLabels=columns, rowColours=rcolors, colColours=ccolors,
- loc='bottom', bbox=[0.0, -0.50, 1, .28])
- plt.subplots_adjust(left=0.15, bottom=0.3, right=0.98)
- plt.xticks(range(14))
- ax.set_xticklabels(dataset['build_num'])
- plt.plot(dataset['master_version'], 'o-', color='magenta', label='master_version')
- plt.plot(dataset['master_xa'], 'o-', color='darkviolet', label='master_xa')
- plt.plot(dataset['4.1.1_version'], 'r--', color='blue', label='4.1.1_version')
- plt.plot(dataset['3.0.0_version'], 'r--', color='orange', label='3.0.0_version')
- plt.plot(dataset['mysql_server'], 'r--', color='lime', label='mysql_server')
- plt.xlim()
- plt.legend()
- plt.xlabel('build_num')
- plt.ylabel('transactions per second')
- plt.savefig('graph/' + path + '/' + case_name)
- plt.show()
-def generate_data(path, case_name, dataset):
- for build in dataset['build_num']:
- fill_dataset(build, case_name, dataset, path, 'master_version', '.master.txt')
- fill_dataset(build, case_name, dataset, path, 'master_xa', '.xa.txt')
- fill_dataset(build, case_name, dataset, path, '4.1.1_version', '.4_1_1.txt')
- fill_dataset(build, case_name, dataset, path, '3.0.0_version', '.3_0_0.txt')
- fill_dataset(build, case_name, dataset, path, 'mysql_server', '.mysql.txt')
-def fill_dataset(build, case_name, dataset, path, version, suffix):
- try:
- with open(path + '/' + str(build) + '/' + case_name + suffix) as version_master:
- value = 0
- for line in version_master:
- if 'transactions:' in line:
- items = line.split('(')
- value = float(items[1][:-10])
- dataset[version].append(value)
- except FileNotFoundError:
- dataset[version].append(0)
-if __name__ == '__main__':
- path = sys.argv[1]
- generate_graph(path, 'oltp_point_select')
- generate_graph(path, 'oltp_read_only')
- generate_graph(path, 'oltp_write_only')
- generate_graph(path, 'oltp_read_write')
- generate_graph(path, 'oltp_update_index')
- generate_graph(path, 'oltp_update_non_index')
- generate_graph(path, 'oltp_delete')
-```