1장. 쿼리 응답 시간

본 포스트는 사내 스터디를 진행하며 정리한 포스트입니다

MYSQL을 더 빠르게, 성능 최적화 선택과 집중

---

성능은 곧 쿼리 응답 시간입니다.

책의 목적

• 본 책의 목적은 MySQL 성능을 현저하게 개선하는 것이며, 다양한 측면에서 방안을 탐구
• MySQL을 관리하는 입장이 아니라 사용하는 입장에서 기술

 

쿼리 응답 시간이란?

• MySQL이 쿼리를 실행하는 데 소요되는 시간
• 쿼리를 받고나서 결과 세트를 클라이언트에 전송한 시점까지의 경과 시간을 의미

 

쿼리 응답 시간 분석을 위한 지표

• 핵심지표로써 쿼리 응답 시간을 집중해야한다
• 이를 분석하기 위해 쿼리 보고를 이해하는 것이 중요하다
• 소스
  • 쿼리 메트릭은 2개의 소스에서 비롯되며 MySQL 배포 및 버전에 따라 다릅니다
  • 쿼리 메트릭을 제공하는 것으로 슬로 쿼리 로그, 성능 스키마가 존재
  • 슬로 쿼리 로그: 디스크에 있는 로그 파일
  • 성능 스키마: performance_schema와 같은 이름의 데이터베이스
  • 두개의 차이점은 얼마나 많은 메트릭을 제공하는지이다
  • 특히나 성능 스키마는 MySQL을 심층 분석할 수 있는 풍부한 여타 데이터를 포함하여 유용성이 높다
  • 슬로 쿼리 로그도 다양한 메트릭을 지원하지만, MySQL 80.14 이전 버전의 슬로 쿼리 로그는 분석의 질이 낮다
  • 슬로 쿼리 로그는 기본으로 비활성화되어 있지만 MySQL을 다시 시작할 필요 없이 바로 활성화 가능하다
  • 슬로 쿼리 로그 long_query_time 설정을 0이나 너무 낮게설정하여 많은 쿼리가 기록되게 한다면 디스크 입출력시간이 증가하므로 유의해야한다
  • 성능 스키마는 활성화하려면 MySQL을 다시 시작해야한다
• 집계
  • 쿼리 메트릭값은 정규화된 SQL 문법을 기준으로 그룹화되고 집계됩니다
  • 정규화된 SQL문을 SHA-256 hash로 변환하여 그룹화하는 방식을 사용
  • SHA-256 hash가 생성되는 과정
    • SQL문을 정규화하여 다이제스트 텍스트를 생성
    • 이를 SHA-256 hash 계산을 통해 다이제스트 해시를 생성
  • 쿼리 정규화시에는, 즉 다이제스트 텍스트를 생성할 시에는 값이 제거된다 ( where절로 들어가는 값이 달라도 같은 다이제스트 텍스트로 생성된다 )
• 보고
  • 쿼리 보고서는 고급 프로파일(쿼리 프로파일)과 쿼리에 특화된 보고서로 구성됩니다
  • 쿼리 프로파일
    • 느린 쿼리가 표시
    • 느리다는 정의는 쿼리가 정렬되는 쿼리 메트릭의 집계치인 정렬 메트릭과 관련있다
    • 일반적으로 쿼리 시간이 기본 정렬 메트릭
    • 쿼리 시간 집계치 종류
      • 쿼리 총시간
        • 실행 시간의 총합
        • 쿼리 A가 응답시간 1초, 10번 실행 = 총시간 10초
        • 쿼리 B가 응답시간 0.1초, 1000번 실행 = 총시간 100초
        • 이 경우 쿼리 B가 가장 느린 쿼리로 집계
        • 쿼리 B를 개선해야 더 많은 효과를 볼 수 있다
      • 실행 시간 비율
        • 쿼리 총 시간(각 쿼리)을 실행 총시간(모든 쿼리)으로 나눈 값
        • 얼마나 많은 비율로 MySQL을 실행시켯는지
        • 실행 시간 비율이 높은것이 가장 느린 쿼리
      • 쿼리 부하
        • 쿼리 총시간을 클럭 타임으로 나눈 것
        • 클럭 시간이 300초, 쿼리 총 시간 250초라면 250/300 = 0.83
        • 해당 지표는 쿼리 동시성을 나타낸다
        • 쿼리 부하가 3.5라는 것은 언제든지 조회하면 실행 중인 쿼리의 인스턴스가 3.5개정도 있다라는 뜻
        • 쿼리 부하가 높을수록 쿼리가 동일하거나 가까운 행에 접근할 때 경합할 가능성이 커집니다
  • 쿼리 보고서
    • 하나의 쿼리에 대해 알아야 할 모든 것을 보여준다
    • 일반적으로 쿼리 프로파일에서 느린 쿼리를 선택하는 것으로 시작된다
    • 쿼리 보고서는 쿼리 메트릭 도구에 따라 크게 달라진다
    • 쿼리 분석에는 보고서의 쿼리 메트릭만 있으면 된다. 메타데이터는 수동으로 수집할 수 있다

 

