Real MySQL 8.0 읽기 - 아키텍처

본 포스팅은 Real MySQL 8.0 개정판을 읽고 정리한 내용입니다. 잘못된 내용이 있다면 편하게 댓글로 지적 부탁드립니다. 감사합니다.

아키텍처

MySQL 아키텍처 구조

MySQL 아키텍처 구조는 크게 MySQL 엔진과 스토리지 엔진으로 구분된다.

1. MySQL 엔진
   • 커넥션 핸들러, SQL 파서 및 전처리기, 옵티마이저
2. 스토리지 엔진
   • 실제 데이터를 스토리지에 저장, 스토리지로부터 데이터를 읽어온다.
   • InnoDB와 MyISAM 스토리지 엔진이 존재하는데, 8.0 이후로부터 MyISAM 엔진은 거의 장점이 없어졌다. 따라서 앞으로는 InnoDB에 대해서만 언급한다.

MySQL에서는 요청을 어떻게 처리할까?

1. 가장 먼저 MySQL과 사용자가 커넥션을 맺는다. SQL문을 처리하고, SQL을 최적화한 후 스토리지 요청에 쓰기를 요청한다.
2. MySQL 엔진의 쿼리 실행기에서 데이터의 처리를 스토리지 엔진에 요청하는데, 이를 핸들러 요청이라고 하며, 이 때 사용되는 API를 핸들러 API라고 한다.

<aside> 💡 핸들러 API? MySQL 엔진은 데이터 읽기/쓰기를 제외한 대부분의 작업을 처리한다. 이 때, 스토리지 엔진의 기능을 활용하기 위한 API를 핸들러 API라고 한다.

</aside>

스레딩 구조

MySQL 서버는 스레딩 기반으로 작동하며, 포그라운드, 백그라운드 스레드로 구분된다. 스레드의 개수는 설정에 따라 가변적이며, 사용자의 요청을 처리하는 포그라운드 스레드는 일부이며 대부분은 백그라운드에서 여러 작업을 수행한다.

포그라운드 스레드

포그라운드 스레드는 접속한 클라이언트 개수만큼 존재하며, 각 클라이언트의 쿼리 문장을 처리한다.

• 스레드의 관리 : 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 스레드 캐시로 되돌아가는데, 스레드 개수가 지정 개수 이상이면 종료시킨다. 일반적인 스레드 풀의 관리 방식과 유사하다.
• 책임 : 포그라운드 스레드가 데이터 버퍼, 캐시까지 처리한 후 버퍼에서 디스크까지의 기록은 백그라운드 스레드에게 넘긴다.

백그라운드 스레드

백그라운드 스레드는 많은 역할을 담당하지만, 그 중 중요한 것은 로그 스레드, 쓰기 스레드이다. 읽기 작업은 응답성이 중요하므로 일반적으로 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 있다.

스레드 풀

스레드 풀은 사용자의 요청을 처리하는 스레드의 개수를 줄여 동시 처리되는 요청이 많더라도 제한된 개수의 스레드 처리에 집중할 수 있게 해 자원 소모를 줄이는 것이 목적이다.

메모리 구조

MySQL은 글로벌 메모리 영역과 로컬 메모리 영역으로 나뉜다.

글로벌 메모리 영역

클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당된다.

• 테이블 캐시
• InnoDB 버퍼풀
• InnoDB 어댑티브 해시 인덱스
• InnoDB 리두 로그 버퍼

로컬 메모리 영역

클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다. 클라이언트 스레드가 사용하는 공간이라고 클라이언트 메모리 영역이라고 부르기도 한다. 로컬 메모리는 스레드 간 독립적으로 사용되지만, 메모리 부족으로 멈출 수 있으므로 적절한 공간을 할당해야한다. 커넥션 버퍼, 결과 버퍼 등 공간은 커넥션이 열리면 할당되는 반면, 조인 버퍼, 소트 버퍼 등은 필요 시에 할당되며 미사용 시 해제된다.

쿼리 실행 구조

쿼리 실행은 MySQL 엔진에서 일어난다. 쿼리 파서 → 전처리기 → 옵티마이저(쿼리 변환) → 쿼리 실행 각 단계에 대해 살펴본다.

쿼리 파서

쿼리 파서는 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업이다. 기본적 문법 오류를 탐지하고, 오류 메시지를 전달할 수 있다. 컴파일러의 파서를 생각하면 이해가 쉽다.

전처리기

