Installing & Testing SLOB

Hi everyone, or maybe just you that read my blog… anyway…

In this post I’ll be trying to setup and run SLOB. Just in case you don’t know what it is, it’s a great tool to test database I/O performance made by Kevin Closson and you can find all the information here.

Usually documentation is for the weak, in this case, let’s find out…

My setup is based on physical machines. Lucky me I have old and unused hardware that I’ll use for this test  (maybe some will recognize it…):

• CentOS Linux release 7.1.1503 (Core)
• Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
• SUN FIRE X4170 SERVER for database server
• SUN FIRE X4275 SERVER for storage

I’ll be using a ZFS filesystem based database, mounted with NFS through infiniband.

[oracle@ ~]$ df -k
172.20.6.5:/dbstorage1 1125768192 8222720 1117545472 1% /oradata/dbstorage1

Let’s install SLOB :

[oracle@ oracle]$ ls -l
total 4120
-rw-r--r--. 1 oracle oinstall 4209173 Jun 11 16:53 2017.05.10.slob_2.4.0.1.tar.gz
drwxr-xr-x. 7 oracle oinstall 4096 May 10 20:57 SLOB

And game over… version does not support Oracle Multitenant Option.

[oracle@ SLOB]$ ./setup.sh SLOB 2
SLOB 2.4.0
NOTIFY : 2017.06.11-16:55:46 :
NOTIFY : 2017.06.11-16:55:46 : Begin SLOB setup.
NOTIFY : 2017.06.11-16:55:46 : ADMIN_CONNECT_STRING: "system/manager"
NOTIFY : 2017.06.11-16:55:46 : Load parameters from slob.conf:

SCALE: 80M (10240 blocks)
SCAN_TABLE_SZ: 1M (128 blocks)
LOAD_PARALLEL_DEGREE: 2
ADMIN_SQLNET_SERVICE: ""
SYSDBA_PASSWD: "manager"
DBA_PRIV_USER: "system"

Note: setup.sh will use the following connect strings as per slob.conf:
 Admin Connect String: "system/manager"
 Non-Admin Connect String: " "

NOTIFY : 2017.06.11-16:55:46 : Testing Admin connect using "sqlplus -L system/manager"
FATAL : 2017.06.11-16:55:47 :
FATAL : 2017.06.11-16:55:47 : This version of SLOB does not support Oracle Multitenant Option
FATAL : 2017.06.11-16:55:47 :

<creating a non-CDB database>

Let’s install SLOB now :

[oracle@ SLOB]$ ./setup.sh SLOB 2
SLOB 2.4.0
NOTIFY : 2017.06.11-17:17:24 :
NOTIFY : 2017.06.11-17:17:24 : Begin SLOB setup.
NOTIFY : 2017.06.11-17:17:24 : ADMIN_CONNECT_STRING: "system/manager"
NOTIFY : 2017.06.11-17:17:24 : Load parameters from slob.conf:

SCALE: 80M (10240 blocks)
SCAN_TABLE_SZ: 1M (128 blocks)
LOAD_PARALLEL_DEGREE: 2
ADMIN_SQLNET_SERVICE: ""
SYSDBA_PASSWD: "manager"
DBA_PRIV_USER: "system"

Note: setup.sh will use the following connect strings as per slob.conf:
 Admin Connect String: "system/manager"
 Non-Admin Connect String: " "

NOTIFY : 2017.06.11-17:17:24 : Testing Admin connect using "sqlplus -L system/manager"
NOTIFY : 2017.06.11-17:17:26 : Dropping prior SLOB schemas. This may take a while if there is a large number of old schemas.
NOTIFY : 2017.06.11-17:17:26 : Previous SLOB schemas have been removed
NOTIFY : 2017.06.11-17:17:26 : Preparing to load 2 schema(s) into tablespace: SLOB
NOTIFY : 2017.06.11-17:17:26 : Loading user1 schema
NOTIFY : 2017.06.11-17:17:42 : Finished loading, indexing and gathering statistics on user1 schema in 16 seconds
NOTIFY : 2017.06.11-17:17:42 : Commencing multiple, concurrent schema creation and loading
NOTIFY : 2017.06.11-17:17:42 : Waiting for background batch 1. Loading up to user2
NOTIFY : 2017.06.11-17:17:45 : Completed concurrent data loading phase: 3 seconds
NOTIFY : 2017.06.11-17:17:45 : Creating SLOB procedure
NOTIFY : 2017.06.11-17:17:45 : SLOB procedure created
NOTIFY : 2017.06.11-17:17:46 : Row and block counts for SLOB table(s) reported in ./slob_data_load_summary.txt
NOTIFY : 2017.06.11-17:17:46 : Please examine ./slob_data_load_summary.txt for any possbile errors
NOTIFY : 2017.06.11-17:17:46 :
NOTIFY : 2017.06.11-17:17:46 : NOTE: No errors detected but if ./slob_data_load_summary.txt shows errors then
NOTIFY : 2017.06.11-17:17:46 : examine /u01/app/oracle/SLOB/cr_tab_and_load.out

