Grumpy Old DBA

How Fast is a disk ?

 I've been providing some information on storage, so I thought I'd post it!

• Published performance figures for disks, disk controllers , storage arrays, SAN’s, NAS and DAS are usually based around optimal conditions which rarely match real world.

• For example the maximum throughput for a disk is usually quoted in bits whilst most users relate to bytes.
• The maximum throughput for an individual disk is usually calculated for sequential io ( where reads and writes are contiguous ) for databases the majority of io is random.
• When calculating the throughput of a disk the spin speed is the most influential factor, the faster the spindle speed, the greater the throughput.

In reality 80% of this figure is the achievable figure ( Ref: SQL Server 2000 Performance Tuning Technical Reference :: Microsoft Press )

Calculations from published figures for Seagate Cheetah and Barracuda disks

( average value of read and write )

Throughput for SQL Server ( Typical )

Ø       SQL Server random io are normally 8k, sequential generally 64k

Turning this to raid arrays

• Creating a 4 spindle raid 5 array would support the following io capacities/sec
• ( available capacity = 3 x disk size )

• Creating a 4 spindle raid 10 array would support the following io capacities/sec
• ( available capacity = 2 x disk size, match for spindle count )

• Creating a 6 spindle raid 10 array would support the following io capacities/sec
• ( available capacity = 3 x disk size, match for capacity )

• Creating a 8 spindle raid 10 array would support the following io capacities/sec
• ( available capacity = 4 x disk size, match for read capacity )

• Notes: Most scsi raid controllers enable split reads on raid 1/10
• ( I’ve assumed this is the same for SATA – but this may not be the case )

• A 4 disk raid 5 array presents 4 spindles for read, thus a comparison must be shown for a raid 10 where 4 spindles are presented for read.

Notes:

• The io rates are taken from the average of the average seek times and track to track times.
• Disks actually support slightly more reads than writes, but for the purpose of this comparison assume read and write io are the same.
• For the 15k disk the absolute figures would be 166 write / 181 read

• One way to improve performance further is to short format disks, e.g. format a 300Gb disk to 150Gb. This technique should halve seek times, disks are sometimes formatted this way with the outer partitions being allocated for fast performing usage and the inside to slower operations. However, a disk head can only be one place at a time and this type of horizontal partitioning can cause inconsistent performance.

• Cache on disks should be disabled in raid arrays for databases.
• Controller cache should ideally be all allocated to write, read cache is counterproductive for a RDBMS which attempts to cache its main data and handle read ahead.
• Write cache attempts to turn random writes to sequential writes and/or facilitate elevator sorts – these increase write performance.

• Note that the “large” amounts of cache memory on a SAN rarely improves performance and a cache once saturated cannot improve disk performance.

o         64Gb of cache divided read/write across 32 LUNs is actually not much memory at all