Silly Little Oracle Benchmark (SLOB)

From Outrun Wiki
(Redirected from SLOB Package)
Jump to: navigation, search

Overview

SLOB (Silly Little Oracle Benchmark) is an IO generator for Oracle that drives lots of real database I/O using minimal CPU resources. It is therefore THE best tool to run I/O related database tests.

From the SLOB user guide:

SLOB drives Oracle Database to perform massive-­‐scale SQL execution with minimal host CPU utilization. This characteristic is true whether testing for physical I/O capability or when the entire data set is cached in the SGA buffer pool.

Installing SLOB traditionally requires downloading a "tar" package, unpacking it (usually in a HOME directory), compiling a few binaries (the wait_kit), then running the provided "setup.sh" and "runit.sh" scripts.

In an effort to make things a bit easier, I re-packaged the SLOB tar file as RPM so that it works directly on Enterprise Linux systems (version 6.x).

Features of the SLOB RPM package
  • Runs on all flavours of Enterprise Linux (Red Hat - compatible) i.e. Oracle, CentOS or Red Hat
  • Requires no additional RPMs to install
  • Sets up a "slob" group and a "slob" special user to run SLOB
  • Puts the original SLOB files in a shared, root-owned, non-modifyable folder (/usr/share/slob)
  • Deletes the files and the special user/group when the RPM package is uninstalled
  • Sets up the environment ($PATH and $SQLPATH) for users who are member of the "slob" group
  • Provides wrapper shell scripts "slob-setup" and "slob-run" to make working with SLOB a little bit easier
  • Provides additional SQL scripts to enhance SLOB ease-of-use and functionality

Copyright

SLOB is copyrighted software. Kevin Closson granted me special permission to re-distribute SLOB. The SLOB RPM package contains all SLOB files from the tarball unmodified, except that the files are placed in a different directory structure (/usr/share/slob) conforming (more or less) with the Filesystem Hierarchy Standard [1]

Installing

You can install the latest SLOB RPM via the Outrun Extras Repository

yum install http://yum.outrun.nl/outrun-extras.rpm
yum install slob

Using the repository ensures you will get updated packages if you run "yum update" on your system. Alternatively you may install SLOB directly as RPM package from Outrun Standalone.

Documentation:

# After install, a manual page is available: 
man slob

# You can find all SLOB files under /usr/share/slob/:
ls -al /usr/share/slob

# List files in slob RPM: 
rpm -ql slob

Environment setup

SLOB requirements:

  • An Oracle installation on the server where SLOB is installed, with a working SQL*Plus ("sqlplus" command) as a minimum requirement
  • An Oracle database (can be on another server, does not have to be Linux)
  • SYSDBA access to the database

If you use the Outrun distribution to run SLOB, AND your database is on the same server as where you install SLOB, then you don't have to setup database access as this is configured automatically.

If you use only the SLOB rpm on a standalone server, you need to perform the following steps:

  • Modify .bashrc or another profile to set ORACLE_HOME, ORACLE_SID and $PATH so that SQL*Plus works correctly
  • Database on the same host: make sure the SLOB user can connect as sysdba to the database
  • Database on another host: edit $HOME/etc/slob.conf with the correct SQL connect string and SYS password
Database connection

SLOB requires a connection to the Oracle database with SYSDBA privileges. If you run it on Outrun, the newly created "slob" user will be a member of the "dba" and "asmdba" groups, and the login will set the ORACLE_HOME and ORACLE_SID automatically (to the first listed database in /etc/oratab). If that's what you want then you don't need to setup database users or connection.

If you want to connect to another database (can be on another, even non-Linux system) you need to setup the connect string in slob.conf. Modify the following section to achieve this:

# Settings for SQL*Net connectivity:
#ADMIN_SQLNET_SERVICE=slob
#SQLNET_SERVICE_BASE=slob
#SQLNET_SERVICE_MAX=2
#SYSDBA_PASSWD=change_on_install

NOTE that SLOB creates users with names USER0..USERXX. If you run against an existing database, make sure these users don't already exist for other purposes as this may cause serious problems.

Generating SLOB data

