Silly Little Oracle Benchmark (SLOB)
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
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 <ref>Not strictly as that would require to have some files in /opt</ref>
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.
# 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
4 Environment setup
- 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.
5 Generating SLOB data
Perform the following steps:
- Create a tablespace for running the tests (traditionally named "IOPS")
- Outrun distro: run from the SQL> prompt, or run
@create-tablespacefrom the Linux command line
- Plain Linux: create the tablespace, for example using /usr/share/slob/misc/ts.sql
- Outrun distro: run
- Modify $HOME/etc/slob.conf file to specify the SCALE (data size per user)
slob-setup <tablespace> <users>
You may find the results of the create phase in $HOME/createlog.
6 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
7 Running the SLOB tests
- Modify the $HOME/etc/slob.conf file to specify default runtime parameters (such as UPDATE_PCT)
- Run the test using
- 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
8 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.
- 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
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.
9 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.
10 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
11 SQL Scripts
- Shows the current AWR reporting interval settings
- Sets new AWR interval settings.
- 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.
- Runs full tablescans on SLOB data
- The default slob.sql script from the SLOB tar package