The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
What is the difference between the Memory Metrics in Query_Requests VS Resource_Acquisitions
We are performing analysis of memory usage of queries and are curious about the differences between the values reported in the 2 v_monitor columns below. The Definitions of these columns in the SQL Ref manual are nearly the same, yet query_requests.memory_acquired_mb is often null, or does not match value in resource_acquisitions. Is a more detailed definition of these 2 columns available?
v_monitor.resource_acquisitions column memory_inuse_kb
Amount of memory in kilobytes acquired by this request
v_monitor.query_requests column MEMORY_ACQUIRED_MB
Memory acquired by this query request in megabytes
Thanks for any Information you can provide
0
Comments
"memory_inuse_kb" shown in RESOURCE_ACQUISITIONS is the initial reserved memory for this transaction. This initial allocation depends upontotal memory given to the RESOURCE POOL & PLANNED CONCURRENCY of that pool. We can say it's always equal to RESOURCE POOL MEMORY SIZE/PLANNED CONCURRENCY
"memory_acquired_mb" in query_requests represent the same value in round off terms. So there should not be much difference in both of these.
Eg:
dbadmin=> select * FROM d33497.RESOURCE_ACQUISITIONS WHERE transaction_id=45035996284960481 and statement_id=1;
node_name | transaction_id | statement_id | request_type | pool_id | pool_name | thread_count | open_file_handle_count | memory_inuse_kb | queue_entry_timestamp | acquisition_timestamp | release_timestamp | duration_ms | is_executing
-----------------+-------------------+--------------+--------------+-------------------+------------------+--------------+------------------------+-----------------+-------------------------------+-------------------------------+-------------------------------+-------------+--------------
v_ossa_node0010 | 45035996284960481 | 1 | Reserve | 45035996428276146 | hp_vertica_pool1 | 12 | 10 | 512000 | 2015-01-02 14:39:16.097822+00 | 2015-01-02 14:39:17.487125+00 | 2015-01-02 14:39:18.938807+00 | 1451 | f
v_ossa_node0011 | 45035996284960481 | 1 | Reserve | 45035996428276146 | hp_vertica_pool1 | 9 | 7 | 512000 | 2015-01-02 14:39:16.096126+00 | 2015-01-02 14:39:17.485644+00 | 2015-01-02 14:39:18.937322+00 | 1452 | f
v_ossa_node0012 | 45035996284960481 | 1 | Reserve | 45035996428276146 | hp_vertica_pool1 | 9 | 7 | 512000 | 2015-01-02 14:39:16.096635+00 | 2015-01-02 14:39:17.486045+00 | 2015-01-02 14:39:18.937766+00 | 1451 | f
(3 rows)
dbadmin=> select * from d33497.query_requests WHERE transaction_id=45035996284960481 and statement_id=1;
node_name | user_name | session_id | request_id | transaction_id | statement_id | request_type | request | request_label | search_path | memory_acquired_mb | success | error_count | start_timestamp | end_timestamp | request_duration_ms | is_executing
-----------------+------------+-----------------------------------+------------+-------------------+--------------+--------------+-----------------------------------------------------------------------------+---------------+---------------------------------------------------+--------------------+---------+-------------+-------------------------------+------------------------------+---------------------+--------------
v_ossa_node0010 | hp_vertica | ossvert1.gre.hp.com-51343:0xe7b73 | 1 | 45035996284960481 | 1 | QUERY | profile select count(distinct x), * from foo1 group by x,z order by z desc; | | "$user", public, v_catalog, v_monitor, v_internal | 500 | t | | 2015-01-02 14:39:15.990521+00 | 2015-01-02 14:39:18.93935+00 | 2949 | f
(1 row)
Hope it helps.
Regards'
Abhishek