Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197

    Red face Unanswered: Bufferpool Hit ratio is very low

    Hi,

    I could see one of my Buffer Pool hit ratio is very low say below 10 %, And when i see to which Tablespace the bufferpool is associated, i found all are default tablespaces ... like USERSPACE1, TEMPSPACE1...

    When i check the table related to USERSPACE i found 5 tables in that.. All are application created and populated by appliction..

    So in this case what should i do to bring the performance level high in those bufferpools..

    I have problem if i move those tables from USERSPACE1 to other TBS .... application is unable to interact.

    Kindly suggest

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    1. Provide output from "select * from syscat.bufferpools"
    2. Provide total server memory installed, and memory in use (depends on your operating system as to how to do this)
    3. Provide number of other databases on the same server, and approximate size of each in GB (can be rough estimate of size).
    4. Provide output from db2level.
    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
    Aug 2010
    Location
    Singapore
    Posts
    197

    Red face

    Hi Marcus, Thanks for your response.

    1.
    [db2inst1@WESSPTDW01 ~]$ db2 "select * from syscat.bufferpools"

    BPNAME BUFFERPOOLID DBPGNAME NPAGES PAGESIZE ESTORE NUMBLOCKPAGES BLOCKSIZE NGNAME
    -------------------------------------------------------------------------------------------------------------------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ------ ------------- ----------- --------------------------------------------------------------------------------------------------------------------------------
    IBMDEFAULTBP 1 - 30000 4096 N 0 0 -
    ITMBUF8K 2 - 10000 32768 N 0 0 -
    BUFPB8K 6 - 15000 8192 N 0 0 -
    BUFPOOL_GEM 7 - 25000 32768 N 0 0 -

    4 record(s) selected.

    [db2inst1@WESSPTDW01 ~]$


    2.
    Total Memory : 32 GB, Memory Used : 30 GB

    3.
    No other DB, 1 instance and 1 DB

    4.
    [db2inst1@WESSPTDW01 ~]$ db2level
    DB21085I Instance "db2inst1" 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".

    [db2inst1@WESSPTDW01 ~]$

  4. #4
    Join Date
    Jan 2010
    Posts
    335
    Hi,

    you have roughly 1 GB allocated for the Bufferpools! Seems to be way to less...

    What is the size of your Database? (db2 "call get_dbsize_info(?,?,?,-1)")
    Is it a dedicated DB-Server or is there also the Application running?

    If you don't have expierence with tuning your DB, then you should consider to activate STMM for the Instance/DB.
    Also FixPack 0 is outdated, you should update your DB2.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You must upgrade to a new fixpack. Fixpack 4 is the latest and is recommended. FP 0 has too many memory related bugs.

    Your current bufferpools only are using less than 1.5 GB so you should be able to increase them to about 15 GB total (assuming you have more than 15 GB of data and indexes). I assume that the 30 GB used now on your server is mostly for file system caching by Linux. Upgrading to FP 4 will help control this.

    You might also want to set the following parameters to fixed values with at least these values (higher is OK), since STMM does not always work well:

    db2 update db cfg for <db-name> using LOCKLIST 16384
    db2 update db cfg for <db-name> using SHEAPTHRES_SHR 500000
    db2 update db cfg for <db-name> using SORTHEAP 100000

    Keep your database activated (bufferpool memory allocated) at all times by activating the database (not necessary if you have HADR or always have at least one persistent connection):
    db2 activate db <db-name>

    Do not measure bufferpool hit ratio unless the database has been continuously activated (explicitly, or with at least one connection) for a long time, as bufferpools are empty when database is first activated.

    Lastly, I usually recommend that data and indexes have separate bufferpools, but that only works if you have them in different tablespaces, since bufferpools are assigned to a particular tablespace.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    nvk,

    to get my DB size, i get following error,

    db2 => call get_dbsize_info(?,?,?,-1)
    SQL0443N Routine "*IZE_INFO" (specific name "") has returned an error
    SQLSTATE with diagnostic text "SQL0727 Reason code or token: 1,-440,42884,
    SNAPSHOT_CNTRFS". SQLSTATE=38553
    db2 =>

    It is a dedicated DB server, And i shall update the fix pack to FP4.

  7. #7
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    Marcus,

    Current db cfg values are
    [db2inst1@WESSPTDW01 ~]$ db2 get db cfg for warehous |grep -i LOCKLIST
    Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(4096)
    [db2inst1@WESSPTDW01 ~]$ db2 get db cfg for warehous |grep -i SHEAPTHRES_SHR
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(264)
    [db2inst1@WESSPTDW01 ~]$ db2 get db cfg for warehous |grep -i SORTHEAP
    Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(52)
    [db2inst1@WESSPTDW01 ~]$

    Shall i go ahead and increase it ?

    In this do i need to change it to manual and then alter it ?

    In this case index is also in a same Buffer pool i will change it to new buffer pool..

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The parameters I mentioned are being managed by STMM (self tunning memory manager) which often does not work well, especially on DB2 Linux, and especially with fixpack 0. You just need to replace "automatic" with the values I supplied above for each of the parameters (you have plenty of server memory to do this). Then restart instance if possible (or at least make sure database is deactivated and activated for changes to take affect).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    Marcus,

    I will update the changes mentioned, and come back with the result of how the performance changed...

    thanks for your guidence

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Don't forget what I said about bufferpools being emptied when there are no connections to the database. All the other things I mentioned should help, but if you don't have at least one connection to the database at all times, then the bufferpools start out empty for the first connection. You can explicitly activate the database to resolve this.
    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
  •