[DB] MySQL 아키텍처 (feat. Real MySQL)

💡 본 문서는 MySQL의 기본서라 불리는 Real MySQL이라는 책을 바탕으로 'MySQL 아키텍처'에 대해 정리해놓은 글입니다.
실제로 쿼리가 들어왔을 때 MySQL 자체에서는 어떤식으로 처리를 하고 어떤식으로 구성되어 있는지를 정리해두었으니 참고 부탁드립니다.

1. MySQL의 전체 구조

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분해서 볼 수 있습니다. 여기서는 MySQL의 쿼리 파서나 옵티마이저 등과 같은 기능을 스토리지 엔진과 구분하고자 위의 그림에서는 "MySQL엔진"과 "스토리지 엔진"으로 구분했으며, 이 둘을 모두 합쳐서 그냥 MySQL 또는 MySQL 서버라고 표현합니다.

1.1 MySQL 엔진

클라이언트로부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL파서 및 전처리기, 쿼리의 최적화된 실행을
위한 옵티마이저가 중심을 이룹니다. 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS 두뇌에 해당하는 처리를 수행합니다. 그리고 성능 향상을 위해 MyISAM의 키 캐시나 InnoDB의 버퍼 풀과 같은 보조 저장소 기능이 포함돼 있습니다. 또한, MySQL은 표준 SQL(ANSI SQL-92) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있습니다.

1.2 스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이
전담합니다. MYSQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러개를 동시에 사용할 수 있습니다.
다음 예제와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후 테이블의 모든 읽기 작업이나 변경 작업은 정의된
스토리지 엔진이 처리합니다.

mysql > CREATE TABLE test_table (fd1 IT, fd2 INT) ENGINE = INNODB;

1.3 핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에게 쓰기 또는 읽기를 요청하는데, 이러한 요청을 핸들러(Handler) 요청이라고 하고, 여기서 사용되는 API를 핸들러 API라고 한다. InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고받는다. 이 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지는 다음의 명령어로 확인할 수 있습니다.

"SHOW GLOBAl STATUS LIKE 'Handler%';"

1.4 MySQL 스토리지 엔진 비교

대부분의 엔진들이 오라클 엔진을 기준으로 기능을 줄이거나 유사하게 구현하고 있습니다. 기능을 줄인 엔진은 그만큼 락 경합이나 데이터 처리시 부하를 줄이면서 대량 인서트나 메모리내의 조회로 성능을 높이고 있는 경향을 보이고 있습니다. MySQL 서버(mysqld)에서 지원되는 스토리지 엔진을 확인하는 방법은 'SHOW ENGINES'로 확인할 수 있으며, 다음의 표로 비교 정리하였습니다.

스토리지 엔진	버전	트랜잭션	LOCK 세분성	강점	약점
MyISAM	모든버전	지원 안함	동시 삽입 가능 테이블 락	SELECT, INSERT, 대량적재	읽기/쓰기 혼합 작업
MyISAM Merge	모든 버전	지원 안함	동시 삽입 가능 테이블 락	분할 보관, 데이터웨어하우징	전역적 테이블 검색
Memory(HEAP)	모든 버전	지원 안함	테이블 락	중간 계산, 통계 참조 데이터	대량 데이터셋, 지속적인 스토리지
InnoDB	모든 버전	지원	MVCC의 ROW락	트랜잭션 처리
Falcon	6.0 이상	지원	MVCC의 ROW 락	트랜잭션 처리
Archive	4.1 이상	지원 안함	ROW 락	로깅, 집계 분석	임의 접근(인덱싱), 갱신, 삭제
CSV	4.1 이상	지원 안함	테이블 락	로깅, 외부 데이터 대량 적재	임의 접근(인덱싱)
Bloackhole	4.1 이상	지원 안함	락 지원 안함	로깅, 복제 아카이브	특수 목적(로깅) 이외의 사용
Federated	5.0 이상	지원 안함	락 지원 안함	배포된 데이터 소스 관리	특수 목적(원격 데이터 조회) 이외의 사용
NDB 클러스터	5.0 이상	지원	ROW 락	고도의 가용성	일반적인 사용 어려움
PBXT	5.0 이상	지원	MVCC의 ROW 락	트랜잭션 처리, 로깅	클러스터 인덱스 지원 안함
SolidDB	5.0 이상	지원	MVCC의 ROW 락	트랜잭션 처리
Maria	6.X 이상	지원	MVCC의 ROW 락	MyISAM 엔진 대체

