Calculating Storage for Logging

Understanding Log File Characteristics

Log files are different from DBEFiles. Log files store log records used by ALLBASE/SQL to perform recovery. DBEFiles contain tables and indexes.

Log files are composed of 512-byte pages. A log file can be from 250 to 524,287 pages. The number of pages in a log file is determined when it is created. The maximum size of a single log file is 4 gigabytes. A single DBEnvironment can have up to 34 log files configured, providing a maximum of 136 gigabytes of log file space. Log files are not associated with a DBEFileSet.

Log Records and Transactions

ALLBASE/SQL logs changes made to DBEFile pages. Changes are written to a log file (or files) as log records. At least one log record is created for each data or index page that is changed. For example, if you update a column in a table that has three indexes created on that column, you have a minimum of four log records written to the log file: one for the change to the column, and one for each of the three indexes.

A log record is a maximum of twice the size of the updated row plus overhead of about 50 bytes. For example, updating a 200-byte column would create a log record of approximately 450 bytes. An update uses the largest amount of space in a log record; other kinds of activity (INSERT and DELETE operations) use smaller log records.

Note that a log record may be larger than a log page, which is 512 bytes. On the other hand, a log page may contain several log records. Log space is allocated in pages, whereas log entries are created as log records, which are inserted into one or more pages.

Transaction size also affects log file size. A transaction is one or more SQL statements that together perform a unit of work in a DBEnvironment. Transactions are implicitly begun by ALLBASE/SQL whenever a user or application program executes most SQL statements. You can also explicitly start a transaction with the BEGIN WORK statement. The transaction is not ended until a COMMIT WORK or ROLLBACK WORK is executed.

A log fileset should be large enough to hold all current transactions. If one file in the fileset fills up, ALLBASE/SQL switches to the next available file (if there is one) and continues logging. If the log fills up before a transaction in progress completes, and if there is no more file space available, ALLBASE/SQL rolls back the current transaction. In most cases, all other transactions are also rolled back, whether or not they involve updates. This can happen if your transaction is larger than available file space in the log. When such a LOG FULL condition occurs, you will not be able to process any more transactions of the same size until some log file space is recovered (in nonarchive logging only), or until a new log file is added to the DBEnvironment. Use the SHOWLOG command and the CHECKPOINT statement to monitor log file use, as described in the "Backup and Recovery" SECT. Use the SQLUtil ADDLOG command to add log files. If you are logging in archive mode, use the STORELOG command to free archive logs for reuse.

Using Archive or Nonarchive Logs

You can specify archive or nonarchive logging for the DBEnvironment. Nonarchive logging tracks all current DBEnvironment activity in a log file. This lets you roll back incomplete transactions when necessary, maintaining consistency in database tables. When transactions are no longer current, the space they occupy in the log file can be reused by other log records. Archive logging tracks all DBEnvironment activity continuously from the time it is enabled.

By default, nonarchive logging is in effect after you issue a START DBE NEW or START DBE NEWLOG statement. If you have the TurboSTORE software, you can turn archive logging on by using the SQLUtil STOREONLINE command, which backs up the DBEnvironment and then enables archive logging. If you do not have TurboSTORE, you can enable archive logging by following the procedure described under "Static Full or Partial Backup Procedures in Archive Mode" in the "Backup and Recovery" chapter. Once archive logging is on, you can only disable it with a START DBE NEWLOG statement.

For many installations, nonarchive logging is appropriate for the phase of database creation and initial loading of tables. Once the DBEnvironment has been loaded, you can use the SQLUtil STOREONLINE command to create a complete backup and turn on archive logging at the same time. (For complete information about the process of backing up the DBEnvironment and turning on archive mode, refer to the "Backup and Recovery" chapter.)

Using Single or Dual Logging

When you create the DBEnvironment, you specify either SINGLE or DUAL logging. In single logging, ALLBASE/SQL maintains one set of log files in either archive or nonarchive mode. For greater security, you can specify dual logging, in which a duplicate set of log files is maintained. When dual logging is in effect, all the procedures described in this chapter apply to the files of both logs.

