Join inner did not fit in memory

Gil_Peretz

Hello, Configuration: Vertica 4 machines Cluster Linux redhat 5.9, 16cpu's x 16GB Tomcat linux - apps server 2x2 setup: set_vertica_options('EE','ENABLE_JOIN_SPILL'); SET_CONFIG_PARAMETER('MergeJoinInnerInitialMB',64); SET_CONFIG_PARAMETER('MinSortMergeJoinMB',512); -- SET_CONFIG_PARAMETER('GlobalSessionProfiling',1); SET_CONFIG_PARAMETER('GlobalQueryProfiling',1); SET_CONFIG_PARAMETER('GlobalEEProfiling',1); I am running a test where 10 tenants (each tenant represent schema) are simultaneously running ETL's that include 20 steps. Also, the ETL's are running in their own resource POOL: EXECUTIONPARALLELISM AUTO PLANNEDCONCURRENCY 12 MAXCONCURRENCY 50 MEMORYSIZE '2G' MAXMEMORYSIZE '8G' priority 65 RUNTIMEPRIORITY HIGH Lots of the ETL queries are failing with: error code 8389, Join inner did not fit in memory... here an example: Join inner did not fit in memory [(tnt_0004_stg.SM_PROM_LOCATION_ext_src_key x tnt_0004_stg.SM_PROM_LOCATION_ext_src_key) using SM_PROM_LOCATION_ext_src_key_super and subquery (PATH ID: 3)] can you please advice.

Sharon_Cutter

If you have a high-concurrency use case with joins, see if you can optimize those hash joins to be merge joins, which will remove the need for the inner relation to fit in memory. Note that the Hardware Planning Guide recommends 4-8GB per core and you are well under that with 16 cores and 16GB of memory. https://my.vertica.com/docs/6.1.x/HardwareDocs/HP_Vertica%20Hardware%20Planning%20Guide.pdf

Navin_C

Hi, This might be due to memory spill during joins. Check the explain plan of queries and determine which join are planned for the query. If you need to change the hash join to merge join try sorting the inner join key values on both the tables. As Sharon said, The hardware does really seems to be a not recommended one for Vertica. Hope this helps.

Gil_Peretz

Thank You, I'm in the process of changing it to Merge Join...I'll keep you posted.