Užitečné diagnostické dotazy ve službě Azure Cosmos DB for PostgreSQL
PLATÍ PRO: 
Azure Cosmos DB for PostgreSQL (využívající rozšíření databáze Citus do PostgreSQL)
Zjištění, který uzel obsahuje data pro konkrétního tenanta
V případě použití s více tenanty můžeme určit, který pracovní uzel obsahuje řádky pro konkrétního tenanta. Azure Cosmos DB for PostgreSQL seskupuje řádky distribuovaných tabulek do horizontálních oddílů a umístí jednotlivé horizontální oddíly do pracovního uzlu v clusteru.
Předpokládejme, že tenanti naší aplikace jsou úložiště a chceme zjistit, který pracovní uzel obsahuje data pro ID úložiště ID=4. Jinými slovy, chceme najít umístění horizontálního oddílu obsahujícího řádky, jejichž distribuční sloupec má hodnotu 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)
);
Výstup obsahuje hostitele a port pracovní databáze.
┌─────────┬────────────┬─────────────┬───────────┬──────────┬─────────────┐
│ shardid │ shardstate │ shardlength │ nodename │ nodeport │ placementid │
├─────────┼────────────┼─────────────┼───────────┼──────────┼─────────────┤
│ 102009 │ 1 │ 0 │ 10.0.0.16 │ 5432 │ 2 │
└─────────┴────────────┴─────────────┴───────────┴──────────┴─────────────┘
Zjištění, který uzel je hostitelem distribuovaného schématu
Distribuovaná schémata se automaticky přidružují k jednotlivým skupinám kolokace, aby tabulky vytvořené v těchto schématech byly převedeny na společnělokované distribuované tabulky bez klíče horizontálního dělení. Distribuované schéma najdete tak, že se připojíte citus_shards k 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
Dotaz můžete také citus_shards přímo filtrovat podle typu tabulky schématu, abyste měli podrobný výpis pro všechny tabulky.
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
Vyhledání distribučního sloupce pro tabulku
Každá distribuovaná tabulka má "distribuční sloupec". (Další informace najdete v tématu Distribuované modelování dat.) Může být důležité vědět, který sloupec je. Například při připojování nebo filtrování tabulek se můžou zobrazovat chybové zprávy s nápovědou, jako je přidání filtru do distribučního sloupce.
Tabulky pg_dist_* v koordinačním uzlu obsahují různá metadata o distribuované databázi. pg_dist_partition Konkrétně obsahuje informace o distribučním sloupci pro každou tabulku. Pomocí vhodné funkce nástroje můžete vyhledat název distribučního sloupce z podrobností nízké úrovně v metadatech. Tady je příklad a jeho výstup:
-- 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;
Příklad výstupu:
┌───────────────┐
│ dist_col_name │
├───────────────┤
│ store_id │
└───────────────┘
Detekce zámků
Tento dotaz se spustí ve všech pracovních uzlech a identifikuje zámky, jak dlouho byly otevřené, a dotazy, které se mají vypnout:
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$);
Příklad výstupu:
┌───────────────────────────────────────────────────────────────────────────────────┐
│ 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""}") │
└───────────────────────────────────────────────────────────────────────────────────┘
Dotazování na velikost horizontálních oddílů
Tento dotaz vám poskytne velikost každého horizontálního oddílu dané distribuované tabulky s názvem 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$);
Příklad výstupu:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ 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"} │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Dotazování na velikost všech distribuovaných tabulek
Tento dotaz získá seznam velikostí pro každou distribuovanou tabulku a velikost jejich indexů.
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';
Příklad výstupu:
┌───────────────┬────────────┐
│ tablename │ total_size │
├───────────────┼────────────┤
│ github_users │ 39 MB │
│ github_events │ 98 MB │
└───────────────┴────────────┘
Všimněte si, že pro dotazování velikosti distribuované tabulky existují další funkce Azure Cosmos DB for PostgreSQL, viz určení velikosti tabulky.
Identifikace nepoužívaných indexů
Následující dotaz identifikuje nepoužívané indexy na pracovních uzlech pro danou distribuovanou tabulku (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$);
Příklad výstupu:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │ result │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│ 102008 │ t │ │
│ 102009 │ t │ {"public.my_distributed_table_102009##some_index_102009##28 MB##0"} │
│ 102010 │ t │ │
│ 102011 │ t │ │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
Monitorování počtu připojení klientů
Následující dotaz spočítá připojení otevřená v koordinátoru a seskupí je podle typu.
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
Příklad výstupu:
┌────────┬───────┐
│ state │ count │
├────────┼───────┤
│ active │ 3 │
│ idle │ 3 │
│ ∅ │ 6 │
└────────┴───────┘
Zobrazení systémových dotazů
Aktivní dotazy
Zobrazení pg_stat_activity ukazuje, které dotazy se aktuálně spouštějí. Můžete vyfiltrovat aktivní spouštění a ID procesu back-endu:
SELECT pid, query, state
FROM pg_stat_activity
WHERE state != 'idle';
Proč čekají dotazy
Můžeme se také dotazovat, abychom viděli nejčastější důvody, proč nečinné dotazy čekají. Vysvětlení důvodů najdete v dokumentaci k 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;
Příklad výstupu při souběžném spuštění pg_sleep v samostatném dotazu:
┌─────────────────┬──────────────────────┐
│ type │ number_of_occurences │
├─────────────────┼──────────────────────┤
│ ∅ │ 1 │
│ PgSleep:Timeout │ 1 │
└─────────────────┴──────────────────────┘
Míra dosažení indexu
Tento dotaz vám poskytne míru dosažení indexu ve všech uzlech. Míra dosažení indexů je užitečná při určování, jak často se indexy používají při dotazování. Hodnota 95 % nebo vyšší je ideální.
-- 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$);
Příklad výstupu:
┌───────────┬────────────────┐
│ nodename │ index_hit_rate │
├───────────┼────────────────┤
│ 10.0.0.16 │ 96.0 │
│ 10.0.0.20 │ 98.0 │
└───────────┴────────────────┘
Rychlost dosažení mezipaměti
Většina aplikací obvykle přistupuje k malému zlomku jejich celkových dat najednou. PostgreSQL uchovává často přístupná data v paměti, aby se zabránilo pomalému čtení z disku. Statistiky o něm můžete zobrazit v zobrazení pg_statio_user_tables .
Důležité měření je procento dat pocházejících z mezipaměti paměti a disku ve vaší úloze:
-- 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$);
Příklad výstupu:
┌───────────┬──────────┬─────────────────────┐
│ heap_read │ heap_hit │ cache_hit_rate │
├───────────┼──────────┼─────────────────────┤
│ 1 │ 132 │ 99.2481203007518796 │
└───────────┴──────────┴─────────────────────┘
Pokud zjistíte, že je poměr výrazně nižší než 99 %, pravděpodobně budete chtít zvážit zvýšení mezipaměti dostupné pro vaši databázi.
Další kroky
- Informace o dalších systémových tabulkách , které jsou užitečné pro diagnostiku