MogDB benchmark测试配置

BenchmarkSQL5.0工具的安装和使用。

下载TPCC标准测试工具BenchmarkSQL5.0

yum install git -y
git clone -b 5.0-mysql-support-opt-2.1 https://github.com/pingcap/benchmarksql.git

安装ant

wget https://rpmfind.net/linux/openmandriva/4.2/repository/x86_64/main/release/ant-1.10.5-5-omv4000.noarch.rpm
rpm -ivh ant-1.10.5-5-omv4000.noarch.rpm 

安装jdk8

wget https://download.oracle.com/otn/java/jdk/8u311-b11/4d5417147a92418ea8b615e228bb6935/jdk-8u311-linux-x64.rpm
rpm -ivh  jdk-8u311-linux-  --force --nodeps

配置环境变量

vim /root/.bashrc
#追加三行
export JAVA_HOME=/usr/java/default
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:JAVA_HOME/lib:$BENCHMARKSQLPATH/run/ojdbc7.jar

编译BenchmarkSQL

[root@mogdb ~]# cd benchmarksql/
[root@mogdb benchmarksql]# ant
Buildfile: /root/benchmarksql/build.xml

init:

compile:
    [javac] Compiling 12 source files to /root/benchmarksql/build

dist:
    [mkdir] Created dir: /root/benchmarksql/dist
      [jar] Building jar: /root/benchmarksql/dist/BenchmarkSQL-5.0.jar

BUILD SUCCESSFUL
Total time: 1 second

下载对应版本jdbc

cd  /root/benchmarksql/lib/postgres/
wget https://opengauss.obs.cn-south-1.myhuaweicloud.com/2.1.0/x86/openGauss-2.1.0-JDBC.tar.gz
tar zxvf openGauss-2.1.0-JDBC.tar.gz

数据库中创建测试库及用户

[omm@mogdb ~]$ gsql -d postgres -p 26000 -r
postgres=# create database tpcc_db;
CREATE DATABASE
postgres=# \c tpcc_db
You are now connected to database "tpcc_db" as user "omm".
tpcc_db=# CREATE USER tpcc WITH PASSWORD "tpcc@123";
CREATE ROLE
tpcc_db=# GRANT ALL ON schema public TO tpcc;
GRANT

编辑benchmarksql配置文件

[root@mogdb postgres]# cd /root/benchmarksql/run/
[root@mogdb run]# vim props.mogdb
db=postgres
driver=org.postgresql.Driver
conn=jdbc:postgresql://172.16.71.29:26000/tpcc_db?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off&autoReconnect=true #修改连接字符串, 包含IP、端口号、数据库
user=tpcc #用户名
password=tpcc@123 #密码
warehouses=100  #仓位数
terminals=300 #并发数
runMins=5  #运行时间
runTxnsPerTerminal=0
loadWorkers=100
limitTxnsPerMin=0
terminalWarehouseFixed=false
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
resultDirectory=mogdb_result_%tY-%tm-%td_%tH%tM%tS

修改runBenchmark.sh文件中funcs.sh所在的实际路径。

[root@mogdb run]# vim runBenchmark.sh               
#!/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/benchmarksql/run/funcs.sh $1 #将此处路径修改为文件所在的实际路径

setCP || exit 1

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

java -cp "$myCP" $myOPTS jTPCC

初始化数据

[root@mogdb run]# cd /root/benchmarksql/run/
[root@mogdb run]# sh runDatabaseBuild.sh props.mogdb
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/buildFinish.sql
# ------------------------------------------------------------
-- ----
-- Extra commands to run after the tables are created, loaded,
-- indexes built and extra's created.
-- PostgreSQL version.
-- ----
vacuum analyze;

开始测试

