75 ways to demystify DB2: #27: Techtip: Does your restore take less time than your backup when all other factors are equal?

Body

Most default installations of DB2 when bundled with other products lays out the database and storage spaces in its most basic form. Usually it has USERSPACE1 where all of the data are stored by default. Over time as the data grows your so does the time it takes to backup your database bringing it to unacceptable levels.

It has also been observed that in such situations a restore operation of the same database completes in a fraction of the time. To explain this phenomenon it is important to understand the Buffer Manipulator (BM).

Buffer Manipulator:   

• Reads data from the disk into the shared memory buffers during backups.
• It is controlled by the PARALLELISM option in the backup command.
• When full it sends the full buffer to db2med thread which writes it out to the media.

We can have only one BM per dbspace, so in an instance where most of the data resides in one tablespace, increasing PARALLELISM does little to improve backup performance. The BM thread belonging to the smaller table spaces completes it work and waits on the BM working the largest tablespace. Effectively the total backup time is a function of the time it takes to backup your largest tablespace.

In contrast during a restore operation DB2 is able to distribute its data across all the available BM, hence achieving a larger degree of parallelism.

As an example, if you have a 1 TB database, with 80% of the data residing in one tablespace, and your backup operation uses 8 for parallelism. 7 BM may finish its operations while the 8^th grinds through 800 GB resident in your largest tablespace.

During restore however, the 8 BM manage 125 GB of data each. In conclusion, with all things being equal, your backup will take however long it takes to process 800 GB of data where as a restore would be the time taken to process 125 GB of data. That accounts for approximately 6x time factor in the performance of a restore v/s a backup.

Additionally, if you run your backups with a compress option, a compress takes about 2-3x more time than decompress.

In conclusion, follow the best practices for tablespace design, distribute your data evenly as you possible can across tablespaces and be wise and perform regular backups.