Create new storage location in HP Vertica database.

The dbadmin user must have full privileges on the directory in order to create the location. Syntax:

CREATE LOCATION 'path'
    {NODE 'nodename' | ALL NODES}
    [SHARED]
    [USAGE 'usetype']
    [LABEL 'labelname']
First create the directory on the host and give ownership to the dbadmin and his group.
[root@DCXLG001 u03]# mkdir data_store1
[root@DCXLG001 u03]# chown -R dbadmin:vertica /u03/data_store1/
chown: invalid group: dbadmin:vertica'
[root@DCXLG001 u03]# chown -R dbadmin:verticadba /u03/data_store1/
 Create  the storage location inside the database
dbadmin= CREATE LOCATION '/u03/data_store1/'
     ALL NODES
     USAGE 'DATA'
     LABEL 'DataCollector';
CREATE LOCATION
  Check the locations
dbadmin= select * from storage_locations;
    location_id    |  node_name   |           location_path           | location_usage | is_retired | location_label | rank | throughput | latency
-------------------+--------------+-----------------------------------+----------------+------------+----------------+------+------------+---------
 45035996273704982 | v_d_node0001 | /home/dbadmin/d/v_d_node0001_data | DATA,TEMP      | f          |                |    0 |          0 |       0
 45035996273757994 | v_d_node0001 | /u03/data_store1                  | DATA           | f          | DataCollector  |    0 |          0 |       0
Measure the location performance Important to know that you need RAM*2 free space available in a storage location to measure its performance. To find the total RAM on your host use the command bellow:
[root@DCXLG001 /]# cat /proc/meminfo | grep MemTotal | awk '{ print $2 }'
8190956
-so i will need around 16Gb of free space in order to measure the performance of the DataCollector location.
dbadmin= SELECT MEASURE_LOCATION_PERFORMANCE('/u03/data_store1','v_d_node0001');
WARNING 3914:  measure_location_performance can take a long time to execute. Please check logs for progress
          MEASURE_LOCATION_PERFORMANCE
-------------------------------------------------
 Throughput : 71 MB/sec. Latency : 818 seeks/sec
  • to monitor the execution of the performance measurement check the space usage on you disk.
Another way to do so is by using the opt/vertica/bin/vertica -m
[dbadmin@DCXLG001 ~]$ /opt/vertica/bin/vertica -m /u03/data_store1

Path:/u03/data_store1 Throughput:71 (MiB/sec) Latency:799 (seeks/sec)

 Setup Storage location performance

dbadmin= select SET_LOCATION_PERFORMANCE (  '/u03/data_store1'  , 'v_d_node0001' , '71' , '818'  );
            SET_LOCATION_PERFORMANCE
-------------------------------------------------
 /u03/data_store1 performance statistics stored.
(1 row)

dbadmin= select * from storage_locations;
    location_id    |  node_name   |           location_path           | location_usage | is_retired | location_label | rank | throughput | latency
-------------------+--------------+-----------------------------------+----------------+------------+----------------+------+------------+---------
 45035996273704982 | v_d_node0001 | /home/dbadmin/d/v_d_node0001_data | DATA,TEMP      | f          |                |    1 |          0 |       0
 45035996273757994 | v_d_node0001 | /u03/data_store1                  | DATA           | f          | DataCollector  |    0 |         71 |     818
(2 rows)

 Why is important to specify the storage performance details in Vertica.

-By providing this type of information to Vertica we assume a tiered storage approach, were your "hot data"(most used data) will be stored on faster disks and regular data will be stored on normal disks. This approach will work well when you have SSD disks allocated to your Vertica Storage.
  • Vertica internal engine will allocate the faster disks to store the columns that are present in the sort order of the projections and the columns that are not in the sort order will reside on the slower disks.
  • Also it will follow the each column rank as specified by the user.