Perform the following steps:

  • Create a tablespace for running the tests (traditionally named "IOPS")
    • Outrun distro: run
      @create-tablespace
      from the SQL> prompt, or run
      runsql create-tablespace
      from the Linux command line
    • Plain Linux: create the tablespace, for example using /usr/share/slob/misc/ts.sql
  • Modify $HOME/etc/slob.conf file to specify the SCALE (data size per user)
  • run
    slob-setup <tablespace> <users>

You may find the results of the create phase in $HOME/createlog.

Modifying AWR reporting

By default, Oracle will create AWR reports every 60 minutes. If AWR takes a snapshot in the middle of a SLOB run, you will get incorrect AWR reports as they will only report over part of the SLOB run period. To avoid this, you need to set the AWR interval to a very long time. See Pythian - My First Experience Running SLOB – Don’t Repeat My Errors (AWR)

The RPM provides a SQL script to make life easier:

SYS:DEMO > @set-awrinterval
Current settings:

Interval Retention
   (min)     (min)
-------- ---------
      60      4320

Enter baseline window size  (default	1): 
Enter AWR snapshot interval (default   60): 51120000
Enter AWR retention	    (default 4320): 51120000

Running the SLOB tests

  • Modify the $HOME/etc/slob.conf file to specify default runtime parameters (such as UPDATE_PCT)
  • Run the test using
    slob-run <users>
Specifying runtime parameters
# You can run slob-run with override parameters on the command-line (this is not default SLOB functionality but part of the wrapper script):

# Different slob.conf file: 
slob-run -c etc/slob-custom.conf 2

# slob-run will create a log directory ($HOME/log) under which you will find the results of the benchmark run.
# Different output logfile: 
slob-run -l log-05 2

# Different slob.conf parameters: 
slob-run 2 UPDATE_PCT=80 RUN_TIME=600

# Any combination works but the -l and -c parameters have to go first, the KEY=VALUE parameters last.

# Multiple unattended SLOB runs:
As you can specify the log directory on the command line, you can automate multiple SLOB runs each with their own logging folder, for example:
for u in 0 10 20 30 ; do slob-run -l log-$u 2 UPDATE_PCT=$u ; done

Full table scans

SLOB is a random I/O generator. I was looking for a way to run bandwidth (rather than IOPS/latency) tests on Oracle and wrote a SQL script to do this using SLOB data. The script is included in the RPM package.

Usage:

  • Login to the database as slob user (with SYSDBA credentials)
  • run slob-fulltablescan.sql:
    • Without parameters: you get asked for the number of users. 0 means all users (read entire dataset)
    • With parameters: 0 for all users, any number to limit the scan to X random users
SYS:DEMO > @slob-fulltablescan
Enter max users when asked. 0 = all users
Enter value for 1: 0

Or (quicker)

SYS:DEMO > @slob-fulltablescan 0
Enter max users when asked. 0 = all users

The procedure will show results as in this example:

USER3	     80  MB, Time:   2.69
USER1	     80  MB, Time:   1.28
USER4	     80  MB, Time:   2.58
USER2	     80  MB, Time:    1.4
---Summary---
Users:		  4
Scanned:	320 MB
Scan rate:	 40.20 MB/s
Runtime:	  7.96

IMPORTANT NOTE: slob-fulltablescan is single threaded. This means you will see the bandwidth limitations for one concurrent session. This is in many cases not the maximum bandwidth the system can achieve. For getting higher bandwidth numbers, run more tablescans in parallel. I plan to include that as parameter in a future version of the script.

Cleaning up

If the tests are complete you may remove the slob package using yum remove slob. Logout any session that uses the "slob" user first. Removing the package will remove the SLOB binaries and the slob user and group. It will NOT delete the SLOB home directory (so all the results and logs are still there).

You may also want to drop all SLOB users (@slob-dropusers) and remove the IOPS tablespace.

Additional features

Running under different user than "slob"

Add the user to the "slob" group to get the environment setup after logging in (here we use the "oracle" user):

gpasswd -a oracle slob

SQL Scripts

ls-awrinterval.sql
Shows the current AWR reporting interval settings
set-awrinterval.sql
Sets new AWR interval settings.
slob-dropusers.sql
Drops ALL users named 'USER%' from the database (including their contents). Make sure there are no users with these names used for other purposes as it may result in data loss.
slob-fulltablescan.sql
Runs full tablescans on SLOB data
slob.sql
The default slob.sql script from the SLOB tar package

  1. Not strictly as that would require to have some files in /opt