• 읽기 전용 테이블에서 굳이 트랜잭션이나 동시성을 요구할 필요는 없기때문에 그러한 것을 배제한 엔진을 선택하여 테이블을 활용하게 됩니다.
• MySQL 엔진들 중 가장 보편적으로 쓰이면서 오라클과 유사한 엔진은 InnoDB 엔진입니다.

2. MySQL 스레딩 구조

MySQL은 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.

2.1 포그라운드 스레드(클라이언트 스레드)

클라이언트가 서버에 접속하게 되면 MySQL 서버는 그 클라이언트의 요청을 처리해 줄 스레드를 생성해 그 클라이언트에게 할당해줍니다. 이 스레드는 DBMS의 앞단에서 사용자(클라이언트와) 통신하기 때문에 포그라운드 스레드라고 하며, 또한 사용자가 요청한 작업을 처리하기 떄문에 사용자 스레드라고도 합니다.

포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무입니다. 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread pool)로 되돌아갑니다. 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면, 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드 만 스레드 캐시에 존재하게 합니다.

이렇게 스레드의 개수를 일정하게 유지하게 만들어주는 파라미터가 thread_cache_size입니다. MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만, InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리합니다.

2.2 백그라운드 스레드

인서트 버퍼(Insert Buffer)를 병합하는 스레드, 로그를 디스크로 기록하는 스레드, InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드, 데이터를 버퍼로 읽어들이는 스레드, 그리고 기타 여러 가지 잠금이나 데드락을 모니터링 하는 스레드가 있습니다. 이러한 모든 스레드를 총괄하는 메인 스레드도 있습니다.

모두 중요한 역할이지만, 가장 중요한 것은 로그 스레드(Log thread)와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 쓰레드 입니다.

• 읽기 / 쓰기 쓰레드의 개수를 지정하는 파라미터 : innodb_write_io_threads / innodb_read_io_threads

SQL 처리 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연되면 안됩니다(사용자가 SELECT 쿼리를 실행했는데, "요청된 SELECT는 10분 뒤에 결과를 돌려주겠다"라고 응답을 보내는 DBMS는 사용하면 안됩니다). 그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있으며 InnoDB 또한 이러한 방식으로 처리합니다. 이러한 이유로 InnoDB에서는 INSERT와 UPDATE 그리고 DELETE 쿼리로 데이터가 변경되는 경우, 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 됩니다. 하지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없습니다.

3. 메모리 할당 및 사용구조

3.1 메모리 영역

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역 으로 구분할 수 있습니다. 글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 무조건 운영체제로부터 할당됩니다. 글로벌 메모리 영역과 로컬 메모리 역역의 차이는 MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 아닌지에 따라 구분되며 각각 다음과 같은 특성이 있습니다.

3.1.1 글로벌 메모리 영역

• 일반적으로 클라이언트 스레드의 수와 무관하게 일반적으로는 하나의 메모리 공간만 할당됩니다.
• 단, 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있지만 클라이언트의 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유됩니다.

3.1.2 로컬 메모리 영역

• MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역입니다.
• 클라이언트와 MySQL 서버와의 커넥션을 세션 이라고 하기 때문에 세션 메모리 영역이라고도 합니다.
• 로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있습니다.
• 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고(커넥션 버퍼나 결과 버퍼), 쿼리를 실행하는 순간에만 할당헀다가 다시 해제하는 공간(소트 버퍼나 조인 버퍼)도 있습니다.

3.2 MySQL 엔진과 스토리 엔진의 영역 처리

SQL 파서 <-> SQL 옵티마이저 <-> SQL 실행기 <-> 데이터 읽기/쓰기 <-> 디스크 스토리지

