Enabling Journaling and Creating Journal Files
Go Up to Journaling and Disaster Recovery
Contents
To create a journal file and activate journaling, use the following DDL syntax:
CREATE JOURNAL [<journal-file-specification>] [LENGTH <number-of-pages> [PAGES]]
[CHECKPOINT LENGTH <number-of-pages> [PAGES]]
[CHECKPOINT INTERVAL <number-of-seconds> [SECONDS]]
[PAGE SIZE <number-of-bytes> [BYTES]]
[PAGE CACHE <number-of-buffers> [BUFFERS]]
[[NO] TIMESTAMP NAME]
[[NO] PREALLOCATE <number-of-pages> [PAGES]];
InterBase currently requires that all journal files be stored in the same directory.
All CREATE JOURNAL clauses are optional. Table 1.1 describes the function of each option and its default value.
| Option | Description | Default values |
|---|---|---|
|
<Journal_file_spec> |
Specifies a quoted string containing the full path and base file name of the journal file. The base journal file name is used as a template for the actual journal file names as they are created. |
The full database path and file name |
|
LENGTH |
This clause specifies the number of pages that can be written to the journal file before rolling over to a new journal file. For tips on calculating this number, see Tips for Determining Journal Rollover Frequency. |
Maximum: 2GB or 4000 pages |
|
|
This clause specifies the number of pages that can be written to the journal file before checkpoint occurs. For tips on calculating checkpoint length, see Tips for Determining Journal Rollover Frequency. |
500 |
|
|
Determines the number of seconds between database checkpoints. The checkpoint interval determines how long it will take to recover after a server crash. Note: If both |
0 |
|
|
Determines the size of a journal page in bytes. A journal page size must be at least twice the size of a database page size. If a journal page size of less is specified, it will be rounded up to twice the database page size and a warning will be returned. The journal page size need not be a power of 2. |
Twice the database page size |
|
|
Determines the number of journal pages that are cached to memory. This number must be large enough to provide buffers for worker threads to write to when the cache writer is writing other buffers. If the number is too small, the worker threads wait and performance suffers. |
100 buffers |
|
|
Determines whether or not to append the file creation timestamp to the base journal file name. If used, the base journal file name will be appended with a timestamp in the following format: YYYY_MM_DDTHH_MM_SSZ.sequence_number.journal
|
Enabled |
|
|
Specifies the amount of disk space preallocated for journal files. For more information about using the preallocate clause, see About Preallocating Journal Space. |
No default value. |
The CREATE JOURNAL statement causes all subsequent write operations on a database to be done asynchronously. The journal file I/O is always synchronous and cannot be altered. All transaction changes are safely recorded on durable storage before the transaction is committed. This guarantees the ACID properties of a transaction (the database industry standards for Atomicity, Consistency, Isolation, and Durability).
Using asynchronous I/O for database writes allows the operating system to optimize file I/O, such as by writing consecutive pages together, or by using scatter/gather techniques that write consecutive pages in discontiguous page buffers. Journal file I/O is performed using InterBase careful write strategy. This implies that database pages can be written back to the database in any order after their changes have been journaled.
During a database checkpoint, any database page writes that were buffered asynchronously are flushed to disc before checkpoint completion is signaled. You can re-enable synchronous writes for the database, which removes the requirement for a flush operation before a database checkpoint can be considered complete. Doing so, however, can degrade performance.
About Preallocating Journal Space
As suggested earlier, for best performance journal files should be placed on a dedicated hard drive. If they are not, access to other files on the drive causes the disk heads to move away from the journal files, which forces a seek-back to the journal file before a new page is written. In addition, each time the journal file size increases, even when the journal files are on a dedicated drive, the disk heads seek away from the journal file so the operating system can update the directory information. To allow the disk heads to remain on the current journal file at all times, you can use the PREALLOCATE clause. The PREALLOCATE clause enables you to allocate space equal to the maximum number of journal files that will exist during normal operation, multiplied by the length of the journal files specified in the LENGTH clause of the CREATE JOURNAL statement.
If the journal is not on a dedicated drive, you can use the PREALLOCATE clause to allocate space equal to the size of the maximum number of journal files that might exist. This guarantees that other files cannot consume the space that may be needed for the journal. If journal archiving is enabled, and you are archiving to a remote machine, allocate enough space to accommodate the journal files that will accumulate if the connection to the remove machine is lost and the journal files cannot be archived for a period of time.
Tips for Determining Journal Rollover Frequency
Journal file rollover is a time-consuming operation. If rollover happens too frequently, performance will degrade. However, if you are using journal archiving, you want the journal file to rollover as often as possible so the completed journal file can be archived frequently, which minimizes the number of transactions that will be lost if disaster destroys the database server. Determining the most effective rollover frequency is a balancing act and the best answer will be different for each InterBase installation.
You can use the following equations to help you determine the most efficacious rollover frequency for your journal files. You can enter the resulting number in the LENGTH clause of the CREATE JOURNAL statement, which specifies when the end of a journal file is reached. When the end of the file is reached, journaling resumes on a new file. When a journal file is complete (i.e. its end has been reached), it can be saved to the archive directory.
To determine frequency in bytes, use the following equation:
(journal file length * journal page size) = # of bytes before rollover occurs
To determine a rollover interval, you can use either of the following equations:
(journal file length * journal page size) / (database page size * writes per minute) = # of minutes between rollovers
The equation above lets you see how often a rollover will occur for a given journal file length. The equation below calculates the journal length that will give the rollover interval you specify:
(rollover interval * database page size * writes per minute) / journal page size = journal file length
Tips for Determining Checkpoint Intervals
InterBase uses the checkpoint interval to determine how long it takes InterBase to recover after a server crash. If the server crashes, all of the changes in the cache will be lost. This is okay because the changes were written synchronously to the journal, which stores them on disk, but not in the database. When the server restarts, it must read all of the journal files and copy the changes in the journal to the database to bring the database file up-to-date. The more journal files there are, the longer this will take. Performing frequent checkpoints means that the changes in the cache are written to the database frequently, so fewer changes are kept in the journal. This in turn means that fewer journal files are required and recovery will take less time.
You can use the following equations to help you determine the most effective checkpoint interval for your system:
(checkpoint length * journal page size) / (database page size * writes/minutes) = # of minutes between checkpoints
To help determine the time your system needs to recover, use this equation:
(checkpoint length * journal page size) / 1,048,576 = maximum time to recover after a crash in seconds
This equation assumes that the journal file is processed at a rate of one megabyte per second during crash recovery. Typically, a journal file is processed at one to two megabytes per second.
To determine checkpoint length for a given recovery time, use this equation:
(recovery time in seconds * 1,048,576) / journal page size = checkpoint length
Displaying Journal Information
To display journaling information for a database, use the following command:
gstat <a_database> -l
The switch is a lower case L, rather than the numeral one.
Using IBConsole to Initiate Journaling
IBConsole offers the same journaling options in a dialog box as those described in Enabling Journaling and Creating Journal Files. You cannot use IBConsole to create journal archives.
To initiate journaling from IBConsole, take the following steps:
- In the tree pane, right-click the database for which to initiate journaling, and select Backup/Restore from the context menu.
- When the Backup/Restore menu options appear, select Create Journal. The Create Journal dialog appears, as shown in the figure:
- On Create Journal, specify the options to use, then choose OK to begin journaling. For descriptions of each option, see Enabling Journaling and Creating Journal Files.
Disabling Journal Files
The DROP JOURNAL statement discontinues the use of write-ahead logging and deletes all journal files. This operation does not delete any journal files in the journal archive but does discontinue maintenance of the journal archive. Dropping journal files requires exclusive access to the database.
To disable journaling, use the following syntax:
DROP JOURNAL