Real MySQL 8.0 4장

김재연·2025년 7월 6일

구조

MySql 의 전체 구조는 아래와 같습니다.

전체 구조를 크게 MySQL 엔진, 스토리지 엔진으로 나눌 수 있고, 실질적으로 디스크와 운영체제로 나눌 수 있습니다.

그리고 MySQL 엔진과 스토리지 엔진을 합쳐서 MySQL 서버라고 표현할 수 있습니다.

MySQL 은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 헨들러와 SQL 인터페이스, SQL 파서, SQL 옵티마이저, 캐시 & 버퍼로 나눌 수 있으며, SQL 이 실행될 수 있도록 혹은 최적화를 할 수 있도록 하게끔 해주는 MySQL 엔진이 있고

이러한 엔진과 다양한 스토리지 엔진 API 를 활용하여 현재의 MySQL 을 이루고 있습니다.

이들을 간단하게 표현하면 MySQL 엔진은 DBMS 의 두뇌 역할을 하는 것이고, 스토리지 엔진은 실질적으로 움직이는 신체 (데이터에 접근) 라고 생각하면 될 것 같습니다.

핸들러

MySQL 엔진과 스토리지 엔진 사이를 연결해주는 것이 핸들러 API 라고 생각하시면 됩니다.

각 핸들러 API 를 통해 얼마나 많은 데이터 작업이 있었는지는 아래의 사진과 같이 확인이 가능합니다.

스레드

MySQL 의 스레딩 모델은 아래와 같습니다.

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 동작합니다.

그리고 크게 포그라운드, 백그라운드 스레드로 구분할 수 있습니다.

이 때, 작동하는 스레드의 목록을 확인하기 위해서 아래와 같이 할 수 있습니다.

이 때 실제로 사용자의 요청을 실행하는 스레드는 thread/sql_one_connection(포그라운드 스레드) 스레드 하나 입니다.

스레드는 서버에 접속된 클라이언트의 수만큼 존재하며, 스레드 캐시를 통해서 이를 효율적으로 관리합니다.

thread_cache_size 에 설정된 값 이상의 thread 가 cache 존재하게 되면 삭제를 진행해줍니다.

또한, InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 마치 쓰기 지연과 같은 행위는 백그라운드 스레드가 처리합니다.

이에 반해 MyISAM 은 모두 포그라운드 스레드가 진행하게 되죠.

쓰기 지연을 하지 않는 겁니다.

메모리 할당

MySQL 의 메모리 사용 및 할당 구조는 아래와 같습니다.

글로벌 메모리 영역은 스레드의 수와 무관하게 하나의 메모리 공간만 할당되게 됩니다.

물론, 필요에 따라 2개 이상의 메모리 공간을 할당 받을 수도 있지만, 이는 클라이언트의 스레드 수와는 무관하다는 점만 인지하면 될 것 같습니다.

그리고, 당연하게도 해당 영역은 모든 스레드에 의해 공유되게 됩니다.

로컬 메모리 영역은 이와는 반대로 클라이언트의 스레드 수와 연관이 있고, 서로의 공간을 공유하지 않는다는 특징을 가지고 있습니다.

이 때, 너무 많은 메모리를 차지해 MySQL 에 서버에 장애를 일으키지 않도록, 효율적으로 메모리를 할당하는 알고리즘이 탑재되어 있습니다.

플러그인 모델

아래 그림과 같이MySQL 서버에 다양하게 플러그인, 컴포넌트, 스토리지 엔진 등을 붙여서 커스터마이징 가능합니다.

그리고 아래의 사진에서 보면 알 수 있듯이, 커스터 마이징하는 부분은 실제로 데이터를 읽기/쓰기 하는 부분임을 알 수 있고, 이는 DBMS 의 전체 기능이 아닌 일부분의 기능만을 수행하는 엔진을 작성하게 되어 난이도를 낮추고, 커스터마이징을 하더라도 안정성을 어느정도 보장할 수 있게 됩니다.

스토리지 엔진의 종류

엔진의 종류를 살펴보기에 앞서 우리가 이 책을 학습하면서 얻어가야 할 궁극적인 목표가 무엇인지에 대해서 짚고 넘어가야 합니다.

책에서는 "하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다." 라고 표현합니다.

이를 알면 성능 최적화, 커스터 마이징등 도 수월할테니 당연히 맞는 말입니다.

쩄든, 이어서 스토리지 엔진의 종류에 대해서 살펴봅시다.

플러그인도 아래와 같이 확인할 수 있습니다.

