MySQL 쿼리 분석 part 1 - 성능 스키마의 9가지 필수 쿼리 메트릭

쿼리 분석(query analysis)의 목표는 느린 응답 시간의 해결이 아닌 ‘쿼리 실행’을 이해하려는 것이다.

느린 응답 시간을 해결하는 행위는 쿼리 분석 후 쿼리 최적화 과정에서 이루어진다.

성능 스키마(performance schema)는 다음의 9가지 필수 쿼리 메트릭을 제공한다.

 

1. 쿼리 시간

• 쿼리 시간(query time)은 가장 중요한 메트릭이다. 쿼리 시간에는 또 다른 메트릭인 잠금 시간(lock time)이 포함된다.
• MySQL에서는 성능 스키마로 수집되는 이벤트를 “시간이 소요되는 측정 정보를 수집할 수 있도록 구비된, 서버가 수행하는 모든 작업”으로 정의하며, 다음의 계층 구조로 구성된다.

hierarchy of event

‘트랜잭션’은 모든 쿼리가 내부에서 수행되는 최상위 이벤트이다.

‘명령문’은 쿼리 메트릭이 적용되는 쿼리이다.

‘단계’는 명령문 실행 과정 내의 단계로, 구문 분석, 테이블 열기, 파일 정렬 수행 등의 과정을 포함한다.

‘대기’는 시간이 걸리는 이벤트이다.

 

2. 잠금 시간

잠금 시간은 쿼리를 실행하는 동안 잠금을 획득하여 사용한 시간이다.

• 잠금 시간은 쿼리 시간의 일부여야 하지만, 값은 상대적이다.
• 잠금 시간이 쿼리 시간의 50%이상이면 문제라고 볼 수 있다.

MySQL에는 많은 스토리지 엔진이 있지만 기본은 InnoDB이다.

InnoDB 스토리지 엔진에서 잠금에는 테이블 락(table lock)과 로우 락(row lock)이 있다. 서버(MySQL)는 테이블과 테이블 락을 관리하며, 로우 레벨 락(row-level lock)은 잠금이 지원되는 스토리지 엔진이 관리한다.

테이블 락과 로우 락은 테이블 데이터에 대한 접근을 제어하지만, 메타데이터 락(metadata lock)은 테이블 구조에 대한 접근을 제어하여 쿼리가 테이블에 접근하는 동안 변경되는 것을 방지한다.

메타데이터 락은 접근하는 모든 테이블을 대상으로 쿼리가 획득하며, 이는 쿼리가 아니라 트랜잭션이 끝날 때 해제된다.

• 성능 스키만의 잠금 시간에는 로우 락 대기가 포함되지 않고 테이블과 메타데이터 락 대기만을 포함한다.
• 대조적으로 슬로 쿼리 로그의 잠금 시간에는 메타데이터, 테이블과 행과 같은 모든 락 대기가 포함된다.

로우 락을 획득하는 데 필요한 시간은 동시성(concurrency)에 따라 다르다. 즉, 얼마나 많은 쿼리가 같은 행에 동시에 접근하는지에 따라 다르다.

행에 대한 동시성이 0이면 잠금 시간이 거의 없다.

• 다음 SQL 문에서 SELECT는 테이블 s의 공유 로우 락(shared row lock)을 획득한다.

INSERT...SELECT FROM s
REPLACE...SELECT FROM s
UPDATE...WHERE...(SELECT FROM s)
CREATE TABLE...SELECT FROM s

잠금 읽기, 특히 SELECT…FOR UPDATE는 확장이 어렵고 문제를 일으키는 경향이 있으며, 일반적으로 같은 결과를 얻을 수 있는 비잠금 해결책이 있기 때문에 피해야 한다.

• 비잠금 읽기(nonclocking read)는 로우 락을 획득하지 못하더라도 메타데이터 락과 테이블 락을 획득하므로 잠금 시간은 0이 아니다. 그러나 이 2가지를 획득하는 것은 1ms 미만으로 매우 빨라야 한다.

ALTER TABLE은 베타 메타데이터 락을 획득하는 일반적인 작업이다.

테이블 교체는 매우 빠르지만 배타 메타데이터 락이 유지되는 동안 모든 테이블 접근이 차단되므로 MySQL에 부하가 많을 때는 중단 현상이 눈에 띄게 발생할 수 있다.

• 다음은 MySQL에서의 잠금에 핵심이다.

innodb_lock_wait_timeout 시스템 변수가 각각의 로우에 적용되므로 잠금 시간은 이보다 상당히 클 수 있다.

잠금과 트랜잭션 격리 수준은 서로 관련되어 있다.

InnoDB는 쓰지 않는 행을 포함하여 접근하는 모든 행을 잠근다.

잠금은 트랜잭션 커밋이나 롤백할 때 해제되며 때로는 쿼리 실행 중에도 해제된다.

InnoDB에는 record, gap, next-key 등 다양한 유형의 잠금이 있다.

