Using a "consumer" SSD on a corporate SQL DB

I

Innocence

2[H]4U
Joined
Mar 9, 2001
Messages
2,604
Hey [H]-

Long story short, our company has a business system that is made by folks who were really good at programming a Terminal-Based Unix system, but Really REALLY bad at development in a windows environment. And we're stuck with it (Primary Vendor compatibility). It's used by about 200 users simultaneously 12-16 hours a day, with peaks to 350-400 users.

The main problem appears to be the way they're working with the SQL backend. A simple action takes over 5 seconds to return results, even on our 0-user-load test system (running on a Xeon-5560 and RAID10 15K SAS2 array on a P410i with 512 BBWC). Pretty much all the SQL delays appears to be disk waits.

At the moment, throwing in $10k worth of "Enterprise" SSDs isn't an option.

So, has anyone successfully run "consumer" SSDs (such as a Samsung 840 Pro) in a relatively high-usage SQL environment? Any idea if a PERC6 or P410/P420 would do proper wear leveling and failure monitoring on consumer SSDs?
 
I've used Crucial M4s in a high end VMWare servers and MySQL db's without too much of an issue. IOPS goes through the roof.
 
I've done this for a few VM hosting boxes recently. I used the 840 Pros and set aside half their capacity for over provisioning just to be safe until I can get back some data on how they are holding up and how quickly their lifespans are degrading. If it works out well, the client may be willing to lay out the cash for an slc drive in the future. The speed difference he noticed when we put the ssds in was the first time I've ever heard the guy use profanity in a positive way lol
 
It greatly depends on what the database is used for, if you're talking a few GB worth of writes or less a day and predominantly reads, then yeah the consumer is fine
 
Innocence said:
Hey [H]-

about 200 users simultaneously

At the moment, throwing in $10k worth of "Enterprise" SSDs isn't an option.
Click to expand...

Economics is wonderful.

$10K/200 is $50/user. So "enterprise" SSDs are a good economic option.

If consumer SSDs will handle the job requirements, it makes it cheaper, but "Enterprise" SSDs are certainly a reasonable choice.

---

How do guys get these jobs.
 
Have you done ANY sort of performance optimizations like indexing and make sure you have the right setup for your tempdb and such? You can gain orders of magnitude of performance from a few simple changes. Also, dont use SSD's in that environment that aren't designed for it, unless you want to add other issues like data corruption and outages to poor performance. Also, look at adding ram.

More spinning disks can help too if properly setup.
 
Monkey God said:
Have you done ANY sort of performance optimizations like indexing and make sure you have the right setup for your tempdb and such? You can gain orders of magnitude of performance from a few simple changes. Also, dont use SSD's in that environment that aren't designed for it, unless you want to add other issues like data corruption and outages to poor performance. Also, look at adding ram.

More spinning disks can help too if properly setup.
Click to expand...
I'm not a DBA. All possible optimizations on the SQL side have been done as far as our data team is concerned - I'm inclined to believe them since they keep all our other DB's running extremely quickly. This is just a one-off frankenstein that causes us problems.

Adding RAM has been discussed, but current usage isn't hitting what's available to it. We tested limiting SQL to 8GB (of the 32GB in the box) for 4 days and there was no measurable difference in response times.

There is no room for additional disks - currently it's a RAID1 OS array (2 disks) and a 6 Disk RAID10 array for data. There are only 8 bays in the server.

Other standard steps like disk alignment have been done as well.
 
GeorgeHR said:
Economics is wonderful.

$10K/200 is $50/user. So "enterprise" SSDs are a good economic option.

If consumer SSDs will handle the job requirements, it makes it cheaper, but "Enterprise" SSDs are certainly a reasonable choice.

---

How do guys get these jobs.
Click to expand...
I'm not arguing with you - but I'm not even going to bother discussing with you how difficult it can be to get ownership buy in to unforeseen expenses in a Medium business.

If it was in the budget, it wouldn't be a problem. Getting it into the budget is a problem.

"How do guys get these jobs"? What kind of bullshit is that? Feel good now?
 
Last edited:
GeorgeHR said:
How do guys get these jobs.
Click to expand...

We'll that's uncalled for. Being creative and frugal is a positive. Just need to go into it with eyes wide open.

OP,
You really need to know the amount of writes being done to estimate if a MLC drive will cut it. If those hundreds of users are generating heavy writes you can burn through a consumer SSD fast.
How many IOPs are your drives serving now?
Read and write latency?
Queue depth?

Assume you're going to need at least 3x SSDs that are over provisioned/short stroked. RAID 1 and a hotspare on a reliable battery backed up RAID controller. You're probably not going to do it right even with consumer SSDs for much under $2k.

Edit: Here's an example of my SAN sporting 6x 400GB enterprise SSDs in a hybrid RAID 60. They're only good to about 20k IOPs with my workload. Essentially all active data gets read and written from the SSD cache. Far, far, below what the SSDs raw rated performance is good for.
san_snap.PNG
 
Last edited:
Intel 320 is the only reliable consumer-grade SSD right now. Soon the Crucial M500 can be added to that. These are the only SSDs for the consumer market that utilise power-safe capacitors.

What olavgg said is unfortunately not true:
If you can disable the write-back cache, you will probably be fine.
Click to expand...
This only works on mechanical harddrives. SSDs can die instantly or have all kind of corruption issues when it loses power unexpectedly. This happens far more than people think. You can look at the SMART attribute 'Unexpected Power-Loss' to see how many times this occurred. Every time this happens on an SSD without power-safe capacitors, the SSD can become corrupted or even die, requiring a secure erase procedure before it'll work again.

If you do stuff with SQL, you do not want corruption. Only choose SSDs with power-safe capacitors. Even then, use RAID1 or even better: ZFS.
 
we use Samsung and PNY consumer drives for our development sql and production web servers over the past 2 years. not one failure yet out of dozens of drives.
 
Innocence said:
how difficult it can be to get ownership buy in to unforeseen expenses in a Medium business.

"How do guys get these jobs?"
Click to expand...

For the small businesses I do work for, $10K is well within my discretion.

I want a job like yours. It seems like a lot of people who post here have jobs I could do.
 
If its that many users a ZFS tiered-storage with a combo of fast spindles and maybe some mirrored SLC ssd's from intel (harder to find and expensive, but will hold up against the writes), and a LOT of ram. It might get you close to the enterprise class performance SSD's
 
You must log in or register to reply here.
Back
Top