autonomous databaseにhammerdbかけてみる
Oracle Cloud always free Advent Calendar 2019の17日目です。
今日はhammerdbを使って、autonomous database(ADW)に負荷をかけてみようと思います。
15日目のsqlplusで接続できる環境が整っていることが前提となります。
#hammerdbのダウンロード
wget https://github.com/TPC-Council/HammerDB/releases/download/v3.3/HammerDB-3.3-Linux-x86-64-Install
#hammerdbのインストール
$chmod +x HammerDB-3.3-Linux-x86-64-Install
$./HammerDB-3.3-Linux-x86-64-Install
This will install HammerDB on your computer. Continue? [n/Y] Y
Where do you want to install HammerDB? [/home/opc/HammerDB-3.3]
Installing HammerDB... Installing Program Files...
Installation complete.
#環境変数の設定
#instantclient 18.3を入れた場合なので、インストールしたOracleクライアントによってパスが変わるので注意
export ORACLE_HOME=/usr/lib/oracle/18.3/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_LIBRARY=$ORACLE_HOME/lib/libclntsh.so.18.1
#walletを~/walletに配置しているので TNS_ADMINを~/walletに指定する
export TNS_ADMIN=~/wallet
#HammerDBの起動
cd HammerDB-3.3/
./hammerdbcli
HammerDB CLI v3.3
Copyright (C) 2003-2019 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>librarycheck
Checking database library for Oracle
Success ... loaded library Oratcl for Oracle
#DBタイプをOracleに指定
hammerdb>dbset db ora
Database set to Oracle
#負荷タイプをTPC-Cに指定
hammerdb>dbset bm TPC-C
Benchmark set to TPC-C for Oracle
#TNS接続識別子を指定
hammerdb>diset connection instance adw_high
Changed connection:instance from oracle to adw_high for Oracle
#ADWの管理ユーザ/パスワードを指定
hammerdb>diset connection system_user ADMIN
Changed connection:system_user from system to ADMIN for Oracle
hammerdb>diset connection system_password XXXXX
Changed connection:system_password from manager to XXXXXX for Oracle
#tpccユーザのパスワードを指定(複雑性を要求されるので注意）
hammerdb>diset tpcc tpcc_pass TccP12#_TccP12#_
Changed tpcc:tpcc_pass from tpcc to TccP12#_TccP12#_ for Oracle
#Hammerdbで使用する表領域を指定
hammerdb>diset tpcc tpcc_def_tab DATA
Changed tpcc:tpcc_def_tab from tpcctab to DATA for Oracle
#実計測時はtimed
hammerdb>diset tpcc ora_driver timed
Clearing Script, reload script to activate new setting
Script cleared
Changed tpcc:ora_driver from test to timed for Oracle
#スキーマ作成
hammerdb>buildschema
Script cleared
Building 1 Warehouses(s) with 1 Virtual User
Ready to create a 1 Warehouse Oracle TPC-C schema
in database ADW_HIGH under user TPCC in tablespace DATA?
Enter yes or no: replied yes
Vuser 1 created - WAIT IDLE
RUNNING - TPC-C creation
Vuser 1:RUNNING
Vuser 1:CREATING TPCC SCHEMA
Vuser 1:CREATING USER tpcc
Vuser 1:CREATING TPCC TABLES
Vuser 1:Loading Item
・・・・・
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=8 W=1
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=9 W=1
Vuser 1:Orders Done
Vuser 1:Loading Orders for D=10 W=1
Vuser 1:Orders Done
Vuser 1:End:Thu Dec 05 05:50:58 GMT 2019
Vuser 1:CREATING TPCC INDEXES
Vuser 1:ORA-01031: insufficient privileges alter session set sort_area_size=5000000
Vuser 1:CREATING TPCC STORED PROCEDURES
Vuser 1:GATHERING SCHEMA STATISTICS
Vuser 1:TPCC SCHEMA COMPLETE
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
TPC-C Driver Script
※途中 ORA-1031が発生していますが、たぶん大丈夫。
#仮想ユーザのステータス確認
hammerdb>vustatus
1 = FINISH SUCCESS
#仮想ユーザの削除
hammerdb>vudestroy
Destroying Virtual Users
Virtual Users Destroyed
vudestroy success
#テストスクリプトのロード
hammerdb>loadscript
TPC-C Driver Script
Script loaded, Type "print script" to view
#仮想ユーザの設定
hammerdb>vuset vu 4
hammerdb>vuset logtotemp 1
hammerdb>vuset unique 1
hammerdb>vuset timestamps 1
#仮想ユーザの作成
hammerdb>vucreate
Vuser 1 created MONITOR - WAIT IDLE
Vuser 2 created - WAIT IDLE
Vuser 3 created - WAIT IDLE
Vuser 4 created - WAIT IDLE
Vuser 5 created - WAIT IDLE
Logging activated
to /tmp/hammerdb_5DE89E98598E03E283235393.log
5 Virtual Users Created with Monitor VU
#テストの実行
hammerdb>vurun
RUNNING - Oracle Timed TPC-C
Vuser 1:RUNNING
Vuser 1:Beginning rampup time of 2 minutes
Vuser 2:RUNNING
Vuser 2:Processing 1000000 transactions with output suppressed...
Vuser 3:RUNNING
Vuser 3:Processing 1000000 transactions with output suppressed...
Vuser 4:RUNNING
Vuser 4:Processing 1000000 transactions with output suppressed...
Vuser 5:RUNNING
Vuser 5:Processing 1000000 transactions with output suppressed...
・・・・・
Vuser 1:FINISHED SUCCESS
Vuser 2:FINISHED SUCCESS
Vuser 5:FINISHED SUCCESS
Vuser 3:FINISHED SUCCESS
Vuser 4:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
TPC-C Driver Script
＜参考＞
