How to measure disk IO speed in Vertica

In Vertica we need to know what are our I/O speeds on the disks used for the database.

Why do we need to measure your I/O speeds? -if you have multiple storage locations on the same node. Vertica will use this knowledge later when determining where (preferably on the fastest location) to store the data that is accessed the most.

How can we measure I/O speeds in Vertica?
  • First 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.

    SELECT node_name, storage_path FROM disk_storage WHERE node_name = 'node_name' AND storage_usage = 'DATA,TEMP';
 node_name     |                  storage_path
---------------+-------------------------------------------------
 node name     |                  full path
 node name     |                  full path
  • Next step use the MEASURE_LOCATION_PERFORMANCE function from Vertica.
    •  SELECT MEASURE_LOCATION_PERFORMANCE('<full path>? ?node name>?;
WARNING:  measure_location_performance can take a long time. Please check logs for progress
           MEASURE_LOCATION_PERFORMANCE
---------------------------------------------------
 Throughput :200xxx MB/sec. Latency : 100xxx seeks/sec
  • After you have discovered the disk I/O performance use the SET_LOCATION_PERFORMANCE function to inform Vertica of the disk I/O performance.
    • SELECT SET_LOCATION_PERFORMANCE('<full path>? ?node name>?< Throughput value >,< Latency value>);
                            SET_LOCATION_PERFORMANCE
--------------------------------------------------------------------------------
 <full path> performance statistics stored.

    Note:

    -this should not be done at peak time ,since is time/resource consuming. >

    Copyright 2020. All Rights Reserved