使用BenchmarkSQL压测磐维2.0
一、实验环境
| 节点类别 | 主机名 | IP | 端口 | 工具 |
|---|---|---|---|---|
| 主节点 | pw_1 | 192.168.3.26 | 17700 | Benchmark |
| 备节点1 | pw_2 | 192.168.3.28 | 17700 | - |
| 备节点2 | pw_3 | 192.168.3.29 | 17700 | - |
二、安装部署
1、下载软件
benchmarksql-5.0 下载地址： https://udomain.dl.sourceforge.net/project/benchmarksql/benchmarksql-5.0.zip
htop 下载地址： https://github.com/htop-dev/htop/releases
R 语言下载地址： https://mirror.bjtu.edu.cn/cran/src/base/R-3/R-3.6.3.tar.gz
2、部署软件
# 安装所需系统包
[root@pw_1 media]# mount /dev/cdrom /media/cdrom
mount: /dev/sr0 is write-protected, mounting read-only
[root@pw_1 media]# yum install -y gcc glibc-headers gcc-c++ gcc-gfortran readline-devel libXt-devel pcre-devel libcurl libcurl-devel ncurses ncurses-devel autoconf automake zlib zlib-devel bzip2 bzip2-devel xz-devel java-1.8.0-openjdk ant
Loaded plugins: fastestmirror, langpacks
Determining fastest mirrors
Dependencies Resolved
=========================================================================================================================================================================================================================================
Package Arch Version Repository Size
=========================================================================================================================================================================================================================================
Installing:
ant noarch 1.9.4-2.el7 base 2.0 M
libXt-devel x86_64 1.1.5-3.el7 base 446 k
libcurl-devel x86_64 7.29.0-59.el7 base 303 k
Updating:
libcurl x86_64 7.29.0-59.el7 base 223 k
Installing for dependencies:
libICE-devel x86_64 1.0.9-9.el7 base 50 k
libSM-devel x86_64 1.2.2-2.el7 base 13 k
xalan-j2 noarch 2.7.1-23.el7 base 1.9 M
xerces-j2 noarch 2.11.0-17.el7_0 base 1.1 M
xml-commons-apis noarch 1.4.01-16.el7 base 227 k
xml-commons-resolver noarch 1.2-15.el7 base 108 k
Updating for dependencies:
curl x86_64 7.29.0-59.el7 base 271 k
libssh2 x86_64 1.8.0-4.el7 base 88 k
Transaction Summary
=========================================================================================================================================================================================================================================
Install 3 Packages (+6 Dependent packages)
Upgrade 1 Package (+2 Dependent packages)
Total download size: 6.7 M
Downloading packages:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.6 MB/s | 6.7 MB 00:00:04
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installed:
ant.noarch 0:1.9.4-2.el7 libXt-devel.x86_64 0:1.1.5-3.el7 libcurl-devel.x86_64 0:7.29.0-59.el7
Dependency Installed:
libICE-devel.x86_64 0:1.0.9-9.el7 libSM-devel.x86_64 0:1.2.2-2.el7 xalan-j2.noarch 0:2.7.1-23.el7 xerces-j2.noarch 0:2.11.0-17.el7_0 xml-commons-apis.noarch 0:1.4.01-16.el7 xml-commons-resolver.noarch 0:1.2-15.el7
Updated:
libcurl.x86_64 0:7.29.0-59.el7
Dependency Updated:
curl.x86_64 0:7.29.0-59.el7 libssh2.x86_64 0:1.8.0-4.el7
Complete!
[root@pw_1 media]# yum install -y pango-devel pango libpng-devel cairo cairo-devel
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package cairo.x86_64 0:1.15.12-3.el7 will be updated
---> Package libsmartcols.x86_64 0:2.23.2-59.el7 will be updated
---> Package libsmartcols.x86_64 0:2.23.2-65.el7 will be an update
--> Finished Dependency Resolution
Dependencies Resolved
Updated:
cairo.x86_64 0:1.15.12-4.el7 pango.x86_64 0:1.42.4-4.el7_7
Dependency Updated:
cairo-gobject.x86_64 0:1.15.12-4.el7 expat.x86_64 0:2.1.0-12.el7 freetype.x86_64 0:2.8-14.el7 fribidi.x86_64 0:1.0.2-1.el7_7.1 glib2.x86_64 0:2.56.1-7.el7 libblkid.x86_64 0:2.23.2-65.el7
libdrm.x86_64 0:2.4.97-2.el7 libmount.x86_64 0:2.23.2-65.el7 libpng.x86_64 2:1.5.13-8.el7 libsmartcols.x86_64 0:2.23.2-65.el7 libuuid.x86_64 0:2.23.2-65.el7 mesa-libEGL.x86_64 0:18.3.4-10.el7
mesa-libGL.x86_64 0:18.3.4-10.el7 mesa-libgbm.x86_64 0:18.3.4-10.el7 mesa-libglapi.x86_64 0:18.3.4-10.el7 util-linux.x86_64 0:2.23.2-65.el7
Complete!
[root@pw_1 media]# cd /enmo/soft/
[root@pw_1 soft]# ll
total 802960
-rw-r--r-- 1 root root 1524810 Jan 16 09:41 compat-openssl11-1.1.1k-4.el9_0.x86_64.rpm
-rw------- 1 omm omm 1270 Jan 16 08:50 config.yaml
-rw-r--r-- 1 omm omm 761562362 Dec 7 20:47 dbops-installer-1.0.0-centos_7-x86_64_intel-cmcc-202311301603.tar.gz
-rw-r--r-- 1 root root 297931 May 25 2021 htop-3.0.5.tar.gz
drwxrwxr-x 19 root root 4096 Jan 16 09:58 openssl-1.1.1t
-rw-r--r-- 1 root root 9881866 Jan 16 09:54 openssl-1.1.1t.tar.gz
drwxr-xr-x 3 omm omm 4096 Oct 16 2021 python3-rpm
-rw-r--r-- 1 omm omm 15634045 Oct 16 2021 python3-rpm.tar.gz
-rw-r--r-- 1 root root 33308185 May 25 2021 R-3.6.3.tar.gz
[root@pw_1 soft]# tar -zxf R-3.6.3.tar.gz
[root@pw_1 soft]# cd R-3.6.3
[root@pw_1 R-3.6.3]# ./configure && make && make install
# 安装htop监控工具
[root@pw_1 R-3.6.3]# cd ..
[root@pw_1 soft]# tar -zxvf htop-3.0.5.tar.gz
[root@pw_1 soft]# cd htop-3.0.5
[root@pw_1 htop-3.0.5]# ./autogen.sh && ./configure && make && make install
[root@pw_1 htop-3.0.5]#
[root@pw_1 htop-3.0.5]# cd ..
[root@pw_1 soft]# ll
total 806828
-rw-r--r-- 1 root root 2263539 May 25 2021 benchmarksql-5.0.zip
-rw-r--r-- 1 root root 1524810 Jan 16 09:41 compat-openssl11-1.1.1k-4.el9_0.x86_64.rpm
-rw------- 1 omm omm 1270 Jan 16 08:50 config.yaml
-rw-r--r-- 1 omm omm 761562362 Dec 7 20:47 dbops-installer-1.0.0-centos_7-x86_64_intel-cmcc-202311301603.tar.gz
drwxrwxr-x 16 root root 8192 Feb 18 16:00 htop-3.0.5
-rw-r--r-- 1 root root 297931 May 25 2021 htop-3.0.5.tar.gz
drwxrwxr-x 19 root root 4096 Jan 16 09:58 openssl-1.1.1t
-rw-r--r-- 1 root root 9881866 Jan 16 09:54 openssl-1.1.1t.tar.gz
-rw-r--r-- 1 root root 1678317 Nov 3 14:56 PanWeiDB-2.0.0-Jdbc-2023102718.tar.gz
drwxr-xr-x 3 omm omm 4096 Oct 16 2021 python3-rpm
-rw-r--r-- 1 omm omm 15634045 Oct 16 2021 python3-rpm.tar.gz
drwxr-xr-x 15 501 games 4096 Feb 18 15:58 R-3.6.3
-rw-r--r-- 1 root root 33308185 May 25 2021 R-3.6.3.tar.gz
# 检查安装情况(java/ant/htop)
[root@pw_1 ~]# ant -version
Apache Ant(TM) version 1.9.2 compiled on June 10 2014
[root@pw_1 ~]# java -version
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)
[root@pw_1 ~]# R --version
R version 3.6.3 (2020-02-29) -- "Holding the Windsock"
Copyright (C) 2020 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)
# 解压软件及JDBC驱动[root@pw_1 soft]# unzip benchmarksql-5.0.zip
[root@pw_1 soft]# ll
total 806828
drwxr-xr-x 6 root root 124 May 26 2016 benchmarksql-5.0
-rw-r--r-- 1 root root 2263539 May 25 2021 benchmarksql-5.0.zip
-rw-r--r-- 1 root root 1524810 Jan 16 09:41 compat-openssl11-1.1.1k-4.el9_0.x86_64.rpm
-rw------- 1 omm omm 1270 Jan 16 08:50 config.yaml
-rw-r--r-- 1 omm omm 761562362 Dec 7 20:47 dbops-installer-1.0.0-centos_7-x86_64_intel-cmcc-202311301603.tar.gz
drwxrwxr-x 16 root root 8192 Feb 18 16:00 htop-3.0.5
-rw-r--r-- 1 root root 297931 May 25 2021 htop-3.0.5.tar.gz
drwxrwxr-x 19 root root 4096 Jan 16 09:58 openssl-1.1.1t
-rw-r--r-- 1 root root 9881866 Jan 16 09:54 openssl-1.1.1t.tar.gz
-rw-r--r-- 1 root root 1678317 Nov 3 14:56 PanWeiDB-2.0.0-Jdbc-2023102718.tar.gz
drwxr-xr-x 3 omm omm 4096 Oct 16 2021 python3-rpm
-rw-r--r-- 1 omm omm 15634045 Oct 16 2021 python3-rpm.tar.gz
drwxr-xr-x 15 501 games 4096 Feb 18 15:58 R-3.6.3
-rw-r--r-- 1 root root 33308185 May 25 2021 R-3.6.3.tar.gz
[root@pw_1 soft]# cd benchmarksql-5.0
[root@pw_1 benchmarksql-5.0]# ll
total 24
-rwxr-xr-x 1 root root 1130 May 26 2016 build.xml
drwxr-xr-x 3 root root 17 May 26 2016 doc
-rwxr-xr-x 1 root root 6376 May 26 2016 HOW-TO-RUN.txt
drwxr-xr-x 5 root root 129 May 26 2016 lib
-rwxr-xr-x 1 root root 5318 May 26 2016 README.md
drwxr-xr-x 7 root root 4096 May 26 2016 run
drwxr-xr-x 6 root root 67 May 26 2016 src
[root@pw_1 benchmarksql-5.0]# cd lib/
[root@pw_1 lib]# ll
total 820
-rwxr-xr-x 1 root root 346729 May 26 2016 apache-log4j-extras-1.1.jar
drwxr-xr-x 2 root root 60 May 26 2016 firebird
-rwxr-xr-x 1 root root 489883 May 26 2016 log4j-1.2.17.jar
drwxr-xr-x 2 root root 42 May 26 2016 oracle
drwxr-xr-x 2 root root 44 May 26 2016 postgres
[root@pw_1 lib]# cd postgres/
[root@pw_1 postgres]# ll
total 580
-rw-r--r-- 1 root root 592322 May 26 2016 postgresql-9.3-1102.jdbc41.jar
[root@pw_1 postgres]# cp /enmo/soft/PanWeiDB-2.0.0-Jdbc-2023102718.tar.gz ./
[root@pw_1 postgres]# ll
total 2220
-rw-r--r-- 1 root root 1678317 Feb 18 16:04 PanWeiDB-2.0.0-Jdbc-2023102718.tar.gz
-rw-r--r-- 1 root root 592322 May 26 2016 postgresql-9.3-1102.jdbc41.jar
[root@pw_1 postgres]# tar -zxvf PanWeiDB-2.0.0-Jdbc-2023102718.tar.gz
panweidb-jdbc-2.0.0.jar
postgresql.jar
[root@pw_1 postgres]# ll
total 3964
-rw-r--r-- 1 root root 1678317 Feb 18 16:04 PanWeiDB-2.0.0-Jdbc-2023102718.tar.gz
-rw-r--r-- 1 91 91 890255 Oct 27 18:39 panweidb-jdbc-2.0.0.jar
-rw-r--r-- 1 root root 592322 May 26 2016 postgresql-9.3-1102.jdbc41.jar
-rw-r--r-- 1 91 91 892335 Oct 27 18:38 postgresql.jar
[root@pw_1 postgres]# cd ../..
# 使用ant编译
[root@pw_1 benchmarksql-5.0]# ant
Buildfile: /enmo/soft/benchmarksql-5.0/build.xml
init:
[mkdir] Created dir: /enmo/soft/benchmarksql-5.0/build
compile:
[javac] Compiling 11 source files to /enmo/soft/benchmarksql-5.0/build
dist:
[mkdir] Created dir: /enmo/soft/benchmarksql-5.0/dist
[jar] Building jar: /enmo/soft/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar
BUILD SUCCESSFUL
Total time: 3 seconds
三、配置props文件
# 数据库类型，postgres代表我们对PG数据库进行测试，它支持的数据库包括PostgreSQL/EnterpriseDB、DB2、Oracle、SQL Server和MySQL。
db=postgres
# 驱动
driver=org.postgresql.Driver
# 修改连接字符串, 包含IP、端口号、数据库 https://jdbc.postgresql.org/documentation/head/connect.html
conn=jdbc:postgresql://192.168.0.11:26000/benchmarksql?prepareThreshold=1&batchMode=on&fetchsize=10
# 设置数据库登录用户和密码。
user=bench
password=gauss@123
# 仓库数，每个Warehouse的数据量大小约为76823.04KB
warehouses=20
# 用于在数据库中初始化数据的加载进程数量，默认为4 (建议填写CPU核数)
loadWorkers=4
# 终端数，即：并发客户端数量, 跟服务端最大work数对应，通常设置为CPU线程总数的2～6倍
terminals=6
# 每个终端(terminal)运行的固定事务数量,当该参数为非0时,runMins参数必须等于零
runTxnsPerTerminal=0
# 要运行指定的分钟,当该参数为非0时,runTxnsPerTerminal必须等于零
runMins=5
# 每分钟总事务数
limitTxnsPerMin=0
# 在4.x兼容模式下运行时，设置为True。设置为false以均匀使用整个配置的数据库。
terminalWarehouseFixed=false
# 以下五个值相加之和为100 (45、43、4、4和4的默认百分比与TPC-C规范匹配)
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
# 测试数据生成目录，默认无需修改，默认生成在run目录下面，名字形如my_result_xxxx的文件夹
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
# 操作系统性能收集脚本，默认无需修改，需要操作系统具备有python2环境
osCollectorScript=./misc/os_collector_linux.py
# 操作系统收集操作间隔，默认为1秒
osCollectorInterval=1
# 操作系统收集所对应的主机，如果对本机数据库进行测试，该参数保持注销即可，如果要对远程服务器进行测试，请填写用户名和主机名
//osCollectorSSHAddr=omm@192.168.0.11
# 操作系统中被收集服务器的网卡名称和磁盘名称
osCollectorDevices=net_eth0 blk_vda
四、配置示例
[root@pw_1 benchmarksql-5.0]# cd run/
[root@pw_1 run]# ll
total 52
-rwxr-xr-x 1 root root 1100 May 26 2016 funcs.sh
-rwxr-xr-x 1 root root 2123 May 26 2016 generateGraphs.sh
-rwxr-xr-x 1 root root 7256 May 26 2016 generateReport.sh
-rwxr-xr-x 1 root root 962 May 26 2016 log4j.properties
drwxr-xr-x 2 root root 218 May 26 2016 misc
-rw-r--r-- 1 root root 1063 May 26 2016 props.fb
-rw-r--r-- 1 root root 947 May 26 2016 props.ora
-rw-r--r-- 1 root root 1021 May 26 2016 props.pg
-rwxr-xr-x 1 root root 385 May 26 2016 runBenchmark.sh
-rwxr-xr-x 1 root root 528 May 26 2016 runDatabaseBuild.sh
-rwxr-xr-x 1 root root 330 May 26 2016 runDatabaseDestroy.sh
-rwxr-xr-x 1 root root 200 May 26 2016 runLoader.sh
-rwxr-xr-x 1 root root 1207 May 26 2016 runSQL.sh
drwxr-xr-x 2 root root 170 May 26 2016 sql.common
drwxr-xr-x 2 root root 29 May 26 2016 sql.firebird
drwxr-xr-x 2 root root 29 May 26 2016 sql.oracle
drwxr-xr-x 2 root root 75 May 26 2016 sql.postgres
[root@pw_1 run]# vi props.panwei
password=Bcv_1308
db=postgres
driver=org.panweidb.Driver
conn=jdbc:panweidb://192.168.3.26:17700/testdb
user=dbmt
password=Bcv_1308
warehouses=10
terminals=20
runMins=5
runTxnsPerTerminal=0
loadWorkers=100
limitTxnsPerMin=0
terminalWarehouseFixed=false
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
~
"props.panwei" [New] 17L, 326C written
五、运行TPCC测试
cd run //进入run目录
./runDatabaseBuild.sh props.panwei //进行测试库创建，数据导入
./runBenchmark.sh props.panwei //执行配置好的测试
./runDatabaseDestroy.sh props.panwei //清理数据
六、运行结果
[root@pw_1 run]# ./runBenchmark.sh props.panwei
17:08:57,937 [main] INFO jTPCC : Term-00,
17:08:57,938 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
17:08:57,938 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
17:08:57,938 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
17:08:57,938 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
17:08:57,938 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
17:08:57,943 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
17:08:57,943 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
17:08:57,943 [main] INFO jTPCC : Term-00,
17:08:57,943 [main] INFO jTPCC : Term-00, db=postgres
17:08:57,943 [main] INFO jTPCC : Term-00, driver=org.panweidb.Driver
17:08:57,943 [main] INFO jTPCC : Term-00, conn=jdbc:panweidb://192.168.3.26:17700/testdb
17:08:57,943 [main] INFO jTPCC : Term-00, user=dbmt
17:08:57,943 [main] INFO jTPCC : Term-00,
17:08:57,943 [main] INFO jTPCC : Term-00, warehouses=10
17:08:57,943 [main] INFO jTPCC : Term-00, terminals=20
17:08:57,944 [main] INFO jTPCC : Term-00, runMins=5
17:08:57,944 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
17:08:57,944 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=false
17:08:57,944 [main] INFO jTPCC : Term-00,
17:08:57,944 [main] INFO jTPCC : Term-00, newOrderWeight=45
17:08:57,944 [main] INFO jTPCC : Term-00, paymentWeight=43
17:08:57,944 [main] INFO jTPCC : Term-00, orderStatusWeight=4
17:08:57,944 [main] INFO jTPCC : Term-00, deliveryWeight=4
17:08:57,944 [main] INFO jTPCC : Term-00, stockLevelWeight=4
17:08:57,944 [main] INFO jTPCC : Term-00,
17:08:57,944 [main] INFO jTPCC : Term-00, resultDirectory=null
17:08:57,944 [main] INFO jTPCC : Term-00, osCollectorScript=null
17:08:57,944 [main] INFO jTPCC : Term-00,
17:08:58,234 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 105
17:08:58,234 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 219
17:08:58,234 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 6811.43 Current tpmTOTAL: 224016 Memory Usage: 19MB / 119MB
17:13:59,751 [Thread-1] INFO jTPCC : Term-00,
17:13:59,752 [Thread-1] INFO jTPCC : Term-00,
17:13:59,752 [Thread-1] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 3087.34
17:13:59,752 [Thread-1] INFO jTPCC : Term-00, Measured tpmTOTAL = 6806.66
17:13:59,752 [Thread-1] INFO jTPCC : Term-00, Session Start = 2024-02-18 17:08:59
17:13:59,752 [Thread-1] INFO jTPCC : Term-00, Session End = 2024-02-18 17:13:59
17:13:59,752 [Thread-1] INFO jTPCC : Term-00, Transaction Count = 34077
[root@pw_1 run]#
