Configuring the Database Cache
Go Up to Database Configuration and Maintenance
Contents
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
gfixorALTER DATABASE SET PAGE CACHEto set the size for a specific database)
- Connection level: applies only to a specific
isqlconnection
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
The 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
To run gfix or 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.
A 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 gfix or SET PAGE CACHE to set cache size rather than DATABASE_CACHE_PAGES.
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;
The empty COMMIT command prompts isql to display information about memory and buffer usage. The “Buffers” line specifies the size of the cache for that database.
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.