Sizing Database Cache Buffers
Go Up to Database Properties
InterBase maintains a cache in the server’s RAM of database pages currently in use. If you have a highly active database, you can gain some performance benefit by raising the default cache from its default of 2048 database pages. As with any cache system, at some point you find diminishing returns. Some experimentation with your particular application and database reveals that point.
See Configuring the Database Cache for details about server cache settings.
The ibserver process running on an InterBase server maintains a cache in memory of recently used data and index pages. Like any cache, it depends on repeated use of data on a given page to help speed up subsequent access. In InterBase SuperServer implementations, the cache is shared by all clients connected to the database.
By default, InterBase allocates enough memory for 2048 pages per database. If the page size of the current database is 4KB, then ibserver uses 8MB of memory. If the page size is 8KB, then ibserver uses 16MB of RAM for cache. The InterBase API provides a method for any individual client to request that the size of the cache be higher. You can set a property on an individual database that establishes a different default cache size when any client connects to that database:
gfix -buffers 5000 database.ib
The default of 2048 assumes that the server has a sufficient memory configuration to allocate for 8MB of RAM per database. If memory is less plentiful on your server, or you have many databases that require simultaneous access, you might need to reduce the default number of cache buffers.
It is highly recommended to increase the cache size for a database if you have enough memory to accommodate it. Consider the following points:
- It is not useful to raise the cache size so high that the memory used by
ibserverstarts to page into virtual memory. That defeats the benefit of caching data from disk in memory. - It is not useful to raise the cache size higher than the number of pages in the database (which you can view with View Database Statistics in IBConsole, or with the
gstatcommand-line program). There is no benefit to this, since any given page from disk occupies only one page in the cache, and is not duplicated. - One block of memory is allocated for cache per database. If a client connects to two separate databases on one server, the
ibserverprocess maintains two separate cache areas of memory. For example, ifdatabase1.ibhas a default cache size of 8000 pages of 4KB each, anddatabase2.ibhas a default cache size of 10,000 pages of 2KB each, then while both databases have at least one connection,ibserverallocates a total of 32MB + 20MB of RAM.
You should experiment with larger cache sizes and analyze the performance improvements. At some point, you will observe diminishing returns. A typical application can achieve up to 30% performance increase from proper cache sizing.