쿼리가 느려졌을 때의 기본 모델은 입력 분포 → 카디널리티 추정 → 계획 선택 → 연산 자원 → 실제 대기의 연결입니다. 이 사슬에서 어느 링크가 달라졌는지 확인해야 통계·인덱스·메모리·잠금 중 맞는 처방을 고를 수 있고, 추정 cost를 실제 시간으로 오해하는 진단을 피할 수 있습니다.
동일 SQL에서의 데이터·계획·자원 차이
한 고객의 주문이 10개일 때는 seek + lookup으로 몇 행만 찾는 계획이 좋아 보여도, 주문 10만 개인 고객에게 같은 계획을 쓰면 원본 행을 수없이 다시 읽게 됩니다. 여기서 seek는 인덱스에서 조건에 맞는 범위로 바로 들어가는 단계이고, lookup은 그 결과의 키로 원본 행을 다시 읽는 단계입니다. query fingerprint는 값만 다른 같은 형태의 SQL을 묶는 식별자이고, bind type은 parameter를 어떤 DB 타입으로 전달했는지, plan ID는 선택된 실행 계획을 식별하는 값이므로 같은 SQL처럼 보여도 다른 계획이 선택될 수 있습니다. 질의를 비교할 때는 정확한 환경·안전한 데이터 분포 특징·실행 시각·이 식별자들과 실제 읽기·대기를 함께 남겨 계획이 바뀐 것인지 같은 계획이 자원 때문에 느려진 것인지 나누고, 옵티마이저의 추정 cost 숫자는 계획 선택을 위한 상대적 모델이지 실제 밀리초가 아니므로 실행 시간으로 바로 읽지 않습니다.
코드 배포가 없어도 데이터 분포·통계·입력값·계획 캐시·메모리·잠금 상태가 달라질 수 있습니다. 느려진 시점을 곧바로 코드 변경의 결과로 단정하지 않습니다.
재현 입력을 “작은 고객”과 “큰 고객”처럼 안전한 분포 범주로 고정하고 같은 fingerprint, bind type, plan ID를 함께 기록합니다. 같은 plan ID인데 시간이 늘었다면 cache·I/O·lock 쪽을 먼저 보고, plan ID가 달라졌다면 추정 행과 연산자 변경을 먼저 봅니다. 이 순서가 없으면 모든 지연을 plan regression으로 오분류하게 됩니다.
추정 행 수와 실제 행 수의 최초 대규모 불일치 연산
| 관측 | 가능한 원인 | 후속 확인 |
|---|---|---|
| 추정 1, 실제 10만 | 분포·상관·parameter plan | join 반복·lookup·spill |
| 추정 과다, 실제 소수 | 부정확한 선택도 | 큰 grant·불필요 scan |
| 계획 같고 I/O 증가 | cache·스토리지·동시 작업 | 논리/물리 읽기·대기 |
| 계획 같고 lock 대기 증가 | 긴 transaction·충돌 | blocker·보유 시간 |
| 컴파일 증가 | 캐시·재컴파일·쿼리 변형 | 컴파일 CPU·계획 수 |
최종 반환 행만 보지 말고 인덱스에서 읽은 후보·필터 후 행·각 join의 반복 횟수를 봅니다. 실제 plan 수집이 쿼리를 실행하는 옵션이라면 운영 변경문에 무심코 적용하지 말고 승인된 사본·읽기 범위에서 사용합니다.
진단 표에서 최초 불일치 연산을 찾는 이유는 뒤의 큰 읽기나 spill이 원인이 아니라 결과일 수 있기 때문입니다. 예를 들어 join 입력에서 추정 1행·실제 100,000행이 처음 나타난 뒤 nested-loop lookup이 반복되면, lookup 자체만 덮기보다 상관·parameter·통계의 선택도부터 확인합니다.
단일 컬럼 통계와 다중 컬럼 상관
100만 행 중 도시 A가 10%, 우편번호 Z가 1%이고 Z가 모두 A에 속한다면, 도시 조건을 통과한 10만 행 안에 Z가 1만 행 있으므로 실제 결과는 1만 행입니다. 두 조건을 서로 독립이라고 보면 0.1×0.01×1,000,000=1,000행으로 잘못 예상하므로 join·lookup·메모리 선택도 어긋날 수 있습니다. 다중 컬럼·확장 통계는 열 사이의 dependency(한 열 값이 다른 열과 함께 나타나는 관계), distinct(서로 다른 값의 수), 빈도 정보를 저장해 추정을 보완할 수 있지만, 지원 범위와 join·parameter 추론은 엔진마다 다릅니다. 새 인덱스와 통계 보강은 서로 다른 처방이고 둘 다 분석·유지 비용이 있으므로, 통계를 무조건 늘리기보다 실제 추정 오차의 원인을 먼저 나눠야 합니다. 여기의 독립 선택도 곱은 설명을 위한 단순 모델입니다. 모든 옵티마이저가 항상 이 계산만 사용하는 것은 아닙니다.
그림을 글로 읽기
- 데이터·parameter·통계 추정
- join·lookup·정렬 계획
- 메모리·반복 읽기 요구
- 실제 CPU·I/O·대기·p99
- 데이터·parameter·통계 추정 → join·lookup·정렬 계획: 카디널리티 입력
- join·lookup·정렬 계획 → 메모리·반복 읽기 요구: 연산별 비용
- 메모리·반복 읽기 요구 → 실제 CPU·I/O·대기·p99: 동시 부하와 결합
연습으로 도시 A 100,000행 중 우편번호 Z가 10,000행인 데이터를 만들고 독립 가정의 1,000행과 실제 10,000행을 비교합니다. 예측 결과는 두 조건의 상관을 모르는 추정이 실제보다 작아지는 것입니다. 다만 이 산술은 엔진의 정확한 추정 알고리즘을 재현하는 테스트가 아니라, 상관을 조사해야 할 이유를 고정하는 설명 모델입니다.
메모리 부족과 과다 grant의 증상 차이
sort·hash가 처리할 실제 행이 메모리에 들어가지 않으면 spill이 되어 임시 디스크로 일부를 넘길 수 있고, 반대로 추정 행 수가 너무 크면 큰 메모리 grant를 예약해 다른 쿼리가 기다릴 수 있습니다. 그래서 요청한 grant·실제 사용량·spill·대기·실제 행 수를 함께 봐야 어느 쪽 문제인지 구분할 수 있습니다.
SQL Server의 query memory grant·feedback, PostgreSQL의 work_mem과 연산자·parallel worker별 사용, MySQL의 버퍼·임시 테이블은 서로 다른 계약이므로 설정 하나를 “쿼리당 절대 메모리”로 읽지 않으며, 연산자 수·병렬도·동시 쿼리가 겹치면 총 사용량이 커질 수 있습니다.
메모리를 늘리기 전에는 불필요한 행·넓은 컬럼·잘못된 join·통계·인덱스 순서를 점검하고, 단일 질의 개선이 전체 workload의 처리량·OOM·p99를 악화시키지 않는지 확인합니다.
spill이 보이면 요청 grant가 작았다는 한 가지 결론으로 끝내지 말고 실제 행 폭·동시 실행 수·연산별 사용량을 함께 봅니다. 반대로 grant가 큰데 실제 사용량이 작고 다른 쿼리가 대기하면 과다 예약 가능성이 큽니다. 완화 후에는 한 쿼리의 elapsed time뿐 아니라 동시 workload의 처리량과 p99를 다시 확인합니다.
Plan 강제·재컴파일 완화책의 유효 기간
좋았던 plan 고정·힌트·재컴파일·통계 갱신은 상황에 따라 도움되지만 다른 parameter나 미래 분포에서 나빠질 수 있습니다. SQL Server Query Store·PostgreSQL 계획 관측·MySQL optimizer 도구의 지원 범위를 구분합니다. 적용 대상·기대 효과·회귀 경보·해제 기준을 기록합니다.
통계 갱신 직후에도 최적 계획이 자동 보장되지는 않습니다. 작은 고객·큰 고객·희귀 조합·없는 조합·cold/warm·동시 쓰기·복제/백업 부하를 비교합니다. 평균 한 값보다 입력 분포별 tail과 자원 사용을 봅니다.
재현에 필요한 이력과 제한적 보관
query ID·plan ID·시각·엔진 버전·통계 변경·parameter의 안전한 크기/분포 특징·실제 행·읽기·대기를 연결합니다. 모든 실행의 전체 plan과 민감 parameter를 무제한 저장하지 않습니다. 느린 사례·계획 변경·오류·대표 입력을 선별하고 드문 큰 tenant가 표본에서 사라지지 않게 별도 범주를 둡니다.
수집 오버헤드·보관 기간·접근권한·원문 마스킹을 정합니다. 과거 parameter를 저장했다면 테스트 데이터로 안전하게 재현하고 타인의 실제 자료를 외부에 보내지 않습니다. 현재 작업에서는 제품별 plan·grant·JFR 같은 실행 관측을 수행하지 않았으며 본문은 진단 절차와 산술 예제입니다.
보관 샘플에는 정상 대표 입력도 포함해야 회귀의 기준선이 생깁니다. 느린 사례만 저장하면 정상에서 비정상으로 얼마나 이동했는지 비교할 수 없고, 민감 parameter 원문을 저장하면 진단 이력 자체가 새로운 노출면이 됩니다. 이력에서 재현 가능한 것은 분포 범주·크기·경계값으로 충분한지 먼저 줄여 봅니다.