아키텍쳐

MySQL 서버는 크게 두 가지 주요 구성 요소로 나눌 수 있습니다: MySQL 엔진과 스토리지 엔진입니다. MySQL 엔진은 주로 쿼리 처리와 최적화를 담당하며, 스토리지 엔진은 실제 데이터를 저장하고 읽어오는 역할을 수행합니다. 이러한 구조로 인해 MySQL은 다른 DBMS와 차별화된 성능을 제공합니다.

MySQL 엔진 아키텍처

MySQL 엔진은 클라이언트로부터의 쿼리 요청을 처리하는 커넥션 핸들러와 쿼리 파서 및 옵티마이저로 구성됩니다. 이 엔진은 표준 SQL(ANSI SQL) 문법을 지원하여 타 DBMS와 호환성을 유지합니다.

스토리지 엔진

MySQL 서버에는 다양한 스토리지 엔진을 동시에 사용할 수 있습니다. 각 스토리지 엔진은 데이터의 저장 및 조회를 담당하며, 특정 테이블이 사용하는 스토리지 엔진을 명시할 수 있습니다. 예를 들어, 다음 SQL 문에서 test_table은 InnoDB 스토리지 엔진을 사용하도록 정의되었습니다:

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

이후 test_table에 대해 발생하는 모든 데이터 작업은 InnoDB 스토리지 엔진이 처리합니다. 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM)나 InnoDB 버퍼 풀과 같은 기능을 내장하고 있습니다.

핸들러 API

쿼리 실행기에서 데이터를 읽거나 쓰는 작업이 필요할 때, MySQL 엔진은 각 스토리지 엔진에 요청을 전달합니다. 이러한 요청을 핸들러 요청이라 하며, 여기서 사용되는 API를 핸들러 API라고 합니다. InnoDB 스토리지 엔진도 이 API를 통해 MySQL 엔진과 데이터를 주고받습니다.

핸들러 API를 통해 얼마나 많은 데이터 작업이 이루어졌는지는 다음 명령으로 확인할 수 있습니다:

SHOW GLOBAL STATUS LIKE 'Handler%';

이 명령은 다양한 핸들러 변수의 값을 보여주며, 각 변수는 특정 데이터 작업의 빈도를 나타냅니다.

MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 작동합니다. 이는 크게 포그라운드 스레드와 백그라운드 스레드로 나눌 수 있습니다. 각 스레드는 특정 작업을 처리하며, MySQL 서버 내의 모든 스레드는 performance_schema.threads 테이블을 통해 확인할 수 있습니다.

SELECT thread_id, name, type, processlist_user, processlist_host FROM performance_schema.threads ORDER BY type, thread_id;

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

포그라운드 스레드는 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 클라이언트가 요청한 쿼리문을 처리합니다. 클라이언트의 작업이 종료되면 해당 스레드는 스레드 캐시로 돌아갑니다. thread_cache_size 시스템 변수를 통해 캐시에 유지할 수 있는 최대 스레드 개수를 설정할 수 있습니다.

포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시에서 가져오며, 필요한 경우 디스크에서 직접 데이터를 읽어옵니다.

백그라운드 스레드

백그라운드 스레드는 주로 InnoDB 스토리지 엔진과 관련된 작업을 처리합니다. 이 작업에는 인서트 버퍼 병합, 로그 기록, 버퍼의 데이터 디스크 기록, 데이터 읽기 등이 포함됩니다. 특히 중요한 것은 로그 스레드와 쓰기 스레드입니다. MySQL 5.5 버전부터는 데이터 쓰기 스레드와 데이터 읽기 스레드의 개수를 innodb_write_io_threads와 innodb_read_io_threads 시스템 변수로 설정할 수 있습니다.

쓰기 작업은 버퍼링될 수 있지만, 읽기 작업은 지연될 수 없기 때문에 InnoDB는 쓰기 작업을 백그라운드에서 처리하며, 이 과정에서 성능을 최적화합니다.

플러그인 스토리지 엔진 모델

MySQL의 독특한 구조 중 하나는 플러그인 모델입니다. 플러그인을 통해 사용할 수 있는 것은 스토리지 엔진에 국한되지 않습니다. 예를 들어, 전문 검색 엔진을 위한 검색어 파서(인덱싱할 키워드를 분리하는 작업)도 플러그인 형태로 개발해서 사용할 수 있으며, 사용자 인증을 위한 Native Authentication과 CachingSHA-2 Authentication 등도 모두 플러그인으로 구현되어 제공됩니다.

MySQL에서 쿼리가 실행되는 과정을 살펴보면 대부분의 작업은 MySQL 엔진에서 처리되고, 마지막 단계인 '데이터 읽기/쓰기' 작업만 스토리지 엔진에서 처리됩니다. 즉, 사용자가 새로운 용도의 스토리지 엔진을 만든다 하더라도 DBMS의 전체 기능이 아닌 일부 기능만 수행하는 엔진을 작성하게 된다는 의미입니다.

MySQL 엔진과 스토리지 엔진의 역할 분담

MySQL 엔진은 쿼리 파서, 옵티마이저, 쿼리 실행기 등의 주요 기능을 처리하며, 실제 데이터의 읽기와 쓰기 작업은 스토리지 엔진에서 처리됩니다. 이처럼 MySQL 엔진은 사람의 두뇌 역할을 하고, 스토리지 엔진은 손과 발 역할을 한다고 비유할 수 있습니다.

MySQL 엔진이 스토리지 엔진에게 데이터 읽기/쓰기를 명령할 때는 반드시 핸들러를 통해 이루어집니다. 'Handler_'로 시작하는 상태 변수들은 MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미합니다. 따라서 MySQL에서 MyISAM이나 InnoDB 같은 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도, MySQL의 처리 과정은 대부분 동일하며, 단지 데이터 읽기/쓰기 영역의 처리만 차이가 있을 뿐입니다.

설치된 스토리지 엔진 확인

