<< Temporary files | Firebird administration using IBExpert | Optimizing performance >>

Memory configuration

Memory settings depend on the one hand on the database page size and on the other the default cache pages specified in firebird.conf. By default, the Superserver allocates 2048 pages for each database and the Classic server allocates 75 pages per client connection per database.

This value can be altered in the firebird.conf by defining the DefaultDbCachePages parameter, the maximum value being 128,000. However, if the memory specified in the firebird.conf (number of pages multiplied by the page size) is larger than the actual available memory, it will not be possible to open the database!

The default setting in the conf file is for all databases, but you can override this for each individual database by specifying the cache buffers.

We therefore recommend leaving the default size in the firebird.conf as it is at 2048, and instead, define in the IBExpert Services menu item, Database Properties, that the database should use 20.000 pages for the cache. The KB size is calculated automatically, and this is the quantity of bytes which remains in the working memory, which of course speeds up the database performance. This cache buffers setting for the database overrides the default cache pages in firebird.conf.

The buffers/cache can be set using the IBExpert menu item Database Properties, found in the Services menu, or using the command-line utility gfix. The only limit to amount of cache is the physical size of the RAM.

The total KB is calculated according to the current database page size. For an alteration to become effective, it is necessary for all users to disconnect from the database and then reconnect.

In gfix the command to set the number of cache pages is:

 gfix -b[uffers] BUFFERS database_name

You cannot change the database page size in this manner, only the number of pages reserved in RAM. One parameter is required which must be numeric and between 50 (the minimum) and 131,072 (the maximum).

The setting applies only to the database you specify. No other databases running on the same server are affected.

Don’t forget, when specifying buffers for an individual database, always use the IBExpert Database Properties menu item or gfix. If you specify buffers in the firebird.conf, it applies to all databases using that Firebird server.

From a certain point increasing the cache will not provide a visible improvement in performance. However if you regularly need to process large amounts of data for reports or similar in a large database, then it is advisable to optimise these parameters.

Please note:

• Superserver: cache memory per database = page size * buffers
• Classic server: cache memory per connection = page size * buffers

Therefore it is important to define the cache memory for the Classic server at a lower level than for the Superserver.

With Superserver the cache remains open as long as someone is logged onto the database. When the last connection logs out, the complete cache is deleted. When the next connection is made, the cache starts to fill up again.

It’s possible to do a fine-tuning workaround here: for example, use a procedure to make a server connection at 6am (or before the main working day starts), run a query that loads all tables into the cache (for example a Count from all tables), commit this transaction and leave the connection open. This way all tables have been loaded into the cache before the users start work, and if the cache is large enough, and the connection is not disconnected, the pages remain in the cache.

Please also refer to Holger Klemt’s article: Optimize database cache utilization to improve database Performance, which offers you further tips regarding cache utilisation.

See also:
Page size
Firebird 2.5 Release Notes: Parameters affecting file system cache usage

back to top of page
<< Temporary files | Firebird administration using IBExpert | Optimizing performance >>