계획은 읽었다, 한 장씩 — 튜닝을 두 번 잘못 판정한 기록

끝났다고 쓴 글

「느린 쿼리의 두 얼굴」에서 나는 인계받아 맡은 사내 업무 시스템의 느린 목록 조회를 다뤘다. 인덱스를 추가했다가 되돌리고, 대신 쿼리를 임시 테이블로 분해해 옵티마이저 추정 비용을 약 35에서 9로 떨어뜨린 이야기다. 그 글을 나는 이렇게 닫았다.

판단의 근거는 둘 다 실행계획이었다. (…) 추측이 아니라 계획을 읽고 갈랐다.

계획은 실제로 읽었다. 다만 한 장씩 읽었다.

그 글을 발행한 뒤 같은 시스템에서 두 번째 어긋남이 있었고, 그 사실은 발행본에 반영되지 않았다. 두 번째 어긋남을 증명하는 계획 캡처는 발행 시점에 이미 내 손에 있었다. 열어보지 않았을 뿐이다.

이 글은 그 정정이다. 두 어긋남을 나란히 놓으면 둘이 같은 모양이라는 게 드러난다.

첫 번째 어긋남 — 검증 환경에서 돌려본 것은 조회 하나였다

인덱스를 넣기 전에 절차는 밟았다. 운영과 별개인 개발·테스트 DB에서 대상 조회를 돌려 빨라지는 것을 확인했고, 운영 반영은 업무가 끝나 사용자가 없는 시간에 했다. 검증 환경을 거쳤고 반영 시점도 골랐다.

그런데 운영에 넣자 같은 테이블을 쓰는 다른 조회들이 무너졌다. 지연은 애플리케이션 타임아웃을 넘겼고, 한 쿼리를 고치려다 여러 조회를 장애로 만들었다. 경위는 선행글에 썼다. 인덱스는 되돌렸다.

인덱스가 그 테이블을 공유하는 모든 쿼리에 대한 전역 변경이라는 결론도 그 글에 썼다. 여기서 새로 여는 것은 결론이 아니라 그 앞단이다. 검증 환경에서 나는 무엇을 확인했나.

대상 조회 하나였다. 그 테이블을 쓰는 다른 프로시저는 한 번도 돌려보지 않았다. 인덱스가 전역 변경이라는 걸 사후에 배웠다고 썼지만, 정확히 적자면 나는 그것을 반영 전에 확인할 자리를 갖고 있으면서 쓰지 않았다. 검증 환경은 있었다. 거기서 돌려본 쿼리가 하나였을 뿐이다.

조심의 형식은 지켰는데 표본이 하나였다. 이것이 첫 번째 어긋남의 정확한 모양이다.

두 번째 어긋남 — 도구에서 잰 것은 실행 하나였다

인덱스를 접고 쿼리 구조를 바꿨다. 조인이 폭발하기 전에 후보 키를 #cand로 끊고 그 작은 집합에만 나머지를 붙이는 2단 분해다.

측정은 공정하게 했다. 관리 도구에서 같은 파라미터 값을 원본과 분해판에 각각 넣고, 실행 시간 통계와 실행계획을 나란히 놓고 비교했다. 분해판은 수백 밀리초 안쪽이었고 화면에서도 곧바로 떴다. 나는 여기서 끝났다고 판정했다. 그리고 선행글에 이렇게 썼다.

무엇보다 이 변경은 프로시저 하나에 갇혀 있어서 다른 조회를 건드리지 않았다. 되돌리기도 쉬웠다.

갇혀 있던 건 맞다. 다만 그 안에서 흔들리고 있었다.

그런데 "아직 좀 느리다"는 얘기가 들어왔다. 애플리케이션 경로에서는 지연이 그대로 남아 있었던 것이다. 확인은 두 층으로 했다. 사용자 쪽 신고가 첫 신호였고, 확증은 캐시에 올라와 있는 계획의 실행 통계를 조회해 얻었다. 계획별 사용 내역을 훑자 느린 쪽 항목 하나가 계속 재사용되고 있는 것이 보였다. 내가 도구에서 잰 그 빠른 실행은 거기 없었다.

남아 있던 계획 캡처를 지금 다시 열어보니 대비가 기계적으로 확인된다. 같은 프로시저의 두 실행이다.

쿼리가 같고 계획이 같고 컴파일까지 같다. 양쪽 다 새로 만든 계획이 아니라 캐시에서 꺼내 쓴 것이다. 다른 것은 파라미터뿐인데 경과 시간이 13배 벌어졌다. 계획은 문제의 지점을 1행으로 추정했는데 실제로는 이천 행 가까이 흘렀고, 그만큼의 중간 결과를 감당하지 못해 tempdb로 넘쳤다.

