January 2007 - Posts

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.

 

Disk

Maximum theoretical  Sequential io

Maximum theoretical random io

15k SCSI/SAS/FC

434

174

10K SCSI/SAS/FC

294

125

7.2k SATA

181

74

 

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 )

 

Disk

Seq io

 

Throughput @64k

random io

Throughput @8k

15k SCSI/SAS

347

21.7 Mbyte/sec

140

1 Mbyte/sec

10K SCSI/SAS

235

14.7 Mbyte/sec

100

0.78 Mbyte/sec

7.2k SATA

145

9 Myte/sec

59

0.46 Mbyte/sec

 

Ø       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 )

 

Disk Type

Random Write

Random Read

Sequential Write

Sequential Read

Io

Mbyte

Io

Mbyte

Io

Mbyte

Io

Mbyte

15k SCSI/SAS

140

1

560

4

347

21.7

1388

86.8

10K SCSI/SAS

100

0.78

400

3.12

235

14.7

940

58.8

7.2k SATA

59

0.46

236

1.84

145

9

580

36

 

 

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

 

Disk Type

Random Write

Random Read

Sequential Write

Sequential Read

Io

Mbyte

Io

Mbyte

Io

Mbyte

Io

Mbyte

15k SCSI/SAS

280

2

560

4

694

43.4

1388

86.8

10K SCSI/SAS

200

1.56

400

3.12

470

29.4

940

58.8

7.2k SATA

118

0.92

236

1.84

290

18

580

36

 

 

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

 

Disk Type

Random Write

Random Read

Sequential Write

Sequential Read

Io

Mbyte

Io

Mbyte

Io

Mbyte

Io

Mbyte

15k SCSI/SAS

420

3

840

8

1041

65.1

2082

130.2

10K SCSI/SAS

300

2.34

600

6.24

705

44.1

1410

88.2

7.2k SATA

177

1.38

472

3.68

335

27

670

54

 

 

 

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

 

Disk Type

Random Write

Random Read

Sequential Write

Sequential Read

Io

Mbyte

Io

Mbyte

Io

Mbyte

Io

Mbyte

15k SCSI/SAS

560

4

1320

8

1388

86.8

2776

173.6

10K SCSI/SAS

400

3.12

800

6.24

940

58.8

1880

117.6

7.2k SATA

236

1.84

472

3.68

580

36

1160

72

 

  • 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

 

 

( A quick note about raid 6 .. This is raid 5 with an additional parity write, this means the array can stand a double disk failure, read performance remains unaffected but write performance degrades further due to the additional parity write.

Raid 6 has a slightly better failure rate than raid 5 where there are large numbers of spindles in the array. )

 

Posted by GrumpyOldDBA with no comments
Filed under:

Not Thinking it through - the saga continues

The saga continues ………..

Here below are the previous posts which relate to the management of a production SQL Server ( by a third party ). We asked that the job which re-indexes the database be disabled until such time as the issue of the database mode was resolved  .. sadly the backup job was disabled instead so not only did we get left with no recovery point after the index rebuild but we didn’t get any backups either.

A quick phone call and the job was re-enabled, however we had imagined that a slip of the mouse in EM had caused the wrong job to be disabled – but no – the schedule had also been disabled so although the backup job has been re-enabled the schedule has not, so still no backups and three days on the situation is unchanged.

We also did a memory upgrade, I provided the process and scripts to enable the extra memory,  sadly the change to the memory script was not run so the o/s has had 13Gb of ram all to itself for the last week or so,  it’s almost a 7 x 24 app on a cluster so we can’t just take the system down when we want. When changing the amount of awe memory used a service restart is required for the change to take effect. I’d also previously had a discussion about using dynamic memory settings with awe ( you can’t – it doesn’t work that way ). SQL Server is now happily using the extra memory and I’m monitoring to see what impact it has had, we’ve gone from 11Gb to 22Gb, so technically the entire database should be able to be held in memory. Some initial stats indicate decreased reads and increased writes but it’s early days and sadly the cluster failed over around 12 hours after the memory change so I’m still waiting for things to stabilise.

 

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2006/11/30/not-thinking-it-through.aspx

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2006/11/30/how-much-memory.aspx

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2006/12/04/not-thinking-it-through-part-2.aspx

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2006/12/11/not-thinking-it-through-part-3.aspx

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2006/12/22/not-thinking-it-through-the-end-maybe.aspx