기본적으로 적용되어 있는 스토리지 엔진이 아니고, 플러그인인 경우, 해당 파일들을 다운로드해서 MySQL 을 재빌드하면 적용할 수 있습니다.

컴포넌트

플러그인은 오직 MySQL 서버와 인터페이스 할 수 있고, 플러그인 끼리는 통신 할 수 없음
플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지않음 (캡슐화안됨)
플러그인은 상호 의존관계를 설정할 수 없어서 초기화가 어려움

컴포넌트는 이러한 플러그인의 단점을 보완해주기 위해서 등장하였습니다.

아래와 같이 컴포넌트를 적용할 수 있습니다.

쿼리 실행 구조

쿼리 파서는 말 그대로 쿼리를 파싱 해주는 역할을 합니다.

전처리기는 쿼리 파서가 만들어준 구조가 문제가 있나 확인하고

옵티마이저는 그림에 써져있는 것과 같은 동작을 실행합니다.

이제 쿼리 실행기가 핸들러 API 를 통해 스토리지 엔진을 실행하면 SQL 의 실행구조가 완성되는 것입니다.

이 때, 쿼리 캐시라는 개념을 사용할 수 있는데, 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제해야 했기 때문에, 심각한 동시 처리 성능 저하를 유발했고, MySQL 8.0 으로 올라오면서 완전히 사라졌다.

또 스레드 풀을 thread_pool_size 로 설정할 수 있고, 이는 CPU 코어의 개수만큼 설정하여 CPU 프로세서 친화도를 높이는 것이 좋습니다.

InnoDB 스토리지 엔진 아키텍쳐

InnoDB 는 엔진 중 거의 유일하게 레코드 락을 제공하기에, 높은 동시성 처리가 가능하며, 안정적이고 성능이 뛰어난 엔진입니다.

그래서 사람들이 InnoDB 스토리지 엔진을 많이 선택하죠

개략적인 구조는 아래와 같습니다.

프라이머리 키에 의한 클러스터링

InnoDB 의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장됩니다.

즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻입니다.

이러한 특징으로 인하 세컨더리 엔덱스들의 논리적인 주소 값으로 이를 활용합니다.

아무래도 실질적으로 디스크에 탑재된 공간을 의미하기에 프라이머리 키를 활용하여 탐색을 하면, 조회 속도에 큰 이점을 주게 됩니다.

외래키 지원

외래키는 테이블 관리를 조금 더 쉽게 만듭니다.

하지만, 데이터의 변경이 있을 때, 이에 대한 전파가 여러 테이블로 번지면서 일종의 교착 상태를 만들어 낼 수도 있습니다.

개발할 때 이를 주의해야하고

또한, 수동으로 데이터를 적재하거나 스키마 변경등의 관리 작업이 외래키 제약 조건으로 인해 실패할 수도 있습니다.

그래서 아래와 같은 기능을 제공합니다.

외래키 제약조건 감시를 잠깐 해지하는 것인데요, 물론 안정적인 구동을 위해서 다시 ON 으로 바꾸는 것을 잊어서는 안됩니다.

MVCC

개인적으로 제일 중요하다고 생각되는 부분입니다.

일반적으로 트랜잭션을 지원하는 DBMS 가 제공하는 기능입니다.

MVCC 의 가장 큰 목적은 잠금을 사용하지 않고, 일관된 읽기를 제공하는 데 있습니다.

아래 그림과 함께 살펴보시죠

Insert 이후 Update 을 때, 버퍼 풀과, 데이터 파일 및 언두 영역의 변화를 나타낸 것입니다.

이런 식으로 언두 로그를 남겨서, 변경 이전의 상태를 남겨놓고 이를 통해 잠금을 걸지 않고도, 다른 트랜잭션이 해당 데이터에 접근하였을 때에도, 트랜잭션 격리 레벨에 따라서 알맞은 데이터를 반환할 수 있는 것 입니다.

물론 언두 로그 영역이 너무 커지는 것을 막기 위해 트랜잭션을 빠르게 커밋 혹은 롤백해주어야 합니다.

장기간 트랜잭션을 잡고 있는다면 언두로그 영역이 너무 커져 시스템의 장애 혹은 성능 저하로 이어질 수 있습니다.

자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리합니다.

그리고 주기적으로 데드락 감지 스레드를 통해 주기적으로 해당 그래프를 탐색하여 교착 상태에 빠진 트랜잭션들을 찾아서 그 중 하나를 강제 종료합니다.

이 때 종료의 우선순위는 언두 로그의 양입니다.

언두 로그 레코드를 더 적게 가진 트랜잭션이 언두 처리를 해야 할 내용이 적다는 것이며, 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문입니다.

