Hi,
Is there a way to find out how many records of a long running insert statements have been inserted? I can't tell if the process is hanging or if its just slow.
Get status of a long running insert ...
Moderator: NorbertKrupa
Re: Get status of a long running insert ...
Hi Brett,
You can query the run-time profiling counters for any query that is executing, in order to check on progress or to see if the query might be hung. Even if you haven’t explicitly PROFILEd the query, the execution_engine_profiles table will have profiling counters until the query completes.
For monitoring an INSERT’s progress, check the counters associated with the DataTarget operator, which sorts and writes the data. Counters of interest would be ‘rows produced’, ’total merge phases’, ‘merge phases completed’. You might see muliple merge phases, and you can see how far through the merge phases the sorting currently is.
—Sharon
You can query the run-time profiling counters for any query that is executing, in order to check on progress or to see if the query might be hung. Even if you haven’t explicitly PROFILEd the query, the execution_engine_profiles table will have profiling counters until the query completes.
For monitoring an INSERT’s progress, check the counters associated with the DataTarget operator, which sorts and writes the data. Counters of interest would be ‘rows produced’, ’total merge phases’, ‘merge phases completed’. You might see muliple merge phases, and you can see how far through the merge phases the sorting currently is.
—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
Re: Get status of a long running insert ...
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Fri May 08, 2015 4:43 pm, edited 1 time in total.
Re: Get status of a long running insert ...
Hi Daniel,
Progress - it depends on which part of the statement you want to track. If it’s the SELECT subquery, then you can monitor the ‘rows produced’ for each path id. Once the work is in the INSERT portion, data can be written out until the sorting is complete, so monitoring the merge phases is helpful - if it’s on 1 of 4 or 3 of 4 phases for example.
I tried to use profiling data for a CTAS in 5.1 the other day and didn’t find the expected profiling data. I’m not sure if that’s a 5.1 issue that is still true in later versions. You can’t EXPLAIN a CTAS in 5.1 and it was still true in 6.1 on the last version I tried it in - which is unfortunate because if you do have profiling data you can’t correlate the data to specific paths. I haven’t tried it in 7.0.
—Sharon
Progress - it depends on which part of the statement you want to track. If it’s the SELECT subquery, then you can monitor the ‘rows produced’ for each path id. Once the work is in the INSERT portion, data can be written out until the sorting is complete, so monitoring the merge phases is helpful - if it’s on 1 of 4 or 3 of 4 phases for example.
I tried to use profiling data for a CTAS in 5.1 the other day and didn’t find the expected profiling data. I’m not sure if that’s a 5.1 issue that is still true in later versions. You can’t EXPLAIN a CTAS in 5.1 and it was still true in 6.1 on the last version I tried it in - which is unfortunate because if you do have profiling data you can’t correlate the data to specific paths. I haven’t tried it in 7.0.
—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
Re: Get status of a long running insert ...
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Fri May 08, 2015 4:42 pm, edited 1 time in total.
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Get status of a long running insert ...
You may want to look at the LOAD_STREAMS system table in the v_monitor schema or the dc_load_events data collection table in the v_internal schema.
Checkout vertica.tips for more Vertica resources.
Re: Get status of a long running insert ...
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Fri May 08, 2015 4:40 pm, edited 1 time in total.