oracle 压测工具 BenchmarkSQL

os: centos 7.6
db: oracle 19.3

oracle 安装的是 19.3 版本，使用的是 oracle MAA 架构。
BenchmarkSQL 有一些依赖，先处理好依赖

配置 epel 源

# wget -P /etc/yum.repos.d/ http://mirrors.aliyun.com/repo/epel-7.repo
# yum clean all  
# yum makecache  

安装 ant、R

# yum install ant R R-devel

安装 oracle JDK

# which java
/usr/bin/java

# /usr/bin/java -version
openjdk version "1.8.0_212"
OpenJDK Runtime Environment (build 1.8.0_212-b04)
OpenJDK 64-Bit Server VM (build 25.212-b04, mixed mode)

# yum list installed|grep -i java
java-1.7.0-openjdk.x86_64               1:1.7.0.221-2.6.18.0.el7_6     @updates 
java-1.7.0-openjdk-headless.x86_64      1:1.7.0.221-2.6.18.0.el7_6     @updates 
java-1.8.0-openjdk.x86_64               1:1.8.0.212.b04-0.el7_6        @updates 
java-1.8.0-openjdk-headless.x86_64      1:1.8.0.212.b04-0.el7_6        @updates 
javapackages-tools.noarch               3.4.1-11.el7                   @base    
python-javapackages.noarch              3.4.1-11.el7                   @base    
tzdata-java.noarch                      2019a-1.el7                    @updates 

# rpm -qa |grep -i -E 'java|jdk' |sort
copy-jdk-configs-3.3-10.el7_5.noarch
java-1.7.0-openjdk-1.7.0.221-2.6.18.0.el7_6.x86_64
java-1.7.0-openjdk-headless-1.7.0.221-2.6.18.0.el7_6.x86_64
java-1.8.0-openjdk-1.8.0.212.b04-0.el7_6.x86_64
java-1.8.0-openjdk-devel-1.8.0.212.b04-0.el7_6.x86_64
java-1.8.0-openjdk-headless-1.8.0.212.b04-0.el7_6.x86_64
javapackages-tools-3.4.1-11.el7.noarch
python-javapackages-3.4.1-11.el7.noarch
tzdata-java-2019a-1.el7.noarch


# rpm -e --nodeps tzdata-java-2019a-1.el7.noarch ;
rpm -e --nodeps java-1.7.0-openjdk-1.7.0.221-2.6.18.0.el7_6.x86_64 ;
rpm -e --nodeps java-1.7.0-openjdk-headless-1.7.0.221-2.6.18.0.el7_6.x86_64 ;
rpm -e --nodeps java-1.8.0-openjdk-1.8.0.212.b04-0.el7_6.x86_64 ;
rpm -e --nodeps java-1.8.0-openjdk-devel-1.8.0.212.b04-0.el7_6.x86_64 ;
rpm -e --nodeps java-1.8.0-openjdk-headless-1.8.0.212.b04-0.el7_6.x86_64 ;

https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
下载 JDK 1.8

# rz
# rpm -Uvh ./jdk-8u211-linux-x64.rpm

# which java
/usr/bin/java
# /usr/bin/java -version
java version "1.8.0_211"
Java(TM) SE Runtime Environment (build 1.8.0_211-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.211-b12, mixed mode)

# vi /etc/profile
export JAVA_HOME=/usr/java/jdk1.8.0_211-amd64
export CLASSPATH=.:${JAVA_HOME}/jre/lib/rt.jar:${JAVA_HOME}/lib/dt.jar:${JAVA_HOME}/lib/tools.jar:${CLASSPATH}
export PATH=${JAVA_HOME}/bin:$PATH

下载 benchmarksql-5.0

在 https://sourceforge.net/projects/benchmarksql/ 下载 benchmarksql-5.0.zip
上传到服务器后解压

# su - oracle
$ rz
$ unzip ./benchmarksql-5.0.zip
$ cd benchmarksql-5.0/
$ export CLASSPATH=.:/usr/share/java/ant-launcher.jar:${CLASSPATH} 
$ ant -buildfile ./build.xml
Buildfile: /home/oracle/benchmarksql-5.0/build.xml

init:
    [mkdir] Created dir: /home/oracle/benchmarksql-5.0/build

compile:
    [javac] Compiling 11 source files to /home/oracle/benchmarksql-5.0/build

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

BUILD SUCCESSFUL
Total time: 1 second

oracle 驱动

$ cat ./lib/oracle/README.txt 
Copy the ojdbc<version>.jar to use with Oracle here, or make
sure that the environment variable ORACLE_HOME is set properly
and the JDBC driver is found at $ORACLE_HOME/lib.

$ cp $ORACLE_HOME/jdbc/lib/ojdbc8.jar ./lib/oracle/

配置log4j

$ find ./ -name log4j.properties
./run/log4j.properties

$ vi ./run/log4j.properties  
  
log4j.rootLogger=INFO, CONSOLE  
  
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender  
log4j.appender.CONSOLE.Threshold=INFO  
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout  
log4j.appender.CONSOLE.layout.ConversionPattern= %d{HH:mm:ss,SSS} [%t] %-5p %x %C{1} : %m%n 

修改配置文件

查看 HOW-TO-RUN.txt 这两个文件，里面有详细的步骤。

$ cd ~/benchmarksql-5.0/run
$ vi props.ora

db=oracle
driver=oracle.jdbc.driver.OracleDriver
conn=jdbc:oracle:thin:@192.156.56.101:1521/srv_pdb1
user=scott
password=tiger

warehouses=3000
loadWorkers=256

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

-- drop user scott cascade;
create user scott identified by tiger 
profile default 
default tablespace USERS
temporary tablespace TEMP
account unlock;

alter user scott quota unlimited on USERS;

grant create session to scott;
grant connect, resource to scott;

一步完成 创建表、生成测试数据、导入数据。

$ cd ~/benchmarksql-5.0/run
$ ./runDatabaseBuild.sh ./props.ora

tpc-c 压测以及性能指标

$ cd ~/benchmarksql-5.0/run
$ ./runBenchmark.sh ./props.ora

tpc-c 结果绘图,生成报告

$ ./my_result_2019-06-10_105020
├── data
│   ├── result.csv
│   └── runInfo.csv
└── run.properties

1 directory, 3 files

$ cd ~/benchmarksql-5.0/run
$ ./generateGraphs.sh ./my_result_2019-06-10_105020/
$ ./generateReport.sh ./my_result_2019-06-10_105020/

tpc-c 重新压测

$ cd ~/benchmarksql-5.0/run
$ ./runDatabaseDestroy.sh ./props.ora
$ ./runDatabaseBuild.sh ./props.ora
$ ./runBenchmark.sh ./props.ora