Introducing SLOB – The Silly Little Oracle Benchmark

BLOG UPDATE 2013.05.03:   SLOB 2 is now available.  Please follow:  this link.

BLOG UPDATE 2013.03.21: See flashdba’s SLOB testing how-to/tips page here.

BLOG UPDATE 2013.02.15: See Brian Pardy’s SLOB usage notes  here.

BLOG UPDATE 2012.05.12: For convenience I’ve made the SLOB README available in PDF form here. The README adds more introductory information to what I’ve written in this original blog entry.

BLOG UPDATE 2012.06.29: Karl Arao has been putting SLOB to good use. I urge you to visit:  Karl’s set up “cheat sheet.”

BLOG UPDATE 2012.06.11: A Simple init.ora sufficient for SLOB physical I/O model testing: init.ora

BLOG UPDATE 2012.08.15: I’m behind on folding in improvements to the SLOB Kit. Just no time for that these days. That’s sad. However, Pythian’s Yury has done a really good job at documenting his testing and some kit improvements you should investigate (improvements are just that, not fixes). See: Yury’s Page On the Matter

Background

We’ve all been there.  You’re facing the need to assess Oracle random physical I/O capability on a given platform in preparation for OLTP/ERP style workloads. Perhaps the storage team has assured you of ample bandwidth for both high-throughput and high I/O operations per second (IOPS).  But you want to be sure and measure for yourself so off you go looking for the right test kit.

There is no shortage of transactional benchmark kits such as Hammerora, Dominic Giles’ SwingBench, and cost-options such as Benchmark Factory.  These are all good kits. I’ve used them all more than once over the years. The problem with these kits is they do not fit the need posed in the previous paragraph.  These kits are transactional so the question becomes whether or not you want to prove Oracle scales those applications on your hardware or do you want to test IOPS capacity? You want to test IOPS. So now what?

What About Orion?

The Orion tool has long been a standard for testing Oracle block-sizes I/O via the same I/O libraries linked into the Oracle server.  Orion is a helpful tool, but it can lead to a false sense of security.  Allow me to explain. Orion uses no measurable processor cycles to do its work. It simply shovels I/O requests into the kernel and the kernel (driver) clobbers the same I/O buffers in memory with the I/O (read) requests again and again. Orion does not care about the contents of I/O buffers and therein lies the weakness of Orion.

At one end of the spectrum we have fully transactional application-like test kits (e.g., SwingBench) or low-level I/O generators like Orion. What’s really needed is something right in the middle and I propose that something is SLOB—the Silly Little Oracle Benchmark.

What’s In A Name?

SLOB stands for Silly Little Oracle Benchmark. SLOB, however, is neither a benchmark nor silly. It is rather small and simple though. I need to point out that by force of habit I’ll refer to SLOB with terms like benchmark and workload interchangeably. SLOB aims to fill the gap between Orion and full function transactional benchmarks. SLOB possesses the following characteristics:

  1. SLOB supports testing Oracle logical read (SGA buffer gets) scaling
  2. SLOB supports testing physical random single-block reads (db file sequential read)
  3. SLOB supports testing random single block writes (DBWR flushing capacity)
  4. SLOB supports testing extreme REDO logging I/O
  5. SLOB consists of simple PL/SQL
  6. SLOB is entirely free of all application contention

Yes, SLOB is free of application contention yet it is an SGA-intensive workload kit. You might ask why this is important. If you want to test your I/O subsystem with genuine Oracle SGA-buffered physical I/O it is best to not combine that with application contention.

SLOB is also great for logical read scalability testing which is very important, for one simple reason: It is difficult to scale physical I/O if the platform can’t scale logical I/O. Oracle SGA physical I/O is prefaced by a cache miss and, quite honestly, not all platforms can scale cache misses. Additionally, cache misses cross paths with cache hits. So, it is helpful to use SLOB to test your platform’s ability to scale Oracle Database logical I/O.

What’s In The Kit?

There are no benchmark results included. The kit does, however, include:

  • README files. A lot of README files. I recommend starting with ~/README-FIRST.
  • A simple database creation kit.  SLOB requires very little by way of database resources. I think the best approach to testing SLOB is to use the simple database creation kit under ~/misc/create_database_kit.  The directory contains a README to help you on your way. I generally recommend folks use the simple database creation kit to create a small database because it uses Oracle Managed Files so you simply point it to the ASM diskgroup or file system you want to test. The entire database will need no more than 10 gigabytes.
  • An IPC semaphore based trigger kit.  I don’t really need to point out much about this simple IPC trigger kit other than to draw your attention to the fact that the kit does require permissions to create a semaphore set with a single semaphore. The README-FIRST file details what you need to do to have a functional trigger.
  • The workload scripts. The setup script is aptly named setup.sh and to run the workload you will use runit.sh. These scripts are covered in README-FIRST.
  • Init.ora files. You’ll find test.ora under ~/misc/sample_data. The purpose of this init.ora is to show just how little tweaking Oracle Database requires to scale physical I/O and logical reads. The directory is named sample_data because I originally intended to offer pairs of init.ora and AWR reports so folks could see what different systems I’ve tested, what performance numbers I’ve seen and the recipe I used (the combination of connected pseudo users and init.ora parameters). The name of the directory remains but I pulled the content so as to not excite Oracle’s lawyers.

Models

The size of the SGA buffer pool is the single knob to twist for which workload profile you’ll generate. For instance, if you wish to have nothing but random single block reads you simply run with the smallest db_cache_size your system will allow you to configure (see README-FIRST for more on this matter).  On the other hand, the opposite is what’s needed for logical I/O testing. That is, simply set db_cache_size to about 4GB, perform a warm-up run and from that point on there will be no physical I/O. Drive up the number of connected pseudo users and you’ll observe logical I/O scale up bounded only by how scalable your platform is.  The other models involve writes. If you want to drive a tremendous amount of REDO writes you will again configure a large db_cache_size and execute runit.sh with only write sessions. From that point you can reduce the size of db_cache_size while maintaining the write sessions, which will drive DBWR into a frenzy.

Who Has Used The Kit?

