티스토리 뷰
“성능은 곧 쿼리 응답 시간이다.”
해당 포스트의 목적은 MySQL 성능을 현저하게 개선하는 것이며, 다양한 측면에서 그 방안을 탐구하고자 한다. MySQL을 관리하는 입장이 아니라 사용하는 입장으로, 최소한의 노력으로 최대한의 성과가 필요한 개발자로서 접근하고자 한다는 것이다.
쿼리 응답 시간(Query Response Time)이란?
쿼리 응답 시간은 MySQL이 쿼리를 실행하는데 소요되는 시간이다.
소요 시간(timing)은 MySQL이 쿼리를 받을 때 시작되고 결과 세트를 클라이언트에게 전송한 시점까지의 경과 시간을 의미한다.
쿼리 응답 시간은 여러 단계와 대기로 구성되지만, 완벽한 상세 명세는 가능하지도 않고 필요하지도 않다.
핵심지표(North Star)로서 쿼리 응답 시간
솔직히 DB가 빠를 때는 아무 이의를 제기할 필요가 없다. 그러나 느려진다면 이의가 필요해진다. 즉, 쿼리 응답 시간은 누구나 진정으로 관심을 가지는 유일한 메트릭이다.
그렇기 때문에 우리는 MySQL이 시작되면 먼저 쿼리 메트릭으로 MySQL이 수행하는 작업을 확인한 뒤, 느린 쿼리를 분석하고 최적화로 응답 시간을 단축하는데 집중해야 한다.
쿼리보고
쿼리 메트릭은 응답 시간, 잠금 시간, 조회된 행 등 쿼리 실행에 관하여 중요한 통찰력을 제공한다. 그러나 쿼리 메트릭 그 자체는 엔지니어에게 의미 있는 방식으로 원시 값을 제공한다.
이를 어떻게 해석하고 학습해야하는지 알아볼 필요가 있다.
쿼리 분석은 실제 수행해야 할 작업으로, 쿼리 실행을 이해하기 위한 목적으로 보고된 쿼리 메트릭과 기타 정보를 분석하게 된다.
따라서 아래 3가지를 이해해야 하지만 이후에 추후 포스트에서 더 알아볼 예정이다.
- 소스 : 쿼리 메트릭은 2개의 소스에서 비롯되며 MySQL 배포 및 버전에 따라 다르다.
- 집계 : 쿼리 메트릭 값은 정규화된 SQL 문법을 기준으로 그룹화 되고 집계된다.
- 보고 : 쿼리 보고서는 고급 프로파일과 쿼리에 특화된 보고서로 구성된다.
1. 소스
쿼리 메트릭은 슬로 쿼리 로그와 성능 스키마에서 비롯된다. 이름에서 알 수 있듯이 슬로 쿼리 로그는 디스크에 있는 로그 파일이고, 성능 스키마는 perfomance_schema와 같은 이름의 데이터베이스다. 디스크의 로그 파일과 데이터베이스의 테이블은 본질적으로 완전히 다르지만, 둘 다 쿼리 메트릭을 제공한다.
주요 차이점은 얼마나 많은 메트릭을 제공하는지 이다.
참고) 슬로 쿼리 로그란 이름에는 역사가 있는데, 예전부터 MySQL은 N초 이상 실행되는 쿼리들만 기록했고 이 떄 N초의 최솟값은 1 이었다.
MySLQ 8.0.14에서 슬로 쿼리 로그는 시스템 변수 log_slow_extra를 활성화하면 쿼리메트릭 6개 정보를 제공한다. 그중에서 Rows-affected, Select_scan, Select_full_join이 누락되어있다. (충분히 좋지만 가능하면 성능 스키마를 사용하는 것이 좋다)
슬로 쿼리 로그는 기본적으로 비활성화되어있지만, MySQL을 다시 시작할 필요없이 바로 활성화할 수 있다. (성능 스키마는 기본으로 활성되어야하지만 일부 클라우드 제공자는 비활성화 설정을 하기도해서 활성화시 MySQL을 다시 실행해야함 )
2. 집계
쿼리메트릭은 쿼리별로 그룹화 되고 집계된다. 쿼리 응답 시간은 MySQL 성능에서 핵심 지표이므로 쿼리별로 쿼리 매트릭을 그룹화 하는 것이 아닌, 응답 시간이 가장 느린 쿼리를 확인하는 최선의 방법이다. 이는 쿼리 보고와 분석의 기초를 구성하는 틀이 된다.
여기서 사소한 문제가 하나 있는데 어떻게 쿼리를 고유하게 식별하여 쿼리가 속한 그룹을 결정하냐는 것이다. 이를 해결하는 방법은 정규화된 SQL문을 SHA-256 해시로 변환하는 것으로 가능해진다.
<SQL 문 → 다이제스트 텍스트 → 다이제스트 해시>
SQL 문
SELECT ‘name’ FROM start_ships WHERE class IN (’galaxy’)
SELECT ‘name’ FROM start_ships WHERE class IN (’galaxy’, ‘interpid)
다이제스트 텍스트
SELECT ‘name’ FROM ‘startships’ WHERE ‘class’ IN (…)
다이제스트 해시
asdkjnqweioufnqowldfqpo…
SQL문은 쿼리 샘플이라고도 하며 보고될 수도 보고되지 않을 수도 있다. 대부분의 쿼리 메트릭 도구는 보안 떄문에 샘플을 폐기하고 다이제스트 텍스트와 해시만 보고한다. EXPLAIN 명령어를 사용하면 쿼리 실행을 이해하는데 필요한 메타데이터를 생성할 수 있으므로 쿼리 분석 샘플이 필요하다.
3. 보고
각 쿼리에는 많은 메트릭이 있으며 각 메트릭에는 최소, 최대, 평균, 백분위수 등 여러 통곗값이 있다. 또한 각 쿼리에는 샘플, EXPLAIN 계획, 테이블 구조 등의 메타데이터가 있다.
이 모든 데이터를 저장하고 처리하는 것은 어려운데, 모든 쿼리 메트릭 도구는 쿼리 프로파일과 쿼리 보고서 라는 2가지 수준의 계층 구조로 데이터를 제공한다.
쿼리 프로파일
쿼리 프로파일에는 느린 쿼리가 표시된다. 쿼리 보고를 위해 최상단에 구성된 것으로 처음 표시되는 항목이다. 쿼리 다이제스트와 함께 메트릭의 제한된 하위 세트를 제공하여 프로파일이라고 부른다.
쿼리 프로파일에 집계치는 다음과 같다.
1. 쿼리 총 시간(total query tiem)
쿼리 총시간은 실행시간의 총합이다. 이 값은 MySQL이 실행하는데 가장 많은 시간을 소비하는 쿼리가 어느것인지에 대한 중요한 질문에 답하므로 가장 중효한 집계치로 볼 수 있다.
총시간이 가장 긴 쿼리는 가장 느리고 시간이 오래걸리는 쿼리로 아래 예시를 들 수 있다.
A쿼리는 응답 시간이 1초이고 10번 실행되지만, 쿼리 B는 응답 시간이 0.1초고 1000번 실행된다 가정한다. 이에 대해 A쿼리가 응답시간이 훨씬 느리지만, 쿼리 B가 10배 많은 시간이 소요된다. 즉 10초 대 초 100초다. 쿼리 총시간으로 정렬된 쿼리프로파일에서 B가 가장 느린 쿼리가 된다.
2. 실행 시간 비율
실행 시간 비율은 쿼리 총시간을 실행 총시간으로 나눈 값이다. 쿼리 C의 쿼리 총시간이 321ms 이고 쿼리 D의 쿼리 총시간이 100ms 이면 실행 총시간은 421ms 다. 개별적으로, 쿼리 C는 실행 총시간의 76.2% 쿼리 D는 실행 총시간의 23.8% 다.
즉, MySQL은 쿼리를 실행하는데 총 421ms가 소요되었으며 그중 쿼리 C 실행에 76.2%를 할애 하였다. 이로서 정렬된 프로파일에서 C가 가장 느린 쿼리가 된다.
3. 쿼리 부하
쿼리부하는 쿼리 총시간을 클럭 타임을 나눈 것으로, 클럭 타임은 시간 범위에 대한 전체 초 수다. 시간 범위가 5분이면 클럭시간은 300초로 쿼리 E의 쿼리 총시간이 250.2초 라면, 부하는 250.2s/300s = 0.83이다. 쿼리 부하가 가장 큰 쿼리가 가장 느린 쿼리로 생각할 수 있다.
추가로, 쿼리 부하에서 동시성을 미묘하게 나타내기도 한다. 쿼리 부하가 평균적으로 1.0보다 작으면 쿼리가 동시에 실행되지 않는다. 쿼리 부하가 1.0보다 크면 쿼리 동시성을 나타낸다. 예시로 쿼리 부하가 3.5라면, 언제든지 조회하면 실행 중인 쿼리의 인스턴스가 3.5개 정도 잇다는 의미다.
즉, 쿼리 부하가 높을 수록 쿼리가 동일하거나 가까운 행에 접근할 때 경합할 가능성이 커진다.* 10을 넘는 쿼리 부하는 매우 느린쿼리로 가능성이 높지만 예외 상황이 있을 수 있다.
쿼리 보고서
쿼리 보고서는 하나의 쿼리에 대해 알아야할 모든 것을 보여준다. 위에서 서술한 쿼리 프로파일은 어떤 정보를 제공하는지()어떤 쿼리가 가장 느린지) 확인 할 수 있지만, 쿼리 보고서는 쿼리분석에서 사용되는 정보로 구성되어있다. 즉, 쿼리 실행을 이해하는데 도움이 되므로 정보가 많을 수록 좋다.
쿼리 보고서는 메트릭 도구에 따라 크게 달라진믄데 소스에서 비롯된 메트릭과 기본 통계값 혹은, 전체 보고서에서 쿼리 샘플 EXPLAIN 계획, 테이블 구조등의 메타데이터가 포함된다.
쿼리분석
쿼리 분석의 목표는 느린 응답 시간을 해결하는 것이 아니라 “쿼리 실행”을 이해하는 것이다. 쿼리실행은 서론에서 본론을 거쳐 결론으로 이어지는 하나의 이야기와 같다. MySQL에서 쿼리가 어떻게 실행되는지 이해하려면 쿼리를 최적화하는 방법을 이해할 수 있으니, 실행 계획을 어떻게 분석할 수 있는지 알아보도록 하자. *참고로 메트릭을 이해하는 것이 아니라 이를 통해 쿼리를 분석하는 것이 중요하다.
쿼리 메트릭
이전에 언급한 것처럼 쿼리 메트릭은 MySQL 배포 및 버전에 따라 다르다. 모든 쿼리 메트릭이 쿼리 실행을 이해하는데 도움이 되지만, 9가지 메트릭에 대해 살펴보고자 한다.
1. 쿼리시간
쿼리시간은 가장 중요한 메트릭이다. 쿼리시간은 또다른 메트릭인 잠금 시간이 포함되어 내제된 시간이다. 슬로 쿼리로그에는 InnoDB의 읽기 시간, 로우 락 대기 시간 대기열 시간을 나타내는 메트릭이 있는데, 잠금 시간은 의미가 있지만, 아쉽게도 슬로 쿼리 로그에서만 정확하다는 기술적인 문제가 있다.
성능 스키마를 사용하면 전부는 아니지만 쿼리 실행의 많은 부분을 알 수 있다.
MySQL의 메뉴얼에서는 성능 스키마로 수집되는 이벤트를 “시간이 소요되는 측정 정보를 수집할 수 있도록 구비된 서버가 수행하는 모든 작업”으로 정의하며, 방대한 수의 이벤트를 계측하여 다음과 같은 계층 구조로 수성된다.
트랜잭션 → 명령문 → 단계 → 대기
모든 쿼리가 트랜잭션 안에서 수행되므로 트랜잭션은 최상위 이벤트이며, 명령문은 쿼리 메트릭이 적용되는 쿼리다. 단계는 명령문 실행 과정 내의 단계로 명령구문분석 테이블 연ㄹ기, 파일 정렬 수행과 같은 과정을 포함한다. 여기서 대기는 시간이 걸리는 이벤트다.
(우리는 효율성을 추구하므로, 성능 스키마에 너무 집착할 필요는 없다. 쿼리 시간이면 충분하다. )
2. 잠금시간
잠금 시간은 쿼리를 실행하는 동안 잠금을 획득하여 사용하는 시간이다. 이상적으로 잠금시간은 쿼리 시간의 극히 일부지만, 값은 상대적이다. 비록 값이 상대적이지만 MySQL은 대기가 아니라 작업에 대부분의 시간을 사용해야하므로 잠금 시간이 쿼리 시간의 50프로 이상이라면 문제라고 생각할 수 있다.
** MySQL 스토리지 엔진과 데이터 잠금
MySQL 의 기본 스토리지 엔진은 InnoDB다. 해당 스토리지 엔진에서 잠금은 테이블락과 로우 락이 있다. 서버는 테이블과 테이블락을 관리하는데 스토리지 엔진으로 InnoDB로 생성되지만 스토리지 엔진에 구애받지는 않는다. 즉, 다른 스토리지 엔진으로 변환할 수 있다는 것이다. 로우 레벨 락은 MyISAM이 지원하지 않으므로 테이블락으로 데이터 접근을 관리해야한다. InnoDB는 로우레벨 락을 지원하므로 로우 락으로 데이터 접근을 관리한다.
서버에서 관리하는 메타데이터 락이 있는데, 스키마 +테이블+ 저장 프로그램등의 접근을 제어한다. 메타데이터락은 테이블 구조에 대한 접근을 제어하여 쿼리가테이블에 접근하는 동안 변경되는 것을 방지한다. 즉, 메타데이터락은 쿼리가 아니라 트랜잭션이 끝날 때 해제된다.
성능스키마의 잠금 시간에는 로우 락 대기가 포함되지 않고 테이블과 메타데이터 락 대기만을 포함한다. 로우 락 대기는 잠금시간에서 가장 중요한 부분인데 성능 스키마의 잠금 시간을 거의 사용하지 않게 된다. 즉, 성능 스키마의 잠금 시간에는 로우 락 대기가 포함되지 않는다.
잠금은 MySQl에서 가장 복잡한 부분이다.
장애 상황을 빠지는 것을 피하는 주요 5가지 사항은 자세히에 대해서는 미루고, 일단 아래 내용을 숙지하도록하자.
- innodb_lock_wait_timeout 시스템 변수가 각각의 로우 락에 적용되므로 잠금 시간은 이보다 상당히 클 수 있다.
- 잠금과 트랜잭션 격리 수준은 서로 관련되어 있다.
- InnoDB는 쓰지 않는 행을 포함하여 접근하는 모든 행을 잠근다.
- 잠금은 트랜잭션 커밋이나 롤백할 때 해제되며 때로는 쿼리 실행 중에도 해제된다.
- InnoDB에는 record, gap, next-key 등 다양한 유형의 잠금이 있다.
로우락에 대해서 이후에 자세히 다루지만 일단 시각화 하는 방버에 대해 알아보자.
쿼리 실행 중에 획득하고 해제되는 잠금의 순서다.
- 테이블에서 공유 메타데이터 락 획득
- 의도 배타적 테이블락 획득
- 행 1 락 획득
- 행 1 갱신
- 행 2 락 획득( 해제쓰지 않는 행에도 락 )
- 행 2 락 해제 ( 커밋 전 해제 )
- 행 3락 획득
- 행 3 갱신
- 커밋 트랜잭션
- 모든 락 해제
여기서 흥미로운 점이 있는데 행 2는 잠겨있다가 트랜잭션이 커밋되기 전에 갱신 없이 잠금이 해제된다. 이러한 일이 발생할 수는 있지만 항상 그런 것은 아니고 쿼리와 트랜잭션 격리 수준에 따라 다르다.
(현재는 로우 락)
3. 조회된 행
조회된 행은 MySQL이 쿼리 조건 절에 일치하는 행을 찾으려고 접근한 행의 수를 나타낸다. 이는 쿼리와 인덱스의 선택도를 나타낸다. 즉, 선택도가 높을 수록 행을 조회하는 낭비가 줄어든다.
이는 INSET를 제외하고 읽기와 쓰기에 적용된다.
4. 보낸 행
보낸 행은 클라이언트에 반환된 행의 수를 타나낸다. 보낸행을 조회된 행과 비교하면 의미가 더해진다.
보낸행 == 조회된 행
이상적인 경우로, 1000개 행이 있는 테이블에서 0.1 %에 접근하여 응답 시간이 허용가능하다면 이상적인 경우지만 이와달리 10,000개 행이 있는 테이블에서 10%에 접근하더라도 문제의 소지가 있다. 일반적으로 보낸행과 조회된 행이 같고 높다면, 쿼리가 테이블 스캔을 유발한다는 것을 의미한다. 일반적으로 성능면에서 매우 안좋은 상황임을 암시한다.
보낸행 < 조회된 행
쿼리나 인덱스의 선택도가 좋지 않다는 것을 알 수 있는 신뢰할 만한 신호다. 많은 행을 조회를 했지만 유의미한 행을 찾는데 불필요한 시간을 낭비했다는 증거이기도 하다.
보낸행 > 조회된 행
매우 드문 경우로 SELECT (id) FROM t2 는 COUNT(id) 값에 대해 1개 행을 보내지만 0개의 행을 조회할 수 있다.
5. 영향받은 행
영향 받은 행은 삽입, 갱신, 삭제된 행의 수를 나타낸다. 엔지니어는 해당 행에만 영향을 미치도록 주의해야한다. 영향받은 행을 확인하는 또 다른 방법은 대량 작업의 배치 크기다. 대량 INSERT 나 UPDATE, DELETE는 복제 지연, 변경 목록 길이, 잠금 시간, 전반적인 성능 저하와 같은 여러 문제를 야기한다. 그런데 “배치 크기는 얼마나 커야하나” 라는 질문도 보편적인 정답은 없다.
6. 설렉트 스캔
설렉트 스캔은 첫 번째로 접근한 테이블에서 수행한 전체 테이블 스캔 횟수를 나타낸다. 이는 쿼리가 인덱스를 사용하지 않는다는 것을 의미하므로 성능에 좋지 않다. 일반적으로 설렉트 스캔이 0이 아니면 쿼리 최적화를 강력하게 권장한다.
해당 값이 )이면 정말 좋은 상황이며 0이 아니라면 쿼리를 최적화해야한다.
7. 설렉트 풀 조인
설렉트 풀 조인은 조인된 테이블을 대상으로 전체 테이블을 스캔한 수를 나타낸다.
설렉트 풀 조인은 항상 0이어야한다.
8. 디스크에 생성된 임시 테이블
디스크에 생성된 임시 테이블의 수를 나타내는 지표로, 쿼리가 메모리에 임시 테입르을 만드는 것은 정상이다.그러나 메모리에 임시 테이블이 너무 커지면 MySQL은 임시 테이블을 디스크에 쓴다. 디스크 접근 속도가 메모리 보다 훨씬 느리므로 응답 시간에 영향을 미친다.
항상 쿼리를 먼저 최적화하고 시스템 변수는 최후의 수단으로 변경하도록 한다.
9. 쿼리 카운트
쿼리카운트는 쿼리 실행 횟수를 나타내고, 이 값이 매우 낮고 쿼리가 느리지 않는 한 기준ㅇ ㅣ없고 임의적이다. 즉, 낮고 느림인 경우 이상한 조합으로 조사해야한다.
메타 데이터와 애플리케이션
쿼리 메트릭보다 쿼리분석에 많이 사용하는 것이 바로 메타데이터다.
실제 EXPLAIN 계획과 각 테이블의 구조 이 2가지 메타데이터가 없으면 쿼리 분석을 완료할 수 없다.
상댓값
각 쿼리 메트릭에서 객관적, 긍정적으로 보는 유일한 값은 0이다. 0이 아닌 값은 항상 쿼리와 애플리케이션에서 상대적인데 보낸 행이 천개여도 괜찮지만 쿼리가 한 행만 반환해야할 때는 끔찍할 수 있다. 상댓값은 메트릭과 메타데이터, 애플리케이션과 같은 전체 이야기를 고려할 때 비로소 의미가 있다.
평균, 백분위수, 최대
쿼리 응답 시간을 단일 값인 것처럼 말하지만 그렇지 않다.
집계에서 쿼리가 메트릭을 그룹화하여 집계하는데 이는 단일 통계치로 출력된다. 즉, 정보를 외곡하거나 문제를 잘못 해석시킬 수 있다.
평균은 지나치게 낙관적이며, 백분위 수는 어디까지나 추정이고 최대는 최상의 표현이다.
쿼리 응답 시간 개선
쿼리 응답 시간을 개선하는 행위는 즉, 쿼리 최적화 작업이라고 볼 수있다. 이 작업은 직접과 간접 쿼리 최적화라는 두 내용으로 접근해야한다.
직접쿼리 최적화
직접 쿼리 최적화는 쿼리와 인덱스를 변경하는 것이다. 성능문제가 상당히 해결되어 매우 효과적인 여정은 종종 직접 쿼리 최적화에서 끝나기도 한다.
간접 쿼리 최적화
간접 쿼리 최적화는 데이터와 접근 패턴을 변경하는 것이다.
예시로 사용하지 않는 데이터를 지운다던지 TRUNCATE TABLE 과 같이 데이터 크기를 줄여 쿼리 응답 시간을 개선하는 것이다.
언제 쿼리를 최적화해야하는가?
느린 쿼리를 수정해도 또 느린 쿼리가 그 자리를 대신하기도한다.매번 쿼리를 최적화할 필요는 없다. 대신에 허용될만한 시간인지를 판단해보고 그렇지 않다면 쿼리를 계속 최적화한다.
즉, 허용할 만한 시간동안 쿼리가 돌아간다면 최적화 여정을 끝내도 되는 것이다.
그럼에도 불구하고 최적화를 위해 쿼리를 분석해야하는 세가지 경우를 따지자면 아래와 같다.
- 성능이 고객에게 영향을 미칠 때
- 코드 변경 전후
- 한 달에 한 번
'DataBase System' 카테고리의 다른 글
| 데이터 접근과 관리 (0) | 2024.07.15 |
|---|---|
| 인덱스 제대로 사용하기 위한 쿼리분석 (0) | 2024.07.13 |
| Index란? (0) | 2023.05.21 |
| 데이터 베이스 키(Key) (0) | 2023.05.21 |
| DBCP 란? (1) | 2023.05.21 |