MySQL에서 쿼리가 실행되는 과정을 크게 나눈다면 거의 대부분의 작업이 MySQL 엔진(SQL 파서, 옵티마이저, 실행기)에서 처리되고, 마지막 "데이터 읽기/쓰기" 작업만 스토리지 엔진에 의해 처리된다.(아주 새로운 용도의 스토리지 엔진을 만든다 하더라도 DBMS의 전체 기능이 아닌 일부분의 기능만 수행하는 엔진을 작성하게 된다는 의미) 하지만, 실질적인 GROUP BY나 ORDER BY등 많은 복잡한 처리는 스토리지 엔진 영역이 아니라 MySQL 엔진의 처리 영역인 쿼리 실행기에서 처리된다.

4. 쿼리 실행 구조

[ oreilly.com/high-performance-mysql ]

4.1 파서(Parser)

사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미합니다. 쿼리문장의 기본 문법 오류는 이 과정에서 발견되며 사용자에게 오류 메시지를 전달하게 됩니다.

4.2 전처리기(Preprocessor)

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인합니다. 각 토큰을 테이블 이름이나 칼럼 이름 또는 내장 함수와 같은 개체를 매핑에 해당 객체의 존재 여부와 객체의 접근권한 등을 확인하는 과정을 이 단계에서 수행합니다. 존재 하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러집니다.

4.3 옵티마이저(Query Optimizer)

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 합니다.

4.4 실행 엔진(Query Execution Engine)

실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행합니다. 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 가정했을때, 다음과 같은 역할을 수행합니다.

ex) 실행엔진이 핸들러에게 테이블을 만들어, WHERE 절에 일치하는 레코드 읽어와, 읽어온 레코드들 임시 테이블로 저장해등을 요청하고 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈에게 넘깁니다.

즉, 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행합니다.

4.5 핸들러(Handler): 스토리지 엔진

MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당한다.

4.6 쿼리 캐시(Query Cache): MySQL 8.0 부터 제거

쿼리 캐시 기능은 동일 SQL 실행시 쿼리 처리 과정에서 Parsing, Preprocesor, Query Optimizer 단계를 수행하지 않고 캐시에 담겨진 이전 결과를 즉시 반환합니다. 다만 단점으로 테이블 데이터가 변경되면 캐싱된 데이터 삭제가 필요하고 그 과정에서 퀴리 캐시에 접근하는 쓰레드에 Lock이 걸리게 되고(Waiting for query cache lock) 그에 따라서 동시 처리 기능 저하가 발생되는 경우가 많아 MySQL 8.0 에서는 Query Cache 기능이 Remove 되었습니다.

• 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?
• 해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?
• 트랜잭션 내에서 실행된 쿼리인 경우 가시 범위 내에 있는 결과인가?
  • 트랜잭션은 자신의 ID보다 ID 값이 큰 트랜잭션에서 변경한 작업 내역이나 쿼리 결과는 참조할 수 없다. 이를 가시범위 라고 한다. 쿼리 캐시도 그 결과를 만들어낸 트랜잭션의 ID가 가시 범위 내에 있을 떄만 사용할 수 있는 것이다.
• CURRENT_DATE(), SYSDATE(), RAND() 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?
• 프리페어 스테이트먼트의 경우 변수가 결과에 영향을 미치지 않는가?
  • 프리페어 스테이트먼트(바인드 변수가 사용된 쿼리)의 경우에는 쿼리 문장 자체에 변수("?")가 사용되기 때문에 쿼리 문장 자체로 쿼리 캐시를 사용할 수 없다. 사용했다 하더라도 실제 MySQL 서버에서는 프리페어 스테이트먼트의 형태로 실행되지 않는다. 진정한 프리페어 스테이트먼트를 사용하려면 프로그램의 소스코드에서 데이터베이스 커넥션을 생성할 때 특별한 옵션을 사용해야만 한다.
• 캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?

• 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?

5. 복제(Replication)

