요즘 하루에 한번씩 slow query log 훑어보는게 루틴이 됐다. 오늘 정리 한번.
my.cnf 설정:
slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = 0
long_query_time 1초로 두면 어플 레벨에서 실질적으로 이상한 쿼리 거의 다 잡힘. log_queries_not_using_indexes는 켜면 너무 많아서 off. 필요할때만 SET GLOBAL로 켜서 확인.
워크플로우:
1. 매일 아침 pt-query-digest 돌림. percona toolkit 짱.
pt-query-digest /var/log/mysql/slow.log > /tmp/digest.txt결과 상위 20개 쿼리만 보면 대부분 문제 있는 쿼리 걸림.
2. 각 쿼리에 대해 EXPLAIN 돌려봄.
- type이 ALL이면 풀스캔. 인덱스 추가 검토
- Using filesort 뜨면 order by가 인덱스 못 탐
- Using temporary + Using filesort 같이 뜨면 거의 무조건 튜닝 필요
- rows가 수십만이면 조건 선택성이 낮은거
3. 인덱스 추가할지 쿼리 리팩토링할지 판단. 보통은 composite index 추가가 답. 근데 인덱스 남발하면 write cost 올라가니까 용량과 쓰기 빈도 고려.
4. 바로 프로덕션에 인덱스 안 걸고, 스테이징에서 ALTER TABLE ... ADD INDEX 시간 재보고 반영. 5천만 행 테이블은 인덱스 추가도 몇 분 걸림. 5.5에서 alter중 락 걸리는거 때문에 조심. 5.6부터 online ddl이 좀 나아짐.
최근 잡은 쿼리 중 가장 웃겼던건 WHERE DATE(created_at) = '2014-11-15'. 이건 인덱스 못 탐. created_at BETWEEN '2014-11-15 00:00:00' AND '2014-11-15 23:59:59' 로 바꾸면 됨. 이런게 아직도 많이 나옴...
