autonomous databaseにjdbcrunnerかけてみる
Oracle Cloud always free Advent Calendar 2019の16日目です。
今日はJavaで実装された汎用データベース負荷テストツールJdbcRunnerを使って、
always free Computeインスタンス(Oracle Linux 7)から autonomous databaseに負荷をかけてみようと思います。
今回、instant client(jdbc)のみでやりたいので、昨日、sqlplusを入れた環境とは別の環境で行います。
また、JDBC接続には、JKS接続とWallet接続がありますが、今回はJKS接続で行います。
#walletの入手
16日目で入手したwallet関連のファイルをすべてをalways free computeインスタンスへコピーします。(格納パスは ~/wallet_jdbc)
#ant/openjdkのインストール（antの依存関係でopenjdk1.8がインストールされる）
sudo yum -y install git ant
#jdbc driverのインストール
sudo yum -y install oracle-instantclient18.3-jdbc
#ojdbc.propertiesの編集
#walletの配置場所は/home/opc/wallet_jdbc
#javax.net.ssl.keyStorePasswordはwalletダウンロード時に指定したパスワード
cat << EOF > ~/wallet_jdbc/ojdbc.properties
#oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/opc/wallet_jdbc)))
oracle.net.ssl_server_dn_match=true
javax.net.ssl.trustStore=/home/opc/wallet_jdbc/truststore.jks
javax.net.ssl.trustStorePassword=Passw0rd
javax.net.ssl.keyStore=/home/opc/wallet_jdbc/keystore.jks
javax.net.ssl.keyStorePassword=Passw0rd
EOF
#jdbcrunnerの取得
git clone https://github.com/sh2/jdbcrunner.git
#コンパイル
cd jdbcrunner
ant
#user作成用のスクリプト配置
cat << EOF > ~/jdbcrunner/scripts/ora_create_user.js
var isLoad = false;
var nTxTypes = 1;
var nAgents = 1;
var connPoolSize = nAgents;
var stmtCacheSize = 10;
var isAutoCommit = true;
var sleepTime = 0;
var throttle = 0;
var isDebug = false;
var isTrace = false;
var logDir = ".";function init() {
if (getId() == 0) {
//execute("DROP USER tpcc cascade")
execute("CREATE USER tpcc IDENTIFIED BY TccP12#_TccP12#_ DEFAULT TABLESPACE DATA QUOTA UNLIMITED ON DATA TEMPORARY TABLESPACE TEMP");
execute("GRANT connect, resource TO tpcc")
}
}function run() {
}function fin() {
}
EOF
#user作成
java -Djava.security.egd=file:/dev/./urandom \
-cp ~/jdbcrunner/jdbcrunner-1.3-nojdbc.jar:/usr/lib/oracle/18.3/client64/lib/ojdbc8.jar \
JR ~/jdbcrunner/scripts/ora_create_user.js \
-jdbcDriver oracle.jdbc.driver.OracleDriver \
-jdbcUrl jdbc:oracle:thin:@datawarehouse_high?TNS_ADMIN=/home/opc/wallet_jdbc \
-warmupTime 1 \
-measurementTime 1 \
-jdbcUser ADMIN \
-jdbcPass <ADMINのパスワード>
09:12:49 [INFO ] > JdbcRunner 1.3
09:12:49 [INFO ] [Config]
Program start time : 20191118-091248
Script filename : /home/opc/jdbcrunner/scripts/ora_create_user.js
JDBC driver : oracle.jdbc.driver.OracleDriver
JDBC URL : jdbc:oracle:thin:@datawarehouse_high?TNS_ADMIN=/home/opc/wallet_jdbc
JDBC user : ADMIN
Warmup time : 1 sec
Measurement time : 1 sec
Number of tx types : 1
Number of agents : 1
Connection pool size : 1
Statement cache size : 10
Auto commit : true
Sleep time : 0 msec
Throttle : - tps
Debug mode : false
Trace mode : false
Log directory : .
Parameter 0 : 0
Parameter 1 : 0
Parameter 2 : 0
Parameter 3 : 0
Parameter 4 : 0
Parameter 5 : 0
Parameter 6 : 0
Parameter 7 : 0
Parameter 8 : 0
Parameter 9 : 0
09:12:55 [INFO ] [Warmup] 0 sec, 165892 tps, (165892 tx)
09:12:56 [INFO ] [Progress] 1 sec, 408335 tps, 408335 tx
09:12:56 [INFO ] [Total tx count] 396940 tx
09:12:56 [INFO ] [Throughput] 396940.0 tps
09:12:56 [INFO ] [Response time (minimum)] 0 msec
09:12:56 [INFO ] [Response time (50%tile)] 0 msec
09:12:56 [INFO ] [Response time (90%tile)] 0 msec
09:12:56 [INFO ] [Response time (95%tile)] 0 msec
09:12:56 [INFO ] [Response time (99%tile)] 0 msec
09:12:56 [INFO ] [Response time (maximum)] 71 msec
09:12:56 [INFO ] < JdbcRunner SUCCESS
#TPCC用のデータロード ユーザ/パスワードは先ほど作成したユーザー（tpcc)
java -Djava.security.egd=file:/dev/./urandom \
-cp ~/jdbcrunner/jdbcrunner-1.3-nojdbc.jar:/usr/lib/oracle/18.3/client64/lib/ojdbc8.jar \
JR ~/jdbcrunner/scripts/tpcc_load.js \
-jdbcDriver oracle.jdbc.driver.OracleDriver \
-jdbcUrl jdbc:oracle:thin:@datawarehouse_high?TNS_ADMIN=/home/opc/wallet_jdbc \
-jdbcUser tpcc \
-jdbcPass TccP12#_TccP12#_09:21:59 [INFO ] > JdbcRunner 1.3
09:21:59 [INFO ] [Config]
Program start time : 20191118-092158
Script filename : /home/opc/jdbcrunner/scripts/tpcc_load.js
JDBC driver : oracle.jdbc.driver.OracleDriver
JDBC URL : jdbc:oracle:thin:@datawarehouse_high?TNS_ADMIN=/home/opc/wallet_jdbc
JDBC user : tpcc
Load mode : true
Number of agents : 4
Auto commit : false
Debug mode : false
Trace mode : false
Log directory : logs
Parameter 0 : 0
Parameter 1 : 0
Parameter 2 : 0
Parameter 3 : 0
Parameter 4 : 0
Parameter 5 : 0
Parameter 6 : 0
Parameter 7 : 0
Parameter 8 : 0
Parameter 9 : 0
09:22:07 [INFO ] Tiny TPC-C - data loader
09:22:07 [INFO ] -param0 : Scale factor (default : 16)
09:22:07 [INFO ] -nAgents : Parallel loading degree (default : 4)
09:22:07 [INFO ] Scale factor : 16
09:22:07 [INFO ] Parallel loading degree : 4
09:22:07 [INFO ] Dropping tables ...
09:22:07 [WARN ] JavaException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist09:22:07 [WARN ] JavaException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
09:22:07 [WARN ] JavaException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
09:22:07 [WARN ] JavaException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
・・・・・
09:28:09 [INFO ] [Agent 2] orders : 20000 / 30000
09:28:13 [INFO ] [Agent 1] orders : 20000 / 30000
09:28:13 [INFO ] [Agent 0] orders : 20000 / 30000
09:28:18 [INFO ] [Agent 3] orders : 20000 / 30000
09:28:19 [INFO ] [Agent 2] orders : 30000 / 30000
09:28:22 [INFO ] [Agent 1] orders : 30000 / 30000
09:28:22 [INFO ] [Agent 0] orders : 30000 / 30000
09:28:24 [INFO ] [Agent 3] orders : 30000 / 30000
09:28:24 [INFO ] Creating indexes ...
09:28:54 [INFO ] Creating foreign keys ...
09:29:01 [INFO ] Analyzing tables ...
09:29:34 [INFO ] Completed.
09:29:34 [INFO ] < JdbcRunner SUCCESS
#TPCC実行
java -Djava.security.egd=file:/dev/./urandom \
-cp ~/jdbcrunner/jdbcrunner-1.3-nojdbc.jar:/usr/lib/oracle/18.3/client64/lib/ojdbc8.jar \
JR ~/jdbcrunner/scripts/tpcc.js \
-jdbcDriver oracle.jdbc.driver.OracleDriver \
-jdbcUrl jdbc:oracle:thin:@datawarehouse_high?TNS_ADMIN=/home/opc/wallet_jdbc \
-jdbcUser tpcc \
-jdbcPass TccP12#_TccP12#_
・・・・・
09:32:42 [INFO ] Tiny TPC-C
09:32:42 [INFO ] Scale factor : 16
09:32:42 [INFO ] tx0 : New-Order transaction
09:32:42 [INFO ] tx1 : Payment transaction
09:32:42 [INFO ] tx2 : Order-Status transaction
09:32:42 [INFO ] tx3 : Delivery transaction
09:32:42 [INFO ] tx4 : Stock-Level transaction
09:32:44 [INFO ] [Warmup] -299 sec, 0,8,1,0,1 tps, (0,8,1,0,1 tx)
・・・・・
＜参考＞
