A buffer pool is a portion of memory allocated by Db2 to cache frequently accessed data pages, reducing disk I/O operations and improving performance.
Troubleshooting buffer pool and tablespace issues in DB2 requires understanding the interaction between them and diagnosing specific performance or functionality problems. Here are steps and best practices for troubleshooting:
Purpose: Stores pages of data and index entries temporarily in memory.
Size: Defined in 4 KB pages (or optionally 8 KB, 16 KB, 32 KB pages).
Optimization: More buffer pool memory reduces disk reads and improves performance.
Multiple Buffer Pools: Db2 allows multiple buffer pools for different workloads.
Parameter Description
BUFFPAGE Number of memory pages assigned to the buffer pool. (Deprecated, use IBMDEFAULTBP instead)
NPAGES Number of pages in the buffer pool (-1 means Db2 determines size dynamically).
PAGESIZE Page size (4 KB, 8 KB, 16 KB, 32 KB).
BLOCKSIZE Determines block I/O size for read-ahead performance.
IMMEDIATEWRITE Controls whether Db2 writes changed pages to disk immediately.
ASSOCIATED TABLESPACES Specifies which tablespaces use the buffer pool.
Symptoms: Identify specific issues (e.g., slow queries, high disk I/O, memory contention).
Error Messages: Check for error codes in the logs or monitoring tools (e.g., SQLCODE, SQLSTATE).
Affected Objects: Determine if specific tables, tablespaces, or queries are involved.
The buffer pool acts as an in-memory cache for pages of data from tablespaces. Improper sizing or usage can lead to performance issues.
Key Checks
Buffer Pool Usage:
SELECT * FROM SYSIBMADM.BP_HITRATIO;
Hit Ratio: A low ratio indicates insufficient buffer pool sizing which lead to frequent disk I/O.
Buffer Pool Size:
SELECT BPNAME,BUFFERPOOLID,NPAGES,PAGESIZE FROM SYSCAT.BUFFERPOOLS;
Ensure NPAGES matches expected usage.
NPAGES Default number of pages in this buffer pool on database partitions in this database partition group.
-1(Computed)
-2(Automatic).
Page Size Mismatch: Tablespaces and buffer pools must have compatible page sizes (4 KB, 8 KB, etc.).
Actions
Increase buffer pool size if memory is available:
ALTER BUFFERPOOL BP1 SIZE 50000;
Add or split buffer pools for specific workloads:
CREATE BUFFERPOOL BP2 SIZE 10000 PAGESIZE 16K;
RDS Buffer Pool -
call rdsadmin.create_bufferpool('db2ntp','TBS_BP16',2500,'Y','Y',16384); ---automatic growth
RDS Create Tablespace -
call rdsadmin.create_tablespace('db2ntp','NTPTBL1SPACE','NTPTBS_BP16',16384,1000,25,'U');
Assign specific tablespaces to custom buffer pools.
Tablespaces store data and are directly tied to buffer pool performance.
Key Checks
Tablespace Utilization:
SELECT SNAPSHOT_TIMESTAMP,TBSP_NAME, TBSP_ID, TBSP_STATE, TBSP_USED_SIZE_KB, TBSP_TOTAL_SIZE_KB,TBSP_UTILIZATION_PERCENT FROM SYSIBMADM.TBSP_UTILIZATION;
High TBSP_USED_SIZE relative to TBSP_TOTAL_SIZE can lead to allocation or fragmentation issues.
Find the relavent table name belongs tablespace below queries
db2 -v "select SUBSTR(b.TABSCHEMA,1, 30), SUBSTR(b.TABNAME,1,30), rtrim(c.TBSPACE) from syscat.tables b , syscat.tablespaces c where b.TBSPACEID=c.TBSPACEID and b.TABSCHEMA in ('test') and b.TYPE='T' order by 3" | more
State of Tablespaces:
States like QUIESCED or OFFLINE indicate problems.
Automatic Storage: Check if tablespaces are configured for automatic growth. If NPAGES -2(Automatic). automatic growth set
Actions
Reorganize tables to reduce fragmentation:
REORG TABLE schema.table_name;
Extend tablespaces if nearing capacity:
ALTER TABLESPACE TS1 RESIZE (ALL 100G);
Set auto-resize:
ALTER TABLESPACE TS1 AUTORESIZE YES;
Diagnostic Tools
MON_GET_BUFFERPOOL: Provides real-time buffer pool performance.
SELECT * FROM TABLE(MON_GET_BUFFERPOOL(NULL, -1)) AS T;
MON_GET_TABLESPACE: Monitors tablespace performance.
SELECT * FROM TABLE(MON_GET_TABLESPACE(NULL, -1)) AS T;
Performance Tips
Use larger page sizes for sequential I/O workloads.
Separate buffer pools for frequently accessed tables.
Enable prefetching for tablespaces:
ALTER TABLESPACE TS1 PREFETCHSIZE 128;
Memory Limits: Ensure DB2 has sufficient memory (DB2_MEMORY_TARGET).
Disk I/O Bottlenecks: Use OS tools (e.g., iostat, vmstat) to identify high disk activity.
Lock Contention: Check for lock waits or timeouts in MON_GET_LOCKS.
db2diag.log: Check the DB2 diagnostic log for warnings or errors.
tail -f $DIAGPATH/db2diag.log
Snapshot Monitoring:
db2 get snapshot for database on <dbname>
Workload Management: Use WLM to prioritize critical queries.
Dynamic SQL Tuning: Capture and optimize slow queries via EXPLAIN PLAN.
SELECT * FROM TABLE(MON_GET_TRANSACTION_LOG(-1));
db2 "select substr(HOST_NAME,1,25) as HOST_NAME, substr(DB_NAME,1,10) as DBNAME,
MEMORY_SET_TYPE, MEMORY_SET_ID, MEMORY_SET_SIZE, MEMORY_SET_COMMITTED, MEMORY_SET_USED,
MEMORY_SET_USED_HWM from table(MON_GET_MEMORY_SET(null,null,-2)) as t with ur"
db2 "select substr(HOST_NAME,1,25) as HOST_NAME, substr(DB_NAME,1,10) as DBNAME,
MEMORY_SET_TYPE, MEMORY_POOL_TYPE, MEMORY_POOL_ID, APPLICATION_HANDLE, EDU_ID, MEMORY_POOL_USED,
MEMORY_POOL_USED_HWM from table(MON_GET_MEMORY_POOL(null,null,-2)) as t with ur"
db2 "select SUM(MEMORY_POOL_USED) as TOT_MEMORY_USED from
table(MON_GET_MEMORY_POOL(null,null,-2)) as t with ur"
SELECT * FROM SYSIBMADM.MON_CURRENT_SQL WHERE ELAPSED_TIME_SEC > 10000;
SELECT BP_NAME, DATA_LOGICAL_READS, DATA_PHYSICAL_READS,
(1 - DATA_PHYSICAL_READS / NULLIF(DATA_LOGICAL_READS, 0)) * 100 AS HIT_RATIO
FROM SYSIBMADM.BP_HITRATIO;
SELECT SNAPSHOT_TIMESTAMP,TBSP_NAME, TBSP_ID, TBSP_STATE, TBSP_USED_SIZE_KB,
TBSP_TOTAL_SIZE_KB,TBSP_UTILIZATION_PERCENT FROM SYSIBMADM.TBSP_UTILIZATION;