Original: https://www.ibm.com/developerworks/cn/analytics/library/ba-lo-backup-restore-performance-issue-judge-optimize/index.html
Db2 backup/restore thread model
DB2 BACKUP THREAD MODEL
First look at the threading model of the Db2 backup operation, as shown in Figure 1 (see Resources for reference):

Figure 1. Backup thread model

db2agent EDU: Responsible for the start of the backup process, including calculating and creating an optimal number of db2bm/db2med EDUs, allocating the optimal size of the backup buffer, and coordinating the backup work by sending control information to db2bm and db2med.
db2bm EDU: Get a backup buffer from the empty queue, then read the data from its corresponding tablespace into the buffer, and then put the full buffer into the full queue. Simply put, it is reading data from disk into memory.
db2med EDU: Get a backup buffer from the full queue and write it to the backup device. If the backup device is a disk, it is directly responsible for handling I/O operations; if the backup device is a storage manager, such as TSM, NBU, it will pass the buffer to the db2vend process. Simply put, write the data in memory to disk or hand it to the storage manager.
Db2vend process: If a storage manager is used, db2med creates a separate db2vend process that receives data from db2med and calls the vendor API to send the data to the storage manager.
RESTORE THREAD MODEL
The threading model of the restore operation is shown in Figure 2 (see Resources for reference):

Figure 2. Restore thread model

db2agent EDU: Similar to the db2agent EDU function in the backup operation, it will not be described again.
db2med EDU: Get a buffer from the empty queue, read the data from the backup image into the buffer, and then put the buffer into the full queue. Simply put, it is to read the backup data from the disk into the memory. In addition, there is only one db2med EDU, regardless of the number of backup files.
db2bm EDU: Get a buffer from the full queue, and then write the contents to the corresponding table space container. If the table space has multiple containers, the I/O operation will be written in parallel by multiple db2pfchr EDUs. Simply put, the data in memory is written to the container.
Db2vend process: If a storage manager is used, a db2vend process is generated, which is responsible for calling the vendor API to read data from the backup and handing the read data to the db2med EDU.
Parameters that affect performance in the Db2 backup/restore command
This chapter focuses on the parameters that affect the performance of the db2 backup/restore command itself.

INCREMENTAL
The incremental backup contains only the data that has been modified since the last backup. The backup file will be smaller, but it does not mean that the required time is very short: if the table space has not been modified, then the incremental backup is This tablespace will be skipped; but as long as any page in the tablespace has been modified, the incremental backup will scan all the pages in the tablespace and determine whether the page needs to be backed up by the contents of the page header. It can even cause incremental backups to take longer than full backup. Another point to note is that if the number of pages modified by the tablespace is too small, causing the db2med EDU to fail to get data for a long time, the storage manager may think that db2 has been hanged, thus interrupting the connection with db2vend, causing the backup to fail. The corresponding solution is to increase the storage manager’s timeout parameter.

COMPRESS/ENCRYPT
If the backup is enabled for compression or encryption, Db2 will take extra time to run the compression algorithm or encryption algorithm, which will cause a slowdown in backup/recovery.

BUFFER
The size and number of BUFFERs affect backup/restore performance, and Db2 automatically tunes the number and size of buffers, the total size of which is limited by the database configuration parameter util_heap_sz. For offline backups, the total size of the backup buffer is ideally 90% of util_heap_sz. For online backups, the total size of the backup buffer is ideally 50% of util_heap_sz. The number of buffers depends on the number of db2bm and db2med EDUs. If you want to artificially set the number and size, you can specify the parameters in the backup/restore command: ‘WITH num-buffers BUFFERS’ and ‘BUFFER buffer-size’

PARALLELISM
When starting a backup, each db2bm EDU is assigned to a separate tablespace. Tablespaces are backed up in descending order, db2bm

After you have finished a tablespace, you will check if there are any unbacked tablespaces, and if so, choose the one that will be backed up. If not, it will remain idle until the end of the backup. The number of db2bm EDUs depends on the number of CPUs. If you want to modify them manually, you can specify the PARALLELISM parameter in the backup/restore command.

OPEN SESSIONS
If it is backed up to disk, how many paths are specified by the TO option of the backup command, how many db2med EDUs there will be, and each db2med will write the data to its own path; if it is backed up to the storage manager, the number of db2med depends on For the parameter ‘OPEN SESSIONS’. For recovery operations, there is only one db2med EDU.

