poor performance on duckdb s3 parquet reading

[bugzpodder]

I've been doing some benchmarks of reading large s3 parquet files from duckdb on AWS EC2 (r7iz.4xlarge [128GiB mem], r7iz.metal-32xl [1TiB mem]) using a couple of different approaches:

1. SELECT <columns> FROM 's3://..../*.parquet'
2. TheadPoolExecutor + SELECT <columns> FROM 's3://..../<n>.parquet'
3. TheadPoolExecutor + py_arrow.parquet.read_table(s3fs.S3FileSystem().open("s3://..../<n>.parquet", columns=columns))

for 2b rows:
Approach 2 > Approach 3 > Approach 1
eg Approach 2 = 1.5min, Approach 3 = 2.5min, Approach 1 = 15min on 128Gib memory

for 100m rows + 250 files:
Approach 3 > Approach 2 > Approach 1
eg Approach 3 = 15s, Approach2 = 35s, Approach 1 > 2 min

for 100m rows + 50 files:
Approach 3 ~= Approach 2 > Approach 1
eg Approach 3 = 10s, Approach2 = 13s, Approach 1 > 2 min

In general, more parquet files = slower. more system memory = faster. selecting specific columns will take less time for all three approaches but the order of performance timing doesn't change.
But the common theme here is duckdb's native parquet reading from s3 is excruciatingly slow (up to 10x).

[sambcom]

What happens when you change #1 to avoid having DuckDB make the ListObjectV2 API calls? I noticed that can be pretty slow with a lot of files.

SELECT *
FROM read_parquet([
    's3://bucket/file1.parquet',
    's3://bucket/file2.parquet'
]);

instead of:

SELECT * FROM read_parquet('s3://bucket/file*.parquet');

Great timing for your post. I'm also benchmarking various ways to gather a bunch of S3 parquet files in EC2 and found that eliminating predicate pushdown in favor of reading the entire file was ~25% faster. Look at all these roundtrips:

200 MB file.

1. select * from parquet WHERE ...: 33s to read 108.5 MB with 1 HEAD and 474 GETs
2. select * from parquet: 25s to read all 200 MB.

#4 could be dask's read_parquet with these tips?

• https://www.coiled.io/blog/pyarrow-strings-in-dask-dataframes
• Read_parquet is slower than expected with S3 dask/dask#9619

dask.config.set({"dataframe.convert-string": True})
df = dd.read_parquet(
    "'s3://..../*.parquet'",
    columns=columns
)

[Arjun9088]

I would like to know how you are getting the number of s3 calls being made. Is it via cloudtrail, or does duckdb have some built in logging mechanism?

[sambcom]

with explain analyze

explain analyze select * from 's3://.....';
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││         HTTPFS HTTP Stats         ││
││                                   ││
││           in: 753.6 MiB           ││
││            out: 0 bytes           ││
││             #HEAD: 244            ││
││             #GET: 974             ││
││              #PUT: 0              ││
││              #POST: 0             ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘

[kyle-cheung]

Did you ever figure this out? On our end we're noticing that DuckDB doesn't full saturate the network bandwidth. It's coming in at about 1/3 of that the machine is capable of transferring.

[danielsnider-aws]

+1

When using DuckDB-wasm to read a single .parque file, I don't see parallel reads to S3. I only see serial reads which makes performance slow. Is this expected?

[dentiny]

Hi, we wrote a duckdb extension called cache_httpfs, which does a few things you guys mentioned above:

• Parallel read for large http requests, with request size tunable; it could largely increase IO throughput and decrease latency
• Add cache for GLOB/LIST operations, so a second invocation wouldn't take any IO
• Both metadata and actual data files are cache either in-memory or on-disk, with size tunable, lifecycle-controllable and stats exposed (i.e. IO latency distribution, cache hit rate, etc)
• You could disable the extension and fallback to httpfs extension any time
• Other cool features

The only thing you need to do is

FORCE INSTALL cache_httpfs FROM community;
LOAD cache_httpfs;

I would appreciate if you could have a try!
Let me know if you have any concerns or features requests! 🤓

[danielsnider-aws]

Thanks! I saw your extension but when I went to https://shell.duckdb.org/ (or in my own app) and I tried:

FORCE INSTALL cache_httpfs FROM community;
LOAD cache_httpfs;

Then I got:

Access Denied 403  https://community-extensions.duckdb.org/v1.2.2/wasm_eh/cache_httpfs.duckdb_extension.wasm

[dentiny]

Ah that's because we currently doesn't support wasm.
There's ongoing issue and request for wasm: dentiny/duck-read-cache-fs#159
We will spend some time on it, thanks for the patience!

[danielsnider-aws]

Btw, a user on Discord said

Extensions won't be able to parallelize reads, the host env wasm needs JS Promise integration. See https://v8.dev/blog/jspi. Or maybe duckdb itself needs to support async operations, I'm not sure

https://discord.com/channels/909674491309850675/921100929984503858/threads/1367220668856537159

[dentiny]

Thanks for the pointer!
The reason why we didn't spend time on wasm is we're not familiar with js and wasm, might need some time to investigate and experiment.
Will keep you updated! Thank you again!