MESSAGE : ADM1822W The active log is being held by dirty pages. This is not an error, but database performance may be impacted. If possible, reduce the database work load. If this problem persists, either decrease the SOFTMAX and/or increase the NUM_IOCLEANERS DB configuration parameters.


I am seeing these warning messages in one of our databases which is in DB2 V9.7 on AIX platform. I have alternate page cleaning turned on for this database.
DB2_USE_ALTERNATE_PAGE_CLEANING=ON

Settings for some related parameters

db2 get db cfg for ypmtest| grep -iE 'softmax|logfilsiz|logprimary|logsecond|num_ioclea ners'
Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(9)
Log file size (4KB) (LOGFILSIZ) = 12800
Number of primary log files (LOGPRIMARY) = 16
Number of secondary log files (LOGSECOND) = 10
Percent log file reclaimed before soft chckpt (SOFTMAX) = 520

Rest of the memory configuration parameters are set to automatic and self tuning is turned on.


High water mark memory usage is also less than the memory limit

$ db2pd -dbptnmem

Database Partition 0 -- Active -- Up 13 days 06:18:32

Database Partition Memory Controller Statistics

Controller Automatic: Y
Memory Limit: 3355444 KB
Current usage: 1625536 KB
HWM usage: 2512896 KB
Cached memory: 424192 KB

Individual Memory Consumers:

Name Mem Used (KB) HWM Used (KB) Cached (KB)
================================================== ======
APPL-YPMTEST 160000 160000 153344
DBMS-teypmxp 66624 66624 11072
FMP_RESOURCES 22528 22528 0
PRIVATE 49536 208832 10368
DB-YPMTEST 1326208 2171584 249408
LCL-p1122318 128 128 0
LCL-p1097804 128 128 0
LCL-p647224 128 128 0
LCL-p622690 128 128 0
LCL-p1487054 128 128 0



Why do I still see those error messages when everything is set to automatic ?? Does softmax parameter still have its affect inspite of the fact that alternate page cleaning is turned on ? If so, would reducing it make the error go away ?

Or Do I need to consider reducing the logfilesz ?

Any help to tune this performance issue would be greatly appreciated.

According to the documentation, SOFTMAX still has an influence. Your softmax is set to need 5.2 log files for recovery, which is about 30% of your total. I think you should reduce it. I do not know for sure if it will fix the situation, but I think of the two solutions presented in the warning message, this should be more promising.

Andy

Proactive page cleaning


Based on the explaination in the above link, I was presuming that SOFTMAX doesn't have any affect in page cleaning. Can you please send me the documentation which you were refering

I was using the same documentation page. In section 2 it states that it uses softmax to determine the LSN gap. Then it states that the alternate method proactively cleans the pages in the LSN gap and those will will be there soon. I took this to mean that softmax is still being used.

Andy

Okay. I'll try reducing the softmax and see if it helps. But can someone suggest any monitoring elements related to the error message I am noticing.

I think we can monitor it using "Log accounted for by dirty pages (Bytes) " . But not exactly sure what that value needs to be based on our settings.

I found the reason why I was getting the error message. This is due to an application which got struck and is in executing phase for more than two days. Deatiled post is in here.

force application not terminating the APP