[SUPPORT] The point query performance after clustering is lags behind Delta Lake.

[zbbkeepgoing]

Describe the problem you faced

• Our scenario

We have 700 million records in our original offline table, distributed across 10 partitions. Each partition has a different data size, ranging from 10GB to 200GB. We plan to ingest this data into a data lake and test the point query performance after applying Clustering.

• Point query scenario

The original table has a column called "vin," which will be used as a filter along with the time partition column for point queries.

• Hudi configuration

hoodie.clustering.plan.strategy.target.file.max.bytes is set to 1GB, consistent with Delta Lake's default value.
hoodie.clustering.execution.strategy.class=org.apache.hudi.client.clustering.run.strategy.SparkSortAndSizeExecutionStrategy
hoodie.clustering.plan.strategy.sort.columns=vin
hoodie.clustering.rollback.pending.replacecommit.on.conflict=true
hoodie.clustering.plan.strategy.daybased.lookback.partitions=10
hoodie.clustering.plan.partition.filter.mode=SELECTED_PARTITIONS
hoodie.clustering.plan.strategy.cluster.begin.partition=part_dt=20230614
hoodie.clustering.plan.strategy.cluster.end.partition=part_dt=20230623
hoodie.clustering.plan.strategy.max.bytes.per.group=17179869184
hoodie.clustering.plan.strategy.max.num.groups=128
hoodie.layout.optimize.enable=true
hoodie.layout.optimize.strategy=z-order

• Phenomena we observed

1. After Clustering, both Hudi and Delta Lake produce Parquet files of approximately 1GB.

2. With Clustering applied, when performing point queries, Hudi scans around 10 files in partitions with larger data, while Delta Lake typically scans only 1-2 files regardless of the partition.

3. We conducted performance tests with 10 concurrent and 1 concurrent queries. We ran hundreds of rounds of tests on both Hudi and Delta Lake, with different combinations of "vin" and time partition columns. The final conclusion was that Delta Lake performs three times better than Hudi.

After examining Hudi's List file code, we found that Hudi primarily uses column statistics (min and max values) to retrieve candidate files. Therefore, we believe that the List file logic itself is unlikely to be the cause of the performance lag. It is highly likely that the issue lies in the Clustering algorithm itself.

Can you please analyze from a professional perspective what is the reason behind this? Because it determines which data lake technology we ultimately choose.

Expected behavior

The point query performance after clustering is comparable to Delta Lake.

Environment Description

• Hudi version : 0.13.1

• Spark version : 3.3

• Hive version : 2.3.9

• Hadoop version : 2.x

• Storage (HDFS/S3/GCS..) : HDFS

• Running on Docker? (yes/no) : no, on k8s

[danny0405]

Did you enable the metadata table yet, for spark it is true, but the max min col stats by default is disabled, without these col stats, I don't think Hudi would do a data skipping with max min filtering, maybe I'm wrong, cc @jonvex for visibility.

[zbbkeepgoing]

Did you enable the metadata table yet, for spark it is true, but the max min col stats by default is disabled, without these col stats, I don't think Hudi would do a data skipping with max min filtering, maybe I'm wrong, cc @jonvex for visibility.

yeah, we activated both mdt and data-skipping at the same time.

[danny0405]

@lokeshj1703 Do you have intreast on this?

[ad1happy2go]

@zbbkeepgoing Ideally delta and hudi should ideally be scanning similar number of files if both are skipping files due to column stats. Can you confirm if hudi reading all the files under the time partition column you are using in the query. It may happen that hudi is not skipping files using col stats at all and just reading files from entire one partition.

we can also sync up on a huddle on hudi community slack in case you want to look together. Ping me (Aditya Goenka)

[zbbkeepgoing]

@zbbkeepgoing Ideally delta and hudi should ideally be scanning similar number of files if both are skipping files due to column stats. Can you confirm if hudi reading all the files under the time partition column you are using in the query. It may happen that hudi is not skipping files using col stats at all and just reading files from entire one partition.

we can also sync up on a huddle on hudi community slack in case you want to look together. Ping me (Aditya Goenka)

Thank you for your reply. Here is update on the latest test info.

Due to the small value set for "hoodie.clustering.plan.strategy.max.bytes.per.group" in our configuration, it resulted in multiple file groups being created for clustering with in each partition. As a result, the number of files scanned by Hudi is higher than Delta.

However, after modifications, currently Hudi data after clustering is approximately 30x faster than the original data, and Delta is around 50X faster. Therefore, Hudi is still significantly lagging behind Delta.

We tried running the same SQL multiple times in Hudi and Delta, and found that Hudi is approximately 1.4-1.6s slower than Delta. Moreover, in the DAG, we observed that Hudi's metadata time is around 1.7s, while Delta's metadata time is 0s, Of course, Delta has a separate job to scan the Delta log, which takes approximately 0.3 seconds. I hope this information can be helpful to your guys.

• Hudi

• Delta

[ad1happy2go]

Thanks a lot for the contribution. @zbbkeepgoing. Now the performance numbers are around as par with the benchmarks also.

Good news is Hudi 0.14.0 will introduce Record Level Index which will improve the performance of point queries a lot.

[ad1happy2go]

@zbbkeepgoing Closing this out. Please reopen in case of any more questions. Thanks.