Showing Posts From

Mysql

MySQL 슬로우 쿼리 로그와의 밤샘 전쟁

MySQL 슬로우 쿼리 로그와의 밤샘 전쟁

MySQL 슬로우 쿼리 로그와의 밤샘 전쟁 새벽 3시의 슬랙 알림 진동이 울렸다. 아내가 뒤척였다. 폰을 집어들었다. 슬랙이다. 프로덕션 알림 채널. [CRITICAL] Slow Query Detected Response Time: 8.3s Endpoint: /api/users/activity8초. 망했다. 잠옷 바람으로 거실로 나왔다. 노트북을 켰다. 부팅되는 동안 물을 마셨다. 손이 떨렸다. 커피 때문인지 떨림 때문인지 모르겠다. VPN 연결. 프로덕션 DB 접속. 슬로우 쿼리 로그를 열었다. SELECT u.*, ua.last_active, ua.page_views FROM users u LEFT JOIN user_activities ua ON u.id = ua.user_id WHERE u.created_at >= '2024-01-01' AND u.status = 'active' ORDER BY ua.last_active DESC LIMIT 100;평범해 보였다. 그게 더 문제다.EXPLAIN은 거짓말을 하지 않는다 EXPLAIN을 돌렸다. +----+-------------+-------+------+---------------+------+---------+------+--------+----------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+----------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 450000 | Using... | | 1 | SIMPLE | ua | ALL | NULL | NULL | NULL | NULL | 890000 | Using... | +----+-------------+-------+------+---------------+------+---------+------+--------+----------+type이 ALL. 둘 다. 45만 로우와 89만 로우를 풀스캔. 카르테시안 곱. 400억 건을 뒤진다는 소리다. "씨발." 소리가 나왔다. 작게. user_activities 테이블을 확인했다. 인덱스가 하나도 없다. user_id에도. last_active에도. 누가 만든 건지 git blame을 돌렸다. 6개월 전. 커밋 메시지 "Add user activity tracking". 커밋한 사람은 나. "아 씨." 이번엔 크게 나왔다. 6개월 전엔 데이터가 얼마 없었다. 몇천 건. 그때는 문제없었다. 근데 지금은 89만 건. 매일 3천 건씩 쌓인다. 프로덕션에서 터지기 전까진 아무도 몰랐다. 스테이징에 데이터가 없으니까.인덱스를 추가할 수 없는 이유들 인덱스 추가하면 된다. 간단하다. 근데 프로덕션에서 ALTER TABLE은 간단하지 않다. 89만 건 테이블에 인덱스 추가하면 락이 걸린다. InnoDB라도 시간이 걸린다. 10분? 20분? 그 동안 서비스는 멈춘다. 새벽 3시에 배포는 못 한다. 승인이 필요하다. CTO 결재. 근데 CTO는 자고 있다. 당연히. 옵션을 생각했다.아침까지 기다린다 - 유저들은 8초 쿼리를 계속 겪는다 지금 긴급 배포 - CTO 깨워서 승인받기 임시방편으로 쿼리 수정 - 근본 해결 아님2번은 무섭다. 3번은 찝찝하다. 1번은 책임이 무섭다. 슬랙을 켰다. CTO에게 DM. "프로덕션 슬로우 쿼리 발생했습니다. 인덱스 추가 필요합니다. 긴급 배포 승인 부탁드립니다." 읽음 표시가 안 뜬다. 10분을 기다렸다. 20분을 기다렸다. 답이 없다.임시방편이라는 이름의 선택 기다릴 수 없었다. 쿼리를 수정하기로 했다. 인덱스 없이 빠르게 만드는 방법. -- 기존 WHERE u.created_at >= '2024-01-01'-- 수정 WHERE u.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)범위를 줄였다. 1년치가 아니라 90일치만. -- ORDER BY 제거 -- LIMIT 100만 남김정렬을 포기했다. 어차피 프론트에서 다시 정렬한다. 코드를 수정했다. 커밋. 푸시. 젠킨스에서 빌드. 배포는 못 한다. 승인이 없으니까. 근데 DB 쿼리는 바로 테스트할 수 있다. 프로덕션 DB에 직접. EXPLAIN SELECT u.*, ua.last_active FROM users u LEFT JOIN user_activities ua ON u.id = ua.user_id WHERE u.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY) AND u.status = 'active' LIMIT 100;0.3초. 됐다. 근데 이건 임시방편이다. 근본 해결이 아니다. 데이터는 계속 쌓인다. 90일 범위도 언젠가 느려진다. 새벽 4시. CTO한테 전화했다. "여보세요?" 목소리가 잠에 절어있다. "죄송합니다. 프로덕션 이슈입니다." 상황을 설명했다. 쿼리 수정으로 임시 해결했다고. 인덱스 추가가 필요하다고. "아침에 회의하자. 6시까지는 자야겠다." 끊겼다. 나는 못 잔다. 아침 9시의 포스트모텀 출근했다. 4시간 잤다. CTO실에 들어갔다. 기획팀장도 있다. 왜 기획팀장까지. "상황 설명해봐." 화면을 공유했다. EXPLAIN 결과. 슬로우 쿼리 로그. 응답시간 그래프. "인덱스가 없었어요. user_activities 테이블에." "왜 없었는데?" "제가... 6개월 전에 만들 때 안 넣었습니다." "왜?" "데이터가 적어서 문제 될 줄 몰랐습니다." 침묵. 기획팀장이 물었다. "유저들 피해는?" "어젯밤 3시부터 8시까지. 약 5시간. 해당 API를 쓰는 유저들이 8초씩 기다렸습니다." "몇 명?" "로그 확인 결과 430명 정도." "우리 서비스 쓸 때 8초씩 기다렸다는 거네?" "...네." 또 침묵. CTO가 말했다. "인덱스 추가 언제 할 건데?" "오늘 점심시간에 하겠습니다. 유저 적을 때." "락 걸리는 시간은?" "15분 정도 예상합니다." "공지는?" "11시 50분에 올리겠습니다." "좋아. 근데 다음부터는 테이블 설계할 때 인덱스 미리 생각해." "네." 회의가 끝났다. 자리로 돌아왔다. 후배가 물었다. "형 괜찮아요?" "응." 괜찮을 리 없다. 점심시간의 배포 11시 50분. 공지를 올렸다. "점심시간 동안 DB 작업으로 서비스 일시 중단됩니다. 12:00 ~ 12:20 예정" 슬랙에 난리가 났다. "갑자기요?" "고객사 미팅 있는데" "점심시간이라 괜찮을 거예요" "아 진짜" 무시했다. 할 건 해야 한다. 12시 정각. 프로덕션 DB 접속. ALTER TABLE user_activities ADD INDEX idx_user_id (user_id), ADD INDEX idx_last_active (last_active);엔터를 눌렀다. 쿼리가 실행됐다. 진행률은 안 보인다. 그냥 기다려야 한다. 1분. 2분. 5분. 슬랙 알림이 울렸다. "아직 안 돼요?" "언제 돼요?" 10분. 12분. "Query OK, 890000 rows affected (13 min 47 sec)" 됐다. 서비스를 켰다. API 테스트. 응답시간 0.2초. 공지를 올렸다. "작업 완료. 서비스 정상화" 아무도 반응 안 했다. 다들 점심 먹으러 갔다. 나도 밥 먹으러 갔다. 김치찌개집. 사장님이 물었다. "오늘 안색이 안 좋네?" "어제 못 잤어요." "야근?" "비슷한 거요." 밥을 먹었다. 맛이 없었다. 배는 고픈데 넘어가지가 않았다. 그 후 일주일 인덱스 추가 후 일주일. 모니터링했다. 슬로우 쿼리 0건. 평균 응답시간 0.18초. 완벽하다. 근데 찝찝하다. user_activities 말고 다른 테이블은? 확인했다. orders 테이블 - 인덱스 3개뿐. user_id, created_at, status. payments 테이블 - 인덱스 2개. order_id, created_at. notifications 테이블 - 인덱스 1개. user_id만. 전부 시한폭탄이다. 데이터 쌓이면 터진다. 금요일 오후. 팀 회의에서 말했다. "모든 테이블 인덱스 점검이 필요합니다." 팀장이 물었다. "얼마나 걸려?" "일주일?" "다음주는 스프린트 마감인데." "그럼 그 다음 주에." "그때는 신규 기능 개발 들어가는데." 결국 없던 일이 됐다. 인덱스 점검은 우선순위에서 밀렸다. 또. 터지기 전까진 아무도 신경 안 쓴다. 나도 마찬가지다. 일이 많으니까. 다음번엔 어느 테이블이 터질까. orders? payments? 모르겠다. 터지면 그때 고치지 뭐. 새벽 3시에 또 깰 것 같다는 생각을 했다. 배운 것과 못 배우는 것 이번 일로 배운 거.테이블 만들 때 인덱스부터 생각하기 데이터 적을 때 빠르다고 안심하지 말기 스테이징에도 프로덕션급 데이터 넣어두기 슬로우 쿼리 알림은 진동 말고 무음으로근데 못 배우는 것도 있다. 기술 부채는 계속 쌓인다. 치울 시간은 없다. 우선순위는 항상 밀린다. 터지기 전까진 아무도 신경 안 쓴다. 개발자도. 기획자도. CTO도. 터지면 그제야 급하게 고친다. 새벽에. 혼자서. 그러고 일주일 지나면 다들 잊는다. 나만 기억한다. 다음번엔 어디가 터질지. 이게 7년차 개발자의 일상이다. 화려하지 않다. 그냥 버티는 거다. 터지면 고치고. 또 터지면 또 고치고. 가끔 생각한다. 이직해야 하나. 근데 다른 회사도 똑같을 거다. 기술 부채는 어디에나 있다. 레거시 코드도. 밤샘 배포도. 그냥 이렇게 사는 거다. 커피 한 잔 더 마셨다. 네 번째다. 오늘도 야근이다.다음번 슬로우 쿼리는 언제 터질까. 폰은 무음으로 해뒀다.

