Cannot allocate sufficient memory for COPY statement (536870912 requested, 268435456 permitted)

rajatpaliwal86

I'm getting this error while trying to run a copy table command to load a JSON. I also tried creating a resource pool with higher MAXQUERYMEMORYSIZE and MEMORYSIZE value but still getting the same error. How to ensure that the query was run on a specific memory pool?

SruthiA

Can you share more details about the resource pool where the query is running? What is MEMORY_CAP_KB, PLANNED CONCURRENCY, MAX CONCURRENCY set to for that pool? Is this the first time it is failing?

rajatpaliwal86

@SruthiA
dbadmin=> select * from RESOURCE_POOLS where name = 'copy_pool';
-[ RECORD 1 ]------------+------------------
pool_id | 45035996275467216
name | copy_pool
is_internal | f
memorysize | 24G
maxmemorysize | 24G
maxquerymemorysize | 24G
executionparallelism | AUTO
priority | 0
runtimepriority | MEDIUM
runtimeprioritythreshold | 2
queuetimeout | 00:05
plannedconcurrency | AUTO
maxconcurrency | 5
runtimecap |
singleinitiator | f
cpuaffinityset |
cpuaffinitymode | ANY
cascadeto |

Is there any way to figure out in which pool the query was run?
The JSON file(500 MB) has around 500k events.

SruthiA

Issue is COPY is unable to get sufficient resources. Set memory size and planned concurrency in such a way that COPY will get requested amount of memory
Query budget is determined the following formula. Based on the above output your planned concurrency is AUTO.. if it is set to Auto,
Vertica automatically sets PLANNEDCONCURRENCY at query runtime, choosing the lower of these two values:

Number of cores
Memory/2GB

Query budget = MEMORYSIZE / PLANNEDCONCURRENCY of the pool

You can run the following query to find out in which pool it was run if you can capture the txn id and statement id from sessions table

select * from dc_resource_acquisitions where transaction_id = <> and statement_id = <>;