Tuning the Log Buffer Size

Normally, tuning the log buffer size is a matter of careful balance, but you can have your cake and eat it too by using the hidden _log_io_size parameter.

How is log buffer space used?

The log buffer in the SGA is internally divided into blocks of the log block size. The value specified by the log_buffer parameter must be a multiple of the log block size. On some operating systems it is rounded up at instance start-up if necessary. Each block in the log buffer maps to a block in the current online log file, most of the time.

Consider for example the instance illustrated in the following figure. This instance has 100 log blocks in the log buffer. After a log switch, an SGA variable (b) is set to indicate that the base disk block for the log buffer is block 2. Another SGA variable (i) is used for the index into the log buffer for redo generation. This is shown as pointing to log buffer block 30 shortly after the log switch. A little later, after several log writes have occurred, the base disk block for the log buffer is disk block 82. This maps to log buffer block 81. Redo generation continues and wraps from log buffer block 100 to log buffer block 1. At the moment illustrated the index into the log buffer for redo generation is block 5. This maps to disk block 106. The log buffer is used cyclically in this way until i maps to the last block in the log file.

How blocks in the log buffer map to block in the log files

When does LGWR write?

There are two classes of LGWR writes: background writes, and sync writes. The distinction is merely as to whether another process has to wait for the log write to complete.

A background write by LGWR can be triggered in either of two ways. Whenever a process allocates space in the log buffer, the number of used log buffer blocks is calculated. This calculation is based on the two SGA variables mentioned above, and is performed under the protection of the redo allocation latch. If the number of used blocks is greater than or equal to the number of blocks specified by the _log_io_size parameter, and if LGWR is not already active, then LGWR is posted to perform a background write. The default value for _log_io_size is 1/3 of the log buffer expressed in log blocks (with an upper bound equivalent to 1M under Oracle8). If more than this number of log buffer blocks are used, then the redo allocation latch is released and the redo writing latch is taken to check whether LGWR is already active.

When LGWR is waiting to be posted, it sleeps on an rdbms ipc message wait with a timeout of 3 seconds (as does DBWn). A background write may also be performed if this sleep times out and LGWR finds that there is some redo available to write.

The most common cause of log sync writes is the end of a transaction. When a process finishes a transaction, normally with a commit, it generates a commit marker in the redo stream. However, the transaction is not recoverable until the log block containing that commit marker has been flushed to disk. Therefore, before the process finishing the transaction can continue, it must wait for LGWR to flush that log block to disk. That process therefore posts LGWR and then sleeps on a log file sync wait with a timeout of 1 second. If several commits occur in distinct transactions before LGWR wakes up, then the commit markers are all flushed to disk in a single sync write. This is sometimes called a group commit .

The only other process to wait for log sync writes is DBWn. DBWn cannot write a database block to disc if the redo for the most recent change to that block has not yet been flushed to the redo log file. Otherwise, if the instance were to crash before that redo could be written, there would be no way to rollback those uncommitted changes, because the redo for the corresponding undo would not yet have been written either, because it would have been part of the same unwritten redo entry.

To prevent this, Oracle maintains a record of the most recent change to each current mode database block in the buffer header structure. This is stored as a redo block address (RBA) comprised of the log file sequence number, the log file block number and an offset into the block in bytes. There are three redo block addresses in the buffer header structure - the low RBA, the recovery RBA and the high RBA. It is the high RBA that is used at this point. Before writing a batch of database blocks, DBWn finds the highest high redo block address that needs to be synced before the batch can be written. DBWn then takes the redo allocation latch to ensure that the required redo block address has already been written by LGWR, and if not, it posts LGWR and sleeps on a log file sync wait.

What if the log buffer is too small?

If the log buffer is too small, then log buffer space waits will be seen during bursts of redo generation. LGWR may not begin to write redo until the _log_io_size threshold (by default, 1/3 of the log buffer or 1M whichever is less) has been exceeded, and the remainder of the log buffer may be filled before LGWR can complete its writes and free some space in the log buffer.

Ideally, the log buffer should be large enough to cope with all bursts of redo generation, without any log buffer space waits. Commonly, the most severe bursts of redo generation occur immediately after a log switch, when redo generation has been disabled for some time, and there is a backlog of demand for log buffer space.

Can the log buffer be too big?

However, if the log buffer is very big, then the default _log_io_size threshold will be big also, and so background writes may seldom be triggered. This means that all the redo will have to be flushed by sync writes, and so log file sync waits will take longer than otherwise. This impacts commit response time, and possibly DBWn performance as well.

Setting _log_io_size

Of course, it is not necessary to carefully balance log buffer space waits against log file sync waits. You can have both a large log_buffer setting to avoid log buffer space waits, and a relatively small _log_io_size setting to minimize log file sync waits and reduce their duration.

However, _log_io_size must not be so small as to keep LGWR unduly active. LGWR uses the redo allocation latch both before and after each write. If LGWR is over active, then there is increased risk of redo allocation latch contention. An over active LGWR also wastes CPU time by performing multiple small writes, and by running more often which involves extra context switches. This can impact LGWR's operating system scheduling priority, which in turn degrades LGWR performance even further.

For raw log files, or file system based log files with direct I/O enabled, the optimal setting for _log_io_size is normally just below the maximum physical I/O size supported by the operating system (device driver). For file system based log files without direct I/O, a smaller setting normally works better.

Note that the _log_io_size setting must be specified in log blocks, whereas the log_buffer parameter is set in bytes. Note further that setting _log_io_size is no excuse for having a wastefully large log_buffer setting. It is very rare to require a log buffer more than a few hundred kilobytes in size.

Copyright Ixora Pty Ltd Send Email Home