Bus Bandwidth
Once we determine the number of disks required, we need to ensure the I/O bus is capable of handling the I/O throughput. With today’s disks capable of sustained sequential throughput of greater than 100MB/sec, it’s easy to overwhelm an I/O channel.
Disk throughput varies depending on the load and type of I/O. As we covered in the previous section, OLTP applications consist of random I/O with a moderate percentage of disk time seeking data, with disk latency (time between disk request and response) an important attribute. In contrast, OLAP applications spend a much higher percentage of time performing sequential I/O, thus the throughput is greater and therefore bandwidth requirements are higher.
There are a variety of bus types matching the different disk types. For example, in a direct attached SCSI disk enclosure, the typical bus used today is Ultra320, with a maximum of 320 MB/sec per channel. In contrast, today’s 2 Gigabit fiber channel offers approximately 200 MB/sec throughput.
In our above example of 2000 disk transfers/sec (1200 read and 400 write), assuming these were for an OLTP application with Random I/O and 8K I/O transfers (which is the SQL Server transfer size for Random I/O), the bandwidth requirements can be calculated as 2000 * 8K, which is a total of 16MB/sec, well within the capabilities of either Ultra320 SCSI or 2 Gigabit Fiber Channel.
Should the bandwidth requirements exceed the maximum throughput, additional disk controllers and/or channels will be required to support the load.
A Note on Capacity
You’ll note that we haven’t addressed storage capacity requirements yet. This is a deliberate decision to ensure the storage system is designed for throughput and performance as the highest priority.
A common mistake made when designing storage for SQL Server databases is to base the design on capacity requirements alone. A guiding principle in designing high performance storage solutions for SQL Server is to stripe data across a large number of dedicated disks and multiple controllers. The resultant performance is much greater than what would be achieved with fewer, higher capacity disks. Storage solutions designed in this manner usually exceed the capacity requirements as a consequence of the performance centric approach.
In our above example where we calculated the need for 16 disks, assuming we use 73GB 15,000 RPM SCSI disks on an Ultra320 bus, we have a total available capacity of 1.1TB. Usable space, after RAID 10 is implemented, would come down to around 500GB. Higher capacity can obviously be achieved with higher capacity disks, or more disks of the same size. Given the choice, go with more disks of a lower capacity, particularly for systems with a high percentage of read activity.
If the projected capacity requirements for our database only total 50GB, then so be it. We end up with 10% storage utilization as a consequence of a performance centric design.
In contrast, a design that was capacity centric would probably choose a single 73GB disk, or two disks to provide redundancy. What are the consequences of this for our example? Assuming 125 IOPS per disk, we would experience extreme disk bottlenecks with massive disk queues handling close to 2000 required IOPS!
Whilst low utilization levels will probably be frowned upon, this is the price of performance, and a much better outcome than constantly dealing with disk bottlenecks. A quick look at any of the server specifications used in setting performance records for the Transaction Processing Council (tpc.org) tests will confirm a low utilization, high disk stripe approach such as that presented above.
Finally, placing capacity as a secondary priority behind performance does not mean we can ignore it. Sufficient work should be carried out to estimate both the initial and future storage requirements. Running out of disk space at 3am is not something I recommend!
This article is an excerpt from Rod Colledge’s forthcoming book SQL Server 2008 Administration, published by Manning Publications. All rights reserved.
]]>