Other SLOB-related are listed in “My SLOB IO testing index“.
NOTE: This is a copy of my G+ post from yesterday. You can find the original over here. As I am going to continue writing about our ongoing IO testing efforts on 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 analyzing the results. However, there are some things that are worth sharing with others to help out with your efforts. This is why I chose to use G+ to publish some intermediate status update and possibly raise some discussion.
SLOB is The Simple Little Oracle Benchmark from +Kevin Closson.
Our attention to SLOB sparked during +Alex Gorbachev‘s and +Kevin Closson‘s Twitter discussion that more or less could be summarized by the following tweet:
@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 efforts with +Gwen Shapira to leverage +Pythian’s follow-the-sun model and a bit of powerful hardware to find the truth. We based ourselves on+Alex Gorbachev‘s assumption that Orion should show roughly the same numbers than SLOB and +Kevin Closson‘s claim that Orion could easily mislead us by giving way too 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 set a small db_cache). Well, by default, there are 128 schemas created with the same data structures.IMHO: It is a very good idea to test the performance by executing the same actions that most Applications do in a real live but make it so simple that it is application-agnostic.
However, Orion has 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 the results will be at least on one particular system, and if the results are different, why.
—- Phase 1
Env: Oracle Linux Server release 5.8, RDBMS 18.104.22.168 (64 bit)
We created SLOB DB using kindly prepared SLOB/misc/create_database_kit/README and a simple DB creation script. We also 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 were not created.
—- Phase 2
We 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 at the results received and tried to find out if the results were comparable. This is where we got a bit of a surprise: SLOB was able to read 10000 8k blocks per second while Orion indicated 4305 IOPS (8k blocks IOs).
Further analysis revealed that the results has been dramatically impacted by Oracle 22.214.171.124 IO requests enhancement. Instead of making single 8k blocks reads, Oracle put several (~20 on average) IO requests together and sent these packs to storage. Instead of the “db file sequential read” event, we saw “db file parallel read” on the top of Timed Foreground Events list.
Further research led 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 it did direct us to the way to have Oracle avoid using “db file parallel read” and use “db file sequential read” instead.
The solution is to set the following hidden parameters. This makes 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 follows:
db_files = 300
processes = 100
memory_max_target = 2G
Stay tuned! We are working though the testing now and will come with SLOB and Orion results soon.
BTW: Already we see that with fast CPUs (like we are running on that system), the CPU time an instance spends on block processing is within 1% of total time spent. Therefore, I predict that the Orion and SLOB results will be very close to each other.
Yury & Co :)