If DB2_BACKUP_USE_DIO is set to ON, then when backing up, the backup file will not be cached at the operating system level, but will be written directly to disk. This will have a certain impact on the performance of the backup, specifically to improve performance or reduce performance, related to the specific scenario, in the actual need to do testing.

ONLINE BACKUP COMPATIBILITY
If it is an online backup, many operations will be incompatible with it. Depending on the type of table space and operation, online backups may be suspended to wait for other operations to complete or fail. Therefore, online backup should try to open these operations. Specific compatibility list can refer to the appendix

TABLE SPACE BELOW THE HIGH WATER MARK LINE FREE EXTENT
When backing up, the normal, used extent below the high water mark will be read under normal conditions until a backup buffer is read and a read operation is counted. If there is a free extent, the read will be interrupted when the free extent is encountered, then the operation that would only need to be read once will be divided into multiple times, resulting in a slower backup speed, which is especially significant for online backup.

PREFETCHING
As mentioned above, if the table space has multiple containers, the backup is not directly read by the db2bm EDU, but is read in parallel by multiple prefetch threads db2pfchr. Parallel I/O can speed up the read speed. Reduce backup time.

NUMBER OF TARGET DEVICES
If possible, specifying multiple target settings can help speed up your backups.

Backup/recovery progress monitoring
can use db2 list utilities show detail Ordb2pd -utilities Command to view the progress of the backup/restore operation, Listing 1 below is an example of a monitoring backup operation:

Listing 1. Monitoring the progress of the backup operation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$ db2 list utilities show detail
ID = 81
Type = BACKUP
Database Name = SAMPLE
Member Number = 0
Description = offline db
Start Time = 02/28/2018 16:20:08.523339
State = Executing
Invocation Type = User
Throttling:
Priority = Unthrottled
Progress Monitoring:
Estimated Percentage Complete = 28
Total Work = 1187160686 bytes
Completed Work = 337194070 bytes
Start Time = 02/28/2018 16:20:08.523349
From the example, you can see that the total workload estimate is about 1187160686 bytes, the completed workload is 337194070 bytes, and the completion percentage is 28%. You can estimate the end time based on the Start Time and the percentage completed.

Backup/Restore Performance Issues Data Collection
When there is a performance problem in backup/recovery, or when you need to tune, you need to collect enough data to find the bottleneck of the performance problem. The data to be collected mainly includes Db2.

Share/recovery performance statistics, Stack data, db2 trace, and some data at the operating system level

BACKUP/RESTORE PERFORMANCE STATISTICS
Starting with Db2 10.1FP2, each time backup/restore is completed, a message similar to Listing 2 is logged in db2diag.log, listing detailed performance statistics:

Listing 2. Backup/restore performance statistics
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
2018-02-28-06.47.20.921681-480 E548681E1520 LEVEL: Info
PID : 2590 TID : 140213653333760 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.inst105.180228144458
AUTHID : INST105 HOSTNAME: db2a
EDUID : 216 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, database utilities, sqluxLogDataStats, probe:395
MESSAGE : Performance statistics
DATA #1 : String, 1016 bytes
Parallelism = 4
Number of buffers = 4
Buffer size = 16388096 (4001 4kB pages)
BM# Total I/O MsgQ WaitQ Buffers kBytes
— ——– ——– ——– ——– ——– ——–
000 140.23 11.29 0.02 128.72 10 119264
001 140.22 139.54 0.00 0.18 178 2837632
002 140.22 0.42 0.00 139.79 1 1904
003 140.22 0.38 0.00 139.83 1 1920
— ——– ——– ——– ——– ——– ——–
TOT 560.91 151.65 0.02 408.54 190 2960720
MC# Total I/O MsgQ WaitQ Buffers kBytes
— ——– ——– ——– ——– ——– ——–
000 140.94 11.64 128.58 0.01 191 3024788
— ——– ——– ——– ——– ——– ——–
TOT 140.94 11.64 128.58 0.01 191 3024788
The meaning is as follows:

