Kueri diagnostik yang berguna di Azure Cosmos DB for PostgreSQL
BERLAKU UNTUK:
Azure Cosmos DB for PostgreSQL (didukung oleh ekstensi database Citus ke PostgreSQL)
Menemukan simpul mana yang berisi data untuk penyewa tertentu
Dalam kasus penggunaan multipenyewa, kita dapat menentukan simpul pekerja mana yang berisi baris untuk penyewa tertentu. Azure Cosmos DB for PostgreSQL mengelompokkan baris tabel terdistribusi ke dalam pecahan, dan menempatkan setiap pecahan pada simpul pekerja di kluster.
Misalkan penyewa aplikasi kami adalah toko, dan kami ingin menemukan simpul pekerja mana yang menyimpan data untuk ID tokonya 4. Dengan kata lain, kami ingin menemukan penempatan untuk pecahan yang berisi baris yang kolom distribusinya memiliki nilai 4:
SELECT shardid, shardstate, shardlength, nodename, nodeport, placementid
FROM pg_dist_placement AS placement,
pg_dist_node AS node
WHERE placement.groupid = node.groupid
AND node.noderole = 'primary'
AND shardid = (
SELECT get_shard_id_for_distribution_column('stores', 4)
);
Output berisi host dan port database pekerja.
┌─────────┬────────────┬─────────────┬───────────┬──────────┬─────────────┐
│ shardid │ shardstate │ shardlength │ nodename │ nodeport │ placementid │
├─────────┼────────────┼─────────────┼───────────┼──────────┼─────────────┤
│ 102009 │ 1 │ 0 │ 10.0.0.16 │ 5432 │ 2 │
└─────────┴────────────┴─────────────┴───────────┴──────────┴─────────────┘
Menemukan kolom distribusi untuk tabel
Setiap tabel terdistribusi memiliki "kolom distribusi." (Untuk informasi selengkapnya, lihat Pemodelan Data Terdistribusi.) Penting untuk mengetahui kolom mana itu. Misalnya, saat menggabungkan atau memfilter tabel, Anda mungkin melihat pesan kesalahan dengan petunjuk seperti, "tambahkan filter ke kolom distribusi".
Tabel pg_dist_* pada simpul koordinator berisi metadata yang beragam tentang database terdistribusi. Secara khusus, pg_dist_partition menyimpan informasi tentang kolom distribusi untuk setiap tabel. Anda dapat menggunakan fungsi utilitas yang nyaman untuk mencari nama kolom distribusi dari detail tingkat rendah dalam metadata. Berikut contoh beserta outputnya:
-- create example table
CREATE TABLE products (
store_id bigint,
product_id bigint,
name text,
price money,
CONSTRAINT products_pkey PRIMARY KEY (store_id, product_id)
);
-- pick store_id as distribution column
SELECT create_distributed_table('products', 'store_id');
-- get distribution column name for products table
SELECT column_to_column_name(logicalrelid, partkey) AS dist_col_name
FROM pg_dist_partition
WHERE logicalrelid='products'::regclass;
Contoh output:
┌───────────────┐
│ dist_col_name │
├───────────────┤
│ store_id │
└───────────────┘
Mendeteksi kunci
Kueri ini akan berjalan di semua node pekerja dan mengidentifikasi kunci, berapa lama mereka telah terbuka, dan kueri yang menyinggung:
SELECT run_command_on_workers($cmd$
SELECT array_agg(
blocked_statement || ' $ ' || cur_stmt_blocking_proc
|| ' $ ' || cnt::text || ' $ ' || age
)
FROM (
SELECT blocked_activity.query AS blocked_statement,
blocking_activity.query AS cur_stmt_blocking_proc,
count(*) AS cnt,
age(now(), min(blocked_activity.query_start)) AS "age"
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
AND blocking_locks.GRANTED
GROUP BY blocked_activity.query,
blocking_activity.query
ORDER BY 4
) a
$cmd$);
Contoh output:
┌───────────────────────────────────────────────────────────────────────────────────┐
│ run_command_on_workers │
├───────────────────────────────────────────────────────────────────────────────────┤
│ (10.0.0.16,5432,t,"") │
│ (10.0.0.20,5432,t,"{""update ads_102277 set name = 'new name' where id = 1; $ sel…│
│…ect * from ads_102277 where id = 1 for update; $ 1 $ 00:00:03.729519""}") │
└───────────────────────────────────────────────────────────────────────────────────┘
Mengkueri ukuran pecahan Anda
Kueri ini akan memberi Anda ukuran setiap pecahan tabel terdistribusi tertentu, yang disebut my_distributed_table:
SELECT *
FROM run_command_on_shards('my_distributed_table', $cmd$
SELECT json_build_object(
'shard_name', '%1$s',
'size', pg_size_pretty(pg_table_size('%1$s'))
);
$cmd$);
Contoh output:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │ result │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│ 102008 │ t │ {"shard_name" : "my_distributed_table_102008", "size" : "2416 kB"} │
│ 102009 │ t │ {"shard_name" : "my_distributed_table_102009", "size" : "3960 kB"} │
│ 102010 │ t │ {"shard_name" : "my_distributed_table_102010", "size" : "1624 kB"} │
│ 102011 │ t │ {"shard_name" : "my_distributed_table_102011", "size" : "4792 kB"} │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Mengkueri ukuran semua tabel terdistribusi
Kueri ini mendapatkan daftar ukuran untuk setiap tabel terdistribusi ditambah ukuran indeksnya.
SELECT
tablename,
pg_size_pretty(
citus_total_relation_size(tablename::text)
) AS total_size
FROM pg_tables pt
JOIN pg_dist_partition pp
ON pt.tablename = pp.logicalrelid::text
WHERE schemaname = 'public';
Contoh output:
┌───────────────┬────────────┐
│ tablename │ total_size │
├───────────────┼────────────┤
│ github_users │ 39 MB │
│ github_events │ 98 MB │
└───────────────┴────────────┘
Perhatikan bahwa ada fungsi Azure Cosmos DB for PostgreSQL lainnya untuk mengkueri ukuran tabel terdistribusi, lihat menentukan ukuran tabel.
Mengidentifikasi indeks yang tidak digunakan
Kueri berikut akan mengidentifikasi indeks yang tidak digunakan pada simpul pekerja untuk tabel terdistribusi tertentu (my_distributed_table)
SELECT *
FROM run_command_on_shards('my_distributed_table', $cmd$
SELECT array_agg(a) as infos
FROM (
SELECT (
schemaname || '.' || relname || '##' || indexrelname || '##'
|| pg_size_pretty(pg_relation_size(i.indexrelid))::text
|| '##' || idx_scan::text
) AS a
FROM pg_stat_user_indexes ui
JOIN pg_index i
ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
AND idx_scan < 50
AND pg_relation_size(relid) > 5 * 8192
AND (schemaname || '.' || relname)::regclass = '%s'::regclass
ORDER BY
pg_relation_size(i.indexrelid) / NULLIF(idx_scan, 0) DESC nulls first,
pg_relation_size(i.indexrelid) DESC
) sub
$cmd$);
Contoh output:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │ result │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│ 102008 │ t │ │
│ 102009 │ t │ {"public.my_distributed_table_102009##some_index_102009##28 MB##0"} │
│ 102010 │ t │ │
│ 102011 │ t │ │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Memantau jumlah koneksi klien
Kueri berikut menghitung koneksi yang terbuka pada koordinator, dan mengelompokkannya berdasarkan jenis.
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
Contoh output:
┌────────┬───────┐
│ state │ count │
├────────┼───────┤
│ active │ 3 │
│ idle │ 3 │
│ ∅ │ 6 │
└────────┴───────┘
Melihat kueri sistem
Kueri aktif
Tampilan pg_stat_activity menunjukkan kueri mana yang sedang dijalankan. Anda dapat melakukan filter untuk menemukan yang aktif mengeksekusi, bersama dengan ID proses backend mereka:
SELECT pid, query, state
FROM pg_stat_activity
WHERE state != 'idle';
Mengapa kueri menunggu
Kami juga dapat melakukan kueri untuk melihat alasan paling umum bahwa kueri tidak diam sedang menunggu. Untuk penjelasan alasannya, periksa dokumentasi PostgreSQL.
SELECT wait_event || ':' || wait_event_type AS type, count(*) AS number_of_occurences
FROM pg_stat_activity
WHERE state != 'idle'
GROUP BY wait_event, wait_event_type
ORDER BY number_of_occurences DESC;
Contoh keluaran saat menjalankan pg_sleep dalam kueri terpisah secara bersamaan:
┌─────────────────┬──────────────────────┐
│ type │ number_of_occurences │
├─────────────────┼──────────────────────┤
│ ∅ │ 1 │
│ PgSleep:Timeout │ 1 │
└─────────────────┴──────────────────────┘
Tingkat kenaikan indeks
Kueri ini akan memberi Anda tingkat kenaikan indeks di semua simpul. Tingkat kenaikan indeks berguna dalam menentukan seberapa sering indeks digunakan saat melakukan kueri. Nilai 95% atau lebih tinggi adalah ideal.
-- on coordinator
SELECT 100 * (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) AS index_hit_rate
FROM pg_statio_user_indexes;
-- on workers
SELECT nodename, result as index_hit_rate
FROM run_command_on_workers($cmd$
SELECT 100 * (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) AS index_hit_rate
FROM pg_statio_user_indexes;
$cmd$);
Contoh output:
┌───────────┬────────────────┐
│ nodename │ index_hit_rate │
├───────────┼────────────────┤
│ 10.0.0.16 │ 96.0 │
│ 10.0.0.20 │ 98.0 │
└───────────┴────────────────┘
Tingkat hit singgahan
Sebagian besar aplikasi biasanya mengakses sebagian kecil dari total datanya sekaligus. PostgreSQL menyimpan data yang sering diakses dalam memori untuk menghindari bacaan lambat dari disk. Anda dapat melihat statistik tentang hal itu di tampilan pg_statio_user_tables.
Pengukuran yang penting adalah berapa persentase data apa yang berasal dari cache memori vs disk dalam beban kerja Anda:
-- on coordinator
SELECT
sum(heap_blks_read) AS heap_read,
sum(heap_blks_hit) AS heap_hit,
100 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_rate
FROM
pg_statio_user_tables;
-- on workers
SELECT nodename, result as cache_hit_rate
FROM run_command_on_workers($cmd$
SELECT
100 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_rate
FROM
pg_statio_user_tables;
$cmd$);
Contoh output:
┌───────────┬──────────┬─────────────────────┐
│ heap_read │ heap_hit │ cache_hit_rate │
├───────────┼──────────┼─────────────────────┤
│ 1 │ 132 │ 99.2481203007518796 │
└───────────┴──────────┴─────────────────────┘
Jika Anda melihat rasio yang jauh lebih rendah dari 99%, Anda mungkin perlu mempertimbangkan untuk meningkatkan cache yang tersedia untuk database.
Langkah berikutnya
- Pelajari tentang tabel sistem lain yang berguna untuk diagnostik