Designing a Minimal Configuration
Go Up to Journaling Tips and Best Practices
To begin, set the following parameters:
CREATE JOURNAL 'e:\database\test' LENGTH 65000 CHECKPOINT LENGTH 10000 PAGE CACHE 2500;
Given a database that has an 8KB page size, the journal PAGE SIZE will default to 16KB (2 x 8KB). Therefore, the LENGTH parameter (65000)will cause rollover to a new journal file every 1GB (65000 x 16KB). If you instead set the LENGTH at 500, the system would roll over to a new journal file every 8MB, which is extremely frequent. A performance drop may occur during this process. Using a larger LENGTH value will make this occur (65000/500 or 130 times) less often.
The CHECKPOINT LENGTH parameter of 10000 means the database checkpoint will occur every 160MB (10000 x 16KB). Assume the built-in CHECKPOINT LENGTH is 500, which means your system will checkpoint the database every 8MB (500 x 16KB). CHECKPOINT LENGTH is a matter of individual taste. It represents the maximum number of bytes that will have to be applied to a database from the journal files after a system crash. You can expect to average between 1MB to 2MB/sec. applying the journal files during the recovery process. So the 160MB checkpoint length suggested here would take a maximum of about 2 minutes to recover depending on your machine. If your organization can tolerate a longer recovery time in return for minimizing the online frequency of database checkpoints, then raise the CHECKPOINT LENGTH accordingly.
The PAGE CACHE parameter can be raised to reduce the probability of incurring journal buffer wait states. At any moment, the journal cache writer thread will be syncing some number of journal buffers to the journal file on disk. During this period, we want to insure that the worker threads have enough spare journal buffers to write to when a database page's journal changes need to be moved to a journal buffer.
For example, imagine that the journal cache writer is syncing 500 journal buffers to disk. The 2500 journal buffer configuration will leave 2000 spare buffers for the worker threads to dump their journal changes. At the built-in PAGE CACHE default of 100, the worker threads can stall due to a high rate of journal buffer wait states.
Lastly, the use of a SAN mirrored cache will always make InterBase journaling sub-system result in lower performance than a non-journaled InterBase database. This is because twice the amount of data is being written with the journaling subsystem: once to the journal files and once to the database files, plus the additional CPU cost of journal cache management in the InterBase server.
Even for direct-attached storage, it is necessary to pay attention to on-disk write cache enablement. New computers sometimes arrive with on-disk write cache enabled. This means that synchronous writes to a database or journal are not really synchronized to disk oxide. Unless the write cache (SAN or direct) has been disabled or has battery backup, it cannot offer durability for database commits.
InterBase journaling should only result in a performance gain when disk I/O is write-through, where every database write goes to disk oxide and not an on-disk cache.
Hopefully, the CREATE JOURNAL statement above will minimize this cost. Remember that the end goal is to provide point-in-time disaster recovery using the CREATE JOURNAL ARCHIVE statement to archive time-consistent database dumps and journal files.