지난 포스팅에서는 MySQL 실행계획의 cost라는 추상적인 값에 의거해, 가장 큰 cost를 지불하는 실행 구문을 찾아 해당 컬럼에 인덱스를 설정해서 병목을 해결해보았다.
하지만, 이는 정확한 근거를 토대로 해결을 한 것이 분명히 아니다. 데이터가 디스크에 어떻게 저장이 되고, 어떤 식으로 읽어오는지에 대한 이해와 이 과정에서 쿼리 튜닝을 통해 무엇을 해결 할 수 있는지를 알아야 정확한 근거에 기반한 쿼리 튜닝을 할 수 있을 것이다. 이번 포스팅은 이 근거 를 알기 위해, 쿼리 튜닝의 궁극적인 목적과 목적의 대상(I/O 병목)에 대한 이해를 해보고자 한다.
MySQL 데이터 저장 구조
DBMS는 일반적으로 블록이라는 단위로 데이터를 읽고 쓰게 된다.
한 블록에는 여러 개의 row가 저장될 수 있다. 몇 개의 row가 될 지는, 각 row마다 저장된 데이터의 크기에 따라 다르다.
MySQL은 InnoDB라는 DB 엔진을 사용한다. InnoDB 는 블록과 유사한 페이지라는 단위로 데이터를 읽고 쓰게 되는데, 기본적인 스펙은 다음과 같다.
페이지의 크기 : 기본 16KB(4KB, 8KB, 32KB, 64KB 등으로 설정 가능)익스텐트: 16KB 페이지 기준 1MB 크기, 총 64개의페이지를 그룹세그먼트: 데이터의 INSERT/DELETE에 따라 확장/축소가 이루어짐.익스텐트단위로 확장/축소가 이루어짐.테이블 스페이스: 여러세그먼트단위로 나뉘어져있는 공간- 테이블 데이터, 인덱스 데이터, 시스템 데이터 등을 저장
앞서 데이터는 페이지 단위로 읽고 쓴다고 했기 때문에, I/O 병목 또한 이 페이지 I/O에서 발생하게 된다.
페이지 I/O
페이지(혹은 블록) I/O는 크게 물리적 I/O, 논리적 I/O로 나뉜다.
- 논리적 I/O : DB 버퍼캐시에서 읽은 블록 I/O 횟수
- 물리적 I/O : 디스크에서 읽은 블록 I/O 횟수
기본적으로 디스크 I/O는 메모리 I/O에 비해 현저히 느리다. 따라서 디스크 I/O를 줄이는 것이 쿼리 튜닝의 목적이라고 할 수 있겠다.
❗ 여기서 짚고 넘어가야 하는 것 - 메모리 I/O와 디스크 I/O는 왜 속도 차이가 나는가? ❗
첫번째, 시스템 콜의 유무에서 차이가 발생한다.. 메모리는 프로세스에 할당이 되어있기 때문에 곧바로 메모리 주소에 접근해서 데이터를 가져오지만, 디스크에 있는 데이터에 접근하기 위해서는 파일 시스템을 거쳐야 한다. 이는 시스템 콜이 발생함을 의미하고 메모리 I/O에 비해 많은 오버헤드를 발생시킨다.
두번째, 하드웨어적인 특성에 따라 차이가 발생한다. 메모리는 전자 신호를 통해 데이터를 읽고 쓰지만, 디스크는 물리적으로 디스크를 회전시켜서 데이터를 읽기 때문에 속도에 차이가 발생한다.
버퍼 캐시(Buffer Pool)
그런데, DBMS는 데이터의 영속성을 위해 데이터를 모두 디스크에 저장하는 것이 아닌가? 그러면 물리적 I/O가 무수히 일어나는 비효율적인 구조가 아닌가? 라는 생각이 들 수 있다. 당연히 그런 구조였다면 정말 비효율적인 구조겠지만 일반적으로 DBMS는 Buffer Pool이라는 메모리 영역에 데이터를 캐싱해두고 I/O를 진행한다.
어떤 데이터에 대한 I/O 시스템 콜이 발생하면,
- 버퍼 캐시에서 해당 데이터가 있는
페이지를 탐색한다. - 버퍼 캐시에 해당
페이지가 존재할 경우 데이터를 작업 결과로 반환 -논리적 I/O - 버퍼 캐시에 데이터가 없을 경우 해당 데이터가 포함된
페이지를 디스크에서 탐색한다 -물리적 I/O
와 같은 절차로 데이터에 접근하게 된다. 이 논리적 I/O와 물리적 I/O의 비율에 대한 효율성 척도를 BCHR(버퍼 캐시 히트율)이라 하고, 다음과 같이 계산한다.
캐시 히트(Cache Hits): 버퍼 캐시에서 요청된 데이터를 성공적으로 찾은 횟수(논리적 I/O 횟수)
캐시 미스(Cache Misses): 버퍼 캐시에서 요청된 데이터를 찾지 못하고 디스크에서 읽어야 하는 횟수(물리적 I/O 횟수)
이를 토대로, 쿼리 튜닝의 목적인 물리적 I/O의 수는 다음과 같이 계산할 수 있다.
물리적 I/O = 논리적 I/O * (100% - BCHR)
물리적 I/O는 논리적 I/O의 수에 따라 결정되는 것을 알 수 있다. 나와 같은 백엔드 개발자가 코드 레벨에서 물리적 I/O를 줄이기 위해서는, 쿼리를 튜닝해서 접근하는 페이지(혹은 블록)의 절대적인 개수(논리적 I/O)를 줄이는 방법이 최선이다. 이를 통해 물리적 I/O를 줄이고 병목을 해결하는 것이다.
❗ 물리적인 I/O를 직접 줄이는 방법은 없나? ❗
버퍼 캐시의 용량을 늘리면 가능하다. MySQL 공식문서에 따르면, 버퍼 풀을 가능한 최대 크기로 설정하는 것이 좋은데, 이 경우 MySQL이 인 메모리 데이터베이스와 유사하게 작동할 수 있다고 한다.
어디까지나 이상적인 상황에 대한 설명이기 때문에, 서버 상황에 따라 사용자가 잘 판단해서 버퍼 풀을 설정해야할 것 같다. 과도하게 설정할 경우 과도한 페이징(메모리 관리 기법으로 앞서 설명한 페이지와는 다른 내용)으로 인한 오버헤드가 발생할 수도 있다고 한다.
결론
정리하면, 쿼리 튜닝을 통한 성능 개선은 쿼리 튜닝 -> 논리적 I/O 횟수 감소 -> 물리적 I/O 횟수 감소 -> 시스템 콜 횟수 감소 -> 프로세스의 대기 시간 감소 -> 성능 개선 이 된다.
다음 포스팅에서는 대표적인 개선 방법 인덱스와 함께, 어떻게 개선하는지를 알아보고자 한다.
참고