NOTIFY : 2017.06.11-17:17:46 : SLOB setup complete. Total setup time: (22 seconds)
NOTIFY : 2017.06.11-17:17:46 : Please do not forget to compile the wait kit.
NOTIFY : 2017.06.11-17:17:46 : Please change directories to ./wait_kit and execute make(1).
NOTIFY : 2017.06.11-17:17:46 : Example:
NOTIFY : 2017.06.11-17:17:46 : $ cd ./wait_kit
NOTIFY : 2017.06.11-17:17:46 : $ make
[oracle@ SLOB]$ cd ./wait_kit
[oracle@ wait_kit]$ make
rm -fr *.o mywait trigger create_sem
cc -c -o mywait.o mywait.c
cc -o mywait mywait.o
cc -c -o trigger.o trigger.c
cc -o trigger trigger.o
cc -c -o create_sem.o create_sem.c
cc -o create_sem create_sem.o
cp mywait trigger create_sem ../
rm -fr *.o

Success ! (this is the moment when you say I should have read the documentation, right?)

Now let’s run SLOB, first check execution options :

[oracle@ SLOB]$ ./runit.sh
NOTIFY : 2017.06.11-17:20:43 : For security purposes all file and directory creation and deletions
NOTIFY : 2017.06.11-17:20:43 : performed by ./runit.sh are logged in: /u01/app/oracle/SLOB/.file_operations_audit_trail.out.
NOTIFY : 2017.06.11-17:20:43 : SLOB TEMPDIR is /tmp/.SLOB.2017.06.11.172043. SLOB will delete this directory at the end of this execution.

FATAL : 2017.06.11-17:20:43 : Invalid command line. Abort.

./runit.sh supports the following command usage:

1. Single Option Invocation.
 $ sh ./runit.sh <number-of-SLOB-schemas-to-test>

2. Multiple Option Invocation
 2.1 This invocation style requires *exactly* four options.
 $ sh ./runit.sh -s <number-of-slob-schemas-to-test> -t <SLOB-threads-per-schema>

NOTE: With Single Option Invocation slob.conf->THREADS_PER_SCHEMA is used. If you
 want more than a single SLOB thread per schema set THREADS_PER_SCHEMA in slob.conf.
 The default setting for slob.conf->THREADS_PER_SCHEMA is 1.

With Multiple Option Invocation slob.conf->THREADS_PER_SCHEMA is overridden.
 The number of SLOB threads per schema is taken from the argument passed
 in with the -t option.

EXAMPLES:

Example 1. 256 SLOB schemas each with slob.conf->THREADS_PER_SCHEMA number
 of SLOB threads per schema:
 $ sh ./runit.sh 256

Example 2. 16 SLOB schemas each with 32 SLOB threads:
 $ sh ./runit.sh -s 16 -t 32

NOTE: Example 2 produces 512 (16*32) Oracle Database sessions.

ADDITIONAL INFORMATION: The SLOB documentation at kevinclosson.net/slob or SLOB/doc




FATAL : 2017.06.11-17:20:43 : Aborting execution. Cleaning up SLOB temporary directory (/tmp/.SLOB.2017.06.11.172043).

Seems easy enough to start a basic test. I haven’t change any configuration except for “DATABASE_STATISTICS_TYPE: awr”  :

[oracle@ SLOB]$ ./runit.sh 2
NOTIFY : 2017.06.11-17:23:42 : For security purposes all file and directory creation and deletions
NOTIFY : 2017.06.11-17:23:42 : performed by ./runit.sh are logged in: /u01/app/oracle/SLOB/.file_operations_audit_trail.out.
NOTIFY : 2017.06.11-17:23:42 : SLOB TEMPDIR is /tmp/.SLOB.2017.06.11.172342. SLOB will delete this directory at the end of this execution.
NOTIFY : 2017.06.11-17:23:42 : Sourcing in slob.conf
NOTIFY : 2017.06.11-17:23:42 : Performing initial slob.conf sanity check...
NOTIFY : 2017.06.11-17:23:42 :
NOTIFY : 2017.06.11-17:23:42 : SQLNET_SERVICE_BASE is not set. Users will connect via bequeth connections (not SQL*Net).
NOTIFY : 2017.06.11-17:23:42 : Connecting to the instance to validate slob.conf->SCALE setting.

