autonomous databaseにswingbenchかけてみる

Oracle Cloud always free Advent Calendar 2019の18日目です。

今日は、Swingbenchを使って、 autonomous databaseに負荷をかけてみようと思います。

 

#swingbenchのダウンロード
wget http://dominicgiles.com/swingbench/swingbenchlatest.zip

 

#解凍
unzip swingbenchlatest.zip

 

#java のインストール
sudo yum -y install java-1.8.0-openjdk

 

#ウォレットのダウンロード
#方法1
Autonomous Database->Autonomous Data Warehouse で接続したいDBを選択
DB接続をクリックし、ウォレットのダウンロードをクリック
パスワードを入力する。

Wallet_datawarehouse.zipがダウンロードされるので、always free Computeインスタンスへアップロードする。(格納先は~/walletとする)

 

#方法2(OCI CLIが使用できることが前提)

oci db autonomous-database listコマンドで接続したいDBのOCIDを確認
oci db autonomous-database list
・・・・
"freeform-tags": {},
"id": "ocid1.autonomousdatabase.oc1.ap-tokyo-1.abxhiljrdouplsv4h3cmqjkyphjr6lkpie72fgrgvr2ehmXXXXX",
"is-auto-scaling-enabled": false,
"is-dedicated": false,
・・・・
#idがOCID

 

#ウォレットのダウンロード
mkdir ~/wallet
cd ~/wallet
oci db autonomous-database generate-wallet \
--autonomous-database-id ocid1.autonomousdatabase.oc1.ap-tokyo-1.abxhiljrdouplsv4h3cmqjkyphjr6lkpie72fgrgvr2ehm5i2ti2aryjltkq \
--file Wallet_datawarehouse.zip \
--password Passw0rd

 

~/walletディレクトリにWallet_datawarehouse.zip が配置される


#swingbenchスキーマの作成&データのロード
cd swingbench/bin

# -cf ウォレットのパス(zipのままでOK)
# -cs 接続識別子
# -ts 表領域
#-dbap ADMINユーザのパスワード
# -dba 管理者ユーザ(ADMIN
# -u swingbench用に新規作成するユーザ名
# -p 新規作成するユーザ用のパスワード
# -async_off 非同期モード
# -create スキーマの作成
# -cl CLIモード
./oewizard -cf ~/wallet/Wallet_datawarehouse.zip \
-cs adw_high \
-ts DATA \
-dbap XXXXXXXX \
-dba ADMIN \
-u soe \
-p TccP12#_TccP12#_ \
-async_off \
-scale 1 \
-create \
-cl \
-v
Operation is successfully completed.
Operation is successfully completed.
SwingBench Wizard
Author : Dominic Giles
Version : 2.6.0.1124

