Memory used by a Query

well I am confused with the multiple memory columns in the monitor tables.

As of now I assume the memory used by a query is the "memory_inuse_kb" column of "resource_acquisitions" table into number of nodes.

1 Gb in that col and 10 nodes is 10 Gb of memory used in the query executiong

But the document in "my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/RESOURCE_ACQUISITIONS.htm"

confuses which one to consider as memory utilized.



  • The total memory reserved by the query is available in RESOURCE_ACQUISITIONS.MEMORY_INUSE_KB. The difference between RESOURCE_ACQUISITIONS.MEMORY_INUSE_KB and QUERY_PROFILES.RESERVED_EXTRA_MEMORY is the "essential memory."

    • RESOURCE_ACQUISITIONS.MEMORY_INUSE_KB is the total memory acquired.
    • QUERY_PROFILES.RESERVED_EXTRA_MEMORY is the unused portion of the acquired memory.
    • The difference gives you the memory in use.



  • Options

    I think an example can help clear up the confusion:


    -You want to run Query1. 

    -The optimizer thinks this query will require 2G of memory. 2G gets reserved for this query, and this will be the value you see in RESOURCE_ACQUISITIONS.MEMORY_INUSE_KB (except in kb)

    -Once the query starts running, it only needs 1.5GB of memory.


    - This query is using 1.5GB, out of the 2GB it had reserved


    The value you will want to use will depend on your purpose. In a way, the query is using 2GB because those 2GB are not longer available as resources for other queries. However, if you wanted to optimize your resource pool configurations, you now know this query only needs 1.5GB to run. 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file