Azure Cosmos DB for PostgreSQL 中有用的诊断查询
适用对象: Azure Cosmos DB for PostgreSQL(由 PostgreSQL 的 Citus 数据库扩展提供支持)
确定哪个节点包含特定租户的数据
在多租户用例中,我们可以确定哪个工作器节点包含特定租户的行。 Azure Cosmos DB for PostgreSQL 将分布式表的行分组成分片,并将每个分片放在群集中的某个工作器节点上。
假设应用程序的租户是商店,而我们想要确定哪个工作器节点包含指定了商店 ID=4 的数据。 换言之,我们想要查找包含分布列中具有值 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)
);
输出将包含工作器数据库的主机和端口。
┌─────────┬────────────┬─────────────┬───────────┬──────────┬─────────────┐
│ shardid │ shardstate │ shardlength │ nodename │ nodeport │ placementid │
├─────────┼────────────┼─────────────┼───────────┼──────────┼─────────────┤
│ 102009 │ 1 │ 0 │ 10.0.0.16 │ 5432 │ 2 │
└─────────┴────────────┴─────────────┴───────────┴──────────┴─────────────┘
找到哪个节点托管分布式架构
分布式架构会自动与各个场地租用组关联,这样在这些架构中创建的表会转换为没有分片键的场地租用分布式表。 可以通过将 citus_shards
与 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
还可以直接查询 citus_shards
,筛选到架构表类型,以获取所有表的详细列表。
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
查找表的分布列
每个分布式表都有一个“分布列”。(有关详细信息,请参阅分布式数据建模。)知道它是哪一列可能会很重要。 例如,在联接或筛选表时,可能会现错误消息,其中包含类似于“将筛选器添加到分布列”的提示。
协调器节点上的 pg_dist_*
表包含有关分布式数据库的各种元数据。 具体而言,pg_dist_partition
包含有关每个表的分布列的信息。 可以使用便捷的实用工具函数在元数据的低级别详细信息中查找分布列名。 下面是一个示例及其输出:
-- 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;
示例输出:
┌───────────────┐
│ dist_col_name │
├───────────────┤
│ store_id │
└───────────────┘
检测锁
此查询将对所有工作器节点运行,并识别锁、锁保持打开状态的时长,以及有问题的查询:
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$);
示例输出:
┌───────────────────────────────────────────────────────────────────────────────────┐
│ 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""}") │
└───────────────────────────────────────────────────────────────────────────────────┘
查询分片的大小
此查询将提供名为 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$);
示例输出:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ 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"} │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
查询所有分布式表的大小
此查询获取每个分布式表的大小及其索引大小的列表。
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';
示例输出:
┌───────────────┬────────────┐
│ tablename │ total_size │
├───────────────┼────────────┤
│ github_users │ 39 MB │
│ github_events │ 98 MB │
└───────────────┴────────────┘
请注意,其他某些 Azure Cosmos DB for PostgreSQL 函数也可用于查询分布式表的大小,具体请参阅确定表大小。
识别未使用的索引
以下查询将识别给定分布式表 (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$);
示例输出:
┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │ result │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│ 102008 │ t │ │
│ 102009 │ t │ {"public.my_distributed_table_102009##some_index_102009##28 MB##0"} │
│ 102010 │ t │ │
│ 102011 │ t │ │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
监视客户端连接计数
以下查询统计协调器上打开的连接数,并按类型将这些连接分组。
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
示例输出:
┌────────┬───────┐
│ state │ count │
├────────┼───────┤
│ active │ 3 │
│ idle │ 3 │
│ ∅ │ 6 │
└────────┴───────┘
查看系统查询
活动查询数
pg_stat_activity
视图显示当前正在执行的查询。 你可以筛选以查找主动执行的进程及其后端的进程 ID:
SELECT pid, query, state
FROM pg_stat_activity
WHERE state != 'idle';
查询为何正在等待
我们还可以通过查询查看非空闲查询正在等待的最常见原因。 如需这些原因的说明,请参阅 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;
在单独查询中并发运行 pg_sleep
时的示例输出:
┌─────────────────┬──────────────────────┐
│ type │ number_of_occurences │
├─────────────────┼──────────────────────┤
│ ∅ │ 1 │
│ PgSleep:Timeout │ 1 │
└─────────────────┴──────────────────────┘
索引命中率
此查询将提供所有节点中的索引命中率。 索引点击率有助于确定查询时索引的使用频率。 值在 95% 或以上比较理想。
-- 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$);
示例输出:
┌───────────┬────────────────┐
│ nodename │ index_hit_rate │
├───────────┼────────────────┤
│ 10.0.0.16 │ 96.0 │
│ 10.0.0.20 │ 98.0 │
└───────────┴────────────────┘
缓存命中率
大多数应用程序通常只是一次性地访问其总数据的一小部分。 PostgreSQL 将频繁访问的数据保存在内存中,以避免从磁盘缓慢读取数据。 可以在 pg_statio_user_tables 视图中查看相关统计信息。
一个重要的度量是内存缓存中的数据与工作负载的磁盘中的数据的百分比:
-- 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$);
示例输出:
┌───────────┬──────────┬─────────────────────┐
│ heap_read │ heap_hit │ cache_hit_rate │
├───────────┼──────────┼─────────────────────┤
│ 1 │ 132 │ 99.2481203007518796 │
└───────────┴──────────┴─────────────────────┘
如果你发现比率明显低于 99%,则可能需要考虑增加可供数据库使用的缓存。
后续步骤
- 了解可用于诊断的其他系统表