파서 과정에서 만들어진 파서 트리에서 구조적 문제점을 확인한다. 여기서 구조적 문제점이란, 토큰을 테이블 명, 컬럼명과 매핑하여 존재, 접근 권한 등을 확인하는 과정이다.

옵티마이저

사용자가 요청한 쿼리를 그대로 사용하는 것보다, 더 효율적으로 처리할 수 있는 방법을 찾는다. 옵티마이저가 더 나은 선택을 할 수 있도록 유도하는 방법도 존재하는데, 이 후에 천천히 다룰 예정이다.

실행 엔진

이 책에서는 옵티마이저를 경영진, 실행 엔진을 중간 관리자, 핸들러를 실무자로 비유한다.

1. 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정
2. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
3. 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
4. 읽어온 레코드를 임시 테이블에 저장하라고 핸들러에게 요청
5. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어오라고 핸들러에게 다시 요청
6. 최종적으로 실행 엔진이 결과를 다른 모듈로 전송한다.

위처럼, 만들어진 계획대로 핸들러에 요청하고 결과를 다른 핸들러의 요청에 입력으로 연결하는 ‘중간 관리자’의 역할을 수행한다.

쿼리 캐시

SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 즉시 결과를 반환한다. 그러나, 데이터가 변경될 때 관련된 데이터 모두를 무효화해야하므로 심각한 동시처리 성능 저하를 발생시켜, 쿼리 캐시는 삭제되었다.

InnoDB 구조

PK에 의한 클러스터링

모든 테이블은 기본적으로 PK를 기준으로 클러스터링되어 저장된다. 모든 세컨더리 인덱스는 레코드의 주소 대신 PK의 값을 논리적 주소로 사용한다. 그러므로 세컨더리 인덱스를 생성하더라도 PK를 거쳐 데이터 페이지에 접근하기 때문에 당연히 PK가 성능이 우수하며, PK가 세컨더리 인덱스에 비해 우선 순위가 높다.

외래 키 지원

외래 키는 운영 상의 불편함 때문에 서비스 DB에서는 사용하지 않는 경우가 많다. 외래 키 적용 시 변경이 생겼을 때 FK의 값이 존재하는지 체크하므로 잠금이 전파되고, 데드락이 발생될 때가 많으므로 주의가 필요하다. foreign_key_checks 옵션을 OFF로 변경해 체크 작업을 멈출 수 있지만 일관성이 깨져도됨을 의미하지는 않으므로 주의 깊은 사용이 필요하다.

MVCC

MVCC는 잠금을 사용하지 않는 일관된 읽기를 제공하기 위한 목적이고, 이는 InnoDB에서 언두 로그를 통해 구현된다. 일관된 읽기를 제공하기 위해, 트랜잭션 격리 수준에 따른 여러 Version으로부터 데이터를 읽어서 사용자에게 보여주게된다.

• Read Uncommitted : InnoDB 버퍼 풀이 현재 가지고 있는 데이터를 반환
• Read Committed : Undo Log의 데이터를 반환
• Repeatable Read : 자신의 Transaction ID보다 낮은 트랜잭션만 읽음

롤백이 발생하면 언두 로그의 데이터를 버퍼풀에 다시 복원하고, 언두 로그를 삭제한다. 커밋이 완료되면, 언두 로그를 필요로 하는 트랜잭션이 없을 때 언두 로그를 삭제한다.

MVCC를 통해 잠금이 없는 일관된 읽기가 가능해진다. Write와 Write 간에만 잠금이 발생하므로, write를 하지 않는 트랜잭션의 경우 어떠한 방해도 받지 않고 읽기를 할 수 있다는 의미이다.

자동 데드락 감지

InnoDB는 데드락을 방지하기 위해 잠금 대기 목록을 그래프로 관리한다. 데드락 감지 스레드가 주기적으로 데드락을 검사하는데, 트랜잭션의 언두 로그 양이 적은 것을 우선적으로 종료하여 MySQL의 롤백에 의한 부하를 줄이며 데드락을 방지한다.

InnoDB 버퍼 풀

1. 디스크의 데이터 파일, 인덱스 정보를 캐시
2. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 하는 버퍼 역할
3. 랜덤 디스크 I/O 횟수를 줄일 수 있다.

버퍼 풀의 크기는 일반적으로 메모리 공간의 50% 이상을 권장한다. 클라이언트 연결의 개수에 따라 추가적으로 레코드 버퍼의 메모리 공간이 많이 필요할 수 있다.

