The storage location capabilities in Vertica have been greatly enhanced as of release 6.1.0! (BTW, a storage location simply refers to a location on disk)
Starting in Vertica 6.1.0 we can now do the following exciting tasks:
- 1. Label storage locations using the ALTER_LOCATION_LABEL function to change the label.
2. Create database object level storage policies using the SET_OBJECT_STORAGE_POLICY function where the database object types include database, schemas, tables, and partition ranges. Nice!
3. Monitor storage policies via the V_MONITOR.STORAGE_POLICIES system table
Prior to release 6.1.0, Vertica stores columns included in the projection sort order on the fastest storage locations. Columns not included in the projection sort order are stored on slower disks. Columns for each projection are ranked as follows:
- 1. Columns in the sort order are given the highest priority (numbers > 1000).
2. The last column in the sort order is given the rank number 1001.
3. The next-to-last column in the sort order is given the rank number 1002, and so on until the first column in the sort order is given 1000 + # of sort columns.
4. The remaining columns are given numbers from 1000–1, starting with 1000 and decrementing by one per column.
Note that we use the MEASURE_LOCATION_PERFORMANCE and SET_LOCATION_PERFORMANCE functions to tell Vertica which locations are the fastest.
For instance, to measure and set the performance of the location “/mnt/vertica/data” on node 1 we could perform the following two commands:
Code: Select all
dbadmin=> SELECT MEASURE_LOCATION_PERFORMANCE('/mnt/vertica/data' , 'v_testdata1_node0001');
WARNING 3914: measure_location_performance can take a long time to execute. Please check logs for progress
MEASURE_LOCATION_PERFORMANCE
-------------------------------------------------
Throughput : 177 MB/sec. Latency : 44 seeks/sec
(1 row)
dbadmin=> SELECT SET_LOCATION_PERFORMANCE('/mnt/vertica/data','v_testdata1_node0001','177','44');
SET_LOCATION_PERFORMANCE
--------------------------------------------------
/mnt/vertica/data performance statistics stored.
(1 row)
Code: Select all
dbadmin=> SELECT ALTER_LOCATION_LABEL('/mnt/vertica/data', '', 'SAN');
ALTER_LOCATION_LABEL
----------------------------------
/mnt/vertica/data label changed.
(1 row)
dbadmin=> SELECT ALTER_LOCATION_LABEL('/usr/local/data/testdata1/v_testdata1_node0001_data', 'v_testdata1_node0001', 'LOCAL');
ALTER_LOCATION_LABEL
--------------------------------------------------------------------
/usr/local/data/testdata1/v_testdata1_node0001_data label changed.
(1 row)
dbadmin=> SELECT ALTER_LOCATION_LABEL('/usr/local/data/testdata1/v_testdata1_node0002_data', 'v_testdata1_node0002', 'LOCAL');
ALTER_LOCATION_LABEL
--------------------------------------------------------------------
/usr/local/data/testdata1/v_testdata1_node0002_data label changed.
(1 row)
dbadmin=> SELECT ALTER_LOCATION_LABEL('/usr/local/data/testdata1/v_testdata1_node0003_data', 'v_testdata1_node0003', 'LOCAL');
ALTER_LOCATION_LABEL
--------------------------------------------------------------------
/usr/local/data/testdata1/v_testdata1_node0003_data label changed.
(1 row)
Code: Select all
dbadmin=> SELECT node_name, location_path, location_usage, location_label, throughput, latency
dbadmin-> FROM storage_locations;
node_name | location_path | location_usage | location_label | throughput | latency
----------------------+-----------------------------------------------------+----------------+----------------+------------+---------
v_testdata1_node0001 | /usr/local/data/testdata1/v_testdata1_node0001_data | DATA,TEMP | LOCAL | 147 | 380
v_testdata1_node0001 | /mnt/vertica/data | DATA | SAN | 177 | 44
v_testdata1_node0002 | /usr/local/data/testdata1/v_testdata1_node0002_data | DATA,TEMP | LOCAL | 170 | 86
v_testdata1_node0002 | /mnt/vertica/data | DATA | SAN | 121 | 40
v_testdata1_node0003 | /usr/local/data/testdata1/v_testdata1_node0003_data | DATA,TEMP | LOCAL | 160 | 100
v_testdata1_node0003 | /mnt/vertica/data | DATA | SAN | 170 | 96
(6 rows)
For the first example, there are no storage policies in place:
Code: Select all
dbadmin=> SELECT * FROM storage_policies;
schema_name | object_name | policy_details | location_label
-------------+-------------+----------------+----------------
(0 rows)
Code: Select all
dbadmin=> CREATE TABLE test_no_sp (col1 INT) KSAFE 1;
CREATE TABLE
dbadmin=> INSERT INTO test_no_sp VALUES (1);
OUTPUT
--------
1
(1 row)
Code: Select all
dbadmin=> SELECT p.projection_name,
dbadmin-> sc.node_name,
dbadmin-> sc.storage_type,
dbadmin-> sc.location_label
dbadmin-> FROM projections p
dbadmin-> LEFT JOIN storage_containers sc
dbadmin-> ON sc.projection_name = p.projection_name
dbadmin-> WHERE p.anchor_table_name = 'test_no_sp';
projection_name | node_name | storage_type | location_label
-----------------+----------------------+--------------+----------------
test_no_sp_b0 | v_testdata1_node0002 | WOS |
test_no_sp_b1 | v_testdata1_node0003 | WOS |
(2 rows)
We can force the data to move to disk with the DO_TM_TASK function:
Code: Select all
dbadmin=> SELECT DO_TM_TASK('MOVEOUT', 'test_no_sp');
DO_TM_TASK
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Task: moveout
(Table: testdata1_wh.test_no_sp) (Projection: testdata1_wh.test_no_sp_b0)
(Table: testdata1_wh.test_no_sp) (Projection: testdata1_wh.test_no_sp_b1)
(1 row)
Code: Select all
dbadmin=> SELECT p.projection_name,
dbadmin-> sc.node_name,
dbadmin-> sc.storage_type,
dbadmin-> sc.location_label
dbadmin-> FROM projections p
dbadmin-> LEFT JOIN storage_containers sc
dbadmin-> ON sc.projection_name = p.projection_name
dbadmin-> WHERE p.anchor_table_name = 'test_no_sp';
projection_name | node_name | storage_type | location_label
-----------------+----------------------+--------------+----------------
test_no_sp_b0 | v_testdata1_node0002 | ROS | LOCAL
test_no_sp_b1 | v_testdata1_node0003 | ROS | SAN
(2 rows)
In the next example, we’ll create a storage policy for a table and see which location is used!
Code: Select all
dbadmin=> CREATE TABLE test_sp (col1 INT) KSAFE 1;
CREATE TABLE
dbadmin=> SELECT SET_OBJECT_STORAGE_POLICY('test_sp', 'SAN');
SET_OBJECT_STORAGE_POLICY
----------------------------
Object storage policy set.
(1 row)
dbadmin=> SELECT * FROM storage_policies;
schema_name | object_name | policy_details | location_label
--------------+-------------+----------------+----------------
testdata1_wh | test_sp | Table | SAN
(1 row)
dbadmin=> INSERT INTO test_sp VALUES (1);
OUTPUT
--------
1
(1 row)
dbadmin=> SELECT DO_TM_TASK('MOVEOUT', 'test_sp');
DO_TM_TASK
--------------------------------------------------------------------------------------------------------------------------------------------------------
Task: moveout
(Table: testdata1_wh.test_sp) (Projection: testdata1_wh.test_sp_b0)
(Table: testdata1_wh.test_sp) (Projection: testdata1_wh.test_sp_b1)
(1 row)
dbadmin=> SELECT p.projection_name,
dbadmin-> sc.node_name,
dbadmin-> sc.storage_type,
dbadmin-> sc.location_label
dbadmin-> FROM projections p
dbadmin-> LEFT JOIN storage_containers sc
dbadmin-> ON sc.projection_name = p.projection_name
dbadmin-> WHERE p.anchor_table_name = 'test_sp';
projection_name | node_name | storage_type | location_label
-----------------+----------------------+--------------+----------------
test_sp_b1 | v_testdata1_node0003 | ROS | SAN
test_sp_b0 | v_testdata1_node0002 | ROS | SAN
(2 rows)
Have fun!