[QPROF] Accessing bytes_spilled value from v_monitor.query_consumption

[yakirgb]

Hello Vertica Team,

I like to use bytes_spilled column to check query performance issues.
This column appears in both v_monitor.query_consumption and v_internal.dc_execution_summaries.

While QPROF seems to be collecting data from query_consumption, I haven't been able to locate a feature in QPROF (such as a bar chart, pie chart, or function) that displays the bytes_spilled column specifically.

Could you please guide me on how to access or visualize the bytes_spilled values within QPROF?

Thank you for your support,
Yakir Gibraltar.

[oualib]

@yakirgb ofc, can you please tell me:

1. If a step of the Maurizio QPROF involves this feature (not mandatory)
2. How do you want to represent it (1D, 2D...) - if multiple features, which one should be 'x' and which one should be 'y'

[yakirgb]

1. The query on this project looks similar to Maurizio QPROF on https://github.com/mfelici/qprof/blob/main/qprof.sh#L440
2. ok, i did double check, on v_monitor.execution_engine_profiles there is counter_name column with `bytes spilled,
   But it's not on the following query :

        query = f"""
            SELECT
                node_name,
                operator_name,
                path_id,
                ROUND(SUM(CASE TRIM(counter_name) WHEN 'execution time (us)' THEN
                    counter_value ELSE NULL END) / 1000, 3.0) AS exec_time_ms,
                SUM(CASE TRIM(counter_name) WHEN 'estimated rows produced' THEN
                    counter_value ELSE NULL END) AS est_rows,
                SUM(CASE TRIM(counter_name) WHEN 'rows processed' THEN
                    counter_value ELSE NULL END) AS proc_rows,
                SUM(CASE TRIM(counter_name) WHEN 'rows produced' THEN
                    counter_value ELSE NULL END) AS prod_rows,
                SUM(CASE TRIM(counter_name) WHEN 'rle rows produced' THEN
                    counter_value ELSE NULL END) AS rle_prod_rows,
                SUM(CASE TRIM(counter_name) WHEN 'consumer stall (us)' THEN
                    counter_value ELSE NULL END) AS cstall_us,
                SUM(CASE TRIM(counter_name) WHEN 'producer stall (us)' THEN
                    counter_value ELSE NULL END) AS pstall_us,
                SUM(CASE TRIM(counter_name) WHEN 'clock time (us)' THEN
                    counter_value ELSE NULL END) AS clock_time_us,
                ROUND(SUM(CASE TRIM(counter_name) WHEN 'memory reserved (bytes)' THEN
                    counter_value ELSE NULL END) / 1000000, 1.0) AS mem_res_mb,
                ROUND(SUM(CASE TRIM(counter_name) WHEN 'memory allocated (bytes)' THEN 
                    counter_value ELSE NULL END) / 1000000, 1.0) AS mem_all_mb
            FROM
                v_monitor.execution_engine_profiles
            WHERE
                transaction_id={self.transaction_id} AND
                statement_id={self.statement_id} AND
                counter_value / 1000000 > 0
            GROUP BY
                1, 2, 3
            ORDER BY
                CASE WHEN SUM(CASE TRIM(counter_name) WHEN 'execution time (us)' THEN
                    counter_value ELSE NULL END) IS NULL THEN 1 ELSE 0 END ASC,
                5 DESC;"""
        query = self._replace_schema_in_query(query)
        return vDataFrame(query)

The table contain 110 counters, i don't think that need all, maybe just add this (or more?) counter to query .

Thank you, Yakir Gibraltar.

[oualib]

This PR should help you: #1192

[yakirgb]

To be honest, I'm not 100% sure what the counters names on v_monitor.execution_engine_profiles because we looking on v_internal.dc_execution_summaries. for columns like :

network_bytes_received
network_bytes_sent
data_bytes_read
data_bytes_written
data_bytes_loaded
input_rows
input_rows_processed
thread_count
peak_memory_kb

On query like (this is not the full query... just took the relevant part):

WITH des AS (
 SELECT _uuid AS cluster_name,
        transaction_id,
        statement_id,
        SUM(cpu_time_us) AS cpu_time_us,
        SUM(network_bytes_received) AS network_bytes_received,
        SUM(network_bytes_sent) AS network_bytes_sent,
        SUM(data_bytes_loaded) AS data_bytes_loaded,
        SUM(bytes_spilled) AS bytes_spilled,
        SUM(input_rows) AS input_rows,
        SUM(input_rows_processed) AS input_rows_processed,
        SUM(thread_count) AS thread_count,
        SUM(data_bytes_read) AS data_bytes_read,
        SUM(data_bytes_written) AS data_bytes_written,
        MAX(network_bytes_received) AS top_node_network_bytes_received,
        MAX(network_bytes_sent) AS top_node_network_bytes_sent,
        SUM(peak_memory_mb)::DECIMAL(15,2) AS peak_memory_mb,
        MAX(peak_memory_mb)::DECIMAL(15,2) AS top_node_peak_memory_mb
   FROM (
         SELECT _uuid,
                node_name,
                transaction_id,
                statement_id,
                SUM(cpu_time_us) AS cpu_time_us,
                SUM(network_bytes_received) AS network_bytes_received,
                SUM(network_bytes_sent) AS network_bytes_sent,
                SUM(data_bytes_loaded) AS data_bytes_loaded,
                SUM(bytes_spilled) AS bytes_spilled,
                SUM(input_rows) AS input_rows,
                SUM(input_rows_processed) AS input_rows_processed,
                SUM(thread_count) AS thread_count,
                SUM(data_bytes_read) AS data_bytes_read,
                SUM(data_bytes_written) AS data_bytes_written,
                SUM(peak_memory_kb)/1024 AS peak_memory_mb
           FROM vertica_monitoring.dc_execution_summaries
          WHERE time > CAST($from_date AS DATETIME) - INTERVAL '10' MINUTE
            AND time <= $to_date
            AND (_uuid = '$uuid' OR '$uuid' = '')
          GROUP BY _uuid, node_name, transaction_id, statement_id
        ) A
  GROUP BY _uuid, transaction_id, statement_id

And at the end we have report in Grafana:

But maybe better to ask someone from Vertica support on which counters they looking in order to debug query performance .

Thank you, Yakir Gibraltar.

[oualib]

@yakirgb Please give me a list of the ones you need and I will add them. The ones we have today were based on Vertica experts feedbacks.

[bentzimortaboola]

@oualib rely on v_monitor.execution_engine_profiles in busy production cluster won't be a good idea since the chance data will be in Vertica memory is low!!!
QPROF maybe need to use dc_execution_engine_profiles
also the QPROF must come with prerequisite that customers must to apply on DC table retention.

dc_execution_summaries is mandatory to use since it summarize most important metrics

[oualib]

@bentzimortaboola to close this ticket, you suggest me to replace execution_engine_profiles by dc_execution_engine_profiles and to add dc_execution_summaries in the tables to store?

@yakirgb I added bytes_spilled in the list of metrics, I need precise name to add some others.

[bentzimortaboola]

yes please take it with Yuanzhe

…

On Thu, Mar 14, 2024 at 12:04 PM Badr Ouali ***@***.***> wrote: @bentzimortaboola <https://github.com/bentzimortaboola> to close this ticket, you suggest me to replace execution_engine_profiles by dc_execution_engine_profiles and to add dc_execution_summaries in the tables to store? @yakirgb <https://github.com/yakirgb> I added bytes_spilled in the list of metrics, I need precise name to add some others. — Reply to this email directly, view it on GitHub <#1191 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFUEH5Q3VR43LAPUOTTZKCLYYFY2NAVCNFSM6AAAAABEOMKYT6VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DOOBUGE4TK> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Mor Bentzi +972545597027