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

Comments

  • Hi,

    "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

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.