Hologres でテーブル統計を分析する方法 - Hologres - Alibaba Cloud ドキュメントセンター

Hologres V1.3 以降では、`hologres.hg_table_info` システムビューを提供し、ご利用のインスタンス内のテーブルに関する日次統計を収集します。このビューをクエリすることで、テーブル情報を監視・分析し、統計に基づいて最適化措置を講じることができます。

制限事項

この機能は Hologres V1.3 以降のバージョンでのみ利用可能です。ご利用のインスタンスがそれ以前のバージョンの場合は、アップグレードする必要があります。サポートが必要な場合は、「一般的なアップグレード準備のエラー」をご参照いただくか、Hologres DingTalk グループにご参加ください。詳細については、「オンラインサポートの利用方法」をご参照ください。
hologres.hg_table_info ビューのデータは T+1 の新鮮度です。特定日のデータは通常、翌日の 05:00 までに更新されます。Hologres インスタンスを V1.3 にアップグレードした当日には、テーブル統計は生成されません。この日にクエリを実行すると、meta warehouse store currently not available エラーが返されます。テーブル統計のクエリは、アップグレードの翌日から開始できます。

注意事項

テーブル統計は、デフォルトで 30 日間保持されます。
非パーティション化内部テーブル (type='TABLE') の場合、ストレージ容量、ファイル数、累計アクセス数、行数などの詳細な統計をクエリできます。
ビュー、マテリアライズドビュー、外部テーブル、パーティション分割された親テーブルなどの他のオブジェクトについては、基本的な情報のみが利用可能です。これには、パーティション数、外部テーブルがマッピングする外部テーブルの名前、ビューとマテリアライズドビューの定義が含まれます。
hologres.hg_table_info テーブルは Hologres メタウェアハウスシステムに属します。hologres.hg_table_info のクエリ失敗はインスタンスで実行中のビジネスクエリに影響を与えないため、hologres.hg_table_info テーブルの安定性はプロダクトのサービスレベルアグリーメント (SLA) の対象外です。

hg_table_info ビュー

hg_table_info ビューには、以下のフィールドが含まれます。

説明

テーブル統計は hologres.hg_table_info システムビューに保存されます。インスタンスを V1.3 にアップグレードすると、デフォルトでテーブル情報が毎日収集されます。
インスタンスを V1.3 にアップグレードする前に作成されたテーブルでは、作成情報が収集されていないため、一部のフィールドが null になる場合があります。アップグレード後に作成されたテーブルでは、すべてのフィールドにデータが入力されます。

フィールド	型	説明	備考
db_name	text	テーブルが存在するデータベースの名前。	なし
schema_name	text	テーブルが存在するスキーマの名前。	なし
table_name	text	テーブルの名前。	なし
table_id	text	テーブルの一意の識別子。外部テーブルの場合、ID のフォーマットは `db.schema.table` です。	なし
type	text	オブジェクトのタイプ。有効な値： `TABLE`：通常のテーブルまたはパーティション分割された子テーブル。 `PARTITION TABLE`：物理的なパーティション分割された親テーブル。 `LOGICAL PARTITION TABLE`：論理的なパーティションテーブル。説明このタイプは Hologres V3.1.25/V3.2.8 以降でサポートされています。それ以前のバージョンでは、報告されるタイプは `TABLE` です。 `DYNAMIC TABLE`：動的テーブル。説明このタイプは Hologres V4.0.14 以降でサポートされています。それ以前のバージョンでは、報告されるタイプは `TABLE` です。 `FOREIGN TABLE`：外部テーブル。 `VIEW`：ビュー。 `MATERIALIZED VIEW`：マテリアライズドビュー。	`type` が `VIEW` の場合、`create_time` および `last_ddl_time` フィールドは null になります。 `type` が `VIEW`、`FOREIGN TABLE`、または `PARTITION TABLE` の場合、`last_modify_time`、`last_access_time`、`hot_file_count`、`cold_file_count`、`total_read_count`、および `total_write_count` フィールドは null になります。タイプが DYNAMIC TABLE の場合、`view_def` フィールドにはタスク定義クエリが表示され、`freshness` や `auto_refresh_enable` などの特別なプロパティは `table_meta` フィールドの `dynamic_table_properties` セクションに表示されます。
partition_spec	text	パーティション分割条件。このフィールドは、パーティション分割された子テーブルに対してのみ有効です。	なし
is_partition	boolean	テーブルがパーティション分割された子テーブルであるかどうかを示します。	なし
owner_name	text	テーブル所有者のユーザー名。この列を `hg_query_log` ビューの `usename` 列と結合できます。	なし
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	テーブルが使用するホットストレージのサイズ (バイト単位)。	`hg_table_info` で報告されるストレージ容量は、`pg_relation_size` 関数の結果と異なる場合があります。この違いは、`hg_table_info` が毎日更新されるのに対し、`pg_relation_size` にはバイナリログのストレージ容量が含まれないため、想定内のものです。
cold_storage_size	bigint	テーブルが使用するコールドストレージのサイズ (バイト単位)。	`hg_table_info` で報告されるストレージ容量は、`pg_relation_size` 関数の結果と異なる場合があります。この違いは、`hg_table_info` が毎日更新されるのに対し、`pg_relation_size` にはバイナリログのストレージ容量が含まれないため、想定内のものです。
hot_file_count	bigint	テーブルのホットストレージ内のファイル数。	なし
cold_file_count	bigint	テーブルのコールドストレージ内のファイル数。	なし
table_meta	jsonb	テーブルの生のメタデータ (JSONB フォーマット)。	なし
row_count	bigint	テーブルまたはパーティションの行数。	パーティション分割された親テーブルの場合、これはすべての子テーブルにわたる合計行数です。
collect_time	timestamp with time zone	統計が収集されたときのタイムスタンプ。	なし
partition_count	bigint	パーティション分割された子テーブルの数。	このフィールドは、パーティション分割された親テーブルに対してのみ有効です。
parent_schema_name	text	パーティション分割された子テーブルの親テーブルのスキーマ名。	このフィールドは、パーティション分割された子テーブルに対してのみ有効です。
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) の 00:00 から 24:00 (UTC+8) までのテーブルに対する読み取り操作の合計数。	Hologres V3.0 以降でサポートされています。パーティションテーブルの場合、SQL ステートメントが特定のパーティション分割された子テーブルを対象とする場合、データは親テーブルからではなく、子テーブルからのみ収集されます。
write_sql_count_1d	bigint	前日 (T-1) の 00:00 から 24:00 (UTC+8) までのテーブルに対する書き込み操作の合計数。	Hologres V3.0 以降でサポートされています。パーティションテーブルの場合、SQL クエリが特定の子テーブルを対象とする場合、データは親テーブルではなく、子テーブルに対してのみ収集されます。

