暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / MogDB V3.0.0 && BenchmarkSQL5.0工具部署与使用

MogDB V3.0.0 && BenchmarkSQL5.0工具部署与使用

原创 由迪 2023-11-28
504

原作者：张均

    1. 实验环境
    1. 安装部署
    1. 测试步骤
    • 3.1 创建数据库tpcc_db和用户tpcc
    • 3.2 编辑BenchmarkSQL配置文件
    • 3.3 装载数据
    • 3.4 修改funcs.sh所在的实际路径
    • 3.5 开始测试
    • 3.6 TPCC跑分结果
    • 3.7 htop工具观察cpu情况
    • 3.8 清除数据
    1. 查看测试报告
    1. 装载数据中遇到的问题
    • 5.1 FATAL: Invalid username/password,login denied.
    • 5.2 Exception in thread “main” java.lang.NumberFormatException: For input string: "100 "

1. 实验环境

实验环境为VMware虚拟机搭建的centOS环境：

IP 数据库 操作系统 内存 硬盘 工具
192.168.213.34 MogDB V3.0.0 CentOS 7.9 4G 20G BenchmarkSQL5.0

2. 安装部署

  • 下载BenchmarkSQL5.0安装包
[root@mogdb ~]# git clone -b 5.0-mysql-support-opt-2.1 https://github.com/pingcap/benchmarksql.git
Cloning into 'tpcc-mysql'...
remote: Enumerating objects: 106, done.
remote: Total 106 (delta 0), reused 0 (delta 0), pack-reused 106
Receiving objects: 100% (106/106), 64.46 KiB | 225.00 KiB/s, done.
Resolving deltas: 100% (30/30), done.
  • 安装JDK和ant
[root@localhost software]# rpm -ivh jdk-8u301-linux-x64.rpm ant-1.9.4-2.el7.noarch.rpm --force --nodeps
warning: jdk-8u301-linux-x64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:ant-0:1.9.4-2.el7                ################################# [ 50%]
   2:jdk1.8-2000:1.8.0_301-fcs        ################################# [100%]
Unpacking JAR files...
	tools.jar...
	plugin.jar...
	javaws.jar...
	deploy.jar...
	rt.jar...
	jsse.jar...
	charsets.jar...
	localedata.jar...
  • 添加java环境变量
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目录下输入ant命令进行编译
[root@localhost benchmarksql-5.0-mysql-support-opt-2.1]# pwd
/opt/software/benchmarksql-5.0-mysql-support-opt-2.1
[root@localhost benchmarksql-5.0-mysql-support-opt-2.1]# ant
/usr/bin/build-classpath: Could not find jaxp_parser_impl Java extension for this JVM
/usr/bin/build-classpath: Could not find xml-commons-apis Java extension for this JVM
/usr/bin/build-classpath: error: Some specified jars were not found
Buildfile: /opt/software/benchmarksql-5.0-mysql-support-opt-2.1/build.xml

init:

compile:
    [javac] Compiling 12 source files to /opt/software/benchmarksql-5.0-mysql-support-opt-2.1/build

dist:
    [mkdir] Created dir: /opt/software/benchmarksql-5.0-mysql-support-opt-2.1/dist
      [jar] Building jar: /opt/software/benchmarksql-5.0-mysql-support-opt-2.1/dist/BenchmarkSQL-5.0.jar

BUILD SUCCESSFUL
Total time: 1 second
  • 下载对应版本的JBDC，并删除自带的JBDC驱动
[root@localhost postgres]# pwd
/opt/software/benchmarksql-5.0-mysql-support-opt-2.1/lib/postgres
[root@localhost postgres]# ls
openGauss-3.0.0-JDBC.tar.gz  postgresql-9.3-1102.jdbc41.jar
[root@localhost postgres]# tar -zxvf openGauss-3.0.0-JDBC.tar.gz 
postgresql.jar
opengauss-jdbc-3.0.0.jar
README_cn.md
README_en.md
[root@localhost postgres]# rm -rf postgresql-9.3-1102.jdbc41.jar

