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 withgfix
. - 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 withCONNECT
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, placeCACHE
<n> after the database name. The followingCONNECT
specifies 500 buffers for the database pointed to by theEMP
handle:
EXEC SQL CONNECT EMP CACHE 500;
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; . . .