ERROR 3587: Insufficient resources to execute plan on pool

Gil_Peretz

While executing MERGE (that include 660 COLUMNS) I am getting the above error. (Insufficient resources to execute plan on pool)
I am using 4 Cores 8GB none cluster machine, redhat 6.3
Also,
I have created a pool named ETL_BATCH and assign user to work with this pool:
MEMORYSIZE= 1G
MAXMEMORYSIZE =6.5G
PLANNED_CONCURRENCY=2
MAX_CONCURENCY=150
EXECUTING_PARALLELISM =AUTO
PRIORITY=40
RUNTIME_PRIORITY=2

The error that i got:
ERROR 3587:  Insufficient resources to execute plan on pool batch_etl [Request Too Large:Memory(KB) Exceeded: Requested = 7019241, Free = 6520059 (Limit = 6520059, Used = 0)]

can you advice please?

Daniel_Leybovic

Increase your memory or modify your query that will use in merge join and not in hash join (if hash join is used)
You can validate what in use - HASH  or MERGE join by executing query EXPLAIN.
http://vertica-forums.com/viewtopic.php?f=3&t=156&p=285

Requested = 7019241    => 7.7GB
Free = 6520059              => 6.2GB (MAX by resource pool 6.5GB)

So query require more memory than you provide.

Gil_Peretz

Thank You Daniel, I'll try the MERGE JOIN first.

O.K, I have tried building the correct projections (using order by) and now the explain plan actually showed that it using MERGE.
however, i am still facing same error, even worst now, it demand 10G of ram:

ERROR 3587:  Insufficient resources to execute plan on pool batch_etl [Request Too Large:Memory(KB) Exceeded: Requested = 9812391, Free = 6520059 (Limit = 6520059, Used = 0)]

here how the MERGE explain plan looks like:
+-DML MERGE [Cost: 0, Rows: 0]|  Target Projection: hpswadmmockTenanttrg.OPPORTUNITY_FACT_p1|  Target Projection: hpswadmmockTenanttrg.OPPORTUNITY_FACT_p1_b1|  Target Projection: hpswadmmockTenanttrg.OPPORTUNITY_FACT_p1_b0|  Target Prep:| +---> JOIN MERGEJOIN(inputs presorted) [RightOuter] [Cost: 749K, Rows: 12K (NO STATISTICS)] (PATH ID: 1)| |      Join Cond: (OPPORTUNITY_FACT.MD_OPPORTUNITY_GL_PK = VAL(2)) AND (OPPORTUNITY_FACT.MD_START_DATE = VAL(2)) AND (OPPORTUNITY_FACT.MD_CP_ID = VAL(2))| | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 467K, Rows: 10K (NO STATISTICS)] (PATH ID: 2)| | |      Projection: hpswadmmockTenanttrg.OPPORTUNITY_FACT_p1
| | |      Runtime Filters: (SIP1(MergeJoin): OPPORTUNITY_FACT.MD_OPPORTUNITY_GL_PK), (SIP2(MergeJoin): OPPORTUNITY_FACT.MD_START_DATE), (SIP3(MergeJoin): OPPORTUNITY_FACT.MD_CP_ID), (SIP4(MergeJoin): OPPORTUNITY_FACT.MD_OPPORTUNITY_GL_PK, OPPORTUNITY_FACT.MD_START_DATE, OPPORTUNITY_FACT.MD_CP_ID)| | +-- Inner -> SELECT [Cost: 106K, Rows: 12K] (PATH ID: 3)| | | +---> STORAGE ACCESS for shd [Cost: 106K, Rows: 12K] (PATH ID: 4)| | | |      Projection: hpswadmmockTenantstg.TITAN_OPPORTUNITY_SHD_FACT_4merge_p1

Gil_Peretz

Please advice....

Daniel_Leybovic

and how about "NO STATISTICS" ?

Gil_Peretz

Done... before every run, for the tables and it's projections.
one more thing I tried to reduce the amount of rows from 12K to 1K it didn't help, same error
Remember the tables have 660 columns each. 

eli_revach

Same as https://community.vertica.com/vertica/topics/resource_limit_not_being_enforce_for_analytic_functions_with_order_by

We are waiting for some vertica Eng to explain the behave .

Any one please please  please   ?

[Deleted User]

Hi all (and hi future readers),

Eli kindly linked to another relevant question in the previous post.  Discussion of this issue has continued on that question, and there is some more information there.  In order to reduce duplication/confusion, please follow that question instead.

If you think this is an unrelated issue, feel free to post some more details and bring it back up here.

Thanks,
Adam