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

1장. 쿼리 응답 시간

주지민 2024. 8. 22. 01:47
반응형

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

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 계획, 테이블 구조 등), 애플리케이션 지식을 사용
  • 쿼리 응답시간을 향상시키려면 직접 및 간접 쿼리 최적화를 알아야한다

 

 

728x90
반응형
저작자표시