Subject Re: [firebird-support] Index Caching
Author Alexandre Benson Smith
slalom91 wrote:
> Hi,
> Can someone please explain how indices are cached and how long they
> remain in the cache of FB2 Superserver? I have a query which takes 20+
> seconds initially. Subsequent identical queries executed immediately
> after the first take 1-2 seconds. After several minutes the query is
> executed again and it reverts back to 20 seconds. During this wait
> period there are other queries being executed accessing different
> pieces of information within the database. I am guessing that I just
> need to tune the amount of pages to cache, but I'd like to make sure I
> understand this clearly before making any adjustments to the config.
> Thanks.

I will give you a quick response... someone will jump in to
complete/correct the points.

The index cache work equal as a data page cache... The engine holds N
pages (no matter if index or data) in cache. It uses a MRU algorithm to
manage the cache.

If you increase the page buffers you will increase the chances that a
given page (be it index or data) stay on index.

I think you could provide your query and your plan to us, so we could
give some advices trying to improve it.

see you !

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil