Configuring the Database Cache
The database cache consists of all database pages (also called buffers) held in memory at one time. Database cache size is the number of database pages. You can set the default size of the database cache at three levels:
- Server level: applies to all databases
- Database level: applies only to a single database (using
ALTER DATABASE SET PAGE CACHEto set the size for a specific database)
- Connection level: applies only to a specific
We recommend setting cache size at the database level rather than at the server level. This reduces the likelihood of inappropriate database cache sizes.
Every database on a server requires RAM equal to the cache size (number of database pages) times the page size. By default, the cache size is 2048 pages per database and the page size is 4KB. Thus, a single database running at the default setting requires 8MB of memory, but three such databases require 24MB of memory.
Default Cache Size Per Database
buffers parameter of the
gfix utility sets the default number of cache pages for a specific database:
gfix -buffers n database_name
This sets the number of cache pages for the specified database to <n>, overriding the server value, which by default is 2048 pages.
The default size for a database can also be set using the ALTER DATABASE statement:
ALTER DATABASE SET PAGE CACHE n
ALTER DATABASE, you must be either
SYSDBA or the owner of the database.
Both gfix and ALTER DATABASE immediately attempt to expand the cache buffers to the number of pages requested.
Default Cache Size Per isql Connection
To configure the number of cache pages for the duration of one
isql connection, invoke
isql with the following option:
isql -c n database_name
<n> is the number of cache pages to be used as the default for the session; <n> is trimmed to the database-specific cache setting if it is greater than that value.
CONNECT statement entered in an
isql query accepts the argument
CACHE n. (Refer to the discussion of
CONNECT in the Language Reference manual for a full description of the
CONNECT function). For example:
isql> CONNECT database_name CACHE n;
The value <n> can be any positive integer number of database pages. If a database cache already exists in the server because of another attachment to the database, the cache size is increased only if <n> is greater than current cache size.
Setting Cache Size in Applications
InterBase API: use the
isc_dpb_num_buffers parameter to set cache size in a database parameter buffer (DPB).
IBX: use the num_buffers parameter to set cache size in the TIBDatabase parameter list. For example: num_buffers=250. For the parameter to be parsed correctly, there must be no spaces around the = sign.
The number of buffers passed by the InterBase API or IBX is trimmed to the database-specific cache setting if it is greater than that value.
Default Cache Size Per Server
For SuperServer installations, you can configure the default number of pages used for the database caches. By default, the database cache size is 2048 pages per database. You can modify this default by changing the value of
DATABASE_CACHE_PAGES in the
ibconfig configuration file. When you change this setting, it applies to every active database on the server
You can also set the default cache size for each database using the
gfix or SET PAGE CACHE utilities. This approach permits greater flexibility, and reduces the risk that memory is overused, or that database caches are too small.
We strongly recommend that you use
SET PAGE CACHE to set cache size rather than
Verifying Cache Size
To verify the size of the database cache currently in use, execute the following commands in isql:
isql> CONNECT database_name; isql> SET STATS ON; isql> COMMIT; Current memory = 415768 Delta memory = -2048 Max memory = 419840 Elapsed time = 0.03 sec Buffers = 2048 Reads = 0 Writes 2 Fetches = 2 isql> QUIT;
COMMIT command prompts
isql to display information about memory and buffer usage. The “Buffers” line specifies the size of the cache for that database.
- Note: The example command listing shows "Buffers = 2048" for user to verify that cache setting has been changed. This is no longer strictly true. For very large cache buffer settings (>256MB), InterBase incrementally allocates additional cache buffers on-demand. So it is possible that the listed command will show a Buffers value that is a lower number. The actual value can always be determined by running gstat -h and examining the Page buffers entry or querying column RDB$PAGE_CACHE from system table RDB$DATABASE.