Tuning Backup Server dump and load operations

We can use following traces to give us a better picture about what goes on when a dump or load happens

Following traces are present as a part of backupserver binary:

BACKUP SERVER TRACES

Backup Server trace flag: 1 is on. This flag does the following: Prints blocksize used during a dump or a load.
Backup Server trace flag: 2 is on. This flag does the following: Prints i/o optimization parameters used during a dump or a load.
Backup Server trace flag: 3 is on. This flag does the following: Allow dumping to the /dev/null device.
Backup Server trace flag: 4 is on. This flag does the following: Prints allocation percentage of the allocation units during dump.
Backup Server trace flag: 5 is on. This flag does the following: Prints the database pagesize used during a dump or a load.

Commands to set them on/off

Using 0 will display the traces set

SYB_BACKUP...qatraceon 0,1,2,3,4,5
SYB_BACKUP...qatraceoff 1,2,3,4,5

Findings:

Increasing stripes upto a certain amount can minimise number of io made or time taken during a dump or load by sybmultbuf to read /write on dumps/devices.
Increasing shared memory available to backup server via -m on backup run server can also help on performance tuning of dumps and loads.

There are few important terms in traces which give us an idea about the io sizes, number of zones used

Dbiosize the database io size to be used on the dump file during dumping a particular stripe
Iocount Total number of IO made for the dump or load session
Zonesize Not very clear but aids performance if the numzones are higher in number
Blocksize the block size to be used
Numzones the more the numzones , the better is performance ( ranges from 1 to 5 )



Dump Cases Considered under tracing for qatraceon 1,2 and 3 :

A.dumping into one stripe with shared memory 100MB

1> dump database sag to '/OracleDumps/opctlsy2/sag.dmp'

2> Backup Server session id is: 33. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.173.1.1: The database pagesize is '2048' bytes.
Backup Server: 6.28.1.1: Dumpfile name 'sag1000609C09 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp'
Backup Server: 4.166.1.1: Using dbiosize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.1: Using iocount of 7 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.2: Using zonesize of 458752 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.2: Using numzones of 2 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.

Notice that the iocount is 7, dbiosize is 65536 bytes, numzones are 2

B. dumping into one stripe with shared memory double

1> dump database sag to '/OracleDumps/opctlsy2/sag.dmp'
2>
Backup Server session id is: 12. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A4F5 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp'
Backup Server: 4.166.1.1: Using dbiosize of 262144 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.1: Using iocount of 7 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.2: Using zonesize of 1835008 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.2: Using numzones of 3 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 4030 ms.

Notice the iocount 7 but dbiosize 262144 bytes, numzones 3, time is 4030 ms

C.dumping on to 2 stripes

dump database sag to '/OracleDumps/opctlsy2/sag.dmp1' stripe on '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server session id is: 33. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A9C7 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp1'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.1: Using iocount of 3 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.2: Using zonesize of 1572864 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A9C7 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.1: Using iocount of 3 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.2: Using zonesize of 1572864 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 3980 ms.

Notice iocount reduced to 6, numzones are 4, dbiosize is 524288, time taken 3980 ms - Performance better as less io, less time


D.dumping to 3 stripes

dump database sag to '/OracleDumps/opctlsy2/sag.dmp1' stripe on '/OracleDumps/opctlsy2/sag.dmp2' stripe on '/OracleDumps/opctlsy2/sag.dmp3'
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A5E2 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.1: Using iocount of 2 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A5E2 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp1'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.1: Using iocount of 2 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A5E2 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp3'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.1: Using iocount of 2 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 4026 ms.

Notice iocount total 6, dbiosize 524288 but zonesize has reduced per dump still it uses numzones as 4, time is 4026ms


E. dumping to 4 stripes

dump database sag to '/OracleDumps/opctlsy2/sag.dmp1' stripe on '/OracleDumps/opctlsy2/sag.dmp2' stripe on '/OracleDumps/opctlsy2/sag.dmp3' stripe on '/OracleDumps/opctlsy2/sag.dmp4'
Backup Server session id is: 26. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp4'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp1'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp3'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 3983 ms.

Notice the iocount as 4 now, dbiosize of 1048576 bytes, numzones as 4

Similarly we can tune the loads based on the traced optimisation parameters.