Reducing Backup Runtimes by Redistributing Tables over Tablespaces
JoernKlauke 27000297FP Visits (8315)
DB2 Backup is able to utilize IO parallelism by reading and backing up multiple tablespaces at the same time. Number of concurrent threads reading the tablespaces (called "buffer manipulators", with db2bm.X EDU name) is controlled by "PARALLELISM" keyword. By default the value will be computed based on the size of utility hep, number of tablespaces and CPUs.
Each tablespace in a DB2 database is read by one single buffer manipulator (db2bm EDU). The backup starts with the largest tablespace. As soon as the backup of one tablespace is completed successfully, the next tablespace is read until all tablespaces are backed up.
It is possible that the largest tablespace has reached a size that is larger than all other tablespaces together. This leads to a situation that this tablespace determines the complete backup runtime.
This situation can be checked in the barstats written into the db2diag.log after each backup. One of the db2bm will show a substantially longer runtime than the other db2bm EDUs:
As you can see buffer manipulator 000 is doing most of the I/Os (note "I/O" column - time spent doing IO and "MBytes" - number of megabytes read) whereas the other manipulators are waiting the most of the time ("WaitQ", time when given db2bm thread is just waiting for others to complete)
The reason for this in this example is the following imbalance of the sizes of the tablespaces:
$ db2 "select subs
As the result, there is very little benefit from backup parallelism because there is a single tablespace that contains majority of the data.
The following tables reside in the tablespace USERSPACE1:
$ db2 "select subs
To relax this situation a little bit this tablespace can be split and some of the tables, namely DATA5 and DATA3 could be moved to a new tablespace. This would have the size of the tablespace USERSPACE1 and should limit the backup runtime. In this example it would even be better to split the tablespace into three.
Create a New Tablespace
In the first step a new tablespace needs to be created. To find out the parameters for USERSPACE1 the following command can be used:
db2look -d <dbname> -l
The following snippet can be found in the output:
CREATE LARGE TABLESPACE "USERSPACE1" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 8192 MANAGED BY AUTOMATIC STORAGE
USING STOGROUP "IBMSTOGROUP"
INITIALSIZE 32 M
DATA TAG INHERIT
DROPPED TABLE RECOVERY ON;
It may be possible that the tablespace was part of the create database statement. If so you can find the parameters in the output of the following command:
db2look -d <dbname> -createdb
Now the tablespace USERSPACE2 can be created in the same way as USERSPACE1.
Now it is time to move tables from the old tablespace USERSPACE1 to the new tablespace USERSPACE2. For this purpose the procedure ADMIN_MOVE_TABLE will be used. This procedure takes the following parameters:
So in this case for table DATA5 the following command would be issued:
$ db2 “CALL SYSP
This command will move table DATA5 to the new tablespace USERSPACE2. Note that other tablespace can be specified for index and lob tablespaces. The table DATA3 will be moved accordingly.
Shrinking the old tablespace
The final step is to shrink the old tablespace. If autostorage is used this can be performed by the following simple command:
db2 alter tablespace USERSPACE1 reduce max
The movement of the extents can be monitored with the following query:
$ db2 "SELECT * FROM TABL
TBSP_NAME TBSP_ID CURRENT_EXENT LAST_EXTENT NUM_EXTENTS_MOVED NUM_EXTENTS_LEFT TOTAL_MOVE_TIME
--------------- ------- ------------- ----------- ----------------- ---------------- ---------------
7 record(s) selected.
The sizes of the tablespaces of the database look like the following now:
$ db2 "select subs
The backup statistics show the following picture now:
At the beginning the backup was taken in 238 seconds. The runtime was decreased to 202 seconds only be re-balancing the data in the tablespaces. The impact will even be higher on enterprise server systems.
For further information on db2look check the following link: http
For further information on admin_move_table check the following link: http
Further information for backup and restore statistics (barstats) can be found here: http