Setup Compute Instance to host Swingbench application

Screen Shot 2018-11-28 at 09.51.58

In this post I show you how easy it is to install a 3rd party application like Swingbench (credits to Dominic Giles) on a Compute Instance within the Oracle Cloud and connect to an Autonomous Transaction Processing Database.

From the Cloud Infrastructure Console menu select Compute > Instances

Instances

Press [Create Instance]

Create Instance

Create Instance 3

Press [Create Instance]

The instance will now get provisioned. After some minutes you will notice the status changes from Provisioning to Running

Connect to the newly created Compute Instance using the default user opc

Reference: http://www.dominicgiles.com/blog/files/category-swingbench.html

Robs-MacBook-Pro:.ssh rzoeteweij$ ssh opc@130.61.120.117 -i /Users/rzoeteweij/.ssh/id_rsa

The authenticity of host '130.61.120.117 (130.61.120.117)' can't be established.
ECDSA key fingerprint is SHA256:u4oqHV70YZoDV9CAih8AORkgxQ3CLvZZRTQroOPfc8k.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '130.61.120.117' (ECDSA) to the list of known hosts.
[opc@swingbench-client ~]$
[opc@swingbench-client ~]$ sudo yum makecache fast

Now install Java JDK 8

[opc@swingbench-client ~]$ sudo yum install java-1.8.0-openjdk-headless.x86_64
[opc@swingbench-client ~]$ java -version
openjdk version "1.8.0_191"
OpenJDK Runtime Environment (build 1.8.0_191-b12)
OpenJDK 64-Bit Server VM (build 25.191-b12, mixed mode)

Next, we install the swingbench code

[opc@swingbench-client ~]$ curl http://www.dominicgiles.com/swingbench/swingbenchlatest.zip -o swingbench.zip
[opc@swingbench-client ~]$ unzip swinbench.zip
[opc@swingbench-client ~]$ ls -al

total 23348

drwx------.  6 opc  opc      4096 Nov  2 08:20 .
drwxr-xr-x.  3 root root       17 Nov  1 14:35 ..
-rw-r--r--.  1 opc  opc        18 Jan 26  2018 .bash_logout
-rw-r--r--.  1 opc  opc       193 Jan 26  2018 .bash_profile
-rw-r--r--.  1 opc  opc       231 Jan 26  2018 .bashrc
drwxrwxr-x.  3 opc  opc        18 Nov  2 07:47 .cache
drwxrwxr-x.  3 opc  opc        18 Nov  2 07:47 .config
drwx------.  2 opc  opc        29 Nov  1 14:35 .ssh
drwx------. 12 opc  opc      4096 Sep  6 17:45 swingbench
-rw-rw-r--.  1 opc  opc  23885962 Nov  2 08:18 swingbench.zip

The wallet file we have created from database DBATPD01 needs to be copied to the swingbench_client server

Robs-MacBook-Pro:admin rzoeteweij$ scp Wallet_DBATPD01.zip opc@130.61.80.167:/home/opc/swingbench
Wallet_DBATPD01.zip                                                                                                           100%   19KB 220.1KB/s   00:00
Robs-MacBook-Pro:admin rzoeteweij$

Install workload schema into DBATPD01

[opc@swingbench-client bin]$ ./oewizard -cf /home/opc/swingbench/Wallet_DBATPD01.zip \
>            -cs dbatpd01_medium \
>            -ts DATA \
>            -dbap <DB Admin Password> \
>            -dba admin \
>            -u soe \
>            -p <SOE Password to be created> \
>            -async_off \
>            -scale 5 \
>            -hashpart \
>            -create \
>            -cl \
>            -v

SwingBench Wizard
Author  :   Dominic Giles
Version :   2.6.0.1090

Running in Lights Out Mode using config file : ../wizardconfigs/oewizard.xml
Connecting to : jdbc:oracle:thin:@dbatpd01_medium
Connected
Running script ../sql/soedgcreateuser.sql
Script completed in 0 hour(s) 0 minute(s) 10 second(s) 813 millisecond(s)

