Hash join error


#1

Run queries from TPC-H in MapD-JDBC to measure elapsed time.
1G and 10G data worked well on 3,5,10,12 queries.
However, for 100G only, the following error occurred:

java.sql.SQLException: Query failed : Exception: Hash join failed, reason(s): Could not build a 1-to-1 correspondence for columns involved in equijoin | SlabTooBig | No equijoin expression found | No equijoin expression found
at com.mapd.jdbc.MapDStatement.executeQuery(MapDStatement.java:70)
at SampleJDBC.main(SampleJDBC.java:55)

The error line was the code that executed the query.

How can I prevent Hash Join error on large amounts of data?
We’ll also measure 1TB of data in the future.


#2

Hi @Hyuck,

Currently, we limit a single GPU memory allocation to 2GB. The error message you are seeing is a result of the hash table for one column pair in the join exceeding 2GB. You can typically get around this limitation by pushing selective filters down into the join, reducing the size of the input columns to the join. Filter push down is is supported, but it is off by default. If you start the mapd_server binary with --enable-filter-push-down you should be able to run the above query.

Note that we currently have to do some conversion steps after pushing down the filter before we can read the result into the hash join runtime. We are actively working to remove those conversion steps and produce a compact output from the filter on GPU that we can forward directly into the hash join runtime. That work is on track and should be released within the next few months.

If you have any problems with filter pushdown let us know – we have run these queries internally and I can retrieve the tuning parameters we used for you.

Thanks,
Alex


#3

@alexb i checked /home/mapd/data/mapd_log/mapd_server.INFO

–enable-filter-push-down is enabled already. but error is occured.
Can I extend cpuSlabSize?
my CPU: i7-8700K
GPU : GTX1080TI
RAM : 32G


#4

Hi @Hyuck,

now you can’t change the size of CPU o GPU’s slab size without recompiling the mapd database; although to run this query in CPU mode, you don’t need to do, because it runs flawlessly ( I’m assuming you are using an integer data type for the keys).

given the current limitation of GPU’s slab size, you’d need at least 2 graphics cards to run such high cardinality join, that, given the hardware you are currently using, would still required for the group by and calculations the Q3 of TCP-H, because you are going to run out of GPU memory (16GB+ assuming you are using decimal datatype for prices, discounts and so on).

Said that, I suggest you run the queries in CPU mode using the “\CPU” command of mapdql or limit the data to a TPC-H 80G
. You can decrease the memory needed converting all decimals datatype to integer (you have to mulitply them by 100, or remove the punctuation) and change the queries accordingly, or adding hardware.

@alexb

As @Hyuck already stated out, the enable_filter_push_down apparently isn’t working; I tried on several scenarios, and turning it on/off isn’t changing anything on explain/explain calcite. I saw there are other parameters related to push-down but they look like thresholds, so I didn’t touch them.