Tuning AI and BI

[ron]

We use Solaris 8 and Progress 9.1d07. (DB is 150 GB, 8K DB blocksize, 16K blocksize for both AI and BI.)



Progress documentation discussing performance and VST information says this:



Compare Before Image Writer (BIW) writes to total BI writes Your BIW should be doing 60+ percent of the writes. .... NOTE: If you are using AI then your AIW will also do a large number of BI writes for you. This is fine.



These figures are typical on our system when online users are on during the day (for a 15 minute sample period):



Total writes to AI: 643

Total writes by AIW: 385 ... (which is 59.9%)

Partial AI writes: 643

Total writes to BI: 818

Total writes by BIW: 350 ... (which is 42.8%)

Partial BI writes: 432


Writes by the BIW never get to the 60% that Progress says we should. Progress says that if you're below 60% then increase the cluster size or block size. Well ... the block size is already at the max. The cluster size is 16.7 MB (not KB) ... so I can't see how the cluster size can be realistically increased.
But I don't know what to make of the comment "NOTE: If you are using AI then your AIW will also do a large number of BI writes for you. This is fine.". How can I quantify this?

Can anyone tell me how to improve the percentage of BIW writes?

Incidentally ... the Partial AI writes is ALWAYS identical to the Total AI writes. Can anyone explain that? It seems to me to be mathematically a contradiction!

Ron.

 

[toby.Harman]

You always ask the interesting ones Ron!

THIS IS NOT A RECOMMENDATION!!
To increase the percentage of BIW writes, go into the R&D options of Promon and start tinkering with the BIW tuning options. Whether or not this will help is a matter of trial and error. I've never found it necessary to do this.

The second question is easier. A Partial AI write means that the AI block is only partially filled before one of the processes forces a flush. The best way I can think of to reduce this is to reduce the AI (and therefore BI!) blocksize. Have a try with an 8K AI and BI blocksize but keep the cluster size large. You may need to go smaller than this to achieve much. It is largely a function of smaller transaction sizes. If it is not causing a problem and taking the BI/AI blocksize down does cause a problem, then leave it alone!

 

[ron]

Thank you Toby for your ever-helpful input.

I am still left with a quandry about the BI writes, however.

I have 818 total writes to the BI ... and I know that 350 of them are done by the BIW. Apparently "some others" are done by the AIW ... and what're left are the "problem ones". But I have no way to quantity this. For all I know the AIW might be doing ALL the rest ... in which case I have a "perfect" result. But the the AIW might be only doing 1 ... and so I have a "poor" result. But I don't know.

 

[ron]

I still have a problem !

Now: we use Solaris 8 and Progress 9.1D09. (DB is 210 GB, 8K DB blocksize, blocksize for both AI and BI just reduced from 16K to 8K. The -Mf broker parameter is not set, so it is always the default value of 3.)

We have just tried Toby's suggestion of reducing the AI and BI blocksize. It has not changed the number of AI partial writes (still between 99.0% and 99.9% of total AI writes). It did change the situation with the BI file just a little: the AI partial writes percentage has dropped from around 50% - 60% -- down to around 40%. A reduction, certainly, but nothing to get excited about.

I am really frustrated about this problem. It makes no sense to me at all that 99.9% of all AI writes are partial writes. Nor does it make sense that 40% of all BI writes are partial writes. Our system has over 200 users and the system is "very busy" during the day. According to my understanding of what Progress say, a BI buffer will not get FORCED to a disc write until a transaction end-note has waited in the buffer for 3 seconds. My statistics tell me that (during the day) a BI write happens (on average) every 0.60 seconds. Granted that they won't be spead completely evenly, but I still can't understand why so many buffers are written before filling-up.

My next tack is to see what happens if -Mf is set higher than 3. I note that many pundits advise against setting -Mf higher than 3. Soon I will be able to evaluate their advice!

If anyone has delved into this area -- tuning the AI and BI -- I'd appreciate any of your experiences.

Ron.

 

[ddavis]

So are AI and BI writes becoming a bottleneck for db performance? How have you measured them to be a problem?

Do you have the ai and bi extents on their own spindles?

I'm not sure you can tune down to the level you're talking about, but I'm also not sure what useful performance gain you would get even if you could.

 

[ron]

Tuning never stops.

Yes, indeed the AI and BI are on dedicated, separate, spindles.

Like lots of others, I'm sure, my company wants "lots" out of their servers -- but can't afford to spend too much on H/W. We are constantly tuning and looking for ways to squeeze just a bit more out of the system. To date it has been very successful.

AI and BI are not at all what I would call bottlenecks, but they contribute to the overall performance state.

In our situation "any" contribution to helping performance is useful.

This particular avenue I'm pursuing may not end up being successful -- but that won't stop me trying.

Ron.