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!

How do I monitor a long running CTAS?

I'm running a Create Table As, but how do I monitor its progress?
I'm looking at counters in v_monitor.execution_engine_profiles and v_internal_events, but they're NULL.

Answers

  • Jim_KnicelyJim_Knicely Administrator
    edited December 2021

    Grab the transaction id and statement id for the CTAS statement and try a query like this?

    dbadmin=> SELECT node_name, operator_name, counter_name, TO_CHAR(SUM(counter_value), '999,999,999,999') rows_loaded_so_far FROM execution_engine_profiles WHERE transaction_id IN (?) AND statement_id = ? AND operator_name IN ('StorageUnion', 'Sort') AND counter_name IN ('rows produced', 'rows to process', 'total rows read in sort') GROUP BY node_name, operator_name, counter_name ORDER BY node_name, operator_name, counter_name, SUM(counter_value);
         node_name                    | operator_name |      counter_name           | rows_loaded_so_far
    ----------------------------+------------------+--------------------------+--------------------
     v_somepoc_node0001 | Sort                        | rows produced               |  4,009,983
     v_somepoc_node0001 | Sort                        | total rows read in sort  |  4,009,983
     v_somepoc_node0001 | StorageUnion    | rows produced                |  4,009,983
     v_somepoc_node0001 | StorageUnion    | rows to process              |  4,009,983
     v_somepoc_node0003 | Sort                       | rows produced                |  4,007,858
     v_somepoc_node0003 | Sort                       | total rows read in sort   |  4,007,858
     v_somepoc_node0003 | StorageUnion    | rows produced                |  4,007,858
     v_somepoc_node0003 | StorageUnion    | rows to process              |  4,007,858
    (8 rows)
    

    For the "StorageUnion" operator, when "rows produced" = "rows to process" then it is done.
    For the "Sort" operator, when "rows produced" = "total rows read is sort" then it is done.

    Note that Sort occurs after StorageUnion.

Leave a Comment

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