Understanding parallel database backups

When you perform a server-side image backup using the Backup utility (dbbackup) by specifying the -s option, or by using the BACKUP DATABASE statement, a parallel database backup is performed. Parallel backups use physical device-level parallelism to decrease the overall time required to complete a backup operation. Parallel backups are not supported on Windows Mobile.

The database server creates a reader thread for each drive on which database files are stored. A writer thread is created for the destination drive where the backup directory is located. Using separate readers and writers allows I/O operations to be performed in parallel, instead of sequentially.

The performance of a parallel backup is limited by the slowest component in the system. In most cases, the bottleneck is likely a physical disk, but it could also be any of the other components, such as the I/O controller or the system bus. Each of these components has a maximum rate at which they can transfer data.

The BACKUP DATABASE statement and the Backup utility (dbbackup) provide options that let you configure the behavior of a parallel backup, including:

  • when and how the checkpoint log is copied
  • the maximum number of pages used at a time to transfer data from the database server to dbbackup (only available when using dbbackup)
  • adding more writers (BACKUP statement only)

Backups should always be made to a separate physical drive. This not only provides a performance benefit from the I/O parallelism, but also improves the safety of the data in the event of a hardware failure.

See also
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0