Link to home
Start Free TrialLog in
Avatar of McKnife
McKnifeFlag for Germany

asked on

Choosing SSD drives for SQL Server

Hi experts.

Let's compare only the performance of these two samsung SSDs which cost almost the same:
Let's not compare the other things like warranty, MTBF and TBW.

Consumer: Samsung 850 Pro 512GB
MLC chips, Read: 100K IOPS, Write: 90K IOPS, sequential 550 MBps (read)/ 470 MBps (write)      
vs.
Enterprise: Samsung SM863 480GB
MLC chips, Read: 97K IOPS, Write: 26K IOPS, sequential 530 MBps (read)/ 480 MBps (write)
The drives (4) will run as RAID 10.

If you were to build an SQL Server for sharepoint, which one would you prefer performance-wise and why?
Avatar of John
John
Flag of Canada image

I would choose the latter over the former. Why?  It should take more overall write operations for a longer period of time. You should get a 5 year life (and be able to get a 5 year warranty) for the enterprise drive.

Make sure it is a PCI-e drive as well which is faster than SATA.

I have a Samsung PCI-e 1 TB drive in my ThinkPad X1 and it is a very fast drive.

I have a 4 year warranty on the drive and machine.
Avatar of lcohan
Just one question before that - did you think about the fact they will just die after the certain MAX number of write cycles was written on them before considering using SSD for SQL database storage?
as detailed here: http://androidpcreview.com/are-torrents-damaging-your-mini-pc/2237/
"...the flash storage, including Solid State Drives (SSD), has a finite amount of read-writes that it can perform before the drive just plain dies. No clicking like normal hard drives….just dead." so I would think twice putting my SQL databases on SSD's...but maybe I'm wrong.
Avatar of McKnife

ASKER

Hi John.

The numbers your comment on for sequential write are almost identical (+2% for the latter). The IOPS are by far better at the former drive (+300%).
The warranty does not matter and is by far better (10 years!) for the former.

"Make sure it is a PCI-e drive as well which is faster than SATA" - well... then please try to find a PCI-e-drive Raidcontroller for me, will you? That segment is pretty new.
Avatar of McKnife

ASKER

@Icohan:
The TBW value you are talking about is huge for the consumer one as well: 150 TB (written). So if we write 50 GB per week, that would last about 60 years.
Avatar of Dr. Klahn
Dr. Klahn

I agree with John on this one.  Consumer grade drives should not be used in any application where RAID is being considered.

By the time the drives are RAIDed together in a 4-pack, that 26,000 I/O per second will become 104,000 I/O per second.  I don't think it is possible to shove 20,000 meaningful disk I/O per second through a volume, much less 100,000 I/O per second.  I'm sure there's a synthetic test where a specially designed drive tester hammers the drive as hard as it can; in the real world this doesn't happen.  So I/O per second is not an issue once the drives are RAIDed.

But if it's important enough to RAID the drives, then it's also important to maximize the reliability of the data.  That's why enterprise quality drives must be used.  They allocate more spare blocks and warn early of impending write leveling failures.

The difference between less than a dozen $100 and $250 drives to your company is negligible.  The pain and suffering that will descend upon you from above if there are issues down the road and you are still employed by the company, is not.
Here is a good article on PCI-e vs. SATA. PCI-e has more data paths and can be made faster whereas SATA has reached its limit for speed

http://www.overclock.net/t/1489684/ssd-interface-comparison-pci-express-vs-sata

I did a quick look for controllers and suggest you look at Intel PCE-e controllers.

Enterprise drives tend to be overprovisioned with extra cells to allow more internal failures without the drive failing. In a Server environment, I think that is important.

PCI-e is meant for Server operation so that is where I would head.
Avatar of McKnife

ASKER

John,
"I did a quick look for controllers and suggest you look at Intel PCE-e controllers" - sure, but which one, at what cost?
"Enterprise drives tend to be overprovisioned with extra cells to allow more internal failures without the drive failing. " - it's hardly useful to speak in general. Where on the fact sheet for the drives do you find that?
"Here is a good article on PCI-e vs. SATA. PCI-e has more..." - sorry, but you are not speaking to a newbie when it comes to storage. The PCI-e is better, totally agreed, but the raid-requirement is what makes it next to impossible. For example, there is this controller: http://highpoint-tech.com/USA_new/series-rr3800a-overview.htm - it would suit. But even finding a price for it is hard.
There are not many conrollers that can do raid for these drive types, yet.
Avatar of McKnife

