The Art of DB2 Log File Sizing

Body

    The art of being a DBA includes being able to tune the database to the needs of the client applications accessing it.  One of the many avenues of this art is the ability to tune the logging parameters such they are not constraining performance. The log file size along with the number of primary and secondary logs in the DB2 configuration are the principle means to accomplish this.

    The ideal number of logs a DB2 database requires starts with good understanding of the clients using the it.  There should be enough primary logs, LOGPRIMARY, to contain the longest expected transaction, space to roll it back, and enough space to hold all of the other transactions that are also occurring while that oldest transaction is still active.  OLTP transactions are designed to be short lived.  These are not the major concern.  Activities like index builds, loads that insert thousands of rows before a commit, maintenance activities, and numerous other examples of tasks that can generate long transactions have to be taken into account by the DBA to set the number of primary logs appropriately.  As the database grows in size the number of primary logs needed could also grow.  This setting should be reviewed regularly to make sure the current size addresses the needs.

    The logging "failsafe" comes in the form of Secondary Logs.  These are logs that can be created by DB2 to handle unexpected transaction activity.  Having secondary logs available, along with the filesystem space required to handle them, can shield applications from an unexpected event that would otherwise cause ab-ended transactions and rollback. A good starting point would be setting LOGSECOND to 20% of the LOGPRIMARY setting.  There must be available filesystem space to handle these logs if they are needed.  Remember, any secondary logs will be removed after they are completed and archived so that disk space will not remain allocated longer than necessary. 

    One more feature of secondary logs is infinite logging.  If your database is not in a pureScale environment then you can set LOGSECOND to -1 to achieve infinite active log space.  The number of primary logs still needs to be set properly but transaction logging needs beyond this will have no limit.  The only practical limit would be the size of the filesystem where these logs reside.  Beware of crash recovery and readlog API performance once secondary logs are utilized since these logs must be retrieved from the archive to the active log path.

    The final piece of this logging artwork is the log file size, LOGFILSZ.  This is the number of 4K pages that are preallocated for a log file.  The log file can fill up quickly when there is a high transaction rate.  When a log fills up it will need to be archived if circular logging is not set, which should encompass most environments that need to backup and recover the database.  So, there is an archiving and retrieval element which must be considered.  If online backups are utilized then the number of logs to replay during a Roll Forward becomes a factor in restore performance.  DB2 log archive and online backup are just two of the events that can be directly affected by the log file size.  If secondary logs are needed then a new log file of this size is allocated at that moment before it is filled with transaction log records.  This could slow down transaction commit performance during the secondary log file creation event.

    It is not healthy for a database to be completing a log file every 2-3 seconds.  This type of load would start putting a strain on the log archiving that needs to occur when the log is complete and no longer active.  The log archives could get behind and would be constantly busy.  This is not ideal.  It is better to increase the log file to a size such that the log archive method being used is able to complete the previous log archive before the next log is ready to be archived.  This will also lessen the impact that the log archive method has on the underlying filesystem thus leaving more I/O bandwidth for active transaction logging.

    When doing a restore of an online DB2 database backup the logs will have to be restored for the roll forward portion of the restore.  The number of logs included in the online backup could be much larger on a high transaction rate system with a small log file size.  The granularity of logs needed for roll forward correlates with the log file size.  If one of these logs were to be corrupt for some unfortunate reason, how much transaction time would be lost in any log would be dependent on the log file size. 

    As a good place to start tuning, size the log files to handle 5-10 minutes of transaction activity during peak load.  During, low periods of activity size the log files such that no less than 2-3 logs are completed an hour.  There is a wide range of size settings, big and small, between these time factors of measurement.  The art is to size the logs just right so that they are not too large nor too small.  A good size is small enough to have enough granularity in the online backup restore but not too small such that log archiving overloads the underlying filesystem or even the readlog API used by many replication utilities for disaster recovery strategies. 

    One final thought on this subject.  If you are using HADR then the number of primary and secondary logs can not exceed 255.  With logged index builds and other large logged transactions that need to be run though HADR could require a larger log file size than you expected to stay within the 255 limit.