Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    2

    Unanswered: Buffer pool problems

    I'm running db2 ese. Output of db2level is:
    DB21085I Instance "tpcd" uses "64" bits and DB2 code release "SQL09070" with
    level identifier "08010107".
    Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXAMD6497", and Fix
    Pack "0".
    Product is installed at "/opt/ibm/db2/V9.7".

    I've attempted to set up the tpch benchmark with a 1gb database size, which seems to have gone fine. When I run a query I get terrible performance (query 17 in particular takes roughly an hour and a half and I've been told that this should be one of the faster queries).

    I use get snapshot and see that the bufferpool (IBMDEFAULTBP) is seeing about a 40% hit rate and that it is only ~400kb. I attempt to change the bufferpool size both through the control center or by using the following instructions that I found:
    db2 -v connect to DB_NAME
    db2 -v select * from syscat.bufferpools
    db2 -v alter bufferpool IBMDEFAULTBP size -1
    db2 -v connect reset
    db2 -v update db cfg for dbname using BUFFPAGE bigger_value
    db2 -v terminate

    Neither of these seems to have any effect, db2 seems to think that the bufferpool is now larger, but if I monitor a query it is still small; nothing has changed. How do I actually change the size of the bufferpool?

    The next thing I tried was to create a new bufferpool using the command center. If I point all of my tablespaces at this new bp or create new tablespaces before loading my data and then run a query the performance is still poor. Furthermore, when monitoring a query my new bufferpool won't even show up.

    To be specific I have tried:
    db2 -v update monitor switches using bufferpool on
    db2 -v get monitor switches
    db2 -v reset monitor all
    -- run your application --
    db2 -v get snapshot for all databases > snap.out
    db2 -v get snapshot for dbm >> snap.out
    db2 -v get snapshot for all bufferpools >> snap.out
    db2 -v reset monitor all db2 -v terminate

    My new bufferpool is not listed anywhere, IBMDEFAULTBP still shows that a number of tablespaces are connected to it. How do I force db2 to use this new bufferpool?

    tl;dr
    1) What is the proper way to change the bufferpool size? my way seems broken.
    2) db2 refuses to use my new bufferpool. How do ensure that a new bufferpool is utilized?
    3) Where else should I be looking to diagnose poor performance?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by spelley View Post
    I'm running db2 ese. Output of db2level is:
    DB21085I Instance "tpcd" uses "64" bits and DB2 code release "SQL09070" with
    level identifier "08010107".
    Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXAMD6497", and Fix
    Pack "0".
    Product is installed at "/opt/ibm/db2/V9.7".
    The first thing you may want to do is update to the latest fixpak, which is 3a at this time.

    Quote Originally Posted by spelley View Post
    1) What is the proper way to change the bufferpool size? my way seems broken.
    2) db2 refuses to use my new bufferpool. How do ensure that a new bufferpool is utilized?
    3) Where else should I be looking to diagnose poor performance?
    1. The BUFFPAGE configuration parameter is deprecated. Use the ALTER BUFFERPOOL statement to modify bufferpool sizes.

    2. Use ALTER TABLESPACE. You will need to deactivate and reactivate the database for these changes to take effect.

    3. You will need to identify where the application is waiting most and eliminate the bottleneck. You can use the view MON_WORKLOAD_SUMMARY to look at the general picture, then call the various monitoring functions (MON_GET_UNIT_OF_WORK, MON_CURRENT_SQL, etc.) to drill down.

  3. #3
    Join Date
    Nov 2010
    Posts
    2
    Didn't know about the fixpacks, I believe this has fixed my problem. I'm amazed that a bug like that shipped. Thanks n_i

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It may have just reset self-tuning memory settings.

Tags for this Thread

Posting Permissions

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