Asynchronous I/O in DuckDB: Work, Thread, Work

Posted by pdet 5 days ago

Asynchronous I/O in DuckDB: Work, Thread, Work(duckdb.org)

147 points | 14 comments

diarrhea 1 hour ago|

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).

pdet 10 minutes ago||

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)

marginalia_nu 21 minutes ago||

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.

bburnett44 4 hours ago||

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?

pdet 16 minutes ago||

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.)

otterley 4 hours ago|||

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.

marginalia_nu 28 minutes ago||

Even so you could use cgroups to cap the memory for the process (including its caches).

myshapeprotocol 7 hours ago||

Deep dive into asynchronous I/O architectures like this is pure engineering gold for high-performance data processing. Excellent breakdown.

pjot 4 hours ago||

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!

datadrivenangel 5 hours ago||

DuckDB is trending towards becoming a query engine, specifically the fastest analytical query engine. This is very good.

abofh 4 hours ago||

Do they have SSL updates yet? Signing is great, but using https means not fighting firewalls to start a job

singhutsav5501 1 hour ago||

is there any aggregator for official docs such as this for db/systems/distributed arch at scale?

mansi1010 6 hours ago||

This is such a long waited feature!

myshapeprotocol 3 hours ago|

Brilliant deep dive into asynchronous I/O and execution thread models. Essential reading for high-performance data engineering.