Monitoring COPY Statement Events (Part 2)

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

A COPY command is typically composed of two phases. The second phase, if using the DIRECT parameter, includes sorting the data and writing the data in Disk.

You can monitor the progress of this phase of the COPY command via the data collector table DC_MERGE_EVENTS.

Example:

dbadmin->[email protected]=> SELECT DISTINCT component, description
dbadmin->[email protected]> FROM data_collector
dbadmin->[email protected]> WHERE table_name = 'dc_merge_events';
  component  |                              description
-------------+------------------------------------------------------------------------
MergeEvents | Events and progress of large sort that requires buffering data in disk
(1 row)

dbadmin=> COPY bf FROM '/home/dbadmin/bf.txt' DIRECT;
Rows Loaded
-------------
    13000000
(1 row)

dbadmin=> SELECT time::TIME time, RIGHT(node_name, 1) node, event_description,
dbadmin->        rows_processed, rows_remaining, elapsed_time_seconds,
dbadmin->        remaining_time_seconds, percent_completed
dbadmin->   FROM dc_merge_events
dbadmin->  WHERE session_id = current_session()
dbadmin->  ORDER BY time;
      time       | node |            event_description            | rows_processed | rows_remaining | elapsed_time_seconds | remaining_time_seconds | percent_completed
-----------------+------+-----------------------------------------+----------------+----------------+----------------------+------------------------+-------------------
11:11:12.383548 | 1    | Started merge sort from chunks on disk. |              0 |       26000000 |                    0 |                     -1 |                 0
11:11:12.38366  | 1    | Running multi-phase merge.              |              0 |       26000000 |                    0 |                     -1 |                 0
11:11:12.386755 | 1    | Running multi-phase merge.              |              0 |       26000000 |                    0 |                     -1 |                 0
11:11:18.420824 | 1    | Running multi-phase merge.              |        1149120 |       24850880 |                    6 |                    130 |                 4
11:11:24.323535 | 1    | Running multi-phase merge.              |        2298240 |       23701760 |                   11 |                    123 |                 8
11:11:30.143029 | 1    | Running multi-phase merge.              |        3447360 |       22552640 |                   17 |                    116 |                13
11:11:36.234431 | 1    | Running multi-phase merge.              |        4596480 |       21403520 |                   23 |                    111 |                17
11:11:42.157763 | 1    | Running multi-phase merge.              |        5745600 |       20254400 |                   29 |                    104 |                22
11:11:48.048463 | 1    | Running multi-phase merge.              |        6894720 |       19105280 |                   35 |                     98 |                26
11:11:54.092553 | 1    | Running multi-phase merge.              |        8043840 |       17956160 |                   41 |                     93 |                30
11:12:00.10969  | 1    | Running multi-phase merge.              |        9192960 |       16807040 |                   47 |                     87 |                35
11:12:06.125121 | 1    | Running multi-phase merge.              |       10342080 |       15657920 |                   53 |                     81 |                39
11:12:12.12821  | 1    | Running multi-phase merge.              |       11491200 |       14508800 |                   59 |                     75 |                44
11:12:13.639376 | 1    | Merge has made progress.                |       11778480 |       14221520 |                   61 |                     73 |                45
11:12:18.029708 | 1    | Running multi-phase merge.              |       12640320 |       13359680 |                   65 |                     69 |                48
11:12:20.287778 | 1    | Final merge phase of multi-phase merge. |       13000000 |       13000000 |                   67 |                     67 |                50
11:12:40.262602 | 1    | DataTarget write finished.              |       26000000 |              0 |                   87 |                      0 |               100
(17 rows)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/DATA_COLLECTOR.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/EnablingAndDisablingDataCollector.htm

Have fun!

Sign In or Register to comment.