Mauro Pagano's Blog

TEMP I/O and Linux TMPFS

5 Comments

There are already several articles on the web about how to speed up TEMP I/O in an Oracle database (I really like this one from Alex Fatkulin) , specially since even the cool and fancy Engineered Systems still behave pretty much the same when it comes to spilling to disk, so probably this blog post is not going to show anything new but at least it can work as a reminder for me 🙂

I have an artificial SQL (pretty ugly to be honest) that spills to disk, around 665M

select count(distinct c1)
  from (select c1 
          from (select rownum||lpad('x',999,'x') c1 from dual connect by rownum <= 5*1e4), 
               (select rownum n1 from dual connect by rownum <= 1e2))

this is the result from SQL Monitor on 12.1.0.2 (I’m using the Oracle DB Developer VM)Screen Shot 2015-04-28 at 8.49.45 PM

Considering TEMP data doesn’t require any kind of persistency, redundancy and recoverability we can considering creating the TEMP tablespace on a file under tmpfs/ramfs. I like tmpfs a little better just because it has the ability to stop growing in size (even though it might swap hence reducing the benefit of going to memory).
From Linux

mount -o size=1G -t tmpfs none /mnt/mytmpfs

and then from SQL*Plus

SQL> create temporary tablespace TEMP2 tempfile '/mnt/mytmpfs/temp2.f' size 1G;
SQL> alter user mpagano temporary tablespace temp2;

and this is the result for the same SQL when using the new shiny TEMP2 tablespaceScreen Shot 2015-04-28 at 9.06.52 PM

Notice how the same amount of data has been spilled to disk but the elapsed time is half and the “direct path write temp” is basically gone.

DISCLAIMER: There are a few “oddities” in the both reports, the 0 buffer gets and no “direct path read temp” in the first one and the absence of the “direct path write temp” in the second one, my educated guess is the combination of low number of samples (the SQL is fast in both cases so number of samples is low) combined with the low wait time for the events (the VM writes to a file that could benefit from caching) could justify why not all the info are captured.

5 thoughts on “TEMP I/O and Linux TMPFS

  1. Hello.
    Nice trick but you didn’t told us anything about your hardware/ I/O system . I would like to see the results of this test using some good SSDs … It would still worth it to mount the tmpfs ?

    Like

  2. Hi Gabriel,

    All the tests were run using the Oracle DB Developer VM running on a VBox on my iMac, no enterprise class hardware 🙂
    The VM could affect the results too since the virtual disk is basically a file that would benefit from underlying OS caching but even considering it then run with tmpfs was still faster.

    Honestly I would *not* consider using this trick if not as last resort in a case where we are out of tuning options, I’m very much against the “kill it with Iron” approach and always in favor of reducing the load instead of finding a different way to handle the higher load!

    Thanks,
    Mauro

    Liked by 1 person

  3. Remember that linux tmpfs is using the page cash plus swap so you still have physical IO if you make it to big. Also according to the kernel documentation:’If you oversize your tmpfs instances the machine will deadlock since the OOM handler will not be able to free that memory.’
    I would rather get fast SSD’s for production systems.

    Like

    • Absolutely agree on not abusing it, it was more a “remember there is also this option” rather then a recommendation…specially since almost every time tuning the SQL is way more beneficial 🙂

      Like

  4. “alter user mpagano temporary tablespace temp2;”
    AFAIK, when you creeate TEMP tblspc in 11.2 and after, there is no way that ensure that only that user is using it!?? All users would use it.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

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