🦤 MySQL 심화 Ⅰ - 쿼리 응답 시간

📖 『MYSQL을 더 빠르게, 성능 최적화 선택과 집중』을 읽고 정리한 글입니다.

개요

“성능은 곧 쿼리 응답 시간이다.”

• 이 책의 목적은 MySQL 성능을 현저하게 개선하는 것이다.
• MySQL 성능 요소는 여러 가지가 존재하며, 그중 쿼리 응답 시간을 먼저 살펴볼 것이다.

쿼리 응답 시간이란?

• MySQL이 쿼리를 실행하는 데 소요되는 시간이다.
• 응답 시간, 쿼리 시간, 실행 시간, 쿼리 지연 시간이라고도 한다.
• MySQL이 쿼리를 받았을 때 시작되고, 결과 세트를 클라이언트에 전송한 시점까지의 경과 시간을 의미한다.
• 쿼리 응답 시간이 줄면 성능은 향상된다.

거짓 성능에 관한 실화

• 2004년도 당시에는 MySQL에 관한 책이나, 블로그, 도구가 거의 없었다.
• 결과적으로 MySQL 성능 문제를 해결하기 위한 최신 기술은 고객에게 더 많은 RAM을 판매하는 것 뿐이었다.
• 저자는 더 많은 RAM을 판매하는 대신 MySQL 성능 문제의 근본 원인을 찾아 해결하고자 기술적인 심층 분석을 수행하기로 마음 먹음
• 당시 문제는 대량의 데이터로 인해 게시판 속도가 느려진 상황이었음
• 저자는 중요한 인덱스가 누락된 단일 쿼리를 발견했고, 쿼리를 제대로 인덱싱하자 성능이 획기적으로 향상됨.
• 물론 고객은 추가 비용을 지불할 필요가 없어짐
• 이렇듯, 이 책에 나오는 모범 사례와 기술들로 꽤 많은 MySQL 성능 문제를 해결할 수 있음

핵심 지표(North Star)

• 기능을 배포하고, 시스템 과열을 통제하는 사이에 누가 MySQL에 시간을 할애할 수 있을까?
• 더 많은 RAM, 더 빠른 CPU, 더 높은 스토리지의 IOPS가 필요한 것일까?
• 아니면 최근 코드 변경으로 인한 문제일까?
• 그것도 아니라면 DBA가 DB에 무언가 작업을 하고 있어 이른바 ‘시끄러운 이웃’ 문제가 발생하고 있는 것일까?
• 신규로 등록한 앱이 평판이 좋아 사용자가 몰린 것이 원인일까?
• 그것보다도, 쿼리 응답 시간은 실제 해결책으로 이어지는 다음과 같은 강력한 특성이 있으므로 가장 먼저 살펴보아야 한다.

의미 있는 것

• 가령 데이터베이스 속도가 빠를 때는 아무도 그것을 살펴보거나 의문을 갖지 않는다.
• 쿼리 응답 시간이 우리가 경험하는 유일한 메트릭이기 때문이다.
• 쿼리를 실행하는데 7.5초가 걸리면 우리는 7.5초의 조급함을 경험할 수 있다.
• 우리에게는 시간이 의미 있다.

실행 가능한 것

• 코드를 담당하고, 쿼리 응답 시간을 직접 확인할 수 있다면 쿼리를 변경할 수 있다.
• 코드를 담당하지 않고, 접근 권한이 없더라도 쿼리 응답 시간을 간접적으로 최적화 할 수 있다.
• 결국 MySQL 성능 문제를 하드웨어 문제로 돌리지 않고, 핵심 지표인 쿼리 응답 속도 향상에 집중해야 한다.
• MySQL이 시작되면 먼저 쿼리 메트릭으로 MySQL이 수행하는 작업을 확인하고, 느린 쿼리를 분석하고 최적화하여 응답 시간을 단축해야 함으로써 문제를 개선해나가야 한다.

쿼리 보고

