Faster than an MD3000?

[Copie]

Hi Folks,
i'm looking for some advice.

We have a few servers that are utilising Dell MD3000 RAID arrays (with 2x MD1000 expansion for a total of 45 drives) for use with SQL server databases. We do a lot of simultainous reading and writing so use 15K 146Gb SAS drives.

We are finding that even with the array setup in a RAID10 with 30 drives for SQL data (the maximum the MD3000 can cope with in a single virtual disk) we are not getting enough performance (we use the other 15 drives for hot spares and SQL logs / temp DB)

A simple HD Tach gives about 200MB/s average read speed and i've not been able to test the write speed as I dont have a registered version but assume it's less than this.

My question is, keeping within a sensible budget of less than 6 figures and ideally not much more than the 45 drive MD array what are my best options for increasing speed?

We are currently using:
Dell 2950's with 32Gb of RAM, Windows 2008, SQL 2008, SAS5/e, MD3000, MD1000

We are not stuck with Dell but would prefer something from their range as we have a good relationship (and good discount) with them.

Thanks for any thoughts

Copie

 

[geiger]

1. Were the HDTach results taken with the DB down?
2. Work with your DBA's(if you have them), break the 30 disk raid 10 into something like 5 raid 10's and distribute the datafiles across them.
3. Talk with your Dell rep about something like a CX4-40.

 

[Copie]

Hi Geiger, thanks for your swift reply!

In answer to your questions:

1) Yes, would you consider 200MB/s fast or slow for this number of spindles?
2) Would 10x 4 spindle RAID10's be faster than a single 30 drive RAID10 array?
3) I like the CX4 but they are exceptionally expensive compared to the MD range, is the AX4 range better than the MD range?

 

[geiger]

1. It's slow, but you are likely maxing the controller.
2. Possibly, it really depends on how the database is structured and if you can get the db to stripe the data across the volumes.
3. The AX4 with a FC backend might do the trick. I would stay away from iSCSI for something like this.

Also, I have to ask, how much tuning has been done at the database side with indexes and such?

 

[AreEss]

One, HDTach is garbage, quit wasting time. It's less than worthless for real hardware.

Two, it's a Dell MD3000. What the hell do you expect? It's an absolute bottom end system. You're most likely at controller limit.

Three, dollars to donuts it's configured completely wrong for the application. Segment size doesn't match block size, cache watermarks set wrong, etcetera. Given the limited configuration information you've given, I have absolutely no problem stating that beyond any question of doubt the arrays are configured completely wrong.

Dell is NOT a reasonable storage vendor, if your primary concern is performance. Under six digits? If you're hellbent on RAID10, dream on. This is a job for properly tuned RAID5 and FC. Nothing else besides SAS and FC can exceed 200MB/s sustained, period. And there are no direct-attach SAS that can do the job.
Short of taking 3-5 days of downtime for a total rebuild, the only way out of this particular mess is building a whole new setup and migrating.

 

[ochadd]

200MB/s is slow. I'm getting that on a four drive RAID 10 of 10k rpm SATA drives. I'm guessing you tested with the array already under load otherwise I'd suspect something wrong/broken.

How much data are you dealing with? SSDs might be a solution. Even the MLC variety is replacing 4-5 SAS drives each in pure IO which is typically what SQL thrives on. Probably won't replace the full 2TB of data your 30 drive set can hold but breaking the data up and putting the heavy use tables on a 4-6 drive SSD RAID 10 set can payoff big time.

I'd do some checking with IOmeter and MS SQL benchmarking tools to see where you sit trying to replicate your specific load.

 

[Copie]

Thanks for your replies

1. It's slow, but you are likely maxing the controller.
2. Possibly, it really depends on how the database is structured and if you can get the db to stripe the data across the volumes.
3. The AX4 with a FC backend might do the trick. I would stay away from iSCSI for something like this.

Also, I have to ask, how much tuning has been done at the database side with indexes and such?

Would increasing the number of controllers (i.e. using two MD3000's instead of 1) help then? Although this isn't a supported configuration.

Zero DB tuning has been done, at the moment i'm at the point where DBA / developer time is more expensive than hardware. When we start to get closer to the 6 figure point the tables might turn, but at the moment I can give more performance gains per dollar than developers can.

One, HDTach is garbage, quit wasting time. It's less than worthless for real hardware.

Two, it's a Dell MD3000. What the hell do you expect? It's an absolute bottom end system. You're most likely at controller limit.

Three, dollars to donuts it's configured completely wrong for the application. Segment size doesn't match block size, cache watermarks set wrong, etcetera. Given the limited configuration information you've given, I have absolutely no problem stating that beyond any question of doubt the arrays are configured completely wrong.

Dell is NOT a reasonable storage vendor, if your primary concern is performance. Under six digits? If you're hellbent on RAID10, dream on. This is a job for properly tuned RAID5 and FC. Nothing else besides SAS and FC can exceed 200MB/s sustained, period. And there are no direct-attach SAS that can do the job.
Short of taking 3-5 days of downtime for a total rebuild, the only way out of this particular mess is building a whole new setup and migrating.

I personally find HD Tach is useful for an "at a glance" rough indicator of performance.

I agree it's bottom end hardware, but it's cheap which is good. I'm asking how to increase performance in a similar price bracket. It's been mentioned before that the controller is the weak link.

You are correct that it's configured completly wrong, it's "out of the box" as it stands and no tuning has been done at all. Setting these configurable items was the sort of thing i was looking for when googling when I came across this forum discussing similar topics so I thought I would join to ask the question.

As I mentioned we are not stuck with Dell but as we buy most of our kit from them we get good discounts. If we opened negotiations with another vendor we would likely get list price and we are trying to keep costs down as much as possible.

Regarding RAID5 vs RAID10, as I mentioned we do a lot of writes which is why we chose RAID10 over RAID5, happy to hear arguments for RAID5 though as it means we need less spindles!

We are using SAS 15K drives already. How would FC drives increase our speed? I was under the impression the actual physical drives were no faster than SAS?

You mention that no DAS SAS can do over 200MB/sec? I'm dismayed to hear that.

We already have about 3 of these setups in production and are looking at more, so a rebuild isn't a problem as we haven't purchased for our next project yet. However each project is seperatly financed so it's not as easy as saying "3 lots of MD3000's could have brought you a CX4" unfortunatly.

200MB/s is slow. I'm getting that on a four drive RAID 10 of 10k rpm SATA drives. I'm guessing you tested with the array already under load otherwise I'd suspect something wrong/broken.

How much data are you dealing with? SSDs might be a solution. Even the MLC variety is replacing 4-5 SAS drives each in pure IO which is typically what SQL thrives on. Probably won't replace the full 2TB of data your 30 drive set can hold but breaking the data up and putting the heavy use tables on a 4-6 drive SSD RAID 10 set can payoff big time.

I'd do some checking with IOmeter and MS SQL benchmarking tools to see where you sit trying to replicate your specific load.

I'm getting conflicting information here, AreEss says no DAS can do 200MB/s yet you mention 200MB/s is slow :/ i'm not sure which is right.

The array was not under load although SQL was running in the back ground with no queries running.

Data sizes our SQL Data partition is 2Tb but we will likely only use 1Tb in total. I did look a the SSD options but, from Dell at least, they are only available in the CX4 SAN's which have a very high initial cost (close to the 6 figure sum)

Good advice with IOMeter, i'll investiagte further.

Thanks

Copie

 

[Ockie]

200MB/s is very slow for that type of setup. I would try to look at controller issues or perhaps look at cascading your controllers.

 

[geiger]

Do you have access to the databases? SQL comes with some basic optimization tools. Run those and see what it reports. I'd imagine there is not a maintenance plan in place to refresh indexes and such.

If the database is not optimized to use indexes over full table scans your performance will get pummeled through full scans. What is the RAM utilization on these servers?

 

[shabazkilla]

I'd work on optimizing your current setup first.

POWERVAULT MD3000 AND MD3000i ARRAY TUNING BEST PRACTICES

 

[geiger]

What SAS controller is in the servers?

 

[AreEss]

DA SAS with that many drives is going to do at best in 90% of the hardware, 250MB/s or less. There are exceptions, but those are also NOT redundant systems. The LSI 8480 can easily break 500MB/s using SAS drives - but it's a host RAID controller with no redundancy beyond hotspares, and presuming quad link. Redundancy costs CPU in SAS - a lot of CPU.

Here's some theoretical throughput charts for you:
http://www.wwpi.com/index.php?option=com_content&task=view&id=983&Itemid=0

A quad SAS link can in theory, do 850MB/sec. Which is why the chart is purely fantasy in no small part. To do 850MB/s you need a certain number of spindles, which increases your IO count, which decreases your potential bandwidth, lather rinse repeat. There's virtually no DA SAS gear out there; the MD3000 may be bottom of the barrel in storage, but it's top dog in SAS attach. (The DS3200 compares well in base configuration, but scales extremely poorly past the second shelf.) Also remember that each individual logical drive, is almost certainly using only one SAS link, not four.

For DA SAS, 200MB/sec absolutely is slower than crap; see LSI 8480 numbers above. That doesn't disagree with what I said; that no comparable DA SAS in the configuration you're talking about is going to do better than that. If you got 48 drives onto an LSI 8480, I would expect it right around the same numbers if not lower. (Especially since you'd have to do a lot of muxing.)
FC in this situation, is not for speed in the way you think. Newer FC arrays do not use an FC backend; they haven't for some time. HDS AMS2000 series is an FC front-end with a multi-star SAS backend, and those things are unholy screamers. (Active-Active controllers help, too.) FC for speed here is on the host side - you have a limited number of I/Os you can put down any given path on any given controller at any given point. Databases have extremely high I/O counts. By shifting to FC on the host side, presuming 2x dual port HBAs and an Active-Passive multipath (2 active, 2 passive path) you increase your theoretical IOPS throughput to >400,000. Also, FC is just a faster attach method; 4Gbit is 512MB/s per port for 1024MB/s per dual port HBA, 8Gbit is double that - or, exceeding PCIe 8x link limit. The combination of these two things make FC a much better connectivity option for databases than SAS.

The STUPID argument against RAID5 is "wah wah it's slower in writes!" (I call it this, because I hear it all the time, from people who can't even tell me why that is.) The fun fact is that given a configuration that does not exceed 70% array CPU load peak, write speeds on correctly configured RAID5 versus RAID1 "reverse" outside of cache. In other words, RAID5 writes faster, because it has more spindles to write to. Writing outside of cache requires you wait for the spindle to ack a write as complete; RAID1 see-saws between two, which causes drive cache holdups. RAID5 walks down a line, so drive cache flush becomes much less of an issue.
But again; that's in a situation where you have the arrays configured correctly. Full stripe writing, etcetera. The point there though, is that in said properly configured array, inside of caching operations, RAID5 and RAID1 write performance is quite literally identical. If you're staying in cache, the cache is your limiting factor more than your CPU typically. Full stripe writes reduce parity calculations to the point where the process does become cache-bound. On any array, past a certain point, RAID5 becomes expensive enough in CPU that it becomes slower. But in configurations similar to yours, if we presume 2GB cache in a 60/40 read/write and a 5s flush cycle, you'll typically have your misses on reads. And nobody argues that RAID5 is slower in read.

To be entirely honest, to give you legitimate numbers on the MD3000, I would have to have access to your setup. Just going by rule of thumb here, I think I could eke out somewhere north of 300MB/s - but not much north, so don't get your hopes up there. Best case, I'd estimate somewhere in the neighborhood of 320-340MB/sec. That's properly configured arrays, tuned for absolute maximum database performance.
You're running into far, far too many hard limits on the MD3000's to be seriously considered for this application, regardless. Even going to 300GB 15k's to get around the virtual drive limits (there's really no effective performance difference between 300GB and 146GB) and reduce your drive count, you're not going to get much better than that.

 

[Copie]

I'd work on optimizing your current setup first.

POWERVAULT MD3000 AND MD3000i ARRAY TUNING BEST PRACTICES

Thank you for the link, i'll read through that doc it looks interesting

What SAS controller is in the servers?

We are using 2x SAS 5/e cards in our servers

DA SAS with that many drives is going to do at best in 90% of the hardware, 250MB/s or less. There are exceptions, but those are also NOT redundant systems. The LSI 8480 can easily break 500MB/s using SAS drives - but it's a host RAID controller with no redundancy beyond hotspares, and presuming quad link. Redundancy costs CPU in SAS - a lot of CPU.

Here's some theoretical throughput charts for you:
http://www.wwpi.com/index.php?option=com_content&task=view&id=983&Itemid=0

A quad SAS link can in theory, do 850MB/sec. Which is why the chart is purely fantasy in no small part. To do 850MB/s you need a certain number of spindles, which increases your IO count, which decreases your potential bandwidth, lather rinse repeat. There's virtually no DA SAS gear out there; the MD3000 may be bottom of the barrel in storage, but it's top dog in SAS attach. (The DS3200 compares well in base configuration, but scales extremely poorly past the second shelf.) Also remember that each individual logical drive, is almost certainly using only one SAS link, not four.

For DA SAS, 200MB/sec absolutely is slower than crap; see LSI 8480 numbers above. That doesn't disagree with what I said; that no comparable DA SAS in the configuration you're talking about is going to do better than that. If you got 48 drives onto an LSI 8480, I would expect it right around the same numbers if not lower. (Especially since you'd have to do a lot of muxing.)
FC in this situation, is not for speed in the way you think. Newer FC arrays do not use an FC backend; they haven't for some time. HDS AMS2000 series is an FC front-end with a multi-star SAS backend, and those things are unholy screamers. (Active-Active controllers help, too.) FC for speed here is on the host side - you have a limited number of I/Os you can put down any given path on any given controller at any given point. Databases have extremely high I/O counts. By shifting to FC on the host side, presuming 2x dual port HBAs and an Active-Passive multipath (2 active, 2 passive path) you increase your theoretical IOPS throughput to >400,000. Also, FC is just a faster attach method; 4Gbit is 512MB/s per port for 1024MB/s per dual port HBA, 8Gbit is double that - or, exceeding PCIe 8x link limit. The combination of these two things make FC a much better connectivity option for databases than SAS.

The STUPID argument against RAID5 is "wah wah it's slower in writes!" (I call it this, because I hear it all the time, from people who can't even tell me why that is.) The fun fact is that given a configuration that does not exceed 70% array CPU load peak, write speeds on correctly configured RAID5 versus RAID1 "reverse" outside of cache. In other words, RAID5 writes faster, because it has more spindles to write to. Writing outside of cache requires you wait for the spindle to ack a write as complete; RAID1 see-saws between two, which causes drive cache holdups. RAID5 walks down a line, so drive cache flush becomes much less of an issue.
But again; that's in a situation where you have the arrays configured correctly. Full stripe writing, etcetera. The point there though, is that in said properly configured array, inside of caching operations, RAID5 and RAID1 write performance is quite literally identical. If you're staying in cache, the cache is your limiting factor more than your CPU typically. Full stripe writes reduce parity calculations to the point where the process does become cache-bound. On any array, past a certain point, RAID5 becomes expensive enough in CPU that it becomes slower. But in configurations similar to yours, if we presume 2GB cache in a 60/40 read/write and a 5s flush cycle, you'll typically have your misses on reads. And nobody argues that RAID5 is slower in read.

To be entirely honest, to give you legitimate numbers on the MD3000, I would have to have access to your setup. Just going by rule of thumb here, I think I could eke out somewhere north of 300MB/s - but not much north, so don't get your hopes up there. Best case, I'd estimate somewhere in the neighborhood of 320-340MB/sec. That's properly configured arrays, tuned for absolute maximum database performance.
You're running into far, far too many hard limits on the MD3000's to be seriously considered for this application, regardless. Even going to 300GB 15k's to get around the virtual drive limits (there's really no effective performance difference between 300GB and 146GB) and reduce your drive count, you're not going to get much better than that.

Thanks for your detailed reply(s), I wont pretend I understand everything as i'm not a storage expert but it looks like even with "out of the box" performance I'm already close to the edge and even with expert help could only increase speed by 30% or so with the current MD hardware.

This is the conclusion I had come to any why I joined this forum to ask the question "Faster than an MD3000?", as in what hardware do we need for the next step increase in performance? Do we need to look at the CX4 FC range to go beyond the 30% increase in performance (not that i'm saying 30% is a small figure but ideally i'm looking at 100%+).

Thanks for your help I appreciate you taking the time to reply

Copie

 

[Ockie]

Thank you for the link, i'll read through that doc it looks interesting



We are using 2x SAS 5/e cards in our servers



Thanks for your detailed reply(s), I wont pretend I understand everything as i'm not a storage expert but it looks like even with "out of the box" performance I'm already close to the edge and even with expert help could only increase speed by 30% or so with the current MD hardware.

This is the conclusion I had come to any why I joined this forum to ask the question "Faster than an MD3000?", as in what hardware do we need for the next step increase in performance? Do we need to look at the CX4 FC range to go beyond the 30% increase in performance (not that i'm saying 30% is a small figure but ideally i'm looking at 100%+).

Thanks for your help I appreciate you taking the time to reply

Copie

There are a ton faster solutions out there, it's all going to come down to "how much can you afford". It's not going to be cheap, once you exit the dell market prices, it's going to go skyward.

 

[Copie]

There are a ton faster solutions out there, it's all going to come down to "how much can you afford". It's not going to be cheap, once you exit the dell market prices, it's going to go skyward.

I appreciate that it's like asking "what car should I buy and what colour?"

What if I changed my question to read instead: "What is the fastest storage system I could buy for a 1Tb SQL server database with 50/50 concurrent reads/writes for $50k, $75k, $100k. Assume i'm using a Dell R710 with 144Gb RAM and dual quad core CPU"

 

[AreEss]

Thanks for your detailed reply(s), I wont pretend I understand everything as i'm not a storage expert but it looks like even with "out of the box" performance I'm already close to the edge and even with expert help could only increase speed by 30% or so with the current MD hardware.

Not a problem. As the saying goes, the first taste is free. (I actually do consulting, especially remote work, soooo...)
I'd say 30% honestly is pretty optimistic. You're talking about 1-2 weeks of build and rebuild, to see where exactly the controller limits lie. Until you know the absolute controller limits, you can't tune against them, which is part of the problem and the mistake most people make. They take a base number which is low - 200MB/s in our case here - then tune for improvement. That's the backwards way. The right way is to find your theoretical limits, then tune toward them.

If we presume that we have a single link per LUN from the array, that means each LUN can only support a maximum of 275MB/s regardless of spindle count. That's physical link limit. Meaning any spindles past 275MB/s under normal load are effectively wasted unless they're hotspares. Which makes 320MB/s optimistic - that's reasonable with best case caching and >80% cache hit rate. Without knowing the exact application, I can't really give accurate estimates on what your cache hit rate can be expected to be, and I don't imagine the MD3000 has any facility for monitoring it effectively.

This is the conclusion I had come to any why I joined this forum to ask the question "Faster than an MD3000?", as in what hardware do we need for the next step increase in performance? Do we need to look at the CX4 FC range to go beyond the 30% increase in performance (not that i'm saying 30% is a small figure but ideally i'm looking at 100%+).

Thanks for your help I appreciate you taking the time to reply

Copie

Well, I think ultimately you have two common problems and mistakes here.
One, you're making the assumption that 200MB/sec isn't fast enough. Two, you're making the assumption that you absolutely have to have something much much faster.

Let's look at it from this standpoint first; you have 200MB/s. That's slow for direct attach SAS. Can it be improved? Absolutely. But throughput is not the best or most effective metric for databases, much less accurate. The question should not be "what's my sequential throughput"; databases don't do sequential throughput. Your question should be "what's my average seek wait and my random access throughput?" Those are the measurements important to databases. It's entirely possible that you're well within requirements, even at 200MB/sec. (However, it's an outside possibility, given array configuration. You're probably taking some hefty delay on random simply because of alignment.)
The reality of databases, is that most databases are not I/O bound by throughput, but by random wait and seek delays. Databases make hundreds and thousands of short, small reads and writes. Even when you're dumping half a terabyte of data into one, it's all short read and write operations. A very, very busy 2.5TB PeopleSoft 9.0 database I worked on averaged over 30,000 IOPS but only needed maybe 120-140MB/s of actual disk throughput - easily achievable on RAID5 with even lower end controllers.

Now, let's look at it from another standpoint; do you really need what you have? The answer to this question might surprise you, but I honestly believe that the correct answer would be "most likely not." Do you need the MD3000 or similar? Absolutely! But do you need the configuration you have in terms of spindle count? I don't think you do. I think your requirements could be met at a lower spindle count, which will reduce controller load, and potentially improve throughput.

So given that, step one obviously isn't to go shopping. Step one is to step back and reexamine things (after I've woken up myself, need more coffee, ugh!) The 200MB/s is most likely not what's holding you up - software striping, long seek, and random delays are where you'll hurt the most. (Best case RAID1 under 2k3 would average >20% host CPU usage at very low throughput on SATA!)

 

[nitrobass24]

You are correct that it's configured completly wrong, it's "out of the box" as it stands and no tuning has been done at all. Setting these configurable items was the sort of thing i was looking for when googling when I came across this forum discussing similar topics so I thought I would join to ask the question.

You CANNOT just throw hardware at a problem and expect it to work better.
You need have it configured properly.

Buying more spindles is obviously not going to help.

1) Get it configured correctly.
2) If your still not happy get a new controller.

 

[Copie]

You CANNOT just throw hardware at a problem and expect it to work better.
You need have it configured properly.

Buying more spindles is obviously not going to help.

1) Get it configured correctly.
2) If your still not happy get a new controller.

Whilst I agree with you, you would be supprised at how distorted some management views these things.

It would cost say $60k for a dba / developer for an entire year to tune a database.
It would cost say $100k for a much faster storage subsystem.

Management see the years developer time in the light that the products the developer can create can be sold for more than $100k outweighing the cost of the storage subsystem.

In their eyes it IS cheaper to just throw hardware at a problem as hardware is cheaper than developer time.

It's a false agument but until I go back and say "it's not going to get faster until you either pay MUCH more money or spend development time on it" hardware is always the cheapest option. My job is to ensure thats true for as long as possible, i.e. find solutions for faster hardware, which is why i'm asking the question here

 

[Ockie]

I appreciate that it's like asking "what car should I buy and what colour?"

What if I changed my question to read instead: "What is the fastest storage system I could buy for a 1Tb SQL server database with 50/50 concurrent reads/writes for $50k, $75k, $100k. Assume i'm using a Dell R710 with 144Gb RAM and dual quad core CPU"

For 1TB? You can probably look at the fusion-IO.


The cost is about 32k IIRC.

 

[Copie]

For 1TB? You can probably look at the fusion-IO.


The cost is about 32k IIRC.

I did see that product but have not seen enough reviews to convince me, but it's a serious contender, especially when we take into account rack space / AC / power etc

 

[Ockie]

I did see that product but have not seen enough reviews to convince me, but it's a serious contender, especially when we take into account rack space / AC / power etc

Give them a call, with your needs and budget, I'm sure they will send you a review unit and perhaps a demonstration team.

 

[AreEss]

For 1TB? You can probably look at the fusion-IO.


The cost is about 32k IIRC.

Fusion-IO is not a good fit for databases, especially when what he has is probably fine, with some work.

I'll second management being a bunch of idiots. You're going to need the DBA to do that work anyways, no matter what. Sigh.

 

[Ockie]

Fusion-IO is not a good fit for databases, especially when what he has is probably fine, with some work.

You will need to back this claim up. From what I've seen and read, this is an excellent performer and was designed for this type of application (SQL and Oracle)

 

[AreEss]

You will need to back this claim up. From what I've seen and read, this is an excellent performer and was designed for this type of application (SQL and Oracle)

First, what he has is probably fine with proper tuning. Why should he be throwing good money after bad? It's the exact same as deciding your RAID5 isn't fast enough, so you go buy 4 more spindles for the array. Investigating, setting up, and buying a Fusion is going to cost him more than fixing what he has, several times over.
To add to this, he's talking about a terabyte of data. Not a terabyte of disk - of data. A single 320GB card lists at $12K. What good does this do him? Absolutely NONE. He can't even get a terabyte worth of cards into the system. And frankly, it's damn unlikely moving just indexes to a smaller one would offer anything either; it's still dependent on the rotational media. Not to mention, it's a ridiculous and flat out stupid amount of overkill.

Point blank, Fusion-IO is not a realistic or reasonable solution for large databases. Period. It's too expensive for too little gain in the vast, vast majority of cases. It's not large enough for serious databases, and anything that's that badly index bound, is still going to end up bound on a traditional array anyways.

You know what the best use of solid state disk in Enterprise is? Small mirrored OS drives (like ESX with FC attached disk,) and making really stupidly fast RAID3 arrays. I mean really stupidly fast.

 

[Ockie]

First, what he has is probably fine with proper tuning. Why should he be throwing good money after bad? It's the exact same as deciding your RAID5 isn't fast enough, so you go buy 4 more spindles for the array. Investigating, setting up, and buying a Fusion is going to cost him more than fixing what he has, several times over.
To add to this, he's talking about a terabyte of data. Not a terabyte of disk - of data. A single 320GB card lists at $12K. What good does this do him? Absolutely NONE. He can't even get a terabyte worth of cards into the system. And frankly, it's damn unlikely moving just indexes to a smaller one would offer anything either; it's still dependent on the rotational media. Not to mention, it's a ridiculous and flat out stupid amount of overkill.

Point blank, Fusion-IO is not a realistic or reasonable solution for large databases. Period. It's too expensive for too little gain in the vast, vast majority of cases. It's not large enough for serious databases, and anything that's that badly index bound, is still going to end up bound on a traditional array anyways.