Several folks from the OakTable Network and other friends. Perhaps they’ll chime in on their findings and what they have learned about their platform as a result of testing with SLOB.

What You Should Expect From SLOB

I/O, lots of it!

Where Is The Kit?

NOTICE! SLOB 2 is now available. Please follow this link.

For historical purposes I’ll leave the version uploaded to the OakTable.net website at the following URL. Simply extract the gzipped tar archive into a working directory and see README-FIRST.

http://oaktable.net/articles/slob-silly-little-oracle-benchmark

135 Responses to “Introducing SLOB – The Silly Little Oracle Benchmark”


  1. 1 Olivier February 7, 2012 at 2:11 am

    Thx Kevin :)

    • 2 Allen Herndon February 26, 2014 at 10:42 am

      Kevin,
      I want to add a think time, so I added a variable to slob.conf and slob.sql. It seems I am breaking the wait mechanism, because I get 0 minutes of benchmark with this statement in the slob.sql loop.
      exec dbms_lock.sleep(v_think);
      Thanks,
      Allen

  2. 4 bryan agrenn February 7, 2012 at 3:25 am

    Thanks Kevin for all your work on this Kit. I used this Kit to verify that the performance on my new Intel 8 socket servers was significantly different than expected for LIO’s (I suspected this from some of my own testing). Published benchmarks do little to give you insight on how Oracle will behave on hardware. This kit gives you a lot more information on how an oracle workload acts.

  3. 6 Noons February 7, 2012 at 1:13 pm

    Kevin, once again thanks for the oppportunity to use SLOB. It’s been invaluable given our permanent effort in rationalising, consolidating and virtualizing our db servers.

    Orion is too “raw” to cover all bases and most other benchmarks out there measure only one type of environment and are waaay too complex and lengthy to quickly setup and get results.

    With SLOB I strongly like the simplicity of the single “tweak button”: db cache size. And the results are measured with AWR, which is something we can all relate to and with.

    The results we got with virtualized Aix (vio) went a long way in validating and explaining the advantages and shortcomings we had empirically observed with this architecture. In particular, SLOB was instrumental in proving and confirming some of the observations I had passed on to our IT architects, namely the fact that our db server partitions are inadequately provisioned in terms of I/O pipes.
    We can do North of 200K liops/core, but nowhere near an order of magnitude less with physical I/O. This is now in the process of being addressed in the next hardware refresh cycle. Just like in so many other engineering issues, once we can pin where the problem is, we can fix it.

    I’m now looking forward to re-testing with 11g and Aix 7.1. Mostly to provide myself and management with a degree of confidence that whatever may break after our upcoming upgrade, is not caused by an underlying architectural or setup defect.

    The most striking thing with SLOB is the ease and simplicity of setup, with repeatable and consistent results and easy and simple to control/modify/tweak.

    In fact, I’d like to propose you rename it Simple Little Old Benchmark!
    Given my engineering background, I like simplicity: it works!

    • 7 kevinclosson February 7, 2012 at 1:46 pm

      My pleasure, Noons. And thanks back to you for being one of my two AIX beta testers. I’m so glad to hear it is useful in your environment! By the way, I’m going to start quoting this one:

      “Just like in so many other engineering issues, once we can pin where the problem is, we can fix it.”

      Now *that* is simplicity and truth rolled into one!

  4. 10 Martin Bach February 9, 2012 at 9:35 am

    Hi Kevin,

    thanks a lot for SLOB-I am going to use it extensively. As you say, ORION isn’t the answer. I am currently involved (sadly not leading!) an evaluation of Flash memory in PCIe devices, or shared via FC for a standard hardware setup. We were missing a tool that creates real Oracle workload, instead of iometer and the likes. Now I’m also being able to BAARF and show them log file parallel writes > 10 ms!

    • 11 kevinclosson February 9, 2012 at 9:39 am

      Hi Martin,

      So you are using the “REDO” model of SLOB..that’s a very large SGA db_cache_size and a writer mix (no readers) to dirty up the cache and force large streaming writes to redo logs? If so, you can tweak that even further by going with very large redo logs so DBWR doesn’t get triggered to flush any more than he needs to… this addition to the REDO model enables max+max theoretical REDO writing

  5. 12 Leighton February 9, 2012 at 9:53 am

    Hi Kevin,
    I’ve been testing with SLOB for a couple of days now on our p7 series servers and I must commend you on it’s simplicity of setup. This tool provides a great way to verify the capability of our infrastructure which we never really had any insight for before. Our sys admins have also embraced it with open arms – something that DBAs and sysadmins never do :)
    Anyway, maybe you can follow up with a webinar or do a demo on using SLOB and how to interpret the results?

    Thanks for a great tool!

  6. 17 Frits Hoogland February 10, 2012 at 7:21 am

    I think it’ll make a very interesting read or presentation to compare results of different kit and settings, to point out strengths/weaknesses, bottlenecks and what is responsible for the bottlenecks.

  7. 20 Matt February 16, 2012 at 9:21 am

    Per Kevin’s comment –
    ” Bottom-bin WSM-EP pushing SLOB to over 18K IOPS/core. You should post this on my my SLOB blog post.”

    Dual Quad Core X5600s.
    Logical reads: 154,012.1 542,874.3
    Block changes: 28.0 98.5
    Physical reads: 144,248.5 508,459.0

    Memory Statistics
    ~~~~~~~~~~~~~~~~~ Begin End
    Host Mem (MB): 60,506.9 60,506.9
    SGA use (MB): 668.0 668.0
    PGA use (MB): 275.6 207.0
    % Host Mem used for SGA+PGA: 1.56 1.45

    I will tweak the setup a bit and see what else I might able to get out of it -

    • 21 kevinclosson February 16, 2012 at 9:44 am

      Thanks Matt. BTW, you should post the output of /proc/cpuinfo. Most folks would be as surprised as I was to see just how bottom-bin these CPUs are considering the SLOB PIO they are able to drive.

      • 22 Matt February 20, 2012 at 10:17 am

        processor : 7
        vendor_id : GenuineIntel
        cpu family : 6
        model : 44
        model name : Intel(R) Xeon(R) CPU E5607 @ 2.27GHz
        stepping : 2
        cpu MHz : 1200.000
        cache size : 8192 KB

        • 23 kevinclosson February 20, 2012 at 11:17 am

          Thanks, Matt. That goes a long way to dispel the assertion that massive IOPS over FC requires too much CPU… these are totally bottom-bin Xeons pushing over 18,000 IOPS/core via fibre channel.

          Fact trumps faith…long live SLOB!

  8. 24 Martin Bach February 20, 2012 at 12:26 am

    Hi Kevin,

    another thing I’d like to mention. I have run the LIO test on a T4-3 and get 3,652,597.7 LIOs per second with 0 writes/512 readers. I was wondering if there were any recommendations for driving a platform to the maximum possible? I’m really interested in the LIO test to see which platform scales well.

    This machine is 2s16c128t with 528G RAM. I used an 80G SGA (ASMM-which leaves about 72G for the buffer cache). Can share AWR if needed.

    Martin

    • 25 kevinclosson February 20, 2012 at 8:52 am

      Have you used processor groups to pin it all into a single socket to get your baseline?

      Why don’t we take it up on the Oaktable email channel. We can enlist Glenn Fawcett if the AWR has no clues.

      I will say that for LIO you should not be over-driving the threads to that level. The LIO model of SLOB has no waits so if you are running 4x processes to processor threads you are creating your own (scheduling) waits. This workload can keep cores busy…should be no reason to lean on the threads. What does it look like if you use processor groups to lock the SGA into the memory of socket0 and run all the foregrounds pinned to socket0 at, say, 16 SLOB readers? Than, perhaps, experiment with 32 SLOB readers in that recipe.

  9. 28 Matt February 20, 2012 at 11:38 am

    more efficient code on the way ?

    here is an update with your latest SLOB kit (2-08-2012)

    Logical reads: 164,920.4 656,365.0
    Block changes: 14.6 58.2
    Physical reads: 166,625.0 663,148.8

  10. 30 mdinhinh February 24, 2012 at 8:50 am

    tar: tape blocksize error

    Is this user error on my behalf? Trying to extract for solaris x86_64.

  11. 32 Stojan February 29, 2012 at 9:10 pm

    Hi Kevin,

    Thanks for the tool kit.

    Just a quick question – should we be setting filesystemio_options=directio to bypass file system cache?

    • 33 kevinclosson March 1, 2012 at 7:13 am

      Hi Stojan,

      I’d recommend filesystemio_options=setall. Enjoy!

      • 34 Stojan March 1, 2012 at 3:58 pm

        Thanks.

        Interesting that I get these numbers with setall:

        Physical reads: 30,623.2 440,323.0

        and these numbers with async:

        Physical reads: 132,452.7 4,743,396.0

        running with 4 readers on Red Hat 5.6 connected to a NetApp

        • 35 kevinclosson March 1, 2012 at 4:27 pm

          Not really a mystery Stojan. With filesystemio_options=asynch (no typo) you have files open in buffered mode. So your I/O is simply being satisfied in the operating system page cache. Depending on what the Netapp looks like and how it is connected (FCP, NFS, iSCSI), and how many spindles there are I’d say 30.6K RIOPS from 4 readers isn’t all that bad. I recommend reverting to setall (because we don’t really run with page-cache buffered Oracle datafiles) and ramp up the readers. Questions:

          1. How many CPUs of what type do you have?
          2. What is db_cache_size set to (do SHOW PARAMETER)

          Try setting readers to 2x the number of processor cores and share with us your findings… a more complete snippet of the AWR would be useful too.

      • 36 Stojan March 1, 2012 at 8:08 pm

        Ok, thanks, that makes it clear. Netapp is connected via FC with 40 spindles.

        cpu :

        processor : 7
        vendor_id : GenuineIntel
        cpu family : 6
        model : 23
        model name : Intel(R) Xeon(R) CPU E5450 @ 3.00GHz
        stepping : 10
        cpu MHz : 3000.108
        cache size : 6144 KB

        db_cache_size big integer 32M

        AWR report of test with 16 readers can be viewed at:

        https://docs.google.com/open?id=0B8L5rsECrEJBakNUWTd0Z2VRNXFYdVdOT2xfY1p1UQ

      • 38 Stojan March 1, 2012 at 11:59 pm

        Sorry about that.

        The document should be open to the public (tested from a colleagues pc) I think you need to click on file then download to view it as it hasn’t been converted to google doc format as it messes up the formatting.

  12. 41 Timur Akhmadeev March 1, 2012 at 1:16 am

    Hi Kevin

    posting back from twitter on an AWR report you’ve referenced.

    The report shows ‘db file parallel read’s in Top 5 and it contradicts your description “SLOB supports testing physical random single-block reads (db file sequential read)”. Technically speaking parallel reads are close to random reads except they take into account index structure and utilize async code path. Tanel described the logic behind parallel reads here. So just to be safe you might want to try turning this thing off by a hidden parameter _db_file_noncontig_mblock_read_count=1 (defaults to 11 on a 11.2.0.3).

    IOPS reported in the ‘physical read total IO requests’ reflect real random reads as ‘physical read total multi block’ is zero, so looks like Oracle counts them correctly (didn’t know how it is accounted before reading docs).

    • 42 kevinclosson March 1, 2012 at 7:30 am

      Hi Timur,

      Yes, I need to change that wording. I’ve known all along that both paths were pushed by SLOB (db file parallel read and db file sequential read). The most important thing is that the workload is all single block random as I intended to point out after your tweet, but since you point it out here I’d say all is well.

      I personally see no value in setting that particular hidden parameter. In my mind as long as the I/O is all single block random it’s OK by me.

      • 43 Marcin Przepiorowski March 1, 2012 at 12:12 pm

        Hi Kevin and Timur,

        I’m testing SLOB and I set event 10753 to disable prefetching
        and workload pattern has been change. More readers are required to run same IOPS and every run cycle is much longer.

        regards,
        Marcin

        • 44 kevinclosson March 1, 2012 at 12:54 pm

          Can you share an AWR report with and without your event? I prefer that approach over the hidden parameter. I’d like to see the AWRs to understand what it does to the workload. Thanks for giving SLOB a try, Marcin!

        • 45 kevinclosson March 1, 2012 at 6:34 pm

          I just tried this event in my setup:

          [oracle@tcag1 SLOB]$ grep ‘Physical reads:’ *awr*even*
          awr.txt-with-10753-event: Physical reads: 407,166.4 1,640,695.6
          awr.txt-without-10753-event: Physical reads: 393,802.3 1,637,358.4

          This is a 2s12c24t server than can handle upwards of 400,000 8K random read IOPS from a XFS file system (filesystemio_options=setall so O_DIRECT) residing in a md(4) SW RAID volume.

          The difference between the default behavior and the behavior with the 10753 setting is only ~3%. What sort of IOPS difference is it in your testing?

  13. 46 Marcin Przepiorowski March 2, 2012 at 12:46 am

    Hi Kevin,

    I found a difference for small number of readers – buffer cache set to 40 MB, 16 readers

    Default settings

    testdb-ora$ grep “Physical reads” 40/awr_16r_1c.txt
    Physical reads: 16,116.9 16,574.7
    testdb-ora$ grep “Elapsed:” 40/awr_16r_1c.txt
    Elapsed: 21.01 (mins)

    Event 10753 set

    testdb-ora$ grep “Physical reads” 40_noprefetch/awr_16r_1c.txt
    Physical reads: 6,120.1 6,430.1
    testdb-ora$ grep “Elapsed:” 40_noprefetch/awr_16r_1c.txt
    Elapsed: 55.14 (mins)

    Strange thing is that time difference disappear for 64 readers

    Default settings

    testdb-ora$ grep “Physical reads” 40/awr_64r_1c.txt
    Physical reads: 14,854.8 15,224.0
    testdb-ora$ grep “Elapsed:” 40/awr_64r_1c.txt
    Elapsed: 40.81 (mins)

    Event 10753 set

    testdb-ora$ grep “Physical reads” 40_noprefetch/awr_64r_1c.txt
    Physical reads: 13,277.6 13,643.8
    testdb-ora$ grep “Elapsed:” 40_noprefetch/awr_64r_1c.txt
    Elapsed: 39.78 (mins)

    My system is running RedHat 5.3 2s6c12t on ext3 filesystem,
    Right now I’m not sure how to explain this behavior and I will rerun all tests with more monitoring in place. (iostat / sar)

    regards,
    Marcin

    • 47 kevinclosson March 2, 2012 at 8:52 am

      I think you mean 2s12s24t.

      At 16 readers there is a not much concurrent I/O outstanding. Disabling non-contiguous multi-block reads just means that now this low-concurrency workload is low-concurrency *and* fully blocking. I think what your test proves to us is that your storage is bottlenecked in the 14K IOPS range and that 64 readers is a sufficient concurrent workload to drive that storage to the limit.

      By the way, be mindful that Ext3 does not support concurrent writes (even in O_DIRECT) so when you switch to a writer test you should see a massive penalty…unless, that is, your tablespace consists of lots and lots of really small files.

      Keep us posted.

      I sure wish we’d build a repository of AWRs that accompany these threads :-(

      An interesting thing for you to experiment with might to boot the instance with a small (40MB) recycle pool because the tables are created with this association. Running in this manner will allow for the index blocks to remain in the SGA buffer pool. As it stands now you are suffering PIO for both index and table. Please give it a try and tell us what you find out.

      Thanks for participating, Marcin.

      • 48 Marcin Przepiorowski March 6, 2012 at 2:25 am

        Hi Kevin,

        I will try recycle pool later and will let you know.
        Now I was trying to stress my host with LIO and I have one question related to runit.sh. You are capturing first AWR snapshot before you setup all session and then there is a ./trigger which start all executions and finish with next AWR snapshot. But between sqlplus loop and ./trigger – there is a sleep 10 (I think to allow Oracle to start all session) but this 10 sec is also included in AWR report. It is not a problem for PIO as it run at my host for 10 min and additional 10 sec for IO/s calculation didn’t skew result much. But when I run LIO test it start confusing me. I have added one line of code displaying logical reads and sysdate before ./trigger and after wait command. Could you please help me understand following numbers:

        SEC NAME VALUE
        —– —————————————————————- ———-
        35887 session logical reads 93202884

        Tm 4

        SEC NAME VALUE
        —– —————————————————————- ———-
        35891 session logical reads 124096780

        So running time was 4 sec (35891 – 35887 ) and same value has been reported in Tm. Number of logical reads is 30 893 896 (124096780 – 93202884). Now time for AWR report numbers:

        Statistic Total per Second per Trans
        session logical reads 30,899,876 2,221,414.5 401,297.1

        So total value is very close to my calculation by LIO per sec is different.
        AWR is reporting 2,221,414 which is mean that 30,899,876 LIO has been done in 13.91 sec (30,899,876 / 2,221,414.5) which is almost same you can find in header of AWR report

        Begin Snap: 326 06-Mar-12 10:08:17 36 .7
        End Snap: 327 06-Mar-12 10:08:31 36 .7

        But according to output from v$sysstat similar number of LIO has been done in 4 sec which is around 7 723 474 LIO / sec.

        When I moved temporary AWR snapshot from line 16 to line 34 (all sessions are still waiting for trigger to run) AWR report looks closer to my calculations (30893894 LIO in 4 sec) :

        Begin Snap: 328 06-Mar-12 10:20:32 58 1.3
        End Snap: 329 06-Mar-12 10:20:36 34 .7

        Statistic Total per Second per Trans
        session logical reads 30,898,215 6,940,299.9 686,627.0

        regards,
        Marcin

        • 49 kevinclosson March 6, 2012 at 11:07 am

          Marcin,

          Good find. You are right. The “before” awr snap should not be taken until after the sessions are connected and the 10 seconds of “dust settling.” I’ll post up a fix.

  14. 50 Sid March 12, 2012 at 4:24 am

    Hi Kevin,

    Thanks for sharing the SLOB. At the end of the SLOB/misc/create_database_kit/cr_db.sql, the alter table is actually for the tablespace IOPS?

    SQL> — End of pupbld.sql
    SQL>
    SQL> create BIGFILE tablespace IOPS datafile size 1G NOLOGGING ONLINE
    2 PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO ;

    Tablespace created.

    SQL>
    SQL> alter tablespace SLOB autoextend on next 200m maxsize unlimited;
    alter tablespace SLOB autoextend on next 200m maxsize unlimited
    *
    ERROR at line 1:
    ORA-00959: tablespace ‘SLOB’ does not exist

  15. 52 bryangrenn March 20, 2012 at 6:01 pm

    Here are my results. I will post in 2 different notes because I tested 2 different pieces of equipment
    First one is an AMD 6276 chip. The server is a 2 socket, 16 core server with 128g of memory.

    Load Profile Per Second Per Transaction Per Exec Per Call
    ~~~~~~~~~~~~ ————— ————— ———- ———-
    DB Time(s): 37.9 46.6 0.00 5.96
    DB CPU(s): 30.3 37.2 0.00 4.76
    Redo size: 15,791.3 19,408.2
    Logical reads: 10,119,426.4 12,437,215.0

    The AWR report can be found here.
    http://dl.dropbox.com/u/23998484/awr_6276_40.txt

    These numbers are really impressive for this hardware. Even better than most of the Intel chips that proceeded it.

  16. 53 bryangrenn March 20, 2012 at 6:05 pm

    Here are my results.
    Second one is an Intel 2870. The server is a 2 socket, 10 core hyperthreading server with 128g of memory.

    ./runit.sh 0 40

    Load Profile Per Second Per Transaction Per Exec Per Call
    ~~~~~~~~~~~~ ————— ————— ———- ———-
    DB Time(s): 37.2 43.4 0.00 5.29
    DB CPU(s): 36.4 42.4 0.00 5.17
    Redo size: 16,151.6 18,810.7
    Logical reads: 12,927,371.4 15,055,624.9

    The AWR report can be found here.
    http://dl.dropbox.com/u/23998484/awr_2870_40.txt
    These numbers bested the numbers in my exadata, and bested the AMD numbers.

    There are certainly impressive. This new Intel E7 4S chip Rocks !!

    • 54 kevinclosson March 21, 2012 at 12:35 pm

      Bryan, I’m confused. You say “The server is 2 socket” but later say “E7 4S chip rocks”

      Can you confirm please the following:

      1. Exactly how many E7 sockets
      2. The SLOB runit.sh args in both cases.

      Thanks!

      • 55 bryangrenn March 21, 2012 at 1:36 pm

        Sorry, both servers are 2 socket.

        The AMD tests were 32 processes (0 32), the Intel tests were 40 (0 40)

        I actually got a little higher on intel with 43 (0 43), 13.2 million LIO’s

        • 56 kevinclosson March 21, 2012 at 2:03 pm

          Thanks for the clarification, Bryan. Really cool numbers! I’m not surprised adding on just a few more than core-count for Intel with SMT helps. Would be interested to see where the SMT threads degrade the LIOPs rate. I certainly wouldn’t expect 80 SLOB sessions (in the LIO model) to produce more LIOPS than 40 but I can’t test that. There is likely a break point. Probably at about 56 sessions (70%…queueing theory).

  17. 57 bryangrenn March 21, 2012 at 4:36 pm

    I’ll try to get to it this week, along with SLB numbers. Are you at all interested in any PIO numbers ? the 2 were comparable with the EMC DMX3 I have hooked up.. That’s the best my storage folks could give me for this little test.
    I also have a new storage array coming in next week with a lot of SSD to benchmark. Life is good :) I will try to hook it up my little server farm, but the servers are due back to the vendor in a couple of weeks.

    • 58 kevinclosson March 21, 2012 at 4:41 pm

      I’d love any PIO numbers… all good!

    • 59 Kim Pearson April 30, 2012 at 8:34 am

      Let me know if you want a SERIOUS Tier 0 disk array to try out. We leave Flash based SSD’s in the dust…literally. I’m just starting my Oracle based benchmark testing, but everything I’ve done up to now has convinced me that the marketing claims around the world records Kove holds are real….we shall see shortly!

  18. 60 Kim Pearson April 30, 2012 at 8:31 am

    I am going to use this to test an XPD L2 Kove disk array. The connection is Fiber Channel, so I’m not going to see 5M IOPS like I could with Infiniband, but I should put just about anything else to shame with this configuration!

    If I can get this working, I’ll move up to Infiniband. Question – can you use RAC with SLOB? I just don’t think I’m going to generate enough I/O with a single instance.

    I have 48 cores and 500 GB of RAM in the server. 8 FC connections into the switch.

    Thanks,

    Kim

    • 61 kevinclosson April 30, 2012 at 10:37 am

      @Kim Pearson : You can use SLOB with RAC. You’ll have to modify runit.sh so that sqlplus connects with a TNS connect string. You’ll have to work out how many users to connect to each instance and change the connect string at each count. Not too difficult. I’d cook it into the kit myself but I don’t really touch RAC that much any more. If you get a model working feel free to paste in your runit.sh

  19. 63 Martin Berger (@martinberx) June 10, 2012 at 11:26 am

    thank you for this tool and it’s method.
    I tried to make myself comfortable with SLOB for LIO testing. But I’m somehow confused by my first findings.
    According to https://kevinclosson.wordpress.com/2012/05/13/quick-reference-readme-file-for-slob-the-silly-little-oracle-benchmark/
    it’s rare to run with more readers than CPUs.
    My test node has 24/12/2 (CPU/Core/Socket) X5680 @ 3.33GHz, but the graph (X:readers, Y:TM) scales up to about 120:
    https://docs.google.com/spreadsheet/oimg?key=0Aqc1gsEhfcN1dFlLVldBSHhhUHZzc0gyMVJRd0tQN1E&oid=1&zx=r22eyqhu58ln
    Do I have some errors in my setup?
    Martin

    • 64 kevinclosson June 11, 2012 at 10:06 am

      Do you have AWRs? Do they show any serious latch contention? I can envision piling up more reader.sql sessions may keep more LIOPS going if there is some sort of chaotic contention on just a few hash chain latches others not hashing to those buckets can continue to push LIOPS? Just a hypothesis because I have no more to go on that that.

      In general a state of health is 1 reader.sql session per core or at most 2 per core (SMT) .. piling up beyond that likely indicates a problem. Look at what reader.sql does and the schema and try to finger any reason these processes would not run full bore. If running full bore with more sessions than cores means waits for CPU.

  20. 68 Martin Berger (@martinberx) June 11, 2012 at 11:42 pm

    I have HP DL 380 G7 for sandbox, test and prod.
    In this case: Host CPU (CPUs: 24 Cores: 12 Sockets: 2) – Intel(R) Xeon(R) CPU X5680 @ 3.33GHz

  21. 69 ujang June 21, 2012 at 8:47 pm

    hi kevin,

    to test multiblock (storage throughput) can I just simply drop all the indexes?
    I will test the machine for data warehouse.

    so the scenario is 20% read 80% write (batch, etl)
    100% read (reporting)
    most of the workload are join and scan huge data.

    • 70 Sergey Kalmykov October 30, 2012 at 9:31 am

      Hi Kevin,

      I’m trying to download SLOB from http://oaktable.net/articles/slob-silly-little-oracle-benchmark. But it seems there is only an update.

      Could you (or anyone else following this blog) please help me to find it?

      /Sergey

      • 71 kevinclosson October 30, 2012 at 10:37 am

        Hi Sergey, This is the entire kit. It is really small (on purpose)… enjoy. Don’t miss this additional info by the way:

        https://kevinclosson.wordpress.com/2012/07/01/putting-slob-the-silly-little-oracle-benchmark-to-use-for-knowledge-sake/

        • 72 Sergey Kalmykov November 3, 2012 at 5:24 am

          hi Kevin,

          I had to run zcat on Linux and then transfer tar to Solaris. It fixed an issue I had.

          There are my results.
          ./runit.sh 0 8

          Per Second
          Logical reads: 1,109.86
          Physical reads: 1,067.67

          Top 5 Timed Events Avg %Total
          ~~~~~~~~~~~~~~~~~~ wait Call
          Event Waits Time (s) (ms) Time Wait Class
          ------------------------------ ------------ ----------- ------ ------ ----------
          db file sequential read 1,373,047 9,111 7 99.6 User I/O

          Statistic Total per Second per Trans
          -------------------------------- ------------------ -------------- -------------
          physical read IO requests 1,372,362 1,067.7 171,545.3
          physical read bytes 11,242,389,504 8,746,382.0 1.40530E+09
          physical read total IO requests 1,372,830 1,068.0 171,603.8
          physical read total bytes 11,250,089,984 8,752,372.8 1.40626E+09

          Looks nice for beginning.

          I was instrumented by loadprof.sql as Karl Arao advises. I noticed that a pair physical reads per second/logical reads per second was floating during the test from 500 to 1400. Is that expected? Or should I dig my SAN setup for an answer. I’m not a Storage admin. But I’m pretty sure that my LUN doesn’t own its physical disks exclusively.

          ok. A general question. I\m trying to investigate if SLOB is a good tool for me. Now we use UFS for our Solaris boxes. It is simple and pretty fast (and old as dinosaurs). But it isn’t flexible. That’s bad now when we are playing virtualization/consolidation games. I’m thinking about ASM or ZFS. There is one more factor adding complexity. We use EMC VNX with all these super smart Fast Cache, Virtual Pool things. Is SLOB good enough to be a proof of concept tool? Or should we spent time (which is money) creating test machines and reproducing production workload on them?

          Thank you!
          Sergey

          • 73 kevinclosson November 3, 2012 at 9:34 am

            Hi Sergey,

            Only you can decide is SLOB is a useful tool. If you want to drive storage with an Oracle Instance I/O workload then, yes, I personally think it is about the best out there because it is free of any transactional overhead whilst performing true, Instance I/O.

            As for the little snippet of day you’ve posted it looks to me like one of two things is true, either a) your random reads are touching spinning disk with end-to-end wait time of 6.6ms or b) the physical I/O is much faster than that and there is scheduling time being added to the end-to-end wait time. How many processor cores are there dealing with this 8 session SLOB test?

            Can you send the entire AWR? I always like seeing these.

  22. 75 Mark M November 20, 2012 at 2:14 pm

    Could not run it because do not have AWR licensed.
    It would be nice if SLOB use Statspack. Is there any reason that SLOB uses AWR not Statspack?

  23. 76 Mark M November 21, 2012 at 10:25 am

    I’ve added runsp.sh and folder statspack/ with set of scripts that use Statspack. Testing now. Let me know if you would like me to send you these files.

  24. 78 netofrombrazil January 20, 2013 at 3:37 pm

    Hi Kevin,

    This is neto from Brazil

    How are you?

    I did a small modification on reader.sql

    SELECT /*+ FULL(cf1)*/ COUNT(c2) into x FROM cf1 where custid > v_r – 256 AND custid < v_r;

    When I run SLOB, pretty much we are doing a parallel full table scan.

    See below:

    Load Profile Per Second Per Transaction Per Exec Per Call
    ~~~~~~~~~~~~ ————— ————— ———- ———-
    DB Time(s): 253.4 1,362.3 40.39 2,140.78
    DB CPU(s): 5.1 27.5 0.81 43.16
    Redo size: 18,186.2 97,785.5
    Logical reads: 329,478.3 1,771,574.8
    Block changes: 36.1 194.1
    Physical reads: 329,390.4 1,771,102.5

    Event Waits Time(s) (ms) time Wait Class
    —————————— ———— ———– —— —— ———-
    direct path read 1,399,875 14,918 5 99.6 User I/O
    DB CPU 302 2.0
    control file sequential read 112 1 10 .0 System I/O
    db file sequential read 44 1 18 .0 User I/O
    db file scattered read 29 0 15 .0 User I/O

    My question is:

    Why do I have the same number of physical reads and logical reads?

    Thank you very much my friend

    All the best

    neto

    • 79 kevinclosson January 20, 2013 at 5:18 pm

      Neto my friend, thanks for stopping by and posting. I’ll take a look at this.

    • 80 kevinclosson January 23, 2013 at 10:08 am

      @Neto: Because you are scanning via the conventional path (SGA buffered)

      • 81 neto from Brazil January 23, 2013 at 10:56 am

        Please explain :-)

        • 82 kevinclosson January 24, 2013 at 10:17 am

          blocks that pass through the SGA are prefaced by a logical read

          • 83 Pavol Babel February 25, 2014 at 4:32 am

            I’m sorry to say, but that’s not correct Kevin :) As you can see Neto’s reads were direct path reads, so they bypassed buffer cache.
            However, every physical read is always counted as consistent get (LIO), which is a little bit confusing. If you want find out exact number of LIO counted by direct reads, you should check “consistent gets direct” statistic.

            • 84 kevinclosson February 26, 2014 at 12:55 pm

              @Pavol I said “blocks that pass through the SGA are prefaced by a logical read” so you countered with “…direct path reads, so they bypassed the buffer cache” What is your point?

              • 85 Pavol Babel February 26, 2014 at 1:12 pm

                Kevin,

                Well, to be more precise you stated “Because you are scanning via the conventional path (SGA Buffered)”, whic is not correct since Neto had been obviously performing direct reads (99.6% waits were direct path reads). Of course I’m not going to argue with “blocks that pass through SGA…”

                • 86 kevinclosson February 26, 2014 at 1:54 pm

                  @Pavol ah, ok. I think with that reply you’ve clarified for other readers…unless you think I need to edit something? I don’t want to just dismiss your point. Let me know.

                • 87 Pavol Babel February 26, 2014 at 3:12 pm

                  It was my fault. So would you mind edit my original post and move it under your from “January 23, 2013 at 10:08 am”. Or maybe I can do it my self with new reply and you can delete the other (messing) stuff.

      • 88 Pavol Babel February 26, 2014 at 3:14 pm

        Well it doesn’t seem to be correct. As you can see Neto’s reads were direct path reads, so they bypassed buffer cache.
        Every physical read is always counted as consistent get (LIO), which is a little bit confusing. If you want find out exact number of LIO counted by direct reads, you should check “consistent gets direct” statistic. That means logical read number from AWR summary will be always greater then physical reads.

  25. 90 thomas traxel March 26, 2013 at 6:07 am

    Hi Kevin

    I came accross the following locking issue in my PIO test on version 11.2.0.3

    Connection count = 99 (./runit 0 99) then everything is fine

    select address, count(*) from V$SQL_SHARED_CURSOR where sql_id = ’68vu5q46nu22s’ group by address;

    ADDRESS CHILD COUNT
    0000000199EA0D90 99

    Top wait event: cell single block physical read

    When the connection count exceeds 100 (./runit 0 128)

    Top wait event: library cache lock

    select address, count(*) from V$SQL_SHARED_CURSOR where sql_id = ’68vu5q46nu22s’ group by address;

    ADDRESS CHILD COUNT
    00000001987EE1B0 100
    000000019CA92FC0 100
    0000000196ADFC68 100
    000000019A96B8F0 100
    000000019ADBE938 100
    000000019BF95FD0 100
    000000019D8870C0 42
    ……..

    The reason is due to new 11.2.0.3 parameter (_cursor_obsolete_threshold default 100) described in ID 296377.1

    You must set this parameter to a value greater then concurrent connections.

  26. 92 Shanker (@slupid) June 26, 2013 at 8:27 pm

    Hi Kevin ,

    I’m looking for @reader @writer sql scripts . Possible to post the syntax please .

    Thanks
    Shanker

  27. 94 Pascal Phillip February 19, 2014 at 8:14 am

    Hi Kevin,

    Thanks for a great and useful IO testing tool.

    I tested it on a single instance DB on 3PAR storage and i got unbelievable good numbers.

    Here is some snippets from one awr report with 80 readers and o writers.

    According to this AWR:

    IOPS = 586,244.5

    GB/s = 4.80288472E+09 = 4.5 GB/s

    Cache Sizes Begin End
    ~~~~~~~~~~~ ———- ———-
    Buffer Cache: 5,056M 5,056M Std Block Size: 8K
    Shared Pool Size: 5,024M 5,024M Log Buffer: 16,528K
    ……

    Statistic Total per Second per Trans
    ——————————– —————— ————– ————-
    …..
    parse count (total) 1,720 5.7 156.4
    parse time cpu 42 0.1 3.8
    parse time elapsed 55 0.2 5.0
    physical read IO requests 177,143,749 586,244.5 16,103,977.2
    physical read bytes 1,451,262,926,848 4.80285050E+09 1.3193299E+11
    physical read total IO requests 177,144,393 586,246.7 16,104,035.7
    physical read total bytes 1,451,273,266,176 4.80288472E+09 1.3193393E+11

    Regards,

    Pascal

    • 95 kevinclosson February 22, 2014 at 9:50 am

      @Pascal

      Thanks for sharing your SLOB2 experiences. I presume this is either 4-node RAC of 2S servers or an 8S Proliant? Am I guess wrong? Please post the slob.conf for readers to learn the relationship between SLOB sessions * slob.conf->SCALE. This is the Active Data Set.

  28. 96 Pavol Babel February 25, 2014 at 4:16 am

    Have also some interesting numbers with IBM 730 Power 7+ 4.2GHz 8 cores (LPAR with 8 cores) with Flash Systems 820 4x 8 Gbit FC

    1) disabled pre-feteching (just to make test consistent across platforms)
    2) AWR Physical reads per second
    Physical reads: 282,327.2
    3) latency
    storage level (at fibres): 150us
    iostat db server: 300us
    db level: 400us

    it is over 35 000 IOPS per CPU CORE.

    It seems Exadata X3-2 1/4 rack with 3 storages won’t catch 280k IOPS with such great latencies


  1. 1 Just read – SLOB – The Silly Little Oracle Benchmark | Oracle Administrators Blog - by Aman Sood Trackback on February 10, 2012 at 9:10 pm
  2. 2 SLOB | Oracle Administrators Blog - by Aman Sood Trackback on February 14, 2012 at 6:21 pm
  3. 3 Measuring Storage Performance For Oracle Systems | DiABOLiKO NETWORK Trackback on April 30, 2012 at 5:55 am
  4. 4 SLOB testing on Violin and Exadata « flashdba Trackback on May 8, 2012 at 10:11 am
  5. 5 Quick Reference README File For SLOB – The Silly Little Oracle Benchmark « Kevin Closson's Blog: Platforms, Databases and Storage Trackback on May 13, 2012 at 9:10 pm
  6. 6 My First Experience Running SLOB – Status Update 1 | The Pythian Blog Trackback on May 14, 2012 at 10:08 pm
  7. 7 SLOB Is Not An Unrealistic Platform Performance Measurement Tool – Part I. Let’s See If That Matters…To Anyone. « Kevin Closson's Blog: Platforms, Databases and Storage Trackback on May 16, 2012 at 8:57 pm
  8. 8 Insider’s Guide to ODA Performance | The Pythian Blog Trackback on May 18, 2012 at 10:23 am
  9. 9 My SLOB IO testing index | The Pythian Blog Trackback on May 20, 2012 at 5:36 pm
  10. 10 SLOB on Violin 3000 Series with Infiniband « flashdba Trackback on May 30, 2012 at 3:59 am
  11. 11 My First Experience Running SLOB – Final results – ORION vs SLOB – 2:0 | The Pythian Blog Trackback on June 3, 2012 at 1:37 am
  12. 12 My First Experience Running SLOB – ORION vs SLOB – 2:0 | The Pythian Blog Trackback on June 3, 2012 at 4:53 am
  13. 13 SLOB Is Not An Unrealistic Platform Performance Measurement Tool – Part II. If It’s Different It’s Not The Same. « Kevin Closson's Blog: Platforms, Databases and Storage Trackback on June 3, 2012 at 8:52 pm
  14. 14 Simple SLOB Init.ora Parameter File For Read IOPS Testing « Kevin Closson's Blog: Platforms, Databases and Storage Trackback on June 10, 2012 at 8:52 pm
  15. 15 Introducing A LinkedIn Group For SLOB Users « Kevin Closson's Blog: Platforms, Databases and Storage Trackback on June 12, 2012 at 9:08 pm
  16. 16 Introducing A LinkedIn Group For SLOB Users | The Pythian Blog Trackback on June 13, 2012 at 3:10 am
  17. 17 Putting SLOB (The Silly Little Oracle Benchmark) To Use For Knowledge Sake! « Kevin Closson's Blog: Platforms, Databases and Storage Trackback on July 2, 2012 at 9:32 am
  18. 18 SLOB on steroids – Part 1 | The Pythian Blog Trackback on July 8, 2012 at 2:14 am
  19. 19 DB Optimizer » NFS versus dNFS Trackback on July 16, 2012 at 10:30 am
  20. 20 Announcing The Oracle Real World Conference. Attending Oracle OpenWorld 2012? Great, It Happens To Coincide With This Great Event! « Kevin Closson's Blog: Platforms, Databases and Storage Trackback on September 13, 2012 at 10:27 am
  21. 21 SLOB using Violin 6616 on Fujitsu Servers « flashdba Trackback on October 30, 2012 at 2:29 am
  22. 22 Are there performance penalties running Oracle ASM on NFS? | The Pythian Blog Trackback on November 6, 2012 at 6:38 pm
  23. 23 ASM vs ZFS performance | portrix systems Trackback on January 6, 2013 at 2:32 pm
  24. 24 Benchmarking filesystem performance for Oracle using SLOB | Pardy DBA Trackback on February 1, 2013 at 10:20 am
  25. 25 Benchmarking filesystem performance for Oracle using SLOB (Part 2) – Results! | Pardy DBA Trackback on February 8, 2013 at 6:56 am
  26. 26 Collection of links, tips and tools for running SLOB | Pardy DBA Trackback on February 15, 2013 at 5:53 pm
  27. 27 ASM Preferred Read: Collect performance metrics « bdt's oracle blog Trackback on February 18, 2013 at 12:04 am
  28. 28 SLOB.R v0.6: An R script for analyzing SLOB results from repeated runs | Pardy DBA Trackback on February 20, 2013 at 8:33 am
  29. 29 ZFS and Database fragmentation | Dirty Cache Trackback on February 26, 2013 at 6:53 am
  30. 30 Using SLOB to Test Physical I/O | flashdba Trackback on March 19, 2013 at 9:18 am
  31. 31 RMOUG and Hotsos | Frits Hoogland Weblog Trackback on April 5, 2013 at 3:39 am
  32. 32 On Benchmarking Database Storage | Pure Storage Blog Trackback on April 30, 2013 at 2:07 pm
  33. 33 SLOB 2 — A Significant Update. Links Are Here. | Kevin Closson's Blog: Platforms, Databases and Storage Trackback on May 3, 2013 at 12:23 pm
  34. 34 Deploying Oracle Database on EXT File System with iSCSI SAN | When in doubt with Oracle?When in doubt with Oracle? Trackback on August 21, 2013 at 4:02 pm
  35. 35 Kyle Hailey » NFS versus dNFS Trackback on October 30, 2013 at 9:39 am
  36. 36 Subway Theme » Measuring Storage Performance For Oracle Systems Trackback on December 21, 2013 at 12:10 am
  37. 37 » IO Testing and Benchmarking Trackback on March 12, 2014 at 5:24 am
  38. 38 New Balance Hardloopschoenen Trackback on March 31, 2014 at 7:53 pm

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




EMC Employee Disclaimer

The opinions and interests expressed on EMC employee blogs are the employees' own and do not necessarily represent EMC's positions, strategies or views. EMC makes no representation or warranties about employee blogs or the accuracy or reliability of such blogs. When you access employee blogs, even though they may contain the EMC logo and content regarding EMC products and services, employee blogs are independent of EMC and EMC does not control their content or operation. In addition, a link to a blog does not mean that EMC endorses that blog or has responsibility for its content or use.

This disclaimer was put into place on March 23, 2011.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,719 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

Follow

Get every new post delivered to your Inbox.

Join 1,719 other followers

%d bloggers like this: