Vertica Tiered Storage Policies

In Vertica we have the option to associate database objects with a specific storage location. This is very useful when you have different types of disks available on your Vertica Cluster hosts. Example: - imagine you have on your Vertica Cluster nodes SSD disks(fast disks) and some other HDD disks used for storing your database data. In this case you could place your hot objects(frequently accessed) on your SSD disks and the other objects(infrequently accessed) in the HDD disks locations. To see how to create a Storage Location follow this link. Note: - if no storage policy exists on the database then Vertica will continue to use any storage available in the default way. Once you create a storage policy it is not necessary to allocate all objects to it, this can be done arbitrary as per your need. Important: -Storage policy have a higher priority than the storage ranking setting. The storage ranking is based on your storage performance measurements.

We can add storage policies to the following objects:

  • Database
  • Schema
  • Table
  • Partition
List your Storage locations and their labels:
dbadmin= select location_path,location_label from storage_locations;
           location_path           | location_label
-----------------------------------+----------------
 /home/dbadmin/d/v_d_node0001_data |
 /u03/data_store1                  | DataCollector
 /u02/load/data_store2             | FAST_SSD_DISK
  • see we have a location labeled FAST_SSD_DISK, this a fast disk we provided to our Vertica node.
Let's create a table and set its Storage Policy to the FAST_SSD_DISK label.
 dbadmin= create table one (id int);
CREATE TABLE

dbadmin=  select set_object_storage_policy ('one', 'FAST_SSD_DISK');
 set_object_storage_policy
----------------------------
 Object storage policy set.
(1 row)

 Once we insert new data into the table the ROS containers will be directed to the FAST_SSD_DISK storage.

dbadmin= insert/*+ direct*/ into one values(1);
 OUTPUT
--------
      1
(1 row)

Query the storage_containers to see the location of the containers that belong to the one table.

dbadmin= select  distinct projection_name, storage_type, location_label from v_monitor.storage_containers;
    projection_name     | storage_type | location_label
------------------------+--------------+----------------
 one_super              | ROS          | FAST_SSD_DISK
 store_sales_fact_super | ROS          |

Also take a look in the operation system data directory.

[root@DCXLG001 data_store2]# tree /u02/load/data_store2
/u02/load/data_store2
+-- 375
+-- 379
+-- 383
¦   +-- 45035996273771383
¦       +-- 45035996273771383_0.fdb
¦       +-- 45035996273771383_0.pidx
+-- 387
    +-- 45035996273771387
        +-- 45035996273771387_0.fdb
        +-- 45035996273771387_0.pidx

6 directories, 4 files

To see the storage policies that are active on your you can query the storage_policies table

dbadmin= select * from v_monitor.storage_policies;
 schema_name | object_name | policy_details | location_label
-------------+-------------+----------------+----------------
 public      | one         | Table          | FAST_SSD_DISK
(1 row)

 Also to view the storage tiers available on your Vertica Cluster use the query bellow.

dbadmin= select * from v_monitor.storage_tiers;
 location_label | node_count | location_count | ros_container_count | total_occupied_size
----------------+------------+----------------+---------------------+---------------------
 DataCollector  |          1 |              1 |                   0 |                   0
 FAST_SSD_DISK  |          1 |              1 |                   1 |                  51
(2 rows)

Copyright 2020. All Rights Reserved