Repository navigation
Improve performance of datafusion-cli when reading from remote storage #16365
Description
Is your feature request related to a problem or challenge?
While testing #16300, I (re-noticed) that datafusion-cli is really slow when reading remote files
For example,
The initial table creation takes 7.5 seconds on my pretty crappy connection:
DataFusion CLI v48.0.0
> CREATE EXTERNAL TABLE nyc_taxi_rides
STORED AS PARQUET LOCATION 's3://altinity-clickhouse-data/nyc_taxi_rides/data/tripdata_parquet/';
0 row(s) fetched.
Elapsed 7.492 seconds.However then simple queries just to get the count take 8-10 seconds 😱
> select count(*) from nyc_taxi_rides;
+------------+
| count(*) |
+------------+
| 1310903963 |
+------------+
1 row(s) fetched.
Elapsed 8.945 seconds.> select count(*) from nyc_taxi_rides;
+------------+
| count(*) |
+------------+
| 1310903963 |
+------------+
1 row(s) fetched.
Elapsed 10.456 seconds.I am almost certain this delay is due to having to read the footers of the parquet files for each query.
Note the speed is much faster when collect_statistics is on
> set datafusion.execution.collect_statistics = true;
0 row(s) fetched.
Elapsed 0.012 seconds.
> CREATE EXTERNAL TABLE nyc_taxi_rides
STORED AS PARQUET LOCATION 's3://altinity-clickhouse-data/nyc_taxi_rides/data/tripdata_parquet/';
0 row(s) fetched.
Elapsed 7.770 seconds.The first query is still slow for some reason: (5 seconds)
> select count(*) from nyc_taxi_rides;
+------------+
| count(*) |
+------------+
| 1310903963 |
+------------+
1 row(s) fetched.
Elapsed 5.114 seconds.But subsequent queries are quite fast:
> select count(*) from nyc_taxi_rides;
+------------+
| count(*) |
+------------+
| 1310903963 |
+------------+
1 row(s) fetched.
Elapsed 0.297 seconds.Describe the solution you'd like
I would like:
datafusion-clito be faster for such queriesdatafusion-clito be an easy to follow model for how to cache metadata when working with ListingTabe that others who build with DataFusion could follow
Describe alternatives you've considered
I think the obvious thing that is needed is a cache for the ParquetMetadata
I think the actual cache should be in datafusion-cli but NOT in the datafusion core crate as I think what and how to cache will be different across systems.
What I envision is:
- APIs in the ListingTable / RuntimeEnv / etc for adding caching of ParquetMetadata
- An implementation of those APIs in
datafusion-cli - Bonus Points: documentation / examples that show how to use those APIs in other system
There is some vestigal code in the cache_manager crate that I think could provide a home for such caching APIs: * https://docs.rs/datafusion/latest/datafusion/execution/cache/cache_manager/index.html
Additional context
Related issues
Activity
swaingotnochill commentedon Jun 13, 2025
Hi @alamb, I would like to take up on this. I am new to the project, but this seems like a good way to start.
alamb commentedon Jun 16, 2025
It would certainly be nice, though it might be tricky for someone without experience with query processing from remote object storage.
swaingotnochill commentedon Jun 16, 2025
I will try to study and dig more on this. It would be great if you can give starting pointers when i am stuck :)
comphead commentedon Jun 16, 2025
Thanks @swaingotnochill I would probably start with profiling. The profiling techniques can be found
https://github.com/apache/datafusion/blob/main/docs/source/library-user-guide/profiling.md#profiling-using-samply-cross-platform-profiler
swaingotnochill commentedon Jun 25, 2025
I started profiling and I can definitely see reading of parquet footers and metadata taking a chunk of time initially. Thanks @comphead
JigaoLuo commentedon Jun 26, 2025
Hi @swaingotnochill
I’m curious about how we can isolate the footer parsing time from the end-to-end Parquet reading time. Could you explain the best approach to measure this for a runtime-breakdown?
alamb commentedon Jun 26, 2025
I personally recommend looking at using
samplyto profile the run: https://datafusion.apache.org/library-user-guide/profiling.html#profile-the-benchmarkYou could potentially use some of the example datasets in https://datafusion.apache.org/user-guide/cli/datasources.html#remote-files-directories
Something like
samply record datafusion-cli -c "select count(*) from 'https://datasets.clickhouse.com/hits_compatible/athena_partitioned/hits_1.parquet'"
Note you'll have to build datafusion-cli with
--profile profilingYou should see something like this
swaingotnochill commentedon Jun 27, 2025
This seems to be the lowest level that is doing both the I/O bound(fetching footer data) and CPU bound(parsing data). However, the fetches are asynchronous. I would likely start with this here and add metrics to measure this exactly in runtime. Other is the profiling which andrew suggested which seems simpler once we setup.
alamb commentedon Jul 1, 2025
@XiangpengHao perhaps has implemented code that could be useful here: datafusion-contrib/liquid-cache#227 (comment)
Basically it shows how to implement paruqet metadata caching with just the external APIs
It would be great to make this easier
swaingotnochill commentedon Jul 2, 2025
This seems nice. I am already working on an implementation, I can maybe take some inspiration from this. Let me have a basic POC working. I will try to create a draft PR by the end of this week.
swaingotnochill commentedon Jul 5, 2025
I observed a significant performance improvement just testing on the master branch without any metadata caching as compared to before. I don't know what change caused this? Any idea.
alamb commentedon Jul 7, 2025
It is almost certainly due to https://datafusion.apache.org/library-user-guide/upgrading.html#datafusion-execution-collect-statistics-now-defaults-to-true
swaingotnochill commentedon Jul 7, 2025
@alamb You are right, the improvement is due to collect_statistics.
POC is able to reduce the original time, however it still doesn't seem to work very well with
collect_statisticswhich is I am still working on.This has most of the code from @XiangpengHao and some changes to incorporate it, but not very promising when compared to
collect_statistics=true.Here are the results:
BlakeOrth commentedon Aug 7, 2025
I've been investigating performance when using the
ListingTablewith remote storage, and sincedatafusion-cliultimately uses theListingTableI'm curious if my findings might be interesting and related to this issue. Most of my focus has been on using theListingTablewith hive partitioned data. I observed there was a very large discrepancy in the performance between hive partitioned datasets and flat partitioned datasets, even when the number of underlying objects was similar. This was actually noted in an unresolved bug from about a year ago #9654 .I put a few simple targeted timing debug prints in the code and learned that in many cases most of the time spent for remote queries is dominated by listing the files in the backing object store. Due to the current implementation around listing files for hive partitioned data this can often be doubly true for such datasets (the depth of the partitioning structure impacts the number of round-trip latency costs that are paid every query). Here are some examples:
I have a POC that, at least initially, appears to normalize the performance between hive partitioned datasets and flat partitioned datasets and would allow both partitioned and flat datasets a like to leverage the existing
ListFilesCachefor users that create aListingTablemanually (such as myself). Would there be interest in looking further at the POC or discussing additional strategies for normalizing and reducing the amount of time spent listing objects?alamb commentedon Aug 8, 2025
I am interested for sure
Similarly to the hooks for Parquet Metadata which @nuno-faria is connecting up as part of this epic
There is an old partial API for caching the results of listing here:
https://docs.rs/datafusion/latest/datafusion/execution/cache/cache_manager/struct.CacheManager.html#method.get_list_files_cache
Perhaps we can follow the same approach as in #17000 and add a default cache for listing results (maybe even using the same cache limit).
The biggest challenge I think will be to clearly articulate when the cached list is expired (so for example, the table picks up changes to the underlying system)
Is this something you might be willing to help with @BlakeOrth ?
BlakeOrth commentedon Aug 8, 2025
At the end of the day I'm going to be working on some way to get listing resulted cached, and I'd much rather make those changes here to contribute back to open source than keep it in our proprietary code. I'm happy to help out to move this forward wherever I can.
In my mind the work to normalize performance between flat and hive partitioned datasets is separate, but related, to any work that would actually cache the listing results from either of those workflows. Should discussions on approach happen here or in separate issue(s) more aligned with the work directly?
BlakeOrth commentedon Aug 11, 2025
@alamb I have some freshly minted test results from using both my POC for normalizing the access patterns of hive partitioned datasets and flat datasets when using the
ListingTableas well as enabling the existing list files cache by default, and enabling the new parquet metadata cache. Obviously getting a (very rough) concept of either of these workflows isn't the hard part, as you've already mentioned communicating to users the expected behavior of the default configuration is going to be the difficult thing. However, I think the performance here likely speaks for itself for simple queries:Current performance (copied from above):
POC performance:
Edit: oops, I realize I used a subset of the table for the two different cases in the original comment, I've edited them to be equivalent now. Not that it changed the outcome too much anyway for cached queries.
alamb commentedon Aug 12, 2025
@BlakeOrth
I think we should make a new issue. I think we can take the same approach for listing results as we took for parquet metadata caching (basically follow the path that @nuno-faria blazed):
Basically
If you are willing to potentially help with this work, I can spec it out in a ticket / epic.
Since they all use the ListingTable implementation I think the code will the same
BlakeOrth commentedon Aug 12, 2025
@alamb
This sounds great. As you've likely noted I've already started getting familiar with the code and recent PRs around this area of the code. If you can tag me on the issues once you have a chance to write them up I'm happy to continue the discussion on design/implementation specifics.
alamb commentedon Aug 14, 2025
I have been thinking about this @BlakeOrth -- and when I was trying to write up this plan, I realized it wasn't actually clear to me what was happening and thus I didn't know what to write up.
One thing I was thinking is that we could make it easier to see what datafusion-cli was doing so we could better diagnose why this command takes so long (and thus how we can make it better0
Perhaps we can take inspiration from @kosiew and #17021 and add some way to monitor what is happening I/O wise from datafusion-cli.
I will try and spec out a ticket for this
BlakeOrth commentedon Aug 14, 2025
@alamb I think additional observability tooling is almost always a positive development. That being said, I have to be completely honest with you and note that I'm ultimately an API user, not a CLI user. I've been using a hacky-instrumented CLI here to help give a common tool and example(s) of potential improvements. The CLI and my use case both leverage the
ListingTablewhich is where I'm personally interested in driving performance improvements with tables on "high latency" storage.Exposing additional metrics around where DataFusion is spending its time at the API level (and in turn through the CLI) does seem very useful to me though. I personally had to rely on a mix of production metrics for our object storage, doing off-cpu-time profiling, and the aforementioned hacked in timing instrumentation, to help me understand that listing files and collecting their object metadata was taking a non-trivial amount of time, especially in hive partitioned contexts. Better metrics should, in theory, eliminate the need for much of that toil.
I don't currently have any true insights (just some educated guesses) as to why table creation is taking so long, but I also have done very little investigation there as of now. I'm personally more interested in improving performance (more query performance than write performance currently) to existing tables and consider the table creation step to effectively be a 1-time cost. I'm happy to share or better clarify any of my current findings. I'm just not entirely sure the best avenue to do so since this is a pretty active project and the core maintainers seem busy already. I can open draft PRs for the couple of POCs I've thrown together, highlight existing areas of code I've done hacky-instrumentation around etc. if it helps further the discussion.
alamb commentedon Aug 15, 2025
I believe the most valuable think you can do is write up the usecases / file tickets
For example, the
CREATE EXTERNAL TABLE overture_maps STORED AS PARQUET LOCATION 's3://overturemaps-us-west-2/release/2025-07-23.0/'is amazing as it is public and people can reproduce the slow table creation (and thus we can actually do something about it)Perhaps you can file a ticket about "improve the performance of ListingTable creation / CREATE EXTERNAL TABLE" with that usecase? I can do it as well
Then you would have a place to connect any POCs that you have made
Likewise this bug you filed is great
ListingTableFactoryon Hive partitioned datasets #17049I think we'll be able to sort that out in short order
alamb commentedon Aug 15, 2025
datafusion-cli] Add a way to see what object store requests are made #17207BlakeOrth commentedon Aug 15, 2025
@alamb done, I've filed #17211 which describes my use case and some of my current findings, and referenced this issue as being related so we can better keep track of some of the context. I will note that the ultimate solution to that issue is likely multi-faceted and probably deserves to be split into multiple smaller units of work.
alamb commentedon Oct 17, 2025
Here is another ideae
metadata_size_hint) #18118alamb commentedon Jan 1, 2026
I am going to claim that after all the work we have done as part of
This issue is complete. We are also tracking remaining work there too, so I don't think this ticket is needed anymore