Page 1 of 1

Is SSD PCIe storage suitable for a database?

Posted: Thu Aug 23, 2018 11:42 pm
by ace1400
I am hearing lots of conflicting advice from different consultants. Our mission critical application is a large (3TB) SQL database with about 150 simultaneous users. Our hardware vendor set up the database to live on mirrored datacenter grade Intel 4TB PCIe SSD drives. We have had some problems that the vendor has blamed on now remedied configuration issues. Our other consultant thinks that a spinning disk RAID array is a much more reliable solution. They just talk in circles around each other. I know there are knowledgeable people on this board - are mirrored PCIe SSD's a reasonable solution? Should we go back to spinning disk? Are there any statistics to judge the reliability of spinning disk array vs a single SSD? Thanks.

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 12:38 am
by creditdefaultswaps
SSD PCIe is just SSD over a PCI bus, so you can expect reads and writes to be faster than spinning disk. That said, network speed and app inefficiencies are more likely to be limiting factors before you run into disk access bottlenecks. So yes, you can use it for a database. Do you need it? That depends. It may be more than is necessary for the job. One should still take precautionary measures (backups, etc) even if going the SSD PCIe route.

Personally, unless disk access is a specific bottleneck for my database cluster, I'd optimize for cost vs. performance.

Backblaze.com publishes annual drive stats that are widely followed in the tech community:
https://www.backblaze.com/blog/hard-dri ... s-q1-2017/

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 1:00 am
by curmudgeon
SSDs are not a magic bullet for database usage. And I've seen issues with nominally high-end SSDs (from various vendors) in the past when put to heavy use. Though the specs in theory may be up to the job, sometimes Murphy intervenes. Random read access is great (which is often an issue for database performance), but heavy log writes or mass data updates can show limitations.

I haven't looked in depth at this stuff for a few years, but my instinct would be to say that if the i/o is 90% read, I might lean towards the SSD option, while if it has more writes, then go for the disk array. More disks, and faster spinning disks, will help performance in the disk array if that is an issue.

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 8:37 am
by Watty
ace1400 wrote: Thu Aug 23, 2018 11:42 pm Our mission critical application.....
Before I retired I didn't work on the hardware side but here are a couple of questions I would ask in additional to looking at normal failures.

1) If something happens like there is a lighting strike, fire, etc that destroys the equipement then how would you recover? If the mirrored solution you are looking at mirrors the data at a remote location then you might be able to recover the data from the remote location.

2) If something happens like there is a software problem, virus, human error, or a disgruntled employee that deletes or corrupts your data then how would you recover? It has been a while but as I recall some mirroring systems can also record all the transactions so that you could go to the last backup and replay all the transactions up to the point where the problem happened.

3) If there is a problem then how much down time are you looking at? With some raid systems you can replace a bad disk without even bringing the system down. With a mirrored system it might take a while to replace a drive and resync the data. In addition to the time to do it how long will it take to get the replacement hardware? With a RAID system you might be able to keep a replacement drive onsite that is ready to install.

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 12:08 pm
by MindBogler
How are they mirrored? In a hardware raid controller or software? Software raid is not a good solution for production environments. The question of backup and disaster recovery is independent. You should have a recovery mechanism in place regardless of the technological implementation.

That said, SSD is superior performance to spinning disk in any comparison involving an equal number of disks: random read/write, sustained read/write. That point is a fact and not debatable. They are inferior when cost is considered.

My first question though would be why is your database 3tb? That often indicates some database hygeine might be in order. One strategy is to archive older records to another database on a cheaper storage solution. This has the added benefit of improving overall performance, especially if some people are writing poor queries... It also lowers and facilitates RTO/RPO.

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 12:17 pm
by bman3
Intel was a good pick. They have been doing PCIe drives for a long time, and SSD's for even longer. I trust them in the datacenter space, and I use them in our servers at work.

If you have mirrored drives like you said, and good backups, then I wouldn't be overly worried.