• 쿼리 메트릭은 응답 시간, 잠금 시간, 조회된 행 등 쿼리 실행에 관하여 중요한 통찰력을 제공한다.
• 쿼리 메트릭은 다른 메트릭과 마찬가지로 엔지니어에게 의미 있는 방식으로 수집, 집계, 보고되어야 하는 원 시 값이다.
• 이 절에서는 쿼리 메트릭 도구가 쿼리 메트릭을 쿼리 보고서로 어떻게 변환하는지 설명한다.
• 쿼리 분석은 실제 수행할 작업이다.
• 쿼리 실행을 이해하기 위한 목적으로 보고된 쿼리 메트릭과 기타 정보를 분석하게 된다.
• MySQL 성능을 높이기 위해 쿼리를 최적화하려면 쿼리 실행 방식을 이해해야 하고, 이를 이해하려면 쿼리 보고서와 메타데이터 등 관련 정보를 사용하여 분석해야 한다.
• 하지만 쿼리 보고는 쿼리 실행에 대한 중요한 통찰력을 제공하는 쿼리 메트릭의 모음을 나타내기 때문에 먼저 쿼리 보고를 이해해야 한다.
• 다음 3가지를 이해해야 쿼리 분석을 제대로 이해할 수 있다.

1. 소스
   • 쿼리 메트릭은 2개의 소스에서 비롯된다.
   • MySQL 배포 및 버전에 따라 다르다.
2. 집계
   • 쿼리 메트릭은 정규화된 SQL 문법을 기준으로 그룹화되고 집계된다.
3. 보고
   • 쿼리 보고서는 고급 프로파일과 쿼리에 특화된 보고서로 구성된다.

소스

• 쿼리 메트릭은 슬로 쿼리 로그나 성능 스키마에서 비롯된다.
• 이름에서 알 수 있듯 슬로 쿼리 로그는 디스크에 있는 로그 파일이고, 성능 스키마는 performance_schema와 같은 이름의 데이터베이스다.
• 디스크의 로그 파일과 데이터베이스의 테이블은 본질적으로 완전히 다르지만, 둘 다 쿼리 메트릭을 제공한다.
• 주요 차이점은 얼마나 많은 쿼리 메트릭을 제공하는지이다.
• 둘 다 쿼리 응답 시간을 제외하고 3 ~ 20개 이상의 메트릭을 제공한다.

슬로 쿼리 로그란 이름에는 역사가 있다. 전부터 MySQL은 N초 이상 실행되는 쿼리만 기록했고, 이때 최솟값은 1이었다. 그 미만으로 수행되는 쿼리는 기록하지 않았는데, 그 이유는 빨랐기 때문이다. 슬로 쿼리 로그는 이름 그대로다. 오늘날엔 최솟값이 0이며, 이렇게 설정하면 MySQL은 실행되는 모든 쿼리를 기록한다.

• 여러 상황을 고려해봐도 성능 스키마는 현재 모든 버전과 배포 버전에 포함되어 있으므로 쿼리 메트릭을 제공하는 가장 좋은 소스이다.
• 성능 스키마는 로컬과 클라우드에서 작동하며, 이 책에서 제공하는 쿼리 메트릭을 모두 제공하고 가장 일관성이 있다.
• 또한 MySQL을 심층 분석할 수 있는 풍부한 여타 데이터를 포함하므로 그 유용성은 쿼리 메트릭 차원을 훨씬 능가한다.
• 슬로 쿼리 로그도 좋은 소스이자, 다음과 같이 매우 다양하다.

1. MySQL
   • 시스템 변수 log_slow_extra를 활성화하면 된다.
   • 성능 스키마에 비해 제공하는 쿼리 메트릭이 제한적이어서, 가능하면 성능 스키마를 사용하는 것이 좋다.
   • 쿼리 메트릭이 제한되면 분석의 질은 낮아질 수 밖에 없다.
2. Percona 서버
   • 시스템 변수 log_slow_inquency를 구성하면 슬로 쿼리 로그에 훨씬 더 많은 메트릭을 제공한다.
   • 시스템 변수 log_slow_rate_limit가 구성되면 쿼리 샘플링을 지원하므로 사용량이 많은 서버 유용하다.
   • 이러한 기능 때문에 Percona 서버의 슬로 쿼리 로그는 훌륭한 소스로서 역할을 한다.
3. MariaDB 서버
   • Percona 서버의 슬로 쿼리 로그 향상을 사용하지만 두 가지 차이점이 있다.
   • 시스템 변수 log_slow_verbosity가 MariaDB에서는 다르게 구성이 되고, Rows_affected 메트릭을 제공하지 않는다는 점이다.
   • 이외에는 마찬가지로 훌륭한 소스로서 역할을 한다.

• 슬로 로그 쿼리는 기본으로 비활성화되어 있지만, MySQL을 재시작할 필요없이 바로 활성화할 수 있다.
• 성능 스키마는 기본으로 활성화되어야 하지만, 일부 클라우드 제공자는 비활성화를 기본으로 설정하기도 한다.
• 또한 슬로 로그 쿼리와는 달리 성능 스키마를 활성화하려면 MySQL을 재시작해야 한다.

