在Hologres中如何查看錶統計資訊並分析 - Hologres

Hologres從 V1.3版本開始提供表統計資訊日誌系統資料表（hologres.hg_table_info）按日收集執行個體內表的統計資訊，協助您對執行個體中的表資訊進行查看、分析，以便您可以根據這些資訊採取最佳化措施。本文將會介紹在Hologres中如何查看錶統計資訊並分析。

使用限制

僅Hologres V1.3及以上版本支援查看錶統計資訊，如果您的執行個體是V1.3以下版本，請您使用自助升級或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。
hologres.hg_table_info表的產出時效是T+1，當天的資料大概會在第二天淩晨5點前更新完成。Hologres執行個體從 V1.1版本升級到 V1.3版本的當天不會產出表統計資訊，查詢時提示：meta warehouse store currently not available，需要在升級後的第二天查詢表統計資訊。

注意事項

表統計資訊日誌預設保留30天的資料。
對於Hologres非分區內部表（type='TABLE'），可以查到詳細統計資訊，如儲存空間、檔案數、訪問累積次數和行記錄數。
對於其他對象（視圖，物化視圖，外表，分區父表），只能查到基本資料，如分區數量，外表對應外部表格名，物化視圖與視圖定義等。
hologres.hg_table_info表屬於Hologres的元倉系統，hologres.hg_table_info查詢失敗不會影響執行個體中的業務Query運行，故hologres.hg_table_info表的穩定性不在產品的SLA保護範圍內。

hg_table_info表

hg_table_info表主要包含的欄位資訊如下。

說明

表統計資訊日誌儲存在hologres.hg_table_info系統資料表裡，執行個體升級到 V1.3版本後，將會預設按天採集表資訊。
部分欄位存在值為空白的情況，屬於歷史建立的表未能統計到建立資訊導致，執行個體升級到V1.3版本後建立的表可以統計到資訊。

欄位	類型	描述	說明
db_name	text	表所在資料庫名。	無
schema_name	text	表所在Schema名。	無
table_name	text	表名。	無
table_id	text	表的唯一標識，外部表格ID使用db.schema.table。	無
type	text	表類型，包括： TABLE：普通表和分區子表。 PARTITION TABLE：分區父表。 FOREIGN TABLE：外表。 VIEW：視圖。 MATERIALIZED VIEW：物化視圖。	當type類型為VIEW時，create_time、last_ddl_time欄位為空白。當type類型為VIEW、FOREIGN TABLE或PARTITION TABLE時，last_modify_time、last_access_time、hot_file_count、cold_file_count、total_read_count、total_write_count欄位值為空白，無記錄。
partition_spec	text	分區條件（分區子表有效）。	無
is_partition	boolean	是否是分區子表。	無
owner_name	text	表Owner的使用者名稱，可與hg_query_log的usename列做join。	無
create_time	timestamp with time zone	表的建立時間。	無
last_ddl_time	timestamp with time zone	最後一次更新表資訊的時間。	無
last_modify_time	timestamp with time zone	最後一次更新表資訊的時間。	無
last_access_time	timestamp with time zone	表最後的訪問時間。	無
view_def	text	視圖的定義。	只對視圖有效。
comment	text	表或視圖的描述資訊。	無
hot_storage_size	bigint	表佔用的熱存空間，單位：Byte。	hg_table_info查詢儲存量大小與使用pg_relation_size查詢存在差異屬於正常情況。原因是hg_table_info資訊按天上報的，且pg_relation_size不包含binlog儲存大小。
code_storage_size	bigint	表佔用的冷存空間，單位：Byte。	hg_table_info查詢儲存量大小與使用pg_relation_size查詢存在差異屬於正常情況。原因是hg_table_info資訊按天上報的，且pg_relation_size不包含Binlog儲存大小。
hot_file_count	bigint	表的熱存檔案數。	無
cold_file_count	bigint	表的冷存檔案數。	無
table_meta	jsonb	原始的Meta資訊，格式為JSONB。	無
row_count	bigint	表或者分區的行記錄數。	如果是分區父表，row_count為所有子表的總行數。
collect_time	timestamp with time zone	資料上報的採集時間。	無
partition_count	bigint	分區子表數量。	只有表為分區父表時有效。
parent_schema_name	text	分區子表的父表Schema名。	只有表為分區子表時有效。
parent_table_name	text	分區子表的父表表名。	只有表為分區子表時有效。
total_read_count	bigint	累計讀表次數（非精確，SELECT，INSERT，UPDATE，DELETE 均會導致次數增加）。	非精確值，不建議使用。
total_write_count	bigint	累計寫表次數（非精確，INSERT，UPDATE，DELETE 均會導致次數增加）。	非精確值，不建議使用。
read_sql_count_1d	bigint	T-1日（0-24點，+8時區）表的讀取總次數。	僅3.0版本開始支援。如果是分區表，SQL命中了具體的分區子表，僅子表採集資料，父表不採集。
write_sql_count_1d	bigint	T-1日（0-24點，+8時區）表的寫入總次數。	僅3.0版本開始支援。如果是分區表，SQL命中了具體的分區子表，僅子表採集資料，父表不採集。