설치된 MySQL 서버(mysqld)에서 지원되는 스토리지 엔진을 확인하기 위해서는 SHOW ENGINES; 명령을 사용할 수 있습니다. 이 명령을 통해 MySQL 서버에 포함된 스토리지 엔진과 그 상태를 확인할 수 있습니다.

예를 들어:

SHOW ENGINES;

위 명령을 실행하면 MySQL 서버에 포함된 스토리지 엔진 목록이 출력됩니다. 각 엔진의 상태는 YES, DEFAULT, NO, DISABLED로 표시되며, 각 값의 의미는 다음과 같습니다:

  • YES: MySQL 서버에 해당 스토리지 엔진이 포함되어 있으며 활성화된 상태입니다.

  • DEFAULT: 'YES'와 동일하지만 필수 스토리지 엔진임을 의미합니다. 이 엔진이 없으면 MySQL이 시작되지 않을 수도 있습니다.

  • NO: 현재 MySQL 서버에 포함되지 않았음을 의미합니다.

  • DISABLED: MySQL 서버에 포함되었으나 파라미터에 의해 비활성화된 상태입니다.

만약 MySQL 서버에 포함되지 않은 스토리지 엔진을 사용하려면 MySQL 서버를 다시 빌드(컴파일)해야 합니다. 그러나 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드하여 쉽게 추가할 수 있으며, 플러그인 형태의 스토리지 엔진은 손쉽게 업그레이드할 수도 있습니다.

플러그인 확인

모든 플러그인의 내용은 SHOW PLUGINS; 명령으로 확인할 수 있습니다. 이 명령을 사용하면 설치된 스토리지 엔진뿐만 아니라 인증, 전문 검색용 파서, 쿼리 재작성과 같은 다른 플러그인도 확인할 수 있습니다.

쿼리 실행 구조

MySQL의 쿼리 실행 구조는 여러 단계로 나누어져 있으며, 각 단계에서 쿼리를 처리하고 최적화하는 작업이 이루어집니다. 이 구조는 MySQL 엔진과 스토리지 엔진으로 나누어져 있으며, 각 엔진이 서로 다른 역할을 담당합니다.

1. 쿼리 파서

쿼리 파서는 사용자가 입력한 SQL 문장을 토큰(token)으로 분리하고 트리 형태의 구조로 만드는 작업을 담당합니다. 이 과정에서 SQL 문장의 기본 문법 오류를 검출하며, 오류가 발견되면 사용자에게 오류 메시지를 반환합니다. 이 단계에서는 SQL 쿼리를 MySQL이 이해할 수 있는 내부 표현으로 변환합니다.

2. 전처리기

전처리기는 쿼리 파서가 생성한 파서 트리를 기반으로 SQL 문장의 구조적인 문제를 확인합니다. 이 과정에서 쿼리에 참조된 테이블, 칼럼, 내장 함수 등의 객체가 실제로 존재하는지, 사용자가 해당 객체에 접근할 권한이 있는지를 검사합니다. 이 단계에서 존재하지 않거나 접근할 수 없는 객체가 있으면 쿼리 실행이 중단되고 오류가 반환됩니다.

3. 옵티마이저

옵티마이저는 SQL 쿼리를 가장 저렴한 비용으로, 가장 빠르게 실행할 방법을 결정합니다. 이 단계는 쿼리 실행 계획을 수립하는 데 중점을 둡니다. 옵티마이저는 다양한 쿼리 처리 전략을 평가하여 최적의 실행 계획을 선택합니다. 예를 들어, 테이블을 스캔할 때 인덱스를 사용할지, 조인 순서를 어떻게 할지 등을 결정합니다. 이 과정에서 옵티마이저는 SQL 쿼리를 가능한 한 효율적으로 실행하기 위한 다양한 전략을 고려합니다.

4. 실행 엔진

실행 엔진은 옵티마이저가 수립한 실행 계획을 실제로 실행하는 역할을 합니다. 이 엔진은 각 단계에서 데이터를 읽고 처리하며, 필요한 경우 데이터를 저장하기도 합니다. 예를 들어, GROUP BY를 처리하기 위해 임시 테이블이 필요하다면, 실행 엔진은 이를 생성하고 데이터를 임시 테이블에 저장하는 작업을 수행합니다. 실행 엔진은 옵티마이저의 지시에 따라 작업을 진행하며, 각 작업 결과를 다음 작업의 입력으로 사용합니다.

5. 핸들러 (스토리지 엔진)

핸들러는 MySQL 서버의 가장 하단에서 실행 엔진의 요청에 따라 데이터를 디스크에 저장하거나 디스크로부터 데이터를 읽어오는 역할을 담당합니다. 핸들러는 결국 스토리지 엔진을 의미하며, MyISAM, InnoDB, Memory와 같은 스토리지 엔진이 각각의 핸들러로 작동합니다. 예를 들어, MyISAM 테이블을 조작하는 경우 MyISAM 스토리지 엔진의 핸들러가, InnoDB 테이블을 조작하는 경우 InnoDB 스토리지 엔진의 핸들러가 작업을 수행합니다.

복제

MySQL 서버에서 **복제(Replication)**는 매우 중요한 기능입니다. 복제는 MySQL 서버 간에 데이터를 동기화하고 고가용성을 유지하는 데 중요한 역할을 합니다. 이 기능은 데이터베이스 서버의 백업, 로드 밸런싱, 데이터 분석 등의 다양한 목적에 사용됩니다. MySQL 복제의 아키텍처와 세부적인 내용은 복제 관련 장에서 자세히 다루게 됩니다.

쿼리 캐시

MySQL 서버에서 **쿼리 캐시(Query Cache)**는 웹 기반의 응용 프로그램에서 빠른 응답을 제공하는 데 중요한 역할을 했습니다. 쿼리 캐시는 SQL 실행 결과를 메모리에 캐시하여 동일한 SQL 쿼리가 실행될 때 테이블을 다시 읽지 않고 즉시 결과를 반환할 수 있게 했습니다. 이는 데이터가 변경되지 않은 경우에 매우 빠른 성능을 제공했습니다.

