Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: Bufferpool size for DB2 UDB 64 bit?

    Hello,

    Does anyone know what the ideal bufferpool size should be for DB2 UDB running on Linux in 64-bit mode?

    I read this somewhere --

    In terms of performance, if you don't increase the size of the bufferpools after the upgrade, the performance will degrade, so you need to have sufficient physical memory on your box to offset the performance degrade. This is a bit of a no-brainer as the only benefit of moving to 64-bit is so that DB2 can address more memory.


    Thanks!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Assuming that your database size is larger than your bufferpool, you generally should allocate as much memory as possible, making sure that you are always using real memory (not vitual memory) and that there is enough real memory available for other DB2 needs, the operating system, and whatever other programs are running on the server.
    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 2007
    Location
    Jena, Germany
    Posts
    2,721
    And the reason why performance degrades when moving to 64 bit and keeping everything else the same is obvious: any pointers used internally in DB2 require now twice as much space (4 instead of 8 bytes). Thus, the same amount of memory can hold less information than before because each information is bigger.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Dec 2002
    Posts
    123
    Our database is about 35 GB..and this is what we have as the bufferpool settings when our db was in 32 bit mode. Would you change anything?


    db2 "select varchar(bpname,20) as bpname,npages,pagesize from syscat.bufferpools"

    BPNAME NPAGES PAGESIZE
    -------------------- ----------- -----------
    IBMDEFAULTBP 1000 4096
    BKDB_BP 10000 8192
    BKDB_DATABP 10000 8192
    TEMPSP2_BP 2000 8192
    HISTBK_BP 2000 8192
    UTILBK_BP 2000 8192
    BKDBREPL_BP 3000 8192

  5. #5
    Join Date
    Dec 2002
    Posts
    123
    Our database is about 35 GB..and this is what we have as the bufferpool settings when our db was in 32 bit mode. Would you change anything?


    db2 "select varchar(bpname,20) as bpname,npages,pagesize from syscat.bufferpools"

    BPNAME NPAGES PAGESIZE
    -------------------- ----------- -----------
    IBMDEFAULTBP 1000 4096
    BKDB_BP 10000 8192
    BKDB_DATABP 10000 8192
    TEMPSP2_BP 2000 8192
    HISTBK_BP 2000 8192
    UTILBK_BP 2000 8192
    BKDBREPL_BP 3000 8192

  6. #6
    Join Date
    Jun 2007
    Posts
    66
    Increase by small amounts like 10% everytime and keep monitoring the BP hit ratio and db2mtrk.BP hit ratio can be your benchmark and you might as well compare the SQL execution times with your older records


    Thanks

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2user
    Our database is about 35 GB..and this is what we have as the bufferpool settings when our db was in 32 bit mode. Would you change anything?


    db2 "select varchar(bpname,20) as bpname,npages,pagesize from syscat.bufferpools"

    BPNAME NPAGES PAGESIZE
    -------------------- ----------- -----------
    IBMDEFAULTBP 1000 4096
    BKDB_BP 10000 8192
    BKDB_DATABP 10000 8192
    TEMPSP2_BP 2000 8192
    HISTBK_BP 2000 8192
    UTILBK_BP 2000 8192
    BKDBREPL_BP 3000 8192
    You have allocated a total of 236 MB in memory to bufferpools. In general, you should have a fewer number of bufferpools. You do not need one for each tablespace.

    It is generally (but not always) good to have separate bufferpools for the following (and not more than this):

    1. small tables and all but the very largest indexes
    2. medium size tables and very large indexes
    3. large tables and tempspace

    This is just a suggestion, and the specific requirements/design of your application may require a slightly different configuration.

    Each of the above bufferpools should be roughly equal in total size (page size times number of pages), but they will have different bufferpool hit ratios because the data behind each bufferpool is a progressively larger percentage of the data assigned to them.

    You can only do the above suggestion (put some indexes in separate bufferpools from the table) if you used DMS tablespaces and if you defined the tables to use different tablespaces for the table and the index.

    Given the above, and that all your tables use 8K pages except for the catalog, you would have a total of 4 bufferpools.

    I would increase the IBMDEFAULTBP to 3000 pages to make sure the system catalog is always in memory. The remaining bufferpools (in total) should be at least 50% of the physical memory on the machine (assuming DB2 is pretty much the only thing running on that server). Note that if you have other DB2 databases on the same server, you will need to take them into account when allocating at least 50% of memory to bufferpools.
    Last edited by Marcus_A; 12-17-07 at 22:01.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Dec 2002
    Posts
    123
    Thanks Marcus! I will keep all this in mind...

Posting Permissions

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