Checkpoint record?

[drunkahol]

Today I checked a database at a client that has been running happily for around 4 years.

It is averaging just over 4 checkpoints per MINUTE during it's 20 hour uptime each day.

Two things -

(1) is this a record?
(2) how the hell do I reduce it since the client already has APW's and BIW's running?

I've tuned checkpoints successfully in the past, but never come across a problem quite as bad as this.

The database is 8.3B running on HP-UX 10.something

The database is a planning database and does a HUGE amount of record updates throughout the day which we cannot (I am told) avoid.

Thanks

Duncan

 

[toby.Harman]

Increase the BI cluster size will reduce the # of checkpoints.

However it will reach a point where it starts to become an overhead during startup/truncate operations.... Don't just set it to 256MB and walk away - That will give you 1GB BI file and that takes time to prepare at startup etc.

If you take your current BI cluster size (which you should be able to find in PROMON or the DB log) and multiply by 4 (ideally we would like about 1 checkpoint per minute). If the result id less than or equal to 64MB then you should be ok to set it. You should also consider setting up a fixed extent to your BI file which is about 6x the cluster size so that you have space for the 4 initial clusters that will be created after truncating.

How many clients/# of records written per second/size of records would help in making suggestions for this one!

Toby