Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2010
    Posts
    67

    Unanswered: bufferpool hit ratio

    Hi All,

    we are using db2 v9.7.0.7 and aix 7.1.

    on one of production database the bufferpool hit ratio in -1.2 (please notice it is minus 1.2) and it is configured as AUTOMATIC and STMM is ON . the most active table in database has CLOB column.

    is some thing wrong with bufferpool configuration ? if yes how can I fix that ?

    Thank you for your help in advance

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    LOB columns (BLOB, CLOB, etc) cannot use bufferpools, so they require synchronous disk I/O for SQL statements. This can be mitigated to some degree by using the INLINE keyword for the column definition. If you INLINE your LOB column, you will probably want to put the table in a 32K tablespaces to allow the inlined amount to be as large as possible.
    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
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    What's the result of the following query:
    Code:
    with a as (
    select 
      (POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS + POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS + POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS) L_READS
    , (POOL_DATA_P_READS + POOL_TEMP_DATA_P_READS + POOL_XDA_P_READS + POOL_TEMP_XDA_P_READS + POOL_INDEX_P_READS + POOL_TEMP_INDEX_P_READS) P_READS
    , BP_NAME
    from table(mon_get_bufferpool(null, -1))
    where bp_name not like 'IBMSYSTEMBP%'
    )
    select 
      L_READS
    , P_READS
    , DEC(L_READS - P_READS, 29)/nullif(L_READS, 0) BP_HR
    , BP_NAME
    from a
    You can place all not inlined LOBs of a table to a different tablespace if you use DMS tablespaces.
    Then you can turn on file system caching namely for this particular tablespace to make the OS cache your LOBs.
    Regards,
    Mark.

  4. #4
    Join Date
    Jan 2010
    Posts
    67
    Thank you mrk.b and Marcus

    As far as I know inlined feature is working just for clob/blob with size <=32k in my case the clib size is 126k.

    already the table is separated in 16k page tablespace.

    I ran the query that have sent by mark and the result for two BP (one of them is default)

    908321567 896246985 0.01 IBMDEFAULTBP
    73367363 68824632 0.06
    BP16KBP

    as you can see the default is 0.01 and the one for that particular table is .06

    if I replace a fix value instead of automatic is a good idea ? please advise

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by mars View Post
    Thank you mrk.b and Marcus

    As far as I know inlined feature is working just for clob/blob with size <=32k in my case the clib size is 126k.

    already the table is separated in 16k page tablespace.
    In a lot of applications, even if a CLOB column is defined as a certain maximum length, most of the instances of the data in that column are significantly smaller and usually fit in the INLINE amount. However, in your application, if every (or even almost all) of the values in your CLOB column are over 32K, then my suggestion will not help.

    Using INLINE will put the first 32K (or whatever you define as the INLINE amount to be) in the same place as the rest of the row (and therefore uses the bufferpool for the data) and the rest of the LOB data goes to the space used for LOB columns (which is a separate area of the tablespace or a different tablespace if you used the LONG option for the CREATE TABLE). If you decide to use INLINE, using a 32K tablespaces will allow you to make the INLINE amount almost 32K, instead of almost 16K.
    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
    Jan 2010
    Posts
    67
    so if I use INLINE option, I should not worry about data truncate for rows with clob bigger than 32k and data integrity , am I right ?

  7. #7
    Join Date
    Jan 2010
    Posts
    67
    Hi Mark.b,

    why I get hit ratio < 1 for all databases when I use the query that you sent to me, I think something wrong with that. could you please check the query?

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think hit ratio should be less than 1.
    If 99 percent of your request were hit(found in bufferpool), hit ratio would be "0.99"(< 1).

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by mars View Post
    so if I use INLINE option, I should not worry about data truncate for rows with clob bigger than 32k and data integrity , am I right ?
    Data will not be truncated. If all the data cannot fit in the INLINE part, the rest is stored in the normal LOB location, and all the data will be returned to the application. But it only makes sense to use INLINE if a significant percent of the LOB columns will actually completely fit in the INLINE portion.
    Last edited by Marcus_A; 01-18-14 at 23:13.
    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
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by mars View Post
    why I get hit ratio < 1 for all databases when I use the query that you sent to me, I think something wrong with that. could you please check the query?
    The query should be OK.
    The same expressions are used in the SYSIBMADM.MON_BP_UTILIZATION view.
    You can check it by
    Code:
    select text 
    from syscat.views 
    where viewschema='SYSIBMADM' and viewname='MON_BP_UTILIZATION'
    You should check if your bufferpool has an adequate size to serve the tablespaces assigned to it.
    The current size of your bufferpool can be checked as below:
    Code:
    select m.bp_cur_buffsz*b.pagesize/power(2, 20) bp_size_mb
    from table(mon_get_bufferpool('IBMDEFAULTBP', -1)) m
    join syscat.bufferpools b on m.bp_name=b.bpname
    Then you can get the same hit rations for each tablespace assigned to this BP:
    Code:
    with a as (
    select 
      (POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS + POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS + POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS) L_READS
    , (POOL_DATA_P_READS + POOL_TEMP_DATA_P_READS + POOL_XDA_P_READS + POOL_TEMP_XDA_P_READS + POOL_INDEX_P_READS + POOL_TEMP_INDEX_P_READS) P_READS
    , m.TBSP_USED_PAGES*b.pagesize/power(2, 20) tbsp_size_mb
    , m.TBSP_NAME
    from syscat.bufferpools b
    join table(mon_get_tablespace(null, -1)) m on b.bufferpoolid=m.TBSP_CUR_POOL_ID
    where b.bpname='IBMDEFAULTBP'
    )
    select 
      L_READS
    , P_READS
    , DEC(L_READS - P_READS, 29)/nullif(L_READS, 0) BP_HR
    , TBSP_SIZE_MB
    , TBSP_NAME
    from a
    Using the latest 2 queries you can realize the following things:
    - you can get the "problem" tablespaces with "bad" HR
    - if sum(tbsp_size_mb) is much greater than bp_size_mb you should enlarge your bufferpool size
    Regards,
    Mark.

  11. #11
    Join Date
    Jan 2010
    Posts
    67
    Thank you Marcus_A/marl.b for your comment and really appreciate


    Marcus
    for getting the percentage length of row do I nee you max(length (col-name)? right?

    mark.b

    the bufferpool is Aotomatic and STMM is on do you suggest I change to fix value ?

  12. #12
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by mars View Post
    mark.b

    the bufferpool is Aotomatic and STMM is on do you suggest I change to fix value ?
    As I've already said it's better to run both those queries first.
    And you should double check if STMM is really on:
    Code:
    db2 get db cfg show detail | grep SELF
    You might set STMM to ON but haven't reactivated your database afterwards.
    Otherwise it looks very strange: if you really have STMM ON, then STMM should try to enlarge these BP's with very bad HR.
    The reason why it doesn't do this might be due to insufficient memory in the OS or "low" hard limit for the INSTANCE_MEMORY dbm parameter - check it as well...
    Regards,
    Mark.

  13. #13
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by mars View Post
    Hi All,

    we are using db2 v9.7.0.7 and aix 7.1.

    on one of production database the bufferpool hit ratio in -1.2 (please notice it is minus 1.2) and it is configured as AUTOMATIC and STMM is ON . the most active table in database has CLOB column.

    is some thing wrong with bufferpool configuration ? if yes how can I fix that ?

    Thank you for your help in advance

    What is the actual size of your BP and prefetch size? I've seen a weird case where STMM increased the prefetch size and shrink the BP so that prefetch became larger than the BP:

    https://groups.google.com/forum/#!searchin/comp.databases.ibm-db2/lennart$20negatiive$20hitratio/comp.databases.ibm-db2/sYBIn-Gc8yw/MRy3Ci9RfIUJ

    Logical reads then resulted in several physical reads which in turn lead to negative BP hitratio. Not saying that this is the case here, but it might be worth investigating.
    --
    Lennart

Posting Permissions

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