3. 测试步骤

3.1 创建数据库tpcc_db和用户tpcc

[omm@localhost ~]$ gsql -d postgres -p15400
gsql ((MogDB 3.0.0 build 62408a0f) compiled at 2022-06-30 14:21:11 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=# create database tpcc_db;
CREATE DATABASE
MogDB=# \q
[omm@localhost ~]$ gsql -d tpcc_db -p15400 -r
gsql ((MogDB 3.0.0 build 62408a0f) compiled at 2022-06-30 14:21:11 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

tpcc_db=# create user tpcc with PASSWORD 'Enmo@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
tpcc_db=# grant all on schema public to tpcc;
GRANT
tpcc_db=# alter user tpcc sysadmin;
ALTER ROLE
tpcc_db=# alter database tpcc_db owner to tpcc;
ALTER DATABASE

3.2 编辑BenchmarkSQL配置文件

[root@localhost Desktop]# cd /opt/software/benchmarksql-5.0-mysql-support-opt-2.1/run
[root@localhost run]# vim props.mogdb
[root@localhost run]# more props.mogdb 
db=postgres
driver=org.postgresql.Driver
conn=jdbc:postgresql://192.168.213.34:15400/tpcc_db?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off #修改连接字符串, 包含IP、端口号、数据库
user=tpcc #用户名
password=Enmo@123 #密码
warehouses=10  #仓位数
terminals=20 #并发数
runMins=5  #运行时间
runTxnsPerTerminal=0
loadWorkers=100
limitTxnsPerMin=0
terminalWarehouseFixed=false
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

3.3 装载数据

数据装载回显如下

Worker 000: Loading ITEM
Worker 001: Loading Warehouse      1
Worker 002: Loading Warehouse      2
Worker 003: Loading Warehouse      3
Worker 004: Loading Warehouse      4
Worker 005: Loading Warehouse      5
Worker 006: Loading Warehouse      6
Worker 007: Loading Warehouse      7
Worker 008: Loading Warehouse      8
Worker 009: Loading Warehouse      9
Worker 010: Loading Warehouse     10
Worker 000: Loading ITEM done
Worker 007: Loading Warehouse      7 done
Worker 008: Loading Warehouse      8 done
Worker 006: Loading Warehouse      6 done
Worker 004: Loading Warehouse      4 done
Worker 005: Loading Warehouse      5 done
Worker 001: Loading Warehouse      1 done
Worker 003: Loading Warehouse      3 done
Worker 009: Loading Warehouse      9 done
Worker 010: Loading Warehouse     10 done
Worker 002: Loading Warehouse      2 done
......

# ------------------------------------------------------------
# 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;

3.4 修改funcs.sh所在的实际路径

[root@localhost 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 /opt/software/benchmarksql-5.0-mysql-support-opt-2.1/run/funcs.sh $1  #将此处路径修改为文件所在的实际路径

setCP || exit 1

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

java -cp "$myCP" $myOPTS jTPCC

3.5 开始测试

[root@localhost run]# sh runBenchmark.sh props.mogdb| tee runLog_`date +%m%d-%H%M%S`.log
01:57:23,553 [main] INFO   jTPCC : Term-00, 
01:57:23,556 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
01:57:23,556 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
01:57:23,556 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
01:57:23,556 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
01:57:23,556 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
01:57:23,559 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
01:57:23,559 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
01:57:23,559 [main] INFO   jTPCC : Term-00, 
01:57:23,559 [main] INFO   jTPCC : Term-00, db=postgres
01:57:23,559 [main] INFO   jTPCC : Term-00, driver=org.postgresql.Driver
01:57:23,559 [main] INFO   jTPCC : Term-00, conn=jdbc:postgresql://192.168.213.34:15400/tpcc_db?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off
01:57:23,559 [main] INFO   jTPCC : Term-00, user=tpcc
01:57:23,560 [main] INFO   jTPCC : Term-00, 
01:57:23,560 [main] INFO   jTPCC : Term-00, warehouses=10
01:57:23,560 [main] INFO   jTPCC : Term-00, terminals=20
01:57:23,561 [main] INFO   jTPCC : Term-00, runMins=5
01:57:23,561 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
01:57:23,561 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=false
01:57:23,561 [main] INFO   jTPCC : Term-00, 
01:57:23,561 [main] INFO   jTPCC : Term-00, newOrderWeight=45
01:57:23,561 [main] INFO   jTPCC : Term-00, paymentWeight=43
01:57:23,561 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
01:57:23,562 [main] INFO   jTPCC : Term-00, deliveryWeight=4
01:57:23,562 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
01:57:23,562 [main] INFO   jTPCC : Term-00, 
01:57:23,562 [main] INFO   jTPCC : Term-00, resultDirectory=mogdb_result_%tY-%tm-%td_%tH%tM%tS
01:57:23,562 [main] INFO   jTPCC : Term-00, osCollectorScript=null
01:57:23,562 [main] INFO   jTPCC : Term-00, 
01:57:23,615 [main] INFO   jTPCC : Term-00, copied props.mogdb to mogdb_result_2022-07-20_015723/run.properties
01:57:23,615 [main] INFO   jTPCC : Term-00, created mogdb_result_2022-07-20_015723/data/runInfo.csv for runID 5
01:57:23,616 [main] INFO   jTPCC : Term-00, writing per transaction results to mogdb_result_2022-07-20_015723/data/result.csv
01:57:23,616 [main] INFO   jTPCC : Term-00,
01:57:23,854 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 23
01:57:23,855 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    98
01:57:23,855 [main] INFO   jTPCC : Term-00, 
Term-00, Running Average tpmTOTAL: 35706.85    Current tpmTOTAL: 1179576    Memory Usage: 44MB / 55MB          
02:02:24,687 [Thread-5] INFO   jTPCC : Term-00, 
02:02:24,688 [Thread-5] INFO   jTPCC : Term-00, 
02:02:24,688 [Thread-5] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 16096.61
02:02:24,689 [Thread-5] INFO   jTPCC : Term-00, Measured tpmTOTAL = 35697.44
02:02:24,689 [Thread-5] INFO   jTPCC : Term-00, Session Start     = 2022-07-20 01:57:24
02:02:24,689 [Thread-5] INFO   jTPCC : Term-00, Session End       = 2022-07-20 02:02:24
02:02:24,689 [Thread-5] INFO   jTPCC : Term-00, Transaction Count = 178557
02:02:24,689 [Thread-5] INFO   jTPCC : executeTime[Payment]=1549880
02:02:24,689 [Thread-5] INFO   jTPCC : executeTime[Order-Status]=218914
02:02:24,689 [Thread-5] INFO   jTPCC : executeTime[Delivery]=494766
02:02:24,690 [Thread-5] INFO   jTPCC : executeTime[Stock-Level]=279795
02:02:24,690 [Thread-5] INFO   jTPCC : executeTime[New-Order]=3455940

3.6 TPCC跑分结果

Measured tpmC (NewOrders) = 16096.61
Measured tpmTOTAL = 35697.44
Term-00, Session Start     = 2022-07-20 01:57:24
Term-00, Session End       = 2022-07-20 02:02:24
Term-00, Transaction Count = 178557

3.7 htop工具观察cpu情况

  • 安装部署
tar  zxvf htop-3.0.5.tar.gz
cd /opt/software/htop-3.0.5
./autogen.sh && ./configure && make && make install
  • 在跑分过程中查看cpu利用情况
    使用htop监控数据库服务端和tpcc客户端CPU利用情况，最佳性能测试情况下，各个业务CPU的占用率都非常高（> 90%）。
    image.png

3.8 清除数据

为了避免多次测试导致数据量太大，影响性能，可以把数据清空重新开始测试。

[root@localhost 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;

4. 查看测试报告

  • 安装R
yum install epel-release
yum install R
  • 检查R是否支持png
[root@mogdb run]# R

R version 3.6.0 (2019-04-26) -- "Planting of a Tree"
Copyright (C) 2019 The R Foundation for Statistical Computing
Platform: x86_64-redhat-linux-gnu (64-bit)

> capabilities()
       jpeg         png        tiff       tcltk         X11        aqua 
       TRUE        TRUE        TRUE        TRUE       FALSE       FALSE 
   http/ftp     sockets      libxml        fifo      cledit       iconv 
       TRUE        TRUE        TRUE        TRUE        TRUE        TRUE 
        NLS     profmem       cairo         ICU long.double     libcurl 
       TRUE        TRUE        TRUE        TRUE        TRUE        TRUE
  • 生成测试报告
[root@mogdb run]# bash generateReport.sh mogdb_result_2022-07-20_224600
Generating mogdb_result_2022-07-20_224600/tpm_nopm.png ... OK
Generating mogdb_result_2022-07-20_224600/latency.png ... OK
Generating mogdb_result_2022-07-20_224600/report.html ... OK
  • 查看测试报告
[root@mogdb run]# cd mogdb_result_2022-07-20_224600/
[root@mogdb mogdb_result_2022-07-20_224600]# ls
data  latency.png  report.html  run.properties  tpm_nopm.png
  • 结果
    tpm_nopm.png
    latency.png

5. 装载数据中遇到的问题

5.1 FATAL: Invalid username/password,login denied.

[root@localhost run]# ./runDatabaseBuild.sh props.mogdb
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
[192.168.213.34:33152/192.168.213.34:15400] FATAL: Invalid username/password,login denied.
Starting BenchmarkSQL LoadData

driver=org.postgresql.Driver
conn=jdbc:postgresql://192.168.213.34:15400/tpcc_db?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off
user=tpcc 
password=***********
warehouses=100  
Exception in thread "main" java.lang.NumberFormatException: For input string: "100  "
	at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
	at java.lang.Integer.parseInt(Integer.java:580)
	at java.lang.Integer.parseInt(Integer.java:615)
	at LoadData.iniGetInt(LoadData.java:388)
	at LoadData.main(LoadData.java:92)
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
# ------------------------------------------------------------
[192.168.213.34:33154/192.168.213.34:15400] FATAL: Invalid username/password,login denied.
# ------------------------------------------------------------
# Loading SQL file ./sql.common/foreignKeys.sql
# ------------------------------------------------------------
[192.168.213.34:33156/192.168.213.34:15400] FATAL: Invalid username/password,login denied.
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/extraHistID.sql
# ------------------------------------------------------------
[192.168.213.34:33158/192.168.213.34:15400] FATAL: Invalid username/password,login denied.
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/buildFinish.sql
# ------------------------------------------------------------
[192.168.213.34:33160/192.168.213.34:15400] FATAL: Invalid username/password,login denied.

需要仔细检查数据库、用户、用户密码是否是对应的，并在props.mogdb文件中将错误信息修正。

5.2 Exception in thread “main” java.lang.NumberFormatException: For input string: "100 "

[root@localhost run]# ./runDatabaseBuild.sh props.mogdb
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
......
Starting BenchmarkSQL LoadData

driver=org.postgresql.Driver
conn=jdbc:postgresql://192.168.213.34:15400/tpcc_db?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off
user=tpcc
password=***********
warehouses=100  
Exception in thread "main" java.lang.NumberFormatException: For input string: "100  "
	at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
	at java.lang.Integer.parseInt(Integer.java:580)
	at java.lang.Integer.parseInt(Integer.java:615)
	at LoadData.iniGetInt(LoadData.java:388)
	at LoadData.main(LoadData.java:92)

这里的错误是在props.mogdb文件中的warehouses配置项，warehouses=100 这个数值后面还多了一个空格，需要将其修改参数，保证每个配置项的数值都填写正确。****

mogdb
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

Mogdb 学习记录（共582篇）
Mogdb 学习指南

评论