LOGSIZE and its effect on logging
The LOGSIZE configuration parameter specifies the size of each logical log file. It is difficult to predict how much logical-log space your database server system requires until the system is fully in use.
- Recovery time objective (RTO)
- This is the length of time you can afford to be without your systems. If your only objective is failure recovery, the total log space only needs to be large enough to contain all the transactions for two checkpoint cycles. When the RTO_SERVER_RESTART configuration parameter is enabled and the server has a combined buffer pool size of less that four gigabytes, you can configure the total log space to 110% of the combined buffer pool sizes. Too much log space does not impact performance; however, too little log space can cause more frequent checkpoints and transaction blocking.
- Recovery point objective (RPO)
- This describes the age of the data you want to restore in the
event of a disaster. If the objective is to make sure transactional
work is protected, the optimum LOGSIZE should be a multiple of how
much work gets done per RPO unit. Because the database server supports
partial log backup, an optimal log size is not critical and a non-optimal
log size simply means more frequent log file changes. RPO is measured
in units of time. If the business rule is that the system cannot lose
more than ten minutes of transactional data if a complete site disaster
occurs, then a log backup should occur every ten minutes.
You can use the Scheduler, which manages and executes scheduled administrative tasks, to set up automatic log backup.
- Long Transactions
- If you have long transactions that require a large amount of log space, you should allocate that space for the logs. Inadequate log space impacts transaction performance.
Choose a log size based on how much logging activity occurs and the amount of risk in case of catastrophic failure. If you cannot afford to lose more than an hour's worth of data, create many small log files that each hold an hour's worth of transactions. Turn on continuous-log backup. Small logical-log files fill sooner, which means more frequent logical-log backups.
If your system is stable with high logging activity, choose larger logs to improve performance. Continuous-log backups occur less frequently with large log files. Also consider the maximum transaction rates and speed of the backup devices. Do not let the whole logical log fill. Turn on continuous-log backup and leave enough room in the logical logs to handle the longest transactions.
The backup process can hinder transaction processing that involves data located on the same disk as the logical-log files. If enough logical-log disk space is available, however, you can wait for periods of low user activity before you back up the logical-log files.