Setting Database Cache Buffers

Go Up to Opening a Database

Besides opening a database, CONNECT can set the number of cache buffers assigned to a database for that connection. When a program establishes a connection to a database, InterBase allocates system memory to use as a private buffer. The buffers are used to store accessed database pages to speed performance. The number of buffers assigned for a program determine how many simultaneous database pages it can have access to in the memory pool. Buffers remain assigned until a program finishes with a database.

The default number of database cache buffers assigned to a database is 256. This default can be changed either for a specific database or for an entire server.

Use the gfix utility to set a new default cache buffer size for a database. See the Operations Guide for more information about setting database buffer size with gfix.
Change the value of DATABASE_CACHE_PAGES in the InterBase configuration file to change the default cache buffer size on a server-wide basis. Use this option with care, since it makes it easy to overuse memory or create caches too small to be usable.

Setting Individual Database Buffers

For programs that access or change many rows in many databases, performance can sometimes be improved by increasing the number of buffers. The maximum number of buffers allowed is system dependent.

Use the CACHE <n> parameter with CONNECT to change the number of buffers assigned to a database for the duration of the connection, where <n> is the number of buffers to reserve. To set the number of buffers for an individual database, place CACHE <n> after the database name. The following CONNECT specifies 500 buffers for the database pointed to by the EMP handle:

EXEC SQL
CONNECT EMP CACHE 500;

Note:
If you specify a buffer size that is less than the smallest one currently in use for the database, the request is ignored.

The next statement opens two databases, TEST and EMP. Because CACHE is not specified for TEST, its buffers default to 256. EMP is opened with the CACHE clause specifying 400 buffers:

EXEC SQL
CONNECT TEST, EMP CACHE 400;

Specifying Buffers for All Databases

To specify the same number of buffers for all databases, use CONNECT ALL with the CACHE <n> parameter. For example, the following statements connect to two databases, EMP, and EMP2, and allot 400 buffers to each of them:

. . .
EXEC SQL
SET DATABASE EMP = 'employee.ib';
EXEC SQL
SET DATABASE EMP2 = 'test.ib';
EXEC SQL
CONNECT ALL CACHE 400;
. . .

The same effect can be achieved by specifying the same amount of cache for individual databases:

. . .
EXEC SQL
CONNECT EMP CACHE 400, TEST CACHE 400;
. . .

Advance To:

Accessing an Open Database