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.
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
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.
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.