When are SSDs slow?
cedardb.com
cedardb.com
that link doesn't do any performance comparison. They claim that CedarDB executes less "code branches" than duckdb, which may or may not translate to faster performance.
> less "code branches" than duckdb, which may or may not translate to faster performance.
In that case it was about 2.5x faster than DuckDB end to end, so a bit less than the difference in branches.
If you want to see some independent benchmarks on Umbra, our underlying technology, its currently first place on Clickbench [1]. You can compare against duckdb there as well.
Benchmarking full tcp-h (not just one query like in your post) on sizable dataset (few TBs) would be very good close to real world scenario, but vendors usually avoid this.
What counts as large or small definitely varies a lot depending on the context of the conversation/analysis.
MotherDuck's "Big Data is Dead" post [0] sticks in mind:
> The general feedback we got talking to folks in the industry was that 100 GB was the right order of magnitude for a data warehouse. This is where we focused a lot of our efforts in benchmarking.
Another point of reference is [1]
> [...] Umbra achieves unprecedentedly low query latencies. On small data sets, it is even faster than interpreter engines like DuckDB
> TPC-H Small Dataset = 866k tuples, sf 0.1
[0] https://motherduck.com/blog/big-data-is-dead/
[1] https://db.in.tum.de/~kersten/Tidy%20Tuples%20and%20Flying%2...
and then user discovers that DuckDB is plagued with OOMs and dramatic performance degradations when his data is slightly larger than memory.
It's neat seeing postgres gearing up for async support. There's also folks like OrioleDB doing massive revamps of postgres & doing disaggregated storage in public. https://github.com/orioledb/orioledb https://hn.algolia.com/?query=orioledb&sort=byDate
Oh they were bought by Suprabase two months ago... Fingers crossed! The Suprabase CEO commented at the time, with a nice basic overview, https://news.ycombinator.com/item?id=40039138
So it seems like it wouldnt be an issue. But it would be running your dev & prod in two different modes.
Author here, I agree! It's quite sad that we need such an involved solution to offset the inherent complexity of the flash medium (latency spikes, erase blocks, ...). We nearly had the perfect solution with Optane[1]: 100ns latency, instantly persisted writes and all that good stuff.
I'm still not over Intel killing it while I did my PhD on it.
UPSes and BBWC evolved to bring reliability to production gear running in non-DC environments when mainline servers used spinning rust without backup power. Today, it's largely a vendor up-charge.
Write barriers cause far too much latency in practice on servers in tier IV datacenters, so they're almost always turned off except for a tiny fraction of systems.
There has never been a "perfect" or a universal solution, only a risk budget and suitability for a specific use-case.
"What Modern NVMe Storage Can Do, and How to Exploit it"[1] by Gabriel Haas and Viktor Leis.
Some time ago I had some really cheap SATA 2.5 SSD 256 GB in my laptop.
If generally worked well - system (FreeBSD) was responsive but ... when I deleted large file (like 1GB+ size) then the system almost FROZE ... until file is deleted.
After some investigation with Allan Jude (thanks again) we came to the conclusion that a single TRIM operation (for delete) take about 500ms (half a second) ...
By default ZFS on FreeBSD uses up to 64 TRIM operations PER SECOND (the vfs.zfs.vdev.trim_max_active setting) - so the SSD was literally DoS with TRIM operation and it crippled it to its knees.
After switching this option from 64 to 1 it solved all the problems and everything was smooth and fast again.
Details below.
% grep -A 1 -i trim /etc/sysctl.conf
# ZFS DELETE FUCKUP TRIM
# vfs.zfs.vdev.trim_max_active: 64 -> 1
vfs.zfs.vdev.trim_max_active=1
Regards,
vermaden
What the 3D graph (also) shows is how poorly SSDs actually perform in single-threaded, low queue depth situations. Many enterprise or datacenter grade SSDs often perform much better and consistently on this front than the average consumer SSD.
0. https://web.archive.org/web/20111112045055/http://buyafuckin...
When designing CedarDB, we recently had multiple instances where we thought: "If we had just a few KiB of Optane here ... "
It's just approximately the Right Thing to use for some things, and I had room for it, and it was rather inexpensive when I bought it. :)
10/10, would recommend and buy again. (Whoops!)
I don’t know if you’re aware but Samsung introduced their own version of optane just shortly before Intel killed their product line. It was called Samsung Z and it was prohibitively expensive for some reason, but I didn’t hear that it was officially killed off (though it likely has been).
https://www.avalanche-technology.com/wp-content/uploads/1Gb-... https://www.everspin.com/spin-transfer-torque-ddr-products
Are you taking the durability into account when you say kilobytes?
Agreed in general, but surely for this usage case (i.e DB) it would still solve the problem, no?
Or am i missing something here
Maybe that's good enough for some people in some applications, but a lot of folks have very valid reservations about using EOL'd products regardless of how well-suited they may be at solving a problem.
I mean: It's like work trucks. I'm a huge fan of GM's Astro/Safari line of RWD cargo vans. Their 4.3l V6 is solid and adequately powerful, the 4L60E transmission is reliable, there's a ton of space in the back with enough height to load 4' tall sheet materials that are standing on edge, and to sit down in front of a bolted-down workbench, and the longitudinal RWD layout has advantages, they're even fairly easy to work on when something inevitably wears out, the deck height is low, and they're small enough to drive through a fairly normal parking garage without worry and to fit into a tight space.
They're pretty awesome (except maybe for safety and fuel economy, but perhaps those aren't my primary concerns), and they solve a particular set of problems very well.
But they aren't being made anymore, and haven't been made for over 20 years. I own one example of a GMC Safari that I'd like to get working well again some day and maybe kit out in some interesting way, but I'm absolutely not going to suggest that anyone buy a fleet of them to use in business because:
They, like Optane, are EOL'd. All of them that will ever be produced have already been made.
---
"So can you explain to me again why you think we should be buying used, old parts for our new datacenter? I know you've told me before, but still not sure that I have the same perspective as you do."
Unfortunately, my laptop, and probably many other developers’ laptops, do not have
an enterprise-grade SSD, or even the option to install one.
Enterprise M.2 SSDs are also a thing (Kingston DC1000B).
If they're using a mac and are limited to external connectivity, then they could throw the above into a thunderbolt enclosure.
I am not hugely concerned with power consumption as I have solar panels. But noise and excess heat matter to me. My whole stack is consuming around 650W currently. Wildly removed from the days I was running off a creatively overprovisioned OpenWRT wireless router using < 20W. But I'm happy to have the increased capacity for my use cases. Gigabit synchronous fiber to the home is a dangerous drug :)
You wouldn't be able to boot from it, but it would work just fine.
Interestingly, there do appear to be PCIe cards that have both external SAS and high speed networking in one:
https://forums.servethehome.com/index.php?threads/unique-oem...
Looks like a bunch of mucking around is needed to get things setup, but seems like it works ok from that point onwards:
• https://www.ebay.com/itm/314150370285
Have been considering grabbing one to try in a HP Microserver Gen 8 that runs Proxmox. Not a lot of free time at the moment though. ;)
Only potential downside I see is the included pcie switch chip. It's dividing up 8gbps between three devices which could independently consume all of that bandwidth. Probably not a huge issue for my uses, but worth considering if you plan on transferring large amounts of data over network and storage simultaneously.
[0]: https://github.com/CloverHackyColor/CloverBootloader
[1]: https://winraid.level1techs.com/t/howto-get-full-nvme-suppor...
Also, on modern laptops you can't trust that components will be powered off when you close the lid, because Microsoft and Intel have been at war with ACPI S3 suspend for years now. So that 1.2W could easily be the minimum power draw of the SSD any time you haven't hibernated or shut down the system.
https://www.tomsguide.com/news/macbook-pro-m3-and-m3-max-bat...
Maybe yours is a lemon and/or should be replaced?
[0] https://www.notebookcheck.net/Lenovo-ThinkPad-T14s-G3-AMD-la... [1] https://docs.kernel.org/admin-guide/pm/amd-pstate.html
A 20% premium for an enterprise SSD? No. A 120% premium is closer to it.
ref: https://www.newegg.com/Enterprise-SSDs/SubCategory/ID-2021
Well on linux the work that has to be done during a blocking sync() can be minimized by triggering async writeback via sync_file_range in advance.
I'm a little bit unclear, is the suggested method here issuing 100 `write` commands and then a `sync` (or something similar) and only informing the downstream of success when the `sync` is finish? Or is the DB actually maintaining a queue of commits to be written that get fsynced all at once?
SSD Controller: "Okay, go for it."
CPU: <Intense Writing Noises />
SSD Controller: "(Hey OS, can you dump what the CPU's throwing at me in your RAM instead? Don't tell him.)"
OS: "Okay."
<100MBs later />
CPU: "All done! You got it, SSD?"
SSD Controller: "Uhh, yeah, one sec."
SSD Controller: "(Hey OS, throw me all that data before the CPU gets wind of this.)"
OS: "Okay."
OS: <Intense Writing Noise />
SSD Controller: "Alright, come on finish writing ya damn NAND."
NAND: "Hold on, man."
CPU: "Sitrep, SSD?"
SSD Controller: "Almost done!"
SSD Controller: "All done!"
CPU: "Great!"
---
Long story short, big bursty writes are faster than small intermittent writes and SSD controllers are bloody liars.
Looking carefully at what happens when data is lost we found that it's basically all recoverable.
This lead to an amazing performance, as data is flushed only when necessary. We flush only every 10 seconds now or when the write through cache is full.
My lesson learned from that is to carefully think about the actual requirements when it comes to transactions and having loose constraints may be the quickest way to speed up performance.
For many applications even a second or two of lost data may not be a big issue.
Slower than comparable HDD? Slower enough that it impacts performance visibly for the same load?
So yes, visible, extremely so. On drives commonly known as excellent consumer drives and that I myself happily use on a general desktop/gaming PC.
From the limited testing available from my personal budget it would suggest that newer drives are still impacted, although not as badly, and larger drives are ( perhaps obviously ) impacted much less than than smaller ones ( 2tb vs 256gb ). I've seen 2 level drops too.. first when cache/slc writes are overrun, and then a second, when I think the drive is forced into some kind of garbage collection/reorg at the same time.
But all that stuff is very controller specific so it's a little hard to generalize.
But equally, I now buy old enterprise drives, because they work so much better under these workloads at equivalent sizes, even if consumers drives would appear faster according to spec sheets or "drive friendly" benchmarks. I personally use old intel ( 37nn and 46nn series ) but that's mostly just what I know, there are similarly performing models from most enterprise ssd vendors
Frankly, the idea that it isn't already a solved problem makes me wonder if that could possibly be the case...
The writes thing... Outside of very rare circumstances, I would think that properly batching inserts/updates within transactions at the app level would resolve half of that, and multi-user concurrency would buy back the rest.
What am I missing?
P.S. I'm mad at the author for enlightening me to the real-world performance impact of enterprise SSD. i'm about to buy, and that might just cost me...
A thread can only fault on one page at a time, and only sequential access patterns can benefit from the OS bringing in multiple pages per fault. Having all of your CPU cores busy handling page faults and context switches to threads that are about to trigger another page fault is only going to generate enough read traffic to keep a few SSDs busy, not a full shelf of 24+ drives. mmap() and synchronous IO don't scale well enough for today's SSDs.
I'll agree that mmap() would probably have issues with prediction there (other than basic readahead), but I would think that issuing a simple bulk read() call (or more likely an equivalent zero copy call; vread() if memory serves? Been too long) would give the kernel enough information to optimize bulk transfers.
Small async reads will always be slow if done serially, but there's already "standard" scatter/gather calls that should let the kernel optimize that as well.
In short, all but a truly naive application should be limited by the SSD or PCIe bandwidth before they ever hit issues like that if the kernel is optimizing correctly, and assuming that the application can predict its own needs.