Example output for backup performance monitoring
This topic presents an example of the output that is displayed from running the db2pd command with the -barstats option, for a running backup operation.
Each example section is followed by a table that provides detailed descriptions of the fields that appear in the results.
Examples
First, the agent or
EDU identifiers for the backup operation are
displayed:
$ db2pd -barstats 994 -db sample
Printing out Backup Runtime Statistics at 2024-04-11-15.03.33.396789:
Backup Related EDUs:
---------------------------------------------------------------------
Backup agent ID: 994
BM 0 (EDU ID): db2bm.994.0 (1131)
BM 1 (EDU ID): db2bm.994.1 (1130)
BM 2 (EDU ID): db2bm.994.2 (1128)
BM 3 (EDU ID): db2bm.994.3 (1129)
BM 4 (EDU ID): db2bm.994.4 (1127)
MC 0 (EDU ID): db2med.994.0 (1132)| Field | Description |
|---|---|
| Backup agent ID | Represents the EDU ID for the main backup agent, which coordinates the various backup EDUs. |
| BM N (EDU ID): db2bm.M.N | Represent the backup Buffer Manipulator EDU identifiers. These EDUs read data from the table space container files. In this example, there are four Buffer Manipulator EDUs, so the backup operation is running with a parallelism of four. |
| MC N (EDU ID): db2med.M.N | Represent the backup Media Controller EDU identifiers. These EDUs write data to one or more backup image files. In this example, since only one backup image path is specified, the backup operation is using one media controller EDU. |
Next, information about each table space that is processed by the backup is
displayed:
Table Spaces to be Backed Up (appTblSpace):
---------------------------------------------------------------------
numEntry: 2
Table Spaces:
tblSpaceName: SYSCATSPACE
tblSpaceID: 0
tblSpaceType: 2
tblSpaceDataType: 0
tblSpaceSize: 36208 (in 4K pages)
dataNotBackedUp: 148209664 (in bytes)
nContainers: 1
backupInProgressTurnedOn: T
backupActiveIsSet: T
autoResizeLockAcquired: T
extentMovementLockAcquired: T
olbLockAcquired: T
userSpecified: T
tblSpaceTotalPages: 20476
pageSize: 8192
extSize: 4
actualSize: 148307968
scanPages: T
backupLSN: 00000000000566A2
tblSpaceName: USERSPACE1
tblSpaceID: 2
tblSpaceType: 2
tblSpaceDataType: 32
tblSpaceSize: 3648 (in 4K pages)
dataNotBackedUp: 14155776 (in bytes)
nContainers: 1
backupInProgressTurnedOn: T
backupActiveIsSet: T
autoResizeLockAcquired: T
extentMovementLockAcquired: T
olbLockAcquired: T
userSpecified: T
tblSpaceTotalPages: 4064
pageSize: 8192
extSize: 32
actualSize: 14942208
scanPages: T
backupLSN: 00000000000566A2| Field | Description |
|---|---|
| tblSpaceName | The name of the table space. (See tablespace_name). |
| tblSpaceID | The table space ID. (See tablespace_id). |
| tblSpaceType | The table space type. (See tablespace_type). |
| tblSpaceDataType | The table space content type. (See tablespace_content_type). |
| tblSpaceSize | The table space usable size (number of 4 KB pages). (See tablespace_usable_pages). |
| dataNotBackedUp | The number of bytes in the table space that have not yet been read. |
| nContainers | The number of table space containers. (See tablespace_num_containers). |
| backupInProgressTurnedOn | A flag that indicates whether a backup of this table space is in progress. This flag is turned off when all pages have been read for the backup. It is used internally by Db2 to alter the behavior of certain table space container operations to maintain data integrity. |
| backupActiveIsSet | A flag that indicates whether a backup is active for this table space. This flag is turned off at the very end of the backup. It protects against running two simultaneous backups on the same table space. |
| autoResizeLockAcquired | A flag that indicates whether the table space auto-resize lock is acquired. The lock prevents a table space auto resize operation from occurring while the online backup is running. |
| extentMovementLockAcquired | Indicates whether the extent movement lock has been acquired for this table space. |
| olbLockAcquired | Indicates whether the Online Backup lock has been acquired for this table space. This lock blocks certain operations on the table space to ensure that data is recoverable. |
| userSpecified | Indicates that the table space was requested to be part of the current backup operation. |
| tblSpaceTotalPages | The total size of the table space, including unusable meta pages (number of 4 KB pages). (See tablespace_total_pages). |
| pageSize | The size of a page for the table space (in bytes). (See tablespace_page_size). |
| extSize | The number of pages that constitute an extent for the table space (in pages). (See tablespace_extent_size). |
| actualSize | The table space total size (in bytes). |
| scanPages | A flag that indicates the need for table space page scanning for an incremental backup operation. |
| backupLSN | The Log Sequence Number for the backup operation on the table space. |
Next, an ordered list of table space processing states is displayed:
Table Space Queue:
[0]
tblSpaceName: SYSCATSPACE
tblSpaceID: 0
Intra-tablespace parallelism Info:
tbspSegmentListLen:17
tbspSegmentExtents:289
maxBMs:5
activeBMs:0
[0]
firstPage: 0
numPages: 12
status: complete
[1]
firstPage: 12
numPages: 1156
status: init
[2]
firstPage: 1168
numPages: 1156
status: init
[3]
firstPage: 2324
numPages: 1156
status: init
[4]
firstPage: 3480
numPages: 1156
status: init
[5]
firstPage: 4636
numPages: 1156
status: init
[6]
firstPage: 5792
numPages: 1156
status: init
[7]
firstPage: 6948
numPages: 1156
status: init
[8]
firstPage: 8104
numPages: 1156
status: init
[9]
firstPage: 9260
numPages: 1156
status: init
[10]
firstPage: 10416
numPages: 1156
status: init
[11]
firstPage: 11572
numPages: 1156
status: init
[12]
firstPage: 12728
numPages: 1156
status: init
[13]
firstPage: 13884
numPages: 1156
status: init
[14]
firstPage: 15040
numPages: 1156
status: init
[15]
firstPage: 16196
numPages: 1156
status: init
[16]
firstPage: 17352
numPages: 752
status: init
[1]
tblSpaceName: USERSPACE1
tblSpaceID: 2
Intra-tablespace parallelism Info:
tbspSegmentListLen:3
tbspSegmentExtents:37
maxBMs:5
activeBMs:0
[0]
firstPage: 0
numPages: 96
status: complete
[1]
firstPage: 96
numPages: 1184
status: init
[2]
firstPage: 1280
numPages: 544
status: init| Field | Description |
|---|---|
| tbspSegmentListLen | The number of segments that the table space has been split into to facilitate Intra-Parallel processing. |
| tbspSegmentExtents | The number table space extents in each segment. |
| maxBMs | The maximum number of Buffer Manipulator EDUs that can process data for this table space simultaneously. |
| activeBMs | The current number of Buffer Manipulator EDUs that are processing data for this table space. |
| firstPage | The first table space page in the specified segment. |
| numPages | The number of table space pages included in the specified segment. |
| status |
The status of processing for the specified segment. The following values are possible:
|
Next, backup performance statistical information is displayed. The displayed values are
aggregated up to the time of collection. Final information is displayed in the Db2® diagnostic log,
db2diag.log, upon completion of the backup operation. Interpretation of this
statistical information can be aided by existing resources, such as Backup and Restore Statistics or Understanding and Tuning DB2/LUW Backup and Restore
Performance.
Note: If a column is not applicable to a particular Backup or Restore operation,
it is not displayed.
Performance statistics:
---------------------------------------------------------------------
Parallelism = 5
Number of buffers = 10
Buffer size = 9441280 (2305 4kB pages)
BM# Total I/O MsgQ WaitQ Throttle LogPhase Buffers Bytes LogBytes
--- -------- -------- -------- -------- -------- -------- -------- -------- --------
000 0:00:21 0:00:00 0:00:00 0:00:20 0:00:00 0:00:00 1 98304 0
001 0:00:21 0:00:00 0:00:00 0:00:20 0:00:00 0:00:00 0 786432 0
002 0:00:21 0:00:00 0:00:00 0:00:20 0:00:00 0:00:00 0 0 0
003 0:00:21 0:00:00 0:00:00 0:00:20 0:00:00 0:00:00 0 0 0
004 0:00:21 0:00:00 0:00:00 0:00:20 0:00:00 0:00:00 0 0 0
--- -------- -------- -------- -------- -------- -------- -------- -------- --------
TOT - - - - - - 1 884736 0
MC# Total I/O MsgQ WaitQ Buffers Bytes
--- -------- -------- -------- -------- -------- --------
000 0:00:21 0:00:00 0:00:00 0:00:00 2 9445376
--- -------- -------- -------- -------- -------- --------
TOT - - - - 2 9445376| Field | Description |
|---|---|
| BM# | The Buffer Manipulator EDU identifier. |
| MC# | The Media Controller EDU identifier. |
| Total | The total elapsed time that this EDU has been in backup processing. |
| I/O | The amount of time that this EDU spent either reading or writing data. |
| MsgQ | The amount of time that the EDU spent waiting to get a buffer. For backup operations, the Buffer Manipulator EDUs wait for an empty buffer to fill with data from the table space. The Media Controller EDUs wait for a full buffer and then write the buffer out to the backup image file. |
| WaitQ | Time spent waiting for a state machine control message. When a Buffer Manipulator EDU has no more table spaces to process, the WaitQ value increases, while other Buffer Manipulators continue to process their table spaces. |
| Compr/Encr | The amount of time that this EDU spent compressing or encrypting data. |
| Throttle | The amount of time that this EDU was paused due to utility throttling. |
| LogPhase | The amount of time that this EDU spent processing recovery logs. |
| RmtStorg | The amount of time that this EDU spent communicating with a Cloud Object Storage platform. Due to internal implementation details, sometimes part of this value can be attributed to the I/O column. |
| Buffers | The total number of I/O buffers that were processed by this EDU. |
| Bytes | The quantity of data that was processed by this EDU. The column header can also be displayed as KBytes or MBytes depending on the quantity of data processed. |
| LogBytes | The quantity of recovery log data processed by this EDU. |
Next, size estimate information is displayed:
Size estimates:
---------------------------------------------------------------------
Total size estimate (bytes): 193081344
Pre-adjusted total size estimate (bytes): 193081344
Init data estimate (bytes): 1863840
User data estimate (bytes): 163250176
End data estimate (bytes): 4122136
Size estimate for MC1 (bytes): 193081344
Size estimate for remaining MCs (bytes): 190984192| Field | Description |
|---|---|
| Total size estimate (bytes) | An estimate of the total amount of data to process during the backup operation. |
| Pre-adjusted total size estimate (bytes) | An estimate of the total amount of data to process during the backup operation. |
| Init data estimate (bytes) | An estimate of the size of database meta information to be included at the beginning of the backup image. |
| User data estimate (bytes) | An estimate of the size of table space container data to be included in the backup image. |
| End data estimate (bytes) | An estimate of the size of database meta information to be included at the end of the backup image. |
| Size estimate for MC1 (bytes) | An estimate of the amount of data processed by Media Controller 1, which includes the database meta information. |
| Size estimate for remaining MCs (bytes) | An estimate of the amount of data processed by all other Media Controllers. |
Next, current work progress information is displayed:
Progress Monitor:
---------------------------------------------------------------------
Phase #: 1 of 1
Total work units (bytes): 193096284
Completed work units (bytes): 1878780
Next, the time that is required to flush (persist) changed pages from one or more buffer pools
into stable storage are displayed.
Note: Database and table space flushing semantics are beyond the
scope of this document.
Also, the time that is required to update the database history file
and table space change history meta information is displayed, along with log file processing
information (when the online backup INCLUDE LOGS option was
specified).Other Backup Statistics:
---------------------------------------------------------------------
Database bufferpool flushing time: 0.18 seconds
Table space bufferpool flushing time 1: 0.02 seconds
Table space bufferpool flushing time 2: 0.44 seconds
Table space bufferpool flushing time 3: Not Recorded
Database recovery history file (db2rhist.asc) elapsed processing time: 0.27 seconds
Table space change history file (db2tschg.his) elapsed processing time: 0.08 seconds
No extraction logs are included in this image.
Total time extraction log file activity has been restricted: 0.03 seconds
Processed 2 of 10 database recovery logs.
Database recovery logs elapsed processing time: 0.95 seconds
Active log file extent range for member 0: S0000004.LOG - S0000013.LOG [4 - 13].And lastly, when the automatic pruning of recovery objects is enabled (that is,
AUTO_DEL_REC_OBJ database configuration parameter is enabled), additional
information is displayed about
pruning.
Database Recovery History File Pruning Statistics:
---------------------------------------------------------------------
Auto deletion of recovery objects (AUTO_DEL_REC_OBJ): ON
Number of database backups to retain (NUM_DB_BACKUPS): 1
Recovery history retention (days) (REC_HIS_RETENTN): 0
Status: In progress
Elapsed processing time: 8.60 seconds
Number of entries pruned: 1
Number of objects deleted: 108
Current state: Deleting
Object Details:
Objpart: 20240320150330001
Operation: B
Location: adsm/libtsm.a
Vendor State Info:
startTime: 20240411134749
function: sqluvdel| Field | Description |
|---|---|
| Status | The status of the recovery object pruning, if enabled. Possible values are:
|
| Processing time | The time spent pruning expired recovery objects. |
| Number of entries pruned | The current number of recovery history file entries pruned. |
| Number of objects deleted | The current number of expired recovery objects (backup images, log files) that have been deleted. |
| Current state | The current state of object deletion or pruning: Possible values are:
|
| Object details | Various (internal) details about the recovery object currently being processed. |