授予查看許可權

表統計資訊日誌需要有一定的許可權才能查看，其許可權規則和授權方式說明如下。

查看Hologres執行個體所有資料庫的表統計資訊日誌。
- 授予使用者Superuser許可權。
  Superuser帳號可以查看Hologres執行個體所有資料庫的表統計資訊日誌，給使用者授予Superuser使用者的許可權，使使用者有許可權查看執行個體所有資料庫的表統計資訊日誌。
```
--將“雲帳號ID”替換為實際使用者名稱。如果是RAM使用者，帳號ID前需要添加“p4_”。
ALTER USER "雲帳號ID" SUPERUSER;
```
- 將使用者添加到pg_stat_scan_table使用者組。
  除Superuser外，Hologres還支援通過設定使用者組pg_stat_scan_tables（V1.3.44以前版本）或pg_read_all_stats（V1.3.44及以上版本）查看所有DB表統計資訊日誌，普通使用者如果需要查看所有日誌，可以聯絡Superuser授權加入該使用者組。授權命令如下。
```
-- V1.3.44以前版本
GRANT pg_stat_scan_tables TO "雲帳號ID";--專家許可權模型授權
CALL spm_grant('pg_stat_scan_tables', '雲帳號ID');  -- SPM許可權模型
CALL slpm_grant('pg_stat_scan_tables', '雲帳號ID'); -- SLPM許可權模型

-- V1.3.44及以上版本
GRANT pg_read_all_stats TO "雲帳號ID";--專家許可權模型授權
CALL spm_grant('pg_read_all_stats', '雲帳號ID');  -- SPM許可權模型
CALL slpm_grant('pg_read_all_stats', '雲帳號ID'); -- SLPM許可權模型
```
查看本資料庫的表統計資訊日誌。
開啟簡單許可權模型（SPM）或基於Schema層級的簡單許可權模型（SLPM），將使用者加入db_admin使用者組，db_admin角色可以查看本資料庫的表統計資訊日誌。
說明
普通使用者只能查詢當前帳號對應資料庫下自己為Owner的表統計資訊日誌。
```
CALL spm_grant('<db_name>_admin', '雲帳號ID');  -- SPM許可權模型
CALL slpm_grant('<db_name>.admin', '雲帳號ID'); -- SLPM許可權模型
```

查詢表統計資訊趨勢的SQL命令

情境1：查看Holo內表的訪問趨勢

-- 執行個體所有內表的趨勢變化：佔用儲存空間、檔案數、讀取次數，寫入次數，行記錄數
SELECT
  db_name,
  schema_name,
  table_name,
  collect_time :: date AS collect_date,
  hot_storage_size,
  cold_storage_size,
  hot_file_count,
  cold_file_count,
  read_sql_count_1d,
  write_sql_count_1d,
  row_count
FROM
  hologres.hg_table_info
WHERE
  collect_time > (current_date - interval '1 week')::timestamptz -- 近一周
  AND type ='TABLE'
  ORDER  BY  collect_date desc ;

情境2：查看佔用磁碟空間較大表的訪問情況

-- 查看佔用磁碟空間最大的 (10) 個表的訪問情況
SELECT
  db_name,
  schema_name,
  table_name,
  hot_storage_size + cold_storage_size AS total_storage_size,
  row_count,
  sum(read_sql_count_1d) AS total_read_count,
  sum(write_sql_count_1d) AS total_write_count
FROM
  hologres.hg_table_info
WHERE
  collect_time > (current_date - interval '1 week')::timestamptz -- 近一周
  AND type = 'TABLE'
  AND (
    cold_storage_size IS NOT NULL
    OR hot_storage_size IS NOT NULL
  )
GROUP BY db_name,schema_name,table_name,total_storage_size,row_count
ORDER BY total_storage_size DESC
LIMIT 10;

情境3：查看儲存top10的表的訪問趨勢和資料量變化趨勢

-- 執行個體儲存top 10的表（已昨天統計值為準）近一周訪問趨勢和儲存量、資料量變化趨勢
WITH top10_table AS (SELECT
  db_name,
  schema_name,
  table_name,
  hot_storage_size + cold_storage_size AS total_storage_size
FROM
  hologres.hg_table_info
WHERE
  collect_time >= (current_date - interval '1 day')::timestamptz -- 昨天
  AND collect_time < current_date
  AND type = 'TABLE'
  AND ( cold_storage_size IS NOT NULL OR hot_storage_size IS NOT NULL )
ORDER BY total_storage_size DESC
LIMIT 10
)
SELECT
  base.db_name,
  base.schema_name,
  base.table_name,
  base.hot_storage_size + cold_storage_size AS total_storage_size,
  base.row_count,
  base.read_sql_count_1d,
  base.write_sql_count_1d,
  base.collect_time :: date AS collect_date
FROM
  hologres.hg_table_info AS base
