Using the SQL Monitoring API

Vertica provides an API for monitoring various features and functions within a database in the form of virtual tables that can be queried using a limited form of the SELECT statement, as described below.

You can use external tools to query the virtual tables and act upon the information as desired. For example, you can a third-party monitoring tool to periodically query the K-Safety level of the database. If it falls below the desired level indicating a host failure, the monitoring tool can use any means necessary to notify the database administrator and/or appropriate IT personnel.

The virtual tables that make up the monitoring API are described in the SQL Reference Manual. They are:

Virtual Table	Description
VT_COLUMN_STORAGE	monitors the amount of disk storage used by each column of each projection on each node.
VT_DISK_STORAGE	monitors the amount of disk storage used by the database on each node.
VT_LOAD_STREAMS	monitors load metrics for each load stream on each node.
VT_PROJECTION_STORAGE	monitors the amount of disk storage used by each projection on each node.
VT_QUERY_METRICS	monitors the sessions and queries executing on each node.
VT_RESOURCE_USAGE	monitors system resource management on each node.
VT_SESSION	monitors external sessions.
VS_SESSION	monitors internal and external sessions on each node.
VT_SYSTEM	monitors the overall state of the database.
VT_TABLE_STORAGE	monitors the amount of disk storage used by each table on each node.
VT_TUPLE_MOVER	monitors the status of the Tuple Mover on each node. The tuple mover is the component of Vertica that moves the contents of the Write Optimized Store (WOS) into the Read Optimized Store (ROS). This data movement is known as a moveout. Normally, the tuple mover runs automatically in the background at preset intervals and is referred to as the ATM. The ROS (Read Optimized Store) is a highly optimized, read-oriented, physical storage structure that is organized by projection and that makes heavy use of compression and indexing. You can use the COPY...DIRECT and INSERT (with direct hint) statements to load data directly into the ROS. Compression is the process of transforming data into a more compact format. Compressed data cannot be directly processed; it must first be decompressed. Vertica uses integer packing for unencoded integers and LZO for compressible data. Although compression is generally considered to be a form of encoding, the terms have different meanings in Vertica. LZO is an abbreviation for Lempel-Ziv-Oberhumer. It is a data compression algorithm that is focused on decompression speed. The algorithm is lossless and the reference implementation is thread safe. The WOS (Write Optimized Store) is a memory-resident data structure into which INSERT, UPDATE, DELETE, and COPY (without DIRECT hint) actions are recorded. Like the ROS, the WOS is arranged by projection but it stores tuples without sorting, compression, or indexing and thus supports very fast load speeds. The WOS organizes data by epoch and holds uncommitted transaction data. Compression is the process of transforming data into a more compact format. Compressed data cannot be directly processed; it must first be decompressed. Vertica uses integer packing for unencoded integers and LZO for compressible data. Although compression is generally considered to be a form of encoding, the terms have different meanings in Vertica. LZO is an abbreviation for Lempel-Ziv-Oberhumer. It is a data compression algorithm that is focused on decompression speed. The algorithm is lossless and the reference implementation is thread safe. The ROS (Read Optimized Store) is a highly optimized, read-oriented, physical storage structure that is organized by projection and that makes heavy use of compression and indexing. You can use the COPY...DIRECT and INSERT (with direct hint) statements to load data directly into the ROS. Compression is the process of transforming data into a more compact format. Compressed data cannot be directly processed; it must first be decompressed. Vertica uses integer packing for unencoded integers and LZO for compressible data. Although compression is generally considered to be a form of encoding, the terms have different meanings in Vertica. LZO is an abbreviation for Lempel-Ziv-Oberhumer. It is a data compression algorithm that is focused on decompression speed. The algorithm is lossless and the reference implementation is thread safe.

Query Syntax

Virtual table queries use a different processing mechanism than database queries Thus, the virtual tables support only a very limited set of query capabilities:

Only the FROM Clause is allowed in the SELECT statement.
Historical queries are not allowed, including AT EPOCH LATEST.
Vertica can execute a query from a snapshot of the database taken at a specific date and time. The syntax is:

AT TIME 'timestamp' SELECT...

The command queries all data in the database up to and including the epoch representing the specified date and time without holding a lock or blocking write operations.

An epoch represents committed changes to the data stored in a database between two specific points in time. In other words, an epoch contains all COPY, INSERT, UPDATE, and DELETE operations that have been executed and committed since the end of the previous epoch.

When a cluster is the Recovering state, the server refuses connection requests and thus cannot be monitored using the SQL monitoring API.

Examples

SELECT CURRENT_EPOCH, K_SAFETY FROM VT_SYSTEM;

SELECT * FROM VT_RESOURCE_USAGE;

SELECT NODE, TOTAL_USER_SESSIONS, TOTAL_QUERIES_EXECUTED FROM VT_QUERY_METRICS;