하지만 MySQL 8.0에서는 쿼리 캐시 기능이 제거되었습니다. 그 이유는 데이터가 변경되면 쿼리 캐시에 저장된 결과를 무효화해야 하는데, 이 과정에서 성능 저하와 동시 처리 성능 문제가 발생했기 때문입니다. 또한, 쿼리 캐시 기능은 특정한 환경에서만 유용하며, 대부분의 경우 성능 향상보다는 오히려 문제가 되는 경우가 많았습니다. 따라서 MySQL 8.0에서는 쿼리 캐시 기능을 제거하는 것이 좋은 선택이었습니다.

스레드풀

MySQL 서버의 스레드풀(Thread Pool) 기능은 MySQL 서버의 성능을 최적화하는 데 사용됩니다. 이 기능은 MySQL 서버 엔터프라이즈 에디션에서 제공되며, Percona Server에서도 유사한 기능을 플러그인 형태로 사용할 수 있습니다.

스레드풀은 사용자의 요청을 처리하는 스레드의 수를 제한하여, 동시에 처리되는 요청이 많아도 MySQL 서버의 CPU가 효율적으로 작동하도록 합니다. 많은 사용자가 스레드풀 기능을 설치하면 성능이 크게 향상될 것으로 기대하지만, 실제로는 스레드풀이 모든 상황에서 성능 향상을 보장하지는 않습니다. 스레드풀은 서버의 자원 소모를 줄이고, CPU의 프로세서 친화도를 높이는 데 기여하지만, 잘못된 설정으로 인해 오히려 성능이 저하될 수도 있습니다.

Percona Server의 스레드풀은 CPU 코어의 개수만큼 스레드 그룹을 생성하며, 각 스레드 그룹은 사용자의 요청을 처리합니다. 스레드 그룹의 수는 thread_pool_size 시스템 변수로 조정할 수 있으며, 사용자의 요청이 많을 때는 추가 스레드를 생성하여 처리할 수도 있습니다. 그러나 스레드 그룹이 이미 많은 작업을 처리하고 있다면, 새로운 작업은 일정 시간이 지나야 처리될 수 있습니다.

스레드풀은 또한 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공합니다. 이렇게 하면 잠금 경합을 줄이고, 전체적인 처리 성능을 향상시킬 수 있습니다.

트랜잭션 지원 메타데이터

데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보는 데이터 딕셔너리 또는 메타데이터라고 불립니다. MySQL 서버는 5.7 버전까지 테이블의 구조를 파일(.FRM 파일)에 저장하고, 일부 스토어드 프로그램도 파일 기반으로 관리했습니다. 그러나 이러한 파일 기반 메타데이터는 트랜잭션을 지원하지 않아, 테이블 생성이나 변경 도중 MySQL 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남을 수 있었습니다.

MySQL 8.0부터는 이러한 문제를 해결하기 위해, 테이블의 구조 정보나 스토어드 프로그램의 코드를 모두 InnoDB 테이블에 저장하도록 개선되었습니다. 이로 인해 MySQL 서버가 비정상적으로 종료되더라도 스키마 변경 작업은 완전한 성공 또는 완전한 실패로 정리되어, 데이터베이스의 일관성이 유지됩니다.

InnoDB 스토리지 엔진을 사용하는 테이블의 메타정보는 InnoDB 기반의 데이터 딕셔너리에 저장되며, MyISAM이나 CSV와 같은 다른 스토리지 엔진의 메타정보는 여전히 파일로 저장됩니다. MySQL 서버는 InnoDB 이외의 스토리지 엔진을 사용하는 테이블을 위해 .sdi 파일을 사용하며, 이 파일은 기존의 .FRM 파일과 동일한 역할을 합니다.

MySQL 8.0의 이러한 개선 사항은 데이터베이스의 안정성과 일관성을 높이는 데 크게 기여하며, 시스템 테이블과 데이터 딕셔너리 정보를 InnoDB 테이블에 저장함으로써 트랜잭션 기반의 데이터 관리를 가능하게 합니다.

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

InnoDB의 모든 테이블은 기본적으로 프라이머리 키(Primary Key)를 기준으로 클러스터링되어 저장됩니다. 이 말은 프라이머리 키 값의 순서대로 레코드가 디스크에 물리적으로 저장된다는 것을 의미합니다. 이러한 클러스터링 덕분에, 세컨더리 인덱스(Secondary Index)는 실제 레코드의 물리적 주소 대신, 프라이머리 키의 값을 참조하여 해당 레코드를 찾습니다. 따라서 프라이머리 키가 클러스터링 인덱스(Clustering Index) 역할을 하며, 이를 이용한 레인지 스캔(Range Scan)은 매우 빠르게 수행될 수 있습니다.

쿼리의 실행 계획에서 프라이머리 키는 다른 보조 인덱스에 비해 우선적으로 선택되는 경향이 있습니다. 이는 오라클 DBMS의 IOT(Index-Organized Table)와 동일한 개념으로, InnoDB에서는 이러한 클러스터링 인덱스 구조가 일반 테이블 구조로 사용됩니다. 클러스터링 인덱스에 대한 더 자세한 내용은 8.8절에서 다루게 됩니다.

반면, MyISAM 스토리지 엔진은 클러스터링 키를 지원하지 않습니다. 따라서 MyISAM 테이블에서는 프라이머리 키와 세컨더리 인덱스 간에 구조적 차이가 없습니다. 프라이머리 키는 단지 유니크 제약을 가진 세컨더리 인덱스일 뿐이며, MyISAM 테이블의 모든 인덱스는 레코드의 물리적 주소 값(ROWID)을 참조합니다. MyISAM 테이블의 구조와 인덱스에 대해서는 4.3.3절 '데이터 파일과 프라이머리 키(인덱스) 구조'를 참고하시기 바랍니다.

외래 키 지원

