Przydatne zapytania diagnostyczne w usłudze Azure Cosmos DB for PostgreSQL
DOTYCZY: 
Usługa Azure Cosmos DB for PostgreSQL (obsługiwana przez rozszerzenie bazy danych Citus do bazy danych PostgreSQL)
Znajdowanie węzła zawierającego dane dla określonej dzierżawy
W przypadku użycia wielodostępnego możemy określić, który węzeł procesu roboczego zawiera wiersze dla określonej dzierżawy. Usługa Azure Cosmos DB for PostgreSQL grupuje wiersze tabel rozproszonych w fragmenty i umieszcza każdy fragment w węźle roboczym w klastrze.
Załóżmy, że dzierżawy aplikacji są magazynami i chcemy znaleźć, który węzeł procesu roboczego przechowuje dane dla identyfikatora magazynu =4. Innymi słowy, chcemy znaleźć umieszczenie fragmentu zawierającego wiersze, których kolumna rozkładu ma wartość 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)
);
Dane wyjściowe zawierają hosta i port bazy danych procesu roboczego.
┌─────────┬────────────┬─────────────┬───────────┬──────────┬─────────────┐
│ shardid │ shardstate │ shardlength │ nodename │ nodeport │ placementid │
├─────────┼────────────┼─────────────┼───────────┼──────────┼─────────────┤
│ 102009 │ 1 │ 0 │ 10.0.0.16 │ 5432 │ 2 │
└─────────┴────────────┴─────────────┴───────────┴──────────┴─────────────┘
Znajdowanie, który węzeł hostuje schemat rozproszony
Schematy rozproszone są automatycznie skojarzone z poszczególnymi grupami kolokacji, tak aby tabele utworzone w tych schematach były konwertowane na kolokowane tabele rozproszone bez klucza fragmentu. Możesz znaleźć miejsce, w którym znajduje się schemat rozproszony, dołączając do citus_shardscitus_schemaspolecenia :
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
Możesz również wykonać zapytanie citus_shards bezpośrednio filtrujące w dół do typu tabeli schematu, aby uzyskać szczegółową listę dla wszystkich tabel.
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
Znajdowanie kolumny dystrybucji dla tabeli
Każda tabela rozproszona ma "kolumnę dystrybucji". (Aby uzyskać więcej informacji, zobacz Modelowanie danych rozproszonych). Ważne jest, aby wiedzieć, która kolumna jest. Na przykład podczas dołączania lub filtrowania tabel mogą zostać wyświetlone komunikaty o błędach z wskazówkami, takimi jak "dodawanie filtru do kolumny dystrybucji".
Tabele pg_dist_* w węźle koordynacji zawierają różne metadane dotyczące rozproszonej bazy danych. W szczególności pg_dist_partition zawiera informacje o kolumnie dystrybucji dla każdej tabeli. Możesz użyć wygodnej funkcji narzędzia, aby wyszukać nazwę kolumny dystrybucji na podstawie szczegółów niskiego poziomu w metadanych. Oto przykład i jego dane wyjściowe:
-- 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;
Przykładowe wyjście:
┌───────────────┐
│ dist_col_name │
├───────────────┤
│ store_id │
└───────────────┘
Wykrywanie blokad
To zapytanie zostanie uruchomione we wszystkich węzłach procesu roboczego i zidentyfikuje blokady, jak długo zostały otwarte, oraz obraźliwe zapytania:
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$);
Przykładowe wyjście:
┌───────────────────────────────────────────────────────────────────────────────────┐
│ 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""}") │
└───────────────────────────────────────────────────────────────────────────────────┘
Wykonywanie zapytań dotyczących rozmiaru fragmentów
To zapytanie zapewni rozmiar każdego fragmentu danej tabeli rozproszonej o nazwie 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$);
Przykładowe wyjście:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ 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"} │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Wykonywanie zapytań dotyczących rozmiaru wszystkich tabel rozproszonych
To zapytanie pobiera listę rozmiarów dla każdej tabeli rozproszonej oraz rozmiar ich indeksów.
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';
Przykładowe wyjście:
┌───────────────┬────────────┐
│ tablename │ total_size │
├───────────────┼────────────┤
│ github_users │ 39 MB │
│ github_events │ 98 MB │
└───────────────┴────────────┘
Zwróć uwagę, że istnieją inne funkcje usługi Azure Cosmos DB for PostgreSQL służące do wykonywania zapytań dotyczących rozmiaru tabeli rozproszonej, zobacz Określanie rozmiaru tabeli.
Identyfikowanie nieużywanych indeksów
Następujące zapytanie zidentyfikuje nieużywane indeksy w węzłach procesu roboczego dla danej tabeli rozproszonej (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$);
Przykładowe wyjście:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │ result │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│ 102008 │ t │ │
│ 102009 │ t │ {"public.my_distributed_table_102009##some_index_102009##28 MB##0"} │
│ 102010 │ t │ │
│ 102011 │ t │ │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Monitorowanie liczby połączeń klienta
Następujące zapytanie zlicza otwarte połączenia na koordynatorze i grupuje je według typu.
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
Przykładowe wyjście:
┌────────┬───────┐
│ state │ count │
├────────┼───────┤
│ active │ 3 │
│ idle │ 3 │
│ ∅ │ 6 │
└────────┴───────┘
Wyświetlanie zapytań systemowych
Aktywne zapytania
Widok pg_stat_activity pokazuje, które zapytania są obecnie wykonywane. Możesz filtrować, aby znaleźć aktywnie wykonywane, wraz z identyfikatorem procesu zaplecza:
SELECT pid, query, state
FROM pg_stat_activity
WHERE state != 'idle';
Dlaczego zapytania oczekują
Możemy również wykonywać zapytania, aby zobaczyć najczęstsze przyczyny oczekiwania zapytań bezczynnych. Aby uzyskać wyjaśnienie przyczyn, zapoznaj się z dokumentacją bazy danych 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;
Przykładowe dane wyjściowe podczas uruchamiania pg_sleep w osobnym zapytaniu współbieżnie:
┌─────────────────┬──────────────────────┐
│ type │ number_of_occurences │
├─────────────────┼──────────────────────┤
│ ∅ │ 1 │
│ PgSleep:Timeout │ 1 │
└─────────────────┴──────────────────────┘
Wskaźnik trafień indeksu
To zapytanie zapewni wskaźnik trafień indeksu we wszystkich węzłach. Wskaźnik trafień indeksu jest przydatny podczas określania, jak często indeksy są używane podczas wykonywania zapytań. Wartość 95% lub wyższa jest idealna.
-- 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$);
Przykładowe wyjście:
┌───────────┬────────────────┐
│ nodename │ index_hit_rate │
├───────────┼────────────────┤
│ 10.0.0.16 │ 96.0 │
│ 10.0.0.20 │ 98.0 │
└───────────┴────────────────┘
Szybkość trafień pamięci podręcznej
Większość aplikacji zazwyczaj uzyskuje dostęp do niewielkiej części danych całkowitych jednocześnie. Usługa PostgreSQL przechowuje często używane dane w pamięci, aby uniknąć wolnych operacji odczytu z dysku. Statystyki dotyczące tego można zobaczyć w widoku pg_statio_user_tables .
Ważnym pomiarem jest to, jaki procent danych pochodzi z pamięci podręcznej w porównaniu z dyskiem w obciążeniu:
-- 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$);
Przykładowe wyjście:
┌───────────┬──────────┬─────────────────────┐
│ heap_read │ heap_hit │ cache_hit_rate │
├───────────┼──────────┼─────────────────────┤
│ 1 │ 132 │ 99.2481203007518796 │
└───────────┴──────────┴─────────────────────┘
Jeśli okaże się, że stosunek jest znacznie niższy niż 99%, prawdopodobnie warto rozważyć zwiększenie pamięci podręcznej dostępnej dla bazy danych.
Następne kroki
- Dowiedz się więcej o innych tabelach systemowych, które są przydatne do diagnostyki