Subject Re: [IBO] How to specify cache (buffer) size
Author Helen Borrie
At 02:26 PM 26/04/2005 +0000, you wrote:

>--- In, Helen Borrie <helebor@t...> wrote:
> >> I need to control the size of Firebird's page cache when
> >> connecting to a database.
> >> ...
> >> I read in
> >> that the cache size can be set at connection time. So I want to
> > There was once an API call you could use to set the cache size
> > temporarily for a connection to Classic on Unix.
>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

> > There used to be a property for it in TIB_Connection,
>I've found the Params property which is a list of various connection
>level parameters, one of which is IB_BUFFERS - could this be it?

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.

> > It's not something you should try to have a user do arbitrarily
> > with Superserver.
>Certainly. My application will calculate the cache size, without any
>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. 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 with the cache is unrewarding if you are short of resources,
since the engine will simply start using disk cache - which just makes
caching pointless.

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. On SS, all users share the cache - which
caches recently used indexes and data - so cache growth is not necessarily
factored by the number of users. However, you sure do get incremental
growth with Classic, since each connection has its own cache, allocated
statically, and caches can't be shared.

> > 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 and interrupting their work.

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.

I don't think that messing around with the cache according to some
proscriptive "tuning algorithm" will win you anything but some unhappy
users. If the system needs more memory, install more memory, or you'll get
heavy thrashing of the disk cache. If you hit the system limit and that's
still not enough, add another server.