중요합니다
Azure Cosmos DB for PostgreSQL은 더 이상 새 프로젝트에 지원되지 않습니다. 새 프로젝트에는 이 서비스를 사용하지 마세요. 대신 다음 두 서비스 중 하나를 사용합니다.
99.999% SLA(가용성 서비스 수준 약정), 인스턴트 자동 크기 조정 및 여러 지역에서 자동 장애 조치(failover)를 사용하는 대규모 시나리오용으로 설계된 분산 데이터베이스 솔루션에는 NoSQL용 Azure Cosmos DB를 사용합니다.
오픈 소스 Citus 확장을 사용하여 분할된 PostgreSQL용 Azure Database for PostgreSQL의 탄력적 클러스터 기능을 사용합니다.
특정 테넌트에 대한 데이터를 포함하는 노드 찾기
다중 테넌트 사용 사례에서 특정 테넌트에 대한 행을 포함하는 작업자 노드를 확인할 수 있습니다. 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%보다 훨씬 낮은 경우 데이터베이스에 사용할 수 있는 캐시를 늘리는 것이 좋습니다.
다음 단계
- 진단에 유용한 다른 시스템 테이블에 대해 알아보기