Cache settings

Database cache size can be determined by (in order of importance):
  1. IB server built-in default
  2. Server wide setting (ibconfig file)
  3. Attachment level (isc_dpb_num_buffers parameter)
  4. Database wide cache setting

IB server built-in default.

Different values are used depending on InterBase server version, e.g.

Server wide setting (ibconfig file).

Because this setting is server wide, be careful when there are more databases on single InterBase server. E.g. you have DATABASE_CACHE_PAGES set to 10000 and use 8K pages; it means 80MB of cache per database , i.e. after connecting to 4 databases, InterBase will allocate 320MB of RAM for cache! For this reason it seems better to keep this value relatively small, and set cache size individually for each database.

Do not forget to uncomment proper line in ibconfig file (i.e. remove # from DATABASE_CACHE_PAGES line) and restart InterBase (superserver) to take change into account.

Attachment level (per connection setting).

It is set by isc_dpb_num_buffers parameter in DPB block, that is used by isc_attach_database() function. The behavior is different for Classic and for Superserver:

Because in classic InterBase each user runs its own copy of IB server, each user has its own buffers, and thus user can set whichever value he/she wants (i.e. changing number of buffers in one process does not affect other users/processes).

In superserver IB, buffers are shared among all users; it means

In isql utility there are two possibilities how to change cache size at attachment level - either by -c command line parameter, or by CACHE parameter of CONNECT statement. In fact, InterBase does not directly support CONNECT statement (i.e. you can't directly execute CONNECT statement by isc_dsql_execute-like calls). isql will parse its command line, and when it encounters CONNECT, it will convert it to calling isc_attach_database() function It means both "-c" command line parameter, and CACHE parater of CONNECT statement are finally converted to isc_dpb_num_buffers value in DPB block. CACHE has higher priority than -c if you use both.

Database wide cache setting.

Contrary to what documentation says (see "Default cache size per ISQL connection", chapter 6, Operation guide) this setting has the highest priority, so it can't be changed even at attachment level! It is set usually by gfix (e.g. gfix -buffers 1234) and stored on gdb header page. New value does not take effect immediately, but when the server opens database file (i.e. after closing all connections first). The value is preserved when doing backup/restore, but sometimes it does not work correctly, so you should verify it after restore. You can also change the value during restore (gbak -c -buffers 1234). To get rid of database wide cache setting, set the value to zero.


Copyright © 2001 Ivan Prenosil

[Last update - February 2002]