Nützliche Diagnoseabfragen in Azure Cosmos DB for PostgreSQL
GILT FÜR: 
Azure Cosmos DB for PostgreSQL (unterstützt von der Citus-Datenbankerweiterung auf PostgreSQL)
Ermitteln, welcher Knoten Daten für einen bestimmten Mandanten enthält
In Anwendungsfällen mit mehreren Mandanten lässt sich ermitteln, welcher Workerknoten die Zeilen für einen bestimmten Mandanten enthält. Azure Cosmos DB for PostgreSQL gruppiert die Zeilen verteilter Tabellen in Shards und platziert jeden Shard auf einem Workerknoten im Cluster.
Nehmen wir einmal an, die Mandanten unserer Anwendung sind Stores, und wir möchten herausfinden, welcher Workerknoten die Daten für den Store mit der ID 4 enthält. Anders gesagt: Wir möchten die Platzierung für den Shard ermitteln, der Zeilen enthält, deren Verteilungsspalte den Wert 4 aufweist:
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)
);
Die Ausgabe enthält den Host und den Port der Workerdatenbank.
┌─────────┬────────────┬─────────────┬───────────┬──────────┬─────────────┐
│ shardid │ shardstate │ shardlength │ nodename │ nodeport │ placementid │
├─────────┼────────────┼─────────────┼───────────┼──────────┼─────────────┤
│ 102009 │ 1 │ 0 │ 10.0.0.16 │ 5432 │ 2 │
└─────────┴────────────┴─────────────┴───────────┴──────────┴─────────────┘
Ermitteln, welcher Knoten ein verteiltes Schema hostet
Verteilte Schemas werden automatisch einzelnen Zusammenstellungsgruppen zugeordnet, sodass die in diesen Schemas erstellten Tabellen in zusammengestellte verteilte Tabellen ohne Shardschlüssel konvertiert werden. Sie können herausfinden, wo sich ein verteiltes Schema befindet, indem Sie citus_shards mit citus_schemas verknüpfen:
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
Sie können auch citus_shards direkt abfragen, indem Sie nach dem Tabellentyp des Schemas filtern, um eine detaillierte Auflistung für alle Tabellen zu erhalten.
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
Ermitteln der Verteilungsspalte für eine Tabelle
Jede verteilte Tabelle besitzt eine „Verteilungsspalte“. (Weitere Informationen finden Sie unter Modellieren verteilter Daten.) Es kann wichtig sein, zu wissen, um welche Spalte es sich handelt. Ein Beispiel: Beim Einbinden oder Filtern von Tabellen werden möglicherweise Fehlermeldungen mit Hinweisen wie „Fügen Sie der Verteilungsspalte einen Filter hinzu“ angezeigt.
Die pg_dist_*-Tabellen auf dem Koordinatorknoten enthalten diverse Metadaten über die verteilte Datenbank. Insbesondere enthält pg_dist_partition Informationen zur Verteilungsspalte für jede Tabelle. Sie können eine praktische Hilfsfunktion verwenden, um den Namen der Verteilungsspalte in den allgemeinen Details der Metadaten zu suchen. Hier sehen Sie Beispiel sowie die zugehörige Ausgabe:
-- 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;
Beispielausgabe:
┌───────────────┐
│ dist_col_name │
├───────────────┤
│ store_id │
└───────────────┘
Erkennen von Sperren
Diese Abfrage wird auf allen Workerknoten ausgeführt und identifiziert Sperren, die Dauer ihrer Aufrechterhaltung sowie die problematischen Abfragen:
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$);
Beispielausgabe:
┌───────────────────────────────────────────────────────────────────────────────────┐
│ 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""}") │
└───────────────────────────────────────────────────────────────────────────────────┘
Abfragen der Größe von Shards
Diese Abfrage gibt die Größe jedes Shards der verteilten Tabelle my_distributed_table zurück:
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$);
Beispielausgabe:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ 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"} │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Abfragen der Größe aller verteilten Tabellen
Diese Abfrage ruft eine Liste mit allen Größen für jede verteilte Tabelle sowie die Größen der zugehörigen Indizes ab.
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';
Beispielausgabe:
┌───────────────┬────────────┐
│ tablename │ total_size │
├───────────────┼────────────┤
│ github_users │ 39 MB │
│ github_events │ 98 MB │
└───────────────┴────────────┘
Beachten Sie, dass es weitere Azure Cosmos DB for PostgreSQL-Funktionen für die Abfrage der Größe einer verteilten Tabelle gibt. Informationen dazu finden Sie unter Ermitteln der Tabellengröße.
Identifizieren nicht verwendeter Indizes
Die folgende Abfrage identifiziert nicht verwendete Indizes auf Workerknoten für eine bestimmte verteilte Tabelle (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$);
Beispielausgabe:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │ result │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│ 102008 │ t │ │
│ 102009 │ t │ {"public.my_distributed_table_102009##some_index_102009##28 MB##0"} │
│ 102010 │ t │ │
│ 102011 │ t │ │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Überwachen der Anzahl von Clientverbindungen
Die folgende Abfrage zählt die auf dem Koordinatorknoten geöffneten Verbindungen und gruppiert sie anhand des Typs.
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
Beispielausgabe:
┌────────┬───────┐
│ state │ count │
├────────┼───────┤
│ active │ 3 │
│ idle │ 3 │
│ ∅ │ 6 │
└────────┴───────┘
Anzeigen von Systemabfragen
Aktive Abfragen
Die Ansicht pg_stat_activity zeigt, welche Abfragen derzeit ausgeführt werden. Sie können eine Filterung durchführen, um die aktiv ausgeführten Abfragen anzuzeigen, zusammen mit der Prozess-ID des zugehörigen Back-Ends:
SELECT pid, query, state
FROM pg_stat_activity
WHERE state != 'idle';
Warum gibt es wartende Abfragen?
Wir können auch die häufigsten Gründe für wartende Abfragen anzeigen, die sich nicht im Leerlauf befinden. Eine Erläuterung der Gründe finden Sie in der PostgreSQL-Dokumentation.
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;
Beispielausgabe bei gleichzeitiger Ausführung von pg_sleep in einer separaten Abfrage:
┌─────────────────┬──────────────────────┐
│ type │ number_of_occurences │
├─────────────────┼──────────────────────┤
│ ∅ │ 1 │
│ PgSleep:Timeout │ 1 │
└─────────────────┴──────────────────────┘
Indextrefferquote
Diese Abfrage stellt die Indextrefferquote für alle Knoten bereit. Die Indextrefferrate ist ein nützlicher Wert, wenn Sie ermitteln möchten, wie häufig Indizes bei Abfragen verwendet werden. Ein Wert von 95 % oder höher ist 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$);
Beispielausgabe:
┌───────────┬────────────────┐
│ nodename │ index_hit_rate │
├───────────┼────────────────┤
│ 10.0.0.16 │ 96.0 │
│ 10.0.0.20 │ 98.0 │
└───────────┴────────────────┘
Cachetrefferrate
Normalerweise greifen die meisten Anwendungen jeweils nur auf einen Bruchteil der Datenmenge zu, die ihnen insgesamt zur Verfügung steht. Mit PostgreSQL werden häufig verwendete Daten im Arbeitsspeicher vorgehalten, um zu vermeiden, dass sie langsam vom Datenträger gelesen werden. Statistiken dazu finden Sie in der Ansicht pg_statio_user_tables.
Eine wichtige Messgröße ist der Prozentsatz an Daten in Ihrer Workload, die aus dem Arbeitsspeichercache gelesen werden, gegenüber den Daten, die von der Festplatte stammen:
-- 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$);
Beispielausgabe:
┌───────────┬──────────┬─────────────────────┐
│ heap_read │ heap_hit │ cache_hit_rate │
├───────────┼──────────┼─────────────────────┤
│ 1 │ 132 │ 99.2481203007518796 │
└───────────┴──────────┴─────────────────────┘
Wenn das Verhältnis deutlich unter 99 % liegt, sollten Sie u. U. in Betracht ziehen, den für Ihre Datenbank verfügbaren Cache zu vergrößern.
Nächste Schritte
- Informieren Sie sich über andere Systemtabellen, die bei Diagnosen nützlich sein können.