자동화된 장애 복구

장애를 복구 하기 위해서 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야합니다.

이 때 해당 값의 설정 가능 범위는 1 ~ 6 까지이며 이렇게 진행했음에도 MySQL 서버가 시작되지 않으면 백업을 이용해서 다시 구축하는 방법밖에 없습니다.

InnoDB 버퍼 풀

디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간입니다.

쓰기 작업을 지연시키는 아주 중요한 역할을 합니다.

InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있습니다.

또한 버퍼 풀 전체를 관리하는 잠금으로 인해 내부 잠금 경합을 많이 유발해왔는데, 이런 경합을 줄이기 위해 버퍼 풀을 여러개로 쪼갰는데요, 이 때 사용하는 시스템 변수는 innodb_buffer_pool_instances 입니다.

버퍼 풀의 구조

InnoDB 버퍼 풀은 크게 LRU 리스트, 플러시 리스트, 프리 리스트라는 3개의 자료구조로 관리됩니다.

프리 리스트는 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지 목록이며 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용됩니다.

LRU 리스트는 아래와 같은 형태를 띄고 있고, 엄밀하게 LRU, MRU 리스트가 결합된 상태라고 볼 수 있습니다.

그냥 MRU 는 최근에 쓴거고, LRU 는 최근에 안 쓴 것이라고 생각하시면 됩니다.

LRU, MRU 리스트를 사용하는 이유는 정말 필요한 페이지들을 버퍼 풀에 놓기 위해서이다.

그냥 이게 전부이다. 해당 자료구조 자체의 목적이

마지막으로 플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지 (더티 페이지)입니다.

데이터가 변경되면 InnoDB 는 변경 내용을 리두 로그 에 기록하고, 버퍼 풀의 데이터 페이지에도 변경 내용을 반영합니다.

리두 로그의 각 엔트리는 특정 데이터 페이지와 연결된다고 생각하시면 됩니다.

버퍼 풀과 리두로그

버퍼 풀과 리두 로그의 관계는 아래 사진과 같습니다.

간단하게 표현하면 클린 페이지 (디스크에서 읽은 상태로 전혀 변경되지 않은 페이지) 를 변경하게 되면 더티 페이지가 되고, 이 때 변경사항은 바로 디스크에 적용되지 않습니다.

그렇기 때문에 서버가 비정상적으로 종료되면 복구를 할 수가 없습니다.

그렇기 때문에 리두 로그에 지속적으로 쓰기 연산을 해주고, 각각의 로그 엔트리에는 버퍼 풀 내부 페이지의 변경사항과 매핑되게 됩니다.

그렇다면 과연 버퍼 풀과 리두 로그 파일은 무작정 크면 될까요?

2가지의 예시를 들어보겠습니다.

InnoDB 버퍼 풀은 100GB이며 리두 로그 파일의 전체 크기는 100MB인 경우
InnoDB 버퍼 풀은 100MB이며 리두 로그 파일의 전체 크기는 100GB인 경우

1번 같은 경우는 리두 로그 엔트리가 4KB 라면, 25600 개의 더티 페이지만 버퍼 풀에 보관할 수 있게 됩니다.

데이터 페이지가 16KB 라고 가정한다면 허용 가능한 전체 더티 페이지의 크기는 400MB 수준 밖에 안되는 것입니다.

2번도 비슷하지 않을까요?

즉, 서로의 상관관계가 중요한 것입니다.

버퍼 풀 플러시

MySQL 5.6 버전까지는 스토리지 더티 페이지 플러시 기능이 그다지 부드럽지 않았습니다.

이 때, 성능상의 악영향 없이 디스크에 동기화하기 위해 다음과 같이 2개의 플러시 기능을 백그라운드로 실행하게 됩니다.

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

플러시 리스트 플러시

리두 로그 엔트리가 사용하는 공간을 비우기 위해 오래전에 변경된 데이터 페이지를 순서대로 디스크에 동기화하는 작업을 진행합니다.

그냥 순서대로 하는 것이다.

디스크 쓰기 폭증

생각을 해보자. 더티 페이지는 비율이 높을 수록 좋다.

서비스에 영향이 가지 않게 저장될 데이터들을 모았다가 효율적으로 한번에 저장하겠다는 것이니까 말입니다.

하지만, 더티 페이지의 비율이 90% 를 넘어가게 되면 InnoDB 스토리지 엔진은 급작스랍게 더티페이지를 디스크로 기록해야 한다고 판단하여, 이 많은 것들을 한번에 써버리는 디스크 쓰기 폭증 현상이 발생하게 되는 것입니다.

이것을 막기 위해서 더티 페이지의 비율을 계속해서 낮게 유지하는 방식이 있으며, innodb_max_dirty_pages_lwm 라는 시스템 변수를 적절하게 설정하면 이를 행할 수 있습니다.

위와 달리 자동으로 설정하는 방법도 있다.

InnoDB 스토리지 엔진은 어댑티브 플러시라는 기능을 제공하고, 이는 리두 로그의 증가 폭을 읽고, 적정한 비율의 더티 페이지가 버퍼 풀에 유지될 수 있도록 쓰기 연산을 조금 조금씩 진행해주게 된다.

리두 로그의 공간이 10% 미만이면 작동하지 않다가, 10% 를 넘어서면 작동하게 된다.

LRU 리스트 플러시

이것은 이름에서부터 나온다.

그냥 사용한지 가장 오래된 더티페이지부터 플러시 하는 것이다.

버퍼 풀 상태 백업 및 복구

버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비되어 있다며 당연히 디스크에 접근할 필요가 없으니 성능이 향상될 것입니다.

하지만 서버를 재시작하면 이를 적용할 수 없죠.

그렇기 때문에, 아래와 같은 기능을 제공해, 백업된 버퍼 풀의 상태를 다시 복구할 수 있습니다.

백업은 굉장히 용량이 작다.

메타데이터만을 저장해놨기 때문인데, 이를 통해서 실제로 버퍼 풀에 기록하는 과정은 상당히 오랜 시간이 걸립니다.

그래서 아래와 같이 현재 진행상태를 알려줄 수 있는 기능을 제공합니다.

아래와 같이 멈출 수도 있습니다.

버퍼 풀의 적재 내용 확인

아래와 같이 확인할 수 있습니다.

이를 통해서 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있습니다.

Double Wirte Buffer

리두 로그 엔트리는 데이터 페이지의 변경사항만을 저장하고, 이 때문에, 더티 페이지를 디스크에 쓰기 연산을 하는 도중 끊길 경우 영원히 페이지의 상태를 정상적으로 돌릴 수 없을 수도 있기 때문에, 위와 같이 변경사항을 DoubleWrite 버퍼에다가 저장해놓고, 엔진이 재시작될 때, 항상 데이터 파일의 페이지들과 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 데이터 파일의 페이지로 해당 내용을 복사하게 됩니다.

언두 로그

언두 로그의 존재 목적은 아래와 같습니다.

트랜잭션 보장
격리 수준 보장

하지만, 이러한 좋은 친구가 예전에는 성능저하를 야기했었습니다.

예전의 언두 로그는 용량이 줄어들 수 없었습니다.

서버를 재시작하기까지 한번 증가된 언두로그의 영역은 줄어들지 않았습니다.

그렇기 때문에 대용량 데이터가 변경된 경우나, 아래와 같은 상황에서 굉장히 치명적이었습니다.

장시간 트랜잭션이 활성화 되었기 때문에 언두로그의 크기는 엄청나졌을 것이고, 또한 이로 인해서 조회 성능또한 격리 수준 보장을 위하여 굉장히 떨어질 것입니다.

하지만, 현재는 언두 로그의 공간을 줄여줄 수 있어, 이러한 점들을 해결하였지만, 여전히 트랜잭션이 장시간 유지되는 것은 성능상 좋지 않습니다.

언두 테이블 스페이스 관리

언두 로그는 이제 언두 슬롯에 저장되어, 이전과 같이 사용 공간을 너무 많이 차지해 성능 저하를 야기할 일은 사라졌습니다.

쩄든 언두 슬롯 하나가, Insert, Update, Delete, Create 등의 DML 을 감당하고 있다는 사실을 인지하고, 하나의 트랜잭션이 보통 2개의 언두 슬롯을 사용한다고 가정했을 때 테이블 스페이스 2개, 세그먼트 128개 16KB InnoDB 에서 131072 개의 트랜잭션을 동시에 감당 해낼 수 있습니다.

기가 막힙니다.

이렇게 트랜잭션에 맞게 언두 로그를 설정하여 성능을 최적화 시킬 수 있게 된 것입니다.

아래는 언두 테이블을 동적으로 추가하고 삭제하는 그림입니다.

언두 테이블을 수동으로 반환하는 방법입니다.

자동으로 반환하는 방법은 주기적으로 불필요해진 언두 로그를 퍼지 스레드가 탐지해서 반환해준다.

체인지 버퍼

인덱스를 업데이트 하는 작업은 굉장히 비용이 큰 작업이다.

