Vertica table size !!!!

[adrian.oprea]

Why is that ?
I am no expert in Vertica not even a begginer i consider myself !!!
I am getting the same values !! from both using projection_storage(used_bytes) and column_storage(ros_used_bytes)!!
Is not acurate ?? why so ?

I mean i should go and sleep on Vertica books for a week or two , but since we can use the forum as a learning poratl let's share the knowladge
Thanks !!

P.S. - sorry for my bad english !!

[rajasekhart]

Hi all,

IMHO
Projection will be created on a table only when you insert atleast one record into that.

Thanks,
Raj.

[JimKnicely]

Hi,

My hope is that this site is all about helping out each other! Here's what I've learned about this topic...

Vertica has a hybrid storage model. That is, there are two types of storage: the WOS (Write Optimized Store) and the ROS (Read Optimized Store). The WOS is in memory while the ROS is on disk. Data in the WOS and ROS are both arranged by projections, however, the data in the WOS is not sorted, not encoded and not compressed whereas in the ROS the opposite is true in that the data is sorted, encoded and compressed. A background process called the Tuple Mover migrates data from the WOS to the ROS peridically or on demand.

Why are there two storage areas? Its beacause Vertica wants users to have access to data as soon as its inserted. It takes time to sort, encode and compress the data. While the data is the WOS users can query it immediatiatly after it is inserted via the INSERT command or the COPY command (without the DIRECT option). Otherwise, there'd be a delay!

Adrian, here is an example that may help out in understanding the process...

Code: Select all

dbadmin=> create table test_sum (col1 varchar(100));
CREATE TABLE
dbadmin=> select sysdate, projection_name, row_count, used_bytes, wos_row_count, wos_used_bytes, ros_row_count, ros_used_bytes from projection_storage where anchor_table_name = 'test_sum';
 sysdate | projection_name | row_count | used_bytes | wos_row_count | wos_used_bytes | ros_row_count | ros_used_bytes
---------+-----------------+-----------+------------+---------------+----------------+---------------+----------------
(0 rows)

There are no projections for the table yet. rajasekhart was correct when he said that a projection will be created when data is inserted into the table:

Code: Select all

dbadmin=> insert into test_sum values ('This is a test');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select sysdate, projection_name, row_count, used_bytes, wos_row_count, wos_used_bytes, ros_row_count, ros_used_bytes from projection_storage where anchor_table_name = 'test_sum';
          sysdate           | projection_name | row_count | used_bytes | wos_row_count | wos_used_bytes | ros_row_count | ros_used_bytes
----------------------------+-----------------+-----------+------------+---------------+----------------+---------------+----------------
 2012-06-28 11:30:54.314266 | test_sum_super  |         1 |      16384 |             1 |          16384 |             0 |              0
(1 row)

Vertica created a "super" projection for us (this is the projection that sKwa mentioned). In the query above we see that the data was inserted into the WOS. Notice the size (used_bytes = wos_used_bytes). This data is uncompressed.

I can force the data to be moved from the WOS to ROS with the DO_TM_TASK function:

Code: Select all

dbadmin=> select do_tm_task('moveout', 'test_sum');
                                       do_tm_task
-----------------------------------------------------------------------------------------
 Task: moveout
(Table: intersect_wh.test_sum) (Projection: intersect_wh.test_sum_super)

(1 row)

dbadmin=> select sysdate, projection_name, row_count, used_bytes, wos_row_count, wos_used_bytes, ros_row_count, ros_used_bytes from projection_storage where anchor_table_name = 'test_sum';
          sysdate           | projection_name | row_count | used_bytes | wos_row_count | wos_used_bytes | ros_row_count | ros_used_bytes
----------------------------+-----------------+-----------+------------+---------------+----------------+---------------+----------------
 2012-06-28 11:31:05.033298 | test_sum_super  |         1 |         88 |             0 |              0 |             1 |             88
(1 row)

Now check out the size of the data in the ROS! It is now compressed! Notice that used_bytes = ros_used_bytes.

Let's insert another record. It'll go into the WOS:

Code: Select all

dbadmin=> insert into test_sum values ('This is another test...');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select sysdate, projection_name, row_count, used_bytes, wos_row_count, wos_used_bytes, ros_row_count, ros_used_bytes from projection_storage where anchor_table_name = 'test_sum';
          sysdate           | projection_name | row_count | used_bytes | wos_row_count | wos_used_bytes | ros_row_count | ros_used_bytes
----------------------------+-----------------+-----------+------------+---------------+----------------+---------------+----------------
 2012-06-28 11:31:24.439148 | test_sum_super  |         2 |      16472 |             1 |          16384 |             1 |             88
(1 row)

We have uncompressed data in the WOS and compressed data in the ROS. Now notice that used_bytes = wos_used_bytes + ros_used_bytes.

Finally, I'll move the data out of the WOS into the ROS:

Code: Select all

dbadmin=> select do_tm_task('moveout', 'test_sum');
                                       do_tm_task
-----------------------------------------------------------------------------------------
 Task: moveout
(Table: intersect_wh.test_sum) (Projection: intersect_wh.test_sum_super)

(1 row)

dbadmin=> select sysdate, projection_name, row_count, used_bytes, wos_row_count, wos_used_bytes, ros_row_count, ros_used_bytes from projection_storage where anchor_table_name = 'test_sum';
          sysdate           | projection_name | row_count | used_bytes | wos_row_count | wos_used_bytes | ros_row_count | ros_used_bytes
----------------------------+-----------------+-----------+------------+---------------+----------------+---------------+----------------
 2012-06-28 11:31:31.173348 | test_sum_super  |         2 |        184 |             0 |              0 |             2 |            184
(1 row)

We're left with all compressed data in the ROS.

Like sKwa suggested, use the solution that Josh gave... That is, sum the used_bytes column in the projection_storage table. Since it's always the wos_used_bytes + ros_used_bytes you'll get a complete result

Does this make sense?

I hope this helps! Guys, let me know if I made any mistakes so that we can all learn!

[adrian.oprea]

Thx knicely87 !!! Great explaining !!

[rajasekhart]

Hi Jim,

Thats a great explanation from you,

Thanks a lot , today i have learned many new points abt the storage..

Thank you very much.
Raj.

[adrian.oprea]

There you go !! my question bring more people to the table !!
I will stick around !!