Cache settings

Source: Ivan Prenosil's InterBase site

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

Notes

IB server built-in default.

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

Server wide setting (ibconfig file).

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).

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.

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.