This "non-production mode" has a bad side effect where benchmarks on laptops will be artificially fast and people will "forget" about the data loss disclaimer, just like the bad old days of MongoDB.
The article also mentions that enterprise SSD often have supercaps & can be basically free because there's enough runtime (even if power goes out) to persist any in-flight data.
So it seems like it wouldnt be an issue. But it would be running your dev & prod in two different modes.
In Postgres, synchronous_commits = off can seem like magic during benchmarks but in real-life applications the anticipated benefits can be underwhelming. Ultimately, a reliable non-volatile write cache, sized for your workload is the answer. It evens out all the peaks and valleys of flash writes and hands control back to the database engine so it can get on with its work.
> Ultimately, a reliable non-volatile write cache, sized for your workload is the answer.
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.
Radian makes ultracapacitor backed RAM cards with flash backup that appear to be NVMe devices. They do a nice job for things like Write Ahead Logs and ZFS ARC/ZIL. They offer effectively unlimited write endurance with a tradeoff of cost and capacity.
More industry experience in real world would've made it clear that they're totally useless for datacenters where there are rarely power outages with many 9's of electrical uptime measured in months or years.
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.
Write barriers aren't just for durability - they also can even out major latency spikes when bloated buffers ultimately must be flushed. Database, filesystem, RAID, device, flash management and individual chips all become saturated at some point. Managing performance at the database engine layer gives you visibility into the issue at a high level as opposed to rolling merrily along until commits start taking 2000 ms. As an example, ZFS is terrifyingly unpredictable at high loads.
Power availability isn't the only concern when it comes to taking the risk of async writes. Kernel panics will cause unflushed writes to be lost, and storage hardware can experience gray or total failures, sometimes completely suddenly.
We used a small embedded db in our desktop product only for durability and SQL semantics. After we got multiple database corruptions we switched to a small file based store written in Java with simple DB semantics.
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.
"Smaller" servers, not really much any more. With high speed networking/SSD even 64GB-12GB servers warm up really fast. Back in the disk days that could take a very long time to read from disk especially if it was random IO.
I have various generations of optane I stocked up on for my own small business and home lab use, and fully agree.
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).
It does solve the problem for now, but the parts aren't being made anymore.
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."
Even when there are enterprise SSDs that are mechanically and electrically compatible with the bays/slots in a laptop, they're often untenable due to the lack of idle power management features on enterprise SSDs (because sleeping when idle kills the latency metrics). Consider that the 480GB DC1000B is officially rated for 1.9W at idle and in practice I wasn't able to get it below 1.2W. Consumer SSDs with working power management would idle under 10mW.
I usually have to tweak my home rackmount equipment over IPMI to set the fan settings to idle when temps are low instead of 24/7/365 jet engine mode which the local university datacenter seems to prefer. I imagine running the fans full blast all the time results in slightly more reliable performance and reliability, but I don't have the luxury of a soundproof HVAC controlled room to lock everything away in, so the lower noise at lower utilization matters to me. This trade-off definitely happens all over the place in enterprise gear.
I just upgraded my main server to an https://www.supermicro.com/products/archive/motherboard/x9dr... with dual Xeon E5-2680v2s. $15 for 20c/40t and 256gb ECC from the local university surplus. Unfortunately, it's still a little too old to offer NVME. That doesn't matter too much to me, since it's connected via 6gbps SAS to several 24 bay NetApp disk shelves (also $15 ea!) full of disks. I have dealt with PCIe ASPM (https://en.wikipedia.org/wiki/Active_State_Power_Management) which similarly trades off between performance, power consumption, and latency.
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 :)
A good suggestion, but it's a 1U server with both available PCIe slots populated between the external SAS controller and a dual 10gbE CNA. I boot from SATA SSDs currently. Could upgrade to enterprise SAS SSDs, but haven't noticed a need for my current uses. The boot drive is rarely touched, as most data terminates at the NetApp disk shelves, which have been a far more reliable way to connect unholy amounts of storage to a machine than any of the consumer direct attached storage options I've tried in the past. There's a mix of SSDs and spinning rust in the disk shelves - SSDs for web server stuff and spinning rust for low cost bulk storage.
Neat! And affordable! Thanks! I will definitely look further into them. I got the 10gbE CNA from another university surplus find where a half dozen were being used as 16gb fibre channel adapters with SFP fibre modules. A little digging revealed that they could be switched to 10gbE CNA mode which is a lot more useful for my purposes. Enterprise gear is full of surprises :D
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.
You can, with caveats. You can use Clover [0] to bootstrap from a USB key (or anything else) that then hands off to an NVMe drive. Or you can mod your BIOS [1]. I have personally done this on a Supermicro 9th gen, and it works great. YMMV, especially with Dells or HPs which tend to be far more picky about everything.
Thats hardly untenable. Most computers draw a lot more watts where you wouldn't miss 1.2W imo. Even these "low watt" computers are drawing like 5-7 watts at idle and once you do anything on them its a lot more than that.
You wouldn't miss the 1.2-1.9W on a gaming laptop with a discrete GPU. But for more mainstream systems with just integrated graphics, 5W is on the high end for idle power and even Intel's latest mid-range laptop parts can get down to about 3W if you don't have Windows doing dumb shit in the background constantly.
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.
You got a source for the 3w? Is this for the cpu or measured at the wall? Seems low. I believe intel n100 can get to 7w but only after heavy tweaking of c states and only with some mobo. A lot of users more in practice from my forum searching on this cpu last year when i almost bought it. Macbook can go down to 5w but again thats idle with nothing running. I don’t think many people leave a laptop computer idling like they might their workstation.
Trusting the Thinkpad spec sheet numbers, an AMD T14s lasts >15hrs in one of the benchmarks on a 57Wh battery. That's less than 4W. Perhaps on Linux and with some tuning, it seems plausible to get to 3W?
Trusting battery estimates? I believe apple claims my m3 mac is capable of 15 hours or so. If I’m lucky I will get like 7 hours with a basic documentation webpage open and the terminal connected to ssh.
The estimates appear to match what notebookcheck[0] reports on their measurements - 4.4W idle average. With some tuning[1] and turning off unused peripherals, I expect to go below 4W idle.
My laptop (a Dell Latitude 5490, i5-8350u) with the screen backlight off and at idle goes down to 2.5w power consumption according to the battery statistics. An SSD idling at 1.9w would nearly double it!
It's also a strange comment: Enterprise DBs are usually run on enterprise-grade hardware in production. To be sure, you can test them on consumer-grade hardware, but if most folks are going to run this stuff in production using enterprise-grade SSDs, the software should be optimized for that hardware. Mechanical sympathy is an important software engineering practice.
The problem of commit latency bottlenecking global throughput seems to be a matter of treating the device as a black box that must be quiesced as a unit. If the NVMe device presented multiple namespaces to the operating system, and if other unnecessary abstractions like the filesystem are discarded, then the database can make progress on one namespace while blocked on another, even within a single host.
To a close approximation, NVMe devices that come in U.2 or U.3 form factors support multiple namespaces, and those that come in M.2 support only 1, and 4KiB LBA format support follows the same pattern with a few exceptions. But you don't have to spend an insane amount to get these features. The Micro 7x00 series costs ~$100/TB.
Generally datacenter drives like Samsung PM9A3, Micron 7xxx, etc., that are available in M.2 and U.2 have the same features between the form factors. The maximum size and performance may differ between them, but this is likely a side effect of physical size for NAND chips and power envelope.
Cool! They used my bench-fio and fio-plot tool for the 3D graph :-) [0]
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.
Slightly off-topic but CedarDB is extremely exciting. It's the commercialization of the widely cited Umbra research DBMS [0] that has been in the works for several years, which benchmarks faster than DuckDB for OLAP [1] whilst simultaneously being really strong for transactional workloads. Also discussed recently here [2].
> which benchmarks faster than DuckDB for OLAP [1]
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.
clickbench is a toy benchmark: small dataset, very specific queries.
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.
its great starting insight, but again its small dataset (100GB) which almost fits memory, and I think many details are missing (for example clickbench publishes all configs and queries, and more detailed report, so vendors can reproduce/optimize/dispute them).
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
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
Maybe slight off topic, but does anyone know what the deal is with the NVMe K/V command set spec? There was a fair bit of noise made about it at the time, but I’ve not seen any drives that support it (despite a few enquiries)
Those new command sets that require a re-write of your entire software storage stack exist solely to satisfy the hyperscalers for whom re-writing the entire software storage stack can be a worthwhile optimization effort. If you don't command enough purchasing power to already be getting custom SKUs from your drive vendors, they don't have enough incentive to add those features to the models they offer you.
The seemingly critical parallel job performance issue sounds like it can and should be handled transparently by the OS when multiple pages are involved, and should thus be an area of research if it isn't already.
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...
> The seemingly critical parallel job performance issue sounds like it can and should be handled transparently by the OS when multiple pages are involved,
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.
That sounds like it would be an issue for anything. If the kernel knows that N pages need to be paged in, it should be able to optimize that behind the scenes in whatever way is needed.
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.
A little off-topic, but since the article mentioned macOS not necessary actually doing a flush even on fsync(), I've been wondering how long it is before macOS actually writes to the drive. A non-trivial number of my writes are a compile followed by, oh yeah, quick fix, recompile. It'd be nice if it would hold off for a while. Given that I'm on a laptop, the risk of losing data is limited to the very rare times I actually run out of power or a kernel panic.
> If you want your data to be stored persistent for real, you need to issue a sync command to make the operating system block and only return control after the data has been persisted.
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.
> Instead of writing every single change to the SSD immediately, we can instead queue many such writes and persist the whole queue in one go. Instead of having one latency-sensitive round trip per commit, we now have one round trip per queue flush, let’s say every 100 commits. We just have to be careful not to tell the user that their data has been committed before the queue containing their data has been flushed.
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?
It's been a while since I was testing but sustained FUA ( Force Unit Access or something like that), writes, which are used by some DB's in stead of cache write + sync degrade consumer SSD'S to HDD levels of performance. Perhaps not as slow as HDDs themselves would degrade under the same workload, but certainly 10s of milliseconds.
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
...when they run out of SLC cache. Fortunately it looks like enterprise SLC SSDs have come back into popularity somewhat, and for those who don't want to overpay, some cheap TLC and QLC drives can be forced to run in full-SLC mode too:
Sometimes its the LEAST expected thing (that one this article does not cover).
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
79 comments
[ 3.9 ms ] story [ 279 ms ] threadSo 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.
[1] https://en.m.wikipedia.org/wiki/3D_XPoint
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.
[1]https://dl.acm.org/doi/10.14778/3598581.3598584
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.
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."
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.
https://nvmexpress.org/resource/technology-power-features/
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
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://github.com/louwrentius/fio-plot
[0] https://umbra-db.com/
[1] https://cedardb.com/blog/ode_to_postgres/
[2] https://news.ycombinator.com/item?id=40241150
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.
[1] https://benchmark.clickhouse.com/
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
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.
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.
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
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
https://news.ycombinator.com/item?id=40405578
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.
Hope that will help someone.Regards,
vermaden