같은 쿼리가 운영에서만 느렸다

목록 화면이 느리다는 이야기가 나와서 열어 보니 여덟 초쯤 걸렸다. 개발 서버에서 같은 화면을 열어 보니 바로 떴다.

Open Table of contents

증상 — 같은 코드에 다른 응답 시간

코드는 같고 MySQL 에 나가는 조회도 같았다. 다른 것은 그 안에 들어 있는 자료의 양뿐이었다.

같은 조회가 자료 양에 따라 이렇게 갈린다면 실행 방식 자체가 달라졌을 가능성이 크다. 그래서 느린 조회를 찾아 앞에 EXPLAIN 을 붙여 양쪽에서 떠 봤다.

화면 쪽 코드를 먼저 의심했지만 orders 를 읽는 자리가 한 군데였고 조건도 하나였다. 볼 것이 적어서 코드보다 아래를 봐야 한다는 것이 금방 갈렸다.

개발 서버는 이렇게 나왔다.

type: ref    key: idx_status    rows: 120

운영 서버는 이렇게 나왔다.

type: ALL    key: NULL          rows: 2840000

운영에서는 idx_status 를 쓰지 않고 표 전체를 훑고 있었다. 인덱스는 양쪽에 똑같이 걸려 있는데 운영에서만 안 쓴다.

SHOW INDEX 로 확인해도 이름과 구성이 같았다. 인덱스가 있느냐 없느냐가 아니라 쓰느냐 마느냐의 문제였다.

인덱스가 있다고 항상 쓰는 것이 아니고 옵티마이저가 그것을 쓰는 편이 나은지 판단해서 고른다.

문제의 조건은 이 한 줄이었다.

WHERE status = 1

status 에는 인덱스가 있다. 그런데 값의 분포가 양쪽에서 달랐다.

값	개발	운영
1 (정상)	100건	270만 건
2 (중지)	20건	8만 건

운영에서는 거의 전부가 status = 1 이다. 전체의 95%를 고르는 조건이면 인덱스로 하나씩 찾아가는 것보다 그냥 다 읽는 쪽이 빠르다.

옵티마이저의 판단이 맞았고 느린 것은 그 판단의 결과였다. 개발 서버는 자료가 적어서 이 차이가 아예 드러나지 않았다.

먼저 조건을 좁혔다. 화면은 어차피 최근 것만 보여주고 있었다.

WHERE status = 1
  AND created_at >= '2017-08-01'

created_at 에 인덱스를 만들었더니 그쪽을 타기 시작했고 여덟 초가 0.3초가 됐다.

다음으로 두 조건을 묶은 인덱스를 만들어 봤다. 하나씩 있는 것보다 묶은 쪽이 나을 때가 있다고 해서였다.

CREATE INDEX idx_status_created ON orders (status, created_at);

0.3초가 0.05초가 됐다. 순서를 바꿔 (created_at, status) 로도 만들어 봤는데 이쪽은 비슷했다.

목록에 붙은 전체 건수 표시도 같은 표를 다시 훑고 있었다. 정확한 수가 꼭 필요한지 물어보니 아니라고 해서 대략의 수로 바꿨다.

재면서 한 번 헷갈렸는데 같은 조회를 연달아 돌리면 두 번째가 훨씬 빠르다.

고쳤다고 좋아했는데 고치기 전 조회도 두 번째는 빨랐다. InnoDB 가 읽은 페이지를 버퍼 풀에 들고 있어서 디스크를 안 거치기 때문이다.

그래서 잴 때는 조건 값을 바꿔 가며 여러 번 재고 처음 실행한 값을 봤다. 개발 서버에서 재는 것은 의미가 없었는데 자료가 다르면 EXPLAIN 부터 다르기 때문이다.

운영 자료를 복사한 서버를 따로 만들어 거기서 쟀다. 그 자리가 없으면 고친 결과를 믿을 근거가 없다.

인덱스를 몇 개까지 만들어도 되는지 모르겠다. 조회는 빨라지는데 쓰기가 느려진다고 하고 지금 orders 에 다섯 개가 걸려 있다.

묶은 인덱스의 순서를 미리 정하는 방법도 몰라서 이번에는 둘 다 만들어 재 보고 골랐다.

말이 나오지 않은 느린 조회를 어떻게 찾는지도 남았다. 느린 조회를 기록하는 설정이 있다고 하니 다음에는 그것부터 봐야겠다.