These configs should respect Spark config except maxNumBits since memory issue. CC @zhouyuan
spark.gluten.sql.columnar.backend.velox.bloomFilter.maxNumBits

 val RUNTIME_BLOOM_FILTER_MAX_NUM_BITS =
 buildConf("spark.sql.optimizer.runtime.bloomFilter.maxNumBits")
 .doc("The max number of bits to use for the runtime bloom filter")
 .version("3.3.0")
 .longConf
 .createWithDefault(67108864L)

Yes @jinchengchenghh, and also we have dedicated gluten configs as well for the above configs. But like is there any way to increase the values bloomFilter.maxNumBits beyond its default values to check its efficiency on larger scale.
Thanks -khem

Please update the value 256 to a big value https://github.com/facebookincubator/velox/blob/main/velox/common/memory/MemoryAllocator.h#L492 to relax the memory restriction and take a try.

I don't try that, but this config default value is set by the size class memory limit.

I think it will also require to update here as well to increase the hard-coded limit https://github.com/facebookincubator/velox/blob/da0d2fdba364c5e14ea98536ecae57d3d7562041/velox/core/QueryConfig.h#L1245

yes

Hi @shadowmmu Thanks for the detailed information — I see the issue now. This appears to be a hard limit in the memory allocator, and modifying line may help(https://github.com/facebookincubator/velox/blob/main/velox/common/memory/MemoryAllocator.h#L492)
Cc: @zhli1142015

To improve BHJ performance, Gluten has a WIP patch (#8931
) that should significantly improve performance for large BHJ workloads.

On the query planning side, DBX performs much better than vanilla Spark, especially on the DS benchmark. There are also several useful optimizations from the Spark community, but not been merged. Some of the cloud vendors also improved on the spark catalyst in their product to improve the planner.

For the Aggregation perf diff, could you please also the the example queries?

thanks, -yuan

Thanks @zhouyuan for you effort,
Its now all clear about the BHJ, bloom filter and Optimized query plan.

For the bloom filter part, need to study Velox code if we can increase the limit without breaking anything.
if you have anything that might help, please share it here.

For the aggregation We have observed the difference in TPCH Q18 for instance.
Here is the related query plans
Velox
image

Photon
image

Hi @shadowmmu Thanks for the detailed information — I see the issue now. This appears to be a hard limit in the memory allocator, and modifying line may help(https://github.com/facebookincubator/velox/blob/main/velox/common/memory/MemoryAllocator.h#L492) Cc: @zhli1142015

To improve BHJ performance, Gluten has a WIP patch (#8931 ) that should significantly improve performance for large BHJ workloads.

On the query planning side, DBX performs much better than vanilla Spark, especially on the DS benchmark. There are also several useful optimizations from the Spark community, but not been merged. Some of the cloud vendors also improved on the spark catalyst in their product to improve the planner.

For the Aggregation perf diff, could you please also the the example queries?

thanks, -yuan

Internally, we’ve removed this limitation and are using Spark’s default value of 64 MB for bloomFilter.maxNumBits. @shadowmmu Is 1 GB the default setting for all queries? What is the overall impact of this?

Hi @zhli1142015 , the default limit is 10MB from spark side, I increased the limit to 1GB to compare it with Databrick's photon but it wasn't effective at all, like even after filtering it was nearly returning all the rows.
Performance wise Velox is nearly 5x slower for Q17 compared to Photon with the default limit of 10 MB for bloom filter.
And on full TPCH suite its 2x slower.
Changing to 1GB hasn't effected anything.

And btw how did you increased the bloomFilter.maxNumBits?