인덱스를 넣었더니 다른 데가 느려졌다

목록 조회가 느려서 인덱스를 넣었다. 조회는 빨라졌다. 며칠 뒤 수집 배치가 느려졌다는 얘기가 나왔다.

Open Table of contents

증상 — 쓰기가 느려졌다

인덱스를 넣은 표가 수집 자료가 쌓이는 표였다.

ALTER TABLE crawl_data ADD INDEX idx_status_date (status, reg_date);

crawl_data 에 하루 20만 건이 들어간다. 보조 인덱스는 INSERT 마다 함께 갱신되므로 넣을 때마다 그만큼 더 쓴다.

배치 시간을 재 봤다.

인덱스 전  12분
인덱스 후  19분

7분 늘었다. 한 건씩 보면 미미한 차이지만 수십만 건이면 눈에 띄게 쌓인다.

조회를 빠르게 하는 것이 쓰기를 느리게 한다. 조회 쪽만 재고 판단한 것이 문제였다.

양쪽을 다 쟀다.

조회 (하루 200회)   4.2초 → 0.3초   절약 13분
삽입 (하루 20만건)  12분 → 19분     손해 7분

걸린 시간에 횟수를 곱해 비교하니 판단할 근거가 생겼다. 전체로는 이득이었지만 삽입이 몰리는 시간대에 문제가 됐다.

한쪽만 재면 전체로는 손해인 변경도 이득으로 보인다.

기존 인덱스를 봤다.

SHOW INDEX FROM crawl_data;

PRIMARY
idx_status
idx_reg_date
idx_status_date   ← 새로 넣은 것
idx_source

복합 인덱스는 왼쪽부터 이어지는 앞부분만 쓰는 조회도 처리한다. idx_status 와 idx_reg_date 가 idx_status_date 에 포함되는 셈이다.

ALTER TABLE crawl_data DROP INDEX idx_status;
ALTER TABLE crawl_data DROP INDEX idx_reg_date;

쓰는 쿼리를 확인하고 두 개를 지우니 삽입이 19분에서 14분이 됐다.

idx_source 도 쓰이는지 확인했다.

EXPLAIN SELECT * FROM crawl_data WHERE source = 'A';

쓰이기는 했다. 다만 그 쿼리가 하루에 몇 번 도는지 로그를 뒤져 보니 관리자 화면에서 2~3회였다.

2~3회를 위해 20만 건의 삽입을 느리게 하고 있었다. 지우고 그 조회는 느려도 감수하기로 했는데 몇 초 걸려도 하루 세 번이면 괜찮다.

넣기 전에 확인할 것도 정했다.

1. 이 표에 쓰기가 얼마나 자주 일어나나
2. 이 인덱스를 쓰는 조회가 얼마나 자주 도나
3. 기존 인덱스로 대신할 수 있나
4. 기존 인덱스 중 이것에 포함되는 것이 있나
5. 넣기 전후로 조회와 쓰기를 둘 다 잰다

3번과 4번이 자주 걸렸다. 이미 있는 것으로 되거나 새것이 옛것을 포함했다.

400만 건 표에 인덱스를 거는 데 6분이 걸렸고 그동안 그 표가 잠겼다.

이건 판에 달린 이야기다. InnoDB 의 Online DDL 이 들어온 뒤로는 보조 인덱스 추가가 ALGORITHM=INPLACE 와 LOCK=NONE 으로 돌아가고 그동안 읽기와 쓰기가 함께 된다. 우리 쪽 판은 그 전이라 잠겼다.

운영에 바로 넣지 않고 시험 서버에서 먼저 쟀다.

CREATE TABLE crawl_data_test LIKE crawl_data;
INSERT INTO crawl_data_test SELECT * FROM crawl_data LIMIT 500000;

50만 건으로 쟀다. 전체를 쓰면 오래 걸리고 너무 적으면 차이가 안 난다.

supervisorctl stop crawler
mysql -e "ALTER TABLE crawl_data ADD INDEX ..."
supervisorctl start crawler

몇 분이 걸리는지 알고 나니 언제 할지도 정해졌다. 새벽에 하면서 그동안 수집을 멈췄고 멈춘 동안의 자료는 크롤러가 재시도로 처리했다.

넣는 결정만 하고 넣는 과정을 안 보면 그 자리에서 사고가 난다.