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

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