Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: v8.2 -> v9.5.1 - self tuning vs. autoconfigure

    we are migrating to DB2 v9.5.1 and AIX 6.1 at the same time.

    - do I leave dbm and db cfg parameters same as in prev. v.8.2 or should I reconfigure (we noticed some performance degradation in v9.5.1).
    - is new self tuning feature in v9 good to use for database memory tuning?
    - what works better - self tuning feature or autoconfigure?

    Thanks in advance
    Last edited by MarkhamDBA; 04-28-09 at 16:17.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would not recommend that you use automatic bufferpools in 9.5. Everything else automatic is OK.
    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 2003
    Posts
    4,292
    Provided Answers: 5
    I would concur with Marcus. My experience with automatic bufferpools was not good. It seems that DB2 kept resizing them over and over again without any let up. Everything else being automatic works really well.

    Andy

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by ARWinner
    It seems that DB2 kept resizing them over and over again
    So, it works as designed. What's wrong with that?
    The bufferpools are constantly matched and optimized for the current workload.
    So STMM and set to "automatic" as much as possible.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I have no experiences with automatic buffer pool management. The question is what kind of impact the resizing has. For example, if it causes additional disk writes because dirty pages have to be flushed to disk, it may indeed not be the best choice to use this feature.

    So it comes down to some explanation from Marcus and Andy on what the issues were. At least I would be interested...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It was a while ago that I experimented with automatic bufferpools so the details are a little fuzzy. What I understand about STMM is that it will do a little tweaking here then do a little tweaking there and it eventually should reach a somewhat steady state. This seems to be what is happening with everything else, but when I turned it on for the bufferpools, it kept changing the sizes back and forth, it never really settled down. And it kept doing it several times each hour, over and over. And I think every time it did a change, I saw a slight performance hit.

    Andy

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Basically it gets down to a couple of issues.

    First, If you have more than one bufferpool, DB2 STMM will treat them equally, but usually the reason for having more than one bufferpool is to keep certain tables from flushing other tables from bufferpool memory. For example, a DBA may have decided that certain tables/indexes will always be in memory and therefore be in its own bufferpool, while other tables/indexes may have to share the remaining bufferpool(s). STMM will take away these decisions made by the DBA and if one bufferpool needs more memory, it could take it away from another bufferpool (even if the DBA has decided that other bufferpool is supposed to always have a 100% hit ratio).

    The other reason for not using STMM on bufferpools, is that many problems (including crashes) have been reported, including many I have witnessed myself. At some point I expect these to be fixed, but reason number 1 above takes precedence, and anyone who actually understands how to optimize bufferpools manually (admittedly not all DBA's do) would not want STMM taking that over in a manner that is less than optimal.
    Last edited by Marcus_A; 04-29-09 at 17:32.
    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 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Marcus_A
    Basically it gets down to a couple of issues.

    First, If you have more than one bufferpool, DB2 STMM will treat them equally, but usually the reason for having more than one bufferpool is to keep certain tables from flushing other tables from bufferpool memory. For example, a DBA may have decided that certain tables/indexes will always be in memory and therefore be in its own bufferpool, while other tables/indexes may have to share the remaining bufferpool(s). STMM will take away these decisions made by the DBA and if one bufferpool needs more memory, it could take it away from another bufferpool (even if the DBA has decided that other bufferpool is supposed to always have a 100% hit ratio).

    The other reason for not using STMM on bufferpools, is that many problems (including crashes) have been reported, including many I have witnessed myself. At some point I expect these to be fixed, but reason number 1 above takes precedence, and anyone who actually understands how to optimize bufferpools manually (admittedly not all DBA's do) would not want STMM taking that over in a manner that is less than optimal.
    how do you assign a bufferpool to a particular table? create table in TS which uses a particular bufferpool? from your experience is it a good way to improve perfomance? thanks
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  9. #9
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Marcus_A
    many problems (including crashes) have been reported
    My report (v9.5.2a on win2003 64bit): converted production database (100 GB) september 2008, use as many "automatic" parameters as I could find and it all works as designed. As the workload constantly varies, so do the sizes of the various memory objects.
    The same database is (redirect) restored on other test servers and the memory consumption is automaticly altered to fit in the smaller server. A helpfull feature is the DB2_OVERRIDE_BPF. I use this parameter to give all the bufferpools an initial value after each database start. So the large values included in the backup-image are immediatly overrulled. STMM picks it up after that. No problems.

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by MarkhamDBA
    how do you assign a bufferpool to a particular table? create table in TS which uses a particular bufferpool? from your experience is it a good way to improve perfomance? thanks
    Look up creating tablespaces
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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