MergeOut List of work to do

Tim_1

Hi,
Is there a way of listing the work that the MergeOut process has to do?
Did find the note on using "SELECT print_next_mergeout_job();", but this only lists the next mergeout job.
https://forum.vertica.com/discussion/240419/take-a-peek-at-the-next-mergeout-job

Can query what is currently running:-
select operation_start_timestamp, node_name, operation_name, table_schema, table_name, projection_name, ros_count, total_ros_used_bytes
from TUPLE_MOVER_OPERATIONS
where is_executing = 'T'
order by operation_start_timestamp desc

Historic Operations and duration (ignore the poor code quality):-
select STMO.operation_start_timestamp, STMO.node_name, STMO.table_name, STMO.projection_name, STMO.ros_count, STMO.total_ros_used_bytes,
DATEDIFF(ms, STMO.operation_start_timestamp, ETMO.operation_start_timestamp ) as "Duration MS"
from (select operation_start_timestamp, node_name, table_name, projection_name, transaction_id, ros_count, total_ros_used_bytes
from tuple_mover_operations where operation_status = 'Start') STMO,
(select operation_start_timestamp, projection_name, transaction_id from tuple_mover_operations where operation_status = 'Complete') ETMO
where 1=1
and STMO.transaction_id = ETMO.transaction_id
and STMO.projection_name = ETMO.projection_name
order by operation_start_timestamp desc

Any way of listing what work has to be done?
One of the tables has around 100+ loads a minute and has hierarchical partitioning.
Normally the TM process handles this, as the data is going into the "active" partition, occasionally old data is populated and the TM process is tide up sorting out large in-active partitions and not enough resources are available to process the active partitions and we get ROS push back.

When this happens would like to be able to see which large partitions still need merging.

Tim

bmurrell

I'd like to know this too. Will help to tune MAXCONCURRENCY and PLANNEDCONCURRENCY in the 'tm' pool, and throttle as above when large non-active partitions have DML.