BM. db2bm ID
Total. EDU existence time in seconds
I/O. EDU reads and writes I/O time. If this ratio is high, the bottleneck is likely to be in I/O.
MsgQ. EDU waits for the I/O buffer time. If the proportion of db2bm is high, it means that there is less free buffer, which can increase the number of buffers during backup.
WaitQ. Waiting for control information, if db2bm is higher, the db2bm EDU is idle.
Buffers. Number of I/O buffers processed
Kbytes. Amount of data processed
MC. db2med ID
Compr. The time taken for the compression operation will appear only in the compressed backup
Compr Bytes. Compressed data will appear only in compressed backups
DB2PD –STACK
Stack is a useful piece of data for performance issues. If you are backing up online and there are not many applications, it is recommended to collect the stack of all EDUs, namely the db2pd -stack all command. Otherwise, it is recommended to collect at least the stack of the backup/restore application. Listing 3 is the output of the db2pd -edu command for a backup job. For example, the db2bm EDU name is db2bm.17686.x, where 17686 is the EDU ID corresponding to the backup job:

Listing 3. Find db2agent EDU based on db2bm name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ db2pd -edu
Database Member 0 — Active — Up 1 days 19:26:08 — Date 2018-02-28-11.06.04.936264
List of all EDUs for database member 0
db2sysc PID: 12910796
db2wdog PID: 11403362
db2acd PID: 8585382

EDU ID TID Kernel TID EDU Name

19228 19228 128516107 db2med.17686.0 (SAMPLE) 0
18971 18971 16187529 db2bm.17686.4 (SAMPLE) 0
18714 18714 124846105 db2bm.17686.3 (SAMPLE) 0
18457 18457 127795435 db2bm.17686.2 (SAMPLE) 0
18200 18200 121700431 db2bm.17686.1 (SAMPLE) 0
17943 17943 125304835 db2bm.17686.0 (SAMPLE) 0
17686 17686 21233761 db2agent (SAMPLE) 0
…
Then according to the output of db2pd -agent in Listing 4, find the corresponding Application handle of 17686 is 1473:

Listing 4. Find the application handle based on db2agent
1
2
3
4
5
6
7
$ db2pd -agent
Database Member 0 — Active — Up 1 days 19:26:09 — Date 2018-02-28-11.06.05.114288
Address AppHandl [nod-index] AgentEDUID Priority Type State
…
0x078000000334BE80 1473 [000-01473] 17686 0 Coord Inst-Active
Finally, collect the stack for the application with the application handle of 1473. The command isdb2pd -stack apphdl=1473 -rep 60 2，Indicates that the stack is only collected for 1473, repeated twice, with an interval of 60 seconds.

DB2 TRACE
Some performance issues may also collect db2 traces. Similarly, you can specify apphdl when collecting db2 traces. For example, the application handle in the previous section can execute the commands in Listing 5 to collect traces:

Listing 5 collects traces for the application
1
2
3
4
5
6
7
$ db2trc on -l 512m -t -apphdl 1473
$ db2trc dmp trcBackup.dmp.1
$ sleep 60
$ db2trc dmp trcBackup.dmp.2
$ sleep 60
$ db2trc dmp trcBackup.dmp.3
$ db2trc off
In addition to the Db2 level data mentioned above, operating system level data is also essential, such as vmstat, iostat, and so on.

Backup/Restore Performance Issues Instance
This section gives a few examples of backup/recovery performance issues.

BACKUP PERFORMANCE PROBLEMS CAUSED BY UNEVEN DISTRIBUTION OF TABLE SPACE DATA
First, let’s look at the backup performance problem caused by the uneven distribution of data in a table space. The backup performance statistics of a database are shown in Listing 6:

Listing 6. Performance statistics for a backup
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
2018-02-28-06.47.20.921681-480 E548681E1520 LEVEL: Info
PID : 2590 TID : 140213653333760 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.inst105.180228144458
AUTHID : INST105 HOSTNAME: db2a
EDUID : 216 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, database utilities, sqluxLogDataStats, probe:395
MESSAGE : Performance statistics
DATA #1 : String, 1016 bytes
Parallelism = 4
Number of buffers = 4
Buffer size = 16388096 (4001 4kB pages)
BM# Total I/O MsgQ WaitQ Buffers kBytes
— ——– ——– ——– ——– ——– ——–
000 140.23 11.29 0.02 128.72 10 119264
001 140.22 139.54 0.00 0.18 178 2837632
002 140.22 0.42 0.00 139.79 1 1904
003 140.22 0.38 0.00 139.83 1 1920
— ——– ——– ——– ——– ——– ——–
TOT 560.91 151.65 0.02 408.54 190 2960720
MC# Total I/O MsgQ WaitQ Buffers kBytes
— ——– ——– ——– ——– ——– ——–
000 140.94 11.64 128.58 0.01 191 3024788
— ——– ——– ——– ——– ——– ——–
TOT 140.94 11.64 128.58 0.01 191 3024788
It can be seen that all db2bm EDUs took 560.91 seconds, and only WaitQ took up 408.54 seconds, accounting for 72.8%. Explain that most db2bm EDUs are waiting to do things, relatively idle, only db2bm001 is busy. The reason for this phenomenon is that the data distribution of the table space is not uniform, one table space is relatively large, and the others are relatively small. It can also be seen from the KBytes in the last column of the BM# part: the amount of data processed by db2bm001 is significantly better than other db2bm More EDU.

