Request exceeds memory limits

dimitri_p

I have a resource pool that is declared as

maxmemorysize '30%' plannedconcurrency 1 maxconcurrency 1

I run my query in that pool and I get

SQL Error [3587] [53000]: [Vertica]VJDBC ERROR: Insufficient resources to execute plan on pool kpi_pool [Timedout waiting for resource request: Request exceeds limits: Memory(KB) Exceeded: Requested = 139266624, Free = 139223242 (Limit = 139266624, Used = 43382) (queueing threshold)]

notice that

Used = 43382

where is that from? No queries are running in the pool.

mosheg

The value Used = 43382 (KB) - about 42 MB - is very likely expected even if no user queries are running.
Because Vertica reserves some memory in each resource pool for internal operations and memory bookkeeping.
Vertica does not instantly release all memory when a query completes.
Some allocations persist briefly to avoid overhead from frequent reallocation.
The "Used" value reflects total memory reserved from the pool, not necessarily memory used by active user queries.
If this causes query rejections, increase the pool’s maxmemorysize slightly to leave margin for such overhead:
ALTER RESOURCE POOL kpi_pool MAXMEMORYSIZE '35%';

Or reduce the query’s memory requirement, if feasible.

dimitri_p

thanks, Moshe. Based on what I see, my query requests exactly Query Budget, which is the entire pool's memory, as plannedconcurrency is set to 1. In that case, increasing pool's memory is just going to raise the budget as well, causing the query to fail for the very same reason as before: the query demands the entire pool's memory and some little portion of it is already taken.

SQL Error [3587] [53000]: [Vertica]VJDBC ERROR: Insufficient resources to execute plan on pool kpi_pool [Timedout waiting for resource request: Request exceeds limits: Memory(KB) Exceeded: Requested = 162477728, Free = 162432812 (Limit = 162477728, Used = 44916) (queueing threshold)]