ASKER

@Dr. Klahn
"Consumer grade drives should not be used in any application where RAID is being considered" - where does it say so?

"By the time the drives are RAIDed together in a 4-pack, that 26,000 I/O per second will become 104,000 I/O per second" - no. It will become 52,000 (because it's a RAID10.  

"I don't think it is possible to shove 20,000 meaningful disk I/O per second through a volume, much less 100,000 I/O per second" - you dont think so, so why would Samsung build drives (like the 850 pro) that deliver next to 100,000 IOPS in a single drive, then? Purely for showing off? ;-)

"it's also important to maximize the reliability of the data.  That's why enterprise quality drives must be used.  They allocate more spare blocks and warn early of impending write leveling failures."
They do? Where does it say so for the model in question?

"The difference between less than a dozen $100 and $250 drives to your company is negligible." - what drives are you talking about? The drives in question cost almost the same and both over 250USD.
From a strategy and business perspective

1. PCI-e (while more expensive) is today's technology and SATA is now yesterday's technology.
2. Enterprise equipment is made for commercial server environments,
SOLUTION
Avatar of Member_2_231077
Member_2_231077

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
PCI-e SSD drives are faster than SATA will ever be. That is why I suggested them.

The PCI-e SSD drive I have here has its own controller and I boot from it just fine.
Avatar of McKnife

ASKER

@andyalder: Hi. Yes, how about comparing these things I didn't want to compare, why not do it although I explicitely wrote "please don't"? :-) Look, I know a thing or two about storage myself. I am not the guy who chooses the wrong stuff for his needs. I am consulting on this one. And the one being consulted looked at the enterprise one and looked at the IOPS and said "as far as I remember, this is pretty low" and he is right if we compare it with for example (you'll guess it) the 850 pro which is a better consumer product.

So I need support in deciding whether this difference in IOPS even matters for an SQL server. That's why I tried to reduce the comparison to the performance aspect.
--
@John
If PCI-e would be available in our price range, which, for storage alone should not exceed a certain limit, let's say controller plus 4 drives less or equal 2500 USD, then why not. but I am already having problems to locate a controller model, maybe you know one?
PS: booting does not matter. This RAID will not be used for booting.
I only saw Intel controllers for PCI-e. The technology is still new. I imagine Samsung has a controller but I have not yet found one.
Avatar of McKnife

ASKER

They do? Please link it.
The last link is about an onboard raid controller, not sold separately and not samsung.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of McKnife

ASKER

Yes, "something" like this. That is surely no enterprise product and I guess reviews like this one are correctly rating it rather low.
But I'll look into similar things. If you again find that intel controller you read about, bring it on.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Scott, He doesn't care if it crashes and reboots with wrong data so long as it runs fast. If there is a power glitch he's happy because SQL/Sharepoint is still running (albeit with wrong data).

Wish the lounge was still here. we could bat it out between two small boy's buttocks in the old days :)
Avatar of McKnife

ASKER

Good one. I wish it was.
No, you can believe I care.

Scott, about your numbers, IOPS per GB, could you explain the calculation? The bigger the DB, the...?
Avatar of McKnife

ASKER

Scott, about your link again: scratch my last question. I asked, because I was confused by the small numbers.

See, if we setup Raid 10 with 4x500 GBs, we have 1 TB usable, so even if the database used the whole drive that would mean multiplying
1000 GBs with 0.5 IOPS/GB, which gives just 500 IOPS.
So I thought, why would you even stress it and write "it did tend to use lots of I/O" while 500 is very little.

Still my question stands. If you were to build an SQL Server for sharepoint, which one would you prefer performance-wise and why?

PS:
@John: we have decided against using NVMe Raid, because it seems any of these controllers are new and have not been industry proven, yet.
You can use SATA drives and controllers and that will work.

However to your original question, I would still use the Enterprise drives. That would be my choice.
I got those numbers from MS.  That was their specification.

When I had to manage Sharepoint, I noticed that I/O was by far the most difficult issues I faced.  And the performance I saw does not necessarily match what MS claimed.  Their values may be based on far more drive sets and/or much fewer users.

I'm also not sure what the best disk block size for Sharepoint is, but I would suggest researching it and using the best blocking, as that may improve performance significantly.
Avatar of McKnife