The corresponding solution is to distribute the data as evenly as possible in different table spaces, so that the workload of each db2bm EDU is basically the same, and there will be no idle db2bm EDU. The premise of improving performance is that CPU, memory, and I/O have no bottlenecks. Otherwise, backup performance may be degraded. For example, if tablespace containers are on the same disk, backing up multiple tablespaces at the same time may cause I/O contention. use.

BACKUP PERFORMANCE ISSUES CAUSED BY TOO MANY FREE EXTENTS
Let’s look at a backup performance problem caused by too many free extents below the high water level in the table space. The specific phenomenon is that about half of the useless tables are deleted. After that, the amount of data backed up is about 54%, but the backup time is only reduced to The original 77%. The backup performance statistics before and after the table is deleted are shown in Listing 7 and Listing 8, respectively:

Listing 7. Backing up performance statistics before deleting a table
1
2
3
4
5
6
7
8
9
10
11
12
13
BM# Total I/O MsgQ WaitQ Buffers kBytes
— ——– ——– ——– ——– ——– ——–
000 167.46 166.95 0.00 0.01 319 5178992
001 167.45 0.45 0.03 166.96 1 608
002 167.45 4.61 0.00 162.83 7 112768
— ——– ——– ——– ——– ——– ——–
TOT 502.37 172.02 0.03 329.81 327 5292368
MC# Total I/O MsgQ WaitQ Buffers kBytes
— ——– ——– ——– ——– ——– ——–
000 169.78 14.93 152.52 0.00 328 5342520
— ——– ——– ——– ——– ——– ——–
TOT 169.78 14.93 152.52 0.00 328 5342520
Listing 8. Backing up performance statistics after deleting a table
1
2
3
4
5
6
7
8
9
10
11
12
13
BM# Total I/O MsgQ WaitQ Buffers kBytes
— ——– ——– ——– ——– ——– ——–
000 129.61 0.22 0.01 129.36 3 608
001 129.60 2.30 0.00 127.28 7 112768
002 129.60 128.72 0.00 0.02 168 2589664
— ——– ——– ——– ——– ——– ——–
TOT 388.81 131.25 0.01 256.67 178 2703040
MC# Total I/O MsgQ WaitQ Buffers kBytes
— ——– ——– ——– ——– ——– ——–
000 131.29 7.44 122.15 0.00 179 2900708
— ——– ——– ——– ——– ——– ——–
TOT 131.29 7.44 122.15 0.00 179 2900708
Comparing the performance statistics before and after, it is found that the backup read rate is about 30MB per second before deleting the table. The calculation method is kBytes of BM# divided by I/O time: 5292368kBytes/172.02=30MB/S, read after backup after deleting the table. The fetch rate is 2703040kBytes/131.25=20MB/S. I think there will be some free extents after deleting the table, so I use the DHWM option of db2dart to look at the corresponding table space high water level. As shown in Listing 9, you can see that there are many EMPTY extents:

Listing 9. db2dart /DHWM check table space results:
1
2
3
4
5
6
7
……
[15992] 5 0x00 [15993] == EMPTY == [15994] 5 0x00 [15995] == EMPTY ==
[15996] 5 0x00 [15997] == EMPTY == [15998] 5 0x00 [15999] == EMPTY ==
[16000] 65534 0x0e [16001] 5 0x00 [16002] == EMPTY == [16003] 5 0x00
[16004] == EMPTY == [16005] 5 0x00 [16006] == EMPTY == [16007] 5 0x00
[16008] == EMPTY == [16009] 5 0x00 [16010] == EMPTY == [16011] 5 0x00
……
Taking the above result as an example, if there is no extent of EMPTY, then these extents from 15992 to 16011 can be read in one read operation. With the EMPTY extent, the read operation encounters an EMPTY extent and is interrupted, so 15992, 15994, 15996, 15998, etc. all require a separate read operation, resulting in a slower read rate, which affects the performance of the backup. Usedb2 list tablespaces show detail You can also see the table space status as shown in Listing 10:

