Optimizing backup performance

When you perform a backup operation, the Db2® database manager automatically chooses 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. Therefore, depending on the amount of storage available on your system, consider allocating more memory by increasing the util_heap_sz configuration parameter.
The objective is to minimize the time it takes to complete a backup operation. Unless you explicitly enter a value for the following BACKUP DATABASE command parameters, the Db2 database manager selects one for them:
  • WITH num-buffers BUFFERS
  • PARALLELISM n
  • BUFFER buffer-size

If the number of buffers and the buffer size are not specified, resulting in the Db2 database manager setting the values, it should have minimal effect on large databases. However, for small databases, it can cause a large percentage increase in backup image size. Even if the last data buffer written to disk contains little data, the full buffer is written to the image anyway. In a small database, this means that a considerable percentage of the image size might be empty.

You can also choose to do any of the following to reduce the amount of time required to complete a backup operation:

  • Specify table space backup.

    You can back up (and subsequently recover) part of a database by using the TABLESPACE option on the BACKUP DATABASE command. This facilitates the management of table data, indexes, and long field or large object (LOB) data in separate table spaces.

  • Increase the value of the PARALLELISM parameter on the BACKUP DATABASE command so that it reflects the number of table spaces being backed up.

    The PARALLELISM parameter defines the number of processes or threads that are started to read data from the database and to compress data during a compressed backup operation. Each process or thread is assigned to a specific table space, so there is no benefit to specifying a value for the PARALLELISM parameter that is larger than the number of table spaces being backed up. When it finishes backing up this table space, it requests another. Note, however, that each process or thread requires both memory and CPU overhead.

  • Increase the backup buffer size.

    The ideal backup buffer size is a multiple of the table space extent size plus one page. If you have multiple table spaces with different extent sizes, specify a value that is a common multiple of the extent sizes plus one page.

  • Increase the number of buffers.

    Use at least twice as many buffers as backup targets (or sessions) to ensure that the backup target devices do not have to wait for data.

  • Use multiple target devices.

  • Consider enabling the DB2_REDUCE_FLUSHING_DURING_BACKUP performance registry variable to reduce the flushing of changed pages within the bufferpools for online backup operations.