Take a Peek at the Next Mergeout Job

Jim_Knicely · February 2019

Mergeout is a Tuple Mover process that consolidates ROS containers and purges deleted records. DML activities such as COPY and data partitioning generate new ROS containers that typically require consolidation, while deleting and repartitioning data requires reorganization of existing containers. The Tuple Mover constantly monitors these activities, and executes mergeout as needed to consolidate and reorganize containers.

If you want to see what’s planned next for the Mergeout

Example:

dbadmin=> SELECT print_next_mergeout_job();
                                                            print_next_mergeout_job
------------------------------------------------------------------------------------------------------------------------------------------------
Site v_test_db_node0001:

Eligible for mergeout:
No ROSes eligible for mergeout
Eligible for dv mergeout:
No DVROSes eligible for mergeout
(1 row)

dbadmin=> CREATE TABLE test (c1 INT);
CREATE TABLE

dbadmin=> INSERT /*+ DIRECT */ INTO test SELECT 1;
OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT /*+ DIRECT */ INTO test SELECT 2;
OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT /*+ DIRECT */ INTO test SELECT 3;
OUTPUT
--------
      1
(1 row)

dbadmin=> DELETE  /*+ DIRECT */ FROM test WHERE c1 = 1;
OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT projection_name
dbadmin->   FROM projections
dbadmin->  WHERE anchor_table_name = 'test'
dbadmin->    AND projection_schema = 'public';
projection_name
-----------------
test_super
(1 row)

dbadmin=> SELECT print_next_mergeout_job();
                                                                                                                                                            print_next_mergeout_job    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Site v_test_db_node0001:

Eligible for mergeout:
  Projection: test_super, stratum no: 0, no of ros chunks: 1, proj total ros count: 6, purge percentage: 1
Selected:
  Projection: public.test_super, partition key: , stratum no: 0, no of ros chunks: 1
Mergeout job:
  Projection: public.test_super, no of ros chunks: 1, size: 51

(1 row)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/TupleMover/Mergeout.htm

Have fun!