You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/01/06 03:36:41 UTC
[GitHub] [shardingsphere] Swastyy commented on a change in pull request #14240: Restore sysbench documents
Swastyy commented on a change in pull request #14240:
URL: https://github.com/apache/shardingsphere/pull/14240#discussion_r779279181
##########
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:
Done.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org