MySQL의 복제는 레플리케이션이라고도 하는데, 복제는 2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간으로 동기화 하는 기술입니다. 복제에는 INSERT나 UPDATE와 같은 쿼리를 이용해 데이터를 변경할 수 있는 MySQL서버와 SELECT 쿼리로 데이터를 읽기만 할 수있는 MySQL서버로 나뉩니다.

5.1 마스터(Master) / 슬레이브(Slave)

MySQL에서는 쓰기와 읽기 역할로 구분해, 전자를 마스터라고 하고 후자를 슬레이브라고 합니다. 서버의 복제에서는 머스터는 반드시 1개이며 슬레이브는 1개 이상으로 구성될 수 있습니다.

5.1.1 마스터

주로 데이터가 생성 및 변경, 삭제되는 주체(시작점)를 마스터 장비라 하며, MySQL의 바이너리 로그가 활성화되면 어떤 MySQL 서버든 마스터가 될 수 있습니다. 일반적으로 복제에 참여하는 여러 서버 가운데 변경이 허용되는 서버를 마스터로 한정할 때가 많습니다.

그렇지 않은 경우 복제되는 데이터의 일관성을 보장하기 어렵기 때문입니다. 슬레이브 서버에서 변경 내역을 요청하면 마스터 장비 는 그 바이너리 로그를 읽어 슬레이브로 넘깁니다. 마스터 장비의 프로세스 가운데 "Bimlog dump"라는 스레드가 이 일을 전담하는 스레드입니다.

5.1.2 슬레이브

데이터(바이너리 로그)를 받아 올 마스터 장비의 정보(IP주소와 포트 정보 및 접속 계정)을 가지고 있는 경우 슬레이브가 됩니다. (마스터나 슬레이브라고 해서 별도의 빌드 옵션이 필요하거나 프로그램을 별도로 설치해야 하는 것은 아닙니다.)마스터 서버가 바이너리 로그를 가지고 있다면 슬레이브 서버는 릴레이 로그를 가지고 있습니다.

슬레이브 서버의 I/O 스레드는 마스터 서버에 접속해 변경 내역을 요청하고 받아 온 변경 내역을 릴레이 로그에 기록합니다. 그리고 슬레이브 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행함으로써 슬레이브의 데이터를 마스터와 동일한 상태로 유지합니다. I/O 스레드와 SQL 스레드는 마스터 MySQL에서는 가동되지 않으며, 복제가 설정된 슬레이브 MySQL 서버에서 자동적으로 가동하는 스레드입니다.

5.2 복제를 사용할 경우 주의할 점

• 슬레이브는 하나의 마스터만 설정 가능
• 마스터와 슬레이브의 데이터 동기화를 위해 슬레이브는 읽기 전용으로 설정
• 슬레이브 서버용 장비는 마스터와 동일한 사양이 적합
  • 여러 개의 스레드로 실행된 쿼리가 슬레이브에서 지연되지 않고 하나의 스레드로 처리될 수 있습니다.
  • 데이터 변경은 데이터 조회보다는 10분의 1 수준으로 유지되는 것이 일반적이므로 마스터 서버와 슬레이브 서버를 같은 사양으로 유지할 때가 많습니다.
  • 또한, 슬레이브 서버는 마스터 서버가 다운된 경우 그에 대한 복구 대안으로 사용될 떄도 많기 때문에 사양을 동일하게 맞추는 경우가 대부분입니다
• 복제가 불필요한 경우에는 바이너리 로그를 중지.
  • 바이너리 로그를 안정적으로 기록하기 위해 갭 락(Gap lock)을 유지하고, 매번 트랜잭션 이 커밋될 때 마다 데이터를 변경시킨 쿼리 문장을 바이너리 로그에 기록해야 한다. 바이너리 로그를 기록하는 작업은 AutoCommit이 활성화 된 MySQL 서버에서 심각한 부하로 나타날 때가 많습니다.
