At 05:41 PM 25/07/2003 +0300, you wrote:

>Hi guys,
>
>I have some questions regarding Firebird cache.
>
>If page size is 8192 bytes and if I set 5000 buffers for a database
>that means 40MB of cache:
>
> 1. For all connections using this database? <---On Superserver.
> 2. Cache per connection? <---On Classic
>
>I have a database with 9 connections to it and the above cache schema.
>It uses only about 50MB of RAM. I have plenty more. How can I use
>this extra RAM to work for me.

Experiment. Too much cache means you will deprive the server of memory for
its operations. Too little means the cache will be paged to disk and
defeat the purpose of having a cache.

For SS, you can make a sort of formula. Add up the total size of your
database files after a backup and restore (in bytes) and multiply that
figure by the estimated percentage of the database that's in use under
normal circumstances. This gives you an approximate figure for the cache
size. Divide that figure by the page size to get the approx. number of
buffers you need.

>I would be happy to hear any comments on using RAM efficiently.

Don't be tempted to inflate the size of the cache to try to improve
performance. If you have unused space in the cache allocation, it's memory
that's not available for the server to do its work. Database servers love RAM.

Don't leave the server logged on. Don't run screensavers. Don't use the
database server for printer serving. Don't play Quake on the network.

>One more question. How can I determine the best page size for my
>data. I use 8192 bytes per page and I am happy but actually I am not sure
>this is the best.

One that's about 125% of the size of the largest row structure that is
accessed frequently, or about 125% of a clean multiple of the largest row
structure (counting the size of the blob-id but not the size of the actual
blobs). Avoid very large varchars (use blobs instead) since the maximum
size of a page is 16 Kb, and you want to minimise the number of page reads.

heLen