Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: RAID-5 and DB2_PARALLEL_IO

    "RAID level 5 involves data and parity striping by sectors, across all disks. Parity is interleaved with data, rather than being stored on a dedicated drive."


    DB2_PARALLEL_IO=*,N where N is the number of disks in a RAID array



    Let's say I have a RAID-5 with 5 disks (4+P)


    Most references suggest setting N to the number of disks excluding parity - 4 data disk spindles:

    DB2_PARALLEL_IO=*,4



    Knowing that parity does not reside on one particular disk, shouldn't N be set to the number of disks including parity:

    DB2_PARALLEL_IO=*,5


    What do you think?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In the IBM InfoSphere Balanced Warehouse D5100: Design and Implementation Guide they suggest using DB2_PARALLEL_IO=*:5 for DS3400 SAN disks with 5 physical spindles per array. That is what we use on our DPF data warehouse.

    Not sure what the difference between colon and comma in the syntax.

    For other SAN or NAS storage systems (which may use proprietary RAID technology) it is best to ask the SAN vendor. NetApps suggested that we use DB2_PARALLEL_IO=* for their NAS systems, and we also use that for our Hitachi SAN's.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Thanks. I believe it should be a colon instead of a comma in my example.

    I found the following document: D5100 documentation: Design and Implementation Guide -05 refresh (Jan, 2010)

    where it's stated:
    As will be discussed in “Recommended DB2 registry variable settings” on page 42, the D5100 uses the DB2_PARALLEL_IO registry variable to specify that there are 5 physical spindles per container.

    In “Recommended DB2 registry variable settings”:
    DB2_PARALLEL_IO *:5 Indicates that all table spaces will have parallel I/O on, and that all table spaces will use 5 as the number of physical drives per container.


    In "What's new" section of this document:
    External disks configured as (4+P+Q) RAID-6 instead of (5+P) RAID-5; storage layout on the InfoSphere Warehouse application server nodes modified to allow the creation of a home directory for the InfoSphere Warehouse administrative user on external storage:


    Does "..instead of (5+P) RAID-5" mean that there are actually 6 disks per array and by setting DB2_PARALLEL_IO *:5 they're excluding P(arity) ?


    Here are some other references where it's suggested not to include parity:
    Distributed DBA: Storage, I/O, and DB2
    http://www.idug.org/conferences/EU2008/data/EU08C12.pdf

    The same information is found in his latest presentation from 2010.


    Here is where it's suggested to include parity:
    http://download.boulder.ibm.com/ibmd...rage_1009I.pdf

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure how RAID-6 actually works, and every time I ask storage person at my company about it (or the number of disks in the array) they just start mumbling something about proprietary RAID (I don't think they really know).

    I will have to check to see if our IBM DS3400 disk is actually RAID-5 or RAID-6, although I may not be able to find out.

    For our other OLTP databases that use Hitachi SAN, I am pretty sure they are RAID-6, although I am not sure how many disks there actually are in the arrays. I know that RAID-5 does not mean there are 5 spindles (you can implement RAID-5 with as few as 3 spindles and up to as many as can be handled by a RAID controller).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Yes, I understand that RAID-5 doesn't mean there are 5 disks.


    "IBM InfoSphere Balanced Warehouse D5100: Design and Implementation Guide" covers RAID-6 storage so I checked "IBM InfoSphere Balanced Warehouse E7100 with InfoSphere Warehouse Version 9.5.1: Design and Implementation" where RAID-5 configuration is covered.


    In E7100 doc, they mention that each array consists of 4 drives in a (3+P) configuration and suggest setting:
    DB2_PARALLEL_IO *:4 Indicates that all table spaces will have parallel I/O on, and that all table spaces will use 4 as the number of spindles per container.


    So, all 4 disks (3+P) will be active during prefetch. This sounds right to me.


    What I don't understand is why most articles, presentations and v9.7 InfoCenter suggest to set:
    DB2_PARALLEL_IO = (# of disks per array) - 1

    From IBM DB2 9.7 for Linux, UNIX and Windows Information Center :

    "...configured as three RAID-5 arrays with five disks in each array...

    Use the DB2_PARALLEL_IO registry variable to enable parallel I/O for all table spaces and to specify the number of physical disks per container.
    For the situation in the example, set DB2_PARALLEL_IO = *:4. "


    I understand that with RAID-5, the capacity is reduced by one disk (space taken up by parity). But if DB2_PARALLEL_IO is not set to the total number of disks per array, then all disks won't be active during prefetch.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Marcus_A View Post
    In the IBM InfoSphere Balanced Warehouse D5100: Design and Implementation Guide they suggest using DB2_PARALLEL_IO=*:5 for DS3400 SAN disks with 5 physical spindles per array. That is what we use on our DPF data warehouse.
    For your DPF data warehouse where you have DB2_PARALLEL_IO=*:5, do you follow BCU recommendations for setting EXTENTSIZE and PREFETCHSIZE?

    Do you know if EXTENTSIZE = RAID strip size (segment size) in your DPF environment?

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    After thinking about this some more, I think DB2_PARALLEL_IO=*:4 should be the correct setting for RAID-5 with 5 disks (4+P)

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl View Post
    For your DPF data warehouse where you have DB2_PARALLEL_IO=*:5, do you follow BCU recommendations for setting EXTENTSIZE and PREFETCHSIZE?

    Do you know if EXTENTSIZE = RAID strip size (segment size) in your DPF environment?
    Here is what the doc recommended for the D5100 InfoSphere Warehouse configuration:

    "The D5100 recommends an extent size of 512 KB. This extent size is large enough that it results in an efficient prefetched scan rate, but also small enough that it supports good performance for multi-dimensional clustering (MDC) tables and partitioned tables."

    I used an extent of 512K. However, since my pagesize for large tables is 32K (instead of 16K for all tablespaces as they recommended), then I used 16 pages for extent size (16 x 32K is 512K).

    For Prefetch I used automatic. According to the same manual, this is what DB2 would calculate:

    "When the database manager is allowed to choose the prefetch size for a table space automatically, it uses the following formula:
    prefetch size = (number of containers) x (number of physical spindles per container) x extent size

    As will be discussed in “Recommended DB2 registry variable settings” on page 50, the D5100 uses the DB2_PARALLEL_IO registry variable to specify that there are 5 physical spindles per container. The AUTOMATIC setting for the prefetch size therefore results in a value of ((1 container) x (5 physical spindles per container) x (512 KB extent size)) = 2560 KB."

    If I am using the wrong value for DB2_PARALLEL_IO (*:5), please let me know. Do you know where I can an updated copy of the IBM InfoSphere Balanced Warehouse D5100: Design and Implementation Guide (mine is SC23-7735-03).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Based on my understanding of RAID-5 (not sure about RAID-6):

    - extentsize should be equal to the RAID strip size (single disk per array) or stripe size (all disks per array - parity).

    - DB2_PARALLEL_IO should be equal to (all disks per array - parity) only if the extentsize is set to RAID strip size (not stripe size)

    - prefetchsize should be set so that (all disks - parity) are active during prefetch and automatic calculation will be correct if the extentsize and DB2_PARALLEL_IO (if it's needed) are set correctly.


    If someone thinks this is incorrect, please let me know.



    You can try the following download site (but you may not be authorized to download):
    https://www14.software.ibm.com/webap...&source=idwbcu

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl View Post
    Based on my understanding of RAID-5 (not sure about RAID-6):

    - extentsize should be equal to the RAID strip size (single disk per array) or stripe size (all disks per array - parity).

    - DB2_PARALLEL_IO should be equal to (all disks per array - parity) only if the extentsize is set to RAID strip size (not stripe size)

    - prefetchsize should be set so that (all disks - parity) are active during prefetch and automatic calculation will be correct if the extentsize and DB2_PARALLEL_IO (if it's needed) are set correctly.


    If someone thinks this is incorrect, please let me know.



    You can try the following download site (but you may not be authorized to download):
    https://www14.software.ibm.com/webap...&source=idwbcu
    I am not an expert on this, but I thought extent size can be an even multiple of stripe size, so it could be 1 x stripe size, 2 x strip size, etc.

    As for the rest of it, is it possible to send an inquiry to the BCU team (or some other IBM group), since they seem to thing that for RAID 5 with 5 disks (including parity) that it should be DB2_PARALLEL_IO=*:5 ?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Marcus_A View Post
    I am not an expert on this, but I thought extent size can be an even multiple of stripe size, so it could be 1 x stripe size, 2 x strip size, etc.
    Let's say I create a tablespace with one container on RAID-5 (3+P)

    RAID STRIP size = 256KB
    RAID STRIPE size = 256 x 3 = 768KB

    EXTENTSIZE = 2 x STRIPE size = 1536KB
    PREFETCHSIZE = AUTOMATIC = (1 container) x EXTENTSIZE = 1536KB (DB2_PARALLEL_IO is not set)


    Each spindle will be accessed twice by utilities such as load or during prefetch. Do you know if this is considered as one I/O or two I/Os?



    Quote Originally Posted by Marcus_A View Post
    As for the rest of it, is it possible to send an inquiry to the BCU team (or some other IBM group), since they seem to thing that for RAID 5 with 5 disks (including parity) that it should be DB2_PARALLEL_IO=*:5 ?
    Question has been sent. I'll let you know when/if I hear back from them.

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl View Post
    Let's say I create a tablespace with one container on RAID-5 (3+P)

    RAID STRIP size = 256KB
    RAID STRIPE size = 256 x 3 = 768KB

    EXTENTSIZE = 2 x STRIPE size = 1536KB
    PREFETCHSIZE = AUTOMATIC = (1 container) x EXTENTSIZE = 1536KB (DB2_PARALLEL_IO is not set)


    Each spindle will be accessed twice by utilities such as load or during prefetch. Do you know if this is considered as one I/O or two I/Os?
    I don't know.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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