My First Experience Running SLOB – Status Update 1

Others SLOB related posts list avaiable from “My SLOB IO testing index”

NOTE: This is a copy of my yesterday’s G+ post. You can find the original over here. As I am going to continue writing about our ongoing IO testing efforts under this blog I decided to provide the first post here to give readers a bit more context.

Status – Work in progress. We are still working on testing and analysing the results. However there are some things that is worth to share with others to help out with your efforts. This is why I chosen to use G+ to publish some intermediate status update and possibly raise some discussion.
SLOB is The Simple Little Oracle Benchmark from +Kevin Closson.
http://kevinclosson.wordpress.com/2012/02/06/introducing-slob-the-silly-little-oracle-benchmark/

Our attention to SLOB sparked during +Alex Gorbachev and +Kevin ClossonTwitter discussion that more or less could be summarized by the following tweet:
https://twitter.com/#!/kevinclosson/statuses/200727040192823296

@alexgorbachev says to @kevinclosson: lots of the system IO bound below the CPU level so you should see similar number with Orion or SLOB. why don’t I test?

Amen! This is where we joined our efforts with +Gwen Shapira leveraging +Pythian’s follow the sun model and a bit of powerful hardware to find the truth. Based on+Alex Gorbachev assumption the Orion should show roughly the same numbers than SLOB. +Kevin Closson claims that Orion could easily mislead us by giving way optimistic results.

SLOB is nothing but a ~84MB schema with a table and index created in Oracle database to test LIO (if you set a big db_cache_size) or PIO (if you sent a small db_cache). Well by default there are 128 schemas created with the same data structures.IMHO: This is a very good idea to test the performance by executing the same actions that most of Applications do in a real live, but make it so simple that it is application agnostic.

However Orion have a good advantage over SLOB as it doesn’t require any installation to start testing PIO. The advantage may skew the results in some cases. Our goal is to see how close results will be from one and another tool at least on one particular system. And if the results will be different why.

—- Phase 1
Env: Oracle Linux Server release 5.8, RDBMS 11.2.0.3 (64 bit)
Created SLOB DB using kindly prepared SLOB/misc/create_database_kit/README and a simple DB creation script. Had to fix type “alter tablespace SLOB” to “alter tablespace IOPS” and increase processes from 100 to at least 200 as otherwise some schemas out of 128 haven’t got created.

—- Phase 2
Executed several tests using simple syntax SLOB/runit.sh 0 12 (first parameter writers count, second parameter readers count)

We executed the script with several different parameters starting from 4 and ending with 68.
—- Phase 3
We had a careful look on the results received and tried to find out if results are comparable.
This is where we got a bit of surprise as SLOB was able to read 10000 8k blocks per second while Orion indicated 4305 IOPS (8k bloks IOs).

Further analysis revealed that the results has been dramatically impacted by Oracle 11.2.0.3 IO requests enhancement.
Instead of making single 8k blocks read Oracle put several (~20 on average) IO requests together and sent those packs to storage. Instead of “db file sequential read” event we saw “db file parallel read” on the top of Timed Foreground Events list.

Further research leaded us to the following MOS note:
Some DB File Parallel Read Against An Empty Partition [ID 1367059.1]
It didn’t provide all the details on “db file parallel read” event but pointed us to the way to let Oracle avoid using “db file parallel read” and use “db file sequential read” instead.

The solution is to set the following hidden parameters. This let Oracle forget about possible IO optimizations and keep going with single block random reads.

alter system set “_db_block_prefetch_limit”=0 scope=spfile;
alter system set “_db_block_prefetch_quota”=0 scope=spfile;
alter system set “_db_file_noncontig_mblock_read_count”=0 scope=spfile;

As of now we have modified the default init.ora parameters kindly provided by+Kevin Closson under SLOB/misc/create_database_kit/create.ora as following:

Original Values
===========

db_files = 300
processes = 100
memory_max_target = 2G
sga_target=1G
filesystemio_options=setall

===========

Our Values

===========

*._db_block_prefetch_limit=0
*._db_block_prefetch_quota=0
*._db_file_noncontig_mblock_read_count=0
*.compatible=’11.2.0.2′
*.control_files=’+DATA/SLOB/CONTROLFILE/Current.266.783032279′
*.cpu_count=1
*.db_block_size=8192
*.db_cache_size=12M
*.db_create_file_dest=’+DATA’
*.db_files=300
*.db_name=’SLOB’
*.filesystemio_options=’setall’
*.processes=1000
*.shared_pool_size=600M
*.UNDO_MANAGEMENT=’AUTO’
===========

Stay tuned we are working though the testing now and will come with SLOB and Orion results soon …..

BTW: Already now we see that with fast CPUs (alike we are running on that system) the CPU time an instance spends on blocks processing is within 1% of total time spent. Therefore my prediction goes that the Orion and SLOB results will be very close to each other.

Yury & Co :)