May 9, 2009 at 6:39 am
Hi All
I've been given a RAID5 only SAN to base a mixed sql2005 and 2008 cluster on.
I read somewhere that timed filecopies were a reasonably accurate measure of SQL disk perormance.
So I ran 10 simultaneous timed copies of a 20GB file (to saturate the 8GB SAN cache) - between LUNs (so as to get contention) and got about 165 megabytes a second.
Which I think is pretty poor for a moder array. The HBAs are 4Gbps and the blades are dual quad cores so I think I/O will be the bottleneck.
Can anyone point me to any info on how to calculate how many databases this is likely to support or at least simple further tests to run?
May 9, 2009 at 7:09 am
There are really too many variables at play here to give a definitive answer there. However, have you checked your disc partition alignment on the RAID array? If the default is used, each page is spread over two units on the disc, effectively doubling your disc io.
This is a nice link covering the issue
(I'm too lazy to write it out, and too honest to take credit for it 🙂 )
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 9, 2009 at 5:47 pm
Thanks Andrew
Yes I did the 64k alignment using an EMC doc as a guide. I also formatted the ntfs partitions with 64 k cluster size.
I'm expecting to have to do some reading and calculations. Like most things in IT I want to use the best practices to get 80% of the way there quickly then do the testing and tuning. I'm looking for answers to questions like:
- Does SQL disk I/O scale in a linear fashion. i.e. can I use 10 20GB DBs for testing and assuming the I/O will be 1/5 of 10 x 100 GB databases?
How have other people approached storage for a consolidated SQL environment?
May 10, 2009 at 1:15 pm
RAID5 is not usually a good choice for SQL Server, especially for the Log files (LDF's).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 11, 2009 at 5:30 am
'storage guy' was wrong. It does do raid 10 - sheesh!
May 12, 2009 at 7:31 am
1) the OP's question is moot without a critical piece of information: what is the IO demand of each database. Also you need to know the IO parameters (90% 8k random writes or 90% 64k sequential reads, etc). Your SAN as configured may not support even ONE database that has 10000 concurrent users and is 2 TB in size. But it could support 7000 databases that are 50 MB each and have only an occassional user doing a few index seeks per day.
2) There are MANY 'things' between the CPUs and the physical media when a SAN is involved, and you can often configure each 'thing' suboptimally. I am happy to see you did sector alignment and 64k cluster sizes. What about HBA queue depty? HBA and SAN cache allocations? How many disks in the raid group?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply