Too many ROS Container

JunaidKhan

Hi,

can somebody help in the below error we are getting in, actullay we have done mergeout moveout multiple time but currently there are huge number of transaction we need to be catered and insert in to the table, is any best practices to optimized ROS for table projection,.

java.lang.RuntimeException: process: throws SQLException[Vertica]VJDBC ERROR: Too many ROS containers exist for the following projections:
FAS.TMP_OC_ACTIVE_ALARMS_COUNTERS_b1 (limit = 45664, ROS files = 45656, DV files = 0, new files = 13)
at com.hp.ossa.batch.batchlet.OssaBatchlet.onStepError(OssaBatchlet.java:460)
at com.hp.ossa.batch.batchlet.jdbc.SqlBatchlet.processStep(SqlBatchlet.java:117)
at com.hp.ossa.batch.batchlet.jdbc.SqlCDCBatchlet.processStep(SqlCDCBatchlet.java:247)
at com.hp.ossa.batch.batchlet.OssaBatchlet.process(OssaBatchlet.java:150)
at org.jberet.runtime.runner.BatchletRunner.run(BatchletRunner.java:72)
at org.jberet.runtime.runner.StepExecutionRunner.runBatchletOrChunk(StepExecutionRunner.java:229)
at org.jberet.runtime.runner.StepExecutionRunner.run(StepExecutionRunner.java:147)
at org.jberet.runtime.runner.CompositeExecutionRunner.runStep(CompositeExecutionRunner.java:164)
at org.jberet.runtime.runner.CompositeExecutionRunner.runFromHeadOrRestartPoint(CompositeExecutionRunner.java:88)
at org.jberet.runtime.runner.JobExecutionRunner.run(JobExecutionRunner.java:60)
at org.wildfly.extension.batch.jberet.impl.BatchEnvironmentService$WildFlyBatchEnvironment$1.run(BatchEnvironmentService.java:243)
at org.wildfly.extension.requestcontroller.RequestController$QueuedTask$1.run(RequestController.java:497)
at org.jberet.spi.JobExecutor$3.run(JobExecutor.java:161)
at org.jberet.spi.JobExecutor$1.run(JobExecutor.java:99)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
at org.jboss.threads.JBossThread.run(JBossThread.java:320)

also i have found the below link to troubleshoot ROS, how can i increase the ROS limit for specific projections.

Br,

HerbC

A few typical situations I often see that cause this issue:

1. Too many partitions on the table. Each partition will be held in a separate ROS container. These partition-based ROS containers will not merge. You could try using Hierarchical Partitioning to merge partitions into monthly and yearly partitions as they age.
   https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Partitions/HierarchicalPartitioning.htm

2. Maybe try loading the rows into WOS instead of directly to ROS. (leave off the /*+ DIRECT */ hint on the INSERT statements). WOS will take many rows without creating a ROS container for each inserted row. Then, occasionally will create a single ROS container for the many rows (called moveout) and write to ROS.

3. Autocommit is turned on in ETL tool. So, after each INSERT into ROS, there is a commit. Each commit creates a ROS container. Better to batch INSERTS (with many INSERTS followed by a single COMMIT).

Just a few thoughts.

JunaidKhan

https://riptutorial.com/vertica/example/30116/error--too-many-ros-containers-exist-for-the-following-projections-

JunaidKhan

https://riptutorial.com/vertica/example/30116/error--too-many-ros-containers-exist-for-the-following-projections-