TeslaCN commented on a change in pull request #14240:
URL: https://github.com/apache/shardingsphere/pull/14240#discussion_r776159224
##########
File path:
docs/document/content/reference/test/performance-test/sysbench-test.en.md
##########
@@ -1,435 +1,1425 @@
+++
-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
+
+```
+Jenins: The latest version
+Sysbench: 1.0.20
+ShardingSphere-Proxy: package from master branch
+MySQL Server: 5.7.28
+```
+
+## Test Program
+
+According to the above hardware environment, the configuration parameters are
as follows,
+and the parameters should be adjusted according to the changes in the hardware
environment.
+
+#### ShardingSphere-Proxy Configuration
+
+```
+Proxy runs on ${host-proxy}
+Version includes: Master branch, 4.1.1, 3.0.0
+Scenarios: config-sharding, config-replica-query,
config-sharding-replica-query, config-encrypt
+Configurations: Refer to Appendix 1
+```
+
+#### MySQL Server Configuration
+
+Two MySQL instances runs on `${host-mysql-1}` and `${host-mysql-2}` machines
respectively.
+```
+Need to create the 'sbtest' database on both instances in advance.
+Set parameter: max_prepared_stmt_count = 500000
+Set parameter: max_connections = 2000
+```
+
+#### Jenkins Configuration
+
+Create 6 Jenkins tasks, and each task calls the next task in turn: (runs on
the `${host-jenkins}` machine).
+```
+1. sysbench_install: Pull the latest code, package the Proxy compression
package
+```
+
+The following tasks are run on a separate Sysbench pressure generating machine
via Jenkins slave: (runs on the `{host-sysbench}` machine)
+```
+2. sysbench_sharding:
+ a. Sharding scenarios for remote deployment of various versions of Proxy
+ b. Execute Sysbench command to pressure test Proxy
+ c. Execute Sysbench command to pressure test MySQL Server
+ d. Save Sysbench stress test results
+ e. Use drawing scripts to generate performance curves and tables (see
Appendix 2 for drawing scripts)
+3. sysbench_master_slave:
+ a. Read and write separation scenarios for remote deployment of various
versions of Proxy
+ b. Execute Sysbench command to pressure test Proxy
+ c. Execute Sysbench command to pressure test MySQL Server
+ d. Save Sysbench stress test results
+ e. Use drawing scripts to generate performance curves and tables
+4. sysbench_sharding_master_slave:
+ a. Remote deployment of sharding + read-write splitting scenarios of
various versions of Proxy
+ b. Execute Sysbench command to pressure test Proxy
+ c. Execute Sysbench command to pressure test MySQL Server
+ d. Save Sysbench stress test results
+ e. Use drawing scripts to generate performance curves and tables
+5. sysbench_encrypt:
+ a. Encryption scenarios for remote deployment of various versions of Proxy
+ b. Execute Sysbench command to pressure test Proxy
+ c. Execute Sysbench command to pressure test MySQL Server
+ d. Save Sysbench stress test results
+ e. Use drawing scripts to generate performance curves and tables
+6. sysbench_result_aggregation:
+ a. Re-execute the drawing script for the pressure test results of all tasks
+ python3 plot_graph.py sharding
+ python3 plot_graph.py ms
+ python3 plot_graph.py sharding_ms
+ python3 plot_graph.py encrypt
+ b. Use Jenkins "Publish HTML reports" plugin to integrate all images into
one HTML page
+```
+
+## Testing Process
+
+Take sysbench sharding as an example (other scenarios are similar)
+
+#### Enter the Sysbench pressure test result directory
+
+```bash
+cd /home/jenkins/sysbench_res/sharding
+```
+
+#### Create the folder for this build
+
+```bash
+mkdir $BUILD_NUMBER
+```
+
+#### Take the last 14 builds and save them in a hidden file
+
+```bash
+ls -v | tail -n14 > .build_number.txt
+```
-## Test Scenarios
+#### Deployment and stress testing
-### Single Route
+Step 1: Execute remote deployment script to deploy Proxy to `{host-proxy}`
-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.
+./deploy_sharding.sh
-### Readwrite-splitting
+```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
-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.
+```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
+```
-### Readwrite-splitting & Encrypt & Sharding
+4.1.1, 3.0.0, three scenarios of direct connection to MySQL, repeat steps 1
and 2 above.
-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.
+#### Execute stop proxy script
-### Full Route
+./stop_proxy.sh
-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.
+```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
+```
-## Testing Environment
+#### Generate pressure test curve picture
-### Table Structure of Database
+```bash
+# Generate graph
+cd /home/jenkins/sysbench_res/
+python3 plot_graph.py sharding
+```
-The structure of table here refer to `sbtest` in `sysbench`
+#### Use Jenkins Publish HTML reports plugin to publish pictures to the page
-```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`)
-);
```
+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
-### Test Scenarios Configuration
+```
+Prepare Statement (ID = 1): SELECT c FROM sbtest1 WHERE id=?
+Execute Statement: ID = 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.
+#### oltp_read_only
-#### Single Route Configuration
+```
+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
-schemaName: sharding_db
+users:
+ - root@%:root
+ - sharding@:sharding
Review comment:
@Swastyy The `server.yaml` can be found here
https://github.com/apache/shardingsphere/blob/master/shardingsphere-proxy/shardingsphere-proxy-bootstrap/src/main/resources/conf/server.yaml
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