InnoDB 스토리지 엔진은 외래 키(Foreign Key)를 지원합니다. 외래 키는 데이터베이스의 참조 무결성을 유지하는 데 중요한 역할을 합니다. 그러나 MyISAM이나 MEMORY 스토리지 엔진에서는 외래 키를 사용할 수 없습니다.

외래 키를 사용하면 부모 테이블과 자식 테이블의 특정 칼럼 간에 참조 관계가 설정되며, 이 칼럼들에는 인덱스가 생성되어야 합니다. 또한, 부모 테이블이나 자식 테이블에서 데이터 변경이 발생할 때마다 해당 테이블에 데이터가 존재하는지 확인하는 작업이 필요합니다. 이러한 작업이 추가적인 잠금을 유발하고, 이는 여러 테이블로 전파되어 데드락(Deadlock)이 발생할 가능성을 높입니다. 따라서 외래 키 사용 시 주의가 필요합니다.

서비스 환경에서 외래 키를 사용하는 것이 번거롭거나 성능 저하를 유발할 수 있으므로, 많은 경우 외래 키를 사용하지 않는 방향으로 설계하기도 합니다. 하지만 개발 환경에서는 데이터 무결성을 유지하는 데 도움이 되므로 외래 키 사용을 고려할 수 있습니다.

외래 키 체크를 일시적으로 비활성화하여 데이터 적재나 스키마 변경 등의 작업을 신속하게 처리할 수 있습니다. 이 경우, foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 체크 작업을 중지할 수 있습니다. 작업이 완료되면 foreign_key_checks를 다시 ON으로 설정하여 외래 키 체크를 활성화하는 것이 중요합니다.

SET SESSION foreign_key_checks = OFF; -- 작업 실행
SET SESSION foreign_key_checks = ON;

이 설정은 세션 범위에서만 유효하며, 작업을 완료한 후에는 외래 키 체크 기능을 반드시 활성화해야 데이터의 무결성을 유지할 수 있습니다. 외래 키 체크를 비활성화한 상태에서 부모 테이블의 레코드를 삭제한 경우, 자식 테이블의 관련 레코드도 삭제하여 일관성을 유지해야 합니다. 또한, 외래 키 체크를 비활성화하면 ON DELETE CASCADE와 ON UPDATE CASCADE 옵션도 무시되므로, 이에 주의해야 합니다.

MVCC (Multi-Version Concurrency Control)

MVCC(다중 버전 동시성 제어)는 레코드 수준의 트랜잭션을 지원하는 DBMS에서 제공하는 기능입니다. MVCC의 주요 목적은 잠금을 사용하지 않고도 일관된 읽기(Consistent Read)를 제공하는 데 있습니다. InnoDB는 언두 로그(Undo Log)를 활용하여 이 기능을 구현합니다.

'멀티 버전'이란, 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미입니다. 이를 통해 트랜잭션 격리 수준(Isolation Level)에 따라 각 트랜잭션이 다른 버전의 데이터를 읽을 수 있게 됩니다. 예를 들어, 트랜잭션 격리 수준이 READ_COMMITTED인 MySQL 서버에서 InnoDB 스토리지 엔진을 사용하는 테이블에서 데이터 변경이 어떻게 처리되는지 살펴보겠습니다.

예시: MVCC를 이용한 데이터 변경

먼저, 다음과 같은 테이블에 한 건의 레코드를 INSERT한 후 UPDATE하여 데이터 변경 작업을 확인합니다.

CREATE TABLE member (
    m_id INT NOT NULL,
    m_name VARCHAR(20) NOT NULL,
    m_area VARCHAR(100) NOT NULL,
    PRIMARY KEY (m_id),
    INDEX ix_area (m_area)
);
INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울');

INSERT 문이 실행되면, InnoDB 버퍼 풀과 데이터 파일에 레코드가 기록됩니다.

그 다음, UPDATE 문을 실행하여 데이터를 변경합니다.

UPDATE member SET m_area = '경기' WHERE m_id = 12;

이 경우, InnoDB 버퍼 풀은 즉시 새로운 값(‘경기’)으로 업데이트되며, 기존 값(‘서울’)은 언두 로그에 저장됩니다. 데이터 파일에 새로운 값이 기록될 수 있지만, 아직 커밋되지 않은 상태에서는 변경된 데이터를 다른 트랜잭션이 읽을 수 없습니다.

SELECT * FROM member WHERE m_id = 12;

이 쿼리에 대한 응답은 트랜잭션 격리 수준에 따라 달라집니다. 격리 수준이 READ_UNCOMMITTED인 경우, InnoDB 버퍼 풀에 있는 변경된 데이터를 읽습니다. 그러나 READ_COMMITTED 이상의 격리 수준에서는 아직 커밋되지 않았기 때문에, 언두 영역에 있는 기존 데이터를 반환합니다. 이는 MVCC의 동작 원리입니다.

잠금 없는 일관된 읽기 (Non-Locking Consistent Read)

InnoDB 스토리지 엔진은 MVCC 기술을 활용하여 잠금을 걸지 않고도 읽기 작업을 수행합니다. 이 덕분에, 다른 트랜잭션이 잠금을 걸고 있더라도 읽기 작업은 지연되지 않고 수행될 수 있습니다.

예를 들어, 트랜잭션 격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ 수준인 경우, INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 상관없이 잠금 없이 바로 실행됩니다. InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용하여, 잠금 없이 일관된 읽기를 제공합니다.

자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 데드락(Deadlock)을 감지하기 위해 잠금 대기 목록을 그래프 형태로 관리합니다. InnoDB는 데드락 감지 스레드를 통해 주기적으로 잠금 대기 그래프를 검사하여 교착 상태에 빠진 트랜잭션을 찾아내고, 그 중 하나를 강제로 롤백합니다. 이때 롤백 대상이 되는 트랜잭션은 일반적으로 언두 로그의 양이 적은 트랜잭션이 선택됩니다. 언두 로그가 적다는 것은 롤백을 할 때 처리해야 할 내용이 적다는 의미이며, 서버에 미치는 부하도 덜합니다.

