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
Hello Andre,
Thanks for posting the awr report from this. I read it and I’d like to point out that I/O is not currently your bottleneck. You need to create some large redo logs and create a large undo tablespace and then re-run this. I’m presuming you currently have the default 50MB redo and tiny UNDO.
LikeLike
Hello Kevin.
Thank you so much for your comment.
Indeed this database was left with default configuration, I’ll reconfigure it and do more testing over ZFS. I’ll update this post as soon as I get new AWR.
Thanks again.
LikeLike