Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Location
    India
    Posts
    23

    Unanswered: Allocation of bufferpool size....Urgent......

    Hi,
    I am working on DB2 UDB. The space allocated for my database is approx 5 GB. For good tuning of the database, How much % of the total space I shud use for the bufferpool??? I have taken 4 GB (Approx 75 %) as the buffer pool size but i guess this is quite high...Pls suggest me proper bufferpool size....

    I have created buffer pool as below:

    CREATE BUFFERPOOL XYZ
    SIZE 125000
    PAGESIZE 32K
    ;

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    If you are using 32 bit, you are limited to 1.75gig total; You can use 1gig,250000 4k pages for buffer pool; not knowing what the application is, it is difficult to recommend. look at AUTOCONFIGURE command; it will make suggestions. with 64 bit you do not have the 2gig limit on db2
    mota

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The actual amount of memory that DB2 can address depends on the OS. For AIX it is about 1.75, for Solaris it is higher, for HP/UX and Linux it is lower. For Windows, it can be higher, depending on the exact version of Windows one is using.

    In this situation, 64-bit instances of DB2 are highly recommended if you have that much memory for a single DB2 instance.

    Normally, 50-75% of real memory for bufferpools is about right. Remember that this is the total of all bufferpools for all activated databases in the DB2 instance. A database is activated if it has any connections, or has been explicity activated.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2004
    Location
    India
    Posts
    23
    Thnx a lotts for ur replies !!!!

  5. #5
    Join Date
    Aug 2004
    Location
    London, UK
    Posts
    31

    From practical experience

    1.75Gb is the theoretical limit in AIX for all database shared memory (i.e. sortheap, bufferpool, locklist etc). In practise, AIX allocates memory in 256Mb chunks. if you are using the table snapshot functions (you are using fenced udfs), this trims off another 256Mb from the theoretical limit. If you use intra-parallelism this trims off another 256Mb. With a suitably large sort heap, the actual amount of physical memory for bufferpools is closer to 1Gb.

    Under Linux, it's even smaller, but under Solaris you can apparantly address much more physical memory.

  6. #6
    Join Date
    Feb 2005
    Posts
    118
    I updated the DB CFG BUFFPAGE parm and I get a msg that BUFFPAGE is effective only if SIZE is -1.
    Therefore I did
    ALTER BUFFERPOOL ......SIZE -1;

    Now before I issue a UPDATE DB CFG BUFFPAGE, I want to what's the most value I can assign.
    I am using windows 2000, and I see that it uses a RAM of 512MB
    So, just knowing my RAM, how do arrive at my number (max value) for BUFFPAGE ?

    thanks.
    AK

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The BUFFPAGE parameter is obsolete and the DB2 version 8.1 manuals say that you should not use it.

    Instead, alter the bufferpool size to the size you need with SQL.

    If your server only has 512 MB of memory, at it is only used as a DB2 server, then I would keep the total of all bufferpools for active databases to about 100 MB. That would be about 24,000 4K pages total for all bufferpools.

    A database is active if at least one application is connected to it, or if it is explicitly activated.
    Last edited by Marcus_A; 02-28-05 at 12:40.
    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
  •