Now that the I/O Buffer feature has been out for a little while, has anyone come up with a formula for working out the optimal buffer settings for each file?

Not being an expert on this (or an expert on anything really), I'd assume the following should be taken into account:

* File size.
* How frequently it's accessed.
* Available memory.
* And maybe others things I haven't considered.

= Steve

Steve,

I've got to get that technical article written...

<< Now that the I/O Buffer feature has been out for a little while, has anyone come up with a formula for working
out the optimal buffer settings for each file? >>

Without writing the technical article here, the main tool to use is this system information table:

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=FileIOStatistics_Table

The key columns are:

HitRatio
AvgRead
AvgWrite
TotalFlushes

You want a HitRatio of around 95% or better.  If the HitRatio is below that figure, then you need to increase the total amount of memory allocated for that particular file.  In general, I would start with looking at how much available memory is in the system, and, depending upon what the machine is used for, try to use about 80% of that memory for the I/O caching, dividing the memory up proportionally according to the size of the database files.  Just remember that whatever you specify, EDB *will* use that amount of memory as quickly as it needs to, so don't overshoot too much.

If the AvgRead or AvgWrite is small (16K or less), then you should consider increasing your block size.  The default of 4K is actually quite a bit too small, and I typically use 64K and get good results, especially if the file is fairly large (500MB or more).  Ideally, you want your average write to be up in the multiple-MB range.  The other item that you can look at for the average write is the TotalFlushes column.  If the average write is too small, it may be because your flush intervals are too small and dirty buffers are being flushed too quickly before any large amount can aggregate.  Of course, it could also be simply because the system isn't very busy, which is why you need a human to look at these parameters and understand the context.

So, the steps would be to enable the I/O buffering with the default settings (or with some settings that are good estimations), and then fine-tune the settings after letting the system run for a while.  This is one of the benefits of the I/O buffering - if you do it right, then the performance of the system will keep increasing and increasing until it reaches a nice optimal level, staying there until the EDB Server is shut down/restarted.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thank you for the detailed explanation.  That's going to come in really handy.

<< Of course, it could also be simply because the system isn't very busy, which is why you need a human to look at these parameters and understand the context. >>

This part would be difficult and, in my view, impractical when there are thousands of installations.  I'm trying to come up with an automated way of adjusting the settings to as close as I can to optimal without requiring human intervention.

= Steve

Hi Tim,

Just to clarify, is a 100% hit ratio represented as 1.00?

= Steve

Steve,

<< This part would be difficult and, in my view, impractical when there are thousands of installations.  I'm trying to come up with an automated way of adjusting the settings to as close as I can to optimal without requiring human intervention. >>

Just setting the I/O buffering memory sizes to something that is reasonable for the installed system along with a large block size should get most of the way there without worrying too much about the avg. write size.

Tim Young
Elevate Software
www.elevatesoft.com

Steve,

<< Just to clarify, is a 100% hit ratio represented as 1.00? >>

Yep.

Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim.

= Steve