원인은 여기까지 단정할 수 있다. 첫 실행 값에 맞춰 만들어진 계획이 캐시에 남아, 값 분포가 다른 요청도 같은 계획을 그대로 탔다.

내가 도구에서 잰 실행은 파라미터가 전부 맞아떨어진 쪽이었다. 원본과 분해판에 같은 값을 넣었으니 개선폭 측정으로는 공정했다. 다만 그 값 하나가 운영에서 도는 실행 전체의 대표값이 아니었다. 첫 번째 어긋남과 같은 모양이다. 그때는 쿼리 표본이 하나였고, 이번엔 값 표본이 하나였다.

세 번째 처방과 그 대가

풀이는 실행 시점마다 계획을 다시 만들게 하는 것이었다. 캐시에 굳은 계획을 재사용하지 않으니 값이 바뀌면 그 값에 맞는 계획이 선다.

이 처방에도 대가가 있다. 매 실행마다 컴파일 비용을 낸다. 앞의 두 처방이 각각 대가를 치른 뒤였으니 이번엔 대가부터 지켜보기로 했다.

반영 후에는 시간대를 지정해 CPU 사용량을 꾸준히 관찰했다. 피크 타임에도 미미한 것을 확인하고 나서 반영을 확정했다.

여기서 처음으로 표본을 늘렸다. 앞의 두 판정은 각각 한 번의 관찰이었는데, 이번엔 시간대를 나눠 여러 번 봤고 가장 불리한 구간을 일부러 포함했다.

결말은 정성으로만 쓸 수 있다. 그 뒤로 그 화면은 바로 열린다. 애플리케이션 기준 응답시간은 그때 재두지 않았다. 도구에서 잰 숫자는 남아 있는데 애플리케이션 경로에서 잰 숫자는 없다. 그 경로가 문제였다는 걸 겪고도 정작 거기에는 눈금을 붙이지 않은 셈이다.

세 번째 질문

선행글은 느린 쿼리 앞에서 던지는 두 질문으로 닫았다. 내가 인덱스를 막고 있나, 이 느림이 인덱스로 풀리는 종류인가. 둘 다 무엇이 느린가를 묻는다.

세 번째 질문은 방향이 다르다. 내가 잰 것이 몇 개인가.

두 번의 어긋남은 같은 자리에서 나왔다. 측정 자체는 옳았다. 그 인덱스는 정말 대상 조회를 빠르게 했고, 그 분해판은 정말 빨랐다. 틀린 것은 측정이 아니라 측정의 표본이었다. 하나를 재고 전체를 결론지었다.

지금은 인덱스를 반영하기 전에 그 테이블을 쓰는 전체 프로시저를 먼저 뽑는다. 각 조회 쿼리를 확인해 인덱스가 어디에 어떻게 닿는지 영향도부터 본다. 첫 번째 어긋남이 남긴 절차다.

값 표본 쪽은 아직 그만큼 정리되지 않았다. 두 번째 어긋남에서 얻은 것은 "도구에서 한 번 잰 것은 한 번 잰 것"이라는 사실이지, 그것을 매번 어떻게 확인하겠다는 절차가 아니다. 목록에 있는 것과 아직 없는 것을 갈라 적어 두는 게 지금의 정직한 상태다.

학습

• 검증 환경을 갖는 것과 검증하는 것은 다르다. 별개 DB가 있었고 사용자 없는 시간을 골랐는데도 뚫린 이유는 환경이 아니라 거기서 돌려본 쿼리가 하나였다는 데 있다.
• 같은 쿼리, 같은 계획, 같은 컴파일이어도 파라미터가 다르면 다른 시간이 나온다. 도구에서 한 번 재고 끝났다고 판정하면 그 판정은 그 한 번에 대해서만 참이다.
• 세 처방 다 대가가 있었는데, 그것을 안 시점이 매번 달랐다. 인덱스는 남의 쿼리가, 쿼리 재작성은 안 풀린 경로가, 실행마다 재계산은 CPU가 대가였다. 앞의 둘은 운영 사고와 사용자 신고가 알려줬고, 마지막 하나만 내가 관찰 구간을 정해 놓고 확인했다.
• "느리면 인덱스"는 절반만 맞다고 썼는데, 그 문장에 하나를 더한다. 빨라진 것을 확인했다고 끝난 것도 아니다. 무엇을 몇 개나 확인했는지 적어 두지 않으면 측정은 아직 끝나지 않았다.