Nuttige diagnostische query's in Azure Cosmos DB for PostgreSQL
VAN TOEPASSING OP: 
Azure Cosmos DB for PostgreSQL (mogelijk gemaakt door de Citus-database-extensie naar PostgreSQL)
Zoeken naar welk knooppunt gegevens voor een specifieke tenant bevat
In het gebruiksscenario voor meerdere tenants kunnen we bepalen welk werkknooppunt de rijen voor een specifieke tenant bevat. Azure Cosmos DB for PostgreSQL groepeert de rijen met gedistribueerde tabellen in shards en plaatst elke shard op een werkknooppunt in het cluster.
Stel dat de tenants van onze toepassing worden opgeslagen en dat we willen weten welk werkknooppunt de gegevens bevat voor de opslag-id=4. Met andere woorden, we willen de plaatsing vinden voor de shard met rijen waarvan de distributiekolom de waarde 4 heeft:
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)
);
De uitvoer bevat de host en poort van de werkroldatabase.
┌─────────┬────────────┬─────────────┬───────────┬──────────┬─────────────┐
│ shardid │ shardstate │ shardlength │ nodename │ nodeport │ placementid │
├─────────┼────────────┼─────────────┼───────────┼──────────┼─────────────┤
│ 102009 │ 1 │ 0 │ 10.0.0.16 │ 5432 │ 2 │
└─────────┴────────────┴─────────────┴───────────┴──────────┴─────────────┘
Bepalen welk knooppunt als host fungeert voor een gedistribueerd schema
Gedistribueerde schema's worden automatisch gekoppeld aan afzonderlijke colocatiegroepen, zodat de tabellen die in deze schema's zijn gemaakt, worden geconverteerd naar gedistribueerde tabellen met een puntkomma zonder een shardsleutel. U vindt waar een gedistribueerd schema zich bevindt door deel te nemen aan citus_shardscitus_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
U kunt ook query's citus_shards uitvoeren om rechtstreeks naar het schematabeltype te filteren om een gedetailleerde vermelding voor alle tabellen te hebben.
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
De distributiekolom voor een tabel zoeken
Elke gedistribueerde tabel heeft een 'distributiekolom'. (Zie voor meer informatie Gedistribueerde gegevensmodellering.) Het kan belangrijk zijn om te weten welke kolom deze is. Wanneer u bijvoorbeeld tabellen toevoegt of filtert, ziet u mogelijk foutberichten met hints zoals 'Een filter toevoegen aan de distributiekolom'.
De pg_dist_* tabellen op het coördinatorknooppunt bevatten diverse metagegevens over de gedistribueerde database. In het bijzonder pg_dist_partition bevat informatie over de distributiekolom voor elke tabel. U kunt een handige hulpprogrammafunctie gebruiken om de naam van de distributiekolom op te zoeken op basis van de details op laag niveau in de metagegevens. Hier volgt een voorbeeld en de uitvoer:
-- 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;
Voorbeelduitvoer:
┌───────────────┐
│ dist_col_name │
├───────────────┤
│ store_id │
└───────────────┘
Vergrendelingen detecteren
Deze query wordt uitgevoerd op alle werkknooppunten en identificeert vergrendelingen, hoe lang ze zijn geopend en de offendingquery's:
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$);
Voorbeelduitvoer:
┌───────────────────────────────────────────────────────────────────────────────────┐
│ 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""}") │
└───────────────────────────────────────────────────────────────────────────────────┘
Query's uitvoeren op de grootte van uw shards
Met deze query krijgt u de grootte van elke shard van een bepaalde gedistribueerde tabel, genaamd 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$);
Voorbeelduitvoer:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ 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"} │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Query's uitvoeren op de grootte van alle gedistribueerde tabellen
Met deze query wordt een lijst opgehaald met de grootten voor elke gedistribueerde tabel plus de grootte van de indexen.
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';
Voorbeelduitvoer:
┌───────────────┬────────────┐
│ tablename │ total_size │
├───────────────┼────────────┤
│ github_users │ 39 MB │
│ github_events │ 98 MB │
└───────────────┴────────────┘
Houd er rekening mee dat er andere Azure Cosmos DB for PostgreSQL-functies zijn voor het uitvoeren van query's op gedistribueerde tabelgrootten. Zie de tabelgrootte bepalen.
Ongebruikte indexen identificeren
De volgende query identificeert ongebruikte indexen op werkknooppunten voor een bepaalde gedistribueerde tabel (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$);
Voorbeelduitvoer:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │ result │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│ 102008 │ t │ │
│ 102009 │ t │ {"public.my_distributed_table_102009##some_index_102009##28 MB##0"} │
│ 102010 │ t │ │
│ 102011 │ t │ │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Aantal clientverbindingen bewaken
De volgende query telt de verbindingen die zijn geopend op de coördinator en groepeert ze op type.
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
Voorbeelduitvoer:
┌────────┬───────┐
│ state │ count │
├────────┼───────┤
│ active │ 3 │
│ idle │ 3 │
│ ∅ │ 6 │
└────────┴───────┘
Systeemquery's weergeven
Actieve query's
In de pg_stat_activity weergave ziet u welke query's momenteel worden uitgevoerd. U kunt filteren om de actieve uitvoeringen te vinden, samen met de proces-id van de back-end:
SELECT pid, query, state
FROM pg_stat_activity
WHERE state != 'idle';
Waarom wachten query's?
We kunnen ook query's uitvoeren om de meest voorkomende redenen te zien waarom niet-actieve query's wachten. Raadpleeg de PostgreSQL-documentatie voor een uitleg van de redenen.
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;
Voorbeelduitvoer bij gelijktijdig uitvoeren pg_sleep in een afzonderlijke query:
┌─────────────────┬──────────────────────┐
│ type │ number_of_occurences │
├─────────────────┼──────────────────────┤
│ ∅ │ 1 │
│ PgSleep:Timeout │ 1 │
└─────────────────┴──────────────────────┘
Indextrefferfrequentie
Deze query biedt u de treffersnelheid van uw index op alle knooppunten. Indextrefferfrequentie is handig bij het bepalen hoe vaak indexen worden gebruikt bij het uitvoeren van query's. Een waarde van 95% of hoger is ideaal.
-- 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$);
Voorbeelduitvoer:
┌───────────┬────────────────┐
│ nodename │ index_hit_rate │
├───────────┼────────────────┤
│ 10.0.0.16 │ 96.0 │
│ 10.0.0.20 │ 98.0 │
└───────────┴────────────────┘
Cachetrefferfrequentie
De meeste toepassingen hebben doorgaans toegang tot een kleine fractie van hun totale gegevens tegelijk. PostgreSQL bewaart veelgebruikte gegevens in het geheugen om trage leesbewerkingen van de schijf te voorkomen. U kunt er statistieken over zien in de weergave pg_statio_user_tables .
Een belangrijke meting is welk percentage gegevens afkomstig is van de geheugencache versus de schijf in uw workload:
-- 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$);
Voorbeelduitvoer:
┌───────────┬──────────┬─────────────────────┐
│ heap_read │ heap_hit │ cache_hit_rate │
├───────────┼──────────┼─────────────────────┤
│ 1 │ 132 │ 99.2481203007518796 │
└───────────┴──────────┴─────────────────────┘
Als u een verhouding hebt die aanzienlijk lager is dan 99%, kunt u overwegen om de cache die beschikbaar is voor uw database te verhogen.
Volgende stappen
- Meer informatie over andere systeemtabellen die nuttig zijn voor diagnostische gegevens