1. Run
promon db-name with the database running
2. Type
R&D, Select: Option 3. Other Displays > Option 4. Checkpoints
3. If the Len is less than 3-5 mins over an extended duration, the checkpointing, or writing of modified buffers to disk, is happening too frequently and causing a performance hit.
4. Make sure a BIW and a few APWS are started on database startup (Enterprise license only):
pro apw
pro apw
pro biw5. If the bi cluster size is below the default of 512K, increase it to increase the time between checkpoints.
For example:
If the average length of time between any two checkpoints were found to be 30 seconds apart in the LEN column of the above PROMON Checkpoint output, and you would like to improve this by making the bi cluster take an average of 120 seconds to fill up, then 120/30 = 4. You would therefore need to increase the current cluster size, up to 4 times the current size to increase the time it takes to fill the cluster by 4 times.
The bicluster size is changed offline through the following command syntax:
$ proutil db-name -C truncate bi -bi <size>
Where:size = Specifies the size of the bicluster in kilobytes. The number must be a multiple of 16 ranging from 16 to 262,128 (16K to 256MB). The default cluster size is 512K. If you use a value that is not a multiple of 16, the value is rounded up to the next multiple of 16.
If after-imaging is enabled, the aiblocksize needs to be equal to the biblocksize for performance reasons. Why? because the related notes in a bi block must be flushed before an ai block can be flushed.
