Hologres でテーブル統計をクエリおよび分析する方法 - Hologres

Hologres V1.3 以降のバージョンでは、インスタンス内のテーブルの統計が毎日収集されます。これらの統計は、hologres.hg_table_info システムテーブルに格納されます。このデータを使用して、テーブルを分析し、最適化を実行できます。このトピックでは、Hologres でテーブル統計を表示および分析する方法について説明します。

制限事項

Hologres V1.3 以降のバージョンのみがテーブル統計の表示をサポートしています。インスタンスが以前のバージョンの場合は、アップグレードする必要があります。詳細については、「アップグレードの準備をする際の一般的なエラー」をご参照いただくか、Hologres DingTalk グループに参加してフィードバックを送信してください。詳細については、「オンラインサポートをさらに得るには」をご参照ください。
hologres.hg_table_info テーブルには 1 日のデータ遅延があります。当日のデータは通常、翌日の 05:00 までに更新されます。Hologres インスタンスが V1.1 から V1.3 にアップグレードされた日には、テーブル統計は生成されません。この日にテーブル統計をクエリすると、次のエラーメッセージが返されます: meta warehouse store currently not available。テーブル統計をクエリするには、アップグレードの翌日まで待つ必要があります。

使用上の注意

デフォルトでは、テーブル統計ログは 30 日間保持されます。
Hologres の非パーティション化内部テーブル (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 にアップグレードされると、Hologres はデフォルトで毎日テーブル情報を収集します。
一部のフィールドは空の場合があります。これは、インスタンスが V1.3 にアップグレードされる前にテーブルが作成された場合に発生します。その作成情報が収集されなかったためです。アップグレード後に作成されたすべてのテーブルの情報が収集されます。

フィールド	型	説明	注
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 としてレポートします。 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	テーブルオーナーのユーザー名。このフィールドを `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	テーブルまたはパーティションの行数。	テーブルが親テーブルの場合、「row_count」はそのすべての子テーブルの行の総数です。
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	前日 (00:00–24:00, UTC + 08:00) のテーブルに対する読み取り操作の総数。	V3.0 以降でのみサポートされます。テーブルがパーティションテーブルであり、SQL クエリが特定の子パーティションにヒットした場合、データは親テーブルではなく、子パーティションに対してのみ収集されます。
write_sql_count_1d	bigint	前日 (00:00–24:00, UTC + 08:00) のテーブルに対する書き込み操作の総数。	V3.0 以降でのみサポートされます。テーブルがパーティションテーブルであり、SQL クエリが特定の子パーティションにヒットした場合、データは親テーブルではなく、子パーティションに対してのみ収集されます。

クエリ権限の付与

テーブル統計ログを表示するには、特定の権限が必要です。権限ルールと権限付与方法を以下に示します。

Hologres インスタンス内のすべてのデータベースのテーブル統計ログを表示します。

ユーザーにスーパーユーザー権限を付与します。

スーパーユーザーは、Hologres インスタンス内のすべてのデータベースのテーブル統計ログを表示できます。

-- "Alibaba Cloud account ID" を実際のユーザー名に置き換えます。RAM ユーザーの場合は、アカウント ID に p4_ プレフィックスを追加します。
ALTER USER "Alibaba Cloud account ID" SUPERUSER;

ユーザーを pg_stat_scan_table ユーザーグループに追加します。

スーパーユーザーに加えて、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 account ID"; -- 標準権限モデルを使用して権限を付与します。
CALL spm_grant('pg_stat_scan_tables', 'Alibaba Cloud account ID');  -- 簡易権限モデル (SPM) を使用して権限を付与します。
CALL slpm_grant('pg_stat_scan_tables', 'Alibaba Cloud account ID'); -- スキーマレベルの簡易権限モデル (SLPM) を使用して権限を付与します。

-- V1.3.44 以降
GRANT pg_read_all_stats TO "Alibaba Cloud account ID"; -- 標準権限モデルを使用して権限を付与します。
CALL spm_grant('pg_read_all_stats', 'Alibaba Cloud account ID');  -- SPM を使用して権限を付与します。
CALL slpm_grant('pg_read_all_stats', 'Alibaba Cloud account ID'); -- SLPM を使用して権限を付与します。

現在のデータベースのテーブル統計ログを表示します。
簡易権限モデル (SPM) またはスキーマレベルの簡易権限モデル (SLPM) を有効にして、ユーザーを db_admin グループに追加できます。db_admin ロールを持つユーザーは、現在のデータベースのテーブル統計ログを表示できます。
説明
通常のユーザーは、現在のデータベースで所有するテーブルの統計のみをクエリできます。
```
CALL spm_grant('<db_name>_admin', 'Alibaba Cloud account ID');  -- SPM を使用して権限を付与します。
CALL slpm_grant('<db_name>.admin', 'Alibaba Cloud account ID'); -- SLPM を使用して権限を付与します。
```

テーブル統計の傾向をクエリする SQL コマンド

シナリオ 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 -- 先週
  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: ストレージ別のトップ 10 テーブルのアクセスとデータ量の傾向の表示

-- 昨日の統計に基づいて、ストレージ別のトップ 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: 小さすぎるファイルが多いために大量のディスク領域を使用しているテーブルの検索

-- 各テーブルのファイル数とディスク領域の使用状況を表示し、平均ファイルサイズでソートします。
-- テーブルグループは現在の 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: テーブルデータが最後に変更された日の行数の変化の表示

-- テーブルの最終変更時刻と、前回の変更と比較して変更された行の総数を表示します。
-- インスタンスに多数のテーブルがある場合は、tmp_table_info CTE をフィルターして、データのフェッチが多すぎることによるクエリ時間の長期化を防ぎます。
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
  );