[root@mogdb run]# sh runBenchmark.sh props.mogdb| tee runLog_`date +%m%d-%H%M%S`.log
19:10:41,863 [main] INFO   jTPCC : Term-00, 
19:10:41,867 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
19:10:41,867 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
19:10:41,868 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
19:10:41,868 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
19:10:41,868 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
19:10:41,874 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
19:10:41,874 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
19:10:41,874 [main] INFO   jTPCC : Term-00, 
19:10:41,874 [main] INFO   jTPCC : Term-00, db=postgres
19:10:41,874 [main] INFO   jTPCC : Term-00, driver=org.postgresql.Driver
19:10:41,874 [main] INFO   jTPCC : Term-00, conn=jdbc:postgresql://172.16.71.29:26000/tpcc_db?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off 
19:10:41,874 [main] INFO   jTPCC : Term-00, user=tpcc
19:10:41,874 [main] INFO   jTPCC : Term-00, 
19:10:41,875 [main] INFO   jTPCC : Term-00, warehouses=10
19:10:41,875 [main] INFO   jTPCC : Term-00, terminals=20
19:10:41,876 [main] INFO   jTPCC : Term-00, runMins=5
19:10:41,876 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
19:10:41,876 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=false
19:10:41,876 [main] INFO   jTPCC : Term-00, 
19:10:41,876 [main] INFO   jTPCC : Term-00, newOrderWeight=45
19:10:41,876 [main] INFO   jTPCC : Term-00, paymentWeight=43
19:10:41,876 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
19:10:41,876 [main] INFO   jTPCC : Term-00, deliveryWeight=4
19:10:41,876 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
19:10:41,876 [main] INFO   jTPCC : Term-00, 
19:10:41,876 [main] INFO   jTPCC : Term-00, resultDirectory=null
19:10:41,876 [main] INFO   jTPCC : Term-00, osCollectorScript=null
19:10:41,877 [main] INFO   jTPCC : Term-00, 
19:10:41,961 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 162
19:10:41,961 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    47
19:10:41,961 [main] INFO   jTPCC : Term-00, 
Term-00, Running Average tpmTOTAL: 29344.10    Current tpmTOTAL: 973080    Memory Usage: 35MB / 105MB          
19:15:42,198 [Thread-5] INFO   jTPCC : Term-00, 
19:15:42,198 [Thread-5] INFO   jTPCC : Term-00, 
19:15:42,198 [Thread-5] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 13177.97
19:15:42,198 [Thread-5] INFO   jTPCC : Term-00, Measured tpmTOTAL = 29340.57
19:15:42,198 [Thread-5] INFO   jTPCC : Term-00, Session Start     = 2021-12-28 19:10:42
19:15:42,199 [Thread-5] INFO   jTPCC : Term-00, Session End       = 2021-12-28 19:15:42
19:15:42,199 [Thread-5] INFO   jTPCC : Term-00, Transaction Count = 146740
19:15:42,199 [Thread-5] INFO   jTPCC : executeTime[Payment]=1231349
19:15:42,199 [Thread-5] INFO   jTPCC : executeTime[Order-Status]=101891
19:15:42,199 [Thread-5] INFO   jTPCC : executeTime[Delivery]=310386
19:15:42,199 [Thread-5] INFO   jTPCC : executeTime[Stock-Level]=915211
19:15:42,199 [Thread-5] INFO   jTPCC : executeTime[New-Order]=3441011

安装R

yum install epel-release
yum install R

生成测试报告

[root@mogdb run]# bash generateReport.sh  mogdb_result_2021-12-28_192729/
Generating mogdb_result_2021-12-28_192729//tpm_nopm.png ... OK
Generating mogdb_result_2021-12-28_192729//latency.png ... OK
Generating mogdb_result_2021-12-28_192729//report.html ... OK

测试数据清空

[root@mogdb run]# sh runDatabaseDestroy.sh props.mogdb
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableDrops.sql
# ------------------------------------------------------------
drop table bmsql_config;
drop table bmsql_new_order;
drop table bmsql_order_line;
drop table bmsql_oorder;
drop table bmsql_history;
drop table bmsql_customer;
drop table bmsql_stock;
drop table bmsql_item;
drop table bmsql_district;
drop table bmsql_warehouse;
drop sequence bmsql_hist_id_seq;