InnoDB는 MySQL 엔진에서 관리되는 테이블 잠금을 볼 수 없으므로, 데드락 감지가 불확실할 수 있습니다. 이를 보완하기 위해 innodb_table_locks 시스템 변수를 활성화하면 InnoDB 내부의 레코드 잠금뿐만 아니라 테이블 수준의 잠금까지 감지할 수 있습니다.

동시 처리 스레드가 많아지거나, 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려질 수 있습니다. 이로 인해 서비스에 영향을 미칠 수 있습니다. 이러한 경우, innodb_deadlock_detect 시스템 변수를 OFF로 설정하여 데드락 감지 스레드를 비활성화할 수 있습니다. 이 경우, 데드락 상황에서는 innodb_lock_wait_timeout 시스템 변수를 설정하여 일정 시간이 지나면 트랜잭션이 실패하고 에러 메시지를 반환하도록 설정할 수 있습니다.

SET innodb_deadlock_detect = OFF;
SET innodb_lock_wait

_timeout = 10; -- 10초로 설정

이와 같이 설정하면 데드락 감지 스레드의 부하를 줄이고, 시스템 성능을 최적화할 수 있습니다. innodb_deadlock_detect를 비활성화한 경우, innodb_lock_wait_timeout 값을 낮추어 설정하는 것이 좋습니다. 이는 트랜잭션이 교착 상태에 빠졌을 때 빠르게 처리할 수 있도록 도와줍니다.

따라서 이 기능을 비활성화할 수 있도록 MySQL 소스 코드를 수정하여 사용했으며, 이 기능의 필요성을 오라클에 요청하여 MySQL 서버에 추가했습니다. 만약 PK 또는 세컨더리 인덱스를 기반으로 높은 동시성 처리를 요구하는 서비스가 있다면, innodb_deadlock_detect를 비활성화하고 성능을 비교해보는 것도 좋은 방법일 수 있습니다.

4.2.6 자동화된 장애 복구

InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 다양한 메커니즘이 탑재되어 있습니다. 이러한 메커니즘을 통해 MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에서 발생한 오류에 대한 일련의 복구 작업이 자동으로 진행됩니다.

InnoDB 스토리지 엔진은 매우 견고하게 설계되어 있어, 데이터 파일이 손상되거나 MySQL 서버가 시작되지 못하는 경우는 거의 발생하지 않습니다. 그러나 MySQL 서버와 무관하게 디스크나 서버 하드웨어 이슈로 인해 InnoDB 스토리지 엔진이 자동으로 복구를 수행하지 못하는 경우도 발생할 수 있습니다. 일단 문제가 생기면 복구 작업이 쉽지 않으며, InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행합니다. 이 단계에서 자동으로 복구될 수 없는 손상이 있다면 자동 복구가 중단되고 MySQL 서버는 종료됩니다.

이 경우, MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정하여 MySQL 서버를 다시 시작해야 합니다. 이 설정 값은 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부를 선택적으로 검사할 수 있도록 합니다.

  • InnoDB의 로그 파일이 손상되었다면 innodb_force_recovery를 6으로 설정하고 MySQL 서버를 기동합니다.

  • InnoDB 테이블의 데이터 파일이 손상되었다면 innodb_force_recovery를 1로 설정하고 MySQL 서버를 기동합니다.

  • 문제의 원인을 알 수 없다면 innodb_force_recovery 설정 값을 1부터 6까지 변경하면서 MySQL을 재시작해 봅니다.

즉, innodb_force_recovery 설정 값을 1로 설정한 후 MySQL 서버를 재시작해보고, MySQL이 시작되지 않으면 다시 2로 설정하고 재시작하는 방식입니다. innodb_force_recovery 값이 클수록 그만큼 상황이 심각하여 데이터 손실 가능성이 커지고, 복구 가능성은 낮아집니다.

