> Not just reading data from S3, but the actual computations too.

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

▲

RobinL a day ago | parent | next [-]

Yep that makes sense.

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,

▲

gh02t a day ago | parent | next [-]

This is also a problem in classical HPC, there aren't very many algorithms that can scale linearly with cores. That's partially why so many HPC algorithms tend to be built on matrix multiplication and Monte Carlo, because those do tend to scale near arbitrarily.

▲

gopalv a day ago | parent | prev [-]

> because of the overhead of parallelizing, despite them running at 100%

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.

	▲	RobinL 14 hours ago \| parent [-]
		Will try this thanks!

▲

elendilm 9 hours ago | parent | prev [-]

> More CPUs pegged at 100% is always a good sign of smooth flows, but optimizing further requires data layout changes from that point.

True. This is true by experience. I want to add that, additionally this is not just true based on experience, rather it is fairly a straightforward logic.

If the cpu is already saturated at 100% doing work in the most "efficient" way possible for a given data structure (some sort of a shannon limit for the given data structure), then there is zero further improvement possible.

Any additional improvement must therefore happen either on the thing that the CPUs operate on (data structure improvements) or on the hardware themselves.