921
使用BenchmarkSQL 測試Oracle 12c TPC-C 性能
使用BenchmarkSQL測試一下Oracle 12c的TPC-C性能，同時對比一下PostgreSQL 9.5的性能。
測試機用的是FusionIO卡，24核的CPU，192G內存。
12c安裝請參考
https://blog.163.com/digoal@126/blog/static/1638770402015112281556942/
https://blog.163.com/digoal@126/blog/static/163877040201511232138735/
下載benchmarksql
https://sourceforge.net/projects/benchmarksql/
下載安裝 JDK7
https://www.oracle.com/technetwork/cn/java/javase/downloads/jdk7-downloads-1880260.html
wget https://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.rpm
rpm -ivh jdk-7u79-linux-x64.rpm
檢查包安裝位置(使用rpm安裝時也可以直接指定位置)
rpm -ql jdk
...
/usr/java/jdk1.7.0_79/bin/java
...
配置JAVA環境變量，將ORACLE jdbc驅動拷貝到benchmark/run目錄
$ cp ~/oracle/product/12.1.0/db_1/jdbc/lib/ojdbc6.jar benchmarksql/run/
$ cp ~/oracle/product/12.1.0/db_1/jdbc/lib/ojdbc7.jar benchmarksql/run/
$ export JAVA_HOME=/usr/java/jdk1.7.0_79
$ export PATH=$JAVA_HOME/bin:$PATH
$ export CLASSPATH=./ojdbc6.jar:./ojdbc7.jar:$CLASSPATH
修改runSQL.sh，加上$CLASSPATH，否則會報oracle驅動CLASS不存在的錯誤。
$ vi runSQL.sh
myCP="../lib/postgresql-9.3-1101.jdbc41.jar"
myCP="$myCP:../dist/BenchmarkSQL-4.1.jar"
myOPTS="-Dprop=$1"
myOPTS="$myOPTS -DcommandFile=$2"
java -cp .:$myCP:$CLASSPATH $myOPTS ExecJDBC
修改props.ora
$ vi props.ora
driver=oracle.jdbc.driver.OracleDriver
conn=jdbc:oracle:thin:@localhost:1521:orcl12c
user=benchmarksql
password=benchmarksql
創建數據庫用戶
$ sqlplus "/ as sysdba"
SQL> create user benchmarksql identified by "benchmarksql";
SQL> grant dba,connect to benchmarksql;
SQL> alter user benchmarksql default tablespace users;
設置ORACLE 批量提交參數
SQL> alter system set commit_write='batch,nowait';
擴展表空間，防止空間不夠。(1000個warehouse夠用了）。
SQL> alter tablespace users add datafile '/disk1/digoal/oradata/users01.dbf' size 31G;
SQL> alter tablespace users add datafile '/disk1/digoal/oradata/users02.dbf' size 31G;
SQL> alter tablespace users add datafile '/disk1/digoal/oradata/users03.dbf' size 31G;
SQL> alter tablespace users add datafile '/disk1/digoal/oradata/users04.dbf' size 31G;
SQL> alter tablespace users add datafile '/disk1/digoal/oradata/users05.dbf' size 31G;
SQL> alter tablespace users add datafile '/disk1/digoal/oradata/users06.dbf' size 31G;
SQL> alter tablespace users add datafile '/disk1/digoal/oradata/users07.dbf' size 31G;
SQL> alter tablespace users add datafile '/disk1/digoal/oradata/users08.dbf' size 31G;
SQL> alter tablespace users add datafile '/disk1/digoal/oradata/users09.dbf' size 31G;
修改建表SQL的ORACLE兼容性：
$ vi sqlTableCreates
create sequence benchmarksql.hist_id_seq;
create table benchmarksql.history (
hist_id integer default benchmarksql.hist_id_seq.nextval primary key,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
);
創建表
$ cd benchmarksql/run
$ ./runSQL.sh props.pg sqlTableCreates
導入數據
$ nohup ./runLoader.sh props.ora numWarehouses 1000 >./load.log 2>&1 &
單位為10萬。1000將寫入一億記錄。
創建索引
$ ./runSQL.sh props.ora sqlIndexCreates
benchmark 測試
修改runBenchmark.sh
$ vi runBenchmark.sh
java -cp .:$CLASSPATH:../lib/postgresql-9.3-1101.jdbc41.jar:../lib/log4j-1.2.17.jar:../lib/apache-log4j-extras-1.1.jar:../dist/BenchmarkSQL-4.1.jar -Dprop=$1 jTPCC
修改props.ora，設置比例。
$ vi props.ora
driver=oracle.jdbc.driver.OracleDriver
conn=jdbc:oracle:thin:@localhost:1521:orcl12c
user=benchmarksql
password=benchmarksql
warehouses=1000
terminals=20
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=300000
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=0
//Number of total transactions per minute
limitTxnsPerMin=0
//The following five values must add up to 100
newOrderWeight=40
paymentWeight=36
orderStatusWeight=8
deliveryWeight=8
stockLevelWeight=8
$ ./runBenchmark.sh props.ora
測試結果後續給出。
[其他]
清數據
./runSQL.sh props.pg sqlTableTruncates
刪表
./runSQL.sh props.pg sqlTableDrops
最後更新：2017-04-01 13:44:32