Log File Sizing

From majordom@e-math.ams.org  Thu Jan  8 11:38:37 1998

A few things to keep in mind when sizing the Log File;
(Apologies if you have this information already.)

Log File Force Abort Limit. (Default is 80%) [configurable]

- the archive process (dmfacp) wakes up (at a configured interval.) and
archives from the recovery log file into journal files committed
transactions. (* remember journaling is by default ON in OpenIngres)
- after the dmfacp process finishes, the recovery (dmfrcp) process moves the
log file marker to the beginning of the oldest current (pending/running)
transaction.
- if a transaction is left running without a commit for an extended period
of time, the log file files up, all the while the archiver is continuing to
copy committed transactions to the journals. However the beginning of the
log file cannot move beyond the oldest uncommitted transition.

You may have a number of transactions that are committing regularly and are
being archived as per normal. However, the recovery process cannot reset to
the beginning of the log file. (thus reclaiming the log file resources)
until the oldest transaction either; commits, aborts, or reaches the Log
File Force Abort Limit.


Log File Full Limit. (Default is 90%) [configurable]

- if the amount of logfile in use, reaches this limit then a log stall
occurs. All updates wait until the aborted transaction rolls back. As Im
sure you aware, performance takes it on the chops when this happens.

Recovery Records.
(Typically, one recovery record is written per data row that is effected,
during a transaction. )

- recovery records contain admin. inf. like transaction id, pid inf., etc.
- location of the effected row.
- before and after images of the data row effected by the update. (thats
complete per row data copies folks!)

Recovery Records are always written out to the Log File before the data row
is updated.


Log File Compensation Log Records (CLRs)

- CLRs hold details of work carried out by a rollback. CLRs get written to
the journal files by the archiver.

CLRs are not actually written to the Log File unless a rollback occurs.
However, when a recovery record is written to the Log File it takes about
2x the space actually needed. (to make room for the CLRs if a rollback
occurs.) You need to plan for almost twice the Log File space when CLRs are
taken into account.


Keep In Mind. (Log File Sizing)
When transactions are running, the recovery system needs to allow sufficient
free space for the following;

- Begin Transaction Block - (date/time stamp log details, userid database
etc.)
- Recovery Records - (remember this includes before and after snaps of the
data row.)
- CLRs - (one compensation log record for each recovery record, incase
rollback occurs)

Note: CLRs are similar in size to the recovery record. log_trace can get you
precise details. Remember even though CLRs are not written to the Log File
until an abort occurs, space is reserved for them. Ouch! This basically
doubles the consumption of Log File Resources.

- End Transaction Block - (date/time stamp etc.)


Size Makes a Difference.
(Um... well at least when considering  the OpenIngres Log File Size.)

Remember every database and every user session uses the same Log File so be
liberal in your settings. This is double true if your datarows are large, or
your data block sizes are robust.

Good Luck,

J.Tyler McGraw

Above Information; copyright  J.Tyler McGraw & Peritas LTD.
__________________________________________________________
E-Mail: tyler.mcgraw@btinternet.com     
Web: http://www.btinternet.com/~tyler.mcgraw/
OpenIngres FAQ: http://www.btinternet.com/~oi12mfaq.htm
Peritas:  http://www.peritas.com
__________________________________________________________
Allan Anderson...>I'm having trouble estimating the log
space usage of queries. The
>usage is 10 times what I expected and these log FORCE-ABORTs are real
>unpleasant on our production systems!
>
>It all started when...
Ingres Q & A
Back to William's Home Page
Email William