그렇기 때문에 InnoDB 에 변경해야 할 인덱스 페이지가 있다면, 바로 작업을 진행하지 않고, 임시 영역에다가 놓는데, 이것을 체인지 버퍼라고 칭한다.

위는 순서대로 체인지 버퍼가 사용중인 메모리 크기와, 체인지 버퍼 관련 오퍼레이션 처리 횟수를 나타냅니다.

리두 로그와 로그 버퍼

디스크 쓰기 지연을 가능케 하기 위해서 리두 로그를 제공하고, 또한 리두 로그의 성능을 챙기기 위해 리두 버퍼를 사용합니다.

또한 리두 로그의 동기화 시기를 아래 그림과 같이 설정을 진행할 수 있습니다.

리두 로그 활성화 및 비활성화

리두 로그는 커밋이 일어나면 무조건 디스크에 쓰기 연산을 진행한다.

그렇기 떄문에, 성능 향상을 위해 데이터 복구, 대용량 데이터 삽입 등의 연산에서 리두 로그를 비활성화 할 수도 있다.

이런 식으로 활성화, 비활성을 판가름 할 수 있습니다.

어댑티브 해시 인덱스

어댑티브 해시 인덱스는 B-Tree 의 성능을 보완해주기 위해서 나왔다.

해시 인덱스는 B-Tree 인덱스의 고유 번호 -> B-Tree 인덱스의 실제 키 값

으로 매핑되어 있다.

이렇게 키 값으로 인덱스의 고유번호를 가지고 있는 이유는, 스토리지 엔진에서 어댑티브 해시 인덱스는 단 하나만 존재하기 때문이다.

해당 어댑티브 해시 인덱스로 인해서 데이터 처리는 오히려 많아졌지만, CPU 사용률이 더 낮아진 것을 확인할 수 있다.

옛날에는 어댑티브 해시 인덱스가 하나이기 때문에 인덱스 경합이 굉장히 심했는데, 마치 버퍼 풀의 내부 경합을 해결했던 것처럼, 어댑티브 해시 인덱스도 여러개를 이를 해결하였습니다.

또한 어댑티브 해시 인덱스가 가지는 단점 및 장점입니다.

아래 그림은

어댑티브 해시 인덱스가 활성화 되어 있는지 비활성화 되어 있는지 확인하는 방법입니다.

hash serach/s 의 값이 0으로 설정되어 있다면 비활성화 되어 있는 것입니다.

MyIsam 스토리지 엔진 아키텍쳐

Key_reads 는 인덱스에서 디스크에서 읽어 들인 횟수를 저장하는 상태 변수이며, Key_reads_requests는 키 캐시로부터 인덱스를 읽은 횟수를 저장하는 변수이다. 이 상태 값을 알아보려면 다음과 같이 Show Global Status 명령어를 사용하면 된다.

키 캐시 공간을 설정하는 방법이다.

데이터 파일과 프라이머리 키(인덱스) 구조

InnoDB 와 다르게 프라이머리 키와 세컨더리 키가 다르지 않다.

또한 프라이머리 키에 의한 클러스터링 없이 단순히 Insert 된 순으로 저장이 된다.

또한 ROWID 라는 물리적인 주소를 가지게 된다.

위 그림에서 보면 알 수 있듯, ROWID 에는 고정, 가변의 종류가 존재한다.

슬로우 쿼리 로그

슬로우 쿼리 로그는 쿼리 튜닝에 굉장히 도움이 된다.

long_query_time 시스템 변수에 기록한 시간 이상의 쿼리들이 모두 기록하게끔 하여, 개선해야 할 쿼리들의 목록을 볼 수 있는 것이다.

또 log_output 시스템 변수를 활용하여, 어떻게 출력할 것인지도 정할 수 있다.

실제로 슬로우 쿼리 로그는 아래와 같이 출력된다.

쿼리에 나온 각각의 항목들에 대한 의미이다.

주의 깊게 보아야 하는 부분은 Lock Time 부분에 대한 해석이다.

실제로 스토리지 엔진 부문의 Lock Time 만이 아닌 MySQL 엔진단의 Lock Time 까지 포함하기 때문에 분석에 큰 의미를 두지 않아도 된다.

해당 슬로우 쿼리를 Percona 에서 개발한 Percona Toolkit 의 pt-query-digest 스크립트를 이용하면 쉽게 분석할 수 있다.

슬로우 쿼리 통계

슬로우 쿼리 통계는 아래와 같다.

김재연

끊임없이 '성장'하는 개발자 김재연입니다.

이전 포스트

이벤트 기반 아키텍쳐

다음 포스트