Cache settings

Database cache size can be determined by (in order of importance):

IB server built-in default
Server wide setting (ibconfig file)
Attachment level (isc_dpb_num_buffers parameter)
Database wide cache setting

IB server built-in default.

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

IB6 classic (Linux) - 75
IB6 superserver (Windows) - 2048
IB5 superserver (Windows) - 256

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

first connection can change the cache size without restrictions (i.e. set higher or lower size than that determined by ibconfig file or built-in default)
all subsequent (secondary) connections can only increase current value

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.

Notes.

Cache size can be set in range from 50 to 65535 (64K). If you use value outside this range, InterBase will sometimes not complain, but the value will be converted to proper range anyway.
It is recommended to not use cache larger than 10000 pages. The larger the cache, the more CPU is needed to work with it; at some level, too large cache can in fact slow down performance.
This problem is fixed in IB6.5
Notice that there is no CACHE parameter for CREATE DATABASE command. E.g. when you create new database by isql and immediately check cache size, you will see that command line -c parameter is ignored, and built-in or ibconfig value will be used.
For anybody who wish to play with these settings: to check current cache size in isql, use commands
```
  SET STAT;
  COMMIT;  
```
In WISQL use menu command
```
  Session
  Basic Settings
  Display Statistics  
```
and then execute
```
  COMMIT;  
```
Less known fact is that cache size can significantly affect login time; the larger is the cache, the longer time is needed for login. Secondary connections (on SuperServer) are slightly faster, but still visibly slow.
Note that isc4.gdb (that must be opened during each login) shows the same effect (slow login) if you set large cache size directly in security database (by gfix), but is not influenced by large default value in ibconfig file.