MySQL 서버가 기동되고 InnoDB 테이블이 인식된다면, mysqldump를 이용해 데이터를 가능한 한 백업하고, 그 데이터를 사용해 MySQL 서버의 DB와 테이블을 다시 생성하는 것이 좋습니다. InnoDB의 복구를 위해 innodb_force_recovery 옵션에 설정할 수 있는 값은 1부터 6까지 있으며, 각 숫자 값으로 복구되는 장애 상황과 해결 방법은 다음과 같습니다. innodb_force_recovery가 0이 아닌 복구 모드에서는 SELECT 이외의 INSERT, UPDATE, DELETE 같은 쿼리는 수행할 수 없습니다.

  1. SRV_FORCE_IGNORE_CORRUPT (innodb_force_recovery = 1) InnoDB의 테이블스페이스의 데이터나 인덱스 페이지에서 손상된 부분이 발견되어도 무시하고 MySQL 서버를 시작합니다. 에러 로그 파일에 "Database page corruption on disk or a failed" 메시지가 출력될 때 주로 해당됩니다. 이 경우, mysqldump 프로그램이나 SELECT INTO OUTFILE... 명령을 이용해 데이터를 덤프한 후 데이터베이스를 다시 구축하는 것이 좋습니다.

  2. SRV_FORCE_NO_BACKGROUND (innodb_force_recovery = 2) InnoDB는 쿼리를 처리하기 위해 여러 종류의 백그라운드 스레드를 동시에 사용합니다. 이 복구 모드에서는 이러한 백그라운드 스레드 중 메인 스레드를 시작하지 않고 MySQL 서버를 시작합니다. InnoDB는 트랜잭션의 롤백을 위해 언두 데이터를 관리하는데, 트랜잭션이 커밋되어 불필요한 언두 데이터는 InnoDB의 메인 스레드에 의해 주기적으로 삭제됩니다(이를 Undo purge라고 합니다). InnoDB의 메인 스레드가 언두 데이터를 삭제하는 과정에서 장애가 발생했다면 이 모드로 복구할 수 있습니다.

  3. SRV_FORCE_NO_TRX_UNDO (innodb_force_recovery = 3) InnoDB에서 트랜잭션이 실행되면 롤백을 대비해 변경 전의 데이터를 언두 영역에 기록합니다. 일반적으로 MySQL 서버는 다시 시작하면서 언두 영역의 데이터를 먼저 데이터 파일에 적용하고, 그다음 리두 로그의 내용을 덮어써서 장애 시점의 데이터 상태를 복구합니다. 그리고 정상적인 MySQL 서버의 시작에서는 최종적으로 커밋되지 않은 트랜잭션은 롤백을 수행하지만, innodb_force_recovery가 3으로 설정되면 커밋되지 않은 트랜잭션의 작업을 롤백하지 않고 그대로 놔둔 채 MySQL 서버를 시작하는 모드입니다. 이때도 MySQL 서버가 시작되면 mysqldump를 이용해 데이터를 백업하여 다시 데이터베이스를 구축하는 것이 좋습니다.

  4. SRV_FORCE_NO_IBUF_MERGE (innodb_force_recovery = 4) InnoDB는 INSERT, UPDATE, DELETE 등의 데이터 변경으로 인한 인덱스 변경 작업을 상황에 따라 즉시 처리할 수도 있고, 인서트 버퍼에 저장해 두고 나중에 처리할 수도 있습니다. 이렇게 인서트 버퍼에 기록된 내용은 언제 데이터 파일에 병합(Merge)될지 알 수 없습니다. MySQL을 종료해도 병합되지 않을 수 있는데, 만약 MySQL이 재시작되면서 인서트 버퍼의 손상을 감지하면 InnoDB는 에러를 발생시키고 MySQL 서버는 시작하지 못합니다. 이때 innodb_force_recovery를 4로 설정하면 InnoDB 스토리지 엔진이 인서트 버퍼의 내용을 무시하고 강제로 MySQL이 시작되게 합니다. 인서트 버퍼는 실제 데이터와 관련된 부분이 아니라 인덱스에 관련된 부분이므로 테이블을 덤프한 후 다시 데이터베이스를 구축하면 데이터 손실 없이 복구할 수 있습니다.

  5. SRV_FORCE_NO_UNDO_LOG_SCAN (innodb_force_recovery = 5) MySQL 서버가 장애나 정상적으로 종료되는 시점에 진행 중인 트랜잭션이 있었다면 MySQL은 그 커넥션을 강제로 끊어버리고 별도의 정리 작업 없이 종료합니다. MySQL이 다시 시작되면 InnoDB 엔진은 언두 레코드를 이용해 데이터 페이지를 복구하고, 리두 로그를 적용해 종료 시점이나 장애 발생 시점의 상태를 재현해냅니다. 그리고 InnoDB는 마지막으로 커밋되지 않은 트랜잭션에서 변경한 작업을 모두 롤백 처리합니다. 그런데 InnoDB의 언두 로그를 사용할 수 없다면 InnoDB 엔진의 에러로 MySQL 서버를 시작할 수 없습니다. 이때 innodb_force_recovery 옵션을 5로 설정하면 InnoDB 엔진이 언두 로그를 모두 무시하고 MySQL을 시작할 수 있습니다. 하지만 이 모드로 복구되면 MySQL 서버가 종료되던 시점에 커밋되지 않았던 작업도 모두 커밋된 것처럼 처리되므로 실제로는 잘못된 데이터가 데이터베이스에 남게 됩니다. 이때도 mysqldump를 이용해 데이터를 백업하고, 데이터베이스를 새로 구축해야 합니다.

  6. SRV_FORCE_NO_LOG_REDO (innodb_force_recovery = 6) InnoDB 스토리지 엔진의 리두 로그가 손상되면 MySQL 서버가 시작되지 못합니다. 이 복구 모드로 시작하면 InnoDB 엔진은 리두 로그를 모두 무시한 채로 MySQL 서버가 시작됩니다. 또한 커밋되었더라도 리두 로그에만 기록되고 데이터 파일에 기록되지 않은 데이터는 모두 무시됩니다. 즉, 마지막 체크포인트 시점의 데이터만 남게 됩니다. 이때는 기존 InnoDB의 리두 로그는 모두 삭제하거나 별도의 디렉터리에 백업하고 MySQL 서버를 시작하는 것이 좋습니다. MySQL 서버가 시작하면서 리두 로그가 없으면 새로 생성되므로 별도로 파일을 만들 필요는 없습니다. 이때도 mysqldump를 이용해 데이터를 모두 백업하여 MySQL 서버를 새로 구축하는 것이 좋습니다.

위와 같은 방법을 진행했음에도 MySQL 서버가 시작되지 않으면, 백업을 이용해 다시 구축하는 방법밖에 없습니다. 백업이 있다면 마지막 백업으로 데이터베이스를 새로 구축하고, 바이너리 로그를 사용해 최대한 장애 시점까지의 데이터를 복구할 수 있습니다. 마지막 풀 백업 시점부터 장애 시점까지의 바이너리 로그가 있다면, InnoDB의 복구를 이용하는 것보다 풀 백업과 바이너리 로그로 복구하는 편이 데이터 손실이 더 적을 수 있습니다. 백업은 있지만 바이너리 로그가 없

거나 손실되었다면, 마지막 백업 시점까지만 복구할 수 있습니다.

자세한 내용은 MySQL 매뉴얼의 innodb_force_recovery 시스템 변수를 참조하십시오.

4.2.7 InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간입니다. 이 버퍼 풀은 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 합니다. 일반적인 애플리케이션에서는 INSERT, UPDATE, DELETE처럼 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시킵니다. 하지만 버퍼 풀이 이러한 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있습니다.

4.2.7.1 버퍼 풀의 크기 설정

일반적으로 전체 물리 메모리의 80% 정도를 InnoDB의 버퍼 풀로 설정하라는 내용의 게시물이 있지만, 그렇게 단순하게 설정해서는 안 됩니다. 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해야 합니다. MySQL 서버 내에서 메모리를 필요로 하는 부분은 크게 없지만, 경우에 따라 레코드 버퍼가 상당한 메모리를 사용할 수도 있습니다. 레코드 버퍼는 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간을 말하며, 커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 꽤 많이 필요해질 수 있습니다. MySQL 서버가 사용하는 레코드 버퍼 공간은 별도로 설정할 수 없으며, 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블의 개수에 따라 결정됩니다. 또한 이 버퍼 공간은 동적으로 해제되기도 하므로 정확히 필요한 메모리 공간의 크기를 계산할 수 없습니다.

