How is table data stored on disk?
Posted: Wed Mar 07, 2012 2:02 pm
Hello, Does anyone know how Vertica stores the data in tables on disk? The directories in the file system are just a bunch of confusing numbers. Thanks.
User 2 User Support
http://www.vertica-forums.com/
Code: Select all
dbadmin=> select storage_path from v_monitor.disk_storage where storage_usage = 'DATA';
storage_path
-------------------------------------------------
/data1/data/intersect/v_intersect_node0001_data
/data2/data/intersect/v_intersect_node0001_data
(2 rows)
Code: Select all
dbadmin=> select projection_name, row_count, ros_count from v_monitor.projection_storage where anchor_table_name = 'holiday_dim';
projection_name | row_count | ros_count
----------------------+-----------+-----------
holiday_dim_node0001 | 60 | 1
holiday_dim_node0002 | 60 | 1
holiday_dim_node0003 | 60 | 1
(3 rows)
Code: Select all
dbadmin=> select storage_type, storage_oid from v_monitor.storage_containers where projection_name = 'holiday_dim_node0001';
storage_type | storage_oid
--------------+-------------------
ROS | 45035996274585219
(1 row)
Code: Select all
bash-3.2$ pwd
/data1/data/intersect/v_intersect_node0001_data
bash-3.2$ find ./ -name 45035996274585219
./219/45035996274585219
bash-3.2$ cd 219/45035996274585219
bash-3.2$ ls -lrt
total 8
-rw------- 1 dbadmin dbadmin 48 Mar 21 22:31 45035996274585219_0.pidx
-rw------- 1 dbadmin dbadmin 134 Mar 21 22:31 45035996274585219_0.fdb
Code: Select all
dbadmin=> create table test1 (col1 int, col2 varchar2(100), primary key (col1)) partition by col1;
CREATE TABLE
dbadmin=> insert into test1 values (1, 'Pittsburgh');
OUTPUT
--------
1
(1 row)
dbadmin=> insert into test1 values (2, 'Toronto');
OUTPUT
--------
1
(1 row)
dbadmin=> insert into test1 values (3, 'Dallas');
OUTPUT
--------
1
(1 row)
Code: Select all
dbadmin=> select projection_name, row_count, ros_count from v_monitor.projection_storage where anchor_table_name = 'test1';
projection_name | row_count | ros_count
-----------------+-----------+-----------
test1_node0001 | 3 | 3
test1_node0002 | 3 | 3
test1_node0003 | 3 | 3
(3 rows)
Code: Select all
dbadmin=> select storage_type, storage_oid from v_monitor.storage_containers where projection_name = 'test1_node0001';
storage_type | storage_oid
--------------+-------------------
ROS | 45035996276572379
ROS | 45035996276572389
ROS | 45035996276572399
(3 rows)
Code: Select all
verticatst01:/usr/local/data/intersect/v_intersect_node0001_data $ ls -lrt ./379/45035996276572379
total 8
-rw------- 1 dbadmin dbadmin 48 Apr 13 07:17 45035996276572379_0.pidx
-rw------- 1 dbadmin dbadmin 3 Apr 13 07:17 45035996276572379_0.fdb
verticatst01:/usr/local/data/intersect/v_intersect_node0001_data $ ls -lrt ./389/45035996276572389
total 8
-rw------- 1 dbadmin dbadmin 48 Apr 13 07:17 45035996276572389_0.pidx
-rw------- 1 dbadmin dbadmin 3 Apr 13 07:17 45035996276572389_0.fdb
verticatst01:/usr/local/data/intersect/v_intersect_node0001_data $ ls -lrt ./399/45035996276572399
total 8
-rw------- 1 dbadmin dbadmin 48 Apr 13 07:17 45035996276572399_0.pidx
-rw------- 1 dbadmin dbadmin 3 Apr 13 07:17 45035996276572399_0.fdb
Code: Select all
[dbadmin@BK 277]$ ls -lrt
total 4
-rw------- 1 dbadmin verticadba 438 Dec 16 13:50 0267ea83948962386adc79978bf46adc00a0000000024745_0.gt