HP 3000 Manuals

ALLBASE/SQL Database Administration Guide

Calculating Storage for Logging 

The DBA should calculate the appropriate size of log files for use with
the ALLBASE/SQL log.  Eventually, log files can become full, and the
result is known as a LOG FULL error condition.  To avoid this, you must
set up logs that are large enough for your system's heaviest needs.

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" chapter.  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.

Determining the Number of Log Files.   

In nonarchive logging, log file space can be reclaimed once transactions
are committed or rolled back.  This means that your log file (files, for
dual logging) should be large enough to hold the single longest
transactions plus all concurrent transactions.

In archive logging, log file space is reclaimed and reused by storing
each log file after it becomes full.  As with nonarchive mode, the sizes
of all component files taken together must be large enough to contain all
active transactions.  But for archive logging, there should be at least
two separate files in the log (two files for each log, in the case of
dual logging) so as to permit log switching followed by eventual log
backup and reuse.

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:


            

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


            

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


            

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.

MPE/iX 5.5 Documentation