BI Clusters

[danishgambit]

Morning 'Talkers,

hoping for some advice on BI Clusters.

Did some checking recently, and found we had 2,017 BI clusters (each of 512kb) on one of our databases, and 42 BI clusters (each of 2,096kb) on another.

Just wondering what sort of numbers you'd be hoping to see on your databases. I'm pretty sure 2,017 is very, very high, but not sure if 42 is excessively high. I expect that 4 would be the ideal, but any thoughts welcome.

TIA,

DG.

 

[RealHeavyDude]

You can tune the BI cluster size ( not to be confused with the BI block size ) to tune the checkpoint interval. If the interval between two checkpoints is too short you will most likely have buffers flushed when a checkpoint is performed - and - believe me, you don't want that to happen.

Therefore setting the BI cluster size individually per database does make sense. The values 512 and 2048 for BI cluster size are not exactly large - depending on your transaction load. Usually I set them not smaller than 4096, often a high as 16392 ...

BI clusters get re-used as soon as there are no "open" transaction notes within that cluster. If your BI file is very large ( many clusters ) then this is an indicator for long running transactions or an extremely high transaction load.

But it all depends, the numbers only do make sense when you know the application and know what's going on at the time you took the sample.


Regards, RealHeavyDude.

 

[danishgambit]

Morning 'Dude,

thanks for the prompt response.

Yep, I was looking to tune the cluster size as we are seeing some small checkpoint intervals during our peak usage times. I know there are few absolutes with respect to tuning, but I was after some opinions on whether having 40'ish BI clusters would be something that would bother DBA's. We're seeing intermittent performance dips and I was wondering if these could be down to the time taken for the Databases to create the new clusters.

Cheers,

DG.

 

[RealHeavyDude]

As I already mentioned, tuning the BI cluster size does make sense.

Checkpoints are the mechanism the database utilizes to ensure from time to time that the contents of the shared memory and the files on disk are in sync. The checkpoint interval is determined by the transaction load and the BI cluster size. Whenever a BI cluster fills up a checkpoint is performed. This checkpoint is a asynchronous operation during which the modified buffers are flushed to disk - if there are any. Asynchronous operation also means that during this operation every other process on the database is blocked until the checkpoint operation is done. Therefore one is interested that the checkpoint interval is not unreasonably short and that there no buffers flushed when a checkpoint occurs. An indication for having an issue with checkpoint intervals is when the database is performing really well but from time to time it just freezes. Usually you would see this if one would just increase the buffer pool so much that the transaction load increases so much that your checkpoint interval becomes to short ...

Other factors here besides the BI cluster size are the asynchronous page writer and the before image writer which come with the enterprise database license. Their only job is to constantly flush modified buffers to disk in the background so that when the checkpoint occurs nothing needs to be flushed. If the checkpoint interval is reasonably long then they have enough time to do a good job.

That's why you should always have the background writers running and tune the BI cluster size to a reasonably large value. The exact value that fits your needs can only be determined during tuning it up until you don't see any flushed buffers at the checkpoints and the interval is longer than 1 or 2 minutes.

Regards, RealHeavyDude.