The function that measures the storage location performance is named MEASURE_LOCATION_PERFORMANCE while the function that is used to record the performance information is named SET_LOCATION_PERFORMANCE.
Basically you have to perform a two step process. First measure a location performance and second record the values returned from step one.
You can query the DISK_STORAGE table to get a list of storage locations. You only need to measure and set the performance ratings for data locations.
Code: Select all
dbadmin=> SELECT node_name, storage_path FROM disk_storage WHERE node_name = 'v_intersect_node0001' AND storage_usage = 'DATA';
node_name | storage_path
----------------------+-------------------------------------------------
v_intersect_node0001 | /data1/data/intersect/v_intersect_node0001_data
v_intersect_node0001 | /data2/data/intersect/v_intersect_node0001_data
(2 rows)
Code: Select all
dbadmin=> SELECT MEASURE_LOCATION_PERFORMANCE('/data1/data/intersect/v_intersect_node0001_data', 'v_intersect_node0001');
WARNING: measure_location_performance can take a long time. Please check logs for progress
MEASURE_LOCATION_PERFORMANCE
---------------------------------------------------
Throughput : 634 MB/sec. Latency : 1200 seeks/sec
(1 row)
dbadmin=> SELECT SET_LOCATION_PERFORMANCE('/data1/data/intersect/v_intersect_node0001_data', 'v_intersect_node0001', 634, 1200);
SET_LOCATION_PERFORMANCE
--------------------------------------------------------------------------------
/data1/data/intersect/v_intersect_node0001_data performance statistics stored.
(1 row)
[code]dbadmin=> SELECT MEASURE_LOCATION_PERFORMANCE('/data2/data/intersect/v_intersect_node0001_data', 'v_intersect_node0001');
WARNING: measure_location_performance can take a long time. Please check logs for progress
MEASURE_LOCATION_PERFORMANCE
---------------------------------------------------
Throughput : 218 MB/sec. Latency : 240 seeks/sec
(1 row)
dbadmin=> SELECT SET_LOCATION_PERFORMANCE('/data2/data/intersect/v_intersect_node0001_data', 'v_intersect_node0001', 218, 240);
SET_LOCATION_PERFORMANCE
--------------------------------------------------------------------------------
/data2/data/intersect/v_intersect_node0001_data performance statistics stored.
(1 row)
Also, Vertica isn't kidding with the warning message stating that the measurement process can take a long time, especially with larger and slower disks!Try to run it during non-peek hours. When I ran the MEASURE_LOCATION_PERFORMANCE in the above examples, it took about 5 minutes for the first 500 GB location and 20 minutes for the second 1.5 TB location.