Get status of a long running insert ...

Moderator: NorbertKrupa

Brett
Beginner
Beginner
Posts: 49
Joined: Fri Oct 11, 2013 1:19 am

Get status of a long running insert ...

Post by Brett » Tue Feb 11, 2014 6:12 pm

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.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Get status of a long running insert ...

Post by scutter » Wed Feb 12, 2014 8:03 pm

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
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Get status of a long running insert ...

Post by id10t » Wed Feb 12, 2014 8:23 pm

Hi!

[DELETED]
Last edited by id10t on Fri May 08, 2015 4:43 pm, edited 1 time in total.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Get status of a long running insert ...

Post by scutter » Wed Feb 12, 2014 10:46 pm

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
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Get status of a long running insert ...

Post by id10t » Thu Feb 13, 2014 7:07 am

Hi!

[DELETED]
Last edited by id10t on Fri May 08, 2015 4:42 pm, edited 1 time in total.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Get status of a long running insert ...

Post by NorbertKrupa » Mon Feb 17, 2014 12:42 am

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.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Get status of a long running insert ...

Post by id10t » Mon Feb 17, 2014 7:36 am

Hi!

[DELETED]
Last edited by id10t on Fri May 08, 2015 4:40 pm, edited 1 time in total.

Post Reply

Return to “New to Vertica Database Administration”