Vertica v12.0.3 -- Too many Ros Containers over TEMPORARY TABLE

federico

Hi everyone
One of our client just updated Vertica to v 12.0.3 and one of our historical etl job started to failed .

The etl job creates a temporary table , withCREATE LOCAL TEMPORARY TABLE... ON COMMIT PRESERVE ROWS and stores data from a SQL Server.

During the insert into temporary table in Vertica , the etl job fails with this syntax:

ERROR: Too many ROS containers exist for the following projections:
v_temp_schema.tempControll_3fdd282e03132378_b0 (limit = 67323, ROS files = 67321, DV files = 0, new files = 23)
v_temp_schema.tempControll_3fdd282e03132378_b1 (limit = 67323, ROS files = 67321, DV files = 0, new files = 23)

In the past I had the same problem , but only on production tables and we forced with
SELECT DO_TM_TASK('mergeout', 't1' , and Vertica was not updated to version 12.0.3

I know that WOS usage has been deprecated , but I don't figure out what's the problem with this scenario.

MergeOutInterval' --> 600

ContainersPerProjectionLimit' --> 1024

Can anyone help me out?

marcothesane

I suppose that you migrated to version 12 from a Vertica version of 9.2 or below - when the WOS, the Write Optimised Store was still part of the architecture.

I suppose that your ETL job fires an INSERT or other DML language statement of the type:
INSERT INTO public.foo VALUES (?,?,?,?,?) and then issues a SQLExecute() on the handle of that statement for tens of thousands of times before a commit. Every SQLExecute() of such a statement generates a new ROS container. Remember that ROS data is sorted, and there is no way to sort/merge the data of the previous SQLExecute()-s of the current transaction with the data of the current one. In the times of the WOS, that did not hurt - as the data there was unsorted and still row-oriented; and it was the Moveout task of the tuple mover that created one ROS container from many SQLExecute() -s once it got triggered.

The limit of 67323 ROS containers is for ROS containers being created in one and the same transaction, hence my suspicion here.

Now that all goes to ROS directly, you should increase the insert rowset size from just one or at most a few rows (which it currently surely is) to , say 10,000. I'm almost completely sure that will solve your problem.

federico

Hi Marco,
Most likely Vertica deleted all the comments about this thread, cause I don't see them.
At the end , 10 days ago I solved .
I digged into the problem , and it is exactly what you said.
The etl job in this case (which was a branch of the original one ) has commit after 50 rows. The original one have this config to 10k.
So I change the config in this branch to 10k, and solved the problem.
Thank you , have a good one.