Listing 10. Table space makes the situation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Tablespace ID = 3
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 2621440
Useable pages = 2621438
Used pages = 647416
Free pages = 1974022
High water mark (pages) = 1294746
Page size (bytes) = 4096
Extent size (pages) = 2
Prefetch size (pages) = 2
Number of containers = 1
Minimum recovery time = 2018-03-05-02.47.06.000000
You can see from Listing 10 that the high water level is 1294746, but the Used Pages only has 647416. The corresponding solution is to usedb2 alter tablespace lower high water mark The command lowers the high water level of the table space. After the water level is lowered, the backup is again tested for 93.36 seconds, which is reduced to 55% of the initial 169.78 seconds, which is expected to be achieved.

RECOVERY PERFORMANCE ISSUES CAUSED BY PRE-ALLOCATION OF LINUX FILE SYSTEMS
Looking at a problem encountered during recovery, the customer feedback said that when doing the Db2 recovery test, passdb2pd -uti Ordb2 list utilities show detail It has been monitored for a long time without any progress (HANG live), but the file system has less space available. This phenomenon was only found on Linux and is not the case on AIX. So I collected some data. First, let’s take a look at the stack of db2med and db2bm when the performance is normal. As shown in Listing 11, you can see that db2med is reading data from FileDevice and db2bm is writing data:

Listing 11: Stack for db2med and db2bm during normal recovery
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
db2med:
< StackTrace >
——FUNCTION——
read
sqloread
sqloreadEx
sqluReadFromFileDevice
sqluMCReadFromDevice
sqluMCReadFromDev4Restore
sqluMCContinueRestore
sqluMCProcessRestoreStatesP
sqluMCStartRestoreMediaControllerPcj
…
StackTrace >
db2bm
< StackTrace >
——FUNCTION——
pwrite
sqloseekwrite64
sqloWriteBlocks
sqlbDMSMapAndWrite
sqlbDMSDirectWrite
sqlbDirectWrite
sqluWriteDMS
sqludBMCont
sqludbufPcj
…
StackTrace >
Let’s take a look, “HANG” live, db2med and db2bm stack, as shown in Listing 12, db2med is not reading data; db2bm is indeed writing, but not write backup data, which is more critical function: RestoreContainers – > DMSAddContainerRequest -> SetFileSize -> sqlowrite, from these functions, what db2bm is doing is to handle the add container request (DMSAddContainerRequest), set the container size (SetFileSize), and then call sqloowrite to achieve the pre-allocation of the container space.

Listing 12. HANG live stack of db2med and db2bm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
db2med:
< StackTrace >
——FUNCTION——
msgrcv
sqlorqueInternal
sqlorque2
sqluReadBufferFromQueue
sqluMCContinueRestore
sqluMCProcessRestoreStatesP
sqluMCStartRestoreMediaControllerPcj
…
StackTrace >
db2bm:
< StackTrace >
——FUNCTION——
write
sqlowrite
sqloSetFileSize
sqlbServiceAddDMSContainerRequest
sqlbDMSAddContainerRequest
sqlbDoDMSAddContainerRequests
sqlbASSetPoolContsForDMS
sqlbASDefineContainersForDMS
sqlbRestoreASContainers
sqlbRestoreContainers
sqlbRestorePoolDef
sqludProcessSPCS
sqludBMInit
sqludbufPcj
sqloEDUEntry
…
StackTrace >
At this point, the problem is clear: the database restore operation is not really HANG live, but db2 is pre-allocating the DMS table space container, and the data can be resumed after the container allocation is completed. After checking the file system, it is found to be in EXT3 format. The file system in EXT3 format does not support fast-allocation. The operation of creating files is very slow. Operations such as creating databases, restoring databases, and creating containers are affected. The solution is to use a file system that supports fast-allocation, such as GPFS, JFS2 (AIX). If it is a LINUX system, consider upgrading EXT3 to EXT4.

Conclusion
The premise of optimization is to have a deep understanding of the principles behind it, so this article starts with the threading model of Db2 backup/restore, and then details the internal and external reasons that affect the performance of backup/restore. Finally, several examples are given for backup/ Data collection and analysis methods for restore performance issues. Due to space limitations, the knowledge points covered in this article cannot be comprehensive. Interested readers can read the documentation in the reference materials. When they encounter problems, they can also collect data and submit them to the IBM Technology Center for assistance.