ASKER

@John
You said so. And you added "From a strategy and business perspective" - while the question was about nothing but performance. So allow me to ask if you'd choose the enterprise one for performance reasons despite the IOPS being so low (1/4 of the consumer drive's IOPS). And if you did, why?

@Scott
So what drive of those two would you choose for performance?
You also went for other aspects like durability, but those don't matter.

Guys, don't get me wrong, I am not going for the fastest drive - it is just one aspect. It is simple: if you pick drives with a high durability, they do have low IOPS, it seems. So if we say, we don't need a drive that lasts 60 years, we might go for the performance and thus maybe not choose an "enterprise" drive.
All the concerns about power loss might not even apply if you are connected to a UPS.

So I wondered, just reading the number of IOPS, if anyone else would be tempted to buy the consumer one just for the performance of if there is any aspect unknown to me that would in the end boost the performance of the IOPS-weak enterprise one so that it would be considered faster for SQL/sharepoint.
As I said, I would go for the faster one if you're very likely to replace the drive in 5 years (maybe 6) anyway.  Any drive should last that long.  But if that company is one that tends to really try to stretch the life of all hardware, then I'd go with the enterprise version.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Correction to working:

As I said, I would go for the faster drive if you're very likely to replace it in 5 years (maybe 6) anyway.  But if your company is one that tends to really stretch the life of all hardware, then I'd go with the enterprise-level drive for its durability.

And again, for the record, I am a DBA, not a disk expert.  I am speaking purely in terms of best performance and management for SQL Server, not of the drives themselves.
Avatar of McKnife

ASKER

@andyalder:
"you are using the write IOPS figure wrong, the random write performance of the SM863 is most likely quoted as queue depth = 1 whereas the 90K IOPS figure you have listed is for QD=32" - so how would you "use it right" to make it comparable? The numbers are from Samsung's datasheets and no QD is indicated.

Edit:
...wait a minute... I relied on someone else's figures.... and it seems that someone made an error quoting datasheets. There is a QD indicated.
Will take a while to sort this.
Avatar of McKnife

ASKER

Ok, no, the numbers weren't wrong.
--850 pro---
4KB Random Read (QD1): Max. 10,000 IOPS
4KB Random Write(QD1): Max. 36,000 IOPS
4KB Random Read(QD32): Max. 100,000 IOPS
4KB Random Write(QD32): Max. 90,000 IOPS

---SM863 480GB---
4KB Random Read (QD1): no info on datasheet
4KB Random Write(QD1): no info on datasheet
4KB Random Read(QD32): Max. 97,000 IOPS
4KB Random Write(QD32): Max. 26,000 IOPS
Avatar of McKnife

ASKER

After more reading: if I get it right, the Power-Loss Protection is necessary to let the raid controller enable the write cache which significantly improves performance. I repeat: if I get it right. You can also read that people are finding ways to circumvent this in order to enable the write cache even on drives that don't have that feature.
Well, we have a UPS running and haven't had power loss crash in x years, but still, having to trick the controller is not what I feel we should be doing, though possible.

Source: https://forums.servethehome.com/index.php?threads/are-samsung-850-pros-not-lsi-9361-8i-compatible.5804/
Since we thought about that very controller as well, I guess I'll give Avagotech/LSI a call tomorrow. Time to go home now.
PLP is so that the SSD can flush its own RAM to flash, it's the equivalent of the battery on the RAID controller in that respect. There's a graph of that on page 14 of http://cdn-reichelt.de/documents/datenblatt/E600/MZ-7KMXXXE_DBA.pdf

And you're right QD=32 from that datasheet as well.
Avatar of McKnife

ASKER

Time to close. Thanks all for participating and offering thoughts and critique.
I spoke with broadcom's (formerly avagotech formerly LSI) support and he said
"both drives are on the compatibility list, both are expected to work well. I cannot recommend one over the other since we are not allowed to do that. And no, the write cache will not be enabled by our controller, it will either be on or off, you can set that yourself based on your preference - without powerloss protection or UPS it will be of course recommended not to enable it".

So I looked at what info I have so far and the numbers are few. I have found one thread with poor numbers for the 850 pro and another thread with better numbers for the SM863 - too bad I cannot afford to test it myself. We have voted to buy the SM863 in the end.