There are strategies that you can use to improve DB2® performance during database
recovery and decrease the time that is required to recover from a DB2 service outage.
The following should be considered when thinking about recovery
performance:
- You can improve performance for databases that are frequently
updated by placing the logs on a separate device. In the case of an
online transaction processing (OLTP) environment, often more I/O is
needed to write data to the logs than to store a row of data. Placing
the logs on a separate device will minimize the disk arm movement
that is required to move between a log and the database files.
You
should also consider what other files are on the disk. For example,
moving the logs to the disk used for system paging in a system that
has insufficient real memory will defeat your tuning efforts.
DB2 database products automatically
attempt to minimize the time it takes to complete a backup or restore
operation by choosing an optimal value for the number of buffers,
the buffer size and the parallelism settings. The values are based
on the amount of utility heap memory available, the number of processors
available and the database configuration.
- To reduce the amount of time required to complete a restore operation,
use multiple source devices.
- If a table contains large amounts of long field and LOB data,
restoring it could be very time consuming. If the database is enabled
for rollforward recovery, the RESTORE command provides
the capability to restore selected table spaces. If the long field
and LOB data is critical to your business, restoring these table spaces
should be considered against the time required to complete the backup
task for these table spaces. By storing long field and LOB data in
separate table spaces, the time required to complete the restore operation
can be reduced by choosing not to restore the table spaces containing
the long field and LOB data. If the LOB data can be reproduced from
a separate source, choose the NOT LOGGED option when creating or altering
a table to include LOB columns. If you choose not to restore the table
spaces that contain long field and LOB data, but you need to restore
the table spaces that contain the table, you must roll forward to
the end of the logs so that all table spaces that contain table data
are consistent.
Note: If you back up a table space that contains
table data without the associated long or LOB fields, you cannot perform
point-in-time rollforward recovery on that table space. All the table
spaces for a table must be rolled forward simultaneously to the same
point in time.
- The following apply for both backup and restore operations:
- Multiple devices should be used.
- Do not overload the I/O device controller bandwidth.
- DB2 database products use
multiple agents to perform both crash recovery and database rollforward
recovery. You can expect better performance during these operations,
particularly on symmetric multi-processor (SMP) machines; using multiple
agents during database recovery takes advantage of the extra CPUs
that are available on SMP machines.
The agent type introduced
by parallel recovery is db2agnsc. DB2 database managers choose the number of agents
to be used for database recovery based on the number of CPUs on the
machine.
DB2 database
managers distribute log records to these agents so that they can be
reapplied concurrently, where appropriate. For example, the processing
of log records associated with insert, delete, update, add key, and
delete key operations can be parallelized in this way. Because the
log records are parallelized at the page level (log records on the
same data page are processed by the same agent), performance is enhanced,
even if all the work was done on one table.
- When you perform a recover operation, DB2 database managers will automatically choose
an optimal value for the number of buffers, the buffer size and the
parallelism settings. The values will be based on the amount of utility
heap memory available, the number of processors available and the
database configuration. Therefore, depending on the amount of storage
available on your system, you should consider allocating more memory
by increasing the util_heap_sz configuration
parameter.