영암사는 승경이네

개요

슬로우 쿼리 개선을 위해 스프링 부트 환경에서 AOP 기반으로 슬로우 쿼리 로그를 남기는 인터셉터를 구현했다. 다만, JPA 환경이냐 Mybatis 환경이냐에 따라 다른 방식으로의 구현이 필요했고, Interceptor 타겟을 설정하는 부분에 따라 로깅을 남기는 구조였기 때문에 '정말 모든 쿼리를 다 체크하고 있을까?' 라는 의문이 들었다.

 그러던 중 Real MySQL의 슬로우 쿼리 관련 챕터를 읽게 되었고, MySQL 자체에 슬로우 쿼리 관련 설정이 있다는 것을 알게 되었다. 이 설정들을 적용해보고, 어떤 형태로 슬로우 쿼리가 추출되는지 확인해보았다.

MySQL 슬로우 쿼리 로깅 활성화

먼저 MySQL에 접속 후 여러 옵션값들을 확인하고 설정해야한다.

1) slow_query_log

슬로우 쿼리 로깅 활성화 여부를 나타내는 설정값이다. 기본값은 OFF이므로 ON 또는 1로 설정한다.

show variables like '%slow_query_log%'; // 슬로우쿼리 활성화 여부 확인

set global slow_query_log = 'ON'; // 슬로우쿼리 활성화

2) long_query_time

슬로우 쿼리에 대한 기준 시간을 나타내는 설정값이다. 기본값은 10초로 되어있어 1초로 수정하였다.

show variables like '%long_query_time%'; // 슬로우 쿼리 기준 시간 확인

set global long_query_time = 1; // 슬로우 쿼리 기준 시간을 1초로 변경

set global 명령어를 통해 변수들을 설정할 경우 MySQL이 재시작될 때 설정값들이 초기화된다. 영구 설정을 하고자하면 my.ini나 my.cnf 파일을 다음과 같이 수정하면 된다.

// my.ini 파일
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

3) log_output

슬로우 쿼리를 남길 수 있는 방법은 두 가지로, 테이블 혹은 디스크이다. 온프레미스 환경에서는 디스크에 저장하는 것을 선호한다. 테이블로 설정할 경우 슬로우 쿼리가 발생할때마다 쓰기 작업이 발생하며, 데이터가 많아질 경우 부하가 발생할 수 있기 때문인데, 이를 방지하기 위해 슬로우 쿼리에 적재된 데이터를 주기적으로 삭제해주는 작업이 필요하다.

 AWS RDS 환경일 경우 파일 시스템에 직접 접근하지 못하므로 테이블에 저장하기도 한다. 참고로, 디스크로 설정한 후 RDS 설정에 SlowQuery Logging 을 활성화하면 Cloud Watch 의 로그 그룹에 저장된다. 필자의 경우 테이블에 적재되도록 설정하였다.

show variables like '%log_output%'; // 슬로우 쿼리 저장 타입 확인 (TABLE or FILE)

set global log_output = 'TABLE'; // 슬로우 쿼리를 테이블에 저장하도록 설정

슬로우 쿼리 테이블 확인

슬로우 쿼리 테이블은 mysql.slow_log 이다. 아래 명령어를 사용해 데이터가 쌓였는지 확인해보자. 아마 비어있을건데, 다음은 슬로우 쿼리를 직접 발생시켜 적재되는지를 확인해볼 것이다.

select * from mysql.slow_log order by start_time desc;

* rows_examined 값이 매우 크면 인덱스가 제대로 안 쓰이고 있을 가능성이 높다.

슬로우 쿼리 발생시키기

슬로우 쿼리가 발생했을 때, mysql.slow_log 테이블에 적재되는지 확인하기 위해 select sleep(2) 쿼리를 날려보자.

 만약 슬로우 쿼리에 로그가 남지 않는다면, 아까 설정했던 global 설정 값들이 현재 세션에는 적용되지 않은 상태일 가능성이 있다. 이 경우 재접속하면 된다.

select sleep(2);

select * from mysql.slow_log order by start_time

sql_text 값이 BLOB 형태로 출력된다면 아래 쿼리를 통해 조회하면 된다.

SELECT 
  start_time,
  user_host,
  CAST(sql_text AS CHAR) AS sql_text,
  query_time,
  lock_time,
  rows_sent,
  rows_examined
FROM mysql.slow_log;

1. 개요

 리두로그와 언두로그를 공부하려했더니 Buffer Pool 이라는 개념이 등장했다. 테이블을 메모리에 캐싱해두는 개념인데, 단순 개념만 알고있기에는 중요한 내용이기에 정리하였다.