버퍼 풀의 구조

버퍼 풀은 거대한 메모리 공간을 페이지 단위로 쪼개어 관리한다. 페이지를 관리하기 위해 아래 3가지 자료 구조를 이용한다.

• LRU 리스트 : 실제로는 MRU와 LRU가 결합된 구조로, 자주 사용되는 데이터를 오래 유지하기 위한 목적
• 플러시 리스트 : 디스크로 동기화되지 않은 페이지를 관리하기 위한 목적으로, 변경이 발생하면 반드시 어느 시점에 디스크에 기록되어야 한다. 데이터가 변경되면, 리두 로그에 변경을 기록하고, 데이터 페이지에도 이를 반영한다. 스토리지 엔진은 체크 포인트를 발생시켜 주기적으로 리두 로그와 데이터 페이지를 동기화한다.
• 프리 리스트 : 실제 사용자 데이터로 채워지지 않은 빈 페이지의 목록

더티 페이지와 리두 로그의 관계

더티 페이지는 버퍼 풀에 무한정 머무를 수 없다. 데이터가 기록되면 리두 로그에 기록하고, 리두 로그 파일은 더티 데이터 페이지를 참조하게 된다. 이 때, LSN이 부여되어 로그 번호를 기록하는데, 체크 포인트 이벤트가 발생하면 특정 LSN까지의 모든 리두 로그와 더티 페이지는 디스크에 동기화된다. 가장 최근 체크포인트의 LSN와 마지막 리두 로그 엔트리의 차이를 체크포인트 에이지(활성 리두 로그 공간의 크기)라고 한다.

버퍼 풀의 절대적인 크기보다, 효과적인 쓰기 버퍼링을 위한 리두 로그 공간의 크기도 할당하는 것이 중요하다.

버퍼 풀 플러시

더티 페이지를 디스크에 동기화하는 것을 의미한다. 가장 중요한 것은 버퍼 풀 플러시 과정에서 디스크 기록의 폭증으로 사용자 쿼리 성능에 영향을 주지 않도록 해야한다. InnoDB 스토리지 엔진에서는 다음 두 가지 플러시를 백그라운드로 실행한다.

• 플러시 리스트 플러시
• LRU 리스트 플러시

플러시 리스트 플러시

리두 로그 공간의 재활용을 위해서는 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 한다. 리두 로그 공간을 비우기 위해서는 반드시 디스크 동기화가 필요한데, 이를 위해 주기적으로 플러시 리스트 함수를 호출해 오래된 데이터 페이지 순으로 동기화 작업을 수행한다. 많은 더티 페이지를 한 번에 디스크에 기록하냐에 따라 사용자 쿼리 처리에 주는 영향도가 달라진다. 참고로, 더티 페이지를 디스크로 동기화하는 스레드를 클리너 스레드라고 한다.

일반적으로 더티 페이지의 비율을 높게 가지고 있다가 한번에 디스크 쓰기를 하는 게 성능 상으로 좋다. 그러나, 버퍼 풀에 더티 페이지가 많을수록 디스크 쓰기 폭발 현상이 발생할 가능성이 높다. 이를 완화하기 위해 일정 비율 이상으로 더티페이지가 발생했을 때, 조금씩 더티 페이지를 디스크로 기록하게 한다.

어댑티브 플러시 기능도 제공하는데, 이는 리두 로그의 증가 속도를 분석하여 적절한 수준의 더티 페이지가 버퍼 풀에 남을 수 있도록 디스크 쓰기를 실행하는 알고리즘이다.

버퍼 풀 상태 백업 및 복구

서버가 셧다운 후 재실행될 때 쿼리 처리 성능이 굉장히 떨어진다. 이는, 버퍼 풀에 쿼리들이 사용할 데이터가 준비되어 있지 않기 때문인데, 셧다운 후 재시작하는 경우 강제 워밍업을 위해 주요 테이블과 인덱스에 대해 풀덤프 및 적재 기능이 도입되었다. 서버 재시작 전에 버퍼 풀의 상태를 백업하고, 서버 재시작 이후에 백업된 버퍼 풀의 상태를 복구할 수 있다.

Double Write Buffer

스토리지 엔진의 리두 로그는 변경된 내용만 기록하는데, 이 때 일부 내용만 기록되는 문제가 발생한다면 페이지를 복구할 수 없게 된다. 따라서 Double-Write 기법을 이용하는데, 더티 페이지를 우선 묶어서 버퍼에 보관한 후 비정상 종료가 발생하면 초기화 시에 Double write 버퍼와 데이터 파일의 페이지의 내용을 비교한다.