MySQL 인덱스 설계: 경험치로는 모자란 부분

MySQL 인덱스 설계: 경험치로는 모자란 부분

MySQL 인덱스 설계: 경험치로는 모자란 부분 느려진다는 신호, 그 다음부터가 지옥문 "김개발님, 대시보드 조회가 자꾸 타임아웃이 나네요. 인덱스를 좀 봐주실 수 있을까요?" 팀원 신입이 슬랙에 메시지를 보냈고, 나는 "아 그거요, 금방 봐드리죠"라고 답했다. 자신감 가득한 대답이었다. 그런데 이게 내 인생의 3시간을 빼앗을 줄은 몰랐다. 일반적으로 백엔드 개발자에게 "쿼리가 느리다"는 보고는 고혈압 약을 먹고도 혈압을 올릴 수 있는 마법의 말이다. 왜냐하면 가능한 원인이 너무 많기 때문이다. N+1 쿼리? 인덱스 미스? 테이블 잠금? MySQL 캐시? 네트워크 레이턴시? 아니면 서버 리소스 부족? 경력 7년차라는 타이틀이 나를 자신감 넘치게 만든다. 7년이면 충분하지 않을까? 인덱스 정도야 내가 다 알지. 나는 빠르게 쿼리 플랜을 뽑아내기로 했다. EXPLAIN EXTENDED SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' AND status IN ('active', 'pending') ORDER BY created_at DESC LIMIT 100;플랜을 봤다. rows: 2,847,392. 풀 테이블 스캔이다. 뭐가 이렇게 많이 나오는 거야? 나는 즉각 인덱스를 설계했다. "네, 여기요. (user_id, created_at, status) 복합 인덱스를 추가하면 될 것 같습니다. 아 그거요, status도 같이 포함해야 필터링이 먹으니까요." 신입이 감탄했다. "오, 역시 시니어는 다르네요!" 그 칭찬이 나를 망쳤다. 나는 인덱스를 추가했고, 자리로 돌아가 커피를 마셨다. 그리고 30분 뒤... "어... 김개발님? 아직 느린데요?"'EXPLAIN' 다시 읽기, 내가 놓친 것들 다시 쿼리 플랜을 봤다. 이번엔 좀 더 찬찬히. 아니, 이전엔 정말 제대로 본 건가? mysql> EXPLAIN EXTENDED SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' AND status IN ('active', 'pending') ORDER BY created_at DESC LIMIT 100\G결과를 보니:Type: ALL - 풀 테이블 스캔 Key: NULL - 인덱스를 사용하지 않음 Key_len: NULL - 인덱스 길이도 없음 Rows: 2,847,392 - 약 284만 건을 스캔 Using where; Using filesort - 디스크 정렬 사용어? 근데 내가 인덱스 추가했는데? 나는 실시간으로 테이블을 다시 확인했다. SHOW INDEXES FROM user_logs;인덱스가 추가되어 있었다. 그럼 왜 안 쓰는 건데? 그 순간이었다. 뇌가 깨어났다. 아, 쿼리 자체가 이상한데? 다시 원래 쿼리를 봤다. SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' AND status IN ('active', 'pending') ORDER BY created_at DESC LIMIT 100;뭔가 이상하다. WHERE절에는 user_id가 먼저 오고, 그 다음에 created_at과 status가 있다. 그런데 내가 만든 인덱스는... (user_id, created_at, status)? 아 그거요. 잠깐. 인덱스가 있어도 MySQL 옵티마이저가 판단해서 안 쓸 수도 있다. 특히 카디널리티(Cardinality) 문제가 있으면 말이다. ANALYZE TABLE user_logs; SHOW INDEXES FROM user_logs\G카디널리티를 확인했다. user_id는 5개. status는 3개. 근데 전체 행은 284만 건. 오... 아. 그 순간 깨달음이 왔다. Status 컬럼의 카디널리티가 너무 낮다는 것이다. 'active'와 'pending'만 있으면, 결국 거의 모든 행이 조건을 만족한다는 뜻이다. 그럼 MySQL은 "인덱스를 타서 284만 건을 다 읽은 다음 정렬할까? 아니면 차라리 풀 스캔 한 번 하고 나중에 정렬할까?" 이렇게 판단하고, 풀 스캔이 더 빠르다고 생각한 것이다. 그런데 내가 뭘 했냐고? 인덱스를 추가하고 "야, 이제 빠르겠지?" 했다. 기초를 무시하고 경험만 믿었다. EXPLAIN EXTENDED SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' ORDER BY created_at DESC LIMIT 100;status 조건을 빼서 실행해봤다. Type: range Key: idx_user_created_status Key_len: 12 Rows: 1,247 Using index condition어? 이제 인덱스를 쓴다? 그렇다. 내가 놓친 문제는 인덱스가 없는 게 아니었다. 쿼리 자체가 문제였고, 옵티마이저의 선택이 합리적이었고, 내가 상황을 제대로 분석하지 않았다는 것이다.쿼리 다시 쓰기, 그리고 배운 것 결국 문제는 쿼리였다. 신입이 준 쿼리를 그대로 실행시키고 있었다. SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' AND status IN ('active', 'pending') ORDER BY created_at DESC LIMIT 100;나는 신입을 불렀다. "어, 이 쿼리를 왜 이렇게 짰어요? status를 꼭 WHERE절에 포함해야 하나요?" 신입 왈: "어... 기획팀에서 'active나 pending 상태인 로그만 보고 싶다'고 했는데..." "근데 실제로 active나 pending이 아닌 상태의 로그가 많나요?" 신입이 생각해봤다. "아, 거의 없네요. 아마 전체의 98% 정도가 active나 pending일 것 같은데요?" 그 순간이었다. 카디널리티가 높지 않은 컬럼은 WHERE절의 앞에 오면 안 된다. 특히 복합 인덱스에서는 더욱 그렇다. 인덱스 컬럼의 순서가 중요하다는 건 알았지만, 이렇게까지 직접 경험할 줄은... 나는 쿼리를 다시 설계했다. SELECT * FROM user_logs WHERE user_id = 123 AND created_at BETWEEN '2024-01-01' AND '2024-12-31' LIMIT 100;그리고 애플리케이션 단에서 status를 필터링했다. 실행 시간: 150ms → 8ms. 18배 빨라졌다. 인덱스를 하나 추가해서가 아니라, 쿼리를 바꿔서. 그 다음부터 나는 매번 "아 그거요"라고 하기 전에 EXPLAIN을 3번은 본다. 아 그거요. 근데 일단 쿼리 플랜부터 봐야 할 것 같은데요.이렇게 말한다. 조금 더 신중해진 나. 인덱스는 은탄환이 아니다 사실 이 경험이 나한테 준 가장 큰 교훈은 간단하다. 인덱스를 추가하는 것보다, 쿼리를 제대로 짜는 게 100배 중요하다. 7년차니까 알겠지 하면서 기초를 무시했다. 느린 쿼리가 나오면 인덱스를 먼저 생각한다. 하지만:EXPLAIN을 제일 먼저 봐야 한다. Type, Key, Key_len, Rows, Extra 모두. 카디널리티를 확인해야 한다. 특히 복합 인덱스를 만들 때. 인덱스 컬럼의 순서가 결정적이다. Equality, Range, Sort 순서로. LIMIT절이 있으면 MySQL은 풀 스캔도 고려한다. 특히 작은 LIMIT일 때. 데이터 분포를 알아야 한다. 같은 인덱스도 데이터가 어떻게 분포하는지에 따라 쓰일 수도, 안 쓰일 수도 있다.그날 이후로 나는 슬랙 메시지에 "쿼리가 느려요"라는 보고가 오면, 먼저 쿼리를 본다. 그 다음에 EXPLAIN을 본다. 그리고 나서야 인덱스를 생각한다. 신입이 또 다른 느린 쿼리 건을 줬을 때, 나는 EXPLAIN을 봤다. N+1이었다. 관계 데이터베이스인데 Loop Join으로 100만 번의 쿼리를 날리고 있었다. 인덱스와는 아무 상관이 없었다. "아 그거요. LEFT JOIN으로 한 번에 가져가시면 될 것 같습니다." 이번엔 자신감이 없었다. EXPLAIN을 5번 봤고, 쿼리를 다시 썼고, 인덱스 필요성을 판단했다. 그 결과 실행 시간이 47초에서 2초로 줄었다. 신입이 감탄했고, 나도 뿌듯했다. 하지만 그 뿌듯함은 다르다. 이번엔 "내가 똑똑해서"가 아니라 "내가 제대로 분석해서"다.당신이 다음부터 할 수 있는 것 이 글을 읽는 개발자에게 당부하고 싶다. 특히 "경력이 좀 쌓인" 개발자에게. EXPLAIN을 무시하지 말자. 쿼리가 느려졌다고 해서 인덱스를 먼저 생각하지 말자. 그리고 자신감으로 기초를 뛰어넘지 말자. 진짜로 해야 할 체크리스트:쿼리를 읽는다. JOIN 수, WHERE 조건, GROUP BY, ORDER BY. 이상한 부분이 있나? EXPLAIN을 본다. Type이 ref가 아닌가? Rows가 너무 많진 않은가? Using filesort가 있진 않은가? 실제 실행 시간을 본다. EXPLAIN은 추정값이다. 100% 정확하지 않다. 쿼리를 수정한다. WHERE절 순서, JOIN 방식, 서브쿼리 활용. 그래도 느리면 인덱스를 생각한다.내가 처음에 인덱스를 추가했을 때, 아무 효과가 없었던 이유는 인덱스가 문제가 아니었기 때문이다. 쿼리 자체가 비효율적이었다. 지금도 팀에서 느린 쿼리가 나오면 내가 본다. 그런데 거의 절반 이상이 인덱스 문제가 아니다. 대부분은:**SELECT *** 하는데 실제로 10개 컬럼만 필요한 경우 Left Join에서 WHERE절에 Right 테이블 조건이 있어서 LEFT가 INNER가 되는 경우 GROUP BY 후 HAVING으로 필터링 하는데, WHERE에서 미리 필터링할 수 있는 경우 Subquery로 일일이 조회하는 경우이런 것들은 인덱스로 안 된다. 쿼리를 다시 써야 한다. 나는 이제 느린 쿼리를 보면 한숨이 아니라 '어, 이게 뭔가 재미있는 문제네?' 이런 생각이 든다. 왜냐하면 대부분 쿼리 최적화로 해결할 수 있기 때문이다. 그리고 그게 훨씬 더 근본적인 솔루션이기 때문이다. 경력이 늘수록, 기초가 더 중요하다는 걸 깨닫는다. 그리고 "아 그거요"라는 대답이 가장 위험한 말이라는 것도.인덱스는 옷깃이 아니라, 기초 설계가 먼저인 법이다.