March 25, 2008 at 11:03 pm
Hello All,
Can someone tell me how many iops i should expect with a 6+1 array, random writes?
thanks!
March 26, 2008 at 10:06 am
I wrote this post which shows the basic calcs involved. It needs a rewrite but am waiting to configure some arrays for actual testing.
http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/01/26/how-fast-is-a-disk.aspx
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 26, 2008 at 10:07 am
you didn't mention what raid you're using - if it's 5 or 6 then write performance is rubbish!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 26, 2008 at 12:58 pm
hi, sorry its a Raid5 6+1. with this configuration, we're seeing about 260 iops on random writes and about 650 random reads. the tool we use to benchmark this is sqlio.
what raid level would you recommend to get the best of both worlds, as far as writes and reads?
thanks
March 27, 2008 at 5:22 am
You have not given us nearly enough information to answer the question.
1) Raid level (given subsequent to OP).
2) Stripe size.
3) did you sector align the partitions?
4) ntfs allocation unit size
5) HBA queue depth?
6) HBA cache ratios?
7) most importantly the size of the reads/writes? 260IOPs could mean very low MB/sec transfer if using 4k blocks and a very nice number if 1MB blocks.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 29, 2008 at 10:40 am
raid 5 ( or 6 ) is only any good for a read only database, you should be capable of getting around 1000 io for reads but only around 250 for writes. The actual io value is based upon the spin speed of the disk and if it's random or sequential io. You get more io for sequential reads/writes. Essentially the block size does not affect io unless you saturate the bandwidth of the bus, with so few disks unlikely with sql server. If you want good write performance raid 10 is your only choice, for most databases raid 1 / 10 is the only raid you should use.
To know if you have a poorly performing disk array perfmon the disk io completion time, for an average array, reads should be around 5 ms and writes possibly + a few ms, certainly under 10. track that to disk idle time. On a fc array I'd expect to see reads at under 3ms and writes under 5ms , that's what I see on my prod boxes.
Adding more memory to your server will reduce disk bottlenecks.
NB. The basic math for disk io remains the same regardless of interface.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 29, 2008 at 10:47 am
These are typical values, in reality you'd probably be lucky to get 80% of these values.
so your raid 5 x 5 spindles would support ((5 * 174 ) * 80% ) /4 for raid 5 overhead
= 174.
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
The reality is that it is quite difficult to validate these figures in a test and even iometer or sqlsim isn't the same as your actual production sql server.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 31, 2008 at 4:26 pm
thanks! this is really helpful!
by the way, do you guys change the queue depth on emulex hba's? i read some posting that it does help, but just not sure.
April 3, 2008 at 3:23 pm
there's a link on my blog I posted today
Yes I have looked at HBA buffers, tricky one as usually you don't get given time to test, I had mine set to 64 on the last change.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply