If you are really observant you may have noticed in my RRD graph post that our write times on our database server are not so hot:

Also looking at this from SQLs perspective confirms this:

Our sequential log writing however is really fast (<10ms), but there is a write performance problem when it comes to our database files that we need to fix.

Too Many Variables

Figuring out what direction to go in with our storage is proving to be quite challenging. The main reason is that there are just so many variables:

• Cost and The Total Cost of Ownership (TCO)
• What will satisfy our needs and prepare us for growth?
• What gives the best performance for our workload and how is that workload going to change?
• Reliability and fail over options
• What direction is storage heading in as far as technology goes
• What fits our scaling pattern

Right now we are thinking about 3 main options:

• A PCI card storage solution like a FusionIO drive
• A SAN
• SSDs in the servers themselves

However, before looking at these options there are some fundamentals and issues to consider when it comes to storage.

The Problem with Figuring Out what is Needed for an IO Workload:

When it comes to analyzing IO workload there are some basic questions that must be answered:

How Much?

There are two main measurements used to answer the question of “how much?” with storage. There is how much data is moved within a certain amount of time (MB/s) and/or how many logical operations there are (IOPS or input/output operations per second). When looking at the workload the amount of operations in the queue is also considered for data that has a steady rate.

How Much of What?

The operations are either going to be reads or writes. The reads or writes will also be either sequential or random. So you end up with some blend of the following four possibilities:

• Sequential Reads
• Sequential Writes
• Random Reads
• Random Writes

What is the Shape Over Time?

Sometimes the shape of IO will be a steady stream of data, but often disk operations will get batched to make the IO more efficient. This means that the shape of the IO will usually be spiky on a micro level (say over several seconds). There will also be a shape on the macro level because most services have peak usage times and there are also scheduled IO intensive operations such as backups.

How Fast?

With whatever workload, operations need to be fulfilled within a certain amount of time. If the disk IO system is busy, then operations have to wait in a queue to be fulfilled.

My main point with all of this is that it is not advisable to just take an average of the amount of IOPS and Megabytes per second over a day and go buy a storage solution. This does not account for how fast these IOPS are satisfied or the shape of data over time. Even if these are taken into account, and taken into account correctly, the workload still should be tested on the actual equipment. The best an analysis can do is give a hypothesis. This leaves two possible courses of action:

1. Take an educated guess and buy something
2. Set up a demo unit or go to a demo location and load test the application

The main problem with option number 2 is that this is a large time investment, and that you have to have the capability to load test your application in a way that accurately reflects real world usage.

My Personal Gripe with Storage Vendors

My gripe is that most large or fast storage solutions can not be purchased at Micro Center. More seriously, even when talking to a sales person on the phone they won’t say what all the options are and what they really cost. Sometimes there is an option to price them out on the website, but the real cost is what the sales person will knock it down to. The sales people want the data I was just talking about (often a limited subset which will only give them a rough ballpark). I have always hated this sales method, I want to see what I get for certain costs — not tell them how much I can spend and have them tell me after that.

The other big thing is that vendors are not public with their actual performance numbers. The SPC1 benchmarks are the best effort I have seen to provide useful information, but the amount of devices in that repository is limited. To a degree this is understandable given all the different workloads as I mentioned, but some basic numbers on various workloads under a Raid 10 configuration would be nice. In other words “Give me some data, please”.

Our Particular Situation and Scaling Model

The sheer size of the stackoverflow.com database compared to our other sites at the moment is a major factor for us. Looking at the above image the amount of IOPS on the stackoverflow.com database is 30 times the amount of IOPS for the superuser.com database. Because of this treating stackoverflow.com’s database as a separate entity for the other databases does make sense.

We also don’t need that much capacity. Going off of Nick Craver’s growth analysis the SO Database will grow from 85 GB to 256 GB over the next 36 months (note: this is just a projection).

I have mentioned this before but our model has been to strike a balance between scaling up and out. We are not particularly attracted to building giant monster systems, nor do we want a bunch of cheap little boxes. We want a balanced amount of medium powered servers. In my mind this fits well with a Microsoft stack.

The Current Options

So taking the above into account here is my current thinking on what the following options might mean for us. We don’t have any demo units in hand yet but our plan is to evaluate FuisionIO as the PCI card option, Equallogic as the SAN option, and Dell approved SSDs put into our current Dell r710 database servers.

Option 1: FusionIO

Pros:

• FusionIO is going to be the fastest option out there. To quote Brent Ozar in his review of FusionIO: “The only way to outperform a Fusion-IO drive is to invest six figures in a SAN and hire a really sharp SAN admin.”
• Simplicity. There is a lot that goes into configuring a SAN correctly, with FusionIO we would copy our database file to the FusionIO drive and be done.

Cons:

• Limited single system availability. There doesn’t seem to be a simple RAID equivalent. For each single server there will likely be only one of these cards in each server. Two can be put in a server and set up to use software RAID but I wonder if that might just end up lowering the availability. In theory since these are solid state devices and not mechanical I would expect to have better reliability than hard drives, but the technology is still fairly new.
• Limited multi-system availability. Any sort of SQL clustering options are out the window and what you have left is log shipping and synchronous or asynchronous mirroring.

I think the FusionIO option fits our scaling model well. We currently have two DB servers — a primary and a fail over. We are planning on expanding to 4 servers so Stack Overflow (and maybe the rest of the original trilogy) can have its own primary and secondary server. There are different options, but one 640GB FusionIO would cover growth for the trilogy and provide the fastest speed compared to a SAN or SSDs. We could then have asynchronous mirroring to the secondary server and in a failover situation Brent Ozar estimated 90 seconds of data loss. Downtime might be around 30 minutes until we get the secondary server up and going manually. We generally favor speed over the highest possible uptime. It is not that we are glib about the uptime of our service, but we don’t have the uptime requirements of a financial institution. For our sites with higher uptime requirements such as Careers we can use the storage in the servers and possibly synchronous mirroring. I also imagine either this or SSDs in the servers themselves will be the cheapest solution — the 640GB drives were quoted at about 10k each for us and the 320GB at about 6.5k from a vendor.

Option 2: A 10GE EqualLogic San with some SSDs

Pros:

• Flexibility in growth and tiered storage. With a SAN we can add shelves as we grow and can tier our storage effectively. So for example we could have an SSD array for the trilogy and a SAS array for our smaller sites. As our sites grow we could move them accordingly. We could also use storage for logs or tempdb.
• Flexibility in availability options. Unlike a fusion drive or SSDs in the server clustering options are now open to us.
• 10GE might be useful for other things if we start to hit network bottlenecks. This is the main reason why 10GE appeals to us more than fiber.

Cons:

• The SAN as a logical unit is a single point of failure unless you buy two. I know these have lots of built-in redundancy but nonetheless our current thinking is that we would want two if we went the SAN route.
• Cost. These things are not cheap. The EqualLogic PS6010S with 8 SSDs is priced at 46k on their site. The redundant 10GE switches if we go with Dell would be about 20k. So without even factoring in other various total cost of ownership factors if we want two SANs we are talking well over 100k. That would be the same cost as getting at least 8 more of our current database servers.

The flexibility and growth options that a SAN offers are appealing. The cost could drastically change if we decided we could live with one SAN, look at different vendors, or give up on the option of having SSDs in the SAN. The performance won’t be as high as the FusionIO would be but for our workload that extra performance might not really matter.

Option 3: SSDs in the Dell Servers

This option would be pretty similar to the FusionIO option except that there will be a trade off of an increase in single server availability options but a decrease in speed. With SSDs we can use a traditional RAID configuration in the servers. These drives on Dell’s site are 4.6k for a 2.5 inch SAS 3 Gbps drive, so for a mirror of two drives it would be $9.2k and would give us 150GB capacity. This wouldn’t leave us much room for growth so we would probably want 300GB capacity. In RAID 10 that will cost about 18.4k. At the moment I don’t have any data on how this would perform but with the current cost of the Dell approved SSDs for our servers this option isn’t too appealing to me yet.

Conclusion

Right now we are still in the preliminary stages. Initially I am fond of the FusionIO option where as George is more in favor of a SAN. One of the main reasons I favor FusionIO at the moment is that it would satisfy our growth in the short term, interestingly enough this is one of the reasons George is less fond of it. George’s main reason for a SAN is that we get greater flexibility with the features a SAN offers such as the ability to fully cluster, use snapshotting, full LUN replication, and dynamic expansion (on some models). By going the SAN route earlier and not later we don’t put off solving the problem until about a year from now when it might be harder to change our infrastructure. If we were to get a SAN now we will learn how to use the advantages given to us by the SAN’s flexibility. I generally agree with this philosophy but not in this particular case. The reason I favor putting off a SAN is that I feel in a year or so the SAN options that include SSDs might be a lot cheaper and more attractive. Also the FusionIO option fits well with our current scaling model. Although the growth of our Stack Exchange sites looks very promising to me, I feel it is too early to predict how they will grow. This is not so much in terms of visitors but more in terms of the IO workload growth. Our developers could make changes that greatly effect our IO workload. So we might have a much better understanding of what we need 6 months to a year from now than we do currently.

What we really need is to get more data on the performance of these various options and get our hands on some demo units. I feel like all 3 of these paths are valid options. Also there are valid options we haven’t looked as closely at yet (for example SANs that don’t support SSDs). At this point it is clear that choosing a storage route to take is no small task.