Starting run
Starting script ../sql/soedgdrop2.sql
Script completed in 0 hour(s) 0 minute(s) 8 second(s) 159 millisecond(s)
Starting script ../sql/soedgcreatetableshash2.sql
Script completed in 0 hour(s) 4 minute(s) 8 second(s) 376 millisecond(s)
Starting script ../sql/soedgviews.sql
Script completed in 0 hour(s) 0 minute(s) 5 second(s) 138 millisecond(s)
Starting script ../sql/soedgsqlset.sql
Script completed in 0 hour(s) 0 minute(s) 30 second(s) 979 millisecond(s)
Inserting data into table ADDRESSES_5624999

...

Script completed in 0 hour(s) 0 minute(s) 1 second(s) 395 millisecond(s)

============================================
|           Datagenerator Run Stats        |
============================================
Connection Time                        0:00:00.004
Data Generation Time                   1:02:33.445
DDL Creation Time                      0:21:37.988
Total Run Time                         1:24:11.439
Rows Inserted per sec                       16,122
Data Generated (MB) per sec                    1.3
Actual Rows Generated                   61,608,289
Commits Completed                            3,116
Batch Updates Completed                    308,078

Connecting to : jdbc:oracle:thin:@dbatpd01_medium
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

While viewing the Database activity during the creation of the SOE schema we see the following:

Overview

Activity

Next, we are doing some checks on the created schema

[opc@swingbench-client bin]$ ./sbutil -soe -cf /home/opc/swingbench/Wallet_DBATPD01.zip -cs dbatpd01_medium -u soe -p <SOE Schema user password> -val

The Order Entry Schema appears to be valid.

--------------------------------------------------
|Object Type    |     Valid|   Invalid|   Missing|
--------------------------------------------------
|Table          |        10|         0|         0|
|Index          |        26|         0|         0|
|Sequence       |         5|         0|         0|
|View           |         2|         0|         0|
|Code           |         1|         0|         0|
--------------------------------------------------

Collect statistics for SOE schema

[opc@swingbench-client bin]$ ./sbutil -soe -cf /home/opc/swingbench/Wallet_DBATPD01.zip -cs dbatpd01_medium -u soe -p <SOE Schema user password> -stats

Collecting statistics for the schema

Collected statistics in : 0:01:52.533

Run a workload

Adjust swingbench configuration to load the users on in a sensible fashion (i.e. to not exceed the login rate)

sed -i -e 's/<LogonGroupCount>1<\/LogonGroupCount>/<LogonGroupCount>5<\/LogonGroupCount>/' \
-e 's/<LogonDelay>0<\/LogonDelay>/<LogonDelay>300<\/LogonDelay>/' \
-e 's/<WaitTillAllLogon>true<\/WaitTillAllLogon>/<WaitTillAllLogon>false<\/WaitTillAllLogon>/' \
../configs/SOE_Server_Side_V2.xml

[opc@swingbench-client bin]$ ./charbench -c ../configs/SOE_Server_Side_V2.xml \
>             -cf /home/opc/swingbench/Wallet_DBATPD01.zip \
>             -cs dbatpd01_low \
>             -u soe \
>             -p <SOE Schema user password> \
>             -v users,tpm,tps,vresp \
>             -intermin 0 \
>             -intermax 0 \
>             -min 0 \
>             -max 0 \
>             -uc 20 \
>             -di SQ,WQ,WA \
>             -rt 0:0.30

Author  :   Dominic Giles
Version :   2.6.0.1090

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