• 바이너리 로그와 트랜잭션 격리 수준(Isolation level)
  • 바이너리 로그 파일은 어떤 내용이 기록되냐느에 따라 STATEMENT 포맷 방식과 ROW 포맷 방식이 있습니다.
  • STATEMENT 방식은 바이너리 로그 파일에 마스터에서 실행되는 쿼리 문장을 기록하는 방식이며, ROW 포맷은 마스터에서 실행된 쿼리에 의해 변경된 레코드 값을 기록하는 방식입니다.

MySQL의 복제는 마스터에서 처리된 내용이 바이너리 로그로 기록되고, 그 내용이 슬레이브 MySQL 서버로 전달되어 재실행 되는 방식으로 처리된다. 바이너리 로그 파일에 SQL 문장을 기록하는 방식을 문장 기반 복제 라고 하며, 변경된 레코드를 바이너리 로그에 기록하는 방식을 레코드 기반의 복제 라고한다. SQL 기반의 복제가 정상적으로 작동하려면 REPEATABLE-READ 이상의 트랜잭션 격리 수준을 사용해야 하며, 그로 인해 InnoDB 테이블에서는 레코드 간의 간격을 잠그는 갭락이나 넥스트 키 락이 필요해진다. 반면 레코드 기반의 복제는 
마스터 슬레이브 MySQL 서버 간의 네티워크 트레픽을 많이 발생시킬 수 있지만 READ-COMMITTED 트랜잭션 격리 수준에서도 작동할 수 있으며 InnoDB 테이블에서 잠금의 경합은 줄어든다.

 

6. InnoDB 스토리지 엔진 아키텍처

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중에서 거의 유일하게 레코드 기반의 잠금을 제공하고 있으며, 때문에 높은 동시성 처리가 가능하고 또한 한정적이며 성능이 뛰어납니다.

6.1 InnoDB 스토리지 엔진의 특성

• 프라이머리 키에 의한 클러스터링
  • InnoDB의 모든 테이블은 기본적으로 PK를 기준으로 클러스터링 되어 저장됩니다. 즉, PK값 순서대로 디스크에
    저장되고, 이로 인해 PK에 의한 레인지 스캔은 상당히 빨리 처리됩니다.
• 잠금이 필요 없는 일관된 읽기
  • InnoDB 스토리지 엔진은 MVCC라는 기술을 이용해 락을 걸지 않고 읽기 작업을 수행합니다. 락을 걸지 않기 때문에
    InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 락을 기다리지도 않습니다.
• 외래키 지원
  • 외래키는 부모테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고,
    그로 인해 데드락이 발생할 수 있습니다. 그래서 실무에서는 잘 사용하지 않습니다.
• 자동 데드락 감지
• 자동화된 장애 복구
• 오라클의 아키텍처 사용

6.2 InnoDB 버퍼풀

• InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간입니다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 합니다. 버퍼 풀이 변경된 데이터를 모아서 처리하게 되면 랜덤한 디스크 작업의 횟수를 줄일 수 있습니다.
• 버퍼 풀의 크기는 innodb_buffer_pool_size로 설정하는데 일반적으로 전체 장착된 물리 메모리의 50 ~ 80% 수준에서 버퍼 풀의 메모리를 결정합니다. 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 결정해야 합니다.
• InnoDB 버퍼 풀은 아직 디스크에 기록되지 않은 변경된 데이터를 가지고 있습니다(이러한 데이터를 가지고 있는 페이지를 Dirty page라고 합니다).
• 이러한 Dirty page는 InnoDB에서 주기적으로 또는 어떤 조건이 되면 체크포인트 이벤트가 발생하는데, 이떄 Write 스레드가 필요한 만큼의 데이터 페이지만 디스크로 기록합니다.

6.3 언두(Undo)로그

언두 영역은 UPDATE 문장이나 DELETE와 같은 문장으로 데이터를 변경해을 떄 변경되기 전의 데이터(이전 데이터)를 보관하는 곳입니다. 언두의 데이터는 크게 두 가지 용도로 사용됩니다.

• 첫 번째, 트랜잭션의 롤백 대비용으로 언두 영역의 백업된 데이터를 다시 데이터 파일(데이터/인덱스 버퍼)로 복구합니다.
• 두 번째, 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는 데 사용합니다.

