Compaq ProLiant 1000 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 42

Compaq Backup and Recovery for Microsoft SQL Server 6.x, maximum throughput achievable with a single

Get Compaq ProLiant 1000 PDF manuals and user guides View all Compaq ProLiant 1000 manuals
Add to My Manuals
Save this manual to your list of manuals

Page 42 highlights

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 42 is about the same as with 11 of the 15/30's. The throughput has also doubled from the first drive to the second, showing SQL Server's ability to take advantage of a second high-speed drive. After the second drive, further performance increase is less pronounced, as the bottleneck begins to shift from the tape drives to the disk subsystem. With four drives a throughput of 48.5 GB/hr is achieved, which is close to the maximum read rate observed earlier for a single Smart-2 Array controller in this environment. The situation encountered here, with respect to the highest backup rate observed, is similar to what was seen when sending data to disk storage. Indeed, a single 35/70-GB DLT drive can receive data at a rate about the same as a RAID-5 disk array on a single Smart-2 SCSIport (see Chart-5). Without going to a configuration involving multiple controllers interleaved with software RAID66, we can consider these results as approaching the maximum throughput achievable with a single SQL Server database dump process on the current hardware.67 An even higher overall throughput can be realized from the system when backing up more than one database in parallel, which will be demonstrated in the next section. A note about so-called "maximum" system throughput: In the previous section we discussed hardware latencies that can contribute to a decrease in system performance scalability. We should also point out that I/O requests from the application to the hardware have to go through numerous software layers as well (operating system, tape driver, miniport driver, etc), any one or a combination of which could introduce software latencies or contention points when multiple such requests are being issued for multiple devices. The software environment is significant in determining a system's performance, and the same hardware could produce different results when driven by different software. This section discusses dumping multiple databases concurrently; that is, having SQL Server dump more than one database at the same time (in parallel). If more than one database resides on the same SQL Server, this strategy can be used to increase the backup capability (overall throughput) of the server. When employing multiple devices to perform such concurrent database dumps, be aware of the following considerations: You can perform one dump (transaction log or database) per dump device. To dump two databases simultaneously to tape, you need to have installed two tape drives. These drives can coexist on the same controller and in the case of DLT drives, the recommended limit of two such drives per controller (discussed earlier) relates here as well. The same applies to load operations. Previous versions (4.21x) of SQL Server upon startup allocated only three internal buffers dedicated to dump and load operations. If all three buffers were consumed, as with three simultaneous dump or load processes, attempting a fourth dump or load would yield little gain in overall throughput. SQL Server 6.x now provides the capability for up to 32 simultaneous dump or load processes, each with its own backup buffer. The backup_threads parameter must be set to equal the maximum number of devices that will be used at one time, as in the case of striped dumps. A single database cannot be dumped concurrently to more than one device at a time. Only after the first dump command completes can another dump for the same database begin. In order to effectively employ multiple concurrent dumps, sequential I/O must be preserved on both the source and destination volumes so as not to introduce disk head contention. 66 See earlier section entitled Establishing Maximum Throughput at the Server. 67 When SQL Server 6.5 database dump tests were run on previous generation (EISA-based) Compaq servers (i.e: the Proliant 4500), the maximum throughput achieved was about 25 GB/hr, due mainly to limitations imposed by EISA bus bandwidth. This throughput was about half of what is seen on the PCI-based Proliant 5000. Future hardware from Compaq will feature technology enhancements that may push the current numbers even higher.

  • 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
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
Compaq Backup and Recovery for Microsoft SQL Server 6.x
Page
42
is about the same as with 11 of the 15/30’s.
The throughput has also doubled from the first drive to the
second, showing SQL Server’s ability to take advantage of a second high-speed drive.
After the second
drive, further performance increase is less pronounced, as
the bottleneck begins to shift from the tape
drives to the disk subsystem
.
With four drives a throughput of 48.5 GB/hr is achieved, which is close
to the maximum read rate observed earlier for a single Smart-2 Array controller in this environment.
The situation encountered here, with respect to the highest backup rate observed, is similar to what was
seen when sending data to disk storage.
Indeed, a single 35/70-GB DLT drive can receive data at a rate
about the same as a RAID-5 disk array on a single Smart-2 SCSIport (see Chart-5).
Without going to a
configuration involving multiple controllers interleaved with software RAID
66
, we can consider these
results as approaching the
maximum throughput achievable with a single SQL Server database dump
process on the current hardware.
67
An even higher overall throughput can be realized from the system
when backing up more than one database in parallel, which will be demonstrated in the next section.
A note about so-called “maximum” system throughput:
In the previous section we discussed hardware
latencies that can contribute to a decrease in system performance scalability.
We should also point out
that I/O requests from the application to the hardware have to go through numerous software layers as
well (operating system, tape driver, miniport driver, etc), any one or a combination of which could
introduce software latencies or contention points when multiple such requests are being issued for
multiple devices.
The software environment is significant in determining a system’s performance, and
the same hardware could produce different results when driven by different software.
This section discusses dumping multiple databases concurrently; that is, having SQL Server dump
more than one database at the same time (in parallel).
If more than one database resides on the same
SQL Server, this strategy can be used to increase the backup capability (overall throughput) of the
server.
When employing multiple devices to perform such concurrent database dumps, be aware of the
following considerations:
You can perform one dump (transaction log or database) per dump device.
To dump two
databases simultaneously to tape, you need to have installed two tape drives.
These drives can
coexist on the same controller and in the case of DLT drives, the recommended limit of two such
drives per controller (discussed earlier) relates here as well.
The same applies to load operations.
Previous versions (4.21x) of SQL Server upon startup allocated only three internal buffers
dedicated to dump and load operations.
If all three buffers were consumed, as with three
simultaneous dump or load processes, attempting a fourth dump or load would yield little gain in
overall throughput.
SQL Server 6.x now provides the capability for up to 32 simultaneous dump
or load processes, each with its own backup buffer.
The
backup_threads
parameter must be set to
equal the maximum number of devices that will be used at one time, as in the case of striped
dumps.
A single database cannot be dumped concurrently to more than one device at a time.
Only after
the first dump command completes can another dump for the same database begin.
In order to effectively employ multiple concurrent dumps, sequential I/O must be preserved on
both the source and destination volumes so as not to introduce disk head contention.
66
See earlier section entitled
Establishing Maximum Throughput at the Server
.
67
When SQL Server 6.5 database dump tests were run on previous generation (EISA-based) Compaq servers
(i.e: the Proliant 4500), the maximum throughput achieved was about 25 GB/hr, due mainly to limitations
imposed by EISA bus bandwidth.
This throughput was about half of what is seen on the PCI-based Proliant
5000.
Future hardware from Compaq will feature technology enhancements that may push the current numbers
even higher.