benchmark for oracle压力测试

安装benchmark

配置本地YUM源

mount -t auto /dev/cdrom  /mnt
 
rm -rf /etc/yum.repos.d/
mkdir -p /etc/yum.repos.d/
 
 
 
cat >> /etc/yum.repos.d/Radhat-Media.repo<<EOF
# Radhat-Media.repo
#
#  This repo can be used with mounted DVD media, verify the mount point for
#  Radhat-7.  You can use this repo and yum to install items directly off the
#  DVD ISO that we release.
#
# To use this repo, put in your DVD and use it with the other repos too:
#  yum --enablerepo=R7-media [command]
#  
# or for ONLY the media repo, do this:
#
#  yum --disablerepo=\* --enablerepo=R7-media [command]
 
[R7-media]
name=Radhat-$releasever - Media
baseurl=file:///mnt/
gpgcheck=0
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Radhat-7
EOF
 
 
yum clean all
yum makecache

 

安装 ant、R

yum install ant R R-devel

安装 oracle JDK

 rpm -ivh jdk-8u261-linux-x64.rpm 

cat>>/etc/profile<<EOF
export JAVA_HOME=/usr/java/jdk1.8.0_261-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
EOF

解压 benchmarksql-5.0

[root@oracle11g home]# unzip benchmarksql-5.0.zip 
[root@oracle11g home]# 

加载oracle 驱动

cp /u01/app/oracle/product/11.2/db/jdbc/lib/ojdbc6.jar /benchmarksql/lib/oracle/.

 

vi /benchmarksql/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 

修改props.ora

vi /benchmarksql/run/props.ora

db=oracle
driver=oracle.jdbc.driver.OracleDriver
conn=jdbc:oracle:thin:@192.168.60.124:1521:orcl1
user=test
password=test

warehouses=100
loadWorkers=100

terminals=400
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=10
//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
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
~

conn=jdbc:postgresql://localhost:5432/benchmarksql

conn参数用于指定连接串，可以修改ip，端口与dbname

loadworkers

该参数用于指定测试数据加载时的并行度
注意与process的糁要＞以上设置

warehouses

warehouses 参数用于指定仓库个数，用于控制测试数据量，默认为1，导入9张表的数据大小大概70多M，当numWarehouse为10时，数据大小可以近似当作1GB数据
每个仓库负责十个区域的供货，每个区域 3000 个客户服务，每个仓库维护 100000 种商品的库存纪录，每个仓库至多有10个终端

terminals

terminals参数用于指定模拟终端数
注意与process的糁要＞以上设置

runTxnsPerTerminal

runTxnsPerTerminal参数用于指定每个终端提交事务数，这个参数与runMins只能选择设置其中一个，另一个必须设置为0
runTxnsPerTerminal=100
runMins=0

runMins

runMins参数用于指定运行时间，单位是分钟，这个参数与runTxnsPerTerminal只能选择设置其中一个，另一个必须设置为0
runTxnsPerTerminal=0
runMins=12

limitTxnsPerMin=100000

limitTxnsPerMin参数用于指定每分钟提交事务数限制，设置太小，压力会不够，如果测试结果不能接近该值就代表压力足够

terminalWarehouseFixed=true

terminalWarehouseFixed参数用于指定终端和仓库的绑定模式，设置为true时可以运行4.x兼容模式，意思为每个终端都有一个固定的仓库。设置为false时可以均匀的使用数据库整体配置。TPCC规定每个终端都必须有一个绑定的仓库，所以一般使用默认值true

ant初始化

[root@oracle11g benchmarksql]# ant
Buildfile: build.xml

init:
    [mkdir] Created dir: /benchmarksql/build

compile:
    [javac] Compiling 11 source files to /benchmarksql/build
    [javac] This version of java does not support the classic compiler; upgrading to modern

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

BUILD SUCCESSFUL
Total time: 4 seconds

压力测试

初始化数据

cd /benchmarksql/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


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