2. InnoDB Buffer Pool이 뭐에요?

말 그대로 InnoDB 엔진에서 제공하는 버퍼 풀인데, 이녀석의 역할은 바로 캐싱이다.

테이블이나 인덱스를 캐싱한다고 한다. 그럼 정확히 어떤 형태로 캐싱을 하고, 실제 환경에서 어떻게 사용되는지 알아보자.

3. Buffer Pool 은 '페이지'들이 캐싱되어 있어요

 버퍼 풀에는 '페이지'들이 저장되어 있다. InnoDB 엔진에서의 페이지란 데이터베이스에서 데이터를 저장 단위이다. I/O를 통해 디스크로부터 페이지 단위로 데이터를 조회하면, Buffer Pool에 이를 그대로 저장한다. 페이지 하나의 크기는 16KB이다. (수정 가능하다)

 페이지들은 버퍼 풀에 차곡차곡 쌓이는게 아니다. 버퍼 풀은 데이터를 효율적으로 캐싱하기 위해 LRU 리스트, Flush 리스트, Free 리스트 라는 3개의 리스트로 구성되어 있으며, 이 중 LRU 리스트에 어떤 규칙에 따라 저장되게 된다.

3.1. Free 리스트

비어있는 페이지들의 목록이다. 사용자의 쿼리가 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다. 

3.2. Flush 리스트

디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(== 더티 페이지)들의 목록이다. 즉, 디스크에서 데이터 페이지를 읽은 후 변경이 가해진 데이터 페이지들의 목록이다. 데이터가 변경되면 해당 페이지는 Flush 리스트에서 관리됨과 동시에 변경 내용을 리두 로그에 기록하고, 버퍼 풀에도 변경 내용을 반영한다. 그 후 특정 시점에 리두 로그가 디스크로 기록된다.

3.3. LRU (+MRU) 리스트

MySQL InnoDB 에서는 변형된 LRU 방식을 채택하고 있다. 바로 LRU와 MRU(Most Recently Used)가 결합된 하이브리드 방식이다. 참고로 LRU와 MRU는 캐시 알고리즘이다.

* LRU (Least Recently Used)
가장 최근에 사용되지 않은 페이지를 교체하는 알고리즘. 가장 오래된 캐시를 제거한다.

* MRU (Most Recently Used)
가장 최근에 사용된 페이지를 교체하는 알고리즘. 가장 최근에 사용된 캐시를 제거한다.

4. 가장 오래된 페이지와 가장 최근의 페이지를 제거한다고??

위 두 캐시 알고리즘이 버퍼 풀에 적용되어 있는 구조이다. 이해가 잘 되지 않는다면 어떻게 동작하는지에 대한 메커니즘을 그려나가보면 이해할 수 있을것이다.

아래는 변형된 LRU, 즉 LRU와 MRU가 결합된 형태의 리스트이다.

InnoDB에서의 페이지 캐시 메커니즘에 의해 New 서브리스트 자주 사용되는 페이지가 저장되는 영역, Old 서브리스트는 신규 혹은 자주 사용되지 않는 페이지가 저장되는 영역으로 사용될것이다. 편의상 New 서브 리스트가 존재하는 영역을 MRU 영역, Old 서브 리스트가 존재하는 영역을 LRU 영역이라 표현하겠다.

긴말 필요없이 바로 동작 메커니즘을 살펴보자

1) 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사한다. [InnoDB 어댑티브 해시 인덱스 > 테이블 인덱스] 를 통해 페이지를 검색한다. 

* 어댑티브 해시 인덱스
B-Tree 의 성능 개선을 위해 InnoDB 에서 자동으로 추가하는 인덱스이다. 자주 읽히는 데이터 페이지의 키값을 이용해 해시 인덱스를 만들고, 필요할 때마다 해당 데이터 페이지로 즉시 찾아갈 수 있게 한다.
- LRU/MRU를 설명하는 글이므로 자세히 다루지 않겠다.

2) 버퍼 풀에 데이터 페이지가 있다면 MRU 영역 방향으로 승급한다. (MRU 리스트로 들어가는게 아닌 방향으로 승급이다.)

3) 버퍼 풀에 데이터가 없다면 디스크로부터 필요한 데이터 페이지를 버퍼 풀에 적재와 동시에 LRU 영역 Head 부분에 추가한다.

4) LRU 영역에 추가한 데이터 페이지가 조회되면 MRU 영역 방향으로 승급하고, 버퍼풀에 없다면 LRU Head 부분에 추가하는 과정을 반복한다.

