PolarDB:TPC-C tests

Download and extract BenchmarkSQL

Download benchmarksql.tar.gz and extract it on the ECS instance:

tar xzvf benchmarksql.tar.gz

BenchmarkSQL does not support the MySQL protocol by default. This example uses open source BenchmarkSQL 5.0, compiled with MySQL protocol support. It requires JDK 8 or later and is compatible with MySQL 5.7 and MySQL 8.0.

Configure the connection

On the ECS instance, navigate to the benchmarksql/run directory and open props.mysql:

cd benchmarksql/run
vi props.mysql

Replace the placeholder values with your PolarDB-X connection details:

db=mysql
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://{HOST}:{PORT}/tpcc?readOnlyPropagatesToServer=false&rewriteBatchedStatements=true&failOverReadOnly=false&connectTimeout=3000&socketTimeout=90000&allowMultiQueries=true&clobberStreamingResults=true&characterEncoding=utf8&netTimeoutForStreamingResults=0&autoReconnect=true
user={USER}
password={PASSWORD}

warehouses=1000
loadWorkers=100

terminals=128
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=5
//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

Key parameters:

Load the data

Run the following commands on the ECS instance to create the schema and import data:

cd benchmarksql/run/sql.common
cp tableCreates.sql.auto tableCreates.sql
cd ..
nohup ./runDatabaseBuild.sh props.mysql &

The import uses 100 concurrent load workers to load over 500 million rows, which takes several hours. Running the import with nohup keeps it running in the background if the SSH session disconnects.

Verify data integrity

After the import completes, connect to the PolarDB-X instance and run the following SQL statements. If all result sets are empty, the data loaded successfully.

SELECT a.* FROM (SELECT w_id, w_ytd FROM bmsql_warehouse) a LEFT JOIN (SELECT d_w_id, SUM(d_ytd) AS d_ytd_sum FROM bmsql_district GROUP BY d_w_id) b ON a.w_id = b.d_w_id AND a.w_ytd = b.d_ytd_sum WHERE b.d_w_id IS NULL;

SELECT a.* FROM (SELECT d_w_id, d_id, D_NEXT_O_ID - 1 AS d_n_o_id FROM bmsql_district) a LEFT JOIN (SELECT o_w_id, o_d_id, MAX(o_id) AS o_id_max FROM bmsql_oorder GROUP BY  o_w_id, o_d_id) b ON a.d_w_id = b.o_w_id AND a.d_id = b.o_d_id AND a.d_n_o_id = b.o_id_max WHERE b.o_w_id IS NULL;

SELECT a.* FROM (Select d_w_id, d_id, D_NEXT_O_ID - 1 AS d_n_o_id FROM bmsql_district) a LEFT JOIN (SELECT no_w_id, no_d_id, MAX(no_o_id) AS no_id_max FROM bmsql_new_order GROUP BY no_w_id, no_d_id) b ON a.d_w_id = b.no_w_id AND a.d_id = b.no_d_id AND a.d_n_o_id = b.no_id_max WHERE b.no_id_max IS NULL;

SELECT * FROM (SELECT (COUNT(no_o_id)-(MAX(no_o_id)-MIN(no_o_id)+1)) AS diff FROM bmsql_new_order GROUP BY no_w_id, no_d_id) a WHERE diff != 0;

SELECT a.* FROM (SELECT o_w_id, o_d_id, SUM(o_ol_cnt) AS o_ol_cnt_cnt FROM bmsql_oorder  GROUP BY o_w_id, o_d_id) a LEFT JOIN (SELECT ol_w_id, ol_d_id, COUNT(ol_o_id) AS ol_o_id_cnt FROM bmsql_order_line GROUP BY ol_w_id, ol_d_id) b ON a.o_w_id = b.ol_w_id AND a.o_d_id = b.ol_d_id AND a.o_ol_cnt_cnt = b.ol_o_id_cnt WHERE b.ol_w_id IS NULL;

SELECT a.* FROM (SELECT d_w_id, SUM(d_ytd) AS d_ytd_sum FROM bmsql_district GROUP BY d_w_id) a LEFT JOIN (Select w_id, w_ytd FROM bmsql_warehouse) b ON a.d_w_id = b.w_id AND a.d_ytd_sum = b.w_ytd WHERE b.w_id IS NULL;