쿼리 분석

• 쿼리 실행을 이해해보자
• 쿼리 메트릭은 소스와 MySQL 배포 및 버전에 따라 다르다
• 쿼리 시간
  • 가장 중요한 메트릭이다
  • 쿼리 시간에는 또 다른 메트릭인 잠금 시간이 포함된다
  • 성능 스크마로 수집되는 이벤트는 다음과 같은 계층 구조로 구성된다
    • 트랜잭션: 모든 쿼리가 트랜잭션 안에서 수행되므로 최상위 이벤트
    • 명령문: 쿼리 메트릭이 적용되는 쿼리
    • 단계: 명령문 실행과정 내의 단계로, 명령문 구문 분석, 테이블 열기, 파일 정렬 수행과 같은 과정이 포함
    • 대기: 시간이 걸리는 이벤트
• 잠금 시간
  • 잠금 시간이 쿼리 시간의 50% 이상이면 문제로 볼 수 있다
  • 배경 지식 타임
    • InnoDB 스토리지 엔진에서 잠금에는 테이블 락과 로우 락이 존재
    • 서버(MySQL)은 테이블과 테이블 락을 관리, 스토리지 엔진에 구애받지 않는다
    • 로우 레벨 락은 잠금이 지원되는 스토리지 엔진만 가능
    • InnoDB는 로우 레벨 락을 지원, 즉 기본 스토리지 엔진인 InnoDB 락은 기본적으로 로우 레벨 락을 의미
    • 서버에서 관리하는 메타데이터 락도 존재, 스키마, 테이블, 저장 프로그램등의 접근을 제어
    • 테이블 락과 로우 락은 테이블 데이터에 대한 접근을 제어, 메타데이터 락은 테이블 구조(열, 인덱스 등)에 대한 접근을 제어하여 쿼리가 테이블에 접근하는 동안 변경되는 것을 방지
    • 모든 쿼리는 접근하는 모든 테이블을 대상으로 메타데이터 락을 획득, 트랜잭션이 끝날 때 해제
  • 성능 스키마의 잠금 시간에는 로우 락 대기가 포함되지 않고 테이블과 메타데이터 락 대기만 포함합니다
  • 잠금은 데이터를 변경하거나 작성하기 전에 행을 잠가야하므로 주로 쓰기(INSERT, UPDATE, DELETE, REPLACE)에 사용
  • 잠금 시간은 행에 대한 동시성에 따라 달라진다
  • 읽기(SELECT)에는 비잠금 읽기와 잠금 읽기가 존재
    • 잠금 읽기: SELECT FOR UPDATE, SELECT FOR SHARE
    • 비잠금 읽기: 일반 SELECT
  • 추가로 쓰기 쿼리에 서브쿼리로 들어가는 SELECT(임시 테이블)는 공유 로우 락을 획득한다
  • 비잠금 읽기는 로우 락을 획득하지 못하더라도 메타데이터 락과 테이블 락을 획득하므로 잠금시간이 0은 아니다
  • SELECT 쿼리는 접근하는 모든 테이블에서 공유 메타데이터 락을 획득하고, 이는 다른 공유 메타데이터 락과 호환된다
  • 단 ALTER TABLE같은 배타 메타데이터 락을 획득하는 쿼리는 다른 모든 배타 메타데이터 락을 차단합니다
  • 숙지 해야하는 내용 5가지
    • innodb_lock_wait_timeout 시스템 변수가 각각의 로우 락에 적용되므로 잠금 시간은 이보다 상당히 클 수 있습니다.
    • 잠금과 트랜잭션 격리 수준은 서로 관련되어 있습니다.
    • innoDB는 쓰지 않는 행을 포함하여 접근하는 모든 행을 잠급니다
    • 잠금은 트랜잭션 커밋이나 롤백할 때 해제되며 때로는 쿼리 실행 중에도 해제됩니다
    • innoDB에는 record, gap, next-key 등 다양한 유형의 잠금이 있습니다
  • 스터디중 나온내용
    • X락은 다른 X락만 차단하고, 단순 SELECT는 허용한다
    • SELECT ... FOR UPDATE시 UPDATE되기전까지 SELECT는 해당 테이블에 접근 가능, UPDATE후 UNDO 로그가 하나 쌓이고 기존에 SELECT하던(같은 트랜잭션을 열었던) 쿼리는 해당 UNDO 로그를 보게됨
