Query execution memory consumption

Hi Siva,
Have you tried restricting the memory size in the resource pool. You can create a new resource pool with limited memory and associate the pool with the db user used to run the query. Details on memory pool at https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATERESOU...

/Sajan

Thanks Sajan!

To clarify a common point of confusion:  The amount of memory that Vertica uses has surprisingly little to do with what query you are running.  The query does provide a lower and upper bound on how much memory it needs.  But those numbers can be very far apart.

It's not about how much memory the query *needs*; it's about how much memory the query *is given*.  And that, as Sajan pointed out, is governed by the resource manager, which you can configure with whatever memory limit you'd like.  If a query is given lots of memory, it may take more than it ends up needing, just to be safe.

For "column_storage" -- note that that's compressed size.  Vertica may need to un-compress the data to perform the JOIN.

Adam

This might restrict access to resources by the user who will use that pool but if the query requires more memory then its executing pool it will result in SPILL operation - and that will make the query really slow.

But limiting the memory on a specific used pool with a running query requesting more than it's actual memory wouldn't make it SPILL on disk and make it run much 

If a query is reserving more than it actually needs, then yeah, restricting it won't necessarily cause it to spill.

Vertica can't know for sure how much memory a query will ideally want, particularly for complex JOINs, without actually running the query to see what your data looks like.  So it makes conservative estimates.

Hi, I tried restricting memory but it is failing  with insufficient memory exception.

Below are the details from resource_acquisitions, looks like thread count is very high. Does high thread count leads to high memory usage ?  Is there a configuration to control threat cont per query ?

node_name thread_count memory_inuse_kb

v_cbidb_node0001 135 4671145

v_cbidb_node0003 134 4586853

v_cbidb_node0002 134 4586853