Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    australia
    Posts
    17

    Unanswered: buffer hit ratio with db2 8.2 and CIO/DIO

    Hi Guys,

    I'm runing db2 fixpak 12 64 bits on AIX 64 bits. We mounted the filesystem to enable the Concurrent I/O and Direct I/O. We did all this in order to avoid the double copying of data that results in additional CPU consumption and reduce the duplication of application data within the filesystem buffer cache.Also According to IBM this is the closest that you can get to a raw devices using a JFS filesystem. Now looking at the db2 snapshots the counter "Direct reads" according to the System Monitor manual says that this counter contains "The number of read operations that do not use the buffer pool" . It means that for instance even if we have room to accomodate heaps of pages in bufferpool they won't be kept. Can someone explain whether if using Direct I/O it'll never use the Bufferpools at all. thanks.

    harby

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    All regular I/O to table and index tablespace pages goes through the buffer pool. There are a few exceptions that require direct reads, such as:

    1. All access to LOB columns bypasses the bufferpools (because it can be more than 32K, which is the maximum bufferpool page size).

    2. All access to LONGVARCHAR columns bypasses the bufferpools for the same reason as #1.

    3. Certain kinds of data in the DB2 Catalog bypasses the bufferpools. You can help alleviate this issue by having a sufficient allocation of memory in the CATALOGCACHE_SZ db parameter.
    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
    Jan 2003
    Location
    australia
    Posts
    17
    Yep I was aware of all this. Basically after I upgraded the instance to 64 bits what I was expecting is a change now that I'm able to address more memory even 32k is not too much when you have 40 gigs of RAM on a Database server. Also for instance a have a bunch of tables (around 5) with Clob/Varchar columns and those are use to create a template PDF documents. Imagine if I can fit most of this data into one bufferpool or at least the most accessed one. The performance would be much better I would've thought.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by hariza
    Yep I was aware of all this. Basically after I upgraded the instance to 64 bits what I was expecting is a change now that I'm able to address more memory even 32k is not too much when you have 40 gigs of RAM on a Database server. Also for instance a have a bunch of tables (around 5) with Clob/Varchar columns and those are use to create a template PDF documents. Imagine if I can fit most of this data into one bufferpool or at least the most accessed one. The performance would be much better I would've thought.
    I am not sure what you are saying. The maximum page size of 32K is not affected by the 32/64 bit instance choice, and has nothing to do with amount of memory than can be addressed by DB2.

    If your total row length will fit in a 32K page, then you would probably be better off using VARCHAR (instead of CLOB) or VARCHAR FOR BIT DATA (instead of BLOB), so that DB2 will make use of bufferpool cache for table pages.
    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
  •