System table - DISK_STORAGE

Hi,

The DISK_STORAGE system table returns statistics on the file system, not Vertica's usage of it. Vertica won't populate the THROUGHPUT and LATENCY columns automatically. You have to do it manually with the SET_LOCATION_PERFORMANCE function!

See:
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/StorageLocations/SettingLocationPerformance.htm

Example:

dbadmin=> SELECT node_name, storage_path, throughput, latency FROM disk_storage WHERE storage_usage = 'DATA,TEMP';
     node_name      |                 storage_path                  | throughput | latency
--------------------+-----------------------------------------------+------------+---------
 v_test_db_node0001 | /home/dbadmin/test_db/v_test_db_node0001_data |          0 |       0
 v_test_db_node0002 | /home/dbadmin/test_db/v_test_db_node0002_data |          0 |       0
 v_test_db_node0003 | /home/dbadmin/test_db/v_test_db_node0003_data |          0 |       0
(3 rows)

dbadmin=> SELECT MEASURE_LOCATION_PERFORMANCE('/home/dbadmin/test_db/v_test_db_node0002_data', 'v_test_db_node0002');
WARNING 3914:  measure_location_performance can take a long time to execute. Please check logs for progress
          MEASURE_LOCATION_PERFORMANCE
------------------------------------------------
 Throughput : 23 MB/sec. Latency : 46 seeks/sec
(1 row)

dbadmin=> SELECT node_name, storage_path, throughput, latency FROM disk_storage WHERE storage_usage = 'DATA,TEMP';
     node_name      |                 storage_path                  | throughput | latency
--------------------+-----------------------------------------------+------------+---------
 v_test_db_node0001 | /home/dbadmin/test_db/v_test_db_node0001_data |          0 |       0
 v_test_db_node0002 | /home/dbadmin/test_db/v_test_db_node0002_data |          0 |       0
 v_test_db_node0003 | /home/dbadmin/test_db/v_test_db_node0003_data |          0 |       0
(3 rows)

dbadmin=> SELECT SET_LOCATION_PERFORMANCE('/home/dbadmin/test_db/v_test_db_node0002_data', 'v_test_db_node0002', '23', '46');
                           SET_LOCATION_PERFORMANCE
------------------------------------------------------------------------------
 /home/dbadmin/test_db/v_test_db_node0002_data performance statistics stored.
(1 row)

dbadmin=> SELECT node_name, storage_path, throughput, latency FROM disk_storage WHERE storage_usage = 'DATA,TEMP';
     node_name      |                 storage_path                  | throughput | latency
--------------------+-----------------------------------------------+------------+---------
 v_test_db_node0001 | /home/dbadmin/test_db/v_test_db_node0001_data |          0 |       0
 v_test_db_node0002 | /home/dbadmin/test_db/v_test_db_node0002_data |         23 |      46
 v_test_db_node0003 | /home/dbadmin/test_db/v_test_db_node0003_data |          0 |       0
(3 rows)

Note that I am getting terrible throughput and latency values 'cause my example above was performed on a 3 node Vertica cluster running on my laptop

Thanks, shame it does not automatically update the DISK_STORAGE table.

Hi,

I think having the MEASURE_LOCATION_PERFORMANCE automatically update the THROUGHPUT and LATENCY columns in the DISK_STORAGE system would be a bad idea. That is, what if the results of MEASURE_LOCATION_PERFORMANCE for a particular run are skewed because of some unknown disk activity on a particular storage location? You'd set the values to a value lower than typical. Prior to setting the values in DISK_STORAGE, I execute MEASURE_LOCATION_PERFORMANCE at various times over a 24 our period (via cron), then take the average of those runs.

Thanks!