【磐维数据库】TPC-C压测

1.工具介绍
BenchmarkSQL是用Java实现的，使用JDBC对SQL数据库进行压力测试。总体架构是一系列处理模拟终端、用户和应用程序线程的数据结构、队列和线程组。

下载地址：https://sourceforge.net/projects/benchmarksql/

2.磐维数据库压测
2.1 安装工具

unzip benchmarksql-5.0.zip

2.2 创建数据库和用户

create user pwdb_exporter password 'xxx';
alter user pwdb_exporter;
create database benchmarksql owner pwdb_exporter;

2.3 修改配置文件props.pg

db=postgres  
driver=org.postgresql.Driver
conn=jdbc:postgresql://x.x.x.x:17700/postgres
user=pwdb_exporter
password=xxx

warehouses=500  #仓库数量
loadWorkers=100  # 数据库初始化数据时候的进程数

terminals=500  #终端数量（并发数）
//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=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

下面五个值的总和必须等于100，默认值为：45, 43, 4, 4，4 ，与TPC-C测试定义的比例一致，实际操作过程中，可以调整比重来适应各种场景。
newOrderWeight=45 新订单事务占总事务的45%
paymentWeight=43 支付订单事务占总事务的43%
orderStatusWeight=4 订单状态事务占总事务的4%
deliveryWeight=4 到货日期事务占总事务的4%
stockLevelWeight=4 查看现存货品的事务占总事务的4%


2.4 初始化环境
创建表和索引
cd /home/omm/benchmarksql/benchmarksql-5.0-x86/run/
./runDataBuild.sh props.pg > build.log

#!/bin/sh

if [ $# -lt 1 ] ; then
    echo "usage: $(basename $0) PROPS [OPT VAL [...]]" >&2
    exit 2
fi

PROPS="$1"
shift
if [ ! -f "${PROPS}" ] ; then
    echo "${PROPS}: no such file or directory" >&2
    exit 1
fi
DB="$(grep '^db=' $PROPS | sed -e 's/^db=//')"

BEFORE_LOAD="tableCreates"
#AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
AFTER_LOAD="indexCreates buildFinish"

#创建表
for step in ${BEFORE_LOAD} ; do
    ./runSQL.sh "${PROPS}" $step
done

./runLoader.sh "${PROPS}" $*

#创建索引
for step in ${AFTER_LOAD} ; do
    ./runSQL.sh "${PROPS}" $step
done

说明：表创建在public下，pwdb_exporter 需要给sysadmin权限，否则建表语句会权限不足导致执行失败。

2.5 压测
cd /home/omm/benchmarksql/benchmarksql-5.0-x86/run/
修改runBenchmark后执行
./runBenchmark props.pg > bench.log

#!/usr/bin/env bash

if [ $# -ne 1 ] ; then
    echo "usage: $(basename $0) PROPS_FILE" >&2
    exit 2
fi

SEQ_FILE="./.jTPCC_run_seq.dat"
if [ ! -f "${SEQ_FILE}" ] ; then
    echo "0" > "${SEQ_FILE}"
fi
SEQ=$(expr $(cat "${SEQ_FILE}") + 1) || exit 1
echo "${SEQ}" > "${SEQ_FILE}"

#source /root/tidb/benchmarksql-5.0-mysql-support-opt-2.1/run/funcs.sh $1
source /home/omm/benchmarksql/benchmarksql-5.0-x86/run/funcs.sh $1

setCP || exit 1

myOPTS="-Dprop=$1 -DrunID=${SEQ}"

java -cp "$myCP" $myOPTS jTPCC

2.6 查看 bench.log文件查看TPC-C指标

2.7 清除压测数据

drop database benchmarksql;

参考：
https://benchmarksql.readthedocs.io/en/latest/TPCC/
https://www.cnblogs.com/haha029/p/17371480.html