• 조회된 행
  • 조회된 행은 MySQL이 쿼리 조건 절에 일치하는 행을 찾으려고 접근한 행의 수를 나타냅니다
  • 쿼리와 인덱스의 선택도(데이터 세트에서 특정 값을 얼마나 잘 골라낼 수 있는지에 대한 지표)를 나타낸다
  • 쿼리와 인덱스의 선택도가 높을수록 MySQL이 일치하지 않는 행을 조회하는데 낭비하는 시간이 줄어든다
• 보낸 행
  • 클라이언트에 반환된 행의 수(결과 세트 크기)를 나타낸다
  • 이상적인 경우 보낸 행 = 조회된 행
    • 10,000개 행만 있는 테이블의 1,000개 행은 응답 시간이 허용되더라도 비율 10%의 문제 소지가 될 수 있다
    • 비율과 관계없이 보낸 행과 조회된 행이 같고 값이 의심스러울 정도로 높으면 쿼리가 테이블 스캔을 유발한다는 소리, 이는 성능면에서 매우 안좋다
  • 보낸 행 < 조회된 행
    • 쿼리나 인덱스의 선택도가 좋지 않다는 신호
    • 조회된 행중 보낼려고 의도한 값의 비율이 적다는 뜻(조회된 데이터중 일치하지 않는 데이터가 많다)
    • 많은 시간을 낭비하고 있다는 뜻(개선해야한다)
  • 보낸 행 > 조회된 행
    • 드문 케이스...
  • 보낸 행 자체가 문제가 되는 케이스는 적다
• 영향받는 행
  • 삽입, 갱신, 삭제된 행의 수를 나타낸다
  • 엔지니어는 해당하는 행에만 영향을 미치도록 주의해야한다
  • 대량 INSERT, UPDATE, DELETE는 복제 지연, 변경 목록 길이, 잠금 시간, 전반적인 성능 저하와 같이 여러 문제를 야기
  • 적당한 배치 크기의 정답은 없다, MySQL과 애플리케이션이 쿼리 응답 시간에 영향을 미치지 않고 유지할 수 있는 배치 크기와 비율로 결정해야한다
• 셀렉트 스캔
  • 첫 번쨰로 접근한 테이블에서 수행한 전체 테이블 스캔 횟수를 나타낸다
  • 쿼리가 2개 이상의 테이블에 접근할 때는 셀렉트 풀 조인 메트릭이 적용된다
  • 이는 쿼리가 인덱스를 사용하지 않는다는 것을 의미하므로 일반적으로 성능에 좋지 않다
  • 셀렉트 스캔이 0이 아니면 쿼리 최적화를 강력하게 권장
  • 테이블 스캔은 MySQL이 결정하게 되는데, 인덱스의 고윳값 수인 카디널리티와 테이블의 전체 행 수, 기타 비용을 고려해 쿼리가 조회할 행 수를 추정한다
• 셀렉트 풀 조인
  • 조인된 테이블을 대상으로 전체 테이블을 스캔한 수를 나타낸다
  • 셀렉트 스캔과 유사하지만 더 나쁘다
  • 항상 0을 유지해야한다
  • EXPLAIN으로 쿼리 분석을 하면, MySQL은 테이블 조인 순서를 위쪽(첫 번째 테이블)에서 아래쪽(마지막 테이블)으로 출력
  • 이때 셀렉트 스캔은 첫 번째 테이블에만 적용, 나머지는 셀렉트 풀 조인 테이블에만 적용된다
    • EXPLAIN 했을때 type: ALL이 된다면 셀렉트 스캔 혹은 셀렉트 풀 조인이다
  • 테이블 조인 순서는 MySQL이 결정
• 디스크에 생성된 임시 테이블
  • 디스크에 생성된 임시 테이블의 수를 나타낸다
  • 쿼리가 메모리에 임시 테이블을 만드는 것은 정상이나, 메모리에 임시 테이블이 너무 커지면 MySQL은 임시 테이블을 디스크에 쓴다, 이 경우 메모리보다 디스크 I/O가 훨씬 느리므로 응답 시간에 영향을 미친다
  • 과도하게 사용되는 디스크의 임시 테이블은 쿼리 최적화가 필요함을 나타내거나, 시스템 변수 tmp_table_size가 너무 작음을 나타낸다(항상 쿼리 최적화를 시도하고, 시스템 변수는 그 후에 생각할 것)
• 쿼리 카운트
  • 쿼리 실행 횟수
  • 해당 값은 매우 낮고 쿼리가 느리지 않는 한 기준이 없고 임의적이지만, 낮고 느림인 경우 최적화 대상으로 조사해야한다

 

메타데이터와 애플리케이션

