[Vertica][VJDBC](5065) ERROR: Too many ROS containers exist for the following projections:

vertica version: Vertica Analytic Database v10.0.0-0
this is my table:

create table dc_run_job_txn (
    id                      varchar(80)           not null
        primary key,
    job_definition_id       varchar(80)           not null,
    job_definition_snapshot long varchar(1048576) not null,
    last_job_status         varchar(40),
    last_job_execution_time int,
    create_time             int                   not null,
    update_time             int                   not null,
    last_job_error_detail   varchar(1000)
);

table projections:

select get_projections('dc_run_job_txn');

Current system K is 0. 
# of Nodes: 1. 
Table dc_formula.dc_run_job_txn has 1 projections. 

Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
dc_formula.dc_run_job_txn_super [Segmented: Yes] [Seg Cols: "dc_formula.dc_run_job_txn.id"] [K: 0] [No buddy projections] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]

The following DML statement will be executed with high frequency:

UPDATE dc_run_job_txn SET last_job_status = 'running', last_job_execution_time = 1592382202606, update_time = 1592382202606 WHERE id = '5ee1b24a78627a71b4477445'

I learned that WOS was abandoned in vertica 10.
Here are some parameters I configured:

ROSPerStratum -1
MoveOutInterval 300
MergeOutInterval 30

We just started using vertica and we are very confused about this issue. Please help us, thank you very much!

Tagged:

Answers

  • Can you check the following parameters
    select get_config_parameter('ContainersPerProjectionLimit');
    select get_config_parameter('FilesPerProjectionLimit');

  • The error you have mentioned
    ERROR 5065: Too many ROS containers exist for the following projections:
    This could be because either mergeout is not catching up or you have not committed the transaction or there are lot of partitions (in your case you have not partitioned the table)
    Check the commit after the update. Committing often would result in Low ROS count.

  • moshegmosheg Vertica Employee Administrator

    In addition, with Vertica you can avoid DELETEs, UPDATEs and MERGE, to reduce delete vectors and improve performance.
    I learned this from Maurizio to manage data versioning and save the row “insert/update/delete” time history.
    How the method works?
    Instead of doing deletes or update to your data, try to use Live Aggregate Projection (LAP) TOP-K with the last version of the data.
    In the base (anchor) table you would just do INSERTs.
    The anchor table will always increase its size since we insert new data to “replace” or “hide” the old data.
    For example:

    DROP TABLE my_table cascade;
    
    CREATE TABLE my_table(
    subject_id  int,
    subject     varchar(10),
    insert_date datetime default sysdate())
    ORDER BY subject_id;
    
    CREATE PROJECTION my_topk_LAP (subject_id, insert_date, subject)
    AS SELECT subject_id, insert_date, subject
       FROM my_table
    LIMIT 1 OVER (PARTITION BY subject_id ORDER BY insert_date DESC);
    
    COPY my_table (subject_id,subject) FROM STDIN DELIMITER ',' ABORT ON ERROR;
    1,A
    2,B
    3,C
    4,D
    \.
    
    \echo 'When we want to update B with New B, we just INSERT new B with its subject_id:'
    INSERT INTO my_table (subject_id,subject) values (2,'New B');
    COMMIT;
    
    SELECT subject_id, subject, insert_date FROM my_topk_LAP order by 1;
     subject_id | subject |        insert_date
    ------------+---------+----------------------------
              1 | A       | 2020-06-22 17:37:03.767162
              2 | New B   | 2020-06-22 17:37:03.803268
              3 | C       | 2020-06-22 17:37:03.767162
              4 | D       | 2020-06-22 17:37:03.767162
    (4 rows)
    
    SELECT subject_id, subject, insert_date FROM my_table order by 1;
     subject_id | subject |        insert_date
    ------------+---------+----------------------------
              1 | A       | 2020-06-22 17:37:03.767162
              2 | New B   | 2020-06-22 17:37:03.803268
              2 | B       | 2020-06-22 17:37:03.767162
              3 | C       | 2020-06-22 17:37:03.767162
              4 | D       | 2020-06-22 17:37:03.767162
    (5 rows)
    

    From time to time (every several months) you can “purge” the anchor table to get rid of old versions of the data.
    Like so:
    Step1: CREATE TABLE new_base_table AS SELECT * from LAP (you create a new table with the “last version” of the data)
    Step2: DROP TABLE base_table CASCADE (drop the old base table, it’s projections and LAP)
    Step3: ALTER TABLE new_base_table RENAME base_table (rename the “last version” of the data)
    Step4: recreate LAP
    From now on you continue INSERTing into the base table as usual.

  • Please add hint 'direct' while using delete or insert .

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file