You do know they make larger capacity cards right?

 

[AreEss]

You do know they make larger capacity cards right?

And what does that have to do with any other point I raised?

$12K per 320GB with absolutely no fault tolerance or redundancy versus $16K list for 1.8TB usable of 146GB 15K before hotspares on a maxed out MD3000. Cost efficacy of a Fusion-IO setup for him? Here, why don't you go try to sell management on it - spend $60K capital on new stuff plus three or four weeks of setup, or spend two weeks to make what he already owns work.

Just because it's a buzzword and popular with a bunch of people on the Internet, doesn't make it worth a damn. Not to mention, what is he going to do with it, given 200MB/s hasn't been a showstopper? Wave his e-peen?

 

[AreEss]

Getting back on topic, and presuming we have one MD3000 to play around with, let's try this configuration.

RAID10 array, 8 disks, segment size at 64k. Use diskpart to make sure the disk is clear. Create a partition with 64k blocks. Format a filesystem from the command line with 64k blocks. Run IOMeter against that single array, let's see what we get. Should be slightly better, somewhere around 250MB/s peak. Tear down and repeat with a RAID10 of 4 disks. Tear down and repeat with a RAID5 of 4+P instead of RAID10.
What you should see is RAID10 8 disk around 250MB/s, RAID5 around 240MB/s, and RAID10 4 disk around 200MB/sec.

 

