Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369

    Unanswered: active data - bufferpool sizing

    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?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    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...

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    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
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Marcus_A View Post
    programmers will very likely start writing really bad code
    Let me guess: you are married to one

  6. #6
    Join Date
    Apr 2011
    Posts
    19
    See what your hit rates are too

    db2pd -db DBNAME -bufferpools

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by Marcus_A View Post
    I allocate at least 50% of physical memory for bufferpools (assuming it does not exceed the size of the database).

    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?
    Last edited by db2girl; 05-04-11 at 06:28.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by BELLO4KA View Post
    that would make my db size ~640 GB
    At least it's close to the BCU recommendation of 300 GB of data per node.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by BELLO4KA View Post
    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?
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by n_i View Post
    At least it's close to the BCU recommendation of 300 GB of data per node.
    This is per logical partition, right?

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by Marcus_A View Post
    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.

    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.

Posting Permissions

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