Running in Lights Out Mode using config file : ../wizardconfigs/oewizard.xml
Connecting to : jdbc:oracle:thin:@adw_high
Connected
Running script ../sql/soedgcreateuser.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 329 millisecond(s)
Starting run
Dec 05, 2019 8:34:24 AM oracle.simplefan.impl.FanManager configure
SEVERE: attempt to configure ONS in FanManager failed with oracle.ons.NoServersAvailable: Subscription time out
Starting script ../sql/soedgdrop2.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 57 millisecond(s)
Starting script ../sql/soedgcreatetables2.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 185 millisecond(s)
Starting script ../sql/soedgviews.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 25 millisecond(s)
Starting script ../sql/soedgsqlset.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 90 millisecond(s)
Inserting data into table ADDRESSES_750001
Inserting data into table ADDRESSES_2
Inserting data into table CUSTOMERS_500001
Inserting data into table CUSTOMERS_2
Completed processing table ADDRESSES_750001 in 0:00:31
Inserting data into table ORDER_ITEMS_714895
Inserting data into table ORDERS_714896
Completed processing table ADDRESSES_2 in 0:00:32
Completed processing table CUSTOMERS_500001 in 0:00:38
Inserting data into table ORDER_ITEMS_1
Inserting data into table ORDERS_2
Completed processing table CUSTOMERS_2 in 0:00:39
Completed processing table ORDERS_2 in 0:02:30
Inserting data into table CARD_DETAILS_750001
Inserting data into table CARD_DETAILS_2
Completed processing table ORDER_ITEMS_1 in 0:02:32
Completed processing table ORDERS_714896 in 0:02:45
Inserting data into table LOGON_1191493
Completed processing table ORDER_ITEMS_714895 in 0:02:47
Inserting data into table LOGON_2
Completed processing table CARD_DETAILS_750001 in 0:00:16
Inserting data into table INVENTORIES
Inserting data into table PRODUCT_INFORMATION
Completed processing table PRODUCT_INFORMATION in 0:00:00
Completed processing table CARD_DETAILS_2 in 0:00:16
Inserting data into table PRODUCT_DESCRIPTIONS
Completed processing table PRODUCT_DESCRIPTIONS in 0:00:00
Inserting data into table WAREHOUSES
Completed processing table WAREHOUSES in 0:00:00
Completed processing table LOGON_1191493 in 0:00:17
Completed processing table INVENTORIES in 0:00:11
Connection cache closed
Starting script ../sql/soedganalyzeschema2.sql
Script completed in 0 hour(s) 0 minute(s) 24 second(s) 422 millisecond(s)
Starting script ../sql/soedgconstraints2.sql
Script completed in 0 hour(s) 0 minute(s) 9 second(s) 608 millisecond(s)
Starting script ../sql/soedgindexes2.sql
Script completed in 0 hour(s) 0 minute(s) 29 second(s) 13 millisecond(s)
Starting script ../sql/soedgsequences2.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 786 millisecond(s)
Starting script ../sql/soedgpackage2_header.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 224 millisecond(s)
Starting script ../sql/soedgpackage2_body.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 150 millisecond(s)
Starting script ../sql/soedgsetupmetadata.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 606 millisecond(s)

============================================
| Datagenerator Run Stats |
============================================
Connection Time 0:00:00.004
Data Generation Time 0:03:37.918
DDL Creation Time 0:01:35.491
Total Run Time 0:05:13.415
Rows Inserted per sec 55,554
Data Generated (MB) per sec 4.5
Actual Rows Generated 13,011,798
Commits Completed 672
Batch Updates Completed 65,082

Connecting to : jdbc:oracle:thin:@adw_high
Connected

Post Creation Validation Report
===============================
The schema appears to have been created successfully.

Valid Objects
=============
Valid Tables : 'ORDERS','ORDER_ITEMS','CUSTOMERS','WAREHOUSES','ORDERENTRY_METADATA','INVENTORIES','PRODUCT_INFORMATION','PRODUCT_DESCRIPTIONS','ADDRESSES','CARD_DETAILS'
Valid Indexes : 'PRD_DESC_PK','PROD_NAME_IX','PRODUCT_INFORMATION_PK','PROD_SUPPLIER_IX','PROD_CATEGORY_IX','INVENTORY_PK','INV_PRODUCT_IX','INV_WAREHOUSE_IX','ORDER_PK','ORD_SALES_REP_IX','ORD_CUSTOMER_IX','ORD_ORDER_DATE_IX','ORD_WAREHOUSE_IX','ORDER_ITEMS_PK','ITEM_ORDER_IX','ITEM_PRODUCT_IX','WAREHOUSES_PK','WHS_LOCATION_IX','CUSTOMERS_PK','CUST_EMAIL_IX','CUST_ACCOUNT_MANAGER_IX','CUST_FUNC_LOWER_NAME_IX','ADDRESS_PK','ADDRESS_CUST_IX','CARD_DETAILS_PK','CARDDETAILS_CUST_IX'
Valid Views : 'PRODUCTS','PRODUCT_PRICES'
Valid Sequences : 'CUSTOMER_SEQ','ORDERS_SEQ','ADDRESS_SEQ','LOGON_SEQ','CARD_DETAILS_SEQ'
Valid Code : 'ORDERENTRY'
Schema Created

 


#負荷をかける
$ ./charbench -c ../configs/SOE_Server_Side_V2.xml \
-cf ~/wallet/Wallet_datawarehouse.zip \
-cs adw_high \
-u soe \
-p TccP12#_TccP12#_ \
-rt 0:0.30
Author : Dominic Giles
Version : 2.6.0.1124

Results will be written to results.xml.
Hit Return to Terminate Run...

Time Users TPM TPS

Operation is successfully completed.
Operation is successfully completed.
8:55:02 AM X XXXX XX

 

<参考>

www.dominicgiles.com