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%보다 훨씬 낮은 경우 데이터베이스에 사용할 수 있는 캐시를 늘리는 것이 좋습니다.
다음 단계
- 진단에 유용한 다른 시스템 테이블에 대해 알아보기