ROS-Container, high number of transactions

Katrin_Pfistere

Hallo, we use Vertica for the last 3 months. We have the problem with "Too many ROS-Container" for some tables. The data is hourly refreshed with delete, insert into ... as select and update statements. (direct option is never used.) Now we want to introduce a new table. We expect 20,000 writing transactions per second and also 20,000 reading transactions per second. Data is never loaded with the copy or copy direct command. Can Vertica handle this without having the "Too many ROS-container"-problem. We need a stable system for this table. If the "Too many ROS-container" problem would occur for this table, we would not be able to insert or update any data for while and this would cost a lot of money... What do you think? Another question. I read a few about the ROS-Container. When are the ROS-Container created? - are they created for every statement, for every transaction Best regards. Katrin

Navin_C

Hello Katrin, Can Vertica handle this without having the "Too many ROS-container"-problem. Too many ROS containers problem Due to heavy data load conditions, there are too many ROS containers. This occurs when the Tuple Mover falls behind in performing mergeout operations. The resulting excess number of ROS containers can exhaust all available system resources. To prevent this, Vertica automatically rolls back all transactions that would load data until the Tuple Mover has time to catch up. Probable Solution over this You need to tweak your Tuple Mover operation settings You can query the TUPLE_MOVER_OPERATIONS table to monitor mergeout activity. However, the Tuple Mover does not immediately start a mergeout when a projection reaches the limit of ROS containers, so you may not see a mergeout in progress when receiving this error. If waiting for a mergeout does not resolve the error, the problem probably is related to insufficient RAM.. A good rule of thumb is that system RAM in MB divided by 6 times the number of columns in the largest table should be greater than 10. For example, for a 100 column table you would want at least 6GB of RAM (6144MB / (6 * 100) = Try increasing the mergeout interval time. This will merge your containers fast based on the data loading rate as specified by you. Also I would suggest you using the COPY command which is build for bulk data loading. When are the ROS-Container created? The ROS containers are created when data is inserted. If your table has 3 partitions based on a key column, then there will be 3 ROS containers created for this table. Hope this helps.

Katrin_Pfistere

So you do not recommend to use Vertica for our new table? We have 3 node system, with k-safety = 2. So we would create 20,000 ROS-Conatiner * 2 every second?

[Deleted User]

Navin: Thanks for the detailed reply! Katrin: More specifically, when you insert data, by default that data is added to the (in-memory) WOS. The Tuple Mover's MoveOut process periodically takes all new data in the WOS and writes a new ROS container. (If the WOS fills, each insert goes straight to a new ROS container; that's often a cause of ROS issues.) If you use COPY DIRECT, that also goes straight to ROS. Take a look through these sections of the doc: https://my.vertica.com/docs/6.1.x/HTML/index.htm#2624.htm https://my.vertica.com/docs/6.1.x/HTML/index.htm#14363.htm Regarding 20,000 transactions per second, though -- I won't say it's impossible, but I will say that Vertica is heavily tuned to be a data-warehouse database (meaning, make very large queries as fast as possible), not an OLTP database (ie., churn through zillions of small updates). We take week-long queries and run them in seconds; we also take some millisecond-long queries and run them in seconds :-) And 20k transactions/sec is not exactly a small number even for an OLTP database, depending on your hardware and on the contents of each transaction. So I'd strongly encourage you to run some feasibility tests on this, to verify that your cluster is able to sustain this workload in general (independent of ROS accumulation). Incidentally -- I'm curious, why are you running a 3-node system at k=2? k=2 will decrease your insert performance because of the extra projection, and won't give you any benefit over k=1 on a 3-node cluster.

[Deleted User]

(For k-safety -- ah, are you thinking of k=1 as "no safety"? That's k=0. k=1 means one level of redundancy, ie., two copies of the data.)

Sharon_Cutter

Katrin, Have you checked the logs to see if all or most of your 20000 inserts are overflowing from WOS to ROS? How large is your WOS? What's the partitioning granularity of the table? Having too many partitions, and also loading across many partitions at once, can lead to "too many ROS containers". --Sharon

Katrin_Pfistere

Hallo Sharon, We haven't created the table for the 20,000 transactions yet. We would just know, if it this would be possible with Vertica. Because of the responses our architects decided yesterday to use antoher db. Thanks for your help. Best regards. Katrin

Chris_Fischer

Why do people ask "I want to screw in a nail, is this hammer suitable?"