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'][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/dbadmin= CREATE LOCATION '/u03/data_store1/'
ALL NODES
USAGE 'DATA'
LABEL 'DataCollector';
CREATE LOCATIONdbadmin= 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[root@DCXLG001 /]# cat /proc/meminfo | grep MemTotal | awk '{ print $2 }'
8190956dbadmin= 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.
[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.