다행히 MySQL 5.7 버전부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 개선되었습니다. 따라서 가능한 한 InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정하여 상황을 봐가면서 조금씩 증가시키는 방법이 최적입니다. 일반적으로 회사에서 이미 MySQL 서버를 사용하고 있다면 그 서버의 메모리 설정을 기준으로 InnoDB 버퍼 풀의 크기를 조정하면 됩니다. 하지만 처음으로 MySQL 서버를 준비한다면 다음과 같은 방법으로 InnoDB 버퍼 풀 설정을 찾아가는 방법을 권장합니다. 운영체제의 전체 메모리 공간이 8GB 미만이라면 50% 정도만 InnoDB 버퍼 풀로 설정하고 나머지 메모리 공간은 MySQL 서버와 운영체제, 그리고 다른 프로그램이 사용할 수 있는 공간으로 확보해주는 것이 좋습니다. 전체 메모리 공간이 그 이상이라면 InnoDB 버퍼 풀의 크기를 전체 메모리의 50%에서 시작하여 조금씩 올려가며 최적점을 찾습니다. 운영체제의 전체 메모리 공간이 50GB 이상이라면 대략 15GB에서 30GB 정도를 운영체제와 다른 응용 프로그램을 위해 남겨두고 나머지를 InnoDB 버퍼 풀로 할당합니다.

InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있으며, 동적으로 버퍼 풀의 크기를 확장할 수 있습니다. 하지만 버퍼 풀의 크기 변경은 크리티컬한 변경이므로 가능하면 MySQL 서버가 한가한 시점을 골라서 진행하는 것이 좋습니다. 또한 InnoDB 버퍼 풀을 더 크게 변경하는 작업은 시스템 영향도가 크지 않지만, 버퍼 풀의 크기를 줄이는 작업은 서비스 영향도가 매우 크므로 가능하면 버퍼 풀의 크기를 줄이는 작업은 하지 않도록 주의해야 합니다. InnoDB 버퍼 풀은 내부적으로 128MB 청크 단위로 쪼개어 관리되는데, 이는 버퍼 풀의 크기를 줄이거나 늘리기 위한 단위 크기로 사용됩니다. 따라서 버퍼 풀의 크기를 줄이거나 늘릴 때는 128MB 단위로 처리됩니다. 버퍼 풀의 크기를 동적으로 변경해야 한다면 반드시 먼저 MySQL 매뉴얼의 내용을 숙지하고 진행하기를 권장합니다.

InnoDB 버퍼 풀은 전통적으로 버퍼 풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합을 많이 유발해왔는데, 이러한 경합을 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선되었습니다. 버퍼 풀이 여러 개의 작은 버퍼 풀로 쪼개지면서 개별 버퍼 풀 전체를 관리하는 잠금(세마포어) 자체도 경합이 분산되는 효과를 내게 됩니다. innodb_buffer_pool_instances 시스템 변수를 이용해 버퍼 풀을 여러 개로 분리해서 관리할 수 있는데, 각 버퍼 풀을 버퍼 풀 인스턴스라고 표현합니다. 기본적으로 버퍼 풀 인스턴스의 개수는 8개로 초기화되지만, 전체 버퍼 풀을 위한 메모리 크기가 1GB 미만이면 버퍼 풀 인스턴스는 1개만 생성됩니다. 버퍼 풀로 할당할 수 있는 메모리 공간이 40GB 이하 수준이라면 기본 값인 8을 유지하고, 메모리가 크다면 버퍼 풀 인스턴스당 5GB 정도가 되게 인스턴스 개수를 설정하는 것이 좋습니다.

4.2.7.2 버퍼 풀의 구조

InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기(innodb_page_size 시스템 변수에 설정된)의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장합니다. 버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 크게 LRU(Least Recently Used) 리스트와 플러시(Flush) 리스트, 그리고 프리(Free) 리스트라는 3개의 자료 구조를 관리합니다. 프리 리스트는 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어있는 페이지들의 목록이며, 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용됩니다. LRU 리스트는 그림 4.13과 같은 구조를 띠고 있는데, 엄밀하게 LRU와 MRU(Most Recently Used) 리스트가 결합된 형태라고 보면 됩니다. 그림 4.13에서 'Old 서브리스트' 영역은 LRU에 해당하며, 'New 서브리스트' 영역은 MRU 정도로 이해하면 됩니다.

  • 데이터 페이지를 읽으면 New 리스트의 머리쪽으로 이동

  • 사용하지 않은 데이터 페이지는 Old 리스트의 프리쪽으로 이동

  • 새로운 페이지는 5/8 지점에 추가

LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지하여 디스크 읽기를 최소화하는 것입니다. InnoDB 스토리지 엔진에서 데이터를 찾는 과정은 대략 다음과 같습니다:

  1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사 A. InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색 B. 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색 C. 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급

  2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가

  3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동 Read Ahead와 같이 대량 읽기의 경우, 디스크의 데이터 페이지가 버퍼 풀로 적재되지만 실제 쿼리에서 사용되지는 않을 수 있으며, 이런 경우에는 MRU로 이동되지 않음.

  4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했는지에 따라 나이(Age)가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지에 부여된 나이가 오래되고('Aging'), 결국 해당 페이지는 버퍼 풀에서 제거됨. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨짐.

  5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

  6. **버퍼 풀 내부에서 최근 접근 여부에 따라 데이터 페이지

는 서로 경쟁하면서 MRU 또는 LRU로 이동하며, InnoDB 스토리지 엔진은 LRU의 끝으로 밀려난 데이터 페이지들을 버퍼 풀에서 제거하여 새로운 데이터 페이지를 적재할 수 있는 빈 공간을 준비함.**