5) 이때, 승급하는 데이터 페이지가 LRU 영역의 Head에 위치했을 경우, 바로 위인 MRU 영역의 Tail이 아닌 MRU 영역의 Head로 이동한다. (이부분이 핵심이다!)

6) 1,2,3,4,5 과정이 반복되면서 자주 사용되지 않는 데이터 페이지들은 자연스럽게 LRU 영역의 Tail 쪽으로 밀려난다. 끝까지 밀려난 데이터 페이지들은 버퍼 풀에서 제거된다.

7) 자주 사용되는 데이터 페이지의 경우 성능 향상을 위해 페이지의 인덱스 키를 '어댑티브 해시 인덱스'에 추가한다.

5. LRU, 오래된 데이터가 제거되는 것은 이해가는데.. MRU는?

LRU 알고리즘이 적용됐다는 것은 이해가 갈것이다. 그럼 최근에 사용된 데이터가 먼저 삭제된다는 MRU는 대체 어디에 적용된 것일까? 데이터 레코드를 버퍼 풀로 읽어오는 과정을 직접 그려보며 '최근 사용된 데이터가 먼저 삭제'되는 MRU 부분을 이해해보자.

1) 1시 30분, 디스크로부터 A 데이터 페이지를 읽어 버퍼 풀에 적재한다. 최초 버퍼 풀 적재시에는 LRU 영역 Head 부분에 저장된다.

2) 1시 32분, 디스크로부터 B 데이터 페이지를 읽어 버퍼 풀에 적재한다. LRU 영역 Head 부분에 저장됨과 동시에 기존 A 데이터 페이지는 Tail 쪽으로 밀려난다.

3) 2시 0분, 디스크로부터 C 데이터 페이지를 읽어 버퍼 풀에 적재한다. LRU 영역 Head 부분에 저장됨과 동시에 A, B 데이터 페이지는 Tail 쪽으로 밀려난다.

4) 2시 10분, 드디어 C 데이터 페이지를 찾는 쿼리가 실행됐고, C 데이터 페이지를 버퍼 풀에서 로드하게 된다. 이때 C 데이터 페이지는 LRU 영역의 Head에 위치했기 때문에 MRU 영역의 Head로 이동하게 된다.

5) 2시 20분, 디스크로부터 D 데이터 페이지를 읽어 버퍼 풀에 적재한다. LRU 영역 Head 부분에 저장됨과 동시에 A, B 데이터 페이지는 Tail 쪽으로 밀려난다.

6) 계속해서 신규 쿼리가 들어옴에 따라 디스크로부터 데이터 페이지를 읽어오게 되었다. E, F, G... X, Y, Z 까지, 다양한 데이터 페이지를 디스크로부터 로드하다보니 5시 30분이 되었다.

6. 최신의 데이터가 먼저 제거된다!

2시 10분에 저장한 C 데이터 페이지보다 최근에 사용했었던 E, F, G, H... 들의 데이터 페이지가 버퍼 풀에서 먼저 제거되었다. MRU 영역에서도 자주 사용되지 않은 데이터 페이지들은 Tail 쪽으로 밀려난다. 단, MRU 영역에 있는 C 데이터 페이지가 삭제되지 않은 이유는, 다른 데이터 페이지들이 여러번 재사용되지 않았고, 이로 인해 MRU 영역에서의 '경쟁'이 발생하지 않았기 때문이다. 

 '최근 사용된 데이터인 E,F,G,H 등의 데이터 페이지들이 먼저 삭제'된다는 점에서 MRU 알고리즘이 적용되었음을 알 수 있다.

7. 정리하면... InnoDB의 LRU + MRU 버퍼 풀

 InnoDB 버퍼 풀은 하이브리드 방식을 사용함으로써 단순 오래된 데이터 페이지 뿐 아니라, 자주 사용되지 않은 최신의 데이터 페이지들도 제거하고 있다. 만약 MRU 알고리즘이 적용되지 않았다면 어떨까? 최근 액세스한 데이터 페이지 들 중 자주 사용되지 않아 사실상 필요없는 데이터 페이지가 메모리에서 공간만 차지할것이고, 버퍼 풀이 가득 차서야 제거될것이다. 캐시 메모리를 최대한 활용하기 위해서는 자주 사용하는 데이터를 많이 저장하고, 최신의 데이터라할지라도 자주 사용하지 않은 데이터는 제거하는 것이 좋지 않겠는가?!

오늘은 InnoDB Buffer Pool에 대한 기본 구조와 InnoDB의 LRU + MRU 리스트에 대해 알아보았다. 다음은 리두 로그와 언두 로그에 대해 공부해보도록 하겠다!