権限の付与

テーブル統計をクエリするには、特定の権限が必要です。このセクションでは、権限ルールと権限を付与する方法について説明します。

Hologres インスタンス内のすべてのデータベースのテーブル統計を表示する
- ユーザーにスーパーユーザー権限を付与する。
  スーパーユーザーは、Hologres インスタンス内のすべてのデータベースのテーブル統計を表示できます。次のコマンドでスーパーユーザー権限を付与します。
```
-- "Alibaba Cloud アカウント ID" を実際のユーザー名に置き換えます。RAM ユーザーの場合、ID の前に "p4_" を付けます。
ALTER USER "Alibaba Cloud アカウント ID" SUPERUSER;
```
- ユーザーを pg_stat_scan_table ユーザーグループに追加する。
  スーパーユーザーに加えて、Hologres では pg_stat_scan_tables ユーザーグループ (V1.3.44 より前のバージョン) または pg_read_all_stats ユーザーグループ (V1.3.44 以降のバージョン) のユーザーがすべてのデータベーステーブルの統計ログを表示することを許可しています。一般ユーザーがすべてのログを表示する必要がある場合、スーパーユーザーに連絡して関連するユーザーグループに追加してもらうことができます。権限付与コマンドは次のとおりです。
```
-- V1.3.44 より前のバージョンの場合
GRANT pg_stat_scan_tables TO "Alibaba Cloud アカウント ID";-- エキスパート権限モデルの場合
CALL spm_grant('pg_stat_scan_tables', 'Alibaba Cloud アカウント ID');  -- 簡易権限モデル (SPM) の場合
CALL slpm_grant('pg_stat_scan_tables', 'Alibaba Cloud アカウント ID'); -- スキーマレベルの簡易権限モデル (SLPM) の場合

-- V1.3.44 以降の場合
GRANT pg_read_all_stats TO "Alibaba Cloud アカウント ID";-- エキスパート権限モデルの場合
CALL spm_grant('pg_read_all_stats', 'Alibaba Cloud アカウント ID');  -- 簡易権限モデル (SPM) の場合
CALL slpm_grant('pg_read_all_stats', 'Alibaba Cloud アカウント ID'); -- スキーマレベルの簡易権限モデル (SLPM) の場合
```
現在のデータベースのテーブル統計を表示する
簡易権限モデル (SPM) またはスキーマレベルの簡易権限モデル (SLPM) を有効にし、ユーザーを db_admin ユーザーグループに追加すると、db_admin ロールは現在のデータベースのテーブル統計ログを表示できます。
説明
一般ユーザーは、現在のデータベースで自身が所有するテーブルの統計のみをクエリできます。
```
CALL spm_grant('<db_name>_admin', 'Alibaba Cloud アカウント ID');  -- 簡易権限モデル (SPM) の場合
CALL slpm_grant('<db_name>.admin', 'Alibaba Cloud アカウント ID'); -- スキーマレベルの簡易権限モデル (SLPM) の場合
```

クエリの例

ユースケース 1：内部テーブルの傾向の監視

-- インスタンス内のすべての内部テーブルの傾向 (ストレージ容量、ファイル数、読み取り/書き込み数、行数を含む) を監視します。
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 -- 過去 1 週間
  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 -- 過去 1 週間
  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：上位 10 テーブルの傾向の分析

-- ストレージで上位 10 のテーブル (昨日の統計に基づく) について、過去 1 週間のアクセス、ストレージ、データ量の傾向を分析します。
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 -- 過去 1 週間
  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 テーブルの傾向の分析

-- 最もストレージを使用していない 10 テーブル (昨日の統計に基づく) について、過去 1 週間のアクセス、ストレージ、データ量の傾向を分析します。
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 -- 過去 1 週間
  AND type = 'TABLE'
ORDER BY total_storage_size ASC  , collect_date DESC ;

ユースケース 5：小規模ファイルを持つ大規模テーブルの特定

-- 各テーブルのファイル数とストレージ容量を表示し、平均ファイルサイズで並べ替えます。
-- テーブルグループは、現在のデータベースのシャード数のみを表示します。他のデータベースでは、この値は null になります。
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
  );