Db2 LUW tens of millions of rows

db2db2-luwphysical-design

We have a table with tens of millions of time series rows. We index by three foreign keys to relations. Let's hypothetically say medical observation row to doctor, acl, and person. Main thing being a 3 part key where acl is based on the user and the other two based on entity relationship.

What's a good physical architecture in db2 to make this run fast? (sub second) It currently takes 10+ seconds to query.

  • RAM usage is around 25-30%
  • Read hits to buffer pool are around -1300%
  • Log hits to buffer pool are 100% (are we doing too many transactions when we don't need to – i.e. change to read uncommitted)

I wonder about having a separate table space for this with a separate buffer pool for example.

Also would you consider partitioning?

Best Answer

I'm curious about your statement: read hits to bufferpool are around -1300%. Is your bufferpool hit ratio -1300% ? Can you check the hit-ratio with?

  WITH BPMETRICS AS (
    SELECT bp_name,
           pool_data_l_reads + pool_temp_data_l_reads +
           pool_index_l_reads + pool_temp_index_l_reads +
           pool_xda_l_reads + pool_temp_xda_l_reads as logical_reads,
           pool_data_p_reads + pool_temp_data_p_reads +
           pool_index_p_reads + pool_temp_index_p_reads +
           pool_xda_p_reads + pool_temp_xda_p_reads as physical_reads,
           member
    FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
   SELECT
    VARCHAR(bp_name,20) AS bp_name,
    logical_reads,
    physical_reads,
    CASE WHEN logical_reads > 0
     THEN DEC((1 - (FLOAT(physical_reads) / FLOAT(logical_reads))) * 100,7,2)
     ELSE NULL
    END AS HIT_RATIO,
    member
   FROM BPMETRICS;

If it is indeed negative, there are more physical reads than logical reads. Several physical reads per logical read is weird, one possible cause is that the bufferpool is so small that it cant hold the EXTENTSIZE for the tablespace in question.

One other thing that comes to mind is if Db2 failed to start the normal bufferpool and instead uses the default bufferpool - which is very small - for that pagesize.

Related Question