DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Filling datafiles 'round robin' on RAID5

  1. #1
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    Filling datafiles 'round robin' on RAID5

    Hello,

    All my customers use Oracle8i-db's on a RAID5 environment.

    While adding multiple datafiles to a particular tablespace, I witnessed the following: the new datafiles were - almost - simultaneously filled with extents. Oracle calls this write process: 'round robin'. Documentation on this subject is scarse.
    (I found one link in the manual: http://download-west.oracle.com/docs...0_io.htm#10927, but this documents the buffer pool)

    My questions:
    1. Does this feature have a positive effect on performance while running on a RAID5 system? And: is it better to build up a tablespace out of multiple datafiles in stead of creating a tablespace with one big datafile?

    2.The teacher on the Oracle9i course 'Performance Tuning' said that RAID5 isn't perfect for OLTP. All my customers run OLTP-db's on RAID5 (and have some performance issues).
    Does annyone have a guideline for RAID5 in combination with Oracle8i?

    I'm a trainee, so any extra documentation on ths subject is very welcome...

    Thanks in advance,
    Erik

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Filling datafiles 'round robin' on RAID5

    Originally posted by efrijters

    My questions:
    1. Does this feature have a positive effect on performance while running on a RAID5 system? And: is it better to build up a tablespace out of multiple datafiles in stead of creating a tablespace with one big datafile?
    Kinda depends on how your RAID 5 is setup. If it is setup as one big filesystem on one controller, then no there is no performance advantage of having one big data file over multiple data files. There's an administrative advantage in that smaller files are easier to move around and distribute if you do have multiple filesystems, but there is little (if any) performance advantage.

    If you have multiple RAID 5 filesystems then sure, it helps performance. Your slow RAID 5 writes get spread out over two controllers and two sets of physical disks instead of just one.


    2.The teacher on the Oracle9i course 'Performance Tuning' said that RAID5 isn't perfect for OLTP. All my customers run OLTP-db's on RAID5 (and have some performance issues).
    Does annyone have a guideline for RAID5 in combination with Oracle8i?
    Is RAID 5 ideal? No. However, I'd rather have RAID 5 than no RAID at all. There are lots of documents on Metalink about RAID and its affects on Oracle. The underlying performance issue with RAID 5 and OLTP systems is that a write to a RAID 5 device require extra reads to calculate parity.

    You may also want to check out:
    http://www.bitpipe.com/data/detail?i...S&x=1704389170
    http://www.bitpipe.com/data/detail?i...S&x=1704389170
    Jeff Hunter

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Use RAID5 for read intensive applications like LDAP etc.
    For OLTP use 1+0

    search the forum, this topic has been discussed many times.

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    http://otn.oracle.com/deploy/perform...orage_conf.pdf recommends Stripe And Mirror Everything (S.A.M.E.) for all types of db - of course Oracle always assumes you can afford an infinite number of disks!

    There are a few "new" ideas in the paper - e.g. NOT putting redo-logs on separate drives (if you follow S.A.M.E.). Any opinions?

  5. #5
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    http://www.quest.com/whitepapers/Pro...ble_Perf-2.pdf discusses the pros & cons of RAID 5, SAME and many other Storage Management issues. It's a good discussion of the different options and how they differ.

    Jodie

  6. #6
    Join Date
    Jan 2003
    Location
    Denver
    Posts
    152
    You lay down datafiles within a tablespace across each controller path.

    This is how you speed up I/O. I have verified that Oracle will always move round robin from datafile to datafile within the tablespace so that if you are putting them on separate controllers you are gaining speed.

    My best load rate with SQL*LOADER is 1/4 billion rows per hour or 60 million per channel.

    At the next level you have to actually build out various tablespaces for various purposes to see which ones perform better.

    I proved that for my 15 Billion row database heavily using bitmap indexes my baseline queries ran faster when I mapped every object over every channel and every spinning disk. Heresy right? Indexes must not be on the same path as tables right ? Not so in every case, only testing will determine your optimal layout.

    I finally got a welcome endorsement of that belief from Suns High performance research department documented in a book recommended on this site. I will post the the text and page number tomorrow as I am likely to be blasted for even suggesting this...


  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I think many of these points are very well substantiated.

    Table-index separation is a discredited concept, and i believe that the only fly-in-the-ointment with round-robin extent allocation is that auto-allocation of space will mess it up. Uniform extent allocation is the way to go there.

    I absolutely believe in spreading all the data across all the available disks/controllers. I think it's an excellent principle.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    jodie, thanks for the link, good to see the other side of the story.

  9. #9
    Join Date
    Jan 2003
    Location
    Denver
    Posts
    152
    Originally posted by slimdave
    I think many of these points are very well substantiated.

    Table-index separation is a discredited concept, and i believe that the only fly-in-the-ointment with round-robin extent allocation is that auto-allocation of space will mess it up. Uniform extent allocation is the way to go there.

    I absolutely believe in spreading all the data across all the available disks/controllers. I think it's an excellent principle.
    Oh good to hear that.

    Here is a textual reference, I used this on an interview to the east coast, still was unable to convince the guy. He really wanted to put everthing onto specific disks, when I did that it was slow until I rebuilt it this way. Proved it again at the next shop. Your mileage may vary if your OLTP.

    The poor mans SAN:

    Page 250 - Configuring and Tuning Databases on the Solaris Platform - Allan Packer

    Database Engineering at Sun Microsystems says

    "Spread all types of data across all available disks" I think redo is not included in this recomendation.

    "The only way to make all disks active during all phases is to stripe all types of tablespaces across all available disks"
    Last edited by BJE_DBA; 05-21-2003 at 02:36 PM.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    He really wanted to put everthing onto specific disks, when I did that it was slow until I rebuilt it this way.
    Sounds like a frustrating interview. You probably couldn't have convinced them that their db's buffer cache hit ratio of 99.9999% was a bad thing as well if you started working there.

    Imagine running a query, and watching only 70% of your disks light up because the other 30% are being used for redo, temp space etc. that your query isn't using. Pretty embarrassing -- you could have boosted your i/o rate by 43% just by spreading the i/o across all those disks.

    Now if you work on data warehouses this becomes all very obvious. You might have just a single query running on the db at a particular time, so you want that query to use all of your disks. ie., you tune the i/o layout by considering each query, not by averaging out a couple of hundred simultaneous queries.

    Now in an OLTP system, where individual blocks are being read instead of full scans, by spreading extents for each table/index over all those lovely disks and controllers (where permitted by the number of extents per table, of course) the average load is going to be spread out as well.

    And there's nothing so satisfying as seeing al your disk lights come on and go off at the same time. Well, there is, but that's another story.

    Anyhoo, this seems very obvious, but i wish someone would post a disagreement -- I'm feelin' feisty today.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width