Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: RAID disk and performance. Setting DB2_STRIPED_CONTAINERS environment variable

    Hi,
    on web page http://www.db2mag.com/qanda/2001q1_h...tionPoll.jhtml
    there is recomendation of setting DB2_STRIPED_CONTAINERS=ON if using RAID disk devices before creating tablespace.

    Does it make any sense to set this db2 environment settings after tablespace is created? I didn't set this variable
    at tablespace creation time. Is it too late?

    Thanks,
    Grofaty

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    striped

    this is a setting for pre v8
    and look in the info center online V8 for this setting and read :
    ....Users are thus strongly advised against setting this registry variable... and

    There will be no impact to existing containers that were created before the registry variable was set.
    Last edited by guyprzytula; 10-25-06 at 12:41.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    guyprzytula, thanks for reply.

    One more question related to RAID disks. Intel server has 4 physical disks incorporated into one RAID5 aray (RAID is hardware configured). So Windows operating system see only one logical disk (with only one partition).

    How should I set IOSERVERS parameter? Should I set "IOSERVERS = 4" or should I set "IOSERVERS=1"?

    My system:
    - Intel server
    - Windows 2003
    - DB2 workgroup edition v8.2.2 (v8 fixpack 9).

    Thanks,
    Grofaty
    Last edited by grofaty; 10-26-06 at 05:48.

  4. #4
    Join Date
    Jun 2006
    Posts
    471

    io

    always define more (eg 4)
    there is an overhead for defining more, but this can be ignored to the gain you could have from use of parallel io with more ioservers
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    guyprzytula, thanks a lot.

    One more question...

    In Admin Guide I have found PREFETCHSIZE setting related to RAID5 settings.
    Code:
    To define the number of prefetched pages for each table space, use the 
    PREFETCHSIZE clause in either the CREATE TABLESPACE or ALTER TABLESPACE 
    statements. The value that you specify is maintained in the PREFETCHSIZE 
    column of the SYSCAT.TABLESPACES system catalog table. It is a good
    practice to explicitly set the PREFETCHSIZE value as a multiple of the
    number of table space containers, the number of physical disks under 
    each container (if a RAID device is used) and the EXTENTSIZE value for 
    your table space, which is the number of pages that the database 
    manager writes to a container before it uses a different container.
    I have executed the following command: "db2 list tablespaces show detail" and output is:
    Code:
    Tablespace ID                        = 2
    Name                                 = USERSPACE1
    Type                                 = System managed space
    Contents                             = Any data
    State                                = 0x0000
      Detailed explanation:
        Normal
    Total pages                          = 1
    Useable pages                        = 1
    Used pages                           = 1
    Free pages                           = Not applicable
    High water mark (pages)              = Not applicable
    Page size (bytes)                    = 4096
    Extent size (pages)                  = 32
    Prefetch size (pages)                = 16
    Number of containers                 = 1
    If I understand correcty the prefetchsize of tablespace should be defined as:
    Prefetch size = (number of containers) * (number of disks per container) * extent size
    In my case "prefetch size" = 1 * 4 * 32 = 128. Did I understand this correctly?

    One more question about db2 environment. In the Admin Guide there is also written:
    Code:
    When reading data from, or writing data to table space containers, 
    DB2 may use parallel I/O if the number of containers 
    in the database is greater than 1. However, there are situations when it 
    would be beneficial to have parallel I/O enabled for single container table 
    spaces. For example, if the container is created on a single RAID device that 
    is composed of more than one physical disk, you may want to issue parallel
    read and write calls. To force parallel I/O for a table space that has a 
    single container, you can use the DB2_PARALLEL_IO registry variable.
    If I understand correctly the DB2_PARALLEL_IO db2 environment variable should be set.

    Should I set this two settings to increase performance? Is there any site effect, that I should be aware of?
    Last edited by grofaty; 10-26-06 at 08:00.

  6. #6
    Join Date
    Jun 2006
    Posts
    471

    ts

    yes : set already db2_parallel_io setting.
    and later change the prefetch size
    it is never recommended to change different setting at the same time, in case the result is worse, you don't know which setting implied this behavior.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Thanks a lot.

    I always change parameters with commands and I save the changed commands in text file. So if something goes wrong I can change it back.

  8. #8
    Join Date
    Oct 2006
    Posts
    15
    All-

    I want to discuss my problem here.

    I have got tablespaces with only one container . ANd surprisingly the
    Prefetch size is 3 time the extent size. And more than one instance was set up with this kind of configuration .

    And on Top of that my unix Admin told that these containers were created across multiple physical disks.

    Is this a correct setting ? Can i improve the performance of the system by chaning these values ?


    Btw- DB2_PARALLEL_IO is strictily for RAID . Is n't it correct?

    Extent size (pages) = 16
    Prefetch size (pages) = 48
    Number of containers = 1

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    DB2_PARALLEL_IO is only for RAID. With disk settings you tell DB2, that you have more physical disk inside operating system logical disk.

    But I don't understand the statement: "And on Top of that my unix Admin told that these containers were created across multiple physical disks."

    If you have only one container in your tablespace, than only one physical disk is used. If not using RAID disks then the optimal configuration is one container by physical disk. So if you are using only one container by tablespace, you probably don't have optimal configuration applied.

    Hope this helps,
    Grofaty

  10. #10
    Join Date
    Jul 2005
    Location
    Irvine, CA
    Posts
    23
    DB2_PARALLEL_IO is not just for RAID. A DBA can create one container per separate singular physical disk (let's say 8 disks) and then set db2 parallel on. DB2 will now evenly spread the data accross all 8 containers. I have heard that this was the original intent of this parallel feature in the first place because. It is similar to a software RAID 0. You can get the benefit of parallel I/O without having to buy RAID. I don't recommend using single unprotected disks though... Use at least RAID1.

    As for "And on Top of that my unix Admin told that these containers were created across multiple physical disks." A single container can be spread accross many physical disks by two methods.

    1. Build a container on a file system that is built from a hardware RAID

    or

    2. Build a container on an AIX file system built on a volume group that is comprised of many logical volumes 0- each lv belonging to a separate pv (disk). THis is AIX striping of physical disks. Again, similar to RAID.

    In addition, an AIX admin can stripe many RAID disks into volume groups. This is sometimes called "striping a stripe". If you use this latter method + build many containers, this is sometimes referred to as "Scattering your data accross the Universe". This can be really good or bad depending on the data access type. Mostly this method is used in conjunction with SANs. I have had SAN "experts" tell me to do this scatter method and other say it is horrible. Only testing bares this out, one way or the other.

    Steve Harris
    Certified DB2 DBA

  11. #11
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Quote Originally Posted by sharrisdb2
    DB2_PARALLEL_IO is not just for RAID. A DBA can create one container per separate singular physical disk (let's say 8 disks) and then set db2 parallel on. DB2 will now evenly spread the data accross all 8 containers. I have heard that this was the original intent of this parallel feature in the first place because. It is similar to a software RAID 0. You can get the benefit of parallel I/O without having to buy RAID. I don't recommend using single unprotected disks though... Use at least RAID1.

    As for "And on Top of that my unix Admin told that these containers were created across multiple physical disks." A single container can be spread accross many physical disks by two methods.

    1. Build a container on a file system that is built from a hardware RAID

    or

    2. Build a container on an AIX file system built on a volume group that is comprised of many logical volumes 0- each lv belonging to a separate pv (disk). THis is AIX striping of physical disks. Again, similar to RAID.

    In addition, an AIX admin can stripe many RAID disks into volume groups. This is sometimes called "striping a stripe". If you use this latter method + build many containers, this is sometimes referred to as "Scattering your data accross the Universe". This can be really good or bad depending on the data access type. Mostly this method is used in conjunction with SANs. I have had SAN "experts" tell me to do this scatter method and other say it is horrible. Only testing bares this out, one way or the other.

    Steve Harris
    Certified DB2 DBA
    Thank you Steve, your comments was usefull, I have a question

    recently we have moved to SAN system, what is your recomendation for changing in db, dbm cfg and registry variables for better performance ? we are using db2v8.2.3 and aix 5.2, do you know any reference related to this ?

    Thank you in advance for your help

  12. #12
    Join Date
    Jul 2005
    Location
    Irvine, CA
    Posts
    23
    M_RAS,

    You should start out with the "Best Practices for Tuning DB2 UDB v8.1 and its Databases" white paper from IBM. There may be a newer one for 8.2 now. I outlines most of what you will want to know. This paper will probably answer your questions much better than most anthing else.

    Remember that each system, being unique, requires its own special settings.
    Even systems with the exact same program and database (such as SAP) will need differing setting due to the data size differences, number of users, etc.
    There are just too many variables involved that preclude one from saying, "these boilerplate settings are the best."

    Plus, if you do the research yourself by trial and error, you get move futher along to becoming an expert

    General Observations and Advice:

    First, I assume you are using DMS tablespaces exception for the catalog, userspace, and temp(s).

    Second, DB2 best practices can differ depending on the SAN manufacturer you are using. Some say to build only one large container for data and one for indexes per tablespace. Others say to make one container for each non-partity physical disk in the RAID. Yet again, others say to make one container per RAID. EMC, IBM Shark, IBM FAStT, Net Appliance, etc. each have a different best practices. The famous "it depends" answer applies here.

    I suggest that you also look for white papers from the SAN manufacturer. Finding anything for DB2 is difficult, but EMC and IBM do have papers on DB2 UDB.

    Third, start out with moderate settings and test. Change only one setting at a time and test. If you change 2 or more at once, you may not get the result you expect and conclude all the changes were bad, or get a good result and conclude all changes were good - but the result would have been even better if one of the setting had't been changed. Yes it is tedious, but a DBA's job is supposed to be Exception - some settings need to be changed together by design and it is appropriate and maniditory to do so in these cases.

    I set the I/O Servers and Prefetchers fairly low, based upon the number of CPUs and non-partity disks. I then ran load tests and looked at the snapshots to see if I needed more cleaners and prefetchers.

    Fourth, OLTP vs OLAP, or a lot of static reads vs a lot of dynamic writes? It makes a big difference which type of data access your programis doing. The databases I work with are 3rd party canned (off the shelf) and cannot be changed a lot. They are a mixture of all data access types, so I have to tune to the worst of all worlds. If I tune for super fast reads, the write speed suffers and vice-versa. Finding a balance is an art and takes time and testing.


    -- Steve

  13. #13
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Quote Originally Posted by sharrisdb2
    M_RAS,

    You should start out with the "Best Practices for Tuning DB2 UDB v8.1 and its Databases" white paper from IBM. There may be a newer one for 8.2 now. I outlines most of what you will want to know. This paper will probably answer your questions much better than most anthing else.

    Remember that each system, being unique, requires its own special settings.
    Even systems with the exact same program and database (such as SAP) will need differing setting due to the data size differences, number of users, etc.
    There are just too many variables involved that preclude one from saying, "these boilerplate settings are the best."

    Plus, if you do the research yourself by trial and error, you get move futher along to becoming an expert

    General Observations and Advice:

    First, I assume you are using DMS tablespaces exception for the catalog, userspace, and temp(s).

    Second, DB2 best practices can differ depending on the SAN manufacturer you are using. Some say to build only one large container for data and one for indexes per tablespace. Others say to make one container for each non-partity physical disk in the RAID. Yet again, others say to make one container per RAID. EMC, IBM Shark, IBM FAStT, Net Appliance, etc. each have a different best practices. The famous "it depends" answer applies here.

    I suggest that you also look for white papers from the SAN manufacturer. Finding anything for DB2 is difficult, but EMC and IBM do have papers on DB2 UDB.

    Third, start out with moderate settings and test. Change only one setting at a time and test. If you change 2 or more at once, you may not get the result you expect and conclude all the changes were bad, or get a good result and conclude all changes were good - but the result would have been even better if one of the setting had't been changed. Yes it is tedious, but a DBA's job is supposed to be Exception - some settings need to be changed together by design and it is appropriate and maniditory to do so in these cases.

    I set the I/O Servers and Prefetchers fairly low, based upon the number of CPUs and non-partity disks. I then ran load tests and looked at the snapshots to see if I needed more cleaners and prefetchers.

    Fourth, OLTP vs OLAP, or a lot of static reads vs a lot of dynamic writes? It makes a big difference which type of data access your programis doing. The databases I work with are 3rd party canned (off the shelf) and cannot be changed a lot. They are a mixture of all data access types, so I have to tune to the worst of all worlds. If I tune for super fast reads, the write speed suffers and vice-versa. Finding a balance is an art and takes time and testing.




    -- Steve

    Thank you again Steve for your help and recomendation. it seems I am at the begginig of a long way, no problem it is the life.

Posting Permissions

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