Disk based join - We need to revisit how memory utilization is being detected and handled

Build tested: 1.0.7-1

This issue was identified when running a 1TB DBT3 on AWS and it is related to tickets MCOL558 and MCOL-559.

For this test, I enabled disk based join.

Here is my understanding of how disk based works.

There is a pool of memory being controlled by the TotalUMMemory parameter and it is being shared by all queries that needs this memory for hash join.

When disk based join is enabled, ExeMgr would still attempt to do hash join using the memory pool. The pro is that if there is enough memory for such operation, no disk based join is need, and therefore better performance. The con is that when memory failed to completed, disk based join will start from all over. The memory join that failed would be an over head.

As described in MCOL-559, aside from this memory pool for hash join, ExeMgr uses memory for other operations. While ExeMgr is attempting a memory join (which would later fail), it pushes memory utilization up. Eventually, total system memory could be exhausted and cause ColumnStore to self-restarted, even before the hash-join memory utilization hits the max value.

It seems to me that it would be frequent that we could run out of system memory before disk based join gets a chance to be used, unless the totalUMMemory is set very low.

I am not sure what we can or should do for this. I just want present the scenario, which I ran into during testing, for brainstorming.

Should have have multi-state parameter for disk based join?

disabled, soft-enabled (use memory join first), hard-enabled (always use disk)