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 f2f3fab Restore sysbench documents (#14240)
f2f3fab is described below
commit f2f3fab4f111db86fe51adaca6ab34441158064f
Author: Swastika Gupta <[email protected]>
AuthorDate: Tue Jan 11 08:12:56 2022 +0530
Restore sysbench documents (#14240)
* initials apache#13825
* Update
docs/document/content/reference/test/performance-test/sysbench-test.en.md
Co-authored-by: 吴伟杰 <[email protected]>
* updates
* Update sysbench-test.en.md
* sysbench-test.en.md updated
* Delete .gitpod.yml
* Update sysbench-test.en.md
Co-authored-by: 吴伟杰 <[email protected]>
---
docs/document/content/reference/test/_index.en.md | 2 +
...{sysbench-test.cn.md => performance-test.cn.md} | 0
...{sysbench-test.en.md => performance-test.en.md} | 2 +-
.../test/performance-test/sysbench-test.en.md | 1623 ++++++++++++++++----
4 files changed, 1341 insertions(+), 286 deletions(-)
diff --git a/docs/document/content/reference/test/_index.en.md
b/docs/document/content/reference/test/_index.en.md
index 3636e02..abfc20f 100644
--- a/docs/document/content/reference/test/_index.en.md
+++ b/docs/document/content/reference/test/_index.en.md
@@ -25,3 +25,5 @@ It includes SQL parser and SQL rewriter modules.
## Performance Test
Provide multiple performance test methods, includes Sysbench, JMH or TPCC and
so on.
+
+## Sysbench Test
\ No newline at end of file
diff --git
a/docs/document/content/reference/test/performance-test/sysbench-test.cn.md
b/docs/document/content/reference/test/performance-test/performance-test.cn.md
similarity index 100%
rename from
docs/document/content/reference/test/performance-test/sysbench-test.cn.md
rename to
docs/document/content/reference/test/performance-test/performance-test.cn.md
diff --git
a/docs/document/content/reference/test/performance-test/sysbench-test.en.md
b/docs/document/content/reference/test/performance-test/performance-test.en.md
similarity index 99%
copy from
docs/document/content/reference/test/performance-test/sysbench-test.en.md
copy to
docs/document/content/reference/test/performance-test/performance-test.en.md
index ec709e1..835a87d 100644
--- a/docs/document/content/reference/test/performance-test/sysbench-test.en.md
+++
b/docs/document/content/reference/test/performance-test/performance-test.en.md
@@ -1,5 +1,5 @@
+++
-title = "Performance Test with Sysbench"
+title = "Performance Test"
weight = 1
+++
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
index ec709e1..0502d8f 100644
--- a/docs/document/content/reference/test/performance-test/sysbench-test.en.md
+++ b/docs/document/content/reference/test/performance-test/sysbench-test.en.md
@@ -1,121 +1,469 @@
+++
-title = "Performance Test with Sysbench"
+title = "Sysbench Test"
weight = 1
+++
-## Target
+At least 5 machines are required:
-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.
+```
+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
+```
-## Test Scenarios
+#### Take the last 14 builds and save them in a hidden file
-### Single Route
+```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
+```
-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.
+#### 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
+```
-### Readwrite-splitting
+#### oltp_write_only
-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.
+```
+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
+```
-### Readwrite-splitting & Encrypt & Sharding
+#### oltp_read_write
-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.
+```
+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
+```
-### Full Route
+#### oltp_update_index
-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.
+```
+Prepare Statement (ID = 1): UPDATE sbtest1 SET k=k+1 WHERE id=?
+Execute Statement: ID = 1
+```
-## Testing Environment
+#### oltp_update_non_index
-### Table Structure of Database
+```
+Prepare Statement (ID = 1): UPDATE sbtest1 SET c=? WHERE id=?
+Execute Statement: ID = 1
+```
-The structure of table here refer to `sbtest` in `sysbench`
+#### oltp_delete
-```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`)
-);
+```
+Prepare Statement (ID = 1): DELETE FROM sbtest1 WHERE id=?
+Execute Statement: ID = 1
```
-### Test Scenarios Configuration
+## Appendix 1
-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.
+#### Master branch version
-#### Single Route Configuration
+server.yaml
```yaml
-schemaName: sharding_db
+rules:
+ -!AUTHORITY
+ users:
+ - root@%:root
+ - sharding@:sharding
+ provider:
+ type: ALL_PRIVILEGES_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-opentracing-enabled: false
+ 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://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
- password:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
+ password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
+ maxPoolSize: 256
+ minPoolSize: 256
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
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
+ maxPoolSize: 256
+ minPoolSize: 256
rules:
- !SHARDING
tables:
- tbl:
- actualDataNodes: ds_${0..3}.tbl${0..1023}
+ 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: k
- shardingAlgorithmName: tbl_table_inline
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_10
keyGenerateStrategy:
- column: id
- keyGeneratorName: snowflake
+ column: id
+ keyGeneratorName: snowflake
defaultDatabaseStrategy:
- inline:
+ standard:
shardingColumn: id
- shardingAlgorithmName: default_db_inline
- defaultTableStrategy:
- none:
+ shardingAlgorithmName: database_inline
shardingAlgorithms:
- tbl_table_inline:
+ 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: tbl${k % 1024}
- default_db_inline:
+ algorithm-expression: sbtest9_${id % 100}
+ table_inline_10:
type: INLINE
props:
- algorithm-expression: ds_${id % 4}
+ algorithm-expression: sbtest10_${id % 100}
keyGenerators:
snowflake:
type: SNOWFLAKE
@@ -123,313 +471,1018 @@ rules:
worker-id: 123
```
-#### Readwrite-splitting Configuration
+config-readwrite-splitting.yaml
```yaml
-schemaName: sharding_db
-
+schemaName: sbtest
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
+ ds_0:
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
+ maxPoolSize: 128
+ minPoolSize: 128
rules:
- !READWRITE_SPLITTING
dataSources:
pr_ds:
- writeDataSourceName: primary_ds
- readDataSourceNames:
- - replica_ds_0
+ primaryDataSourceName: ds_0
+ replicaDataSourceNames:
+ - ds_0
+ - ds_0
```
-#### Readwrite-splitting & Encrypt & Sharding Configuration
+config-shadow.yaml
```yaml
-schemaName: sharding_db
-
+schemaName: sbtest
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
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
+ maxPoolSize: 256
+ minPoolSize: 256
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
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
+ maxPoolSize: 256
+ minPoolSize: 256
rules:
- !SHARDING
tables:
- tbl:
- actualDataNodes: pr_ds_${0..3}.tbl${0..1023}
- databaseStrategy:
+ 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: tbl_database_inline
+ shardingAlgorithmName: table_inline_9
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ sbtest10:
+ actualDataNodes: ds_${0..1}.sbtest10_${0..99}
tableStrategy:
standard:
- shardingColumn: k
- shardingAlgorithmName: tbl_table_inline
+ shardingColumn: id
+ shardingAlgorithmName: table_inline_10
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
- bindingTables:
- - tbl
- defaultDataSourceName: primary_ds_1
- defaultTableStrategy:
- none:
+
+ defaultDatabaseStrategy:
+ standard:
+ shardingColumn: id
+ shardingAlgorithmName: database_inline
shardingAlgorithms:
- tbl_database_inline:
+ database_inline:
type: INLINE
props:
- algorithm-expression: pr_ds_${id % 4}
- tbl_table_inline:
+ algorithm-expression: ds_${id % 2}
+ table_inline_1:
type: INLINE
props:
- algorithm-expression: tbl${k % 1024}
- keyGenerators:
- snowflake:
- type: SNOWFLAKE
+ algorithm-expression: sbtest1_${id % 100}
+ table_inline_2:
+ type: INLINE
props:
- worker-id: 123
-- !READWRITE_SPLITTING
- dataSources:
- pr_ds_0:
- writeDataSourceName: primary_ds_0
- readDataSourceNames:
- - replica_ds_0
- loadBalancerName: round_robin
- pr_ds_1:
- writeDataSourceName: primary_ds_1
- readDataSourceNames:
- - replica_ds_1
- loadBalancerName: round_robin
- pr_ds_2:
- writeDataSourceName: primary_ds_2
- readDataSourceNames:
- - replica_ds_2
- loadBalancerName: round_robin
- pr_ds_3:
- writeDataSourceName: primary_ds_3
- readDataSourceNames:
- - replica_ds_3
- loadBalancerName: round_robin
- loadBalancers:
- round_robin:
- type: ROUND_ROBIN
-- !ENCRYPT:
- encryptors:
- aes_encryptor:
- type: AES
+ algorithm-expression: sbtest2_${id % 100}
+ table_inline_3:
+ type: INLINE
props:
- aes-key-value: 123456abc
- md5_encryptor:
- type: MD5
+ algorithm-expression: sbtest3_${id % 100}
+ table_inline_4:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest4_${id % 100}
+ table_inline_5:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest5_${id % 100}
+ table_inline_6:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest6_${id % 100}
+ table_inline_7:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest7_${id % 100}
+ table_inline_8:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest8_${id % 100}
+ table_inline_9:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest9_${id % 100}
+ table_inline_10:
+ type: INLINE
+ props:
+ algorithm-expression: sbtest10_${id % 100}
+ keyGenerators:
+ snowflake:
+ type: SNOWFLAKE
+ props:
+ worker-id: 123
+- !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:
- sbtest:
+ 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:
- c:
- plainColumn: c_plain
- cipherColumn: c_cipher
- encryptorName: aes_encryptor
pad:
- cipherColumn: pad_cipher
- encryptorName: md5_encryptor
+ 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:
+ pr_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: MGR
+ props:
+ group-name: 92504d5b-6dec-11e8-91ea-246e9612aaf1
```
-#### Full Route Configuration
+#### 4.1.1 version
+
+server.yaml
```yaml
-schemaName: sharding_db
+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://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
+ url:
jdbc:mysql://${host-mysql-1}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
+ maxPoolSize: 256
ds_1:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
+ url:
jdbc:mysql://${host-mysql-2}:3306/sbtest?serverTimezone=UTC&useSSL=false
+ username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
- maxPoolSize: 200
- ds_2:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
+ 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: 200
- ds_3:
- url: jdbc:mysql://***.***.***.***:****/ds?serverTimezone=UTC&useSSL=false
- username: test
+ 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: 200
-rules:
-- !SHARDING
+ maxPoolSize: 256
+shardingRule:
tables:
- tbl:
- actualDataNodes: ds_${0..3}.tbl1
+ sbtest1:
+ actualDataNodes: ds_${0..1}.sbtest1_${0..99}
tableStrategy:
- standard:
- shardingColumn: k
- shardingAlgorithmName: tbl_table_inline
- keyGenerateStrategy:
+ 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
- keyGeneratorName: snowflake
defaultDatabaseStrategy:
- standard:
+ inline:
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
- props:
- worker-id: 123
+ 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
```
-## Test Result Verification
+config-encrypt.yaml
-### 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=?
+```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
+```
-SELECT sql statement for full route:
-SELECT max(id) FROM tbl WHERE id%4=1
+#### 3.0.0 version
-SELECT sql statement for single route:
-SELECT id, k FROM tbl ignore index(`PRIMARY`) WHERE id=1 AND k=1
+server.yaml
-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=?
+```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
```
-### Jmeter Class
+config-sharding.yaml
-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
+```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}
+```
-### Compile & Build
+config-master_slave.yaml
-```shell
-git clone https://github.com/apache/shardingsphere-benchmark.git
-cd shardingsphere-benchmark/shardingsphere-benchmark
-mvn clean install
+```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
```
-### Perform Test
+config-sharding-master_slave.yaml
-```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
+```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
```
-### Process Result Data
+config-encrypt.yaml
-Make sure the location of result.jtl file is correct.
-```shell
-sh shardingsphere-benchmark/report/script/gen_report.sh
+```
+Unsupported
```
-### Display of Historical Performance Test Data
+## Appendix 2
-In progress, please wait.
-<!--
-The data of [benchmark
platform](https://shardingsphere.apache.org/benchmark/#/overview) is show daily
--->
+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')
+```
