Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Location
    USA
    Posts
    53

    Unanswered: extentsize and prefetchsize

    How do you decide what EXTENTSIZE and PREFETCHSIZE should be set to? Please provide suggestions for OLTP and DSS systems. I'm still confused about the two parameters.

    DB2 9.1 on AIX

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can now set prefetch size to automatic in 9.5 (not sure about 9.1). But normally it should be extent size times number of containers in the tablespace.

    Prefetch (which are sort of look-ahead reads to get data into the bufferpool before pages are actually requested) should only be a factor if you have queries that do table scans (hopefully not OLTP applications).

    Generally, you will want smaller extent sizes for OLTP and larger for Data Warehouses, but it should be an even multiple of the RAID stripe size (1x, 2x, etc).
    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
    Jul 2009
    Location
    USA
    Posts
    53
    I think I understand PREFETCHSIZE, but I'm still unclear about EXTENTSIZE.

    EXTENTSIZE - the number of pages written to a tablespace container before writing to the next container. Based on this definition, EXTENTSIZE setting does affect how DB2 is reading data from the disk, only how it's writing it to the disk.

    Am I correct? What is the reason behind using smaller EXTENTSIZE for OLTP and larger for Data Warehouse?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    With a data warehouse, you are usually reading and writing larger chunks of data at one time. But if the amount of data that is being read is smaller, you still want it spread over multiple containers to improve performance.
    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
    Jul 2009
    Location
    USA
    Posts
    53
    I understand about reading data - table scans will benefit from a larger PREFETCHSIZE.

    But in a data warehouse, we do not do a lot of writing, do we? I don't see how larger EXTENTSIZE is beneficial.

    I guess I'm still confused about what EXTENTSIZE is and what effect this parameter has on queries that mostly do table scans.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2aix View Post
    I understand about reading data - table scans will benefit from a larger PREFETCHSIZE.

    But in a data warehouse, we do not do a lot of writing, do we? I don't see how larger EXTENTSIZE is beneficial.

    I guess I'm still confused about what EXTENTSIZE is and what effect this parameter has on queries that mostly do table scans.
    You write a lot of data at one time when data is loaded to the data warehouse, and you read a lot of data (usually) when you do queries against a data warehouse. DB2 does the physical access to disk in extent chunks. If the amount of data read or written at one time is significantly less than the extent size, you may be wasting some resources, although I have never seen hard numbers on the actual performance difference.

    For OLTP, the exact opposite is true compared to a DW (in terms of of the amount of data for reading and writing). But in both cases (OLTP and DW), you want to spread to data across multiple containers (if you have multiple containers in the tablespace) to reduce spindle contention. But there is a quirk here, since DB2 tries to avoid synchronous I/O to disk, and disk writes are deferred to when a page cleaner comes along later and writes the data from the bufferpool to disk, sometimes long after the SQL insert, update, or delete. The page cleaners try to write as much data at one time for a given tablespace, so it is possible that more data than you realize is being written at one time.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jul 2009
    Location
    USA
    Posts
    53
    Thanks for clearing my doubts. Very helpful.

Posting Permissions

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