Vertica management and monitoring

vertica_monit.sql

-- List of schemas
\dn

-- list all tables
\dt

-- list only tables with "public" schema 
\dt public.*

-- database size
SELECT GET_COMPLIANCE_STATUS();

-- raw data size at the schema level
-- https://dba.stackexchange.com/a/65118/23608
-- http://vertica.tips/2014/01/24/license-audit-utilization-raw-size/
SELECT /*+ label(estimated_raw_size)*/
       pj.anchor_table_schema, 
       pj.used_compressed_gb, 
       pj.used_compressed_gb * la.ratio AS raw_estimate_gb 
FROM   (SELECT ps.anchor_table_schema, 
               SUM(used_bytes) / ( 1024^3 ) AS used_compressed_gb 
        FROM   v_catalog.projections p 
               JOIN v_monitor.projection_storage ps 
                 ON ps.projection_id = p.projection_id 
        WHERE  p.is_super_projection = 't' 
        GROUP  BY ps.anchor_table_schema) pj 
       CROSS JOIN (SELECT (SELECT database_size_bytes 
                           FROM   v_catalog.license_audits 
                           ORDER  BY audit_start_timestamp DESC 
                           LIMIT  1) / (SELECT SUM(used_bytes) 
                                        FROM   V_MONITOR.projection_storage) AS ratio) la 
ORDER  BY pj.used_compressed_gb DESC;

-- compressed size is the actual size of the data on the disk
SELECT /*+ label(compressed_table_size)*/
       anchor_table_schema, 
       anchor_table_name, 
       SUM(used_bytes) / ( 1024^3 ) AS used_compressed_gb 
FROM   v_monitor.projection_storage 
GROUP  BY anchor_table_schema, 
          anchor_table_name 
ORDER  BY SUM(used_bytes) DESC;

-- Monitoring CPU Usage
-- https://my.vertica.com/blog/vertica-quick-tip-monitoring-cpu-usage/
SELECT start_time,
AVG(average_cpu_usage_percent) AS avg_cpu_usage
FROM v_monitor.cpu_usage
WHERE start_time BETWEEN NOW() - INTERVAL '24 hours' AND NOW()
GROUP BY start_time
ORDER BY AVG(average_cpu_usage_percent) DESC
LIMIT 5;