따라서 처음 한 번 읽힌 데이터 페이지가 이후 자주 사용된다면 그 데이터 페이지는 InnoDB 버퍼 풀의 MRU 영역에서 계속 살아남게 되며, 반대로 거의 사용되지 않는다면 새롭게 디스크에서 읽힌 데이터 페이지들에 밀려서 LRU의 끝으로 밀려나 결국은 InnoDB 버퍼 풀에서 제거될 것입니다.

플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(이를 더티 페이지라고 함)의 변경 시점 기준의 페이지 목록을 관리합니다. 디스크에서 읽은 상태 그대로 전혀 변경이 없다면 플러시 리스트에 관리되지 않지만, 일단 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되며 특정 시점이 되면 디스크로 기록되어야 합니다. 데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영합니다. 그래서 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결됩니다. 하지만 리두 로그가 디스크로 기록되었다고 해서 데이터 페이지가 디스크로 기록되었다는 것을 항상 보장하지는 않습니다. 때로는 그 반대의 경우도 발생할 수 있는데, InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화하게 됩니다. 체크포인트는 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 리두 로그의 어느 부분부터 복구를 실행해야 할지 판단하는 기준점을 만드는 역할을 합니다.

4.2.7.3 버퍼 풀과 리두 로그

InnoDB의 버퍼 풀과 리두 로그는 매우 밀접한 관계를 맺고 있습니다. InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리의 성능이 빨라집니다. 물론 이미 디스크의 모든 데이터 파일이 버퍼 풀에 적재될 정도의 버퍼 풀 공간이라면 더는 버퍼 풀 크기를 늘려도 성능에 도움되지 않겠지만, 그렇지 않다면 디스크의 데이터가 버퍼 풀 메모리로 적재되면 성능이 좋아질 것입니다. 하지만 InnoDB 버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데, 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐시 기능만 향상시키는 것입니다. InnoDB 버퍼 풀의 쓰기 버퍼링 기능까지 향상시키려면 InnoDB 버퍼 풀과 리두 로그와의 관계를 먼저 이해해야 합니다.

InnoDB의 버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지(Clean Page)와 함께 INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 더티 페이지(Dirty Page)도 가지고 있습니다. 더티 페이지는 디스크와 메모리(버퍼 풀)의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록되어야 합니다. 하지만 더티 페이지는 버퍼 풀에 무한정 머무를 수 있는 것은 아닙니다. InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용합니다. 즉, 데이터 변경이 계속 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 덮어 쓰입니다. 그래서 InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리해야 하는데, 재사용 불가능한 공간을 활성 리두 로그(Active Redo Log)라고 합니다. 그림 4.14에서 화살표를 가진 엔트리들이 활성 리두 로그 공간인 것입니다.

리두 로그 파일의 공간은 계속 순환되어 재사용되지만, 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 갖게 되는데, 이를 LSN(Log Sequence Number)이라고 합니다. InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화하는데, 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 됩니다. 하지만 활성 리두 로그 공간의 마지막은 계속해서 증가하기 때문에 체크포인트와 무관합니다. 그리고 가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지(Checkpoint Age)라고 합니다. 즉, 체크포인트 에이지는 활성 리두 로그 공간의 크기를 의미합니다.

InnoDB 버퍼 풀의 더티 페이지는 특정 리두 로그 엔트리와 관계를 가지고, 체크포인트가 발생하면 체크포인트 LSN보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화되어야 합니다. 물론 당연히 체크포인트 LSN보다 작은 LSN 값을 가진 리두 로그 엔트리도 디스크로 동기화되어야 합니다.

이제 버퍼 풀의 더티 페이지 비율과 리두 로그 파일의 전체 크기가 어떤 관계인지 이해했으니, 간단한 예제를 통해 살펴보겠습니다.

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

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

1번의 경우, 리두 로그 파일의 크기가 100MB밖에 되지 않기 때문에 체크포인트 에이지(Checkpoint Age)도 최대 100MB만 허용됩니다. 예를 들어, 평균 리두 로그 엔트리가 4KB라면 25,600개의(100MB/4KB) 더티 페이지만 버퍼 풀에 보관할 수 있게 됩니다. 데이터 페이지가 16KB라고 가정한다면 허용 가능한 전체 더티 페이지의 크기는 400MB 수준밖에 안 되는 것입니다. 결국 이 경우는 버퍼 풀의 크기는 매우 크지만, 실제 쓰기 버퍼링을 위한 효과는 거의 보지 못하는 상황입니다. 2번의 경우도 1번과 동일한 방식으로 계산해볼 수 있는데, 대략 400GB 정도의 더티 페이지를 가질 수 있습니다. 하지만 버퍼 풀의 크기가 100MB이기 때문에 최대 허용 가능한 더티 페이지는 100MB 크기가 됩니다(물론 InnoDB 버퍼 풀의 여러 가지 설정으로 인해 100MB까지는 아니지만 여기서는 설명의 편의를 위해서입니다).

그렇다면 1번과 2번 중 어떤 경우가 좋은 것일까요? 사실 둘 다 좋은 설정은 아니라고 할 수 있습니다. 1번의 경우는 잘못된 설정이라는 것을 쉽게 알 수 있습니다. 그리고 2번의 경우는, 만약 리두 로그 공간이 무조건 큰 것이 좋다면 왜 오라클에서 기본값으로 리두 로그 공간을 1200GB로 설정하지 않았을까를 생각해보면 당연한 결과입니다.

참고: 일반적으로 리두 로그는 변경된 데이터만을 가지고 버퍼 풀은 데이터 페이지를 통째로 가지기 때문에, 데이터 변경이 발생해도 당연히 버퍼 풀의 크기가 100GB라고 해서 리두 로그의 공간이 100GB가 되어야 한다는 것은 아닙니다. 리두 로그는 훨씬 작은 공간만 있으면 됩니다.

PreviousMysqlNextExecution Plan

Last updated