Using Multiple Log Files

By default, ALLBASE/SQL creates a single log file (or two files, in dual logging) when you create the DBEnvironment. During normal operation, transactions are logged until there is no more space available. This is known as a LOG FULL error condition. If a LOG FULL condition arises, all transactions are rolled back.

With archive logging, LOG FULL occurs when the end of the last file has been reached, and if there is no other log available to switch into. You can assure that there is always another log to switch into by making sure that all transactions commit promptly, and by using the SQLUtil STORELOG command to store log files as they fill up. Once stored, and once all the transactions in them are complete, archive log files are marked available for reuse.

With nonarchive logging, LOG FULL occurs if there is still no available space in the file after ALLBASE/SQL takes a checkpoint while using the last file in the fileset. To prevent LOG FULL in nonarchive logging, make sure the combined size of all the files in the fileset is larger than the space required for the largest number of concurrent transactions you expect in the DBEnvironment.

To prevent a LOG FULL condition from ever arising, you can use multiple log files to provide enough log file space for the largest amount of DBEnvironment activity that will ever require logging at any one time. You can do this by setting up a log containing a circular fileset (or two circular filesets, for dual logging). A circular fileset is a group of reusable files belonging to the same log. You can add log files to the circular fileset without stopping the DBEnvironment. When you are using a circular fileset, a LOG FULL condition can only arise if there is no available space in any of the files in the fileset. With either archive or nonarchive logging, you may use up to 34 additional log files in a log (or 34 additional files for each set in dual logging).

For a complete discussion of log file types, refer to the "Backup and Recovery" chapter.

Sample Log Configuration

The following START DBE statement creates a sample DBEnvironment named PartsDBE in dual logging mode with two 256-page log files named PartsDBELog1 and PartsDBELog2 and system file names of PartsLg1 and PartsLg2:

   isql=> START DBE PartsDBE.SomeGrp.SomeAcct' NEW

   > DUAL LOG, LOG DBEFILE PartsDBELog1 AND PartsDBELog2

   > WITH PAGES = 256,

   > NAME = 'PartsLg1' AND 'PartsLg2';

The following SQLUtil ADDLOG command adds another file to each of the dual logs. The result is two log filesets:

   

   ***************************************************************

      >> addlog

      DBEnvironment Name:  PartsDBE.SomeGrp.SomeAcct

      Maintenance Word:   Return 

      Enter Log File Name(s) Separated by a Blank? PartsLg3     PartsLg4

      New Log File Size? 300

      Add Log File (y/n)?  y



      Log files 'PartsLg3' and 'PartsLg4' were Added.

      Log Identifier Is: 2

Whatever size you choose to make the individual log files, the disk space used for logging will double if you use dual logging.

Disk Space for the Log

The major difference between the archive and nonarchive log filesets is that archive log files must be backed up for possible use in the event of a media failure. Nonarchive log files do not need to be backed up. Also, certain operations (such as initial table loads) produce more logging in archive mode than in nonarchive mode. In the following sections, size estimates are based on archive logging, so the result will be more than adequate for nonarchive logging as well.

When a DBEnvironment is configured, ALLBASE/SQL creates at least one log file with a default file name of DBELOG1 (and DBELOG2 if DUAL LOG is specified), and a default size of 250 pages. The file name is relative to the DBECon file name unless you specify a fully qualified file name. You can use the default name and size or specify a name and size (up to 524,287 pages) during configuration. You use the SQLUtil ADDLOG command to add additional files to the DBEnvironment.

Estimating Log File Size

To estimate the total size (sum of the sizes of component files) of a log:

1. Calculate the number of log pages (LP) that the longest transaction will require.

2. Multiply the results (LP) by the maximum number of concurrent transactions allowed in the DBEnvironment to get the total number of log pages (TLP) required. (This assumes that the maximum number will all be involved in operations that require the same amount of logging. If you are running transactions in applications such as report generators that do not involve logging, subtract the non-logging transactions from the maximum, and use the result in the calculation.)

