Does having the worker pool hold as many threads as cores work well alongside the async pool? It is basically oversubscribed by design.
I built a system once which had (this is Rust) a Rayon worker thread pool of 4 threads and a Tokio async pool of 2 (multithreaded runtime). On a system of 6 vCPU. This ended up working fine. Tokio was not starved so handled network requests at low latency.
One difference is DuckDB is a pure network client. If one of its async threads is starved it is not the end of the world (e.g. k8s does not kill your pod for failure of replying to health checks).
I've run quite a few benchmarks on that as well, on a few different machines, and oversubscribing ASYNC threads demonstrated very little performance downside. In the end, the memory governor also keeps these threads "in check" while still allowing full utilization when possible.
There is still something to gain from tuning it further (as you can see in the async I/O tuned benchmark), but having that network saturation by default is still a work in progress.
Network IO is heavily NIC queue bound, if your NIC only has one queue it just makes it slower to do any threading workload against it.
On my ryzen 9 it needs around 8 cores to do the same work in a threaded io loop than you can do single threaded. And the mechanism doesnt matter, you could share an fd, use SO_REUSEPORT or just share memory between threads.
Just doing the sharing makes everything extremely slow. One context switch becomes more expensive than just doing it single threaded.
As long as you're scheduled by the kernel and not something like Kubernetes with a CPU limit, you can generally oversubscribe I/O threads without much of a problem. They're mostly parked waiting for syscalls anyway. Heck, even if they're mostly doing CPU work, the scheduler generally deals with it pretty gracefully.
Using 512gb of ram for a 22gb remote file does feel a bit weird for a benchmark but maybe they couldn’t get a large number of cores without lots of memory?
The main reason I decided to use a beefier machine is that it gives me flexibility when benchmarking, without the need to set up different environments. The CSV data, for example, is >80 GB. We can also “scale down the machine” for experiments where we want to stress-test lower-memory scenarios or use fewer threads by configuring DuckDB’s settings (e.g., SET memory_limit = '10GB'; or SET threads TO 1;).
Note that SET memory_limit is a soft limit and can be completely ignored for some tasks, so this wouldn’t be the same as having a limit on physical memory.
Most cloud providers start with a 2:1 ratio of memory in GiB to CPU cores and go up from there. Databases also are the most common workload for large-memory systems because they benefit so much from large buffer caches.
Do the CSV files allow quoted newlines? If yes, what's the trick to avoid checking the whole file too find out whether a newline is quoted or a record separator when reading it from the middle in an async thread?
DuckDB uses a speculative parallel CSV parsing technique. The basic idea is that the parser speculates about the state the CSV parser is in at a random byte (e.g., whether it is inside a quoted field) and tries to figure out where the next row starts based on that.There are validation steps during finalization as well, to ensure the parser did not got anything wrong in its speculation.
In general, yes. But Hive, for example, doesn't support embedded newlines or record separators that are not newlines, which means that a lot of older CSV files containing big data do not allow quoted newlines.
Does having the worker pool hold as many threads as cores work well alongside the async pool? It is basically oversubscribed by design.
I built a system once which had (this is Rust) a Rayon worker thread pool of 4 threads and a Tokio async pool of 2 (multithreaded runtime). On a system of 6 vCPU. This ended up working fine. Tokio was not starved so handled network requests at low latency.
One difference is DuckDB is a pure network client. If one of its async threads is starved it is not the end of the world (e.g. k8s does not kill your pod for failure of replying to health checks).
I've run quite a few benchmarks on that as well, on a few different machines, and oversubscribing ASYNC threads demonstrated very little performance downside. In the end, the memory governor also keeps these threads "in check" while still allowing full utilization when possible.
There is still something to gain from tuning it further (as you can see in the async I/O tuned benchmark), but having that network saturation by default is still a work in progress.
(Disclaimer: I'm the author of the blogpost)
Network IO is heavily NIC queue bound, if your NIC only has one queue it just makes it slower to do any threading workload against it.
On my ryzen 9 it needs around 8 cores to do the same work in a threaded io loop than you can do single threaded. And the mechanism doesnt matter, you could share an fd, use SO_REUSEPORT or just share memory between threads.
Just doing the sharing makes everything extremely slow. One context switch becomes more expensive than just doing it single threaded.
As long as you're scheduled by the kernel and not something like Kubernetes with a CPU limit, you can generally oversubscribe I/O threads without much of a problem. They're mostly parked waiting for syscalls anyway. Heck, even if they're mostly doing CPU work, the scheduler generally deals with it pretty gracefully.
Tested on a dev env (t3.nano) with a rather challenging query (500 rather evenly distributed items) on a file with ~70m rows w/ about 576 row groups.
version 1.4.4: 31.89s
preview: 4.42s
Using 512gb of ram for a 22gb remote file does feel a bit weird for a benchmark but maybe they couldn’t get a large number of cores without lots of memory?
The main reason I decided to use a beefier machine is that it gives me flexibility when benchmarking, without the need to set up different environments. The CSV data, for example, is >80 GB. We can also “scale down the machine” for experiments where we want to stress-test lower-memory scenarios or use fewer threads by configuring DuckDB’s settings (e.g., SET memory_limit = '10GB'; or SET threads TO 1;).
(Disclaimer: I’m the author of the blog post.)
Note that SET memory_limit is a soft limit and can be completely ignored for some tasks, so this wouldn’t be the same as having a limit on physical memory.
In past experience with DuckDB I have found this soft-limit distinction to be very important.
Most cloud providers start with a 2:1 ratio of memory in GiB to CPU cores and go up from there. Databases also are the most common workload for large-memory systems because they benefit so much from large buffer caches.
Even so you could use cgroups to cap the memory for the process (including its caches).
DuckDB is trending towards becoming a query engine, specifically the fastest analytical query engine. This is very good.
What is it missing to be called a query engine?
Should also mention that it is of the most convenient!
far from the fastest - our work on Presto on GPUs would like a word ;-)
Do the CSV files allow quoted newlines? If yes, what's the trick to avoid checking the whole file too find out whether a newline is quoted or a record separator when reading it from the middle in an async thread?
DuckDB uses a speculative parallel CSV parsing technique. The basic idea is that the parser speculates about the state the CSV parser is in at a random byte (e.g., whether it is inside a quoted field) and tries to figure out where the next row starts based on that.There are validation steps during finalization as well, to ensure the parser did not got anything wrong in its speculation.
I've never gotten around to writing a blog post about it, but I go quite in-depth on the technique in this presentation: https://www.youtube.com/watch?v=YrqSp8m7fmk
(Disclaimer: I'm the author of the blog post and also the developer who implemented the entire CSV parser in DuckDB.)
Burning my points to say dude that's sick, parsing CSVs is hell I am genuinely going to watch this thank you.
there isn't any upvote limit on HN.
You’re officially my hero, the duckdb csv parser is the fastest I could find and helped save a data project with hundreds of gigabytes of csv pain.
Thank you so much. I love DuckDB!
In general, yes. But Hive, for example, doesn't support embedded newlines or record separators that are not newlines, which means that a lot of older CSV files containing big data do not allow quoted newlines.
most likely - they read large chunks and decode them
I wonder how this would work in trying to parallelize the worker threads (multiple duckdb instances) coordinating them via Quack.
Ducks all the way down!
Do they have SSL updates yet? Signing is great, but using https means not fighting firewalls to start a job
This is such a long waited feature!
is there any aggregator for official docs such as this for db/systems/distributed arch at scale?