lock time when query is excuted

• 상기의 그림에 2가지 특이점이 있다.

성능 스키마의 잠금 시간에는 레이블 1, 2만 포함된다. 슬로 쿼리 로그에는 레이블 1, 2, 3, 5,와 7이 포함된다.

행 2는 잠겨있다가(5) 트랜잭션이 커밋되기 전에(9) 갱신(쓰기) 없이 잠금이 해제된다(6). 이런 일이 발생할 수 있지만 항상 그런 것은 아니며 쿼리와 트랜잭션 격리 수준에 따라 다르다.

 

3. 조회된 행

• 조회된 행(rows examined)은 MySQL이 쿼리 조건 절에 일치하는 행을 찾으려고 접근한 행의 수를 나타내며, 쿼리와 인덱스의 선택도를 나타낸다.

선택도(selectivity)는 데이터 세트에서 특정 값을 얼마나 잘 골라낼 수 있는지에 대한 지표이다.

• 쿼리와 인덱스의 선택도가 높을수록 MySQL이 일치하지 않는 행을 조회하는 데 낭비하는 시간이 줄어든다.
• 이것은 INSERT…SELECT 문이 아닌 한 INSERT를 제외하고 읽기와 쓰기에 적용된다.
• 예를 들면, SELECT의 WHERE 절에 id를 포함하여 조회하는지 안 하는 지에 따른 조회된 행의 수를 보면 쉽게 파악이 가능하다.

 

4. 보낸 행

보낸 행(rows sent)은 클라이언트에 반환된 행의 수를 나타낸다.

1) 보낸 행 = 조회된 행

• 이상적인 경우는 보낸 행과 조회된 행이 같고, 전체 행의 백분율로 계산할 시 상대적으로 값이 작고, 허용할 수 있는 쿼리 응답 시간일 때이다.

2) 보낸 행 < 조회된 행

• 조회된 행보다 보낸 행이 작다는 것은 쿼리나 인덱스의 선택도가 좋지 않다는 신뢰할 만한 신호이다.

3) 보낸 행 > 조회된 행

• 조회된 행보다 보낸 행이 큰 것이 가능하지만 이것은 드문 경우이다.

 

5. 영향받은 행

• 영향받은 행(rows affected)은 삽입, 갱신, 삭제된 행의 수를 나타낸다.
• 엔지니어는 해당하는 행에만 영향을 미치도록 주의해야 하며, 이의 반대의 경우 심각한 버그가 발생한다.
• 의도한 행보다 더 많은 행이 영향을 받았다는 것은 새로운 쿼리가 만들어졌거나 기존 쿼리가 수정되었다는 것을 의미한다.

 

6. 셀렉트 스캔

• 셀렉트 스캔(select scan)은 첫 번째로 접근한 테이블에서 수행한 전체 테이블 스캔 횟수를 나타낸다.
• 이는 쿼리가 인덱스를 사용하지 않는다는 것을 의미하므로 일반적으로 성능에 좋지 않다.
• 셀렉트 스캔은 모두 0이거나 모두 1일 가능성이 큰데, 0이면 정말 좋은 상황이며, 0이 아니면 쿼리를 최적화해야 한다.

 

7. 셀렉트 풀 조인

• 셀렉트 풀 조인(select full join)은 조인된 테이블을 대상으로 전체 테이블을 스캔한 수를 나타낸다.
• 이것은 셀렉트 스캔과 유사하지만 더 나쁘다. 이는 항상 0이어야 하며, 그렇지 않다면 쿼리 최적화를 해야 한다.
• 테이블 조인 순서는 쿼리가 아니라 MySQL이 결정한다. SELECT…FROM t1, t2, t3으로 조인을 하더라도, 실제 조인순서는 이와 다를 수 있다.

조인 순서를 확인하기 위해서는 항상 쿼리를 EXPLAIN하여 확인해야 한다.

• 쿼리 실행 중에 테이블에서 발생하는 풀 조인의 수가 이전 테이블 행의 곱과 같은 경우가 많아, 셀렉트 풀 조인은 셀렉트 스캔보다 일반적으로 더 나쁘다.

 

8. 디스크에 생성된 임시 테이블

• 디스크에 생성된 임시 테이블(created tmp disk tables)은 디스크에 생성된 임시 테이블의 수를 나타낸다.
• 쿼리가 메모리에 임시 테이블을 만드는 것은 정상이나 메모리에 임시 테이블이 너무 커지면 MySQL은 임시 테이블을 디스크에 쓴다.
• 디스크 접근 속도가 메모리보다 자릿수가 다를 정도로 훨씬 느리므로 응답 시간에 영향을 미친다.

 

9. 쿼리 카운트

• 쿼리 카운트(query count)는 실행 횟수를 나타낸다.
• 해당 값이 매우 낮고 쿼리가 느리지 않는 한 기준이 없고 임의적이다.
• ‘낮고 느림’인 경우 이상한 조합이므로 조사해야 한다.