I have a question related to page #7 of the following presentation:
http://www.ne-imug.com/Presentations...20Features.pdf


"Advisable to allocate 5% of active data for bufferpool sizing"


I understand about active data, but why 5% for bufferpools. What does it mean?

I think he means that bufferpools should be sized at 5% of the data volume (on disk). In other words, if your database is 100 GB, you should try to allocate 5 GB for bufferpools.

I guess my question is why only 5% of active data (we need to exclude dormant data). Is 5% sufficient? Do they consider just raw data or include indexes, temps...

I don't know what it means, but I allocate at least 50% of physical memory for bufferpools (assuming it does not exceed the size of the database). Obviously, if multiple databases are on the same server, then that needs to be taken into account.

In looking at the entire presentation, it looks like the presenter is mixing DPF recommendations with general recommendations, without making it clear which is which. Maybe that is the source of the confusion.

Regarding the rest of the presentation, Currently Committed (CUR_COMMIT db cfg) is fools gold. If this is implemented, programmers will very likely start writing really bad code (poor concurrency, lack of commits, failure to close transactions) without getting any deadlocks, and DB2 will start having to go deeper and deeper into the logs (including to logs on disk) to retrieve uncommitted data, and performance will eventually deteriorate to unacceptable level.

Let me guess: you are married to one

See what your hit rates are too

In your production DPF env, what is the size of your bufferpools relative to the db size (across all partitions)?

Let's say, DPF with 2 physical data servers (I excluded admin server) - each data server has 32 GB. I assume all data stored in the db is active. If I allocate 50% of 32 GB for bufferpools on each physical server and apply "Advisable to allocate 5% of active data for bufferpool sizing" recommendation, that would make my db size ~640 GB. Is your db size close to this number?

At least it's close to the BCU recommendation of 300 GB of data per node.

Yes, it is close for our DPF system. Keep in mind that each of our 2 physical data servers with 32 GB of memory has 4 DPF parititions (8 total for the 2 data servers), each with its own bufferpools. So 5% is probably reasonable for data warehouse, although I have multiple bufferpools, and the dimension tables (small reference tables) are fully covered by bufferpool memory in a different bufferpool than the large fact tables.

For a OLTP application, I would have multiple bufferpools, and different tables would have different percentages of bufferpool behind them:

For example (crude example):

1. Small tables and small/medium size indexes: Bufferpools 100% or more the size of the data
2. Medium Size tables and Large indexes: Bufferpools 50% the size of the data
3. Large tables: Bufferpools 5-20% the size of the data

The above scenario (based on queueing theory) is one reason why STMM control of bufferpools does not work well.

This is per logical partition, right?

Yes, I omitted the fact that each physical data server has 4 logical partitions (I was using your DPF setup as an example).

Using BCU recommendation of 300 GB of data per logical partition and recommendation to allocate 5% of data for bufferpools, BPs would need 60 GB of RAM per physical data server. But I think BCU documentation mentions a total of 32 GB of RAM per server.


Thanks everyone for the explanation.