Time     Users       TPM      TPS     NCR   UCD   BP    OP    PO    BO    SQ    WQ    WA
11:29:45 [0/20]      0        0       0     0     0     0     0     0     0     0     0
11:29:47 [0/20]      0        0       0     0     0     0     0     0     0     0     0
11:29:48 [0/20]      0        0       0     0     0     0     0     0     0     0     0
11:29:49 [20/20]     0        0       0     0     0     0     0     0     0     0     0
11:29:50 [20/20]     0        0       0     0     1140  0     0     0     0     0     0
11:29:51 [20/20]     8        8       0     0     1921  1510  1696  0     0     0     0
11:29:52 [20/20]     27       19      859   154   658   1668  1696  0     0     0     0
11:29:53 [20/20]     40       13      1139  409   66    1737  842   1628  0     0     0
11:29:54 [20/20]     62       22      2154  381   982   2079  842   1628  0     0     0
11:29:55 [20/20]     78       16      2154  381   310   1315  752   937   0     0     0
11:29:56 [20/20]     96       18      2172  381   1308  2282  752   845   0     0     0
11:29:57 [20/20]     108      12      2225  381   828   1989  752   845   0     0     0
11:29:58 [20/20]     124      16      1064  1124  455   734   752   1433  0     0     0
11:29:59 [20/20]     143      19      2681  1124  88    2268  981   950   0     0     0
11:30:00 [20/20]     168      25      805   301   274   1539  805   1023  0     0     0
11:30:01 [20/20]     186      18      805   916   1229  1368  805   935   0     0     0
11:30:02 [20/20]     203      17      1941  428   453   701   1468  1253  0     0     0
11:30:03 [20/20]     228      25      1559  428   333   1275  1468  618   0     0     0
11:30:04 [20/20]     260      32      569   262   449   623   509   754   0     0     0
11:30:05 [20/20]     281      21      1678  197   153   1844  509   754   0     0     0
11:30:06 [20/20]     306      25      1587  648   991   960   509   757   0     0     0
11:30:07 [20/20]     329      23      1221  648   1273  2093  1110  445   0     0     0
11:30:08 [20/20]     344      15      1221  608   1341  1374  1110  608   0     0     0
11:30:09 [20/20]     348      4       2070  608   924   1374  1832  528   0     0     0
11:30:10 [20/20]     369      21      1359  608   785   1466  1007  1233  0     0     0
11:30:11 [20/20]     392      23      969   568   996   1674  1007  1227  0     0     0
11:30:12 [20/20]     411      19      683   440   401   1807  1007  1024  0     0     0
11:30:13 [20/20]     432      21      1126  388   185   274   475   323   0     0     0
11:30:14 [20/20]     460      28      974   311   741   1147  475   1217  0     0     0
11:30:15 [20/20]     476      16      1337  311   730   1390  475   1217  0     0     0
11:30:16 [20/20]     484      8       2069  813   378   2847  475   1217  0     0     0
11:30:17 [0/20]      499      15      2069  813   378   2847  475   1217  0     0     0

Completed Run.

Additional DB statistics using dbtimemonitoring

On my desktop I have downloaded the dbtimemonitoring from http://www.dominicgiles.com/swingbench/dbtimemonitor16Aug2018.zip

I have changed the xml in the dbtimemonitoring

Robs-MacBook-Pro:bin rzoeteweij$ cat databases.xml
<?xml version = '1.0' encoding = 'UTF-8'?>
<WaitMonitor Title="Monitored Databases" xmlns="http://www.dominicgiles.com/waitmonitor">
<!-- You can add as many Monitored Databases as you like. -->
<!-- They will connect to the server over the thin jdbc connection stack.-->
<!-- Passwords will be encrypted from clear text after the file is read.-->
<!-- To change/update a password simply change all the text between the <Password>
tags</Password>-->
<!-- -->
<MonitoredDatabase>
<ConnectString>adb.eu-frankfurt-1.oraclecloud.com:1522/aa8lbv43ewwd_dbatpd01_low.atp.oraclecloud.com</ConnectString>
<Comment>Test ERP System</Comment>
<Username>Admin</Username>
<Password>Password</Password>
</MonitoredDatabase>
</WaitMonitor>

Run the dbtimemonitor tool

./dbtimemonitor -u admin -p Password -cs dbatpd01_medium -cf /Users/rzoeteweij/instantclient_12_2/network/admin/Wallet_DBATPD01.zip

dbmonitoring

Run swingbench from my laptop

./swingbench -c ../configs/SOE_Server_Side_V2.xml \
-cf /Users/rzoeteweij/instantclient_12_2/network/admin/Wallet_DBATPD01.zip \
-cs dbatpd01_low \
-u soe \
-p Password \
-intermin 0 \
-intermax 0 \
-min 0 \
-max 0 \
-uc 20 \
-di SQ,WQ,WA

swingbench

Disclaimer
Please notice that all statements made by me and information provided on this blog are mine and not necessarily those of Oracle Corporation.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s