슬로 쿼리 로그는 long_query_time을 0으로 설정하면 모든 쿼리를 기록할 수 있지만, 주의해야 한다. 사용량이 많은 서버에서는 디스크 입출력이 증가하고, 상당한 양의 디스크 공간을 사용할 수 있다.

집계

• 쿼리 메트릭은 쿼리 별로 그룹화되고 집계된다.
• 쿼리 응답 시간은 MySQL 성능에서 핵심 지표이므로 쿼리 별로 쿼리 메트릭을 그룹화하는 것이 응답 시간이 가장 느린 쿼리를 확인하는 최선의 방법이다.
• 한 가지 문제가 존재하는데, 어떻게 해야 쿼리가 속한 그룹을 고유하게 식별할 수 있는 지이다.
• 가령 시스템 메트릭(CPU, 메모리, 스토리지) 등은 호스트 이름이 고유하므로 호스트 이름 별로 그룹화된다.
• 그러나 쿼리에는 호스트 이름처럼 고유하게 식별되는 특성이 없다.
• 이를 해결하는 방법은 다음과 같이 정규화된 SQL 문을 SHA-256 해시로 변환하는 것이다.

SELECT col FROM tbl WHERE id=1;                                  # SQL
SELECT `col` FROM `tbl` WHERE `id` = ?                           # 정규화된 SQL
3d590d7a91357f8b1e2f8458f8289111321b6bbad6ff3eb785e4fc18a79ca06c # 해시값

• 정규화된 SQL을 다이제스트 텍스트, 해시값은 다이제스트 해시라고 한다.
• MySQL 매뉴얼에서는 이 둘을 가리킬 때 모호하게 다이제스트라고 지칭한다.
• 쿼리 메트릭의 맥락에서 용어에 중요한 변화가 있다.
• 쿼리라는 용어는 다이제스트 텍스트와 동의어로 변경된다.
• SQL문은 쿼리 샘플이라고도 하며, 보고될 수도 있고 그렇지 않을 수도 있다.
• 대부분의 쿼리 메트릭 도구는 보안 때문에 샘플을 폐기하고 다이제스트 텍스트와 해시만 보고된다.
• EXPLAIN 명령어를 사용하면 쿼리 실행을 이해하는데 필요한 메타 데이터를 생성할 수 있으므로 쿼리 분석에 샘플이 필요하다.
• 일부 쿼리 메트릭 도구는 샘플을 EXPLAIN한 다음 폐기하고 EXPLAIN 계획(EXPLAIN 결과)을 보고한다.
• 샘플만 보고하는 도구들도 있지만, EXPLAIN 명령에 SQL을 복사 및 붙여넣기하면 되므로 여전히 편리하다.
• 만약 이러한 기능이 없으면 소스에서 SQL 샘플을 수동으로 추출하거나 작성해야 한다.
• 용어는 다음과 같이 쿼리 메트릭 도구에 따라 다르다.

공식	대체
SQL 문	쿼리
샘플	쿼리
다이제스트 텍스트	클래스, 패밀리, 핑거프린트, 쿼리
다이제스트 해시	클래스 ID, 쿼리 ID, 시그니처

“간결함은 지혜의 본질이다.”

• Percona에서 유래한 다른 용어는 쿼리 추상화이다.
• SQL 명령과 테이블 목록으로 고도로 추상화된 SQL 문이다.

SELECT col FROM tbl WHERE id=1; # SQL
SELECT tbl                      # 쿼리 추상화

• 쿼리 추상화는 고유하지는 않지만 간결해서 유용하다.
• 대개 개발자들은 쿼리 추상화만 보고도 그것이 나타내는 전체 쿼리를 알 수 있다.
• 정규화 과정은 또 다른 중요한 점을 제시한다.
• 다른 구문으로 같은 논리의 쿼리를 동적으로 생성하면 안된다.
• 그렇지 않으면 다이제스트로 정규화되고 다른 쿼리로 보고된다.

SELECT name FROM captions WHERE last_name = 'Picard';
SELECT name FROM captions WHERE last_name = 'Picard' AND first_name = 'Jean-Luc';

