성능 == 쿼리 응답 시간
우리는 MySQL 관리자가 아니라 사용하는 입장이기 때문에 필수 요소만 집중!!
쿼리 응답 시간 (Query response time): 쿼리를 실행하는데 소요되는 시간
쿼리를 받았을 때부터 여러 단계와 블로킹 시간으로 구성되지만 완벽한 상세 명세는 없고 필요하지도 않음.
- 쿼리 응답 시간은 우리가 경험하는 유일한 메트릭이고, 가장 먼저 살펴봐야 한다
- 느린 쿼리를 분석하고 최적화하여 응답 시간을 단축해야지 하드웨어 탓 ㄴㄴ
- 최적화를 하려면 쿼리 분석을 해야 함
- 쿼리 실행 방식 이해 (쿼리 보고서, 메타데이터)
쿼리 보고
소스: 쿼리 메트릭은 2개의 소스에서 비롯됨(MySQL 버전에 따라 다름)
슬로 쿼리 로그(slow query log):
- 옛날에는 1초 이상 걸리는 것만 느리다고 기록했는데 요즘은 기본값이 0초라서 모든 쿼리를 기록함
- MySQL 8.0.14 이전은 슬로 쿼리 로그보단 성능 스키마를 쓰-자, Percona 서버나 MariaDB 서버는 슬로 쿼리 로그가 훌륭한 소스이다.
- 기본적으로 비활성화, 재부팅 없이 활성화 가능
성능 스키마(performance schema)
집계: 쿼리 메트릭값은 정규화된 SQL 문법을 기준으로 그룹화되고 집계
- SQL문을 정규화한 뒤 SHA-256으로 해싱한 것이 키
- 실제 SQL을 쿼리 샘플이라고 하는데 보안적인 측면 때문에 대부분 메트릭 도구는 다이제스트 텍스트와 해시만 보고함
- 쿼리 추상화: 고유하지는 않지만 간결하고 개발자들은 이것만봐도 전체 쿼리 예상 가능
// 샘플
SELECT col FROM tbl WHERE id = 1
// 다이제스트 쿼리
SELECT col FROM tbl WHERE id = ?
// 쿼리 추상화
SELECT tbl보고: 쿼리 보고서는 고급 프로파일과 쿼리에 특화된 보고서로 구성
쿼리 프로파일
- 쿼리들이 느린 순서대로 표시됨 (정렬 기준은 다양함)
- 쿼리 시간이 어떻게 집계되는지는 보통 알기 어려움
- 쿼리 총시간 = 쿼리 당 응답시간 호출 횟수 = 응답 시간(0.1초) 호출 횟수(1000번)
- 실행 시간 비율 = 쿼리 총시간 / 모든 쿼리 총시간
- 쿼리 부하 = 쿼리 총시간 / 단위 시간(초)
쿼리 보고서
- 한 쿼리에 대해 알아야할 모든 것을 보여줌
- 프로파일에서 느린 쿼리를 선택한 뒤 해당 쿼리의 보고서를 보는 순서로 최적화 시작
- 보고서는 메트릭 도구에 따라 다른데 기본적으로 소스에서 비롯된 통계값이 포함됨. 전체 보고서에는 쿼리 샘플, EXPLAIN 계획, 테이블 구조 등의 메타데이터 포함
- 사실 분석할 때는 쿼리 메트릭만 있으면 됨 (나머지는 전부 수동으로 수집 가능하니까)
쿼리 분석
숫자에 매몰되지 말고 쿼리 실행을 이해해라
매우 중요한 9가지 쿼리 메트릭
- 쿼리 시간(query time): 명령문 구문 분석, 테이블 열기, 파일 정렬, 잠금 등 해당 쿼리가 실행되며 걸리는 총 시간
- 잠금 시간(lock time): 쿼리 실행 중 잠금 시간. 잠금 시간이 쿼리 시간의 50퍼 이상이면 문제라고 할 수 있음
- 주의!! 성능 스키마의 잠금 시간에는 innoDB의 레코드락이 포함되지 않고 테이블, 메타데이터 락만 포함한다. 따라서 잠금 시간은 성능 스키마가 아니라 슬로 쿼리 로그 거만 쓰는 것이 좋겠다.
- 읽기는 단순 SELECT와 SELECT ~ FOR UPDATE (X락)가 있음. 다만 INSERT ~ (SELECT ~) 처럼 서브쿼리는 S락을 건다
- 단순 SELECT도 메타데이터락, 테이블락을 얻으므로 잠금 시간이 0은 아니지만 1ms 미만.
- 조회된 행(rows examined): 쿼리 조건에 일치하는 행을 찾으려고 접근한 행의 수. 카디널리티가 높을 수록 낭비하는 시간이 줄어든다(인덱스 잘 사용)
- 근데 옵티마이저가 비용 기반으로 최적화하기 때문에 인덱스가 있어도 풀 테이블 스캔을 하는 경우도 있다.
- 보낸 행(rows sent): 조회된 행에서 실제로 클라이언트에 보낸 행의 수
- 보낸 행만으로 보면 거의 문제가 되지 않는데 조회된 행과 같이 보면 유의미함
- 조회된 행 = 보낸 행이면 이상적. 그러나 이 행 개수가 테이블 전체 행 개수의 10% 이상이면 풀 테이블 스캔을 유발할 수도!
- 영향받은 행(rows affected): 업데이트된 행의 수
- 기대한 행에만 영향을 미치도록 주의하자
- 셀렉트 스캔(select scan): 첫 번째로 접근한 테이블에서 수행한 풀 테이블 스캔 횟수
- 이게 0이 아니라면 쿼리 최적화 무조건 해
- 셀렉트 풀 조인(select full join): 조인된 테이블을 대상으로 풀 테이블 스캔 횟수
- 이건 셀렉트 스캔보다 더 나쁨!! 0이 아니면 무조건 해
- full outer join을 뜻하는데 이전 테이블 행의 곱과 같기 때문에 매우 안좋음
- 디스크에 생성된 임시 테이블(created tmp disk tables): 디스크에 생성된 임시 테이블 수
- 사실 임시 테이블을 메모리에 만드는 것은 정상. 근데 그게 커지면 디스크에 쓰게 되는데 그럼 문제가 됨
- 그렇다고해서 임시 테이블 사이즈를 늘리는 것은 최후의 수단이고 항상 쿼리 최적화부터 진행해라
- 쿼리 카운트(query count): 쿼리 실행 횟수
- 낮고 느리면 이상하게 봐야함
메타데이터와 애플리케이션은 쿼리 메트릭보다 쿼리 분석에 더 많이 사용된다
메타데이터: EXPLAIN, 테이블 구조 등
애플리케이션: 이 쿼리를 왜 실행하는지 등
상댓값
각 쿼리 메트릭에서 객관적으로 긍정적인 값은 0이 유일
평균, 백분위수, 최대
평균은 지나치게 낙관적이다
백분위수는 상위 N%(특잇값)를 무시함으로써, 객관적으로 대표성을 띈다
최대는 매우 중요하다. 누군가는 최대 쿼리 응답 시간을 경험하고 애플리케이션을 떠났을 것이다.
평균, 백분위수, 최대가 나타내는 것과 나타내지 못하는 것을 알고 있으면 된다.
만약 쿼리가 일정하지 않고 불안정한 경우 분석이 복잡해지고, MySQL 외부가 원인일 가능성이 높다.
쿼리 응답 시간 개선
직접 쿼리 최적화: 쿼리와 인덱스를 변경하는 것.
MySQL 8.4 SELECT 최적화하기: https://dev.mysql.com/doc/refman/8.4/en/select-optimization.html
간접 쿼리 최적화: 접근 대상 데이터와 접근 패턴을 변경하는 것
테이블을 TRUNCATE 하면 빨라진다
쿼리는 언제 최적화해야 할까?
- 성능이 고객에게 영향을 미칠 때 (당연)
- 코드 변경 전후: 이상적인 엔지니어가 되려면 코드 변경 전후에 쿼리 메트릭을 검토하자
- 한 달에 한 번: 좋은 쿼리가 나쁜 쿼리가 되는 시점은 늘 존재한다
MySQL을 더 빠르게
무조건 직접 및 간접 쿼리 최적화를 해야 한다. 쉬운 길이 있을 것 같나?
渽晛