Redshift系统信息表

Redshift system table and views包括：

  • SVV views contain information about database objects with references to transient STV tables.
  • SYS views are used to monitor query and workload usage for provisioned clusters and serverless workgroups.
  • STL views are generated from logs that have been persisted to disk to provide a history of the system.
  • STV tables are virtual system tables that contain snapshots of the current system data. They are based on transient in-memory data and are not persisted to disk-based logs or regular tables.
  • SVCS views provide details about queries on both the main and concurrency scaling clusters.
  • SVL views provide details about queries on main clusters.

STL视图

STL系统视图来自于Redshift的日志文件，记录了整个系统的历史。这些日志文件保存在每个redshift节点上，STL视图从这些文件获取信息，转化成视图给管理员用

保留时长：STL系统视图只保留最近7天。如果想保留更久，需要定期的将其copy到其他表，或者unload到S3.

查看数据表磁盘使用情况

查看每个node的磁盘使用情况：

SELECT
  owner AS node,
  diskno,
  used,
  capacity,
  used/capacity::numeric * 100 as percent_used
FROM stv_partitions
WHERE host = node
ORDER BY 1, 2;

image-20221104181328889

Used、capacity单位都是MB。


查看每张表占用空间大小：

SELECT
  name,
  count(*)
FROM stv_blocklist
JOIN (SELECT DISTINCT name, id as tbl from stv_tbl_perm) USING (tbl)
GROUP BY name;

image-20221104181445159

单位都是MB。