UPDATE_PCT: 10
SCAN_PCT: 0
RUN_TIME: 300
WORK_LOOP: 0
SCALE: 80M (10240 blocks)
WORK_UNIT: 64
REDO_STRESS: LITE
HOT_SCHEMA_FREQUENCY: 0
HOTSPOT_MB: 8
HOTSPOT_OFFSET_MB: 16
HOTSPOT_FREQUENCY: 3
THINK_TM_FREQUENCY: 0
THINK_TM_MIN: .1
THINK_TM_MAX: .5
DATABASE_STATISTICS_TYPE: awr
SYSDBA_PASSWD: "manager"
DBA_PRIV_USER: "system"
ADMIN_SQLNET_SERVICE: ""
SQLNET_SERVICE_BASE: ""
SQLNET_SERVICE_MAX: ""

EXTERNAL_SCRIPT: ""




Note: runit.sh will use the following connect strings as per slob.conf settings:
 Admin Connect String: "system/manager"

NOTIFY : 2017.06.11-17:23:42 : Clearing temporary SLOB output files from previous SLOB testing.
NOTIFY : 2017.06.11-17:23:42 : Testing admin connectivity to the instance to validate slob.conf settings.
NOTIFY : 2017.06.11-17:23:42 : Testing connectivity. Command: "sqlplus -L system/manager".
NOTIFY : 2017.06.11-17:23:42 : Next, testing 2 user (non-admin) connections...
NOTIFY : 2017.06.11-17:23:42 : Testing connectivity. Command: "sqlplus -L user1/user1".
NOTIFY : 2017.06.11-17:23:43 : Testing connectivity. Command: "sqlplus -L user2/user2".
NOTIFY : 2017.06.11-17:23:43 : Performing redo log switch.
NOTIFY : 2017.06.11-17:23:46 : Redo log switch complete. Setting up trigger mechanism.
NOTIFY : 2017.06.11-17:23:56 : Running iostat, vmstat and mpstat on current host--in background.
NOTIFY : 2017.06.11-17:23:56 : Connecting 1 (THREADS_PER_SCHEMA) session(s) to 2 schema(s) ...
NOTIFY : 2017.06.11-17:23:56 :
NOTIFY : 2017.06.11-17:23:56 : Executing awr "before snap" procedure. Command: "sqlplus -S -L system/manager".
NOTIFY : 2017.06.11-17:23:58 : Before awr snap ID is 1
NOTIFY : 2017.06.11-17:23:58 :
NOTIFY : 2017.06.11-17:23:58 : Test has been triggered. Processes are executing.
NOTIFY : 2017.06.11-17:23:58 : List of monitored sqlplus PIDs written to /tmp/.SLOB.2017.06.11.172342/3412.f_wait_pids.out.
NOTIFY : 2017.06.11-17:24:08 : Waiting for 287 seconds before monitoring running processes (for exit).
NOTIFY : 2017.06.11-17:28:55 : Entering process monitoring loop.
NOTIFY : 2017.06.11-17:29:04 : Run time 306 seconds.
NOTIFY : 2017.06.11-17:29:04 : Executing awr "after snap" procedure. Command: "sqlplus -S -L system/manager".
NOTIFY : 2017.06.11-17:29:05 : After awr snap ID is 2
NOTIFY : 2017.06.11-17:29:06 : Terminating background data collectors.
./runit.sh: line 1533: 11785 Killed ( iostat -xm 3 > iostat.out 2>&1 )
./runit.sh: line 1533: 11786 Killed ( vmstat 3 > vmstat.out 2>&1 )
./runit.sh: line 1533: 11787 Killed ( mpstat -P ALL 3 > mpstat.out 2>&1 )
NOTIFY : 2017.06.11-17:29:25 :
NOTIFY : 2017.06.11-17:29:25 : SLOB test is complete.
NOTIFY : 2017.06.11-17:29:25 : Cleaning up SLOB temporary directory (/tmp/.SLOB.2017.06.11.172342).

My little disks were over 85 at %util… only 3 disks … need to reconfigure ZFS !

avg-cpu: %user %nice %system %iowait %steal %idle
 0.06 0.00 4.80 6.63 0.00 88.50

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc 0.00 280.00 0.00 1100.00 0.00 124120.00 112.84 1.50 1.36 0.79 86.60
sdb 0.00 277.00 0.00 1103.00 0.00 123952.00 112.38 1.50 1.36 0.79 86.80
sdf 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdd 0.00 277.00 0.00 1104.00 0.00 123328.00 111.71 1.53 1.39 0.79 87.50
sdg 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

So this was my 1st try with SLOB. Results were disappointing as the I/O was terrible on my ZFS file system … lots of tunning to be made. Maybe I should call someone that knows about this 🙂

! NEVER GIVE UP !

!! MAMA SUMAE !!

AWR report is posted here: awrrpt_1_1_2

I’ll post again after my file system tuning is done. Thank you if you read until here  🙂

UPDATE : Looks like it happened before I was expecting as Kevin suggested the database configuration was a default one. I’ve increased both REDO and UNDO size and these were the results : awrrpt_1_54_55