6.4 인서트 버퍼(Insert Buffer)

• RDBMS에서 레코드가 INSERT되거나 UPDATE 될 때는 데이터 파일을 변경하는 작업 뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요합니다.
• 그런데 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱 스가 많다면 이 작업은 상당히 많은 자원을 소모하게 됩니다. 그래서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 업데이트를 수행하지만, 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면, 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상 시키게 됩니다. 이때 사용하는 임시 메모리 공간을 인서트 버퍼(Insert Buffer)라고 합니다.
• 사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 인서트 버퍼를 사용할 수 없습니다.
• 인서트 버퍼에 임시로 저장돼 있는 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 인서트 버퍼 머지 스레드(Merge thread)라고 합니다.

MySQL 5.5부터는 innod_change_buffering이라는 설정 파라미터가 새로 도입되어 작업의 종류별로 인서트 버퍼를 활성화할 수 있으며, 인서트 버퍼가 비효율적일 때는 인서트 버퍼를 사용하지 않게 설정할 수 있도록 개선됐습니다.

6.5 리두(Redo) 로그 및 로그 버퍼

• 데이터를 버퍼링해 두기 위해 존재하는 InnoDB 버퍼 풀로만은 ACID를 보장할 수 없는데, 이를 위해 변경된 내용을 순차적으로 디스크에 기록하는 로그 파일을 가지고 있으며, 이를 리두 로그라고 합니다.
• 리두 로그 덕분에 DBMS 데이터는 버퍼링을 통해 한꺼번에 디스크에 변경된 내용을 처리할 수 있고 그로 인해 상당한 성능 향상을 기대할 수 있게 됐습니다.
• 하지만 사용량(특히 변경 작업)이 매우 많은 DBMS 서버의 경우에는 이 리두 로그의 기록 작업이 큰 문제가 되는데, 이러한 부분을 보완하기 위해 ACID 속성을 보장하는 수준에서 버퍼링하게 됩니다. 이러한 리두 로그 버퍼링에 사용되는 공간이 로그 버퍼라 합니다.

6.6 MVCC(Multi Version Concirrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지
않는 일관된 읽기를 제공하는데 있습니다. InnoDB는 언두 로그를 이용해 이 기능을 구현합니다.

예를 들어 특정 데이터 레코드를 1번 사용자가 'A' 내용에서 'B' 내용으로 UPDATE 한다고 가정해 보자.

• InnoDB 버퍼 풀 : 수정된 'B' 내용이 반영(레코드 전체)
• Undo 로그 : 수정 전의 'A' 내용이 반영(PK, 메타정보 및 수정된 칼럼만 백업)
• 이 때 커밋이나 롤백이 아직 일어나지 않는 상황에서, 2번 사용자가 해당 데이터를 읽으려고 하면 어떻게 될까?

이 질문의 답은 격리 수준에 따라 다르며, 이러한 과정을 DBMS 에서는 MVCC 라고 표현한다.

• READ_UNCOMMITTED : InnoDB 버퍼 풀이나 데이터 파일로부터 변경된 'B' 데이터를 읽어서 반환합니다.
• READ_COMMITTED 이상 : 아직 커밋되지 않았기 때문에 언두 영역의 'A' 데이터를 반환합니다.

6.7 잠금 없는 일관된 일기(Non-locking consistent read)

• InnoDB에서 격리 수준이 SERIALIZABLE이 아닌 READ-UNCOMMITTED이상의 수준인 경우, INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행됩니다.
• 읽으려는 행이 실제로 lock이 걸려있어도 읽을 때는 undo영역에서 읽기 때문에 lock이 걸리든 말든 상관없이 이전 버전의 데이터를 읽을 수 있습니다.

 

참고

• [book] 이성욱. 『개발자와 DBA를 위한 Real MySQL』. 위키북스. 2012.
• [Blog] Real MySQL 정리, 3장 MySQL 아키텍처: https://junghyungil.tistory.com/135
• [Blog] MySQL 아키텍쳐: https://enterone.tistory.com/223