Get status of a long running insert ...

[NorbertKrupa]

Did I miss where OP posted they are using 7? Were you able test out dc_load_events?

[id10t]

Hi!

[DELETED]

[Brett]

Thanks for the great feedback! I am using Vertica 7 by the way... I will try some of the suggestions provided and will post my findings

[JimKnicely]

Hi,

I have a long running insert using the /*+ DIRECT */ hint.

I believe that I I can monitor the progress of it by querying the EXECUTION_ENGINE_PROFILES table...

Code: Select all

vertica=> SELECT counter_name, MAX(counter_value), MIN(counter_value) 
vertica->   FROM execution_engine_profiles 
vertica->  WHERE counter_name ILIKE '%sort%' 
vertica->     OR counter_name ILIKE '%clock%' 
vertica->     OR counter_name ILIKE '%estimated%' 
vertica->    AND session_id = 'verticadb01.u-28326:0x178b'
vertica->  GROUP BY counter_name 
vertica->  ORDER BY counter_name;
      counter_name       |    MAX     |    MIN     
-------------------------+------------+------------
 clock time (us)         | 4083970253 |          0
 estimated rows produced | 2319448203 | 2319448203
 rows output by sort     |  372360617 |          0
(3 rows)

In the example, when the MAX and MIN values of the “rows output by sort” = the “estimated rows produced”, the INSERT will be done!

[scutter]

The “estimated rows produced” are just estimates - they are what shows up in the EXPLAIN output and there’s no guarantee that they match reality. When you’re deep into the sort phase of the INSERT, other lower operators will have completed, and you should be able to use “rows produced” for a completed operator. I think there may be a counter in 6.x that tracks the incoming row count for an operator when the info is available - I haven’t gone looking for it though.

—Sharon

[JimKnicely]

The query seems to work better than an earlier post. At least, it'll give you a ball park of when the insert will complete...

Code: Select all

SELECT counter_name,
       MAX(counter_value),
       MIN(counter_value),
       ROUND(AVG(counter_value)) AVG
  FROM execution_engine_profiles
 WHERE (counter_name ILIKE '%sort%'
    OR counter_name ILIKE '%clock%'
    OR counter_name ILIKE '%estimated%')
   AND session_id = '???'
 GROUP BY counter_name
 ORDER BY counter_name;

In this query, I believe that as the AVG "rows output by sort" approaches the MAX "estimated rows produced" the INSERT will be closer to completion.

​Example:

Code: Select all

   
vertica=> SELECT counter_name, MAX(counter_value),
vertica->        MIN(counter_value),
vertica->        ROUND(AVG(counter_value)) AVG
vertica->   FROM execution_engine_profiles
vertica->  WHERE (counter_name ILIKE '%sort%'
vertica->     OR counter_name ILIKE '%clock%'
vertica->     OR counter_name ILIKE '%estimated%'
vertica->    AND session_id = 'prd-db-01.u-28326:0x2efc')
vertica->  GROUP BY counter_name
vertica->  ORDER BY counter_name;
      counter_name       |    MAX     |    MIN    |    AVG   
-------------------------+------------+-----------+-----------
 clock time (us)         | 7970590481 |         0 |   8312379
 estimated rows produced |  905522180 | 905522180 | 905522180
 rows output by sort     |  696839831 |         0 |  12274690
(3 rows)

AS Scutter pointed out, the "estimated rows produced" is an estimate, but I've found it to be a pretty good estimate