memory_allocated_bytes from "v_monitor"."query_plan_profiles" always NULL : A bug?

Sudhakar_B

Vertica Enterprise Edition 9.3.1 and also VMWare CE version 10.0.0

When profiling some queries I noticed that memory_allocated_bytes from v_monitor.query_plan_profiles is always NULL.
Further investigation into vs_system_views for query_plan_profiles view revealed that, counter_name being used is memory allocated (bytes).

  from v_internal.dc_execution_engine_profiles 
    where counter_name = 'memory allocated (bytes)' 
    UNION
    from v_internal.vs_execution_engine_profiles 
    where counter_name = 'memory allocated (bytes)' 

Whereas neither of the tables referenced above have that value for counter_name!

with x as (
select distinct counter_name
from v_monitor.execution_engine_profiles 
where counter_name ilike '%mem%'
union
select distinct counter_name
from v_internal.vs_execution_engine_profiles 
where counter_name ilike '%mem%'
)
select * from x
order by 1
; 

Which of the counter_name values should really be used?
current allocated rid memory (bytes)
current memory allocations (count)
current memory capacity (bytes)
current memory overhead (bytes)
current memory padding (bytes)
current memory requested (bytes)
current unbalanced memory allocations (count)
current unbalanced memory capacity (bytes)
current unbalanced memory overhead (bytes)
current unbalanced memory padding (bytes)
current unbalanced memory requested (bytes)
memory reserved (bytes)
peak allocated rid memory (bytes)
peak memory allocations (count)
peak memory capacity (bytes)
peak memory overhead (bytes)
peak memory padding (bytes)
peak memory requested (bytes)
peak unbalanced memory allocations (count)
peak unbalanced memory capacity (bytes)
peak unbalanced memory overhead (bytes)
peak unbalanced memory padding (bytes)
peak unbalanced memory requested (bytes)
reserved rid memory (bytes)

Jim_Knicely

The memory allocated (bytes) counter was depracated years ago. Hmm. I just opened a Jira to check if the v_monitor.query_plan_profiles needs to be updated. There might be a reason it hasn't. I will update this thread when I get the answer!

Sudhakar_B

Thank much @Jim_Knicely for response and confirmation.
As a work around I am joining v_monitor.query_plan_profiles with v_monitor.execution_engine_profile. However, which memory counter can be used from above to caculate memory used at path_id level from v_monitor.execution_engine_profile?
Would that be peak memory requested (bytes)

SruthiA

@Sudhakar_B : You can use query_consumption a new view which will help you in collecting the Peak memory used by a query. Along with that we can even get the network used, cpu cycles used per query.

dbadmin=> select * from query_consumption limit 0;
end_time | session_id | user_id | user_name | transaction_id | statement_id | cpu_cycles_us | network_bytes_received | network_bytes_sent | data_bytes_read | data_bytes_written | data_bytes_loaded | bytes_spilled | input_rows | input_rows_processed | peak_memory_kb | thread_count | duration_ms | resource_pool | output_rows | request_type | label | is_retry | success
----------+------------+---------+-----------+----------------+--------------+---------------+------------------------+--------------------+-----------------+--------------------+-------------------+---------------+------------+----------------------+----------------+--------------+-------------+---------------+-------------+--------------+-------+----------+---------
(0 rows)

dbadmin=>

Jim_Knicely

@Sruthi - I guess it all depends on where @Sudhakar_B needs to know how much memory is allocated - at each plan step or at the end, what was the peak? Per the documentation, the MEMORY_ALLOCATED_BYTES is supposed to show the amount of memory the path used

See:
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_PLAN_PROFILES.htm

Sudhakar_B

Thank @SruthiA and @Jim_Knicely !
QUERY_CONSUMPTION is a great view and I use it every day to identify high resource consuming queries from overall workload. The metrics in this are aggregated at query or (TRANSACTION_ID,STEMENT_ID) level. Correct?
However we have queries that come in which are 2000 lines long and have 30-40 tables joins. So when tuning a single query I wanted to find out which PATH ID (s) in that query plan is consuming high resource, so that we can focus on tuning that portion of the query or tables involved in that step. As @Jim_Knicely pointed out QUERY_PLAN_PROFILE.MEMORY_ALLOCATED_BYTES should give this information. I can get the overall query level memory usage from profiling the query.
Just wanted confirmation if there are any COUNTER_NAME that can be used for relatively measuring memory used at PATH_ID level.
I don't even need exact value, just a relative pattern to narrow down the focus at PATH level will be great.
Thanks once again for your feedback and help. Much appreciated.

SruthiA

@Sudhakar_B Yes. You are right. QUERY_CONSUMPTION provides info aggregated at query level. The below query might help to narrow down the issue at path level.

SELECT
operator_name,
counter_name,
path_id,
COUNT(DISTINCT operator_id),
AVG(counter_value)
FROM
execution_engine_profiles
WHERE
transaction_id = :t_id
AND statement_id = :s_id
AND path_id = :path_id
AND node_name =
(
SELECT
LOCAL_NODE_NAME())
GROUP BY
1,2,3
HAVING
SUM(counter_value) > 0 ORDER BY 1;

The below best practices page contains more queries against Execution_Engine_Profiles against few scenarios. I hope this my help your scenarios

https://www.vertica.com/kb/System-Tables-For-Performance/Content/BestPractices/System-Tables-For-Performance.htm