You would need to have a large RAID array of spinning disks to match the IOPS of one of those Intel drives. In all likelihood, you removed your storage bottleneck, and are now running into different problems like the configuration issues you mentioned.

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 12:28 pm
by rgs92
Can't you just have cloud storage for all of this? It's only a few TB. Wouldn't access and transaction processing be fast enough for users?

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 1:01 pm
by bampf
ace1400 wrote: Thu Aug 23, 2018 11:42 pm I am hearing lots of conflicting advice from different consultants. Our mission critical application is a large (3TB) SQL database with about 150 simultaneous users. Our hardware vendor set up the database to live on mirrored datacenter grade Intel 4TB PCIe SSD drives. We have had some problems that the vendor has blamed on now remedied configuration issues. Our other consultant thinks that a spinning disk RAID array is a much more reliable solution. They just talk in circles around each other. I know there are knowledgeable people on this board - are mirrored PCIe SSD's a reasonable solution? Should we go back to spinning disk? Are there any statistics to judge the reliability of spinning disk array vs a single SSD? Thanks.
Mirrored SSD and RAID Arrays can mean essentially the same thing. (Many arrays are shipping all flash, and yes, I know that changes the meaning of raid to RAIS or RAIF but no one actually pays attention to that). Spinning disk fails at a much greater rate than ssds. RAID encapsulates mirroring and more extensive raid protection aspects as well (RAID 5 etc.... ) This is a configuration choice. Raid mirrored in the same device is not as "safe" as two eventually consistent mirrored copies disaggregated across availability zones. Local performance is generally better than disaggregated performance. This is a lot of words to say you are asking for the answer to a very complex question on a financial board. Personally, I deploy very large databases (50+TB) on software defined mirrored storage on very large cloud storage pods that are capable of 20MM IOPS and aggregate bandwidth of 35GBs. Can it work? You betcha. Does it work for you? Well, you have to give a lot more details and you probably need to pay for years of expertise to adequately answer that question.

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 1:02 pm
by bampf
rgs92 wrote: Fri Aug 24, 2018 12:28 pm Can't you just have cloud storage for all of this? It's only a few TB. Wouldn't access and transaction processing be fast enough for users?
That really depends on latency and a bunch of other things. People deploy dbs in the cloud all the time. The use case matters.

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 1:07 pm
by oldcomputerguy
creditdefaultswaps wrote: Fri Aug 24, 2018 12:38 amOne should still take precautionary measures (backups, etc) even if going the SSD PCIe route.
Especially if one is going the SSD route. Given the large load on storage devices typically imposed by database activity, SSD might not be the best choice. You might want to research how SSD devices have shorter write-cycle lifespan than HDD devices.

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 1:48 pm
by MindBogler
bampf wrote: Fri Aug 24, 2018 1:02 pm
rgs92 wrote: Fri Aug 24, 2018 12:28 pm Can't you just have cloud storage for all of this? It's only a few TB. Wouldn't access and transaction processing be fast enough for users?
That really depends on latency and a bunch of other things. People deploy dbs in the cloud all the time. The use case matters.
They also put the database consumer in the cloud as well. Accessing a high IO database over the internet, while possible, would lead to significant performance degradation.

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 5:53 pm
by Phineas J. Whoopee
Agreeing with prior posters, the specific technology of the storage is not the essential point.

The essential point is maintaining backup discipline, including on-site and off-site backups.

Once in a department where I worked the tech support person presented the middle manager with two options: buy a new drive or tape system for backups; or create a new partition on the existing hardware. The middle manager, whom the support person didn't educate on the issue, made a cost-based decision.

You've already guessed what happened. The drive suffered an unrecoverable hardware failure. Much of the data was never restored.

At least the support person got a bonus for reducing the department's capital spending one year.

PJW

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 8:24 pm
by haystd
If you're using Intel enterprise drives (and you probably are), they are designed for database type workloads. Nonetheless, you still need to monitor drive health, particularly write cycles / life used. You can do this through something to monitor SMART data. I have seen companies go cheap and use consumer grade SSDs. While this usually works, they sometimes don't always play nice with RAID controllers or large numbers of parallel writes. As other posters have said, redundant storage on the server does not equal backups and they should be performed and validated regularly.

SSDs wearing out does happen though, I recently diagnosed a server that had burned through the write cycles on a set of consumer grade SSDs in a RAID 10. I was not particularly impressed by that particular model of drives (due to other premature deaths in the enterprise), but they did see an incredible number of writes before failing.

Way off topic, but as far as hardware RAID vs software RAID. It depends, on Linux many argue that software RAID is both more reliable and more performant. And, ZFS is generally regarded as superior to hardware RAID in many use cases.

Re: Is SSD PCIe storage suitable for a database?

Posted: Fri Aug 24, 2018 11:54 pm
by gostars
Phineas J. Whoopee wrote: Fri Aug 24, 2018 5:53 pm Agreeing with prior posters, the specific technology of the storage is not the essential point.

The essential point is maintaining backup discipline, including on-site and off-site backups.

Once in a department where I worked the tech support person presented the middle manager with two options: buy a new drive or tape system for backups; or create a new partition on the existing hardware. The middle manager, whom the support person didn't educate on the issue, made a cost-based decision.

You've already guessed what happened. The drive suffered an unrecoverable hardware failure. Much of the data was never restored.

At least the support person got a bonus for reducing the department's capital spending one year.

PJW
Hopefully the manager was sacked for giving too much responsibility to a person who clearly wasn't qualified for it. Giving systems administrator duties to "tech support" is an inexcusable failure in judgment. No halfway competent systems administrator would even consider another copy of the data on the same disk to be a backup, much less propose that as an option to management.

Re: Is SSD PCIe storage suitable for a database?

Posted: Sat Aug 25, 2018 12:12 am
by whodidntante
This question again?

Re: Is SSD PCIe storage suitable for a database?

Posted: Sat Aug 25, 2018 12:13 am
by ace1400
Thanks for all the replies. It sounds like using mirror Intel PCIe SSD's as the database repository is not prima facie evidence of incompetence.

Ace