Monitoring COPY Statement Events (Part 2): Quick Tip

Posted January 23, 2019 by Jim Knicely, Vertica Principal Solution Architect

City in Blur Motion
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->vmart@sandbox1=> SELECT DISTINCT component, description dbadmin->vmart@sandbox1--> FROM data_collector dbadmin->vmart@sandbox1--> 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/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/DATA_COLLECTOR.htm

https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/EnablingAndDisablingDataCollector.htm

Have fun!