언두 로그

트랜젝션의 격리 수준을 보장하기 위해 변경 이전의 데이터를 임시 백업하는데, 이를 언두 로그라고 한다.

1. 트랜잭션 보장 : 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야하는데, 이때 언두 로그에 백업한 데이터를 이용해 복구한다.
2. 격리 수준 보장 : 데이터를 변경하는 도중에 다른 트랜잭션에서 읽기 접근을 하면, 언두 로그에 백업해둔 데이터를 읽어서 반환한다.

트랜잭션이 오랫동안 종료되지 않고 지속되면 언두로그가 계속해서 쌓이는데, 이 때 늘어난 디스크 할당량을 저절로 줄여주는 메커니즘이 8.0부터 적용되었다. 트랜잭션을 짧게 가져가서 언두로그가 많이 안쌓이도록 해야한다.

언두 로그 관리

언두 테이블 스페이스 공간을 정리하는 방법은 자동, 수동이 있다.

• 자동 모드 : 퍼지 스레드가 주기적으로 불필요한 언두 로그를 삭제한다.
• 수동 모드 : 테이블 스페이스를 비활성화한 후 불필요한 공간을 찾아 반납

체인지 버퍼

데이터의 변경이 발생할 때, 인덱스도 업데이트해야하는데, 버퍼 풀에 인덱스 페이지가 없다면 즉시 업데이트하지 않고 임시 공간에 두고 사용자에게 결과를 반환하는 형태로 성능을 향상시키는데, 이 공간이 체인지 버퍼이다.

임시 저장된 인덱스 레코드 조각은 이후 체인지 버퍼 머지 스레드에 의해 병합된다.

리두 로그

트랜잭션의 영속성과 가장 밀접한 연관을 지닌다. 서버가 비정상 종료되었을 때, 데이터 파일에 기록되지 못한 데이터를 복구할 수 있게하는 안전 장치이다.

1. 커밋되었지만 데이터 파일에 기록되지 않은 데이터

리두 로그에 저장된 데이터를 데이터 파일에 복사한다.

1. 롤백됐지만 데이터 파일에 이미 기록된 데이터

리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록하도록 설정하는 것을 권장한다. 그렇게 되어야 비정상 종료가 일어날 때 직전 트랜잭션 내용이 모두 리두 로그에 존재하여 복구가 가능하다. 이는 많은 디스크 I/O가 필요하고, 따라서 어느 주기로 동기화할지 결정하는 변수가 존재한다.

어댑티브 해시 인덱스

사용자가 수동으로 생성하는 인덱스가 아닌, InnoDB 스토리지 엔진에서 자주 요청하는 데이터에 대해서 자동으로 생성하는 인덱스이다. 어댑티브 해시 인덱스는 B-Tree 검색 시간을 줄여주기 위해 도입된 기능으로, 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 해시 인덱스를 검색해 데이터 페이지를 즉시 찾아갈 수 있다.

해시 인덱스는 인덱스 키 값과 데이터 페이지 주소 쌍으로 관리되는데, ‘B-tree 인덱스 Id + B-Tree 인덱스 키 값’의 조합으로 생성된다. B-Tree 인덱스 고유 번호가 포함되는 이유는 어댑티브 해시 인덱스는 하나만 존재하기 때문이다. 즉, 모든 해시 인덱스가 하나의 해시 인덱스에 저장되며, 특정 키 값이 어느 인덱스에 속한 것인지 구분해야한다. 이 때, 데이터 페이지의 메모리 주소는 버퍼 풀에 로딩된 페이지 주소를 의미한다. 즉, 버퍼 풀에 있는 데이터 페이지만 관리하고, 버퍼 풀에서 데이터 페이지가 없어지면 해시 인덱스에서도 해당 페이지 정보가 사라진다.

어댑티브 해시 인덱스는 다음 경우에 도움이 된다.

• 디스크 데이터가 버퍼 풀의 크기와 비슷한 경우
• 동등 조건 검색이 많은 경우
• 쿼리가 데이터 중에서 일부 데이터에 집중되는 경우

그러나, 추가 메모리 공간이 필요하며, 디스크에서 읽어오는 경우는 도움이 되지 않는다. 뿐만 아니라 삭제 시에 해시 인덱스를 스캔하여 제거하기 때문에 CPU 자원을 많이 소모한다.