We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


What is the difference between the Memory Metrics in Query_Requests VS Resource_Acquisitions — Vertica Forum

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

  • Abhishek_RanaAbhishek_Rana Vertica Employee Employee
    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