DuckDB's Asynchronous I/O: 3x Faster Queries on S3
Asynchronous I/O in DuckDB: Work, Thread, Work
DuckDB v2.0, due this fall, introduces asynchronous I/O for Parquet and CSV files, dramatically speeding up queries on remote storage like S3. By using a dedicated thread pool and read-ahead queue, DuckDB can saturate network bandwidth, cutting TPC-H Query 6 runtime from 8.23 to 2.84 seconds (3x faster). The feature is available now in preview builds.
It doesn't matter how fast query operators are in a database system if we can't pull in the data quickly.
- diarrhea
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).
- bburnett44
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?
- NorthSouthNorth
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
- datadrivenangel
DuckDB is trending towards becoming a query engine, specifically the fastest analytical query engine. This is very good.
- ilyagr
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?