한국어 English 日本語简体中文繁體中文 Deutsch ไทย Tiếng Việt Русский Português (Brasil)Español Bahasa Indonesia

실행 계획 변경으로 인한 쿼리 지연 Query plan regression (stats, parameter sniffing)

원인 ID db-plan-flip · 주 담당 인프라팀·DB 인프라 · 함께 게임개발팀·서버 개발

코드는 그대로인데 DB가 같은 쿼리를 처리하는 방법(실행 계획)을 바꾸면, 어제 2ms였던 쿼리가 오늘 수백 ms가 됩니다.

왜 통계 자동 갱신, DB 재시작, 데이터 분포 변화로 DB가 실행 계획을 새로 세움 → 그러면 인덱스를 안 타는 계획이 골라져 같은 쿼리가 수십~수백 배 느려지고 커넥션이 묶임 → 화면에서는 배포도 없었는데 특정 기능 로딩이 갑자기 느려지고 다른 요청까지 대기

증상: 입력 지연, 접속 불가·무한 로딩
요인: 지연, 정체
누가 겪나: 특정 기능만, 서버 전체
언제: 가끔 무작위로
담당: 주 담당 인프라팀·DB 인프라 · 함께 게임개발팀·서버 개발
게임개발팀 할 일: 값에 따라 결과 수가 크게 다른 쿼리는 나눠 쓰거나 계획 힌트 검토, 인덱스를 확실히 타는 쿼리 설계.
인프라팀 할 일: 느린 쿼리와 실행 계획 기록 감시, 좋은 계획 고정(SQL Server 쿼리 저장소 등), 통계 갱신 시각 관리.
그래프에서는: 어느 순간부터 계단처럼 올라감 · 쿼리별 평균 실행 시간
확인할 곳: 같은 모양 쿼리의 평균 시간을 주기적으로 모아 추이를 봄. MySQL은 events_statements_summary_by_digest의 AVG_TIMER_WAIT, PostgreSQL은 pg_stat_statements의 mean_exec_time(12 이하는 mean_time)임. 느려진 전후의 실행 계획은 EXPLAIN이나 PostgreSQL auto_explain, SQL Server는 쿼리 저장소의 회귀된 쿼리(Regressed Queries) 화면으로 비교함
이러면 맞음: 배포가 없던 시각에 한 쿼리의 평균 시간이 계단처럼 몇십 배 오르고 그 시점이 통계 갱신·DB 재시작과 겹치며 실행 계획이 바뀌어 있음
이러면 아님: 실행 계획은 그대로인데 느려졌으면 데이터 증가, 잠금 대기(db-hot-row), 디스크 쪽
확인 수단: 인프라 도구로 확인(게임 코드 불필요)
더 알아보기: SQL Server는 처음 들어온 값에 맞춰 세운 계획을 다시 씁니다(파라미터 스니핑). 아이템이 몇 개뿐인 새 캐릭터로 세운 계획이 아이템 수만 개인 오래된 캐릭터에게 쓰이면 크게 느려지고, 반대 경우도 흔합니다. 재시작으로 계획이 지워지면 멀쩡해졌다가 다시 나빠지기도 합니다.

출처

Query Processing Architecture Guide Microsoft SQL Server
파라미터 스니핑: 컴파일·재컴파일 때 들어온 파라미터 값에 맞춰 실행 계획을 세움
Parameter Sensitive Plan Optimization Microsoft SQL Server
데이터 분포가 고르지 않으면 캐시된 계획 하나가 모든 파라미터 값에 맞지 않음
Monitor performance by using the Query Store Microsoft SQL Server
통계·스키마·인덱스 변화로 계획이 바뀌고 계획 캐시는 최신 계획만 둠, 쿼리 저장소의 계획 강제로 좋은 계획 고정, 회귀된 쿼리(Regressed Queries) 화면으로 느려진 쿼리와 계획 비교
Statement Summary Tables MySQL
events_statements_summary_by_digest: 같은 모양 쿼리별 COUNT_STAR·AVG_TIMER_WAIT(평균 시간)
pg_stat_statements — track statistics of SQL planning and execution PostgreSQL
문장별 calls, total_exec_time, mean_exec_time(평균 실행 시간)
pg_stat_statements (PostgreSQL 12 Documentation) PostgreSQL
12까지는 열 이름이 total_time·mean_time
auto_explain — log execution plans of slow queries PostgreSQL
auto_explain.log_min_duration보다 오래 걸린 쿼리의 실행 계획을 로그에 남김

실행 계획 변경으로 인한 쿼리 지연 Query plan regression (stats, parameter sniffing)

출처

함께 보면 좋은 원인

같은 층: L12 데이터베이스

같은 증상(입력 지연)의 다른 층 원인