• 쿼리 메트릭보다 쿼리 분석에 더 많이 사용하는 것이 바로 메타데이터다
• EXPLAIN 계획, 각 테이블의 구조(SHOW CREATE TABLE)등이 주요 메타데이터
• 어플리케이션에서 쿼리가 어떤 용도로 사용되고 있는지도 주요한 지표이다

 

상대값

• 각 쿼리 메트릭에서 객관적으로 긍정적으로 보는 유일한 값은 0이다
• 예시) 수천 개의 행에 불필요하게 접근해 반환하고, 그것이 DB 크기가 커질수록 행 수도 늘어 시간이 지남에 따라 속도가 느려졌다. 원인은 SELECT COUNT (*) 였다

 

평균, 백분위수, 최대

• 평균은 지나치게 낙관적
  • 평균에 속으면 안된다, 매우 크거나, 매우 작은 몇개의 값이 평균 응답시간을 왜곡할 수 있다
• 백분위수는 어디까지나 추정
  • 평균이 갖는 문제를 보완(P95, P99)
  • 무시되는 값의 작은 비율을 특잇값으로 간주
  • 높은 백분위수가 원하는 값이 되도록 하는게 바람직하다(이상적이나, 현실은 힘들 수도 있다)
• 최대는 최상의 표현
  • 최소, 평균, P99 쿼리 시간은 모두 밀리초지만, 최대 쿼리 시간은 초 단위일 수도 있다
  • 최대 쿼리 시간은 백분위수가 갖는 문제를 보완하므로 어떤 값도 버리지 말라

 

쿼리 응답 시간 개선 맛보기

• 쿼리 최적화라고도 한다
• 직접 쿼리 최적화
  • 쿼리와 인덱스를 변경하는 일
  • 종류
    • 인덱스 머지 최적화
    • 해쉬 조인 최적화
    • 인덱스 컨디션 푸시다운 최적화
    • 다중 범위 읽기 최적화
    • Constant-Folding 최적화
    • IS NULL 최적화
    • ORDER BY 최적화
    • GROUPT BY 최적화
    • DISTINCT 최적화
    • LIMIT 쿼리 최적화
    • 기타 등
  • 직접 쿼리 최적화는 필요조건이지만 충분 조건은 아니다
  • 선행 수행작업이지만, 이를 통해 최적화가 실패한다면 간접 쿼리 최적화로 이어져야한다
• 간접 쿼리 최적화
  • 데이터와 접근 패턴을 변경하는 일
  • 쿼리 변경 대신 쿼리가 접근하는 대상과 방법(각각의 데이터와 접근 패턴)을 변경한다
  • 결국 쿼리, 데이터, 접근 패턴은 성능과 관련하여 뗄 수 없는 관계이므로 이러한 변경은 쿼리를 간접적으로 최적화해준다
  • TRUNCATE TABLE등이 해당한다 ( 데이터를 줄여, 응답시간을 확보하기 )

 

언제 쿼리를 최적화해야 할까?

• 성능이 고객에게 영향을 미칠 때
  • 사후 대응이라 좋다고 말할 순 없다
  • 적정 대기 시간을 초과했을때, 최적화를 고려해야한다
• 코드 변경 전후
• 한달에 한번
  • 쿼리 응답 시간은 데이터와 접근 패턴이 변경됨에 따라 달라지기에 주기적으로 관리해야한다
  • 처음 설계때 데이터가 인입되고, 어떻게 사용될건지를 고려해야한다

 

요점 정리

• 성능은 쿼리 응답 시간, 즉 MySQL이 쿼리를 실행하는 데 걸리는 시간입니다
• 쿼리 응답 시간은 의미 있고 실행 가능하므로 MySQL 성능에서 핵시 지표입니다
• 쿼리 메트릭은 슬로 쿼리 로그나 성능 스키마에서 비롯
• 성능 스키마는 쿼리 메트릭 중 최상의 소스입니다
• 쿼리 메트릭은 다이제스트(SQL문 정규화)에 의해 그룹화되고 집계됩니다
• 쿼리 프로파일은 느린 쿼리를 보여 주며 느리다는 것은 정렬 메트릭과 관련
• 쿼리 보고서는 하나의 쿼리를 대상으로 사용할 수 있는 모든 정보를 보여 주며 쿼리 분석에 사용
• 쿼리 분석의 목표는 느린 응답 시간을 해결하는 것이 아니라 쿼리 실행을 이해하려는 것
• 쿼리 분석에는 쿼리 메트릭, 메타데이터(EXPLAIN 계획, 테이블 구조 등), 애플리케이션 지식을 사용
• 쿼리 응답시간을 향상시키려면 직접 및 간접 쿼리 최적화를 알아야한다