WOS and ROS

Moderator: NorbertKrupa

deepakalevoor
Newbie
Newbie
Posts: 1
Joined: Thu Aug 14, 2014 12:08 pm

Re: WOS and ROS

Post by deepakalevoor » Thu Aug 14, 2014 12:37 pm

Hi,

I followed advice of @knicely87 and created a test table and inserted 3 records to my test table. For testing pupose I have set MoveOutInterval and MoveOutMaxAgeTime as 60 and 120 secs respectively in Configuration_Parameters
But still the Tuple Mover has not moved the contents from WOS to ROS after the time has elapsed on what is set in MoveOutInterval and MoveOutMaxAgeTime
Can you please on when the actual write from WOS to ROS happens and also on how to identify whether a particular record in table is read from WOS or ROS?

Also what is the meaning of "DMMoveout" in operation_name and plan_type?

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: WOS and ROS

Post by NorbertKrupa » Thu Aug 14, 2014 7:05 pm

How are you verifying that the moveout hasn't happened? Did you commit the rows you tried inserting?
Checkout vertica.tips for more Vertica resources.

rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Re: WOS and ROS

Post by rajasekhart » Sat Oct 18, 2014 10:20 am

Hi Deepak,

I guess, the move out will happen at the given moveout interval only after the MoveOutSizePct reaches the given threshold limit.

Can you give a try after making the below setting.

SELECT SET_CONFIG_PARAMETER('MoveOutSizePct', 0);

Thank you,
Raj!!
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

User avatar
BoMBaY
Beginner
Beginner
Posts: 26
Joined: Tue Jul 16, 2013 5:45 am

Re: WOS and ROS

Post by BoMBaY » Mon Oct 20, 2014 3:46 am

Mrao wrote:Good discussion.

What is the benefit of having WOS? Why do not we load it directly into ROS? I understand that WOS is for trickle feed and ROS is for bulk load. Why can not Vertica have just ROS?
Vertica has limit on number of storage containers per projection. You can only have 1024 ROS containers per projection.
You can monitor number ROS by check ros_count in projection_storage system table.

The benefit of WOS is assume that you have a lot of table and you trickle load to ROS every 15 second with few records on many tables and Mergeout process can't handle his job quick enough. You will face the ROS limitation.
Itipong Chewinpipat (Bay)
DBA Specialist (Vertica/Oracle)

Image
ImageImage

sumitkchawla
Newbie
Newbie
Posts: 2
Joined: Thu Mar 26, 2015 8:03 pm

Re: WOS and ROS

Post by sumitkchawla » Thu Mar 26, 2015 8:14 pm

Hi

I am facing a peculiar issue with Merge command. I assume it to be related to WOS\ROS. Initially, I am loading close to 100K rows into a target table. Immediately after the initial load, I am running a unit test which updates the datasource and then tries to run a merge command from source to target table. If the update size is bigger like 30-40K, the merge ends up adding duplicate rows into table. The problem does not happen if the update size is smaller like 3-4K. I assume that Tuple mover has not happened, and probably some spill from WOS to ROS is causing this data inconsistency issue. Can somebody explain the reason here? Would having a larger WOS would help? Or what other configuration parameters need to be tweaked in order to avert this issue.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: WOS and ROS

Post by NorbertKrupa » Thu Mar 26, 2015 11:40 pm

sumitkchawla wrote:Hi

I am facing a peculiar issue with Merge command. I assume it to be related to WOS\ROS. Initially, I am loading close to 100K rows into a target table. Immediately after the initial load, I am running a unit test which updates the datasource and then tries to run a merge command from source to target table. If the update size is bigger like 30-40K, the merge ends up adding duplicate rows into table. The problem does not happen if the update size is smaller like 3-4K. I assume that Tuple mover has not happened, and probably some spill from WOS to ROS is causing this data inconsistency issue. Can somebody explain the reason here? Would having a larger WOS would help? Or what other configuration parameters need to be tweaked in order to avert this issue.
I would try optimizing your query before changing configuration parameters. What have you done to optimize your query? Have you tried using an update instead of merge? Have you tried optimizing projections for delete?

Vertica is not OLTP. You should carefully consider the need for updates.
Checkout vertica.tips for more Vertica resources.

sumitkchawla
Newbie
Newbie
Posts: 2
Joined: Thu Mar 26, 2015 8:03 pm

Re: WOS and ROS

Post by sumitkchawla » Fri Mar 27, 2015 3:27 am

Here is my situation. I am constantly polling a postgres database, and trying to keep Vertica data in sync. Vertica tables are denormalized form of multiple postgres tables.

Merge queries are quite simple. I copy differential data from OLTP into a temp table, and then merge the temp table into target table using the merge key.Merge key is same as the Primary key for the table. Not sure how "Update and Insert" would work differently here? Would it not have the same issue when trying to look for a match with Primary key?

I am a newbie to Vertica. I don't understand what is optimizing projections for delete? We are at very early stage of using Vertica. We have not created any additional projections from table.

Post Reply

Return to “New to Vertica”