监控数据库

此页面包含一组对监控数据库有用的通用 SQL 语句。

检查磁盘空间

检查表使用的磁盘空间。

=> 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;

检查已使用的总磁盘空间。

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

检查可用磁盘空间量。

=> 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;

调整数据类型

将 Zip 和 Population 列从 VARCHAR 更改为 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;

用新的数据类型刷新 cities_flex_view

=> SELECT build_flextable_view('cities_flex');

实体化 Flex 表

将 Flex 表和所有列实体化为持久 Vertica 表。

=> CREATE TABLE cities AS SELECT * from cities_flex_view;

查看用户和角色信息

查看用户信息。

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

标识用户。

=> SELECT * FROM user_sessions;

按用户查看查询。

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

查看角色。

=> SELECT * FROM roles;

查看数据库信息

查看资源池分配。

=> SELECT user_name, resource_pool FROM users;

查看表信息。

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

查看投影信息。

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

查看更新信息。

=> 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;

查看活动事件。

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

查看备份。

=> SELECT * FROM database_backups;

查看磁盘存储。

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

查看长时间运行的查询

=> 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;

查看读取优化存储 (ROS) 容器的大小和计数。

=> 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;

查看许可证信息

查看许可证使用情况。

=> SELECT GET_COMPLIANCE_STATUS();

查看数据库如何符合您的许可证。

=> SELECT AUDIT('');

审核数据库以检查它是否符合许可证的原始存储限额。

=> SELECT AUDIT_LICENSE_SIZE();

比较数据库和您的许可证的存储大小。

=> 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;