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

Using RAID-0 could

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 35 highlights

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 35 Previously, for single-device dumps all reads from the disk array occurred sequentially by one thread. Now as we add dump devices SQL Server allocates additional threads from the backup threads pool53, assigning one thread per dump device. The threads process data in round-robin order, with each thread reading one extent54 at a time, skipping unallocated extents. The read pattern from the array as a whole remains essentially sequential in nature. Also for single device-dumps, the overall throughput we could hope to achieve was limited by the speed of the storage device. When doing a dump operation to multiple, striped devices however, the throughput bottleneck may be shifted from the destination end (i.e: the storage devices), to the source end (the data drives), depending upon the number and speed of the backup devices used. Furthermore, the read capability of SQL Server is increased because there are now multiple threads reading from the database files, with each thread allocated its own in-memory buffer. We can attempt to compensate for this increased demand for asynchronous reads from the disk subsystem by adding a second controller but another controller will increase read capability only if the two controllers are combined using a software array such as a Windows NT stripe set (please reference the earlier section entitled Database Layout Considerations). For this section, the following additions were made to the server configuration: An additional Smart-2 controller was added to the primary PCI bus in our system. Each of the two SCSI channels (or 'SCSI-ports') on both of the Smart-2 controllers was supplied with four Fast-Wide SCSI-2 disk drives. Four drives were found to be sufficient to saturate a single SCSI channel when performing large size sequential I/O such as that performed in our testing. For tests in which the entire database resided on one Smart-2 controller, a single logical volume (array) using RAID-5 fault tolerance was created to hold the database, so that the data and parity is striped across the drives on both SCSI-ports using a 16KB striping factor. Using RAID-0 could have yielded results up to 5% slower for this configuration, because the 128KB striping factor used with RAID-0 corresponds less optimally to the 16KB sequential reads requested by SQL Server during database dumps. Using RAID-1 on the data volume could require twice the number of drives to achieve similar results. For tests in which the database was laid out across two Smart-2 controllers, 2 logical volumes using RAID-5 were created on each of the controllers (1 per SCSI-port). A stripe set using Windows NT Disk Administrator was then implemented to combine all 4 logical volumes.55 In this section we will attempt to determine the maximum read throughput achievable during a database dump on a Proliant 5000 system. In large, this involves increasing the read capacity of the disk subsystem by extending the database array across multiple SCSI channels and controllers. Lack of any throughput bottleneck at the receiving end was guaranteed by dumping to 'null' devices56 (which act as 'bit buckets' for the data) so that the system is only reading (not writing) data. After having thus determined the throughput at the source end, we can then proceed to find the "optimal throughput" at the receiving end in the sections that follow. 53 The number of threads reserved for dump and load operations is determined by the backup_threads parameter, and should be set equal to the maximum number of dump devices being used simultaneously. 54 An extent is a SQL Server storage structure consisting of eight 2KB data pages. 55 For this configuration the striping factor for both Smart-2 controllers was changed to 128KB, which works better with the 64KB stripe size used by Windows NT striping. The default striping factor chosen by the Smart2 for RAID-5 arrays is 16KB, but this setting resulted in up to 25% lower performance for our tests. 56 The 'diskdump' device in SQL Server is a 'null' device. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797

  • 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
35
1997 Compaq Computer Corporation, All Rights Reserved
Doc No 444A/0797
Previously, for single-device dumps all reads from the disk array occurred sequentially by one thread.
Now as we add dump devices SQL Server allocates additional threads from the backup threads pool
53
,
assigning one thread per dump device.
The threads process data in round-robin order, with each thread
reading one extent
54
at a time, skipping unallocated extents.
The read pattern from the array as a whole
remains essentially sequential in nature.
Also for single device-dumps, the overall throughput we could hope to achieve was limited by the
speed of the storage device.
When doing a dump operation to multiple, striped devices however, the
throughput bottleneck may be shifted from the destination end (i.e: the storage devices), to the source
end (the data drives), depending upon the number and speed of the backup devices used.
Furthermore,
the read capability of SQL Server is increased because there are now multiple threads reading from the
database files, with each thread allocated its own in-memory buffer.
We can attempt to compensate for
this increased demand for asynchronous reads from the disk subsystem by adding a second controller -
but another controller will increase read capability only if the two controllers are combined using a
software array such as a Windows NT stripe set (please reference the earlier section entitled
Database
Layout Considerations
).
For this section, the following additions were made to the server configuration:
An additional Smart-2 controller was added to the primary PCI bus in our system.
Each of the two SCSI channels (or ‘SCSI-ports’) on both of the Smart-2 controllers was supplied
with four Fast-Wide SCSI-2 disk drives.
Four drives were found to be sufficient to saturate a
single SCSI channel when performing large size sequential I/O such as that performed in our
testing.
For tests in which the entire database resided on one Smart-2 controller, a single logical volume
(array) using RAID-5 fault tolerance was created to hold the database, so that the data and parity is
striped across the drives on both SCSI-ports using a 16KB striping factor.
Using RAID-0 could
have yielded results up to 5% slower for this configuration, because the 128KB striping factor used
with RAID-0 corresponds less optimally to the 16KB sequential reads requested by SQL Server
during database dumps.
Using RAID-1 on the data volume could require twice the number of
drives to achieve similar results.
For tests in which the database was laid out across two Smart-2 controllers, 2 logical volumes
using RAID-5 were created on each of the controllers (1 per SCSI-port).
A stripe set using
Windows NT Disk Administrator was then implemented to combine all 4 logical volumes.
55
In this section we will attempt to determine the maximum read throughput achievable during a
database dump on a Proliant 5000 system.
In large, this involves increasing the read capacity of the
disk subsystem by extending the database array across multiple SCSI channels and controllers.
Lack of
any throughput bottleneck at the receiving end was guaranteed by dumping to ‘null’ devices
56
(which
act as ‘bit buckets’ for the data) so that the system is only reading (not writing) data.
After having thus
determined the throughput at the source end, we can then proceed to find the “optimal throughput” at
the receiving end in the sections that follow.
53
The number of threads reserved for dump and load operations is determined by the
backup_threads
parameter, and should be set equal to the maximum number of dump devices being used simultaneously.
54
An extent is a SQL Server storage structure consisting of eight 2KB data pages.
55
For this configuration the striping factor for both Smart-2 controllers was changed to 128KB, which works
better with the 64KB stripe size used by Windows NT striping.
The default striping factor chosen by the Smart-
2 for RAID-5 arrays is 16KB, but this setting resulted in up to 25% lower performance for our tests.
56
The ‘diskdump’ device in SQL Server is a ‘null’ device.