Monitoring COPY Statement Events (Part 2)
Jim_Knicely
- Select Field - Administrator
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/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!
0