Monitoring the database

This page includes a collection of general-purpose SQL statements useful for monitoring your database.

Check disk space

Check disk space used by tables.

=> SELECT projection_schema, anchor_table_name, to_char(sum(used_bytes)/1024/1024/1024,'999,999.99')
as disk_space_used_gb FROM
projection_storage
GROUP by projection_schema, anchor_table_name ORDER by
disk_space_used_gb desc limit 50;

Check total disk space used.

=> SELECT to_char(sum(used_bytes)/1024/1024/1024,'999,999.99') AS gb FROM projection_storage;

Check the amount of free disk space.

=> SELECT to_char(sum(disk_space_free_mb)/1024,'999,999,999') AS
disk_space_free_gb, to_char(sum(disk_space_used_mb)/1024,'999,999,999') AS
disk_space_used_gb FROM disk_storage;

Adjust data types

Change the Zip and Population columns from VARCHAR to INT.

=> UPDATE cities_flex_keys set data_type_guess='int' WHERE key_name='Zip';
=> UPDATE cities_flex_keys set data_type_guess='int' WHERE key_name='Population';
=> COMMIT;

Refresh the cities_flex_view with the new data types

=> SELECT build_flextable_view('cities_flex');

Materialize the flex table

Materialize the flex table and all columns into a persistent Vertica table.

=> CREATE TABLE cities AS SELECT * from cities_flex_view;

View user and role information

View user information.

=> SELECT user_name, is_super_user, resource_pool, memory_cap_kb, temp_space_cap_kb, run_time_cap FROM users;

Identify users.

=> SELECT * FROM user_sessions;

View queries by user.

=> SELECT * FROM query_profiles WHERE user_name ILIKE '%dbadmin%';

View roles.

=> SELECT * FROM roles;

View database information

View resource pool assignments.

=> SELECT user_name, resource_pool FROM users;

View table information.

=> SELECT table_name, is_flextable, is_temp_table, is_system_table, count(*) FROM tables GROUP by 1,2,3,4;

View projection information.

=> SELECT is_segmented, is_aggregate_projection, has_statistics, is_super_projection, count(*) FROM projections GROUP by 1,2,3,4,5;

View update information.

=> SELECT substr(query, 0, instr(query, '')+1) count(*) FROM (SELECT transaction_id, statement_id, upper(query::varchar(30000)) as query FROM query_profiles
WHERE regexp_like(query,'^\s*update\s','i')) sq GROUP BY 1 ORDER BY 1;

View active events.

=> SELECT * FROM active_events WHERE event_problem_description NOT ILIKE '%state to UP';

View backups.

=> SELECT * FROM database_backups;

View disk storage.

=> SELECT node_name, storage_path, storage_usage, storage_status, disk_space_free_percent FROM disk_storage;

View long-running queries

=> SELECT query_duration_us/1000000/60 AS query_duration_mins, table_name, user_name, processed_row_count AS rows_processed, substr(query,0,70) FROM query_profiles
ORDER BY query_duration_us DESCLIMIT 250;

View sizes and counts of Read Optimized Store (ROS) containers.

=> SELECT node_name, projection_name, sum(ros_count), sum(ros_used_bytes) FROM projection_storage GROUP BY 1,2 HAVING sum(ros_count) >= 50
ORDER BY 3 DESC LIMIT 250;

View license information

View license consumption.

=> SELECT GET_COMPLIANCE_STATUS();

View how the database complies with your license.

=> SELECT AUDIT('');

Audit the database to check if it complies with raw storage allowance of your license.

=> SELECT AUDIT_LICENSE_SIZE;

Compare storage size of database the database and your license.

=> SELECT /*+(license_utilization)*/
audit_start_timestamp,
database_size_bytes / (1024^3) AS database_size_gb,
license_size_bytes / (1024^3) AS license_size_gb, usage_percent
FROM v_catalog.license_audits ORDER BYaudit_start_timestamp DESC LIMIT 30;