Have more than 1024 Ros containers

edited January 2018 in General Discussion

I create a simple table like this:

then I insert more than1024 different values and then
run :

SELECT DO_TM_TASK('moveout');
SELECT DO_TM_TASK('mergeout');

after that, I check number of partitions by this query:

and result is:

for check number of Ros containers I run this query:

and result:

So I have more than 1024 number of Ros containers. I can insert more data and not getting any error.

but for other situations I get "too many ros containers" for having more than 1024 Ros containers.

what is happened?


  • Jim_KnicelyJim_Knicely - Select Field - Administrator


    When you ran the TM Moveout command, you should have received an error...


    dbadmin=> select export_tables('','public.test_ros_partition');
    CREATE TABLE public.test_ros_partition
        id int,
        p int NOT NULL
    PARTITION BY (test_ros_partition.p);
    (1 row)
    dbadmin=> insert into test_ros_partition select row_number() over (), row_number() over () from all_tables cross join vs_columns limit 1033;
    (1 row)
    dbadmin=> select storage_type, count(*) from storage_containers where projection_name = 'test_ros_partition_super' group by storage_type;
     storage_type | count
     WOS          |     1
    (1 row)
    dbadmin=> SELECT DO_TM_TASK('moveout', 'test_ros_partition_super');
     Task: moveout
    (Table: public.test_ros_partition) (Projection: public.test_ros_partition_super)
    On node v_sfdc_node0001:
      ERROR 5060:  Too many data partitions
    (1 row)

    You should also get an error if you try to insert directly into ROS:

    dbadmin=> rollback;
    dbadmin=> insert /*+ direct */ into test_ros_partition select row_number() over (), row_number() over () from all_tables cross join vs_columns limit 1033;
    ERROR 5060:  Too many data partitions
    HINT:  Verify that the table partitioning expression is correct

    Can you check the values of the following config parameters?

    • MaxPartitionCount
    • ContainersPerProjectionLimit


    dbadmin=> select parameter_name, current_value, default_value, description from configuration_parameters where parameter_name in ('MaxPartitionCount', 'ContainersPerProjectionLimit');
            parameter_name        | current_value | default_value |                                      description
     ContainersPerProjectionLimit | 1024          | 1024          | Number of ROS containers that are allowed before new ROSs are prevented (ROS pushback)
     MaxPartitionCount            | 1024          | 1024          | Max no of partitions per projection
    (2 rows)
  • There's a different between blowing past the limit in a single operation and creeping up to it slowly. It's generally a bad thing to get data "stuck" in the WOS with moveout failing. I suspect if you load 900 partitions into the wos, moveout, then load 133 more you can see the behavior. The WOS isn't partitioned, so inserts there can sneak past some of the max partition count checks.

  • @Jim_Knicely this is very useful , thanks

Leave a Comment

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