4

I am looking for a solution to [dramatically] improve an enterprise database performance on a system that needs to support thousands of transactions (inserts & large selects) per second. Besides the database/queries optimization, we are also looking for hardware improvements, too.

I need someone with experience in database administration that used SSD drives to store databases. What RAID configuration did you use? RAID1 looks like a good choice maybe combined with another redundancy-focused configuration, also SLC technology would be preferred for SSD drives I suppose.

The questions are: what type of SSD drives are you using (make, model)? How are they organized in RAIDs, what improvements did you achieve and how many (major) issues did you encounter? How much are you worried about the reliability of current SSD drives? Would you recommend using them for database (meaning critical) storage?

Also, what was the approximative price (at the time you implemented the solution, of course) of the implemented solution?

Maybe this post can also provide hystorical data about how SSD+database relationship has evolved over the time.

PS: If important, we are currently using SQL Server 2008 R2. Database size: over 400GB at this time, but my question is not only related to my specifics.

Thanks.

An interesting article found here: http://www.remote-dba.net/t_in_memory_cohesion_ssd.htm

6

Start by watching Brent Ozar's introduction into the subject SQL on SSDs: Hot and Crazy Love. Follow up with the extensive and exhaustive benchmarking series from Paul Randal: Benchmarking: Introducing SSDs (Part 1: not overloaded log file array). There are many details, depending on workload type, dat vs. log, placement of tempdb etc etc etc.

| improve this answer | |
  • 1
    +1 for the good references. Benchmarks are useful but the daily experience is valuable too. – Adi Feb 5 '13 at 9:38
  • 2
    I would say that Paul's series go way beyond 'benchmarks'. Also is very important to understand your bottleneck. Consider that 1TB of RAM is $3K. – Remus Rusanu Feb 5 '13 at 9:43
3

I don't use SSDs for production database work directly (IIRC one of the SANs a client's service runs from uses them as a cache layer) but have used them extensively for dev/test work (including databases).

How much are you worried about the reliability of current SSD drives?

No more so than traditional drives, good SSDs are no less reliable then good spinning drives these days. RAID everything is still a requirement not a recommendation, but that is no different then with traditional drives.

Would you recommend using them for database (meaning critical) storage?

Yes, with all the usual caveats (that apply equally to any other type of media).

what type of SSD drives are you using (make, model)?

We've found Samsung EVOs noticeably more nippy than equivalently priced drives from elsewhere, but as our use case for them has not been the same as yours YMMV, and once you get to a certain size of array you might start to find the quality of your RAID controller affecting performance more than the individual drives.

How are they organized in RAIDs

I think the general advice more or less stays the same. The RAID5/6 write performance issue is not quite as significant but is still a consideration (for write heavy workloads at least) so RAID10 is still generally preferred.

Other Notes

Of course a key issue is price, good SSDs are still a lot more expensive than even the best traditional drives per Gb. One option to consider is splitting your data between both types of drive if you have a core set that is used regularly and "online archive" data that is not as often referenced or doesn't need to be as quickly accessed. If you have to think about partitioning for this rather than splitting whole tables by filegroup and you aren't already using Enterprise edition, then you need to compare the licensing cost against the cost of more SSD space!

Another thing to consider is if your bottleneck is the traditional drives as much as you think it is. If all your generally active data fits in RAM with plenty of room to spare then during normal operation you'll only be hitting disk for writes, so if you don't have a write-heavy load pattern you might not see a large performance change except when starting from cold. If your active data doesn't fit into RAM but could practically do so if you bought more, then make sure you consider that option too. How much of that 400+Gb is touched in normal operation? Would the 128Gb per instance supported by standard edition (64Gb as you are using 2008) easily cover it? If you are using enterprise edition already then it will use as much RAM as your physical boxes will accept.

| improve this answer | |
0

For 1,000 IOPS (I/O per second), you cannot go wrong with enterprise grade of SSD (more expensive but more durable and reliable than consumer grade SSD).

SSD provide ~10 times the IOPS than HDD, that even disk array striping (e.g. with 10 disks stripping data in small block) can hardly come close to its performance. Indeed SSD is value buy for enterprise.

google SSD usage experience & facebook SSD usage experience show real world experience.

| improve this answer | |
0

Main positive to SSDs is the performance boost for random reads compared to HDDs. However for sequential reads you'll notice less of an improvement. As a typical OLTP database will use mostly random reads SSDs are good for this purpose.

Main downside is cost compared to HDD, although it has dropped considerably over the last few years. Although more expensive I would recommend enterprise grade SSDs for database workloads.

Of course not all SSDs are the same either, PCI-E based SSDs, M.2 and 3D NAND are considerably quicker than your standard SSD. Naturally these are more expensive.

I would typically say the best performance vs cost option for a regular OLTP database would be tempDBs on PCI-E SSDs, data and log files on standard SATA SSD and backups on HDD. However it will depend on your workloads and where you really require the performance boost.

I would still suggest that having plenty of memory for your SQL instance is more vital than what disks you use.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.