STMM normally maintains a minimum number of normal bufferpool
pages (1000 at this time). This is on top of any configured
block page area, which STMM does not tune. On database
activation, scaling back may occur when memory is constrained.
This process only attempts to maintain an overall size of 1000
for a bufferpool, which may compromise the configuration when
there is a block page area configured. The following conditions
may result in performance degradation and SQL1218 errors.
1. The bufferpool may initialize with a very small number of
normal pages (as low as 32). STMM will try to increase this to
the normal minimum size on the next tuning cycle.
2. The block page area may be initialized with less than the
configured value. This condition will persist during the
current activation as STMM does not tune the block page area.
3. In an STMM/HADR environment, STMM does not actively tune the
secondary. If the scaling back occurs on the secondary, the
condition of having a small number of normal pages will persist,
potentially leading to failures hadr replay processing,
resulting in the secondary database marked bad. Since STMM is
not tuning, any scaling back on the secondary will also be
persistent (configuration changes are written to disk and will
be effective on the next activation).
Example db2diag.log messages showing scaling back with a block
page area configured:
2012-10-30-10.03.31.350919-240 I31939A505 LEVEL: Info
PID : 8585406 TID : 1814 PROC : db2sysc
0
INSTANCE: mcornish NODE : 000 DB : TEST
APPHDL : 0-42 APPID:
*LOCAL.mcornish.121030140331
AUTHID : MCORNISH
EDUID : 1814 EDUNAME: db2agent (TEST) 0
FUNCTION: DB2 UDB, buffer pool services,
sqlbScaleAutoBPsByFactor, probe:35
MESSAGE : Altering bufferpool "BP1" From: "20000"<automatic> To:
"1000"<automatic>
2012-10-30-10.03.31.351426-240 E32445A837 LEVEL: Warning
PID : 8585406 TID : 1814 PROC : db2sysc
0
INSTANCE: mcornish NODE : 000 DB : TEST
APPHDL : 0-42 APPID:
*LOCAL.mcornish.121030140331
AUTHID : MCORNISH
EDUID : 1814 EDUNAME: db2agent (TEST) 0
FUNCTION: DB2 UDB, buffer pool services,
sqlbSetNumSlotsAndNBPForNewRTBPDef, probe:20
MESSAGE : ADM6069W Buffer pool "BP1" (ID "2") has a
NUMBLOCKPAGES value of
"10000" which exceeds the maximum supported value for
a buffer pool
of this size. The NUMBLOCKPAGES value for this buffer
pool has been
reduced to "980" for its current activation. The
value of
NUMBLOCKPAGES can be permanently changed by using the
ALTER
BUFFERPOOL SQL statement. |
