Scenario: Improving performance of database backups

Problem

Dave, a system administrator, notices a performance degradation in server database backups after an IBM Storage Protect upgrade.

Goal

Dave expects the time to complete a database backup to be the same as it was before the upgrade.

Data collection

Analysis and determination of the bottleneck

During database backup operations, the database is read and then backed up to the target storage pool. The data is backed up from the disk where the IBM Storage Protect database is located through a host bus adapter (HBA) to the IBM Storage Protect server. The data is transferred from the server through a separate HBA to the tape system.

Dave starts by reviewing data from the API client instrumentation. He finds that Thread 1 shows a backup period of almost 5 hours (17954.687 seconds). About 99% of the time was spent in the API send data category. By reviewing the API client categories in API instrumentation categories, Dave determines that most of the time was spent either sending data to the IBM Storage Protect server through the network, or writing the data to the backup device.

Thread: 1  Elapsed time = 17954.687 sec (1)

Section                  Actual(sec)	Average(msec)	 Frequency used
------------------------------------------------------------------
Waiting on App           168.155	      2.8	          59246
API Send Data          17786.518	    300.3	          59236 (2)
API Query                  0.001	      0.1	          4
API End Txn                0.004	      4.1	          1
API Misc                   0.009	      3.0	          3
Other                      0.000	      0.0	          0
------------------------------------------------------------------

Dave reviews the server instrumentation data. He determines the time when the database backup was started by examining the IBM Storage Protect activity log. He locates the files that were collected by the servermon component, and finds the files that are time-stamped during the database backup. The file names have the following structure: YYYYMMDD-HHMM-show.txt. By examining a file that was time-stamped during the database backup, Dave finds the thread number for the database backup.

Dave searches the data for a node that is named $$_TSMDBMGR_$$, and finds the information for the database backup session:

Session 22486:    Type=Node,   Id=$$_TSMDBMGR_$$
    Platform=DB2/AIX64, NodeId=1, Owner=tsminst
    SessType=4, Index=0, TermReason=0
    threadId=24431
    ProxyByAgent False
    RecvWaitTime=0.000  (samples=0)
    Backup  Objects ( bytes )  Inserted: 0 ( 0.0 )
    Backup  Objects ( bytes )  Restored: 0 ( 0.0 )
    Archive Objects ( bytes )  Inserted: 0 ( 0.0 )
    Archive Objects ( bytes ) Retrieved: 0 ( 0.0 )
    Last Verb ( ConfirmResp ), Last Verb State ( Sent )
    Global id reports 0 mount points in use
    Write MP count 0, read MP count 0 keepUsedMP = No.

The information shows that Thread 24431 is associated with the database backup. Dave opens the server instrumentation report from the same time frame. The file names for server instrumentation reports have the format YYYYMMDD-HHMM-YYYYMMDD-HHMM-instr.txt, where the time stamps represent the start and end times when the output was gathered. He searches for Thread 24431, and finds the following output:

Thread 24431   00:21:34.695-->00:43:20.577
Operation       Count  Tottime  Avgtime  Mintime  Maxtime InstTput  Total KB
----------------------------------------------------------------------------
Network Recv   660678 1190.148    0.002    0.000   64.847  15556.7  18514797(3)
Network Send       21    0.000    0.000    0.000    0.000                  0
Thread Wait     72323  112.404    0.002    0.000   33.003
Unknown                  3.328
----------------------------------------------------------------------------
Total                 1305.881                             14178.0  18514797

Dave finds that most of the time was spent in the Network Recv phase. By reviewing the server instrumentation categories in Server instrumentation categories, he determines that most of the time was spent receiving data from the network.

Dave determines that the network is causing performance degradation. The client and server reports show lengthy times for sending and receiving data over the network

Problem resolution

Dave identifies network settings that were incorrectly set as a result of the upgrade. Dave corrects the settings, and the performance of the database backup time achieves the same level as before the upgrade.