Cara menganalisis statistik tabel di Hologres - Hologres

Mulai versi V1.3, Hologres menyediakan tampilan sistem hg_table_info untuk mengumpulkan statistik harian tentang tabel dalam instans Anda. Anda dapat melakukan kueri terhadap tampilan ini guna memantau dan menganalisis informasi tabel serta menerapkan langkah-langkah optimasi berdasarkan statistik tersebut.

Batasan

Fitur ini hanya tersedia untuk Hologres V1.3 ke atas. Jika instans Anda menggunakan versi sebelumnya, Anda harus melakukan upgrade. Untuk bantuan, lihat Kesalahan umum saat persiapan upgrade atau bergabunglah dengan grup DingTalk Hologres. Untuk informasi lebih lanjut, lihat Bagaimana cara mendapatkan dukungan online lebih lanjut?.
Data dalam tampilan hologres.hg_table_info memiliki tingkat kesegaran T+1. Data untuk hari tertentu biasanya diperbarui paling lambat pukul 05.00 keesokan harinya. Pada hari instans Hologres di-upgrade ke V1.3, tidak ada statistik tabel yang dihasilkan. Kueri pada hari tersebut akan mengembalikan error meta warehouse store currently not available. Anda dapat mulai melakukan kueri statistik tabel pada hari setelah upgrade.

Catatan penggunaan

Statistik tabel disimpan selama 30 hari secara default.
Untuk tabel internal non-partisi (type='TABLE'), Anda dapat melakukan kueri statistik detail, seperti storage space, jumlah file, jumlah akses kumulatif, dan jumlah baris.
Untuk objek lain, seperti view, materialized view, foreign table, dan tabel induk partisi, hanya informasi dasar yang tersedia. Informasi tersebut mencakup jumlah partisi, nama tabel eksternal yang dipetakan oleh foreign table, serta definisi view dan materialized view.
Tabel hologres.hg_table_info termasuk dalam sistem meta warehouse Hologres. Karena kegagalan dalam melakukan kueri terhadap hologres.hg_table_info tidak memengaruhi kueri bisnis yang sedang berjalan di instans, stabilitas tabel hologres.hg_table_info tidak dicakup oleh service level agreement (SLA) produk.

Tampilan hg_table_info

Tampilan hg_table_info berisi bidang-bidang berikut.

Catatan

Statistik tabel disimpan dalam tampilan sistem hologres.hg_table_info. Setelah instans di-upgrade ke V1.3, informasi tabel dikumpulkan setiap hari secara default.
Beberapa bidang mungkin bernilai null untuk tabel yang dibuat sebelum instans di-upgrade ke V1.3 karena informasi pembuatannya tidak dikumpulkan. Untuk tabel yang dibuat setelah upgrade, semua bidang diisi.