• 위 두 쿼리는 논리적으로는 같을 수 있지만, 서로 다른 다이제스트로 정규화되므로 보고 측면에서는 서로 다른 쿼리이다.
• 어떤 쿼리 메트릭 도구도 이 쿼리들을 결합할 수 없다.
• 그리고 모든 조건, 특히 WHERE 절은 쿼리 실행과 최적화에 영향을 미치므로 별도로 이 쿼리들을 보고하는 것이 기술적으로 옳다.
• 쿼리 정규화에서 한 가지 알아야 할 점은 값이 제거되므로 다음 두 쿼리는 같은 다이제스트로 정규화되고 그룹화되고 집계되어 하나의 쿼리로 보고된다.

SELECT `name` FROM star_ships WHERE class IN ('galaxy');             # SQL 1
SELECT `name` FROM star_ships WHERE class IN ('galaxy', 'intrepid'); # SQL 2
SELECT `name` FROM star_ships WHERE class IN (...);                  # 다이제스트 텍스트

보고

• 하나의 애플리케이션이 수백 개의 쿼리를 가질 수 있으므로 보고는 도전이자 기술이라고 할 수 있다.
• 각 쿼리에는 많은 메트릭이 존재하며, 각 메트릭에는 최소, 최대, 평균, 백분위수 등 여러 통곗값이 있다.
• 또한 각 쿼리에는 샘플, EXPLAIN 계획, 테이블 구조 등의 메타 데이터가 있다.
• 이 모든 데이터를 저장하고 처리하고 나타내는 것은 어렵다.
• 거의 모든 쿼리 메트릭 도구는 쿼리 프로파일과 쿼리 보고서라는 두 가지 수준의 계층 구조로 데이터를 제공한다.

쿼리 프로파일

• 쿼리 프로파일에는 느린 쿼리가 표시된다.
• 쿼리 보고를 위해 최상단에 구성된 것으로, 일반적으로 쿼리 메트릭 도구에 처음 표시되는 항목이다.
• 쿼리 다이제스트와 함께 메트릭의 제한된 하위 세트를 제공하므로 프로파일이라고 부른다.
• 느림이라는 개념은 쿼리가 정렬되는 쿼리 메트릭의 집계치인 정렬 메트릭과 관련된다.
• 정렬 메트릭이 쿼리 시간이 아니더라도 첫 번째로 정렬된 쿼리를 가장 느린 쿼리라고 한다.
• 만약 정렬 메트릭이 평균적으로 전송된 행 수라면, 첫 번째로 정렬된 쿼리가 여전히 가장 느린 쿼리라고 한다.
• 어떤 쿼리 메트릭이든 정렬 메트릭이 될 수 있지만, 일반적으로는 쿼리 시간이 기본 정렬 메트릭이다.
• 쿼리 실행 시간을 줄이면 MySQL이 더 많은 작업을 수행하거나 다른 작업을 더 빨리 수행할 수 있는 시간을 확보할 수 있다.
• 쿼리 시간을 기준으로 쿼리를 정렬하면 가장 느리고 시간이 오래 걸리는 쿼리부터 확인할 수 있다.
• 그러나 쿼리 시간이 어떻게 집계되는지는 일반적으로 알기 어렵다.
• 가장 일반적인 집계치는 다음과 같다.

쿼리 총시간

• 쿼리 총시간은 실행 시간의 총합이다.
• 이 값은 MySQL이 실행하는 데 가장 많은 시간을 소비하는 쿼리가 어느 것인지 알 수 있는 중요하고 가장 일반적인 집계치다.
• 쿼리 메트릭 도구는 MySQL이 각 쿼리를 실행하는 데 소요되는 모든 시간을 합산한다.
• 총시간이 가장 긴 쿼리는 가장 느리고, 시간이 오래 걸리는 쿼리다.
• 이것이 왜 중요한 지를 보여주는 예시가 있다.
• 가령 쿼리 A는 응답 시간이 1초이고 10번 실행되지만, 쿼리 B는 응답 시간이 0.1초이고 1,000번 실행된다고 하자.
• 쿼리 A는 응답 시간이 훨씬 느리지만, 쿼리 B가 10배 더 많은 시간이 소요된다.
• 쿼리 총시간으로 정렬된 쿼리 프로파일에서 가장 느린 쿼리는 쿼리 B다.
• 이는 쿼리 B를 최적화함으로써 MySQL의 시간을 가장 많이 확보할 수 있기 때문에 중요하다.

실행 시간 비율

