Hi,
Currently we have a checkpoint in about every 20-30seconds. We already changed the BI-blocksize to 16KB and the clustersize to 256Mb. Which is the maximum I think. System runs fluently so no worries there.
Soon we expect some extra load. Any idea how we can reduce the checkpoints a bit. Currently running 11.6.4 on redhat
Thanks
Tjerk
During the sync time at checkpoint the transaction activity is frozen.
Is it expected for your application and/or for the end users to create 256Mb of db changes every 20-30 seconds? If yes then nothing to worry about.
Hi George,
Yes we have about 750GB of new and mainly changing data/day. Logging we already moved to another system. Sync time of the checkpoints is about 10 to 20ms which is not a problem but in some rare cases can take 1.3sec.
Are you regularly flushing buffers at checkpoint?
Very rarely
Just want to be prepared if the load gets bigger to know what I need to do.
Is it possible to increase -bibufs to reduce de buffers flushed?
> Is it possible to increase -bibufs to reduce de buffers flushed?
This parameter refers to BI buffers. Increasing it will reduce the possibility of empty BI buffer waits.
"Buffers flushed at checkpoint" refers to buffers in the buffer pool (-B) that have to be written at the end of the checkpoint processing because their contents haven't yet been written to disk by an APW.
So, how do you reduce it?
A larger bi cluster size. Or more APWs. Or faster disks.
Or code that commits fewer transactions.
> So, how do you reduce it?
Assuming you are using the Enterprise RDBMS license, you can try to reduce buffers flushed at checkpoint by ensuring that you are running an APW; you might need more than one on a very busy system with lots of update activity. Larger BI cluster size will space checkpoints farther apart, giving the APWs more time to do their job of writing dirty blocks.
Tjerk, can you run 'time proutil empty -C bigrow 508 -zextendSyncIO'?
I guess the time will be compatible with the intervals between checkpoints in your database.
Then the current bottleneck is the writes to BI file.
Ok, so, he has APW and bi cluster is 256 M. About 3 checkpoints each 5 minutes and just some flushes, that he solves with more APW?
> About 3 checkpoints each 5 minutes
It's an average interval per 160 hours (almost a week), while at the busy time: "Currently we have a checkpoint in about every 20-30 seconds".
Tjerk, during this 1.3sec checkpoint, is there hick up to apply transactions during this time frame ? or other user impact ?
During the sync time at checkpoint the transaction activity is frozen.