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!

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.

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.

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

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

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

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.

Thanks Marcus! I will keep all this in mind...