Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Unanswered: one big bufferpool or multiy small bufferpool which one is better for perfformance

    I use db2v8.2.2 and aix 5.2

    in perfformance point of view which one is better, one big buffer pool and assign all tablespaces to that or small bufferpools assign to different tablespaces?

    any idea?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you don't know the answer to that, then use one big bufferpool.

    If you really understand bufferpools, then using 2 or 3 bufferpools for different kinds of data (small tables/indexes, medium size tables/large indexes, very large tables, etc) might be a good idea for a large database. But never just arbitrarily assign tablespaces to different bufferpools.
    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
    Oct 2005
    Posts
    109
    I ususally leave the default bufferpool to be used for the system catalog, and create one big one for all other.

    And on LUW I usually use 8K or 16K pagesize. In my experience even OLTP projects today tend to read and display much data before processing, so I found this useful as a standard definition.

    Only very special cases (like caching of one special table or so)might give you a valid reason to change that. I'd only start doing that in case problems.

    If you know what the applications does it might be good to assign multiple bufferpools ... but if design changes you will have to check always as well. Especially with externally developed projects or software packages nobody is to know what happens with the next release and this becomes rather tough.
    Juliane

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by juliane26
    And on LUW I usually use 8K or 16K pagesize. In my experience even OLTP projects today tend to read and display much data before processing, so I found this useful as a standard definition.
    That might be OK if your tables where highly clustered and the application was usually asking for multiple rows in the same clustering sequence. Obviously, when doing a lot of table scans, page size greater than 4K is appropriate. But for most OLTP applications, 4K page size is usually best.
    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 2004
    Posts
    138
    at any page size, maximum rows allowed is 255..if you define 16k for a table with average row length of 50 bytes, you end up wasting space..I use 4k for OLTP, 16k for DSS

Posting Permissions

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