Hasznos diagnosztikai lekérdezések az Azure Cosmos DB for PostgreSQL-ben
A KÖVETKEZŐKRE VONATKOZIK: 
Azure Cosmos DB for PostgreSQL (a Citus adatbázisbővítménye a PostgreSQL-re)
Annak megállapítása, hogy melyik csomópont tartalmaz adatokat egy adott bérlőhöz
A több-bérlős használati esetben meghatározható, hogy melyik feldolgozó csomópont tartalmazza egy adott bérlő sorait. Az Azure Cosmos DB for PostgreSQL az elosztott táblák sorait szegmensekbe csoportosítja, és az egyes szegmenseket egy feldolgozó csomóponton helyezi el a fürtben.
Tegyük fel, hogy az alkalmazás bérlői tárolók, és meg szeretnénk keresni, hogy melyik feldolgozó csomópont tárolja az id=4 tároló adatait. Más szóval a 4 értéket tartalmazó sorokat tartalmazó szegmens elhelyezését szeretnénk megtalálni:
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)
);
A kimenet tartalmazza a feldolgozói adatbázis gazdagépét és portját.
┌─────────┬────────────┬─────────────┬───────────┬──────────┬─────────────┐
│ shardid │ shardstate │ shardlength │ nodename │ nodeport │ placementid │
├─────────┼────────────┼─────────────┼───────────┼──────────┼─────────────┤
│ 102009 │ 1 │ 0 │ 10.0.0.16 │ 5432 │ 2 │
└─────────┴────────────┴─────────────┴───────────┴──────────┴─────────────┘
Elosztott sémát futtató csomópont megkeresése
Az elosztott sémák automatikusan egyes társhelycsoportokhoz vannak társítva, így az ezekben a sémákban létrehozott táblák szilánkkulcs nélküli elosztott táblákká lesznek konvertálva. Az elosztott séma helyének megkereséséhez csatlakozzon citus_shards a következőhöz citus_schemas:
select schema_name, nodename, nodeport
from citus_shards
join citus_schemas cs
on cs.colocation_id = citus_shards.colocation_id
group by 1,2,3;
schema_name | nodename | nodeport
-------------+-----------+----------
a | localhost | 9701
b | localhost | 9702
with_data | localhost | 9702
Közvetlenül a sématábla típusára való szűrést is lekérdezheti citus_shards , hogy az összes tábla részletes listaeleme legyen.
select * from citus_shards where citus_table_type = 'schema';
table_name | shardid | shard_name | citus_table_type | colocation_id | nodename | nodeport | shard_size | schema_name | colocation_id | schema_size | schema_owner
----------------+---------+-----------------------+------------------+---------------+-----------+----------+------------+-------------+---------------+-------------+--------------
a.cities | 102080 | a.cities_102080 | schema | 4 | localhost | 9701 | 8192 | a | 4 | 128 kB | citus
a.map_tags | 102145 | a.map_tags_102145 | schema | 4 | localhost | 9701 | 32768 | a | 4 | 128 kB | citus
a.measurement | 102047 | a.measurement_102047 | schema | 4 | localhost | 9701 | 0 | a | 4 | 128 kB | citus
a.my_table | 102179 | a.my_table_102179 | schema | 4 | localhost | 9701 | 16384 | a | 4 | 128 kB | citus
a.people | 102013 | a.people_102013 | schema | 4 | localhost | 9701 | 32768 | a | 4 | 128 kB | citus
a.test | 102008 | a.test_102008 | schema | 4 | localhost | 9701 | 8192 | a | 4 | 128 kB | citus
a.widgets | 102146 | a.widgets_102146 | schema | 4 | localhost | 9701 | 32768 | a | 4 | 128 kB | citus
b.test | 102009 | b.test_102009 | schema | 5 | localhost | 9702 | 8192 | b | 5 | 32 kB | citus
b.test_col | 102012 | b.test_col_102012 | schema | 5 | localhost | 9702 | 24576 | b | 5 | 32 kB | citus
with_data.test | 102180 | with_data.test_102180 | schema | 11 | localhost | 9702 | 647168 | with_data | 11 | 632 kB | citus
Tábla terjesztési oszlopának megkeresése
Minden elosztott tábla rendelkezik egy "terjesztési oszlop" értékkel. (További információ: Elosztott adatmodellezés.) Fontos lehet tudni, hogy melyik oszlopról van szó. Táblák csatlakoztatásakor vagy szűrésekor például olyan tippeket tartalmazó hibaüzenetek jelenhetnek meg, mint a "szűrő hozzáadása a terjesztési oszlophoz".
A pg_dist_* koordinátorcsomópont táblái különböző metaadatokat tartalmaznak az elosztott adatbázisról. Különösen pg_dist_partition az egyes táblák terjesztési oszlopával kapcsolatos információkat tartalmazza. Egy kényelmes segédprogrammal megkeresheti a terjesztési oszlop nevét a metaadatok alacsony szintű részleteiből. Íme egy példa és kimenete:
-- 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;
Example output:
┌───────────────┐
│ dist_col_name │
├───────────────┤
│ store_id │
└───────────────┘
Zárolások észlelése
Ez a lekérdezés az összes munkavégző csomóponton fut, és azonosítja a zárolásokat, a megnyitásuk óta eltelt időt és a jogsértő lekérdezéseket:
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$);
Example 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""}") │
└───────────────────────────────────────────────────────────────────────────────────┘
A szegmensek méretének lekérdezése
Ez a lekérdezés megadja egy adott elosztott tábla minden szegmensének méretét, az úgynevezett 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$);
Example 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"} │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Az összes elosztott tábla méretének lekérdezése
Ez a lekérdezés lekéri az egyes elosztott táblák méreteinek és indexeik méretének listáját.
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';
Example output:
┌───────────────┬────────────┐
│ tablename │ total_size │
├───────────────┼────────────┤
│ github_users │ 39 MB │
│ github_events │ 98 MB │
└───────────────┴────────────┘
Vegye figyelembe, hogy vannak más Azure Cosmos DB for PostgreSQL-függvények is az elosztott táblaméret lekérdezéséhez, lásd a táblaméret meghatározását.
Nem használt indexek azonosítása
Az alábbi lekérdezés azonosítja a nem használt indexeket egy adott elosztott tábla feldolgozó csomópontjain (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$);
Example output:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │ result │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│ 102008 │ t │ │
│ 102009 │ t │ {"public.my_distributed_table_102009##some_index_102009##28 MB##0"} │
│ 102010 │ t │ │
│ 102011 │ t │ │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Ügyfélkapcsolatok számának figyelése
Az alábbi lekérdezés megszámolja a koordinátoron megnyitott kapcsolatokat, és típus szerint csoportosítja őket.
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
Example output:
┌────────┬───────┐
│ state │ count │
├────────┼───────┤
│ active │ 3 │
│ idle │ 3 │
│ ∅ │ 6 │
└────────┴───────┘
Rendszer-lekérdezések megtekintése
Aktív lekérdezések
A pg_stat_activity nézet azt mutatja, hogy mely lekérdezések futnak éppen. Szűréssel megkeresheti az aktívan végrehajtókat, valamint a háttérrendszer folyamatazonosítóját:
SELECT pid, query, state
FROM pg_stat_activity
WHERE state != 'idle';
Miért várakoznak a lekérdezések?
Lekérdezhetjük a nem tétlen lekérdezések leggyakoribb okait is, amelyek várakoznak. Az okok magyarázatáért tekintse meg a PostgreSQL dokumentációját.
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;
Példakimenet egy külön lekérdezésben való egyidejű futtatáskor pg_sleep :
┌─────────────────┬──────────────────────┐
│ type │ number_of_occurences │
├─────────────────┼──────────────────────┤
│ ∅ │ 1 │
│ PgSleep:Timeout │ 1 │
└─────────────────┴──────────────────────┘
Index találati aránya
Ez a lekérdezés minden csomóponton megadja az index találati arányát. Az indexek találati aránya hasznos annak meghatározásához, hogy milyen gyakran használják az indexeket lekérdezéskor. A 95%-os vagy magasabb érték ideális.
-- 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$);
Example output:
┌───────────┬────────────────┐
│ nodename │ index_hit_rate │
├───────────┼────────────────┤
│ 10.0.0.16 │ 96.0 │
│ 10.0.0.20 │ 98.0 │
└───────────┴────────────────┘
Gyorsítótár találati sebessége
A legtöbb alkalmazás általában a teljes adatok egy kis részét éri el egyszerre. A PostgreSQL a memóriában tárolja a gyakran használt adatokat, hogy elkerülje a lemezről való lassú olvasást. A statisztikát a pg_statio_user_tables nézetben tekintheti meg.
Fontos mérés, hogy az adatok hány százaléka származik a memóriagyorsítótárból és a számítási feladatban lévő lemezből:
-- 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$);
Example output:
┌───────────┬──────────┬─────────────────────┐
│ heap_read │ heap_hit │ cache_hit_rate │
├───────────┼──────────┼─────────────────────┤
│ 1 │ 132 │ 99.2481203007518796 │
└───────────┴──────────┴─────────────────────┘
Ha az arány jelentősen alacsonyabb, mint 99%, akkor érdemes megfontolni az adatbázis számára elérhető gyorsítótár növelését.
Következő lépések
- További információ a diagnosztika szempontjából hasznos egyéb rendszertáblákról