LEFT JOIN
  top10_table
ON
  base.db_name = top10_table.db_name
  AND base.schema_name = top10_table.schema_name
  AND base.table_name = top10_table.table_name
WHERE
  collect_time > (current_date - interval '1 week')::timestamptz -- 近一周
  AND type = 'TABLE'
  AND ( cold_storage_size IS NOT NULL OR hot_storage_size IS NOT NULL )
ORDER BY total_storage_size DESC , collect_date DESC;

情境4：查看儲存最少的表的訪問趨勢和資料量變化

-- 執行個體儲存最少的10個表（已昨天統計值為準）近一周訪問趨勢和儲存量、資料量變化趨勢
WITH top10_table AS (SELECT
  db_name,
  schema_name,
  table_name,
  hot_storage_size + cold_storage_size AS total_storage_size
FROM
  hologres.hg_table_info
WHERE
  collect_time >= (current_date - interval '1 day')::timestamptz -- 昨天
  AND collect_time < current_date
  AND type = 'TABLE'
ORDER BY total_storage_size ASC LIMIT 10
)
SELECT
  base.db_name,
  base.schema_name,
  base.table_name,
  base.hot_storage_size + cold_storage_size AS total_storage_size,
  base.row_count,
  base.read_sql_count_1d,
  base.write_sql_count_1d,
  base.collect_time :: date AS collect_date
FROM
  hologres.hg_table_info AS base
RIGHT JOIN
  top10_table
ON
  base.db_name = top10_table.db_name
  AND base.schema_name = top10_table.schema_name
  AND base.table_name = top10_table.table_name
WHERE
  collect_time > (current_date - interval '1 week')::timestamptz -- 近一周
  AND type = 'TABLE'
ORDER BY total_storage_size ASC  , collect_date DESC ;

情境5：查詢小檔案過多而導致佔用磁碟空間大的表

-- 查看每個表的檔案數和佔用磁碟大小，並按平均檔案大小排序
-- table group 只能顯示當前 db 的 shard count，其它 db 的顯示為空白
SELECT
  db_name,
  schema_name,
  table_name,
  cold_storage_size + hot_storage_size AS total_storage_size,
  cold_file_count + hot_file_count AS total_file_count,
  (cold_storage_size + hot_storage_size) / (cold_file_count + hot_file_count) AS avg_file_size,
  tmp_table_info.table_meta ->> 'table_group' AS table_group,
  tg_info.shard_count
FROM
  hologres.hg_table_info tmp_table_info
  LEFT JOIN (
    SELECT
      tablegroup_name,
      property_value AS shard_count
    FROM
      hologres.hg_table_group_properties
    WHERE
      property_key = 'shard_count'
  ) tg_info ON tmp_table_info.table_meta ->> 'table_group' = tg_info.tablegroup_name
WHERE
  collect_time > (current_date - interval '1 day')::timestamptz
  AND type = 'TABLE'
  AND (
    cold_storage_size IS NOT NULL
    OR hot_storage_size IS NOT NULL
  )
  AND (
    cold_file_count IS NOT NULL
    OR hot_file_count IS NOT NULL
  )
  AND cold_file_count + hot_file_count <> 0
ORDER BY avg_file_size;

情境6：查看錶最近一次修改表資料那天的行數變化

-- 查看錶最近一次修改時間，相對前一次修改時間 的總共修改資料量
-- 當執行個體表數量很大時，建議對 CTE tmp_table_info 做過濾，以免因拉資料太大而導致查詢時間太久
WITH tmp_table_info AS (
  SELECT
    db_name,
    schema_name,
    table_name,
    row_count,
    collect_time,
    last_modify_time
  FROM
    hologres.hg_table_info
  WHERE
    last_modify_time IS NOT NULL
    AND type = 'TABLE'
    -- 在這裡對 tmp_table_info 做一些過濾
    -- 如 collect_time > (current_date - interval '14 day'):: timestamptz
    -- 如 table_name like ''
    -- 如 type = 'PARTITION'
)
SELECT
  end_data.db_name AS db_name,
  end_data.schema_name AS schema_name,
  end_data.table_name AS table_name,
  (end_data.row_count - start_data.row_count) AS modify_row_count,
  end_data.row_count AS current_rows,
  end_data.last_modify_time AS last_modify_time
FROM
  (
    SELECT
      db_name,
      schema_name,
      table_name,
      row_count,
      last_modify_time
    FROM
      tmp_table_info
    WHERE
      collect_time > (current_date - interval '1 day')::timestamptz -- 查詢昨天記錄的表的最後修改時間
  ) end_data
  LEFT JOIN (
    SELECT
      db_name,
      schema_name,
      table_name,
      row_count,
      collect_time
    FROM
      tmp_table_info
  ) start_data ON (
    end_data.db_name = start_data.db_name
    AND end_data.schema_name = start_data.schema_name
    AND end_data.table_name = start_data.table_name
    AND end_data.last_modify_time::date = (start_data.collect_time + interval '1 day')::date
  );