Subject Re: [firebird-support] Query Performance - correlated subqueries and group by statements
Author Alexandre Benson Smith
Ann W. Harrison wrote:

>You can add more page buffers (called cache pages in some places) in a
>variety of ways - through the configuration file, using a server
>setting, or using a tool to set the cache size for a particular
>database. The size should not be larger than 10,000 until Firebird 2.
Hi People !

I always wonder the effect of SO cache caching the disk space of the
pages that are already on FB Cache. :-)

Let me try to explain better what I meant:

Suppose I have 10k page buffers and Page Size = 8K, I will have FB SS
using 80MB for cache.

Now, lets supose I have a 200MB Database and that my computer has 1GB
RAM, I will have a lot of spare RAM (that FB will eventually use on
sorts, etc.) but not for cache the pages. Probably the SO will have all
the rest of the DB pages on it's own cache, so the entire DB will be on
cache, but some pages will be double cached (by the FB internal cache
and by the SO disk cache) right ?

But, If I have a server with 256MB RAM (lets supose that 128MB is used
by the SO per si and the others services), so I have 128 MB free of RAM,
taking the same page cache numbers and page size FB will use 80MB RAM
and the SO you have only 48 MB free (that FB will eventually use on
sorts, but lets ignore it for a while). Lets suppose that the SO will
use 40MB of the remaining 48 for disk cache, the disk clusters that will
be held in memory will be the more recent accessed (that will match to
the disk space used by the most recent pages acessed by FB, which are
the same pages that will be on FB cache).

This will not be a double cache ? This will not be just a waste of memory ?

The case will be even worse if one use the CS version, lets get back to
the server with 1GB RAM:
Lets suppose I have 20 connections each connection uses 40MB of RAM (5K
pages buffer * 8kb of page size). The 20 connections will use 800MB RAM,
lets assume SO will use more 256MB for disk cache and that I have a
800MB DB. Each connection will have 5% of total DB pages on it's
individual cache, and SO will keep 30% of the DB pages on RAM (that will
be probably the same pages already cached by FB), so in essence I will
have about 30% of my DB in RAM. If I used a small page cache (lets
assume the default 75 pages used on classic) the total memory used by FB
cache for all 20 connections will be about 12 MB for it's cache, each
connection will hold just 0.07% of the database pages in RAM, but the SO
will have more 788MB of RAM to use as disk cache, the 788MB will be
enough to hold the entire database in RAM. Taking this as correct If I
use 5000 pages buffers I will have just 30% of my DB in RAM, but if I
reduce the page buffers to 75 I will have 100% of the DB in RAM and a
bit more of free RAM to be used by sorts, etc.

I'd like to know if anyone knows what will be the best choice, use a
small page buffer size and let the SO cache the DB or use a greater page
size and let FB cache the DB (even it will cache the same page a lot of
times - for each connection) and the SO double cache it ?

Does anyone has any numbers or experience in this kind of tunning ?

Did I understand right how the both caches works together ?

Any hints or links for a deep study in such a case ?

thank you !

see you !


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