Cara mengkueri dan menganalisis statistik tabel di Hologres - Hologres

Mulai dari Hologres V1.3, statistik harian untuk tabel di instans Anda dikumpulkan dan disimpan dalam tabel sistem hologres.hg_table_info. Data ini dapat digunakan untuk menganalisis tabel dan melakukan optimasi. Topik ini menjelaskan cara melihat serta menganalisis statistik tabel di Hologres.

Batasan

Fitur melihat statistik tabel hanya didukung pada Hologres V1.3 dan versi selanjutnya. Jika instans Anda menggunakan versi sebelumnya, Anda perlu meningkatkan versi. Untuk informasi lebih lanjut, lihat Kesalahan umum saat mempersiapkan peningkatan atau bergabunglah dengan grup DingTalk Hologres untuk memberikan umpan balik. Untuk informasi lebih lanjut, lihat Bagaimana cara mendapatkan dukungan online lebih banyak?.
Tabel hologres.hg_table_info memiliki keterlambatan satu hari. Data untuk hari saat ini biasanya diperbarui pada pukul 05:00 hari berikutnya. Pada hari instans Hologres ditingkatkan dari V1.1 ke V1.3, statistik tabel tidak dibuat. Jika Anda mencoba menanyakan statistik tabel pada hari tersebut, pesan kesalahan berikut akan ditampilkan: meta warehouse store currently not available. Anda harus menunggu hingga hari setelah peningkatan untuk menanyakan statistik tabel.

Catatan penggunaan

Secara default, log statistik tabel disimpan selama 30 hari.
Untuk tabel internal non-partisi di Hologres (type='TABLE'), Anda dapat menanyakan statistik rinci seperti ruang penyimpanan, jumlah file, jumlah akses kumulatif, dan jumlah baris.
Untuk objek lainnya, seperti tampilan, tampilan materialisasi, tabel eksternal, dan tabel induk, Anda hanya dapat menanyakan informasi dasar seperti jumlah partisi, nama tabel eksternal untuk tabel eksternal, dan definisi tampilan.
Tabel hologres.hg_table_info adalah tabel sistem di gudang metadata Hologres. Kegagalan menanyakan tabel hologres.hg_table_info tidak memengaruhi kueri bisnis di instans Anda. Oleh karena itu, stabilitas tabel hologres.hg_table_info tidak tercakup oleh Service-Level Agreement (SLA) produk.

Tabel hg_table_info

Tabel hg_table_info berisi bidang-bidang berikut.

Catatan

Statistik tabel disimpan dalam tabel sistem hologres.hg_table_info. Setelah instans ditingkatkan ke V1.3, Hologres secara default mengumpulkan informasi tabel setiap hari.
Beberapa bidang mungkin kosong jika sebuah tabel dibuat sebelum instans ditingkatkan ke V1.3, karena informasi pembuatannya belum dikumpulkan. Informasi dikumpulkan untuk semua tabel yang dibuat setelah peningkatan.

