--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

>
>> Hmmm... the article at the link above specifically mentions IB6 and
>> both Classic and SuperServer.
>
> Claudio wrote those articles a long time ago and before we had
> access to the Services API. It could be well worthwhile to raise
> a question for him (and others) in firebird-devel about exactly
> what has transpired in four years.

Will do.

>> I've found the Params property which is a list of various
>
> Yes - but I think you will find it accesses the DPB item
> isc_dpb_num_buffers and doesn't do anything. There's a function
> isc_dpb_set_page_buffers which (from recollection) does work.

Ok, I'll try.

> >end user intervention, and I'll make sure to limit the result to
> >reasonable bounds (eg. 256-4096).
>
> 256 is too low for SS unless it's a single-user app - and
> throughput would be seriously hampered for that single user.

256 x 4096 = 1MB cache. This should be enough for small DBs that
contain just a few hundred rows in the main table, regardless of
number of users.

> It would be interesting to see what sort of algorithm you would
> use, since nobody in history has ever devised an algorithm for
> calculating the optimal buffer size. Basically, messing about

As you and other experienced DBAs advise, I tested my DB with
real-world data. I benchmarked the heaviest operations - batch import
of data and the most complicated query that I use - with all
combinations of page sizes (1K-16K) and cache sizes (256-4096).

I now have a pretty good idea of how those settings affect my
application. Clearly a page size of 4096 is optimal. Cahce size of 256
is enough for small DBs (few hundred rows in main table), medium DBs
(several hundred thousands rows) perform best with 1024-2048, and
bigger DBs (several millions rows) benefit from 4096.

So all I have to do is to scale the number of rows to a cache size in
the range 256-4096.

> Do you really understand what the database cache is used for?
> I suspect that you don't, if you are presuming that a bigger
> database needs a proportionally bigger cache.

I think that I do understand how the cache is used, and the difference
between CS and SS. I always read every piece of documentation that I
can find before I post questions. In this case, as I explained above,
I took experts' advice and tested with actual data.

> > > The right way to set the database cache override is by running
> > > gfix -buffers (or the equivalent Services Manager operation
> >
> >But that requires disconnecting all users
>
> True, but not a valid argument. Only the first connection to a
> database can modify the cache; so all users would have to log
> out, anyway, in order to effect any change. There's no question
> of "changing the cache size in mid-stream" -- it can't happen.

If the IB6 functionality is still intact, then the first connection
sets the size, and later connections can increase it (but not
decrease). That's suits me just fine. But it goes back to the question
whtether the cache size API still works.

Thanks,

Eyal.