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

Db2 uses a technique called Intra-table-space Parallelism to allow two or more threads to process the data for a single table space. This is beneficial when the table spaces in your database vary widely in size. If the target table space can be processed by multiple threads in parallel it can reduce the overall elapsed time for the Backup operation.

There can be a further benefit if the database is encrypted or the Backup is encrypted or compressed. The work of doing that data transformation can be spread across multiple CPU cores and can improve overall performance.

If your database does not match either of these criteria, there are some cases where the Intra-table-space Parallelism can lead to too much simultaneous I/O and a decrease in performance. If you notice this behavior in your Backup tasks, you can revert to serial processing for each table space by setting the registry variable DB2_BACKUP_ITP=OFF.

If the number of buffers and the buffer size are not specified, then the Db2 database manager sets the values, with minimal effect on large databases. However, for small databases, having the database manager set the values 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. In a small database, this can result in a large percentage of the image size being 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.