[Copie]

Thanks for your comments on the Fusion IO device, I agree with some of your points and whilst I believe this is an immature technology, I think the idea has a lot of legs in the future.

Regarding that the 8 spindle RAID10 is the fastest setup in your example, where is the point of deminishing returns? I'm assuming 3 arrays of 8 is faster than 1 array of 24 (or 30 as in my case). Is this purely down to the controller (in the MD3000) or would with a "better" controller more spindles give more speed? Not that I have that option with the MD3000 but it helps me understand the bottlenecks.

I'm starting to believe that if I split my database up into filegroups on multiple virtual disks of fewer spindles each I'll gain more speed. If this alone will take me from 200MB/s to 250MB/s with zero outlay i'll be very pleased. The bit i'm struggling with though is 3 arrays of 8 is the same number of spindles as 1 array of 24, so how does the same controller deal with the multiple arrays better?

Thanks for all your detailed responses, I appreciate you taking the time to answer my n00b questions.

 

[AreEss]

Thanks for your comments on the Fusion IO device, I agree with some of your points and whilst I believe this is an immature technology, I think the idea has a lot of legs in the future.

In theory, yes. In practice? Not realistically, because then you simply shift to CPU binding. MLC/SLC solid state have many of the same failings of rotational, and are not equivalent or even equal to decade old solid state drives in terms of IOPS. Old solid state drives rely on a combination of ASIC, FPGA, and SSRAM or similar. (I've seen a few with ZBT SRAM - a single ZBT SRAM controller is good for 200MB/s and <0.1ms read/write cycles.) They are much, much faster and handle much, much higher loads across the board.
I don't see any "new" SSD technology being at that point for years; the fact is that the market is a race to the bottom. Who can do it the absolute cheapest with the absolute least additional costs like support staff, etcetera.

Regarding that the 8 spindle RAID10 is the fastest setup in your example, where is the point of deminishing returns? I'm assuming 3 arrays of 8 is faster than 1 array of 24 (or 30 as in my case). Is this purely down to the controller (in the MD3000) or would with a "better" controller more spindles give more speed? Not that I have that option with the MD3000 but it helps me understand the bottlenecks.

Controller and logical limitations. Throwing 24 drives into a single RAID10 is a bad, bad thing. Very bad. You hit diminishing returns where the controller craps out or seeks become excessive, which varies widely by drive, controller, interface, and host.
Here's the problem though; your only way around that would be software striping on the host side or data segmentation. If we presume that one LUN uses 1 SAS channel, that means no LUN can individually exceed 250MB/s realistic under any circumstances. In other words, you're in a bad spot, because your controller doesn't support the functionality you likely need and you're severely interface limited.
If we presume that each drive is capable of 100MB/s sustained sequential read, then an 8 disk RAID10 gives you 400MB/s potential, a 5 disk RAID5 gives you 400MB/s potential, and a 4 disk RAID10 gives you 200MB/s potential.

I'm starting to believe that if I split my database up into filegroups on multiple virtual disks of fewer spindles each I'll gain more speed. If this alone will take me from 200MB/s to 250MB/s with zero outlay i'll be very pleased. The bit i'm struggling with though is 3 arrays of 8 is the same number of spindles as 1 array of 24, so how does the same controller deal with the multiple arrays better?

Honestly, I would have to look at your database to advise you on how to split it. There's also some ugly testing that I'd have to do myself as well. Part of the problem is that we don't know how LUNs are actually handled in terms of controller-host link. If it's being simplistic, no LUN has more than a single link, and each successive LUN goes in a round robin. That means in theory, 1 LUN is 250MB/s, 2 LUNs reach 500MB/s, 3 LUNs reach 750MB/s, and you peak at 1000MB/s. (Again, 4xSAS link.) However, determining whether this is right as well as the behavior of the controller, takes a lot of testing. Never trust the documentation; it was written at some point long ago, and firmware updates may or may not have changed behavior. It's always more reliable to test yourself.

There are two reasons the controller will handle smaller arrays better. First, large arrays require large, frequent, complex parity calculations. They also chew up bandwidth duplicating writes between shelves, which is VERY VERY BAD. Two, the myth of "more spindles is always better" is just that - a myth. Physical spindles are decoupled from the host by the controller, which introduces itself as a limiter or expander. It can do good, it can do bad, like any device. More spindles only works, when you're not choking somewhere else. This is why the SVC is the absolute GOD of database storage. With properly configured arrays behind it, it can stripe a database across hundreds of spindles effectively and efficiently.
There is no controller on the market in the sub-"HOLY $@*!" range that will not choke to death on 24 spindles in any single array. 24 spindles means duplicating 12 writes and performing 12 parity calculations. It also means that you need to move about 800MB/s between shelves over SAS links for duplicate read/write, leaving you no bandwidth at all for the host. That's presuming it uses all 4 SAS links, too.

Every single array on the market, excepting those with proprietary RAID (HP, HDS, and IBM DS8k) prefers the same specific multiples; 2x RAID1, 4x RAID10, 8x RAID10, 4+P RAID3, 4+2P RAID3, 4+P RAID5, 8+P RAID5, 4+2P RAID6, and 8+2P RAID6. In any array type there are several thresholds you do not want to cross; disk or host interface, seek delay and wait, cache, and processor loading. This is oversimplifying it greatly, but you get the general idea. Those specific array arrangements are thoroughly tested and continuously proven to provide the best performance per spindle on virtually any given controller.
The simplest way to put it, is that if you take a 5 pack of 146GB 15K drives in a 4+P RAID5, and install those drives in a variety of controllers, performance between those controllers should generally be very similar and near peak performance. Any performance issues should be isolated to either host, controller, or application. Your drives should provide a theoretical throughput of 400MB/s sustained sequential, thus, numbers below that indicate a limiting factor that needs further examined. (There's better ways that get more accurate numbers, but they're extremely time consuming, because you have to generate a lot of baseline data.)

Thanks for all your detailed responses, I appreciate you taking the time to answer my n00b questions.

No problem.

EDIT:
Thinking on it a bit, if you weren't already heavily invested in the MD3k's, your environment really would be a perfect SVC Express environment. You could use multiple "low cost" FC arrays behind the SVC. The SVC presents the disks to the host as a LUN, but in reality, it's spread out over multiple controllers and multiple arrays. Think of it as a special flavor of RAID in between hardware RAID arrays and the host. The main reason I'd say you're a good SVC candidate is because your primary load is databases, you have multiple hosts which need separately administrated controllers, and you're planning for expansion. The SVC gets between your host and your disk, allowing you to add physical disk without outages, expand and shrink LUNs as needed, and so on. You can even add, replace and upgrade SVC nodes fully non-disruptive - no downtime for maintenance. It's really an elegant system.
BUT, and it's a huge one and why I'm very hesitant to recommend it (though IBM will cut you some killer deals, trust me,) is because it has steep requirements. One, it's FC only. You'll need approved FC switches (I recommend Qlogic,) and FC arrays which are also approved. You'll need an entire FC infrastructure, including HBAs for your servers. You'll also need people who are familiar with storage on both the arrays behind it and the SVC itself. Storage is a very complicated beast. Once an SVC is in place, it's not at all hard for anyone to learn how to do basic vdisk administration - 30 minutes of training, and you can allocate new disks yourself. But more advanced functions, expansion, planning, etcetera, I really recommend far more training. In other words, going to an SVC system would require tossing what you have in entirety, and spending a great deal of money. Even with steep discounts, it's a six plus digit proposition. It positions you better for the future, but the actual need is somewhat questionable - especially with only 6TB of disk. It's simply cost versus benefit. The SVC gives you a lot of things you're missing, but they may be able to be worked around much more cheaply.