3. Add 38 pages per log file for overhead used by ALLBASE/SQL to maintain the log.

Step 1 calculates the number of log pages needed for the longest transaction. The longest transaction is the transaction that affects the largest number of rows in a DBEnvironment between a BEGIN WORK and a COMMIT WORK or ROLLBACK WORK statement issued either programmatically or through ISQL.

The formula used to approximate the log pages needed for the longest transaction is as follows:

PercentInserted, PercentDeleted, and PercentChanged are the percentages of your tables that are affected by the transaction. You can derive these values by dividing the number of rows inserted (or deleted or changed) by the total number of rows in the table. These percentages are then applied to the entire DBEnvironment to arrive at a number of log pages for storing all the information (data from tables, indexes, and constraints, plus overhead) that is logged by the transaction. DBE Size (in bytes) can be estimated by using the following query after doing an UPDATE STATISTICS for all tables in your DBEnvironment:

isql=> SELECT 4030*SUM(DBEFUPAGES) FROM SYSTEM.DBEFILE;

Example

Assume that you have a DBEnvironment containing six tables with 30,000 rows each (total 180,000 rows). Together, all the tables and indexes occupy five megabytes. Your longest transaction will insert 50 rows, delete 25 rows, and change 100 rows. This calculates to .03% inserted, .01% deleted, and .06% changed.

If you apply these numbers to the calculation for LP in step 1, you get the following results:

In step 2, you multiply the number of log pages (LP) by the total number of concurrent transactions allowed in the DBEnvironment. The total number of concurrent transactions is a value that you can set at DBEnvironment configuration and change later using SQLUtil or START DBE. It is stored in the Maximum Transactions parameter in the DBECon file, which is discussed in the next chapter, "DBEnvironment Configuration and Security."

	NOTE: Using the value for Maximum Transactions in the preceding formula assumes that all transactions are equally involved in logging. In other words, it is assumed that each transaction contains roughly the same amount of INSERT, UPDATE, or DELETE operations. If this is not true, you may wish to use a weighted or average value in arriving at the total. While the above calculations will provide more than enough log file space, you can arrive at an optimal log size by using weighted values.

The following is the calculation for total log pages:

TLP = LP * (Maximum Transactions)

Suppose that Maximum Transactions is set to 30. The following calculations give the total number of log pages (TLP) based on the previous calculations:

TLP = 20 Pages * (30 Transactions) = 600 Log Pages

Finally, in step 3, add 38 pages of overhead used by ALLBASE/SQL to arrive at the size your log file should be.

Log File Size = 600 Log Pages + 38 Overhead Pages = 638 Pages

For nonarchive logging, one log file larger than 638 pages should be sufficient (two log files, if you are using dual logging). If you add additional log files, ALLBASE/SQL will switch to the second file if additional space is needed.

For archive logging, you should use at least two separate files, so that ALLBASE/SQL can switch from the first to the second, thus permitting the first to be backed up. The size of each log file should be no smaller than 638 pages, but the actual size should take into account the number of log files available for log switching and the frequency with which logs can be backed up on your system. If backups are not done at frequent intervals, a larger number of log files will be necessary in archive mode.

	NOTE: Updates involving indexed pages can result in a considerable amount of B-tree page splitting, which requires additional logging.

Until your system is in full production, use a larger than necessary log. You can then monitor the free log space from time to time until you know the optimal size for your configuration. You can easily add and purge log files as needed with the SQLUtil ADDLOG and PURGELOG commands.

For all the foregoing calculations on the required size of log files, it is assumed that all users are starting and ending transactions in reasonable ways. However, if a user starts a transaction and then neglects to commit work for a very long time, files in which that transaction is active will not be available for switching as assumed in the calculation. In such a case, the calculations are meaningless.

	NOTE: If you are using dual logging, you specify two different filenames for each file you add to the DBEnvironment with the START DBE NEW, START DBE NEWLOG, or ADDLOG commands.

Additional information about creating and maintaining logs is found in the "Backup and Recovery" chapter.