느린 PostgreSQL 쿼리 디버깅을 위한 체계적인 가이드
pg_stat_statements, EXPLAIN ANALYZE, 버퍼 읽기, 행 추정, 인덱스 및 검증을 통해 느린 PostgreSQL 쿼리를 디버깅하세요.
느린 PostgreSQL 쿼리 디버깅을 위한 체계적인 가이드
느린 PostgreSQL 쿼리는 더 이상 미스터리로 취급하지 않으면 수정하기 쉬워집니다. 데이터베이스는 일반적으로 선택한 경로, 예상한 행 수, 실제로 접근한 행 수, 캐시 또는 디스크에서 읽었는지 여부, 다른 세션을 기다렸는지 여부를 보여줄 수 있습니다.
제가 가장 자주 보는 실수는 "이 엔드포인트가 느리다"에서 바로 "인덱스를 생성하자"로 뛰어넘는 것입니다. 때로는 효과가 있습니다. 때로는 조건자가 인덱스를 사용할 수 없는 방식으로 작성되어 인덱스가 무시됩니다. 때로는 쿼리 자체는 괜찮지만 20분 동안 열려 있는 트랜잭션 뒤에서 차단되는 경우도 있습니다. 체계적인 접근 방식은 쿼리 형태, 플래너 추정, I/O, 메모리 및 잠금을 분리하기 때문에 시간을 절약해 줍니다.
쿼리 성능 병목 현상 이해하기
도구를 살펴보기 전에 PostgreSQL 쿼리 성능이 저하되는 일반적인 이유를 인식하는 것이 중요합니다. 이러한 문제는 일반적으로 몇 가지 주요 범주로 나뉩니다.
- 누락되거나 비효율적인 인덱스: 인덱스가 빠른 액세스를 제공할 수 있었음에도 데이터베이스가 대규모 테이블에서 순차 스캔을 수행하도록 강제됩니다.
- 최적이 아닌 쿼리 구조: 복잡한 조인, 불필요한 하위 쿼리 또는 함수의 잘못된 사용은 플래너를 혼란스럽게 할 수 있습니다.
- 오래된 통계: PostgreSQL은 효율적인 실행 계획을 수립하기 위해 통계에 의존합니다. 통계가 오래되면 플래너가 비효율적인 경로를 선택할 수 있습니다.
- 리소스 경합: 높은 I/O 대기 시간, 과도한 잠금 또는 PostgreSQL에 할당된 메모리 부족과 같은 문제.
1단계: 느린 쿼리 식별
느린 쿼리를 수정하려면 먼저 정확하게 식별해야 합니다. 사용자 불만에 의존하는 것은 비효율적입니다. 데이터베이스 자체의 경험적 데이터가 필요합니다.
pg_stat_statements 사용
프로덕션 환경에서 리소스를 많이 사용하는 쿼리를 추적하는 가장 효과적인 방법은 pg_stat_statements 확장을 사용하는 것입니다. 이 모듈은 데이터베이스에 대해 실행된 모든 쿼리의 실행 통계를 추적합니다.
확장 활성화 (슈퍼유저 권한 및 설정 리로드 필요):
-- 1. postgresql.conf에 나열되어 있는지 확인
-- shared_preload_libraries = 'pg_stat_statements'
-- 2. 데이터베이스에 연결하고 확장 생성
CREATE EXTENSION pg_stat_statements;
최상위 문제 쿼리 조회:
가장 많은 총 시간을 소비하는 쿼리를 찾으려면 다음 쿼리를 사용하십시오.
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM
pg_stat_statements
ORDER BY
total_exec_time DESC
LIMIT 10;
이전 PostgreSQL 버전에서는 이러한 열 이름이 total_time 및 mean_time일 수 있습니다. 서버가 노출하는 이름을 사용하십시오.
총 시간과 평균 시간은 다른 질문에 답합니다. 평균 20밀리초이지만 백만 번 실행되는 쿼리는 데이터베이스에서 가장 큰 비용이 될 수 있습니다. 한 시간에 한 번 40초 동안 실행되는 쿼리는 한 사용자에게는 고통스럽지만 전체 시스템에는 덜 중요할 수 있습니다. 둘 다 살펴보십시오.
역사적으로 비용이 많이 드는 쿼리가 아닌 현재 느린 쿼리가 필요한 경우 활성 세션을 확인하십시오.
SELECT pid, now() - query_start AS age, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;
2단계: EXPLAIN ANALYZE로 실행 계획 분석
느린 쿼리가 분리되면 다음 중요한 단계는 PostgreSQL이 어떻게 실행하고 있는지 이해하는 것입니다. EXPLAIN 명령은 의도된 계획을 보여주지만 EXPLAIN ANALYZE는 실제로 쿼리를 실행하고 각 단계에 소요된 실제 시간을 보고합니다.
구문 및 사용법
가장 자세한 출력을 위해 항상 느린 쿼리를 EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)으로 감싸십시오. BUFFERS 옵션은 디스크 I/O 활동을 보여주므로 중요합니다.
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM large_table lt
JOIN other_table ot ON lt.id = ot.lt_id
WHERE lt.status = 'active' AND lt.created_at > NOW() - INTERVAL '1 day';
출력 해석
출력은 가장 안쪽 노드가 먼저 실행되므로 아래에서 위로 그리고 오른쪽에서 왼쪽으로 읽습니다. 집중해야 할 주요 지표는 다음과 같습니다.
cost=: 플래너의 예상 비용이며 실제 경과 시간이 아닙니다. 밀리초가 아닌 계획 선택을 비교하는 데 사용하십시오.rows=: 해당 노드에서 처리된 예상 행 수입니다.actual time=: 이 특정 작업에 소요된 실제 시간(밀리초)입니다.rows=(실제): 이 노드에서 반환된 실제 행 수입니다.loops=: 이 노드가 실행된 횟수입니다(중첩 루프에서 종종 높음).
비효율성 발견:
- 대규모 테이블의 순차 스캔: 대규모 테이블 액세스가
Index Scan또는Bitmap Index Scan대신Seq Scan을 사용하는 경우 더 나은 인덱스가 필요할 가능성이 높습니다. - 예상 행과 실제 행 간의 큰 차이: 플래너가 10행을 예상했지만 노드가 실제로 1,000,000행을 처리한 경우 통계가 오래되었거나 플래너가 잘못된 선택을 한 것입니다.
- 조인/정렬에서 높은
actual time:Hash Join,Merge Join또는Sort작업에 과도한 시간이 소요되는 경우 종종 메모리 부족(work_mem) 또는 인덱스를 효과적으로 사용할 수 없음을 나타냅니다.
또한 Buffers 줄을 확인하십시오. shared hit는 PostgreSQL이 캐시에서 페이지를 찾았음을 의미합니다. shared read는 저장소에서 페이지를 읽어야 했음을 의미합니다. 쿼리가 느린 이유는 계획이 나쁘거나 계획은 합리적이지만 디스크에서 많은 양의 콜드 데이터를 읽기 때문일 수 있습니다.
팁: 복잡한 계획의 경우 explain.depesz.com과 같은 온라인 도구나 pgAdmin의 시각적 실행 계획 뷰어를 사용하여 결과를 그래픽으로 해석하십시오.
3단계: 일반적인 병목 현상 해결
EXPLAIN ANALYZE 결과를 기반으로 대상 수정을 적용하십시오.
인덱스 최적화
대규모 테이블에서 Seq Scan이 지배적이고 쿼리가 선택적이라면 WHERE, JOIN 및 ORDER BY 절에 사용된 열에 인덱스를 고려하십시오. 순차 스캔이 자동으로 나쁜 것은 아닙니다. 대부분의 행이 필요할 때 PostgreSQL이 올바르게 선택할 수 있습니다.
예: 쿼리가 status로 필터링한 다음 user_id로 조인하는 경우:
-- 더 빠른 조회 및 조인을 위한 복합 인덱스 생성
CREATE INDEX idx_large_table_status_user_id ON large_table (status, user_id);
프로덕션 시스템의 경우 쓰기를 차단하지 않아야 할 때 CREATE INDEX CONCURRENTLY를 사용하고 일반 트랜잭션 블록 내에서는 실행할 수 없음을 기억하십시오.
CREATE INDEX CONCURRENTLY idx_large_table_status_user_id
ON large_table (status, user_id);
통계 업데이트 (VACUUM ANALYZE)
플래너가 매우 부정확한 추정(예상 행과 실제 행 간의 불일치)을 하는 경우 테이블 통계 업데이트를 강제합니다.
ANALYZE VERBOSE table_name;
-- 활동이 많은 테이블의 경우 테이블별 자동 진공/분석 임계값을 낮추는 것을 고려하십시오.
메모리 튜닝
정렬 또는 해시 작업이 디스크로 유출되는 경우(BUFFERS 출력에서 높은 I/O 또는 느린 정렬로 표시됨) PostgreSQL의 사용 가능한 작업 메모리를 늘리십시오.
-- 특정 쿼리 테스트를 위해 세션 수준 work_mem 증가
SET work_mem = '128MB';
-- 또는 지속적인 성능 향상을 위해 postgresql.conf에서 전역적으로 설정
경고:
work_mem을 전역적으로 너무 높게 설정하면 많은 복잡한 쿼리가 동시에 실행될 때 시스템 메모리가 고갈될 수 있습니다. 서버 용량에 따라 신중하게 조정하십시오.
계획에서 정렬 또는 해시 유출 세부 정보를 찾으십시오. 최신 PostgreSQL 출력은 external merge Disk: ...와 같은 정렬 방법을 표시할 수 있으며, 이는 작업이 사용 가능한 메모리를 초과하여 임시 파일을 작성했음을 나타냅니다.
쿼리 재작성
때로는 구조 자체가 문제입니다. 인덱스 사용을 방지하는 조건인 비SARGable 조건자(예: WHERE 절에서 인덱스 열에 함수 적용)를 피하십시오.
비효율적 (인덱스 사용 방지):
WHERE DATE(created_at) = '2023-10-01'
효율적 (인덱스 사용 허용):
WHERE created_at >= '2023-10-01 00:00:00' AND created_at < '2023-10-02 00:00:00'
또 다른 일반적인 패턴은 애플리케이션에 필요한 것보다 훨씬 더 많은 열을 선택하는 것입니다. SELECT *는 계획 최적화를 어렵게 만들고 메모리 사용량을 증가시키며 인덱스 전용 스캔이 가능할 때 추가 힙 읽기를 강제할 수 있습니다. 핫 경로의 경우 열을 의도적으로 나열하십시오.
잠금 확인
EXPLAIN ANALYZE가 세션에서 빠르지만 애플리케이션이 느린 경우 쿼리가 유용한 실행 시간을 얻기 전에 대기 중일 수 있습니다. 잠금 대기를 확인하십시오.
SELECT pid, wait_event_type, wait_event, now() - query_start AS age, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;
그런 다음 차단자를 찾으십시오.
SELECT blocked.pid AS blocked_pid,
blocker.pid AS blocker_pid,
blocked.query AS blocked_query,
blocker.query AS blocker_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks
ON blocked_locks.pid = blocked.pid AND NOT blocked_locks.granted
JOIN pg_locks blocker_locks
ON blocker_locks.locktype = blocked_locks.locktype
AND blocker_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocker_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocker_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocker_locks.pid <> blocked_locks.pid
JOIN pg_stat_activity blocker
ON blocker.pid = blocker_locks.pid;
수정 사항은 애플리케이션 수준일 수 있습니다. 더 짧은 트랜잭션, 느린 외부 API 호출을 트랜잭션 외부로 이동, 불필요한 SELECT ... FOR UPDATE 방지 또는 경쟁 트랜잭션이 교착 상태에 빠지지 않도록 테이블 업데이트 순서 변경 등이 있습니다.
작은 예: 느린 대시보드 쿼리
대시보드가 몇 초마다 이 쿼리를 실행한다고 가정해 보겠습니다.
SELECT *
FROM orders
WHERE DATE(created_at) = CURRENT_DATE
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
테이블에는 수백만 개의 행이 있습니다. EXPLAIN ANALYZE는 순차 스캔, 필터에 의해 제거된 많은 수의 행 및 정렬을 보여줍니다. 첫 번째 본능은 created_at에 인덱스를 생성하는 것일 수 있지만 조건자가 열을 DATE(created_at)로 래핑하므로 created_at의 일반 인덱스는 덜 유용합니다.
날짜 필터를 범위로 다시 작성하십시오.
SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE created_at >= CURRENT_DATE
AND created_at < CURRENT_DATE + INTERVAL '1 day'
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
그런 다음 필터 및 정렬과 일치하는 인덱스를 고려하십시오.
CREATE INDEX CONCURRENTLY idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'paid';
이것은 보편적인 인덱스 레시피가 아닙니다. paid가 일반적인 대시보드 필터이고 애플리케이션이 일반적으로 최신 결제 주문을 요청할 때 작동합니다. 애플리케이션이 계정별로 많이 필터링하는 경우 더 나은 인덱스는 account_id로 시작할 수 있습니다. 요점은 쿼리에 언급된 단일 열이 아니라 실제 액세스 패턴을 중심으로 인덱스를 설계하는 것입니다.
변경 후 계획은 더 적은 행을 스캔하고 가급적이면 명시적 정렬을 피해야 합니다. 계획이 여전히 순차 스캔을 선택하는 경우 날짜 범위가 너무 넓은지, 통계가 오래되었는지 또는 프로덕션의 쿼리 매개변수가 테스트 사례와 다른지 확인하십시오.
4단계: 확인 및 모니터링
변경을 구현한 후에는 비교 가능한 매개변수를 사용하여 정확히 동일한 쿼리에서 EXPLAIN ANALYZE를 다시 실행하십시오. 목표가 항상 인덱스 스캔을 보는 것은 아닙니다. 목표는 더 적은 작업을 확인하는 것입니다. 필터에 의해 제거된 행 감소, 읽은 버퍼 감소, 디스크 유출 없음, 더 나은 행 추정 또는 비용이 많이 드는 노드에서 소요된 시간 감소 등입니다.
pg_stat_statements를 계속 모니터링하여 수정된 쿼리가 더 이상 최상위 문제 목록에 나타나지 않는지 확인하여 수정 사항이 전반적으로 긍정적인 영향을 미치는지 확인하십시오.
또한 인덱스를 추가한 후 쓰기 비용을 확인하십시오. 모든 새 인덱스는 삽입, 업데이트 및 삭제 중에 유지 관리되어야 합니다. 하나의 대시보드에 대한 완벽한 읽기 인덱스는 대량 수집 경로를 느리게 하는 경우 좋지 않은 절충안이 될 수 있습니다. 중요한 테이블의 경우 양쪽을 확인하십시오. 느린 쿼리가 개선되었는지, 쓰기 지연 시간이나 테이블 블로트가 이후에 악화되었는지 확인하십시오.
실제 장애 중에 도움이 되는 또 다른 습관은 현실적인 매개변수 값으로 테스트하는 것입니다. PostgreSQL은 10개의 행을 가진 고객과 천만 개의 행을 가진 고객에 대해 다른 계획을 선택할 수 있습니다. 애플리케이션이 준비된 명령문을 사용하는 경우 일반 계획은 psql에 붙여넣는 일회성 쿼리와 다르게 동작할 수도 있습니다. 프로덕션 문제가 하나의 테넌트, 하나의 계정 또는 하나의 날짜 범위에 영향을 미치는 경우 안전한 환경에서 가능한 한 가깝게 해당 형태를 재현하십시오.
쿼리가 파괴적이거나 EXPLAIN ANALYZE로 실행하기에 너무 비용이 많이 드는 경우 일반 EXPLAIN으로 시작하여 스테이징에서 실행하거나 롤백할 트랜잭션으로 테스트를 래핑하십시오. UPDATE 및 DELETE의 경우 프로덕션을 변경하기 전에 계획의 스캔 및 조인 부분에서 많은 것을 배울 수 있습니다.
변경 전후 계획, 타이밍 및 변경 이유에 대한 간단한 메모를 보관하십시오. 이 습관은 나중에 우발적인 성능 저하를 방지하고 다음 사람에게 스키마의 신비로운 인덱스 이름 대신 실제 설명을 제공합니다.