Field	Type	Description	Remarks
db_name	text	Nama database tempat tabel berada.	None
schema_name	text	Nama skema tempat tabel berada.	None
table_name	text	Nama tabel.	None
table_id	text	Pengidentifikasi unik untuk tabel. Untuk foreign table, format ID-nya adalah `db.schema.table`.	None
type	text	Jenis objek. Nilai yang valid: `TABLE`: tabel reguler atau tabel anak partisi. `PARTITION TABLE`: tabel induk partisi fisik. `LOGICAL PARTITION TABLE`: tabel partisi logis. Catatan Jenis ini didukung di Hologres V3.1.25/V3.2.8 ke atas. Di versi sebelumnya, jenis yang dilaporkan adalah `TABLE`. `DYNAMIC TABLE`: tabel dinamis. Catatan Jenis ini didukung di Hologres V4.0.14 ke atas. Di versi sebelumnya, jenis yang dilaporkan adalah `TABLE`. `FOREIGN TABLE`: tabel eksternal. `VIEW`: view. `MATERIALIZED VIEW`: materialized view.	Jika `type` adalah `VIEW`, bidang `create_time` dan `last_ddl_time` bernilai null. Jika `type` adalah `VIEW`, `FOREIGN TABLE`, atau `PARTITION TABLE`, bidang `last_modify_time`, `last_access_time`, `hot_file_count`, `cold_file_count`, `total_read_count`, dan `total_write_count` bernilai null. Ketika jenisnya adalah DYNAMIC TABLE, bidang `view_def` menampilkan kueri definisi tugas, sedangkan properti khusus seperti `freshness` dan `auto_refresh_enable` ditampilkan dalam bagian `dynamic_table_properties` dari bidang `table_meta`.
partition_spec	text	Kondisi partisi. Bidang ini hanya berlaku untuk tabel anak partisi.	None
is_partition	boolean	Menunjukkan apakah tabel merupakan tabel anak partisi.	None
owner_name	text	Username pemilik tabel. Anda dapat menggabungkan kolom ini dengan kolom `usename` pada tampilan `hg_query_log`.	None
create_time	timestamp with time zone	Timestamp saat tabel dibuat.	None
last_ddl_time	timestamp with time zone	Timestamp saat metadata tabel terakhir diperbarui.	None
last_modify_time	timestamp with time zone	Timestamp saat data tabel terakhir dimodifikasi.	None
last_access_time	timestamp with time zone	Timestamp saat tabel terakhir diakses.	None
view_def	text	Definisi view.	Bidang ini hanya berlaku untuk view.
comment	text	Deskripsi tabel atau view.	None
hot_storage_size	bigint	Ukuran penyimpanan panas yang digunakan oleh tabel, dalam byte.	Ukuran penyimpanan yang dilaporkan di `hg_table_info` mungkin berbeda dari hasil fungsi `pg_relation_size`. Perbedaan ini diharapkan karena `hg_table_info` diperbarui setiap hari dan `pg_relation_size` tidak mencakup ukuran penyimpanan binary logs.
cold_storage_size	bigint	Ukuran penyimpanan dingin yang digunakan oleh tabel, dalam byte.	Ukuran penyimpanan yang dilaporkan di `hg_table_info` mungkin berbeda dari hasil fungsi `pg_relation_size`. Perbedaan ini diharapkan karena `hg_table_info` diperbarui setiap hari dan `pg_relation_size` tidak mencakup ukuran penyimpanan binary logs.
hot_file_count	bigint	Jumlah file dalam penyimpanan panas untuk tabel.	None
cold_file_count	bigint	Jumlah file dalam penyimpanan dingin untuk tabel.	None
table_meta	jsonb	Metadata mentah tabel, dalam format JSONB.	None
row_count	bigint	Jumlah baris dalam tabel atau partisi.	Untuk tabel induk partisi, ini adalah total jumlah baris di seluruh tabel anaknya.
collect_time	timestamp with time zone	Timestamp saat statistik dikumpulkan.	None
partition_count	bigint	Jumlah tabel anak partisi.	Bidang ini hanya berlaku untuk tabel induk partisi.
parent_schema_name	text	Nama skema tabel induk untuk tabel anak partisi.	Bidang ini hanya berlaku untuk tabel anak partisi.
parent_table_name	text	Nama tabel induk untuk tabel anak partisi.	Bidang ini hanya berlaku untuk tabel anak partisi.
total_read_count	bigint	Jumlah kumulatif operasi baca pada tabel. Ini adalah nilai perkiraan, karena operasi `SELECT`, `INSERT`, `UPDATE`, dan `DELETE` semuanya dapat menambah hitungan ini.	Nilai ini merupakan perkiraan dan tidak direkomendasikan untuk penghitungan akurat.
total_write_count	bigint	Jumlah kumulatif operasi tulis pada tabel. Ini adalah nilai perkiraan, karena operasi `INSERT`, `UPDATE`, dan `DELETE` semuanya dapat menambah hitungan ini.	Nilai ini merupakan perkiraan dan tidak direkomendasikan untuk penghitungan akurat.
read_sql_count_1d	bigint	Total jumlah operasi baca pada tabel pada hari sebelumnya (T-1), dari pukul 00.00 hingga 24.00 (UTC+8).	Didukung di Hologres V3.0 ke atas. Untuk tabel partisi, jika pernyataan SQL menargetkan tabel anak partisi tertentu, data hanya dikumpulkan dari tabel anak tersebut, bukan dari tabel induk.
write_sql_count_1d	bigint	Total jumlah operasi tulis pada tabel pada hari sebelumnya (T-1), dari pukul 00.00 hingga 24.00 (UTC+8).	Didukung di Hologres V3.0 ke atas. Untuk tabel partisi, jika kueri SQL menargetkan tabel anak tertentu, data hanya dikumpulkan untuk tabel anak tersebut, bukan tabel induk.

Berikan izin

Anda harus memiliki izin tertentu untuk melakukan kueri statistik tabel. Bagian ini menjelaskan aturan izin dan cara memberikannya.

Lihat statistik tabel untuk semua database dalam instans Hologres

Berikan hak istimewa superuser kepada pengguna.
Superuser dapat melihat statistik tabel untuk semua database dalam instans Hologres. Perintah berikut memberikan hak istimewa superuser.
```
-- Ganti "ID akun Alibaba Cloud" dengan username aktual. Untuk Pengguna RAM, tambahkan awalan "p4_" pada ID tersebut.
ALTER USER "ID akun Alibaba Cloud" SUPERUSER;
```

