What's better for performance with, say, 16 drives?

  • some say RAID10 the lot
  • others suggest have a smaller array dedicated to logs and RAID10 the rest

I'm wondering if this is an opportunity for a project to raise the profile of the site - IIRC I've got 16+ 72GB 15K U320 drives and the other things we'd need to set up a test rig (except a spare chassis) - who knows we might even be able to get SE to chip in for anything else we need and expenses?

We'd need:

  • someone to build
  • someone to host
  • someone to install
  • someone to devise tests
  • someone to blog about the whole process
  • a windows server (or MSDN?) license so we've something to install SQL Server on top of

Any thoughts, fellow Database Simians?

  • 1
    Oh dear, what have I started. My intention was to ask Brent about the math behind the recommendation next time he popped in, not to imply that it was wrong. As for any testing, I think we'd find the problem with trying to objectively measure a set of configurations is the lack of good "general" workload, as there's no such thing. Dec 4, 2011 at 19:48
  • Sorry I've misrepresented you! As you suggest, I don't think we are talking 'right' and 'wrong' here, but there are some things we can objectively measure - like how the gain from adding drives tails off in a RAID10 array, using standard scattered/sequential io tests and how those figures compare to a 2 or 4 drive array that might be dedicated to logs - I'm a firm believer in one big RAID10 but I'd love to understand some of the nuances better. Also it's supposed to be fun :-) Dec 4, 2011 at 21:21

2 Answers 2


Well, I've got some stuff that might help. I live in SL5 and have the following items:

  • Several HP XW9300 workstations with 16GB of RAM and 2x Opteron 280 CPUs. Some of them have 6x 10K or 15K drives and Adaptec ASR-2200s controllers.

  • A Tyan S2916 motherboard and 2x opteron 2376s (i.e. chips with H/W virtualisation support)

  • 1x HP MSA30 with 4x 72GB 10k drives

  • 1x PC Pitstop 10-way desktop array with 10x Fujitsu MBA3147NC drives (146GB 15k)

  • 2x Xyratex RS1600FC arrays with 16x72G 10k drives (ST373307FCV with 'V' firmware) in storage

  • 1x Netapp DS14 Mk2 F/C array with 14 x 72GB 10k drives (netapp firmware but formatted with 512 byte sectors)

  • A few QLA2302 and 2342 dual channel 2Gb F/C HBAs.

  • At least two seats worth of valid licencing for SQL Server dev edition. 1 current MSDN and 1 boxed SQL Server 2005.

  • 1x COA for Windows Server 2008R2 std ed, 1x MSDN licence and one COA for Windows 2003 server R1 std edition.

  • COAs on some the HPs for XP 32 or 64 bit.

  • 1x retail Windows 7 ultimate box

  • A few ETL processes that could be fitted with sanitised data sets and used as benchmarks.

  • 1x 3COM baseline 2828 gigabit ethernet switch

  • Considerable experinece in buying secondhand kit off ebay ;)

Does that help?

If you're feeling really keen, the TPC benchmark specs are available from the TPC web site.

An independent comparison with some quantitative results

In principle, I'm all in favour of a test lab capable of producing some quantitative comparisons. Bonus points if somebody can get hold of a used SAN with sufficient documentation to experiment with configurations and room to set it up. Something roughly the spec of (say) an EMC CX300 - CX500 with two shelves of 10k disks (and a working install of Navisphere ;-) Even a loan for a month or two would help.

In particular, I'd be up for a project to produce apples to apples comparisons with the following:

  • SAN vs. direct attach storage of equivalent physical spec (i.e. number of spindles)

  • VM vs. bare metal server

  • RAID-5/50, RAID-6/60 vs RAID 10 on an actual ETL or OLTP benchmark

  • Separate logs on an OLTP or ETL benchmark, particularly on a SAN.

  • You mean you don't live in Tunbridge Wells? You have quite a graveyard! Does your 14 drive array connect via FC or SCSI (some arrays that take FC drives connect via SCSI, don't they?) and have you got the HBA for your Tyan MB? Dec 4, 2011 at 6:55
  • @Jack Douglas - I used to live near Tunbridge Wells; now I live in Sunningdale. All of the F/C stuff (the Xyratex and Netapp arrays) is F/C-F/C. In practice that makes it next to useless on Windows as SW RAID on Windows is poo; I got it for Linux/Oracle but found myself mostly working on SQL Server. Dec 4, 2011 at 15:34
  • @Jack Douglas - The HPs all have U320 drives in them and I've got Adaptec ASR-2200s RAID controllers in most of them. The MSA70 and desktop 10-drive array are both U320. The Tyan MB is actually from an XW9400; I was going to retrofit it but never got around to it. It has a couple of PCI-X/100 slots that would take the Qlogic HBAs or the Adaptec RAID controllers, plus some PCI-e x4 or x8 slots. All of my SCSI and F/C stuff is PCI-X, and the XW9300s have PCI-X slots only. Dec 4, 2011 at 15:39
  • I've had significant trouble with underperforming SAN based infrastructure in the past, so I'd really like to write a white paper and back it up with some hard data. I have some informal benchmarking I did with an ETL process that showed the HPs running the same ETL job a lot faster than a SAN/Blade server they wanted to deploy to. Dec 4, 2011 at 15:41

"As for any testing, I think we'd find the problem with trying to objectively measure a set of configurations is the lack of good "general" workload, as there's no such thing."

In the past I have invested quite a bit of time trying to find RAID calculators and perform accurate benchmarks -- but I have to agree with Mark on this one, in the end the experiments generally just end up telling you how well the particular experiment performs on your particular hardware.

Some of the complexities:

Workload characteristics:

  • Read / Write percentages
  • Sequential vs Random patterns
  • Block sizes of data written

Hardware characteristics:

  • The effect of caching
  • Controller bottlenecks
  • The specifics of how the controller implements the RAID
  • Total number of drives

Logical characteristics:

  • Filesystem block sizes, or in the case of Linux or SANs which filesystem

And these are just what I can think of off the top of my head.

Other Options:
Perhaps more useful would be a series of blog posts about how to properly profile and simulate a real world workload. For that you just need to find a DBA with a company or client that will let them be open about this particular project.

You also might scale back what you are doing and do something like try to figure out how well RAID 10 scales as you increase the size of the array. That might be intersting even if you are just doing a small set of workloads.

In the end I don't think you can draw a conclusion about what is the best choice. Putting logs on their own array is also a safety concern. Also, in general logs are sequential so this helps simplify troubleshooting IO issues. Both of these often will outweigh performance implications for many people.

  • 1
    +1, especially for "You also might scale back what you are doing and do something like try to figure out how well RAID 10 scales as you increase the size of the array. That might be intersting even if you are just doing a small set of workloads." - that's precisely what I'm suggesting we do first. Dec 6, 2011 at 16:51
  • Can we do some tests with the SE data dump? That gives us a sizeable bit of data, it's well known, and people have already devised queries and the like for it.
    – jcolebrand Mod
    Dec 8, 2011 at 15:39
  • 1
    @Kyle Brandt - A particular category of tests I'd like to see is to compare the performance of different HW configs with about the same on-paper spec, such as SAN vs. DAS with roughly the same number of disks on an ETL benchmark. Some informal testing I did a while ago showed a factor of 2 difference in performance of an ETL job. Dec 19, 2011 at 15:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .