WOS and ROS

[usli06]

What are the differences between the WOS and the ROS? What are they used for and if ROS is faster why use WOS? Any why do these acronyms remind me of characters from the TV show friends?

[JimKnicely]

Hi,

The ROS isn't "faster" than the WOS per se, it's just more organized

The Write Optimized Store (WOS) is stored in memory (RAM) while the Read Optimized Store (ROS) is stored on your hard disk. Typically the WOS should be faster for read and write operations, but since its RAM you have a lot less of it. To further increase the performance of data loads into the WOS, Vertica stores the data in it without any type of sorting, compression or indexing. The ROS on the other hand is a highly optimized via compression and indexing. Since the ROS is so highly organized we experience the best performance from it when reading the massive amounts of table data we'd expect to retrieve from a data warehouse.

If you execute a standard INSERT, UPDATE, DELETE or COPY (without the DIRECT hint) command any affected data will be stored in the WOS. But if you execute an INSERT with the /*+ direct */ hint or a COPY (with the DIRECT hint) affected data will be stored "directly" in the ROS.

Note that a component of Vertica called the Tuple Mover (TM) migrates data from memory (WOS) to disk (ROS).

Let's run through an example of how data moves into and out of the WOS and ROS. I'll create a table named WOS_ROS_TEST for the exercise:

Code: Select all

dbadmin=> create table wos_ros_test (pk int, name varchar(100));
CREATE TABLE

Next I'll add some records using standard INSERT statements:

Code: Select all

dbadmin=> insert into wos_ros_test values (1, 'Jim');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into wos_ros_test values (2, 'Jane');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into wos_ros_test values (3, 'Josh');
 OUTPUT
--------
      1
(1 row)

We can query the PROJECTION_STORAGE system table to find out if the data for this table is stored in the WOS or the ROS:

Code: Select all

dbadmin=> select node_name, wos_row_count, ros_row_count from projection_storage where anchor_table_name = 'wos_ros_test';
      node_name       | wos_row_count | ros_row_count
----------------------+---------------+---------------
 v_intersect_node0001 |             3 |             0
 v_intersect_node0002 |             3 |             0
 v_intersect_node0003 |             3 |             0
(3 rows)

The above query shows that all three records are stored in the WOS (the wos_row_count column = 3).

Now I'll add more records to the table table, but this time I'll include the DIRECT hint:

Code: Select all

dbadmin=> insert /*+ direct */ into wos_ros_test values (4, 'Peng');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert /*+ direct */ into wos_ros_test values (5, 'Joe');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert /*+ direct */ into wos_ros_test values (6, 'Pert');
 OUTPUT
--------
      1
(1 row)

Once again, we can query the PROJECTION_STORAGE table to find out where these new records are stored:

Code: Select all

dbadmin=> select node_name, wos_row_count, ros_row_count from projection_storage where anchor_table_name = 'wos_ros_test';
      node_name       | wos_row_count | ros_row_count
----------------------+---------------+---------------
 v_intersect_node0001 |             3 |             3
 v_intersect_node0002 |             3 |             3
 v_intersect_node0003 |             3 |             3
(3 rows)

The above query shows that the three new records were stored directly to the ROS (ros_row_count = 3)!

So how does data move from the WOS to the ROS? It's the job of the Tuple Mover process. There are several system parameters that control when the Tuple Mover does its thing but I'll leave a discussion of those for another post. In the mean time, if we don't want to wait around, we can force the Tuple Mover to move the data for a table:

Code: Select all

dbadmin=> select do_tm_task('moveout', 'wos_ros_test');
                                   do_tm_task               
-------------------------------------------------------------------------------------
 Task: moveout
(Table: public.wos_ros_test) (Projection: public.wos_ros_test_node0001)
(Table: public.wos_ros_test) (Projection: public.wos_ros_test_node0002)
(Table: public.wos_ros_test) (Projection: public.wos_ros_test_node0003)

(1 row)

dbadmin=> select node_name, wos_row_count, ros_row_count from projection_storage where anchor_table_name = 'wos_ros_test';
      node_name       | wos_row_count | ros_row_count
----------------------+---------------+---------------
 v_intersect_node0001 |             0 |             6
 v_intersect_node0002 |             0 |             6
 v_intersect_node0003 |             0 |             6
(3 rows)

Now all the records are happily stored in the ROS on disk (ros_row_count = 6).

[nnani]

Excellent and made easy by knicely

One more difference I would like to mention

WOS is Row store
ROS is Columnar Store

WOS is mostly used for Trickle loading
ROS is mostly used for Bulk Loading

sometimes Vertica is also refered as a Hybrid Database as it has a Row store and a Column store.

[rajasekhart]

hi guys,

Once you made a bulk load, vertica initially stores it in WOS, where the data gets stored in unoptimised way.

But after some time , it performs MOVEOUT operation automatically.
And then , the data retrieval becomes faster. (Correct me if i am wrong).

Now , my question is after how much time , it would move the data from WOS to ROS??
And if i do tuple mover operation , does it makes my database faster (Without running the database designer)???

PLease help me..


Thanks,
Raj!!

[id10t]

Hi!

>> Once you made a bulk load, vertica initially stores it in WOS...
Not exactly, depends on what type of bulk load you choosed: AUTO/DIRECT/TRICKLE.
http://en.wikipedia.org/wiki/Bulk_insert
(for Vertica it's COPY statement only)

• AUTO/DIRECT/TRICKLE
  https://my.vertica.com/docs/6.1.x/HTML/ ... m#1668.htm
  Specifies the method COPY uses to load data into the database. The default load method is AUTO, in which COPY loads data into the WOS (Write Optimized Store) in memory. When the WOS is full, the load continues directly into ROS (Read Optimized Store) on disk.
• Loading Directly into WOS (AUTO)
  https://my.vertica.com/docs/6.1.x/HTML/ ... #17615.htm
  This is the default load method. If you do not specify a load option, COPY uses the AUTO method to load data into WOS (Write Optimized Store in memory). The default method is good for smaller bulk loads (< 100MB). Once WOS is full, COPY continues loading directly to ROS (Read Optimized Store on disk) containers.
• WOS Overflow
  https://my.vertica.com/docs/6.1.x/HTML/ ... m#4983.htm
  The WOS exists to allow Vertica to efficiently batch small loads into larger ones for I/O purposes. Loading to the WOS is fast because the work of sorting, encoding, and writing to disk is deferred and performed in the background by the Tuple Mover's moveout process. Since the WOS has a finite amount of available space, it can fill up and force Vertica to spill small loads directly to disk. While no data is lost or rejected when the WOS gets full, it can result in wasted I/O bandwidth. Thus, follow the Tuning the Tuple Mover guidelines to avoid WOS overflow.
• Tuple Mover Configuration Parameters
  https://my.vertica.com/docs/6.1.x/HTML/ ... #14361.htm
  The following configuration parameters control how the Tuple Mover operates. You can use them to adjust its operation to suit your needs, as described in the following sections.
  • ActivePartitionCount
  • MergeOutInterval
  • MoveOutInterval
  • MoveOutMaxAgeTime
  • MoveOutSizePct

PS
>> PLease help me..
Google,Wiki and Vertica Docs - your are best friends, try to find answer by yourself, don't try just to get the answer("and you will find that world much more interesting and your knowledge increased")

[Mrao]

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?

[id10t]

Hi!

[DELETED]