BenchmarkSQL对数据库性能测试示例
最近在做TIDB的上线部署，部署完成后需要对其进行性能测试，所以我们就用了benchmarkSQL这个工具啦
TiDB文档地址：https://pingcap.com/docs-cn/stable/benchmark/benchmark-tidb-using-tpcc/
#首先我们需要下载benchmarksql git clone -b 5.0-mysql-support-opt-2.1 https://github.com/pingcap/benchmarksql.git #安装好 java , ant sudo yum install -y java ant #构建ant cd benchmarksql ant
#远程复制服务命令：(将下载好的benchmarksql通过scp远程复制到指定服务器，如果网络是通的则可以直接SecureFX上传) scp -r user@10.22.33.44:/home/user/tidb/benchmarksql ./
接下来我们就要开始准备测试数据库性能了，这里我们是使用benchmarksql测试tidb （和Mysql类似，所以我们会参考mysql的配置信息来做调整）
1.参考 benchmarksql/run/ props.mysql 配置文件
db=mysql driver=com.mysql.jdbc.Driver conn=jdbc:mysql://localhost:4000/tpcc?useSSL=false&useServerPrepStmts=true&useConfigs=maxPerformance&rewriteBatchedStatements=true user=root password= warehouses=1 loadWorkers=4 terminals=1 //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
warehouses=40 # 使用 40 个 warehouse terminals=100 # 使用 100 个终端 loadWorkers=10 # 导入数据的并发数
参考以上配置文件新建我们自己的测试配置文件 benchmark_tidb.properties 并调整参数信息：(需要先新建对应的库信息 在数据库中新建数据库：benchmark_test )
db=mysql driver=com.mysql.cj.jdbc.Driver conn=jdbc:mysql://10.55.33.44:4000/benchmark_test user=root password= warehouses=40 loadWorkers=10 terminals=100 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal=0 //To run for specified minutes- runTxnsPerTerminal must equal zero runMins=2 //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
2.开始运行 进入benchmarkSQL的run目录，依次执行以下命令
./runSQL.sh benchmark_tidb.properties ./sql.common/tableCreates.sql
./runSQL.sh benchmark_tidb.properties ./sql.common/indexCreates.sql
./runDatabaseBuild.sh benchmark_tidb.properties
./runBenchmark.sh benchmark_tidb.properties
netstat -tlnp #说明 l:listening t: tcp n:number p:process