Long mergeouts after replicating 1 table to another database.

bmurrell

I'm running v12.0.4-26.
We have a load in Prod. As soon as it completes we replicate this to UAT.
In Prod, mergeouts occurs during the load, and minimally immediately afterwards.
It takes 1.5 hours to replicate to UAT.
As soon at it completes, UAT starts the mergeouts for 3 hours with high CPU.

I've noticed that MaxMrgOutROSSizeMB i set in Prod, at 20480, but not set in UAT.
I assume that's why but have no history of why that was set.

Would that be the cause?

Bryan_H

Could you clarify the replicate process? Are you using vbr or another method to replicate?

bmurrell

Hi Brian_H, this is a vbr replicate.

Bryan_H

My guess is that you're replicating a snapshot of unmerged ROS containers, which are then merged out all at once in UAT, rather than incrementally during load and replication on Prod. Check tuple_mover_operations and compare timing of mergeout events per projection to see if that's the case. I would set MaxMrgOutROSSizeMB identically since UAT may be trying to merge out to a single infinite container, where Prod stops when a ROS container reaches or exceeds 20 GB.

bmurrell

This morning before the replicate completed I set MaxMrgOutROSSizeMB on UAT to the same as Prod.
Once the replicate completed, there was a small uptick in CPU for 5 minutes, so setting this the same as Prod has fixed the issue.
I collect CPU and mergeout data into grafana and can see it used to mergeout continuously for 3 hours with high CPU.

My only question here is ... if MaxMrgOutROSSizeMB is set to 20Gb, does that mean each file is a ROS Container, counting towards the 1024 limit per projection per node ?

scherepanov

Limit is on ROS container count per projection, not on file count.
ROS count limit is not as strict as it used to be - you can potentially see over 3000 ROS containers in (poorly managed) projection and it will still be working.
ROS can have many files, generally speaking there is one file per column per projections (Vertica merges small files into one, making it hard to count files). And there is a positional index files that are also per column per projection, they are also being merged if small.
There is no limit on number of files per ROS container. You do have a limit on number of columns in table, and it is rather high, something like few thousands.
MaxMrgOutROSSizeMB is for total size of ROS, that includes all files.