Halaman ini menjelaskan cara memantau performa database AlloyDB Omni menggunakan skrip observabilitas PostgreSQL.
Melihat status proses yang terhubung dan peristiwa tunggu
Anda dapat menentukan status proses yang terhubung ke instance AlloyDB Omni
serta backend yang menunggu aktivitas dengan membuat kueri
tampilan pg_stat_activity
.
SELECT
pid,
datname,
age(backend_xid) AS age_in_xids,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
state,
wait_event_type,
wait_event,
query_id,
query
FROM
pg_stat_activity
WHERE
state != 'idle'
AND pid <> pg_backend_pid()
ORDER BY
4 DESC
LIMIT 10;
Melihat tabel terbesar
Anda dapat menentukan ukuran tabel terbesar dengan membuat kueri tampilan pg_stat_user_tables
.
SELECT
oid,
oid::regclass table_name,
pg_size_pretty(pg_relation_size(oid)),
relpages,
s.seq_scan,
s.idx_scan
FROM
pg_class,
pg_stat_user_tables s
WHERE
s.relid = oid
AND oid > 16383
AND relpages > 100
AND relkind = 'r'
ORDER BY
relpages DESC
LIMIT 20;
Melihat pemindaian berurutan teratas
Anda dapat melihat pemindaian berurutan teratas dengan membuat kueri tampilan
pg_stat_user_tables
.
SELECT
relid,
relname,
seq_scan,
pg_size_pretty(pg_relation_size(relid))
FROM
pg_stat_user_tables
ORDER BY
seq_scan DESC
LIMIT 15;
Melihat pemindaian indeks teratas
Anda dapat melihat pemindaian indeks teratas dengan membuat kueri tampilan pg_stat_user_tables
.
SELECT
relid,
relid::regclass table_name,
idx_scan,
pg_size_pretty(pg_relation_size(relid))
FROM
pg_stat_user_tables
WHERE
idx_scan > 10
ORDER BY
idx_scan DESC
LIMIT 15;
Melihat transaksi yang berjalan paling lama
Anda dapat melihat transaksi yang berjalan paling lama dengan membuat kueri
tampilan pg_stat_activity
dan memeriksa usia setiap transaksi.
SELECT
pid,
age(backend_xid) AS age_in_xids,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
state,
query
FROM
pg_stat_activity
WHERE
state != 'idle'
ORDER BY
2 DESC
LIMIT 10;
Memeriksa progres penyedotan debu
Anda dapat memeriksa progres operasi pembersihan dengan membuat kueri
tampilan pg_stat_progress_vacuum
dan menggabungkannya dengan tampilan pg_stat_activity
menggunakan ID proses.
SELECT
p.pid,
now() - a.xact_start AS duration,
coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
CASE
WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
WHEN a.query ~*'^vacuum' THEN 'user'
ELSE
'regular'
END AS mode,
p.datname AS database,
p.relid::regclass AS table,
p.phase,
pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
p.index_vacuum_count,
round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a using (pid)
ORDER BY now() - a.xact_start DESC;
Melihat kueri asinkron
Untuk melihat kueri yang berjalan secara asinkron, Anda dapat membuat kueri pada tampilan pg_stat_activity
dan memfilter kueri yang bukan merupakan proses pemimpin.
SELECT
query,
leader_pid,
array_agg(pid) FILTER (WHERE leader_pid != pid) AS members
FROM
pg_stat_activity
WHERE
leader_pid IS NOT NULL
GROUP BY
query,
leader_pid;
Melihat SQL kunci pemblokiran
Anda dapat melihat aktivitas yang diblokir dengan membuat kueri tampilan pg_locks
dan
menggabungkannya dengan tampilan pg_stat_activity
.
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocked_activity.wait_event AS blocked_wait_event,
blocking_activity.wait_event AS blocking_wait_event,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
Menentukan efektivitas ukuran work_mem
dan temp_buffers
Untuk menentukan apakah work_mem
dan temp_buffers
memiliki ukuran yang tepat untuk
kebutuhan Anda, Anda dapat membuat kueri tampilan pg_stat_database
dan memeriksa file postgres.log
.
Dengan menggunakan pg_stat_database
, jalankan kueri berikut dan jika ada peningkatan
temp_files
atau temp_bytes
di antara eksekusi, penyesuaian mungkin
diperlukan untuk work_mem
atau temp_buffers
.
SELECT
datname,
temp_files,
temp_bytes
FROM
pg_stat_database;
Setelah menjalankannya, periksa file postgres.log
untuk melihat apakah file sementara
digunakan:
LOG: [fd.c:1772] temporary file: path "base/pgsql_tmp/pgsql_tmp4640.1", size 139264
Tujuannya adalah untuk meminimalkan pembuatan file sementara, bukan sepenuhnya mencegah
file tersebut terjadi. Hal ini karena menetapkan work_mem
dan temp_buffers
adalah keseimbangan antara memori yang tersedia di host dan jumlah koneksi
yang memerlukan memori. Untuk menetapkan parameter ini dengan benar, Anda harus memahami setiap workload.