问题
使用tpcc压测shardingproxy性能损失较大，性能只有直连的27%左右
直连tpmTOTAL: 58605.9
通过shardingproxy连接tpmTOTAL: 16052.87
shardingproxy本身只使用了读写分离功能，请问要如何配置才能提升这种场景的性能？
直连mysql的压测结果
root@ubuntu:/usr/local/benchmarksql-5.0/run# ./runBenchmark.sh mysql.properties
17:32:37,282 [main] INFO jTPCC : Term-00,
17:32:37,285 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
17:32:37,285 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
17:32:37,285 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
17:32:37,285 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
17:32:37,285 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
17:32:37,287 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
17:32:37,287 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
17:32:37,287 [main] INFO jTPCC : Term-00,
17:32:37,287 [main] INFO jTPCC : Term-00, db=mysql
17:32:37,287 [main] INFO jTPCC : Term-00, driver=com.mysql.jdbc.Driver
17:32:37,287 [main] INFO jTPCC : Term-00, conn=jdbc:mysql://xxx:6316/proxytest?useSSL=false
17:32:37,288 [main] INFO jTPCC : Term-00, user=proxytest
17:32:37,288 [main] INFO jTPCC : Term-00,
17:32:37,288 [main] INFO jTPCC : Term-00, warehouses=20
17:32:37,288 [main] INFO jTPCC : Term-00, terminals=4
17:32:37,289 [main] INFO jTPCC : Term-00, runMins=10
17:32:37,289 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
17:32:37,289 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
17:32:37,290 [main] INFO jTPCC : Term-00,
17:32:37,290 [main] INFO jTPCC : Term-00, newOrderWeight=45
17:32:37,290 [main] INFO jTPCC : Term-00, paymentWeight=43
17:32:37,290 [main] INFO jTPCC : Term-00, orderStatusWeight=4
17:32:37,290 [main] INFO jTPCC : Term-00, deliveryWeight=4
17:32:37,290 [main] INFO jTPCC : Term-00, stockLevelWeight=4
17:32:37,290 [main] INFO jTPCC : Term-00,
17:32:37,290 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
17:32:37,290 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
17:32:37,290 [main] INFO jTPCC : Term-00,
17:32:37,308 [main] INFO jTPCC : Term-00, copied mysql.properties to my_result_2021-12-22_173237/run.properties
17:32:37,308 [main] INFO jTPCC : Term-00, created my_result_2021-12-22_173237/data/runInfo.csv for runID 34
17:32:37,308 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2021-12-22_173237/data/result.csv
17:32:37,309 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
17:32:37,309 [main] INFO jTPCC : Term-00, osCollectorInterval=1
17:32:37,309 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
17:32:37,309 [main] INFO jTPCC : Term-00, osCollectorDevices=net_eth0 blk_sda
17:32:37,364 [main] INFO jTPCC : Term-00,
17:32:37,636 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 110
17:32:37,636 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 227
17:32:37,636 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 58605.80 Current tpmTOTAL: 3877992 Memory Usage: 133MB / 921MB
17:42:37,675 [Thread-4] INFO jTPCC : Term-00,
17:42:37,675 [Thread-4] INFO jTPCC : Term-00,
17:42:37,675 [Thread-4] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 26331.16
17:42:37,675 [Thread-4] INFO jTPCC : Term-00, Measured tpmTOTAL = 58605.9
17:42:37,675 [Thread-4] INFO jTPCC : Term-00, Session Start = 2021-12-22 17:32:37
17:42:37,675 [Thread-4] INFO jTPCC : Term-00, Session End = 2021-12-22 17:42:37
17:42:37,675 [Thread-4] INFO jTPCC : Term-00, Transaction Count = 586061
使用shardingproxy的压测结果
root@ubuntu:/usr/local/benchmarksql-5.0/run# ./runBenchmark.sh shardingproxy.properties
10:01:15,846 [main] INFO jTPCC : Term-00,
10:01:15,848 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
10:01:15,849 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
10:01:15,849 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
10:01:15,849 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
10:01:15,849 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
10:01:15,851 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
10:01:15,851 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
10:01:15,851 [main] INFO jTPCC : Term-00,
10:01:15,851 [main] INFO jTPCC : Term-00, db=mysql
10:01:15,851 [main] INFO jTPCC : Term-00, driver=com.mysql.jdbc.Driver
10:01:15,851 [main] INFO jTPCC : Term-00, conn=jdbc:mysql://127.0.0.1:3307/TESTDB?useSSL=false
10:01:15,851 [main] INFO jTPCC : Term-00, user=root
10:01:15,852 [main] INFO jTPCC : Term-00,
10:01:15,852 [main] INFO jTPCC : Term-00, warehouses=20
10:01:15,852 [main] INFO jTPCC : Term-00, terminals=4
10:01:15,853 [main] INFO jTPCC : Term-00, runMins=10
10:01:15,853 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
10:01:15,853 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
10:01:15,854 [main] INFO jTPCC : Term-00,
10:01:15,854 [main] INFO jTPCC : Term-00, newOrderWeight=45
10:01:15,854 [main] INFO jTPCC : Term-00, paymentWeight=43
10:01:15,854 [main] INFO jTPCC : Term-00, orderStatusWeight=4
10:01:15,854 [main] INFO jTPCC : Term-00, deliveryWeight=4
10:01:15,854 [main] INFO jTPCC : Term-00, stockLevelWeight=4
10:01:15,854 [main] INFO jTPCC : Term-00,
10:01:15,854 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
10:01:15,854 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
10:01:15,854 [main] INFO jTPCC : Term-00,
10:01:15,871 [main] INFO jTPCC : Term-00, copied shardingproxy.properties to my_result_2021-12-23_100115/run.properties
10:01:15,871 [main] INFO jTPCC : Term-00, created my_result_2021-12-23_100115/data/runInfo.csv for runID 28
10:01:15,872 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2021-12-23_100115/data/result.csv
10:01:15,872 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
10:01:15,872 [main] INFO jTPCC : Term-00, osCollectorInterval=1
10:01:15,873 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
10:01:15,873 [main] INFO jTPCC : Term-00, osCollectorDevices=net_eth0 blk_sda
10:01:15,931 [main] INFO jTPCC : Term-00,
10:01:16,200 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 213
10:01:16,201 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 136
10:01:16,201 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 16052.97 Current tpmTOTAL: 1062588 Memory Usage: 65MB / 778MB
10:11:16,256 [Thread-3] INFO jTPCC : Term-00,
10:11:16,256 [Thread-3] INFO jTPCC : Term-00,
10:11:16,257 [Thread-3] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 7210.7
10:11:16,257 [Thread-3] INFO jTPCC : Term-00, Measured tpmTOTAL = 16052.87
10:11:16,257 [Thread-3] INFO jTPCC : Term-00, Session Start = 2021-12-23 10:01:16
10:11:16,257 [Thread-3] INFO jTPCC : Term-00, Session End = 2021-12-23 10:11:16
10:11:16,257 [Thread-3] INFO jTPCC : Term-00, Transaction Count = 160532
说明
两次压测都是按 清理数据 -> 准备初始化数据 -> 等待主从同步完毕 -> 开始压测 的步骤进行压测；
配置
shardingproxy配置
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: 127.0.0.1:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: false
使用distSQL创建数据源与读写分离规则
CREATE DATABASE TESTDB;
USE TESTDB;
ADD RESOURCE hostM1 (
HOST=<线上ip1>,
PORT=6316,
DB=proxytest,
USER=proxytest,
PASSWORD=password
),hostM2 (
HOST=<线上ip2>,
PORT=6316,
DB=proxytest,
USER=proxytest,
PASSWORD=password
),hostS1 (
HOST=<线上ip3>,
PORT=6316,
DB=proxytest,
USER=proxytest,
PASSWORD=password
);
CREATE READWRITE_SPLITTING RULE ms_group_0 (
WRITE_RESOURCE=hostM1,
READ_RESOURCES(hostM2,hostS1),
TYPE(NAME=random)
);
benchmarksql配置
db=mysql
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://127.0.0.1:3307/TESTDB?useSSL=false
user=root
password=root
warehouses=20
loadWorkers=21
terminals=4
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=10
//Number of total transactions per minute
limitTxnsPerMin=0
//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=true
//The following five values must add up to 100
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
osCollectorScript=./misc/os_collector_linux.py
osCollectorInterval=1
//osCollectorSSHAddr=user@dbhost
osCollectorDevices=net_eth0 blk_sda
使用环境：
- 系统：ubuntu
- mysql集群：线上机器部署3台mysql:5.7集群，已配置主从同步
- shardingproxy：5.0.0版本，单节点部署，使用zk作为配置中心
- benchmarksql：5.0版本