Error: Join inner did not fit in memory

[Julie]

Hi guys,

When I run a CTAS statement in vsql it works ok, but when I run t he CTAS statement as part of a script I get the following error:

2013-01-23 07:49:21.968 Init Session:0x2aaad0001270 <ERROR> @v_node0002: 53200/3814: Join inner did not fit in memory
LOCATION: readInner, /scratch_a/release/vbuild/vertica/EE/Processing/Joins.cpp:3502

Anyone else have this issue and found a solution?

[JimKnicely]

Hi Julie,

You can try enabling the ENABLE_JOIN_SPILL Veritca option:

Code: Select all

dbadmin=> select set_vertica_options('EE','ENABLE_JOIN_SPILL');
                     set_vertica_options
--------------------------------------------------------------

EE Vertica Options
--------------------
ENABLE_JOIN_SPILL


(1 row)

Please read this post: viewtopic.php?f=5&t=626

[scutter]

Going back to the post that was referred to:

2012-12-17 08:57:17.924 Init Session:0x1b463660 [EE] <INFO> Query Retry action: Setting add_vertica_options('EE','ENABLE_JOIN_SPILL');

Be aware that this is telling you that vertica is automatically enabling ENABLE_JOIN_SPILL - you don't need to explicitly set it. For many joins like this, the failure for the inner to fit in memory happens very quickly, and the retry thus also happens quickly. The inner not fitting in memory is also subject to what else is happening on the system - if there is a lot of memory free, the query may succeed in fitting the inner in memory; if the system is busy, it may not. Manually enabling join spill is a way of getting consistency in performance, and forcing a query to abide by the query budget determined by the resource pool. But it's also slower than running without it.

But really the question here is what's different between the run in vsql and the run in the script. Are the tables being referred to in the CTAS statement all pre-existing tables, or are they being loaded as part of the script? Dynamically generated tables could have no stats and no row counts, and be incorrectly selected as the inner - this can be avoided by using DO_TM_TASK('analyze_row_count') .

--Sharon

[Julie]

Thanks, guys!

scruuter, I think you helped me figure this out I am truncating and re-loading the tables prior to using them to build another table via a CTAS statament. I've added a "SELECT analyze_statistics('');" command to the script just prior to the CTAS statement. The script now runs flawlessly. THANKS!

[nnani]

Hello All,

I had the same scenario.

When profiled the query the output was like this

Code: Select all

NOTICE 4788:  Statement is being profiled
HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=49539595901477151 and statement_id=8;
NOTICE 3557:  Initiator memory for query: [on pool general: 3653798 KB, minimum: 3653798 KB]
NOTICE 5077:  Total memory required by query: [111630874 KB]
ERROR 3815:  Join inner did not fit in memory [(v_temp_schema.day_light_saving_tbl_lt x v_temp_schema.recdg_evt_log_new) using day_light_saving_tbl_lt_b0 and previous join (PATH ID: 11)]

As advised, I tried enabling the join spill

However even after enabling the join spill

I am still getting the error "Join inner did not fit in memory"

I wanted to understand what does "EE" stand for in the below query

Code: Select all

select add_vertica_options('EE','ENABLE_JOIN_SPILL');

Also I tried using

Code: Select all

select show_current_vertica_options;

It says " Column "show_current_vertica_options" does not exist"

After enabling the join spill the profiling was done again which gave the following output.

Code: Select all

NOTICE 4788:  Statement is being profiled
HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=67553994410988925 and statement_id=2;
NOTICE 3557:  Initiator memory for query: [on pool general: 3653798 KB, minimum: 3653798 KB]
NOTICE 5077:  Total memory required by query: [111630874 KB]
ERROR 3815:  Join inner did not fit in memory [(v_temp_schema.day_light_saving_tbl_lt x v_temp_schema.recdg_evt_log_new) using day_light_saving_tbl_lt_b0 and previous join (PATH ID: 11)]
qa0cpaload=>

Any Clues on what might be the reason there is no change in the numbers.