Async io generally causes a CPU bump as a side-effect, when the bottleneck moves from the disk to the CPU (more usually memory bandwidth).
My experience is from a different SQL engine, but the improvement in scan rates directly translates all other operators getting more work to do per unit time.
More CPUs pegged at 100% is always a good sign of smooth flows, but optimizing further requires data layout changes from that point. Even if it is a sub-linear speedup out of concurrency scaling - more with more is still a win, assuming you can pay for more (the Dy-skew paper has an example I can quote).
One thing we've seen with DuckDB, which they also mention in their docs, is that it's possible to 'over parallelize'. Beyond some number of cores, the overall workflow measured in wall time actually slows down when you add more CPUs, because of the overhead of parallelizing, despite them running at 100%.
We observe this even if we pull the data from S3 onto the local SSD before processing,
Duckdb might not be the reason, the real reason might be AWS.
It's been two years since I worked on this, but AWS Nitro might be triggering a high CPU Steal % when all the cores are busy at the same time.
You can do taskset -c 1-191 <server-process> and get a tiny bit of breathing room there.
I almost always do this above 32 cores, since this lets me always have a nice responsive ssh connection when the rest of the CPUs are churning high.
The more cores you have, the less of a problem sparing 1 core becomes.
Do you find that with the performance of duckdb, you see large deployments of spark based workflows less often? It seems hard to justify the complexity of spark if you can use duckdb , but I’m wondering if you see end users still reach for spark for any reasons?
There are a variety of people still use Spark, but this is usually due to constraints on their platform/infrastructure, not because it performs better. For instance people on Databricks can sometimes find it hard to use DuckDB on large machines, or they're just more familiar with Spark.
For a new user who has flexibility over where to run their workloads, we'd always recommend DuckDB. This is particularly the case now that: - Splink 5 (released in September) allows you to run your workload in n chunks, meaning you can distribute it across multiple machines even on DuckDB - DuckDB 2.0 parallelises better on large machines, so throwing a single massive machine at problems is now more viable
In general, after using Spark for a long time, I think for most workloads, it's just not really needed any more. Too complex, too difficult to tune. Perhaps if you have genuinely massive data. I've written a bit more about why I advise against Spark in most instances here: https://www.robinlinacre.com/recommend_duckdb/