• 실행 시간 비율은 쿼리 총시간을 실행 총시간으로 나눈 값이다.
• 예를 들어 쿼리 C의 쿼리 총시간이 321ms이고, 쿼리 D의 쿼리 총시간이 100ms이면 실행 총시간은 421ms이다.
• 개별적으로 쿼리 C는 실행 총시간의 (321 / 421) × 100 = 76.2%이고 쿼리 D는 실행 총시간의 (100 / 421) × 100 = 23.8%이다.
• 즉 MySQL은 쿼리를 실행하는 데 총 421ms가 소요되었으며, 그중 76.2%는 쿼리 C를 실행하는 데 할애하였다.
• 실행 시간 비율로 정렬된 프로파일에서는 쿼리 C가 가장 느린 쿼리이다.
• 실행 시간 비율은 일부 쿼리 메트릭 도구에서만 사용된다.

쿼리 부하

• 쿼리 부하는 쿼리 총시간을 클럭 타임으로 나눈 것이다.
• 클럭 타임이란 시간 범위에 대한 전체 초 수이다.
• 시간 범위가 4분이면 클럭 시간은 300초이다.
• 예를 들어 쿼리 E의 쿼리 총시간이 250.2초이면 부하는 250.2 / 300 = 0.83이다.
• 또한 쿼리 F의 쿼리 총시간이 500.1초이면 부하는 500.1 / 300 = 1.67이다.
• 쿼리 부하 별로 정렬된 쿼리 프로파일에서 쿼리 F는 가장 부하가 크므로 가장 느린 쿼리에 해당된다.
• 부하는 시간과 관련 있기도 하지만 동시에 실행되는 다중 인스턴스와 같은 동시성을 미묘하게 나타내기도 한다.
• 쿼리 부하가 평균적으로 1.0보다 작으면 쿼리가 동시에 실행되지 않는다.
• 아니라면 쿼리 동시성을 나타낸다.
• 예를 들어 쿼리 부하가 3.5라는 것은 언제든지 조회하면 실행 중인 쿼리의 인스턴스가 3.5개 정도 있다는 것과 같다.
• 쿼리 부하가 높을 수록 쿼리가 동일하거나 가까운 행에 접근할 때 경합할 가능성이 커진다.
• 10보다 큰 쿼리 부하는 매우 느린 쿼리일 가능성이 있지만 예외 상황이 있을 수 있다.
• 정렬 메트릭이 보낸 행과 같은 비시간적 쿼리 메트릭을 사용하면 진단하려는 내용에 따라 다른 집계치가 의미 있을 수 있다.
• 이것은 쿼리 총시간보다 훨씬 덜 일반적이지만, 때로는 최적화할 만한 가치가 있는 쿼리라는 것을 알려준다.

쿼리 보고서

• 쿼리 보고서는 하나의 쿼리에 대해 알아야 할 모든 것을 보여준다.
• 이것은 쿼리 보고를 구성하는 두 번째 단계이다.
• 일반적으로 쿼리 프로파일에서 가장 느린 쿼리를 선택하는 것으로 시작한다.
• 또한 모든 쿼리 메트릭과 메타데이터도 제공한다.
• 쿼리 프로파일은 그것 자체로도 많은 정보를 제공하지만, 쿼리 보고서는 쿼리 분석에 사용되는 많은 정보들로 구성되어 있다.
• 따라서 쿼리 실행을 이해하는 데 도움이 되므로 정보는 많을 수록 좋다.
• 쿼리 보고서는 메트릭 도구에 따라 크게 달라진다.
• 가장 기본적인 최소 보고서에는 소스에서 비롯된 모든 쿼리 메트릭과 최소, 최대, 평균, 백분위수 등 해당 메트릭의 기본 통곗값이 포함된다.
• 전체 보고서에는 쿼리 샘플, EXPLAIN 계획, 테이블 구조 등의 메타 데이터가 포함된다.
• 일부 쿼리 메트릭 도구에서는 메트릭 그래프, 히스토그램, 이상 감지, 시간 이동 비교, 개발자 도구, SQL 주석 등의 추가 정보를 제공한다.
• 쿼리 분석에는 보고서의 쿼리 메트릭만 있으면 되고, 메타 데이터를 수동으로 수집할 수 있다.
• 만약 사용하는 쿼리 메트릭 도구가 쿼리 메트릭만 보고한다면, 최소한 EXPLAIN 계획과 테이블 구조를 수동으로 수집하는 것부터 시작해야 한다.
• 이렇게 쿼리 보고서가 있다면, 쿼리 분석을 수행할 준비가 된 것이다.