使用benchmarksql对PostgreSQL进行TPC-C测试

安装benchmarksql

• 下载
  https://sourceforge.net/projects/benchmarksql/
• 解压

tar -zxvf benchmarksql-5.0.zip
cd benchmarksql-5.0/

安装JAVA相关管径

sudo apt install openjdk-11-jdk
sudo apt install ant

系统环境

配置benchmarksql

• 编译

cd /home/frank/tools/benchmarksql-5.0
ant
cd run

vim props.pg

db=postgres
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5432/benchmarksql
user=frank
password=123456

warehouses=1
loadWorkers=4

terminals=1
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=10
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=0
//Number of total transactions per minute
limitTxnsPerMin=300

//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

导入测试表

./runDatabaseBuild.sh props.pg

执行测试

./runBenchmark.sh props.pg

frank@DESKTOP-6NF3B9K:~/tools/benchmarksql-5.0/run$ ./runBenchmark.sh props.pg
18:57:03,967 [main] INFO   jTPCC : Term-00,
18:57:03,969 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
18:57:03,969 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
18:57:03,969 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
18:57:03,969 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
18:57:03,969 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
18:57:03,971 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
18:57:03,971 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
18:57:03,971 [main] INFO   jTPCC : Term-00,
18:57:03,977 [main] INFO   jTPCC : Term-00, db=postgres
18:57:03,978 [main] INFO   jTPCC : Term-00, driver=org.postgresql.Driver
18:57:03,978 [main] INFO   jTPCC : Term-00, conn=jdbc:postgresql://localhost:5432/benchmarksql
18:57:03,978 [main] INFO   jTPCC : Term-00, user=frank
18:57:03,978 [main] INFO   jTPCC : Term-00,
18:57:03,978 [main] INFO   jTPCC : Term-00, warehouses=1
18:57:03,978 [main] INFO   jTPCC : Term-00, terminals=1
18:57:03,981 [main] INFO   jTPCC : Term-00, runTxnsPerTerminal=10
18:57:03,981 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=300
18:57:03,981 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true
18:57:03,981 [main] INFO   jTPCC : Term-00,
18:57:03,981 [main] INFO   jTPCC : Term-00, newOrderWeight=45
18:57:03,981 [main] INFO   jTPCC : Term-00, paymentWeight=43
18:57:03,982 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
18:57:03,982 [main] INFO   jTPCC : Term-00, deliveryWeight=4
18:57:03,982 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
18:57:03,982 [main] INFO   jTPCC : Term-00,
18:57:03,982 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
18:57:03,982 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
18:57:03,982 [main] INFO   jTPCC : Term-00,
18:57:04,000 [main] INFO   jTPCC : Term-00, copied props.pg to my_result_2022-03-20_185703/run.properties
18:57:04,004 [main] INFO   jTPCC : Term-00, created my_result_2022-03-20_185703/data/runInfo.csv for runID 4
18:57:04,005 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2022-03-20_185703/data/result.csv
18:57:04,006 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
18:57:04,006 [main] INFO   jTPCC : Term-00, osCollectorInterval=1
18:57:04,006 [main] INFO   jTPCC : Term-00, osCollectorSSHAddr=null
18:57:04,006 [main] INFO   jTPCC : Term-00, osCollectorDevices=net_eth0 blk_sda
18:57:04,019 [main] INFO   jTPCC : Term-00,
18:57:04,104 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 200
18:57:04,105 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    119
18:57:04,105 [main] INFO   jTPCC : Term-00,                                                                                                                        Te18:57:06,155 [Thread-1] INFO   jTPCC : Term-00, nt tpmTOTAL: 84    Memory Usage: 18MB / 126MB
18:57:06,159 [Thread-1] INFO   jTPCC : Term-00,
18:57:06,161 [Thread-1] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 178.74
18:57:06,161 [Thread-1] INFO   jTPCC : Term-00, Measured tpmTOTAL = 327.7
18:57:06,161 [Thread-1] INFO   jTPCC : Term-00, Session Start     = 2022-03-20 18:57:04
18:57:06,161 [Thread-1] INFO   jTPCC : Term-00, Session End       = 2022-03-20 18:57:06
18:57:06,162 [Thread-1] INFO   jTPCC : Term-00, Transaction Count = 10