튜닝은 감이 아니라 관찰에서 시작한다
데이터베이스 성능 튜닝은 "인덱스를 하나 더 추가해 볼까?"에서 시작하면 안 된다. 먼저 무엇이 느린지, 언제 느린지, 얼마나 자주 느린지 확인해야 한다. 같은 "느림"이라도 원인은 다르다. 특정 API만 느릴 수 있고, 전체 쿼리가 느릴 수 있으며, 쓰기 요청만 밀릴 수도 있다. CPU가 부족한 경우도 있지만, 실제로는 락 대기나 커넥션 풀 고갈이 원인일 때도 많다.
운영에서 성능을 볼 때는 평균보다 분포를 봐야 한다. 평균 응답 시간이 50ms여도 p95가 2초라면 일부 사용자는 이미 장애를 경험하고 있다. p95는 전체 요청 중 95%가 이 시간 안에 끝났다는 뜻이고, p99는 더 극단적인 지연을 보여준다. 사용자는 평균을 경험하지 않는다. 자신이 보낸 요청 하나의 지연을 경험한다.
좋은 튜닝 절차는 단순하다.
증상을 지표로 확인한다.
느린 쿼리나 대기 원인을 좁힌다.
실행 계획과 자원 사용량을 함께 본다.
작은 변경을 적용한다.
변경 전후를 같은 기준으로 비교한다.
부작용이 있으면 되돌린다.
이 흐름을 지키면 불필요한 인덱스, 과도한 설정 변경, 원인 없는 서버 증설을 줄일 수 있다.
AI 생성 이미지
반드시 봐야 할 핵심 지표
첫 번째는 쿼리 지연 시간이다. 쿼리별 평균 실행 시간, p95, p99, 최대 실행 시간을 본다. 단 한 번 30초 걸린 쿼리보다, 200ms씩 하루에 수백만 번 실행되는 쿼리가 더 큰 병목일 수 있다. 그래서 "가장 오래 걸린 쿼리"와 "총 시간을 가장 많이 소비한 쿼리"를 함께 봐야 한다.
두 번째는 처리량이다. 초당 쿼리 수, 초당 트랜잭션 수, 읽기와 쓰기 비율을 확인한다. 지연 시간이 증가했는데 처리량도 함께 증가했다면 단순히 부하가 늘어난 상황일 수 있다. 반대로 처리량은 줄었는데 지연 시간이 늘었다면 락, I/O 대기, 커넥션 고갈처럼 요청이 제대로 처리되지 못하고 있을 가능성이 크다.
세 번째는 커넥션 수다. 애플리케이션의 커넥션 풀 사용률, 데이터베이스의 활성 세션 수, 대기 중인 커넥션 수를 함께 봐야 한다. 커넥션이 많다고 항상 좋은 것은 아니다. 너무 많은 커넥션은 데이터베이스가 동시에 처리해야 할 작업을 늘리고, 메모리와 컨텍스트 스위칭 비용을 증가시킨다. 반대로 풀이 너무 작으면 애플리케이션에서 요청이 커넥션을 기다리며 지연된다.
네 번째는 락 대기와 데드락이다. CPU와 디스크가 한가한데 쿼리가 느리다면 락을 의심해야 한다. 긴 트랜잭션이 특정 행이나 테이블을 오래 잡고 있으면 뒤따르는 요청이 줄줄이 대기한다. 주문 상태 변경, 재고 차감, 정산 처리처럼 같은 데이터를 자주 수정하는 기능에서 특히 중요하다.
다섯 번째는 버퍼 캐시 적중률이다. 데이터베이스는 자주 읽는 페이지를 메모리에 보관한다. 캐시 적중률이 낮아지면 디스크에서 읽는 비율이 늘고 지연 시간이 커질 수 있다. 다만 캐시 적중률 하나만 보고 메모리를 늘리면 안 된다. 대량 분석 쿼리처럼 원래 많은 데이터를 읽어야 하는 작업인지, 인덱스가 없어 불필요하게 많은 페이지를 읽는지 구분해야 한다.
PostgreSQL에서는 순차 스캔 횟수, 인덱스 스캔 횟수, 읽은 행과 반환한 행의 차이, 임시 파일 쓰기량 같은 지표가 쿼리 효율을 판단하는 단서가 된다. 예를 들어 임시 파일 쓰기가 자주 발생하면 정렬이나 해시 작업이 메모리에 담기지 못하고 디스크로 밀려났을 가능성이 있다1.
느린 쿼리 로그와 실행 계획을 연결하기
느린 쿼리 로그는 출발점이지 결론이 아니다. 로그에 어떤 SQL이 느리다고 찍혔다면 다음 질문을 해야 한다.
항상 느린가, 특정 시간대에만 느린가?
평균은 낮지만 호출 횟수가 많아 총 비용이 큰가?
특정 파라미터에서만 느린가?
실행 계획이 바뀐 적이 있는가?
느린 시간이 실제 실행 시간인가, 락 대기나 I/O 대기가 포함된 시간인가?
실무에서는 쿼리를 정규화해서 본다. 예를 들어 WHERE user_id = 1과 WHERE user_id = 2는 같은 형태의 쿼리로 묶어야 한다. 그래야 특정 SQL 패턴이 전체 부하에서 차지하는 비중을 알 수 있다.
그다음 실행 계획을 본다. 실행 계획은 데이터베이스가 어떤 순서로 테이블을 읽고, 어떤 인덱스를 사용하고, 조인을 어떻게 수행하며, 정렬이나 집계를 어디서 처리하는지 보여준다. PostgreSQL의 EXPLAIN ANALYZE, MySQL의 EXPLAIN ANALYZE, SQL Server의 실제 실행 계획처럼 DBMS마다 도구는 다르지만 목적은 같다.
실행 계획에서 특히 확인할 것은 다음이다.
테이블 전체를 읽는지, 인덱스로 필요한 범위만 읽는지
예상 행 수와 실제 행 수가 크게 다른지
조인 순서가 비효율적인지
정렬이나 해시 집계가 디스크로 spill 되는지
인덱스를 사용했지만 너무 많은 행을 다시 필터링하는지
LIMIT가 있는데도 많은 행을 먼저 정렬하고 있지는 않은지
예상 행 수와 실제 행 수가 크게 다르면 통계 정보가 오래되었거나 데이터 분포가 치우쳐 있을 수 있다. 이때 무조건 인덱스를 추가하기보다 통계 갱신, 쿼리 조건 변경, 복합 인덱스 순서 조정을 함께 검토해야 한다.
SQL Server의 Query Store처럼 쿼리 텍스트, 실행 계획, 런타임 통계, 계획 변경 이력을 함께 보관하는 기능은 성능 회귀를 찾는 데 유용하다. 같은 쿼리가 어제는 빠르고 오늘은 느려졌다면, 데이터 증가 때문인지 실행 계획 변경 때문인지 비교할 수 있다2.
병목 자원을 구분하는 방법
성능 문제를 해결하려면 어느 자원이 한계에 도달했는지 구분해야 한다.
CPU 병목은 CPU 사용률이 지속적으로 높고, 동시에 복잡한 연산 쿼리가 늘어날 때 의심한다. 대량 정렬, 집계, 함수 계산, 비효율적인 조인, 너무 많은 행 필터링이 원인일 수 있다. 이 경우 인덱스가 도움이 될 수 있지만, 쿼리가 실제로 줄일 수 있는 행을 많이 읽고 있는지 먼저 봐야 한다.
메모리 병목은 캐시 적중률 하락, 임시 파일 증가, 스왑 사용, 정렬 작업 지연으로 나타난다. 작업 메모리를 늘리면 정렬이나 해시 조인이 빨라질 수 있지만, 동시 쿼리가 많으면 전체 메모리 사용량이 급증한다. 설정값은 단일 쿼리가 아니라 동시 실행 수를 기준으로 판단해야 한다.
디스크 I/O 병목은 읽기·쓰기 지연 시간, IOPS, 큐 길이, 체크포인트나 플러시 시점의 지연으로 드러난다. 인덱스가 없어서 많은 페이지를 읽는 경우도 있고, 반대로 인덱스가 너무 많아 쓰기마다 갱신 비용이 커진 경우도 있다. 읽기 병목인지 쓰기 병목인지 구분하지 않으면 잘못된 처방을 내리기 쉽다.
네트워크 병목은 데이터베이스와 애플리케이션 사이의 지연, 큰 결과 집합 전송, 지나치게 많은 왕복 요청에서 발생한다. DB가 빠르게 결과를 만들었는데 애플리케이션 응답이 느리다면 결과 크기와 네트워크 구간을 확인해야 한다. 필요한 컬럼만 조회하고, N+1 쿼리를 줄이고, 같은 리전에 배치하는 것도 튜닝이다.
락 병목은 자원 사용률이 낮은데 요청이 대기할 때 나타난다. 이 경우 서버를 키워도 해결되지 않는다. 긴 트랜잭션을 줄이고, 같은 행을 갱신하는 순서를 통일하고, 필요한 경우 조건부 업데이트나 작업 큐 분산을 적용해야 한다. 모니터링 도구에서 wait event나 blocking 정보를 함께 보면 지연이 CPU 계산 때문인지 대기 때문인지 좁히기 쉽다3.
테이블 비대화와 통계 정보도 성능이다
운영 데이터베이스는 시간이 지나면 구조가 변한다. 행이 삽입되고, 수정되고, 삭제된다. 이 과정에서 테이블과 인덱스가 실제 유효 데이터보다 커질 수 있다. 특히 MVCC를 사용하는 PostgreSQL에서는 업데이트나 삭제된 행이 바로 물리적으로 사라지지 않고, 나중에 정리될 죽은 튜플로 남는다. VACUUM은 이런 공간을 재사용 가능하게 만들고, 정리가 늦어지면 테이블 비대화, 디스크 사용량 증가, 쿼리 성능 저하로 이어질 수 있다4.
테이블 비대화가 생기면 같은 조건을 조회해도 더 많은 페이지를 읽어야 한다. 인덱스도 커지고, 캐시에 올려야 할 데이터가 늘어난다. 결국 메모리 효율이 떨어지고 I/O가 증가한다. 그래서 성능 튜닝은 쿼리만 보는 일이 아니다. 테이블 크기, 인덱스 크기, 죽은 행 비율, 자동 정리 작업의 지연 여부도 함께 봐야 한다.
통계 정보도 중요하다. 옵티마이저는 통계를 보고 어떤 인덱스를 쓸지, 어떤 테이블을 먼저 조인할지 결정한다. 통계가 오래되었거나 실제 데이터 분포를 제대로 반영하지 못하면 잘못된 실행 계획을 선택할 수 있다. 작은 테이블이라고 예상했는데 실제로는 수백만 행을 읽거나, 선택도가 높은 조건이라고 판단했는데 실제로는 대부분의 행을 읽는 식이다.
PostgreSQL의 ANALYZE, MySQL의 ANALYZE TABLE, SQL Server의 통계 갱신은 이런 판단 근거를 새로 만드는 작업이다. 자동 통계 갱신이 있더라도 대량 백필, 대규모 삭제, 특정 테넌트 데이터 급증 이후에는 직접 확인해야 한다.
안전하게 튜닝하는 절차
성능 개선은 변경 작업이다. 따라서 배포와 같은 수준의 안전 절차가 필요하다.
먼저 가설을 한 문장으로 적는다. "주문 목록 API가 느린 이유는 orders(user_id, created_at) 인덱스가 없어 최근 주문 조회에서 많은 행을 정렬하기 때문이다."처럼 원인과 개선 방법이 드러나야 한다. "DB가 느려서 인덱스를 추가한다"는 가설이 아니다.
다음으로 변경 전 기준값을 저장한다. 쿼리 실행 시간, 호출 횟수, 총 소요 시간, 실행 계획, 읽은 행 수, 읽은 페이지 수, 락 대기, CPU와 I/O 지표를 기록한다. 가능하면 같은 요일, 같은 시간대와 비교한다. 트래픽 패턴이 다른 시간대를 비교하면 개선 효과를 착각할 수 있다.
그다음 작게 바꾼다. 인덱스를 추가한다면 운영 DB에서 락 영향을 확인하고, DBMS가 제공하는 온라인 생성 옵션을 검토한다. 쿼리를 바꾼다면 일부 트래픽에만 적용하거나 기능 플래그로 되돌릴 수 있게 한다. 설정값을 바꿀 때는 한 번에 여러 값을 조정하지 않는다. 여러 변경을 동시에 넣으면 무엇이 효과를 냈는지 알 수 없다.
변경 후에는 같은 지표로 다시 측정한다. 목표 쿼리의 p95가 줄었는지, 총 DB 부하가 줄었는지, 쓰기 지연이나 복제 지연이 늘지는 않았는지 확인한다. 읽기 쿼리 하나를 빠르게 하려고 인덱스를 추가했는데 쓰기 성능이 크게 나빠졌다면 전체 시스템 관점에서는 실패일 수 있다.
마지막으로 롤백 방법을 준비한다. 코드 변경은 되돌릴 수 있어야 하고, 인덱스 추가는 제거 가능해야 하며, 설정 변경은 이전 값이 기록되어 있어야 한다. 데이터 구조를 바꾸는 변경은 단순 롤백이 어려울 수 있으므로, 이전 장에서 다룬 것처럼 롤포워드 전략까지 준비해야 한다.
성능 튜닝의 핵심은 빠른 결론이 아니라 좁혀 가는 과정이다. 지표를 보고, 병목을 가정하고, 실행 계획으로 확인하고, 작은 변경을 적용한 뒤 다시 측정한다. 이 반복을 습관화하면 데이터베이스는 더 이상 알 수 없는 검은 상자가 아니라, 관찰하고 개선할 수 있는 시스템이 된다.
참고
1Key metrics for PostgreSQL monitoring
이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.