Bidang	Tipe	Deskripsi	Catatan
db_name	text	Nama database tempat tabel berada.	Tidak ada
schema_name	text	Nama skema tempat tabel berada.	Tidak ada
table_name	text	Nama tabel.	Tidak ada
table_id	text	Pengenal unik tabel. Untuk tabel eksternal, format ID adalah db.schema.table.	Tidak ada
type	text	Jenis tabel. Nilai valid: TABLE: tabel standar dan partisi anak. PARTITION TABLE: tabel induk fisik. LOGICAL PARTITION TABLE: tabel partisi logis. Catatan Jenis ini didukung di Hologres V3.1.25/V3.2.8 dan versi selanjutnya. Untuk versi sebelumnya, sistem melaporkan jenis tabel sebagai TABLE. FOREIGN TABLE: tabel eksternal. VIEW: tampilan. TAMPILAN YANG DI-MATERIALISASI: Tampilan yang di-materialisasi.	Jika `type` adalah `VIEW`, bidang `create_time` dan `last_ddl_time` kosong. 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` kosong dan tidak memiliki catatan.
partition_spec	text	Kondisi partisi. Bidang ini valid untuk partisi anak.	Tidak ada
is_partition	boolean	Menunjukkan apakah tabel tersebut adalah partisi anak.	Tidak ada
owner_name	text	Nama pengguna pemilik tabel. Anda dapat menggabungkan bidang ini dengan kolom `usename` dari tabel `hg_query_log`.	Tidak ada
create_time	timestamp with time zone	Waktu ketika tabel dibuat.	Tidak ada
last_ddl_time	timestamp with time zone	Waktu ketika informasi tabel terakhir diperbarui.	Tidak ada
last_modify_time	timestamp with time zone	Waktu ketika informasi tabel terakhir diperbarui.	Tidak ada
last_access_time	timestamp with time zone	Waktu ketika tabel terakhir diakses.	Tidak ada
view_def	text	Definisi tampilan.	Bidang ini valid hanya untuk tampilan.
comment	text	Deskripsi tabel atau tampilan.	Tidak ada
hot_storage_size	bigint	Ruang penyimpanan yang digunakan oleh data panas tabel, dalam byte.	Normal jika ukuran penyimpanan yang ditanyakan dari `hg_table_info` berbeda dari ukuran yang dikembalikan oleh fungsi `pg_relation_size`. Hal ini karena data `hg_table_info` dilaporkan setiap hari, dan hasil dari `pg_relation_size` tidak termasuk ukuran penyimpanan binary logging.
cold_storage_size	bigint	Ruang penyimpanan yang digunakan oleh data dingin tabel, dalam byte.	Normal jika ukuran penyimpanan yang ditanyakan dari `hg_table_info` berbeda dari ukuran yang dikembalikan oleh fungsi `pg_relation_size`. Hal ini karena data `hg_table_info` dilaporkan setiap hari, dan hasil dari `pg_relation_size` tidak termasuk ukuran penyimpanan binary logging.
hot_file_count	bigint	Jumlah file data panas dalam tabel.	Tidak ada
cold_file_count	bigint	Jumlah file data dingin dalam tabel.	Tidak ada
table_meta	jsonb	Metadata asli, dalam format JSONB.	Tidak ada
row_count	bigint	Jumlah baris dalam tabel atau partisi.	Jika tabel adalah tabel induk, `row_count` adalah total jumlah baris di semua tabel anaknya.
collect_time	timestamp with time zone	Waktu ketika data dikumpulkan untuk pelaporan.	Tidak ada
partition_count	bigint	Jumlah partisi anak.	Bidang ini valid hanya ketika tabel adalah tabel induk.
parent_schema_name	text	Nama skema tabel induk untuk partisi anak.	Bidang ini valid hanya ketika tabel adalah partisi anak.
parent_table_name	text	Nama tabel induk untuk partisi anak.	Bidang ini valid hanya ketika tabel adalah partisi anak.
total_read_count	bigint	Jumlah kumulatif operasi baca pada tabel. Ini bukan nilai pasti, karena operasi SELECT, INSERT, UPDATE, dan DELETE semuanya meningkatkan hitungan.	Ini bukan nilai pasti. Jangan gunakan.
total_write_count	bigint	Jumlah kumulatif operasi tulis pada tabel. Ini bukan nilai pasti, karena operasi INSERT, UPDATE, dan DELETE semuanya meningkatkan hitungan.	Ini bukan nilai pasti. Jangan gunakan.
read_sql_count_1d	bigint	Jumlah total operasi baca pada tabel pada hari sebelumnya (00:00–24:00, UTC+8).	Didukung hanya di V3.0 dan versi selanjutnya. Jika tabel adalah tabel partisi dan kueri SQL mengenai partisi anak tertentu, data dikumpulkan hanya untuk partisi anak, bukan tabel induk.
write_sql_count_1d	bigint	Jumlah total operasi tulis pada tabel pada hari sebelumnya (00:00–24:00, UTC+8).	Didukung hanya di V3.0 dan versi berikutnya. Jika tabel tersebut adalah tabel partisi dan kueri SQL mengenai partisi anak tertentu, data hanya dikumpulkan untuk partisi anak tersebut, bukan untuk tabel induk.

Berikan izin kueri

Anda memerlukan izin tertentu untuk melihat log statistik tabel. Aturan izin dan metode otorisasi dijelaskan di bawah ini.

Lihat log statistik tabel untuk semua database dalam sebuah instans Hologres.

Berikan izin Superuser kepada pengguna.

Seorang Superuser dapat melihat log statistik tabel untuk semua database dalam sebuah instans Hologres.

-- Ganti "ID akun Alibaba Cloud" dengan nama pengguna yang sebenarnya. Untuk Pengguna RAM, tambahkan awalan p4_ ke ID akun.
ALTER USER "ID akun Alibaba Cloud" SUPERUSER;

Tambahkan pengguna ke pg_stat_scan_table kelompok pengguna.

Selain Superuser, pengguna dalam pg_stat_scan_tables grup (untuk versi lebih lama dari V1.3.44) atau pg_read_all_stats grup (untuk V1.3.44 dan yang lebih baru) juga dapat melihat log statistik tabel untuk semua database. Pengguna biasa dapat menghubungi Superuser untuk ditambahkan ke grup yang sesuai. Perintah otorisasi adalah sebagai berikut.

-- Untuk versi lebih lama dari V1.3.44
GRANT pg_stat_scan_tables TO "ID akun Alibaba Cloud"; -- Berikan izin menggunakan model otorisasi PostgreSQL standar.
CALL spm_grant('pg_stat_scan_tables', 'ID akun Alibaba Cloud');  -- Berikan izin menggunakan model izin sederhana (SPM).
CALL slpm_grant('pg_stat_scan_tables', 'ID akun Alibaba Cloud'); -- Berikan izin menggunakan model izin tingkat skema (SLPM).

-- Untuk V1.3.44 dan yang lebih baru
GRANT pg_read_all_stats TO "ID akun Alibaba Cloud"; -- Berikan izin menggunakan model otorisasi PostgreSQL standar.
CALL spm_grant('pg_read_all_stats', 'ID akun Alibaba Cloud');  -- Berikan izin menggunakan SPM.
CALL slpm_grant('pg_read_all_stats', 'ID akun Alibaba Cloud'); -- Berikan izin menggunakan SLPM.

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

Perintah SQL untuk menanyakan tren dalam statistik tabel

Skenario 1: Lihat tren akses tabel internal

-- Perubahan tren untuk semua tabel internal dalam instans: ruang penyimpanan, jumlah file, jumlah baca, jumlah 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 -- Minggu lalu
  AND type ='TABLE'
  ORDER  BY  collect_date desc ;

Skenario 2: Lihat informasi akses untuk tabel yang menggunakan banyak ruang disk

-- Lihat informasi akses untuk 10 tabel yang paling banyak menggunakan ruang disk.
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 -- Minggu lalu
  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;

Skenario 3: Lihat tren akses dan volume data untuk 10 tabel teratas berdasarkan penyimpanan

-- Akses, penyimpanan, dan tren 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 -- Minggu lalu
  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;

Skenario 4: Lihat tren akses dan volume data untuk tabel yang menggunakan penyimpanan paling sedikit

-- Akses, penyimpanan, dan tren 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 -- Minggu lalu
  AND type = 'TABLE'
ORDER BY total_storage_size ASC  , collect_date DESC ;

Skenario 5: Temukan tabel yang menggunakan banyak ruang disk karena terlalu banyak file kecil

-- Lihat jumlah file dan penggunaan ruang disk untuk setiap tabel, dan urutkan berdasarkan ukuran file rata-rata.
-- Kelompok tabel hanya dapat menampilkan jumlah shard dari DB saat ini. Untuk DB lainnya, kosong.
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;

Skenario 6: Lihat perubahan jumlah baris pada hari data tabel terakhir dimodifikasi

-- Lihat waktu modifikasi terakhir tabel dan total jumlah baris yang dimodifikasi dibandingkan dengan modifikasi sebelumnya.
-- Jika instans memiliki banyak tabel, filter CTE tmp_table_info untuk mencegah waktu kueri panjang akibat pengambilan terlalu banyak data.
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.
    -- Contohnya, collect_time > (current_date - interval '14 day'):: timestamptz
    -- Contohnya, table_name like ''
    -- Contohnya, 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 dicatat 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
  );