Tambahkan pengguna ke kelompok pengguna pg_stat_scan_table.

Selain superuser, Hologres memungkinkan pengguna dalam kelompok pengguna pg_stat_scan_tables (untuk versi sebelum V1.3.44) atau kelompok pengguna pg_read_all_stats (untuk V1.3.44 dan versi setelahnya) untuk melihat log statistik semua tabel database. Jika pengguna biasa perlu melihat semua log, mereka dapat menghubungi superuser untuk ditambahkan ke kelompok pengguna yang relevan. Perintah otorisasi adalah sebagai berikut.

-- Untuk versi sebelum V1.3.44
GRANT pg_stat_scan_tables TO "ID akun Alibaba Cloud";-- Untuk model izin expert
CALL spm_grant('pg_stat_scan_tables', 'ID akun Alibaba Cloud');  -- Untuk model izin sederhana (SPM)
CALL slpm_grant('pg_stat_scan_tables', 'ID akun Alibaba Cloud'); -- Untuk model izin tingkat skema (SLPM)

-- Untuk V1.3.44 dan setelahnya
GRANT pg_read_all_stats TO "ID akun Alibaba Cloud";-- Untuk model izin expert
CALL spm_grant('pg_read_all_stats', 'ID akun Alibaba Cloud');  -- Untuk model izin sederhana (SPM)
CALL slpm_grant('pg_read_all_stats', 'ID akun Alibaba Cloud'); -- Untuk model izin tingkat skema (SLPM)

Lihat statistik tabel untuk database saat ini
Jika Anda mengaktifkan model izin sederhana (SPM) atau model izin tingkat skema (SLPM) dan menambahkan pengguna ke kelompok pengguna db_admin, role db_admin dapat melihat log statistik tabel untuk database saat ini.
Catatan
Pengguna biasa hanya dapat melakukan kueri statistik untuk tabel yang mereka miliki di database saat ini.
```
CALL spm_grant('<db_name>_admin', 'ID akun Alibaba Cloud');  -- Untuk model izin sederhana (SPM)
CALL slpm_grant('<db_name>.admin', 'ID akun Alibaba Cloud'); -- Untuk model izin tingkat skema (SLPM)
```

Kueri contoh

Kasus penggunaan 1: Pantau tren tabel internal

-- Pantau tren untuk semua tabel internal dalam instans, termasuk ukuran penyimpanan, jumlah file, jumlah baca/tulis, dan jumlah baris.
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 -- Selama seminggu terakhir
  AND type ='TABLE'
  ORDER  BY  collect_date desc ;

Kasus penggunaan 2: Periksa pola akses tabel besar

-- Periksa pola akses untuk 10 tabel teratas berdasarkan ukuran penyimpanan.
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 -- Selama seminggu terakhir
  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;

Kasus penggunaan 3: Analisis tren untuk 10 tabel teratas

-- Analisis tren akses, penyimpanan, dan volume data selama seminggu terakhir untuk 10 tabel teratas berdasarkan penyimpanan (berdasarkan statistik kemarin).
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 -- Kemarin
  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 -- Selama seminggu terakhir
  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;

Kasus penggunaan 4: Analisis tren untuk 10 tabel terbawah

-- Analisis tren akses, penyimpanan, dan volume data selama seminggu terakhir untuk 10 tabel yang menggunakan penyimpanan paling sedikit (berdasarkan statistik kemarin).
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 -- Kemarin
  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 -- Selama seminggu terakhir
  AND type = 'TABLE'
ORDER BY total_storage_size ASC  , collect_date DESC ;

Kasus penggunaan 5: Identifikasi tabel besar dengan file kecil

-- Lihat jumlah file dan ukuran penyimpanan untuk setiap tabel, lalu urutkan berdasarkan ukuran file rata-rata.
-- Kelompok tabel hanya menampilkan jumlah shard untuk database saat ini. Untuk database lain, nilai ini 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;

Kasus penggunaan 6: Periksa perubahan jumlah baris

-- Lihat waktu modifikasi terakhir dan total perubahan jumlah baris sejak modifikasi terakhir.
-- Jika instans berisi banyak tabel, filter CTE tmp_table_info untuk mencegah waktu kueri yang lama akibat pengambilan data berlebihan.
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'
    -- Tambahkan filter untuk tmp_table_info di sini.
    -- Contoh: collect_time > (current_date - interval '14 day'):: timestamptz
    -- Contoh: table_name like ''
    -- Contoh: 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 -- Kueri waktu modifikasi terakhir tabel yang tercatat kemarin.
  ) 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
  );