Skip to end of metadata
Go to start of metadata

磁盘空间

--查看表使用的磁盘空间
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;


调整数据类型

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
--? SELECT build_flextable_view('cities_flex');


物化弹性表

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;
--projection 信息
SELECT is_segmented, is_aggregate_projection, has_statistics, is_super_projection, count(*) FROM projections GROUP by 1,2,3,4;
--update信息
--? 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;
--活跃的events
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 DESC LIMIT 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;


  • No labels