요약
MySQL은 접속 클라이언트, 엔진, 스토리지 엔진으로 구성됩니다.
엔진에서는 SQL 문장을 분석하고 검증하며 최적화한 후 스토리지 엔진을 통해 데이터를 읽고 씁니다.
스토리지 엔진은 실제 디스크에 접근하여 데이터를 읽고 쓰는 역할을 수행합니다. InnoDB, MyISAM 등을 선택할 수 있습니다.
InnoDB는 기본적으로 선택되는 스토리지 엔진이며, 버퍼풀과 언두 로그 등을 통해 MVCC를 제공합니다.
주요 용어 정리
https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html
용어 정리 요약
커넥션 핸들러 (핸들러 API)
공식문서에는 커넥션 핸들러를 설명하는 그림이 없어서, 전체적인 MySQL 아키텍처를 나타내는 그림을 추가했습니다.
커넥션 핸들러는 MySQL 엔진이 데이터를 읽거나 쓸 필요가 있을 때 각 스토리지 엔진에 읽기 또는 쓰기를 요청합니다. 이를 핸들러 요청이라고 합니다.
핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지 확인할 수 있습니다.
MySQL 엔진
쿼리 파서, 전처리기, 옵티마이저 등으로 구성되어 있으며 옵티마이저가 MySQL의 핵심 부분을 담당합니다.
쿼리 파서는 기본적인 SQL 문장 오류를 체크하고 토큰으로 나누어 Parse Tree로 구성합니다. Parse Tree를 기반으로 토큰에 존재하는 테이블, 칼럼이 실제로 존재하는 값인지를 확인합니다.
옵티마이저는 내장된 우선순위에 따라 실행 계획을 수립합니다. 다양한 방법들을 고려하고 실행 계획의 비용이 낮은 방법을 선택하여 수행할 수 있습니다.
Query Parser
스토리지 엔진(Storage Engines)
스토리지 엔진은 실제 데이터를 디스크 스토리지에 저장하거나 불러오는 역할을 담당합니다. MySQL 엔진의 핸들러 API를 통해 호출되며 대표적으로 InnoDB와 MyISAM 스토리지 엔진이 있습니다.
MySQL 엔진은 하나지만, 스토리지 엔진은 여러 개를 동시에 사용할 수 있습니다. 스토리지 엔진은 플러그인 형태로 제공되며, 핸들러 API를 직접 구현하여 사용자가 직접 스토리지 엔진을 추가할 수 있습니다. MySQL8.0부터는 플러그인 형태가 아닌 컴포넌트 형태로 지원됩니다.
테이블이 사용할 스토리지 엔진을 지정할 수 있습니다. 예를 들어:
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;
SQL
복사
CRUD 작업이 발생하면 InnoDB 스토리지 엔진이 해당 처리를 담당하며, 각 스토리지 엔진은 성능 향상을 위해 버퍼풀(InnoDB), 키 캐시(MyISAM)와 같은 기능을 내장하고 있습니다.
나만의 스토리지 엔진을 구현할 때는 데이터 읽기/쓰기 작업만 구현하면 됩니다.
MyISAM이나 InnoDB 스토리지 엔진 가운데 어떤 것을 사용하더라도 큰 차이가 없을 것이라고 생각할 수 있습니다. 그러나 데이터 읽기/쓰기 작업이 얼마나 달라질 수 있는지는 이 책의 나머지 부분에서 설명합니다.
여기서 중요한 점은 하나의 쿼리 작업이 여러 하위 작업으로 나뉘고, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지, 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다는 것입니다.
MyISAM
•
5.5 버전 이전에 테이블 생성시 디폴트로 지정되던 스토리지 엔진
•
지원 기능이 많지 않아 구조가 단순
•
키 캐시 기능을 지원한다.
•
Full-text 인덱싱 가능
•
Table level-lock
InnoDB 스토리지 엔진
•
MySQL 5.5 버전 이후에 테이블 생성시 디폴트로 지정된 스토리지 엔진
•
지원 기능이 많아 구조가 복잡. 자원을 많이 사용
•
키 캐시 대신 버퍼풀을 지원한다.
•
인덱싱(Full-text 포함), 외래키, 제약조건, 동시성제어, 트랜잭션 지원
•
그리고 무엇보다도 트랜잭션이 지원 되기 때문에 현재는 MySQL 사용한다고 하면 InnoDB 스토리지 엔진을 사용한다고 보아도 무방할 정도로 많은 곳에서 사용하고있다.
•
MySQL을 사용하는 이유 = 트랜잭션 처리가 필요해서 = InnoDB 스토리지 엔진 사용
•
Row level-lock
운영체제 하드웨어 부분
실제 데이터와 로그 데이터를 저장하는 부분입니다. Redo Log, Undo Log 등 다양한 정보들을 저장합니다.
MySQL 스레딩 구조
MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 작동하며, 크게 포그라운드 스레드와 백그라운드 스레드로 구분됩니다.
포그라운드 스레드는 실제 사용자의 요청을 처리하며, 클라이언트의 개수에 따라 수가 정해집니다. 사용자가 작업을 완료하고 연결을 종료하면, 해당 연결을 관리하는 스레드는 스레드 캐시로 돌아가며, 일정 개수의 스레드가 항상 유지됩니다.
데이터 버퍼나 캐시를 먼저 읽어서 데이터를 가져오며, 없는 경우 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어 작업을 처리합니다.
InnoDB 테이블의 경우에는 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼에서 디스크까지의 기록 작업은 백그라운드 스레드가 처리합니다.
백그라운드 스레드
•
데이터를 읽어와 버퍼에 기록하는 것 외
•
인서트 버퍼를 병합
•
로그를 디스크로 기록
•
InnoDB 버퍼 풀의 데이터를 디스크에 기록
•
데이터를 버퍼로 읽어오기
•
잠금이나 데드락을 모니터링 등 다양한 작업을 수행합니다.
MyISAM은 사용자 스레드가 쓰기작업까지 처리하기 때문에, 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없습니다.
결국, MySQL(InnoDB)이 스레드를 포그라운드, 백그라운드로 나눈 이유는 읽기 요청의 처리와 쓰기 요청의 처리를 분리하기 위함입니다. 읽기 요청은 즉시 처리되어야 하지만, 쓰기 작업 같은 경우는 여러 요청을 모아서 한번에 처리해도 사용상의 문제가 되지 않기 때문입니다.
백그라운드 쓰레드가 여러 개가 동작하며 모든 작업이 수행되면 포그라운드 스레드가 사용자에게 데이터를 보여주는 것을 기대할 수 있습니다.
트랜잭션 지원 메타데이터
메타데이터는 생성 및 변경 작업에 트랜잭션이 지원되지 않아 테이블의 생성 및 변경 도중 MySQL 서버가 비정상적으로 종료되면 데이터가 일관되지 않은 문제가 있었고, 이를 데이터베이스나 테이블이 깨졌다라고 표현했습니다.
MySQL 8.0 버전부터는 이런 문제점을 해결하기 위해 트랜잭션 기반의 InnoDB의 테이블에 저장하도록 개선되었습니다.
이 정보는 mysql.ibd라는 테이블스페이스에 저장됩니다.
MySQL 메모리 영역
MySQL에서 사용하는 메모리 영역은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분됩니다.
글로벌 메모리 영역:
이 메모리 영역은 MySQL이 시작될 때 운영체제로부터 할당받으며, 클라이언트의 수와는 무관합니다. 모든 스레드가 공유하는 메모리 영역으로, 버퍼와 캐시에 사용됩니다. 대표적으로 InnoDB의 버퍼풀이 이에 해당합니다.
로컬 메모리 영역 (또는 세션 메모리 영역, 클라이언트 메모리 영역):
이 메모리 영역은 포워드 스레드 (또는 클라이언트 스레드)가 사용자의 쿼리를 처리하기 위해 사용하는 독립적인 메모리 공간입니다. 스레드 간에 공유되는 글로벌 메모리 영역과는 달리, 로컬 메모리 영역은 스레드 간에 공유되지 않습니다. 이 영역에는 커넥션 버퍼, 정렬 또는 조인 시 사용하는 버퍼 등이 포함됩니다. 커넥션 버퍼는 커넥션이 열려 있는 동안 계속 메모리 공간에 남아 있으며, 조인이나 정렬 시 사용하는 버퍼는 쿼리를 실행하는 순간에만 메모리 공간에 할당되었다가 해제됩니다.
플러그인 스토리지 엔진 모델
MySQL의 독특한 구조 중 대표적인 것이 플러그인 모델입니다. 스토리지 엔진 외에도 검색어 파서, 사용자 인증 등의 기능이 플러그인 형태로 존재합니다.
MySQL은 기본적으로 다양한 스토리지 엔진을 제공합니다.
쿼리가 실행되는 과정에서 대부분의 작업은 MySQL 엔진이 수행하며, 데이터 읽기/쓰기 작업만 스토리지 엔진이 핸들러를 통해 수행합니다.
새로운 스토리지 엔진을 사용하면 전체 기능에는 영향을 미치지 않고, 오직 데이터 읽기/쓰기 작업에만 영향을 줍니다. 이는 객체지향에서 Interface를 만들고 구현체를 교체하는 것과 유사합니다.
플러그인은 오직 MySQL 서버와만 인터페이스 가능하며, 플러그인 간의 통신은 불가능합니다. 이는 MySQL 서버의 변수나 함수를 직접 호출해야 하므로 안전하지 않고(캡슐화 불가능), 상호 의존 관계를 설정할 수 없어 초기화가 어렵다는 단점이 있습니다.
컴포넌트
MySQL 8.0부터 기존 플러그인 아키텍처 대체를 위한 컴포넌트 아키텍처를 지원합니다.
컴포넌트 아키텍처는 논리적 컴포넌트 집합으로 캡슐화하는 것을 목표로 합니다. 기능 확장을 위해 실행 중인 서버에 컴포넌트를 추가할 수 있으며, 동적 또는 정적으로 연결할 수 있습니다. 또한 컴포넌트는 서비스 API를 통해 다른 구성 컴포넌트와 통신 가능합니다.
쿼리 실행 구조
SQL 요청이 들어오면 다음의 단계별로 역할을 수행합니다.
쿼리 파서 - 쿼리 문장을 토크나이징하고 기본 문법 에러를 탐지합니다.
전처리기 - 구조적 문제를 확인하며, 테이블과 칼럼 이름의 존재 여부를 확인하고 접근 권한을 검사합니다.
옵티마이저 - 가장 저렴한 비용으로 처리를 빠르게 할 수 있는 방법을 결정합니다. 이는 경영진(두뇌)의 역할과 유사합니다.
실행 엔진 - 핸들러에게 요청을 담당하게 합니다. 이는 중간 관리자(손)의 역할과 유사합니다.
핸들러 혹은 스토리지 엔진 - 실행 엔진의 요청에 따라 데이터를 디스크에 저장하고 읽어오는 역할을 담당합니다. 이는 각 업무의 실무자의 역할과 유사합니다.
복제
쿼리 캐시
웹 기반 응용 프로그램에서 빠른 응답이 필요할 때 쿼리 캐시는 중요한 역할을 합니다. 그러나 테이블의 데이터가 변경되면 해당 테이블과 연관된 모든 캐시 저장 결과를 삭제해야 하므로 동시 처리 성능이 저하되는 문제가 있습니다.
이로 인해 MySQL 8.0에서는 쿼리 캐시 기능이 완전히 제거되었고, 관련 시스템 변수도 모두 삭제되었습니다.
스레드 풀
스레드 풀의 목적은 동시 처리되는 요청이 많아도 CPU가 제한된 수의 스레드 처리에만 집중할 수 있도록, 사용자의 요청을 처리하는 스레드 수를 줄이는 것입니다.
이를 통해 서버 자원의 소모를 줄일 수 있습니다. 그러나 스레드 풀을 사용하거나 스레드 수를 무조건 늘리는 것이 항상 좋지는 않습니다. 이는 불필요한 컨텍스트 스위칭으로 성능 저하를 초래할 수 있습니다.
CPU가 제한된 수의 스레드만으로 적절히 처리하도록 유도하면, CPU의 프로세서 친화도를 높이고 불필요한 컨텍스트 스위치를 줄여 오버헤드를 감소시킬 수 있습니다.
일반적으로 스레드 그룹의 수는 CPU 코어의 수에 맞추는 것이 좋습니다.
MySQL 서버 엔터프라이즈 에디션과 Percona Server에서만 스레드 풀 기능을 지원합니다. 커뮤니티 에디션에서는 지원하지 않지만, 동일한 버전의 Percona Server의 스레드풀 플러그인 라이브러리 (thread_pool.so 파일)을 MySQL 커뮤니티 에디션 서버에 설치하여 사용할 수 있습니다.
트랜잭션 지원 메타데이터
MySQL 5.7 버전까지는 테이블 구조와 일부 스토어드 프로그램을 파일 기반으로 관리하였습니다.
하지만 이 파일 기반의 메타데이터는 테이블 생성이나 변경 작업 중에 트랜잭션을 지원하지 않습니다. 따라서 MySQL 서버가 비정상 종료되면 일관성이 보장되지 않아 테이블이 손상될 수 있습니다.
이를 개선하기 위해 MySQL 8.0부터는 모든 관련 정보를 InnoDB 테이블에 저장합니다. 시스템 테이블과 데이터 딕셔너리 정보는 모두 mysql DB에 저장되며, information_schema의 테이블 구조는 mysql DB의 tables 테이블을 참조하는 뷰로 구성됩니다.
MySQL에 적용된 레이어드 아키텍처
MySQL에도 레이어드 아키텍처가 적용되어 있습니다.
Utility Layer
외부 요청을 처리하기 위해 MySQL Connectors가 제공됩니다. MySQL Connectors가 받은 요청은 MySQL 엔진에 전달됩니다. 이러한 MySQL Connectors를 레이어드 아키텍처에서는 Utility Layer라 부릅니다.
SQL Layer
MySQL 엔진에 요청이 도착하면, 쿼리 파서가 문법 오류를 확인합니다. 이후 전처리기가 컬럼명, 테이블명 등의 실제 존재 여부와 접근 권한을 검증합니다. 그 다음 옵티마이저가 쿼리 최적화를 수행하고, 실행 엔진이 스토리지 엔진이 제공하는 핸들러 API를 호출하여 쿼리를 수행합니다. 이러한 과정을 거치는 MySQL 엔진을 SQL Layer라 합니다.
Storage Engine Layer
스토리지 엔진은 실제로 디스크에 접근하여 데이터를 읽고 씁니다. MySQL 엔진이 쿼리를 수행할 수 있도록 핸들러 API를 제공합니다. MySQL 8.0에서는 InnoDB가 기본 스토리지 엔진으로 사용됩니다.
MySQL에서 백엔드 규칙을 볼 수 있습니다.
웹 애플리케이션에서만 적용되는 설계 원칙이 아니었을지도 모르겠습니다.
MySQL의 아키텍처는 레이어별로 관심사를 분리하여 백엔드에서 자주 보았던 레이어드 아키텍처와 유사한 점이 있습니다.
첫째, 각 레이어가 관심사를 분리하여 느슨한 결합을 가지고 있습니다.
InnoDB 외의 스토리지 엔진을 선택할 일은 거의 없지만, 스토리지 엔진은 핸들러 API를 제공하고 MySQL 엔진은 이를 호출하는 구조입니다. 이로 인해 스토리지 엔진이 변경될 수 있는 구조입니다. MySQL 엔진은 쿼리를 분석하고 최적화하는 핵심 역할만 담당하며, 디스크에 접근하여 데이터를 읽고 쓰는 역할은 스토리지 엔진에게 위임합니다. 이는 Repository 패턴에서의 Repository와 DAO의 관계와 유사합니다.
둘째, 하나의 요청에 대해 하나의 스레드를 할당하여 처리합니다.
스프링에서는 하나의 Http Request에 대해 하나의 스레드를 할당하여 처리합니다. 인터셉터와 아규먼트 리졸버가 기본 요청의 유효성을 검증하고, 컨트롤러는 요청을 처리할 주체에게 메시지를 전달합니다. MySQL에서도 하나의 요청에 대해 하나의 사용자 스레드를 할당하여 처리합니다. 쿼리 파서와 전처리기에서 SQL 요청의 유효성을 검증합니다.
스프링의 Http Request는 데이터베이스 처리가 완료된 후에 응답이 가능하지만, MySQL의 요청은 InnoDB를 사용한다고 가정했을 때, 디스크에 쓰기 작업이 완료되기를 기다리지 않고 바로 응답이 이루어집니다. 디스크 접근 비용이 크기 때문에 디스크에 쓰는 작업은 백그라운드 스레드를 이용하여 쓰기 지연 및 일괄 처리됩니다.
하나의 SQL문이 응답되기까지
MySQL 8.0 기준, SQL 요청이 들어왔을 때 처리가 완료될 때까지의 전반적인 흐름을 살펴봅시다.
1.
MySQL Connectors에 요청이 도착합니다.
2.
사용자 스레드가 할당되고 요청이 MySQL 엔진에 전달됩니다.
3.
토큰 파서가 SQL을 MySQL이 이해할 수 있는 최소 단위로 분리하고, 문법 유효성을 검증합니다.
4.
전처리기가 컬럼명, 테이블명 등이 존재하는지 확인하고, 접근 권한이 있는지 검증합니다.
5.
옵티마이저가 사용자가 전달한 SQL문을 어떻게 실행해야 효율적일지 결정합니다.
6.
실행 엔진이 핸들러 API(스토리지 엔진, 대부분의 경우 InnoDB)를 호출하고 결과를 가지고 다시 호출하며 흐름을 제어합니다.
7.
결과를 반환합니다.
8.
백그라운드 스레드에서 커밋되었으나 디스크에 반영되지 않은 내용을 디스크에 접근하여 일괄 처리합니다.
아직 빠진 부분이 많지만, 이해를 돕기 위해 개괄적으로 작성해봤습니다.
InnoDB 스토리지 엔진 아키텍처
MySQL 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금 제공하며, 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어난 스토리지 엔진입니다.
프라이머리 키에 의한 클러스터링
클러스터링: 비슷한 데이터를 묶어 보관하는 작업입니다.
InnoDB의 모든 테이블은 프라이머리 키 값의 순서를 기준으로 디스크에 저장됩니다.
InnoDB는 프라이머리키를 기준으로 데이터를 정렬하며, 이는 실제 데이터의 저장 형태와 세컨더리 인덱스의 리프노드에 프라이머리 키가 데이터로 저장되어 있는 형태를 의미합니다. 이러한 특성 때문에 인덱스 레인지 스캔을 활용하면 데이터를 빠르게 읽을 수 있습니다.
(클러스터링 인덱스 특성으로 인해, 프라이머리 키를 이용한 Range Scan이 매우 빠릅니다.)
프라이머리 키는 다른 보조 인덱스에 비해 비중이 높게 설정되어 있어, 쿼리 플랜에서 선택될 확률이 높습니다.
이러한 구조는 오라클의 IOT(Index Organized Table)와 동일하며, 모든 인덱스는 물리적인 레코드의 주소 값(ROWID)을 가지고 있습니다.
반면에 MyISAM은 프라이머리키와 세컨더리 인덱스의 구분이 없고, 리프노드는 실제 데이터의 물리적인 주소값을 가집니다. 따라서 MyISAM에서 프라이머리 키는 단지 유니크 제약을 가진 세컨더리 인덱스일 뿐입니다.
세컨더리 인덱스는 레코드의 물리적 주소가 아닌 프라이머리 키 값을 논리 주소로 사용합니다.
외래키 지원
InnoDB 스토리지 엔진에서만 지원하는 기능입니다. MyISAM이나 Memory 테이블에서는 이용할 수 없습니다.
부모 테이블과 자식 테이블에 데이터가 있는지 확인하는 작업과 쌍방의 테이블에 인덱스 생성이 필요하기 때문에 잠금이 여러 테이블로 확산되고, 이로 인해 DeadLock이 발생할 가능성이 높아져 실제 서비스 운영 환경에서는 사용하지 않는 경우가 많습니다.
foreign_key_checks 변수를 OFF로 설정하면 일시적으로 확인 작업 및 연쇄 작업을 중단할 수 있습니다. 일관성을 유지한 후, 다시 외래키 체크 기능을 활성화해야 합니다.
MVCC (Multi Version Concurrency Control)
MVCC의 주요 목표는 잠금 없이 일관된 읽기를 제공하는 것입니다. InnoDB 스토리지 엔진은 언두 로그를 이용하여 MVCC 기능을 구현하였습니다.
MVCC는 한 레코드에 여러 버전이 동시에 관리되며, 격리 수준(isolation level)에 따라 처리 방식이 달라집니다.
트랜잭션 격리 수준
READ_UNCOMMITED ⇒ 락이 걸려있어도 아직 커밋되지 않은 데이터도 읽겠다.
READ_COMMITED ⇒ 락이 걸려있어도 이전 커밋된 상태 까지의 데이터만 읽겠다.
REPEATABLE_READ ⇒ 자신보다 이전 트랜잭션ID 에서 변경까지만 읽겠다. Inno DB의 기본값
SERIALIZABLE ⇒ 락이 걸려있으면 락이 회수 될때까지 기다렸다가 읽겠다.
Python
복사
UPDATE를 실행하면 커밋 여부와 상관없이 InnoDB 버퍼 풀의 내용이 변경되며, 이전 값은 Undo log에 저장됩니다. (데이터 파일의 내용은 일반적으로 버퍼 풀과 동일합니다)
격리 수준이 READ_UNCOMMITTED인 경우, 커밋 여부와 상관없이 변경된 데이터를 반환하기 위해 버퍼 풀 또는 데이터 파일의 데이터를 읽어 반환합니다.
격리 수준이 READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE인 경우, 변경 전 내용을 보관하는 Undo 영역의 데이터를 반환합니다.
트랜잭션을 COMMIT하면 현재 상태를 영구 데이터로 변환하고, Undo 영역이 필요한 트랜잭션이 없을 때 Undo 백업 데이터를 삭제합니다.
트랜잭션을 ROLLBACK하면 Undo 영역의 백업 데이터를 InnoDB 버퍼 풀로 복구합니다.
잠금 없는 일관된 읽기 (Non-Locking Consistent Read)
InnoDB 스토리지 엔진은 MVCC을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.
SERIALIZABLE를 제외한 다른 격리 수준에서는 INSERT와 연결되지 않은 SELECT 작업이라면 다른 트랜잭션의 잠금을 기다리지 않고 읽기 작업이 가능하다.
(특정 사용자가 레코드를 변경하고 아직 커밋하지 않았다고 하더라도 이 변경 트랜잭션이 다른 사용자의 Select를 방해하지 않습니다.)
오랜 시간동안 트랜잭션이 활성 상태인 경우 일관된 읽기를 위해 Undo log를 삭제하지 못하므로 가능한 빨리 COMMIT이나 ROLLBACK을 통해 트랜잭션을 끝내는 것이 좋습니다.
자동 DeadLock 감지
InnoDB 스토리지 엔진은 교착 상태(DeadLock) 감지를 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리합니다.
교착 상태 감지 스레드는 주기적으로 잠금 대기 그래프를 검사하여, 교착 상태에 빠진 트랜잭션 중 하나를 강제로 종료합니다. 이때, Undo log의 양이 적은 트랜잭션이 우선적으로 종료됩니다. (ROLLBACK시에 Undo 처리할 내용이 적기 때문에 MySQL 서버 부하가 적습니다)
교착 상태 감지 스레드가 잠금 목록을 검사할 때는 잠금 목록에 새로운 잠금이 추가되므로, 동시 처리 스레드가 많은 경우 CPU 자원 소모가 많을 수 있습니다.
이 문제를 해결하기 위해 innodb_deadlock_detect 시스템 변수를 사용하여 교착 상태 감지 스레드를 비활성화하고, innodb_lock_wait_timeout 시스템 변수를 활성화하여 교착 상태 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환하도록 설정할 수 있습니다.
PK 또는 Secondary Index를 기반으로 동시성 처리를 많이 요구하는 서비스가 있다면, innodb_deadlock_detect를 비활성화하고 성능을 비교해 볼 것을 권장합니다. 이 때, innodb_lock_wait_timeout 을 기본 값인 50초 미만으로 설정해서 사용할 것을 권장합니다.장애 복구 자동화
InnoDB 데이터 파일은 MySQL 서버가 시작될 때 완료되지 않은 트랜잭션과 일부만 디스크에 기록된 데이터 페이지 등에 대해 자동 복구 작업을 수행합니다. 그러나 디스크나 서버 하드웨어 문제로 인해 자동 복구가 불가능한 경우, 자동 복구를 중지하고 MySQL 서버가 종료됩니다.
이런 경우 innodb_force_recovery 시스템 변수를 설정해서 InnoDB 엔진이 데이터 파일이나 로그 파일의 손상 여부를 선택적으로 검사하게 해야 합니다.
문제의 원인에 따라 위 시스템 변수를 1~6의 값을 설정하여 MySQL 서버를 재시작하고, 가능한 데이터를 백업한 뒤 데이터베이스를 재구축합니다.
만약 모든 설정값에 대해 MySQL 서버가 시작되지 않는다면, 백업을 사용하여 데이터베이스를 다시 구축해야 합니다.
마지막 전체 백업 시점부터 장애 발생 시점까지의 바이너리 로그가 있다면, InnoDB 복구보다는 전체 백업과 바이너리 로그를 사용하여 복구하는 것이 데이터 손실이 더 적을 수 있습니다.
InnoDB 버퍼 풀
InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간입니다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 하는 버퍼 역할을 함께 수행하여 디스크 작업 횟수를 줄일 수 있습니다.
버퍼 풀 크기
버퍼 풀 크기를 설정할 때는 운영체제와 각 클라이언트 스레드가 사용할 메모리까지 충분히 고려하여 설정하여야 합니다.
MySQL 5.7 버전부터는 버퍼 풀 사이즈를 런타임 중에 동적으로 변경할 수 있습니다. 따라서 처음에는 적절하게 작은 값으로 설정하고, 상황을 보면서 증가시키는 것이 좋습니다. 일반적으로는 운영체제의 전체 메모리의 50% 시작해서 최적점을 찾을 때까지 올려나갑니다.
즉, 증가시키는 것은 영향도가 적지만 줄이는 것은 영향도가 매우 크므로 줄이는 일이 없도록 주의하여야 합니다. 적절히 작은 값으로 설정한 후 상황을 봐가며 증가시키는 것이 좋습니다.
버퍼 풀 사이즈는 'innodb_buffer_pool_size' 시스템 변수로 변경할 수 있습니다. MySQL 서버가 비교적 한가한 시점에 작업을 진행하는 것이 좋습니다. 버퍼 풀의 전체 크기를 늘리거나 줄일 때는 128MB 단위로 처리됩니다.
버퍼 풀 구조
버퍼 풀의 메모리 공간은 페이지 단위의 조각으로 분할되며, 필요한 경우 데이터 페이지를 읽어 조각에 저장하는 구조를 가지고 있습니다.
InnoDB 스토리지 엔진은 버퍼 풀의 페이지 조각을 관리하기 위해 LRU 리스트, Flush 리스트, 그리고 Free 리스트라는 세 가지 자료 구조를 사용합니다.
•
LRU 리스트
◦
이는 LRU(Least Recently Used)와 MRU(Most Recently Used) 리스트가 결합된 형태입니다.
◦
디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것입니다.
◦
innodb_page_size 시스템 변수의 설정된 크기의 여러 페이지들로 쪼개어 저장됩니다. 이 때, 버퍼가 가득차면, 버퍼를 비워줘야합니다.
◦
이 때, Aging을 통해 자주 사용되는 페이지는 MRU 영역에 계속 위치하게 되고, 잘 사용되지 않는 페이지는 LRU의 끝으로 밀려나 버퍼 풀에서 제거됩니다.
•
클린 페이지, 더티 페이지 에 대한 정리
◦
버퍼 풀에 캐시된 상태에서 데이터가 INSERT, UPDATE, DELETE 명령으로 변경되는 경우에는 어떻게 될까요?
▪
클린 페이지 : 디스크에서 버퍼 풀로 데이터를 가져온 뒤에 변경되지 않은 데이터를 가진 페이지입니다.
▪
더티 페이지 : 디스크에서 버퍼 풀로 데이터를 가져온 후에 INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 페이지입니다.
◦
더티 페이지의 변경된 데이터들은 언젠가는 버퍼풀에서 나와서 디스크에 다시 기록되어야 합니다. 이때 Redo Log를 활용하여 더티 페이지가 버퍼풀에 무한정 머무르지 않도록 관리합니다.
•
Flush 리스트
◦
이는 디스크로 동기화되지 않은 데이터 페이지(더티 페이지)의 변경 시점을 기준으로 페이지 목록을 관리합니다.
•
Free 리스트
◦
이는 비어 있는 페이지 목록으로, 새로운 디스크 데이터 페이지를 읽어올 때 사용됩니다.
버퍼 풀과 리두 로그
InnoDB 버퍼 풀은 데이터베이스 성능 향상을 위해 데이터 캐시 기능과 쓰기 버퍼링 기능을 지원합니다.
Redo 로그는 기본적으로 데이터베이스에 일어난 모든 변화를 저장하는 메모리 공간입니다. Redo 로그는 여러 개의 고정된 크기의 ib_logfile이라는 이름의 파일들로 저장이 되며, 각 파일은 원형의 버퍼로 연결되어 관리됩니다.
InnoDB 버퍼 풀과 리두 로그의 관계를 요약하면 다음과 같습니다:
1.
InnoDB 버퍼 풀은 클린 페이지와 더티 페이지(변경된 데이터)를 포함합니다.
2.
더티 페이지는 언젠가 디스크에 기록되어야 합니다.
3.
한정된 메모리 공간인 버퍼 풀에 더티 페이지가 계속 머무를 수 없으므로, 리두 로그 파일을 통해 데이터 변경을 기록합니다.
4.
리두 로그 파일에 데이터 변경이 기록될 때마다 시퀀스 번호인 LSN(Long Sequence Number)이 증가합니다.
5.
InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시키며, 가장 최근 체크포인트 지점의 LSN(Long Sequence Number)이 활성 리두 로그 파일의 시작점이 됩니다.
6.
체크포인트가 발생하면, LSN 시작점부터 그보다 작은 LSN을 가진 리두 로그 엔트리와 관련된 버퍼 풀의 더티 페이지를 디스크로 동기화합니다.
a.
즉, 체크 포인트 발생 시, LSN보다 작은 부분에 해당하는 Redo Log와 관련된 더티 페이지는 모두 디스크로 동기화되어야 합니다.
따라서, 버퍼 풀의 더티 페이지 비율과 리두 로그 파일의 전체 크기는 쓰기 버퍼링 기능의 성능에 영향을 미칩니다. 버퍼 풀의 메모리 공간을 확장하는 것은 데이터 캐싱 기능만 향상시키는 것이며, 쓰기 버퍼링 기능까지 향상시키려면 InnoDB 버퍼 풀과 리두 로그 파일의 전체 크기를 적절히 조정하여 최적값을 찾아야 합니다.
버퍼 풀 플러시(Buffer Pool Flush)
InnoDB 스토리지 엔진은 성능에 부정적인 영향을 미치지 않으면서 더티 페이지를 디스크에 동기화하기 위해 Flush_list 플러시 기능과 LRU_list 플러시 기능을 백그라운드에서 실행합니다.
Flush_list 플러시
•
오래된 더티 페이지들을 수집하여 디스크와 동기화하는 작업
•
리두 로그 공간을 지우려면 반드시 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크와 동기화되어야 합니다. 이 더티 페이지를 디스크와 동기화하는 작업은 백그라운드에서 실행되며, 이때 사용되는 스레드를 클리너 스레드라고 합니다.
LRU_list 플러시
•
버퍼풀 내 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해 새로운 페이지들을 읽어올 공간을 만드는 작업
•
이때, 클린 페이지의 경우에는 즉시 프리(Free) 리스트로 옮겨지고, 더티페이지의 경우에는 디스크에 동기화 된다.
현재 MySQL 8.0 버전에서는 디스크 쓰기 폭증과 같은 현상이 대부분 발생하지 않기 때문에, 특별한 성능 문제가 발생하지 않는 한 관련 시스템 변수를 조정할 필요는 없습니다.
버퍼 풀 상태 백업 및 복구
서버를 종료 한 뒤 다시 시작하면, 캐시된 페이지들이 버퍼 풀에서 사라져 성능이 평상시의 1/10도 되지 않을 수 있습니다.
즉, 쿼리 요청이 매우 빈번한 서버를 재시작할 때 성능 저하가 발생할 수 있습니다. 이는 버퍼 풀에 데이터가 적재되어 있지 않아 모든 데이터를 디스크에서 읽어와야 하기 때문입니다.
버퍼풀 내의 디스크 데이터가 잘 캐시되어 있는 상태를 '워밍업'이라고 합니다.
잘 워밍업 된 경우에는 성능이 몇 십 배 향상될 수 있습니다.
이를 해결하기 위해 MySQL 5.5 버전에서는 서버 시작 전 주요 테이블과 인덱스에 대해 풀 스캔을 실행하여 Warming Up(디스크의 데이터가 버퍼 풀에 적재되어 있는 상태)을 구현했습니다.
MySQL 5.6 버전부터는 innodb_buffer_pool_dump_now 시스템 변수를 사용하여 현재 버퍼 풀의 상태를 백업할 수 있게 되었습니다.
// 수동 버퍼 풀 백업하기
SET GLOBAL innodb_buffer_pool_dump_now=ON;
// 버퍼풀 복구 시작
SET GLOBAL innodb_buffer_pool_load_now=ON;
// // 버퍼풀 복구 상태 확인 (CLI에서 마지막에 \\G 붙이면 더 상세한 정보를 보여줌)
SHOW STATUS LIKE 'innodb_buffe_pool_dumps_status'\\G
// // 버퍼풀 복구 중지
SET GLOBAL innodb_buffer_pool_load_abort=ON;
Python
복사
자동 버퍼 풀 백업 및 복구
•
서버를 종료하기 전에 수동으로 버퍼 풀을 백업해두었다가 복구할 수 있지만, 이 작업을 서버를 재시작할 때마다 수동으로 하기는 어렵습니다.
•
InnoDB에서 'innodb_buffer_pool_dump_at_shutdown'과 'innodb_buffer_pool_load_at_startup' 설정을 MySQL 서버의 설정 파일에 추가하면, 버퍼 풀 백업 및 복구를 자동으로 실행할 수 있습니다.
Double Write Buffer
하드웨어의 오작동이나 시스템의 비정상적인 종료로 인해 데이터 페이지의 일부만 디스크에 기록되는 Partial-page (Torn-page) 현상이 발생합니다. 이를 방지하기 위해 InnoDB 스토리지 엔진은 Double-Write 기법을 사용합니다.
출처: Real MySQL 8.0
1.
실제 데이터 파일에 변경 내용을 기록하기 전에 더티 페이지를 묶어 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록합니다.
2.
InnoDB 스토리지 엔진은 각 더티 페이지를 파일의 적당한 위치에 랜덤 쓰기를 실행합니다.
3.
데이터 파일의 페이지들과 DoubleWirte 버퍼의 내용을 비교합니다.
4.
데이터 파일 페이지와 DoubleWirte 버퍼의 내용이 다르다면 DoubleWirte 버퍼의 내용을 데이터 파일의 페이지로 복사합니다.
데이터 무결성이 매우 중요한 서비스에서 Double-Write 기능을 활성화하는 것을 권장합니다.
만약 성능을 위해 InnoDB Redo 로그 동기화 설정(innodb_flush_log_at_trx_commit)을 1이 아닌 값으로 설정했다면 Double-Write도 비활성화하는 것이 좋습니다.
엔진은 실제 디스크 상의 데이터 파일에 위 그림상 A~E 까지 더티페이지 내용을 기록하기 전에 double write buffer 에 먼저 기록하고, 쓰기 작업이 중간에 실패 할 경우 버퍼 내 내용과 디스크 내 데이터 파일의 내용을 비교해 동기화 작업을 완료 시킨다.
언두 로그
언두 로그란 트랜잭션과 격리 수준을 보장하기 위해 DML 변경 이전 버전의 데이터를 별도로 백업해둔 데이터입니다.
1.
트랜잭션 보장 용도
•
트랜잭션 롤백 시 Undo log를 사용해 변경 전 데이터로 복구합니다.
2.
격리 수준 보장 용도
•
데이터 변경 도중 다른 커넥션에서 데이터 조회 시 격리 수준에 맞게 Undo log에 백업해둔 데이터를 읽어서 반환합니다.
트랜잭션이 장시간 유지된다면 Undo Log의 양이 점점 증가하게 되어 쿼리 성능이 떨어질 수 있으므로 Undo log의 급증 여부를 모니터링하는 것이 필요합니다.
-- MySQL 서버의 모든 버전에서 사용 가능한 명령
SHOW ENGINE INNODB STATUS \G
-- MySQL 8.0 버전에서 사용 가능한 명령
SELECT count
FROM information_schema.innodb_metrics
WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';
SQL
복사
언두 테이블스페이스
언두 테이블스페이스란 언두 로그가 저장되는 공간으로, 이 공간은 버전마다 저장되는 공간이 다릅니다.
•
MySQL 5.6 이전
◦
모두 시스템 테이블스페이스(ibdata.ibd)에 저장됨
•
MySQL 5.6
◦
innodb_undo_tablespaces 시스템 변수를 0으로 설정 시 시스템 테이블스페이스에 저장, 2보다 큰 값으로 설정 시 별도의 Undo log 파일에 저장
•
MySQL 8.0
◦
모두 별도의 Undo log 파일에 저장
하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 Undo Slot(16바이트 단위)을 가집니다.
출처: Real MySQL 8.0
하나의 트랜잭션이 필요로 하는 Undo Slot의 개수는 대략 2개 정도이며,
DML의 특성에 따라 최대 4개까지 사용하게 됩니다.
Innodb 페이지 크기를 16바이트로 나눈 개수만큼 언두 슬롯 가짐
최대 동시 트랜잭션 수
= (InnoDB 페이지 크기) / 16 (롤백 세그먼트 개수) (언두 테이블스페이스 개수) / 2언두 로그 공간이 남는 것은 크게 문제가 되지 않지만, 부족한 경우 트랜잭션을 시작할 수 없는 심각한 문제가 발생하므로 가능한 기본값을 유지하는 것이 좋습니다. MySQL 8.0 부터는 CREATE UNDO TABLESPACE나 DROP TABLESPACE와 같은 명령으로 언두 테이블스페이스를 동적으로 추가 및 삭제할 수 있습니다.
체인지 버퍼
체인지 버퍼는 변경해야 할 인덱스 페이지를 디스크로부터 읽어와야 하는 경우 자원 소모를 줄이기 위해 사용되는 임시 메모리 공간입니다.
출처: MySQL 8.0 Document - InnoDB Storage Engine Change Buffer
데이터 변경 시 InnoDB는 즉시 업데이트를 실행하지 않고 체인지 버퍼에 저장해두었다가 사용자에게 결과를 반환하는 형태로 성능을 향상시킵니다. 이후 체인지 버퍼에 임시 저장된 인덱스 레코드 조각은 이후에 백그라운드 스레드 중 버퍼 머지 스레드에 의해서 병합됩니다.
•
유니크 인덱스의 경우 중복 여부를 체크해야 하므로 체인지 버퍼 사용할 수 없습니다.
MySQL 5.5 이전 버전까지는 INSERT 작업에 대해서만 버퍼링 가능(인서트 버퍼)했지만, 이후 점차 개선되어 MySQL 8.0부터는 INSERT, DELETE, UPDATE 작업도 버퍼링 가능하게 되었습니다.
-- 체인지 버퍼가 사용중인 메모리 공간 크기 확인
mysql> SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory
WHERE EVENT_NAME='memory/innodb/ibuf0ibuf';
-- 체인지 버퍼 관련 오퍼레이션 처리 횟수 확인
mysql> SHOW ENGINE INNODB STATUS \G
SQL
복사
리두 로그 및 로그 버퍼
리두 로그
리두 로그는 트랜잭션 요소 중 영속성(Durability)과 밀접하게 연관되어 있으며, 하드웨어나 소프트웨어 등 여러 문제점으로 인해 MySQL 서버가 비정상 종료되었을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치 역할을 합니다.
모든 DBMS는 읽기 성능을 고려한 자료구조를 가지고 있어 쓰기를 위해 디스크의 랜덤 액세스가 필요한데, InnoDB는 이로 인한 성능 저하를 막기 위해 리두 로그를 사용합니다.
MySQL 서버가 비정상 종료되었을 때 InnoDB 스토리지 엔진의 데이터 파일에 발생하는 문제를 리두 로그를 사용하여 해결하는 방법은 다음과 같습니다.
1.
커밋되었지만 데이터 파일에 기록되지 않은 데이터
•
리두 로그에 저장된 데이터를 데이터 파일에 다시 복사
2.
롤백되었지만 파일에 이미 기록된 데이터
•
리두 로그를 통해 트랜잭션 상태(커밋/롤백/실행중)를 확인하고 언두 로그의 내용을 데이터 파일에 복사
변경 작업이 매우 많은 DBMS 서버의 경우 리두 로그의 기록 작업이 성능 저하로 이어질 수 있습니다. 이를 위해 ACID 속성을 보장하는 수준에서 로그 버퍼 공간에 리두 로그 버퍼링을 수행합니다.
MySQL 8.0 부터는 리두 로그 수동 활성화/비활성화할 수 있으며, 데이터를 복구하거나 대용량 데이터를 한 번에 적재하는 경우 리두 로그를 비활성화하여 데이터 적재 시간을 감소시킬 수 있습니다. 데이터 적재 이후에는 꼭 다시 활성화하도록 주의합니다.
-- 로그 비활성화
ALTER INSTANCE DISABLE INNODB REDO_LOG;
-- ... 대량 데이터 적재 수행...
-- 리두 로그 활성화
ALTER INSTANCE ENABLE INNODB REDO_LOG;
SQL
복사
리두 로그 아카이빙
MySQL 서버에 유입되는 데이터 변경이 너무 많으면 리두 로그가 빠르게 증가하고, 새로 추가되는 리두 로그 내용을 복사하기 전에 덮어 쓰일 수 있습니다. 이렇게 되면 데이터 백업 파일은 일관된 상태를 유지하지 못하고 데이터 백업에 실패하게 됩니다.
이를 위해 MySQL 8.0 버전부터는 리두 로그 아카이빙 기능을 지원하여 데이터 변경이 많아서 리두 로그가 덮어 쓰인다고 해도 백업이 실패하지 않도록 합니다.
Adaptive Hash Index
어댑티브 해시 인덱스란 사용자가 수동으로 생성한 인덱스가 아닌 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스입니다.
innodb_adaptive_hash_index 시스템 변수를 이용하여 어댑티브 해시 인덱스를 활성화/비활성화할 수 있습니다.
B-Tree 인덱스의 검색 시간을 줄이기 위해 도입된 기능으로, 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고 필요할 때마다 어댑티브 해시 인덱스를 검색해 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있게 해줍니다.
출처: 카카오 테크 - MySQL InnoDB의 Adaptive Hash Index 활용
해시 인덱스는 인덱스 키 값과 해당 인덱스의 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리됩니다. 이때 인덱스 키 값은 B-Tree 인덱스의 고유 ID와 B-Tree 인덱스의 실제 키 값의 조합이며, 데이터 페이지 주소는 실제 키 값이 저장된 데이터 페이지의 메모리 주소(InnoDB 버퍼 풀에 로딩된 페이지 주소)입니다.
어댑티브 해시 인덱스와 성능
어댑티브 해시 인덱스를 사용한다고 해서 무조건 성능이 향상되는 것은 아닙니다.
•
성능 향상에 크게 도움이 되지 않는 경우
◦
디스크 읽기가 많은 경우
◦
특정 패턴의 쿼리가 많은 경우(LIKE 패턴 검색이나 조인)
◦
매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
•
성능 향상에 도움이 되는 경우
◦
디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
◦
동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
◦
쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
서비스 패턴을 파악해 어댑티브 해시 인덱스가 도움이 되고 있는지 불필요한 오버헤드를 발생시키고 있는지 판단하여 적절하게 사용해야 합니다.
MVCC : 다중 버전 동시성 제어
레코드 레벨의 트랜잭션을 지원하며,
하나의 레코드에 대해 여러 버전이 동시에 관리되는 개념
시나리오
UPDATE 요청이 왔다고 가정합시다!
요청을 처리할 내용이 리두 로그에 기록됩니다.
트랜잭션이 생성되며, 버퍼 풀에 요청 처리 내용이 기록됩니다.
언두 로그에는 UPDATE 요청 전의 마지막 COMMIT 버전이 보관됩니다.
트랜잭션이 완료되어 커밋이 수행되면, 버퍼 풀의 내용이 디스크에 반영됩니다.
READ_UNCOMMITTED, READ_COMMITTED
이 과정 도중, COMMIT 완료 이전에 SELECT 조회 요청이 오면 어떻게 될까요?
아직 디스크에 반영되지 않은 UPDATE 요청의 버퍼 풀 내용을 반환해야 할까요,
아니면 최종 COMMIT 버전인 언두 로그의 내용을 반환해야 할까요?
버퍼 풀의 내용을 반환하는 것이 READ_UNCOMMITTED 설정이며,
언두 로그의 내용을 반환하는 것이 READ_COMMITTED 설정입니다.
이와 같이 두 가지 버전을 동시에 관리할 수 있으며, 설정에 따라 다른 값을 반환하므로
이를 MVCC라고 합니다.
InnoDB가 이 기능을 제공하기 때문에 테이블 단위의 잠금을 걸지 않아도 되며,
따라서 No-Locking Consistent Read가 가능하다고 합니다.
트랜잭션 진행 중 롤백이 수행되면, 언두 로그의 값을 이용해 복원을 진행합니다.
리두 로그를 통한 복원
리두 로그는 MySQL이 재실행될 때 검사됩니다.
마지막 COMMIT과 리두 로그의 내용이 일치하는지 검사하며,
일치하지 않는다면 리두 로그를 기반으로 복구를 진행합니다.
트랜잭션이 정상적으로 처리되어 COMMIT으로 반영해야 한다고 선언되었지만,
백그라운드 스레드가 디스크에 반영하기 이전에 서버가 비정상적으로 종료되었다가 재실행되면,
이때 리두 로그를 통해 복구가 진행됩니다.