Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Bufferpool size

  1. #1
    Join Date
    Nov 2011
    Posts
    87

    Unanswered: Bufferpool size

    Hi, can you please assist me getting correct bufferpool size if possible.

    DB2 9.7 FP 3a / on windows 2008 R2. 6GB RAM

    our system has 4 bufferpools 4K pages.
    bufferpool1 40MB
    bufferpool2 900MB
    bufferpool3 400MB
    bufferpool4 40MB.

    The data hit rate is over 82.5%
    and the index hit rate is over 98% except bufferpool 4 it is 85%.

    The memory usage is around 4GB and always 2GB ram is available.

    The question is, how do I know that allocated bufferpool size is enough?
    Is it worth it ALTER it to AUTO and enable STMM?

    Thanks.

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    if you did not use DPF and not so familiar with db2 .
    I Suggest to use STMM。
    and bufferpools hit ratio does not mean much。
    It must be combined with other instructions to see whether there is a perfomance problem or not。

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You need to be running the 64-bit versions of both Windows and DB2 (which you apparently don't) to take advantage of the memory above 4 GB.

  4. #4
    Join Date
    Nov 2011
    Posts
    87
    No, we are running on 64bit - both (db2 and win 2008r2)

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2mtrk View Post
    Is it worth it ALTER it to AUTO and enable STMM?
    Yes. In your case I would.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    db2mtrk, You don't mention what Edition of DB2 you are running. If you are using DB2 Express, it is limited to only 4GB of memory.

  7. #7
    Join Date
    Nov 2011
    Posts
    87
    Hi,
    we are running DB2 ESE v9.7 FP 3a

    What happen if i allocate more pages to the BP's?

    Thanks.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Then the BPs have more space and can cache more pages from disk. Thus increases the chances that DB2 doesn't have to read pages from disk, which is about a factor of 1000x slower that in-memory access (your mileage may vary, depending on the disk subsystem you have, but even with SSDs you have order of magnitude of difference). Ultimately, a bigger buffer pool will give you a higher BP hit ratio.

    The real question is if you have a performance problem and need to tune something - or not. If not, why bother? If you have a performance problem, what is the nature of it?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2mtrk View Post
    Hi,
    we are running DB2 ESE v9.7 FP 3a

    What happen if i allocate more pages to the BP's?

    Thanks.
    Generally, you can increase the bufferpool sizes so long as the system has enough free real memory (not just virtual memory) to accommodate the request.
    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
    Nov 2011
    Posts
    87
    when we do the batch load (this creates new customer account details)
    it creates 250 accounts in a minute. But I think this is little bit low and this can be increased.

    As we taking over new business, I have to load over 1 million customers account and this is going to take ages.

    any other areas I can tune ?

    Thanks

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I suspect you're not using either batch [insert] or load, but individual insert statements, otherwise you'd see much higher performance. Consider using the LOAD utility. If you insist on using SQL, look at your logging (log write time, log buffer full events) and page cleaner performance (no victim buffer events, dirty page steals).

    You should also install the latest fixpack (5).

  12. #12
    Join Date
    Nov 2011
    Posts
    87
    yes, it is a java application and doing individual insert into at least 4 tables.

    For the logging, it creates around 25 logs to insert 15K accounts. log size is 10MB. primary log number is 60 and the secondary is 40. Also HADR is in place.

    I am thinking to increase the seconday logs to maximum number (240) when I do the 1 millioan customer inserts.

    any other suggestions?

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2mtrk View Post

    I am thinking to increase the seconday logs to maximum number (240)
    How's that supposed to help performance?

  14. #14
    Join Date
    Nov 2011
    Posts
    87
    Quote Originally Posted by n_i View Post
    How's that supposed to help performance?
    Yes, you are correct, this will not increase the performance. But what I was trying to say is by increasing the number of logs I want to stop logs getting full. But I am not sure that how many logs are needed to insert 1 million records.

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Make sure that:

    ( (LOGPRIMARY + LOGSECOND) x 4096 ) < the number of bytes available on your mount point or path where logs are stored.
    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
  •