Query execution memory consumption

siva

Our database size as given by column_storage is around 500 MB. Query is taking about 2 to 3 GB of memory (as per v_monitor.query_requests).   

We want reduce the memory consumption to be below 1 GB.

We used database designer to get projections and it has generated about 200 projections.

We deployed all of them and we see that query cost has decreased. But memory consumption remains around 2 GB.

Does table projections have any impact on query memory consumption ?  Are there any Vertica configurations which will result in less memory consumption.

Thanks
-Siva

Sajan_Sangraula

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

[Deleted User]

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

Adrian_Oprea_1

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.

Adrian_Oprea_1

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 

[Deleted User]

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.

siva

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