MySQL 전체 구조
MySQL 엔진
MySQL 엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행한다.
MySQL 서버에서 MySQL 엔진은 하나만 가능하다.
스토리지 엔진
데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 역할을 수행한다.
MySQL 서버에서 스토리지 엔진은 여러개를 동시에 사용할 수 있다.
mysql > CREATE TABLE test (fd1 INT, fd2 INT) ENGINE=INNODB;위와 같이 쿼리에서 엔진을 지정하면 이후 해당 테이블의 모든 작업은 지정한 스토리지 엔진이 처리한다.
핸들러 API
MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 헨들러를 통해야 한다.
MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때 각 스토리지 엔진에 요청하는데 이러한 요청을 핸들러 요청이라고 하고 여기서 사용되는 API를 핸들러 API라고 한다.
이 핸들러 API를 통해 얼마나 많은 작업이 있었는지 확인 가능하다.
mysql > SHOW GLOBAL STATUS LIKE 'Handler%';전통적인 MySQL 스레딩 구조
MySQL 엔터프라이즈 에디션에서는 스레드 풀 모델도 사용 가능하다.
전통적인 스레드 모델 vs 스레드 풀 모델
- 전통적인 스레드 모델에서는 커넥션별로 포그라운드 스레드가 하나씩 생성되고 할당된다.
- 스레드 풀에서는 커넥션과 포그라운드 스레드는 1:1 관계가 아니라 하나의 스레드가 여러개의 커넥션 요청을 전담한다.
포그라운드 스레드 (클라이어트 스레드)
실제 사용자 요청을 처리하는 스레드
특징
- 포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재해 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
- 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 되돌아간다.
- 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다.
- 포그라운드 스레드는 데이터를 MySQL 데이터 버퍼나 캐시로부터 가져오며 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.
- InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.
백그라운드 스레드
백그라운드에서 동작하는 스레드로 백그라운드 스레드의 개수는 MySQL 서버의 설정 내용에 따라 가변적일 수 있다.
InnoDB 백그라운드 스레드가 처리하는 작업
- Insert Buffer를 병합하는 스레드 (Insert buffer merge thread)
- 로그를 디스크로 기록하는 스레드 (Log thread)
- InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드 (Write thread)
- 데이터를 버퍼로 읽어 오는 스레드 (Read thread)
- 잠금이나 데드락을 모니터링하는 스레드
로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드이다.
InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요가 없지만 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하는게 좋다.
데이터 읽기 작업은 절대 지연될 수 없지만 데이터 쓰기 작업은 지연되어 처리될 수 있다.
일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있으며 InnoDB 또한 이러한 방식으로 처리한다. 이러한 이유로 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다.
메모리 할당 및 사용 구조
MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.
글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당된다.
MySQL 시스템 변수로 설정해 둔 만큼 메모리를 할당받는다.
글로벌 메모리 영역
일반적으로 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당된다.
단 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있지만 클라이언트 스레드 수와 무관하며 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유된다.
로컬 메모리 영역
세션 메모리 영역이라고도 표현하며 MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다.
클라이언트가 MySQL에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다.
주의 사항
- 로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되지 않는다.
- 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 메모리 공간을 할당조차도 하지 않는다.
플러그인 스토리지 엔진 모델
MySQL은 스토리지 엔진뿐만 아니라 다양한 기능을 플러그인 형태로 지원한다.
인증이나 전문 검색 파서 또는 쿼리 재작성과 같은 플러그인이 있다.
그뿐만 아니라 커스텀하게 확장할 수 있게 플로그인 API가 메뉴얼로 공개돼 있으므로 기능을 확장하거나 새로운 기능을 구현할 수 있다.
플러그인 모델 단점
- 오직 MySQL 서버와 인터페이스할 수 있고 플러그인끼리는 통신할 수 없다.
- MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음(캡슐화 안 됨)
- 상호 의존 관계를 설정할 수 없어서 초기화가 어려움
컴포넌트
MySQL 8.0부터는 기존의 플러그인 아키텍처의 문제점을 해결하기 위해 컴포넌트 아키텍처가 지원된다.
쿼리 실행 구조
쿼리 파서
사용자 요청으로 들어온 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다.
쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달하게 된다.
전처리기
파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다.
각 토큰을 테이블 이름이나 칼럼 이름, 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 수행한다. 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.
옵티마이저
사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당한다.
실행 엔진
만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
GROUP BY를 임시 테이블로 처리하는 과정
1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
3. 읽어온 레코드를 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
핸들러(스토리지 엔진)
MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다. 핸들러는 스토리지 엔진을 의미하며 테이블 조작의 경우 생성시 지정한 엔진이 작동한다.
복제
16장에서 계속됩니다...
쿼리 캐시 - MySQL 8.0 부터 제거됨
쿼리 캐시로 SQL 실행 결과를 메모리에 캐시하여 빠른 응답이 가능했다.
하지만 테이블의 데이터가 변경되면 캐시에 관련된 내용을 모두 삭제해야 하는 문제가 발생했다.
이는 심각한 동시 성능 저하와 많은 버그의 원인이 되어 제거됐다.
스레드 풀
스레드 풀은 내부적으로 사용자 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많더라도 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다.
스레드 풀이 실제 서비스에서 눈에 띄는 성능 향상을 보여준 경우는 드물었다.
또한 CPU 스케줄링을 잘못하면 더 느려지는 사례도 발생할 수 있다.
트랜잭션 지원 메타데이터
데이터베이스 서버에서 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 한다.
MySQL 5.7 까지는 파일로 관리했는데 파일 기반의 작업은 트랜잭션을 지원하지 않고 서버가 비정상적으로 종료되면 일관되지 않는 상태로 남는 문제가 발생했다.
그래서 MySQL 8.0 부터 InnoDB의 테이블에 저장하도록 개선됐다.
트랜잭션 기반의 InnoDB 스토리지 엔진에 저장되도록 개선되면서 스키마 변경 작업 중간에 서버가 비정상적으로 종료된다고 하더라도 완전한 성공 또는 완전한 실패로 정리된다.
MySQL 서버가 작동하는데 기본적으로 필요한 테이블들을 묶어서 시스템 테이블이라고 하는데 대표적으로 사용자 인증과 권한에 관련된 테이블들이 있다.
InnoDB 스토리지 엔진 아키텍처
프라이머리 키에 의한 클러스터링
InnoDB의 모든 테이블은 기본적으로 프라이머리 키 값의 순서대로 디스크에 클러스터링 되어 저장된다.
모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.
결과적으로 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정된다.
외래 키 지원
외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능이다.
외래키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고 그로 인해 데드락이 발생할 때가 많으므로 개발할때도 외래키의 존재에 주의하는 것이 좋다.
외래키를 일시적으로 멈추고 작업을 처리할 수도 있는데 반드시 일관성을 맞춰준 후 다시 외래키 체크 기능을 활성화해야 한다.
MVCC (Multi Version Concurrency Control)
레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능으로 잠금을 사용하지 않는 일관된 읽기를 제공한다.
InnoDB는 언두로그를 이용해 이 기능을 구현한다.
여기서 Multi Version 이라 함은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미다.
MySQL 서버의 시스템 변수에 설정된 격리 수준에 따라 데이터를 조회하는 위치가 다르다.
이러한 동시성을 제어하는 과정을 MVCC라고 한다.
잠금 없는 일관된 읽기 (Non-Locking Consistent Read)
InnoDB 스터리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.
잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기디라지 않고 읽기 작업이 가능하다.
격리 수준이 SERIALIZABLE이 아닌 경우 INSERT와 연결되지 않은 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계 없이 항상 잠금을 대기하지 않고 바로 실행한다.
InnoDB에서는 변경되기 전에 데이터를 읽기 위해 언두 로그를 사용한다.
오랜 시간 동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 때가 가끔 있는데 바로 이러한 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 발생하는 문제다.
따라서 트랜잭션이 시작됐다면 가능한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.
자동 데드락 감지
InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리한다.
데드락 감지 스레드를 가지고 있어 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료한다.
이때 어느 트랜잭션을 먼저 강제 종료할 것인지를 판단하는 기준은 트랜잭션의 언두 로그 양이며 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다.
트랜잭션이 언두 레코드를 적게 가졌다는 이야기는 롤백을 해도 언두 처리를 해야 할 내용이 적다는 것이며 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문이다.
MySQL 엔진에서 관리되는 테이블 잠금은 볼 수가 없어서 데드락 감지가 불확실할 수도 있는데 시스템 변수를 활성화해 테이블 레벨 잠금까지 감지할 수 있게 된다.
동시 처리 스레드가 매우 많아지거나 트랜잭션이 가진 잠금 개수가 많아지면 데드락 감지 스레드가 느려진다.
데드락 감지 스레드는 잠금 목록을 검사해야 하기 때문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트(잠금 테이브)에 새로운 잠금을 걸고 데드락 스레드를 찾게 된다.
데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업을 진행하지 못하고 대기하면서 서비스에 악영향을 미치게 된다.
이렇게 동시 처리 스레드가 매우 많은 경우 데드락 감지 스레드는 더 많은 CPU 자원을 소모할 수도 있다.
이런 문제점을 해결하기 위해 MySQL 서버는 innodb_deadlock_detact 시스템 변수를 제공하며 OFF로 설정하면 데드락 감지 스레드는 더이상 작동하지 않게 된다. 데드락 감지 스레드가 작동하지 않으면 데드락이 발생해도 무한정 대기하게 된다.
하지만 innodb_lock_wait_timeout 시스템 변수를 활성화하면 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환하게 된다.
자동화된 장애 복구
InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러 가지 메커니즘이 탑재돼 있다.
MySQL 서버가 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다.
InnoDB에서는 데이터 파일 손상이나 서버가 시작되지 못하는 경우는 거의 없지만 하드웨어 이슈로 InnoDB 스토리지 엔진이 자동으로 복구를 못 하는 경우도 발생할 수 있다.
InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다.
이 단계에서 복구될 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 서버는 종료돼 버린다.
이때는 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 한다.
InnoDB 버퍼 풀
디스크 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.
쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다.
일반적인 애플리케이션에서는 INSERT, UPDATE, DELETE처럼 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시킨다.
하지만 버퍼 풀이 이러한 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다.
버퍼 풀의 크기 설정
MySQL 서버 내에서 메모리를 필요로 하는 부분은 크게 없지만 아주 독특한 경우 레코드 버퍼가 상당한 메모리를 사용하기도 한다.
레코드 버퍼는 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간을 말하는데 커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 꽤 많이 필요해질 수도 있다.
MySQL 5.7부터 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 개선됐다.
전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블 개수에 따라서 결정해야 한다.
그래서 가능하면 InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 봐 가면서 증가시키는 방법이 최적이다.
또한 이 버퍼 공간은 동적으로 해제되기도 하므로 정확히 필요한 메모리 공간의 크기를 계산할 수가 없다.
InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있으며 동적으로 버퍼 풀의 크기를 확장할 수 있다.
InnoDB 버퍼 풀의 크기를 줄이는 작업은 서비스 영향도가 매우 크므로 가능하면 버퍼 풀의 크기를 줄이는 작업은 하지 않도록 주의하자.
InnoDB 버퍼 풀은 전통적으로 버퍼 풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합을 많이 유발해왔는데 이러한 경합을 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게됐다.
여러 개의 작은 버퍼 풀로 쪼개지면서 잠금(세마포어)자체도 경합이 분산되는 효과를 내게 되는 것이다.
버퍼 풀의 구조
버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다.
버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 LRU 리스트와, 플러시 리스트, 프리 리스트라는 3개의 자료 구조를 관리한다.
- 프리 리스트
InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않는 비어 있는 페이지들의 목록이며 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다.
- LRU 리스트
LRU 리스트는 LRU와 MRU 리스트가 결합된 형태로 보면 된다.
LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 오랫동안 InnoDB 버퍼 풀에 메모리에 유지해서 디스크 읽기를 최소화하는 것이다.
버퍼 풀 관리를 위한 LRU 리스트 구조
InnoDB 스토리지 엔진에서 데이터를 읽는 과정
1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
- InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색
- 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색
- 버퍼 풀에 이미 데이터가 있었다면 해당 페이지를 MRU 방향으로 승급
2.. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동(Read Ahead와 같이 대량의 읽기의 경우 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만 실제 쿼리에서 사용되지 않을 수도 있으며 이런 경우 MRU로 이동되지 않음)
4. 버퍼 풀에 상주하는 데이터 페이지는 사용자가 쿼리가 얼마나 최근에 접근했는지에 따라 나이가 부여되며 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지에 부여된 나이가 오래되고 결국 해당 페이지는 버퍼 풀에서 제거된다. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 젊어지고 다시 MRU의 헤더 부분으로 옮긴다.
5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가
그래서 처음 한 번 읽힌 데이터 페이지가 이후 자주 사용된다면 그 데이터 페이지는 InnoDB 버퍼 풀의 MRU 영역에서 살아남게 되고 반대로 거의 사용되지 않는다면 새롭게 디스크에서 읽히는 데이터 페이지들에 밀려서 LRU의 끝으로 밀려나 결국 InnoDB 버퍼 풀에서 제거될 것이다.
- 플러시 리스트
플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 페이지(더티 페이지)를 변경 시점 기준의 페이지 목록을 관리한다.
디스크에서 읽은 상태 그대로 전혀 변경이 없다면 플러시 리스트에 관리되지 않지만 일단 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록돼야 한다.
데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다.
그래서 InnoDB 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다.
리두 로그의 각 엔트리는 특정 데이터 페이지와 연결된다.
하지만 리두 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는 것을 항상 보장하지는 않는다.
때로는 그 반대로 경우도 발생할 수 있는데 InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화하게 된다.
체크포인트는 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 리두 로그의 어느 부분부터 복구를 실행해야 할지 판단하는 기준점을 만드는 역할을 한다.
버퍼 풀과 리두 로그
InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리의 성능이 빨라진다.
하지만 InnoDB 버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두가지 용도가 있는데 메모리 공간만 단순히 늘리는 것은 데이터 캐싱 기능만 향상시키는 것이다.
InnoDB 버퍼 풀의 쓰기 버퍼링 기능까지 향상시키려면 InnoDDB 버퍼 풀과 리두 로그와의 관계를 먼저 이해해야 한다.
InnoDB의 버퍼 풀은 디스크에서 읽은 상태로 변경되지 않은 Clean Page와 함께 INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 Dirty Page도 포함하고 있습니다. Dirty Page는 디스크와 메모리(버퍼 풀)의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록되어야 합니다. 그래서 Dirty Page는 버퍼 풀에서 무한정 머무를 수 없습니다.
InnoDB 스토리지 엔진의 Redo 로그는 1개 이상의 고정 크기 파일을 연결하여 순환 고리처럼 사용합니다. 데이터 변경이 계속 발생하면 Redo 로그 파일에 기록된 로그 엔트리는 어느 시점에 다시 새로운 로그 엔트리로 덮어쓰이게 됩니다. 따라서 InnoDB 스토리지 엔진은 전체 Redo 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분하여 관리해야 하는데, 이렇게 재사용 불가능한 공간을 Active Redo 로그라고 합니다.
Redo 로그 파일의 공간은 계속 순환되어 재사용되지만, 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 가지게 되는데 이를 LSN(Log Sequence Number)이라고 합니다. InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 Redo 로그와 버퍼 풀의 Dirty 페이지를 디스크로 동기화하는데, 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 Active Redo 로그 공간의 시작점이 됩니다. 그러나 Redo 로그 공간의 마지막은 계속해서 증가하기 때문에 체크포인트와 무관합니다. 가장 최근 체크포인트의 LSN과 마지막 Redo 로그 엔트리의 LSN 차이를 Checkpoint Age라고 합니다. 즉, Checkpoint Age는 Active Redo 로그 공간의 크기를 의미합니다.
InnoDB 버퍼 풀의 Dirty 페이지는 특정 Redo 로그 엔트리와 관계를 가지고 있으며, 체크포인트가 발생하면 체크포인트 LSN보다 작은 Redo 로그 엔트리와 관련된 Dirty 페이지는 모두 디스크로 동기화되어야 합니다. 물론 체크포인트 LSN보다 작은 LSN 값을 가진 Redo 로그 엔트리도 디스크로 동기화돼야 한다.
버퍼 풀 플러시
InnoDB 스토리지 엔진은 버퍼 풀에서 디스크로 기록되지 않은 더티 페이지들을 성능상 악영향 없이 디스크에 동기화하기 위해 2개의 플러시 기능을 백그라운드로 실행한다.
- Flush_list 플러시
- LRU_list 플러시
Flust_list 플러시
InnoDB 스토리지 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 한다. 그런데 이때 오래된 리두 로그 공간이 지워지려면 반드시 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화돼야 한다.
이를 위해 InnoDB 스토리지 엔진은 주기적으로 Flush_list 플러시 함수를 호출해서 Flus_list 에서 오래전에 변경된 데이터 페이지 순서대로 동기화하는 작업을 수행한다.
이때 언제부터 얼마나 많은 더티 페이지를 한 번에 디스크로 기록하느냐에 따라 사용자의 쿼리 처리가 악영향을 받지 않으면서 부드럽게 처리된다.
LRU 리스트 플러시
InnoDB 스토리지 엔진은 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 하는데 이를 위해 LRU_list 플러시 함수가 사용된다.
LRU 리스트의 끝부분부터 시작해서 시스템 변수에 설정된 개수만큼의 페이지를 스캔한다.
이때 스캔하면서 더티 페이지는 디스크에 동기화하게 하며 클린 페이지는 즉시 Free 리스트 페이지로 옮긴다.
버퍼 풀 상태 백업 및 복구
InnoDB 서버의 버퍼 풀은 쿼리 성능과 매우 밀접한 관계가 있습니다. 버퍼 풀에 쿼리가 사용할 데이터가 이미 준비되어 있으면 디스크에서 데이터를 읽지 않고도 쿼리를 처리할 수 있기 때문입니다. 이러한 상태를 Warming Up이라고 합니다. MySQL 5.6 버전부터는 버퍼 풀 덤프 및 적재 기능이 도입되었습니다.
InnoDB 버퍼 풀은 크지만, 몇십 MB 이하인 것으로 확인됩니다. 이는 버퍼 풀의 LRU 리스트에서 적재된 데이터 페이지의 메타 정보만 가져와서 저장하기 때문입니다. 따라서 버퍼 풀의 백업은 매우 빠르게 완료됩니다.
그러나 백업된 버퍼 풀의 내용을 다시 버퍼 풀로 복구하는 과정은 InnoDB 버퍼 풀의 크기에 따라 상당한 시간이 걸릴 수 있습니다. 이는 백업된 내용에서 각 테이블의 데이터 페이지를 다시 디스크에서 읽어와야 하기 때문입니다. 이를 위해 InnoDB 스토리지 엔진은 버퍼 풀 복구 과정이 어느 정도 진행되었는지 확인할 수 있는 상태 값을 제공합니다.
InnoDB 스토리지 엔진은 MySQL 서버가 종료되기 직전에 버퍼 풀의 백업을 실행하고, MySQL 서버가 시작될 때 자동으로 백업된 버퍼 풀의 상태를 복구할 수 있는 기능을 제공합니다.
버퍼 풀의 적재 내용 확인
InnoDB 버퍼 풀의 메모리에 어떤 테이블의 페이지들이 적재돼 있는지 확인할 수 있다.
Double Write Buffer
InnoDB 스토리지 엔진의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다.
이로 인해 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제가 발생하면 그 페이지의 내용은 복구할 수 없을 수도 있다.
이렇게 페이지가 일부만 기록되는 현상을 Partial-page 또는 Torn-page라고 하는데 이런 현상은 하드웨어의 오작동이나 시스템의 비정상 종료 등으로 발생할 수 있다.
InnoDB는 이 같은 문제를 막기 위해 Double-Write 기법을 이용한다.
Double Writer Buffer 과정
1. InnoDB 엔진은 실제 데이터 파일에 변경 내용을 기록하기 전 더티 페이지를 우선 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록한다.
2. 그리고 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기를 실행한다.
Double Writer 버퍼 공간에 기록된 변경 내용은 정상적으로 더티 페이지가 기록되면 더이상 필요가 없어진다.
Double Writer 버퍼의 내용은 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래의 목적으로 사용된다.
InnoDB 스토리지 엔진은 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWriter 버퍼의 내용을 데이터 파일의 페이지로 복사한다.
DoubleWriter 버퍼는 데이터의 안정성을 위해 자주 사용되는데
SSD처럼 랜덤 IO나 순차IO의 비용이 비슷한 저장 시스템에서는 상당히 부담스럽다.
하지만 데이터 무결성이 매우 중요한 서비스에서는 DoubleWrite 활성화를 고려하는 것이 좋다.
만약 데이터베이스 서버의 성능을 위해 InnoDB 리두 로그 동기화 설정을 1이 아닌 값으로 설정했다면 DoubleWrite도 비활성화하는 것이 좋다.
언두 로그
InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전 버전의 데이터를 별도로 백업한다.
이렇게 백업된 데이터를 언두 로그라고 한다.
언두로그가 사용되는 곳
- 트랜잭션 보장
트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데 이때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구한다.
- 격리 수준 보장
특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경 중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.
언두 로그 레코드 모니터링
언두 영역은 INSERT, UPDATE, DELETE 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터를 보관하는 곳이다.
트랜잭션을 커밋하지 않아도 실제 데이터 파일 내용은 변경되고 이 상태에서 사용자가 커밋하면 현재 상태가 그대로 유지되고 롤백하면 언두 영역의 백업된 데이터를 다시 데이터 파일로 복구한다.
언두 테이블스페이스 관리
MySQL 8.0부터 언두 로그는 시스템 테이블스페이스 외부의 별도 로그 파일에 항상 기록되도록 개선되었습니다. 하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가집니다. 롤백 세그먼트는 1개 이상의 언두 슬롯을 가지게 됩니다.
하나의 롤백 세그먼트는 InnoDB 페이지 크기를 16바이트로 나눈 값의 개수만큼의 언두 슬롯을 가집니다. 예를 들어, InnoDB 페이지 크기가 16KB인 경우 하나의 롤백 세그먼트는 1024개의 언두 슬롯을 가지게 됩니다. 트랜잭션은 INSERT, UPDATE, DELETE 문장의 특성에 따라 최대 4개까지 언두 슬롯을 사용하게 됩니다. 일반적으로 트랜잭션은 임시 테이블을 사용하지 않으므로, 하나의 트랜잭션은 대략 2개 정도의 언두 슬롯을 필요로 한다고 가정할 수 있습니다.
따라서 최대 동시 처리 가능한 트랜잭션의 개수는 다음 수식으로 예측할 수 있습니다.
최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 * (롤백 세그먼트 개수) * (언두 테이블 스페이스 개수)가장 일반적인 설정인 16KB InnoDB에서 기본 설정을 사용하면, 대략 131072 = (16 1024 / 16 128 * 2 / 2) 개 정도의 트랜잭션이 동시에 처리 가능합니다. 언두 로그 슬롯이 부족한 경우에는 트랜잭션을 시작할 수 없는 심각한 문제가 발생할 수 있습니다. 언두 로그 관련 시스템 변수를 변경해야 한다면, 적절히 필요한 동시 트랜잭션 개수에 맞게 언두 테이블스페이스와 롤백 세그먼트의 개수를 설정해야 합니다.
체인지 버퍼
인덱스를 업데이트하는 작업은 랜덤한 디스크 액세스가 필요하기 때문에, 테이블에 인덱스가 많을 경우 상당한 자원을 소모합니다. 따라서 InnoDB는 변경할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하고, 디스크에서 읽어와서 업데이트해야 한다면 즉시 실행하지 않고 임시 공간에 저장한 뒤 사용자에게 결과를 반환합니다. 이때 사용하는 임시 메모리 공간을 체인지 버퍼라고 합니다.
유니크 인덱스의 경우, 사용자에게 결과를 전달하기 전에 중복 여부를 확인해야 하기 때문에 체인지 버퍼를 사용할 수 없습니다.
체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되며, 이를 체인지 버퍼 머지 스레드라고 합니다.
체인지 버퍼는 기본적으로 InnoDB 버퍼 풀의 25%까지 사용할 수 있도록 설정되어 있고, 필요한 경우 버퍼 풀의 50%까지 설정할 수 있습니다. INSERT나 UPDATE와 같은 작업이 빈번하게 발생한다면, 더 많은 버퍼 풀을 할당하면 됩니다.
리두 로그 및 로그 버퍼
리두 로그는 트랜잭션의 4가지 요소인 ACID 중 D(Durable)에 해당하는 영속성과 밀접하게 관련되어 있습니다. 리두 로그는 하드웨어나 소프트웨어 등의 문제로 인해 MySQL 서버가 비정상적으로 종료되었을 때, 데이터 파일에 기록되지 못한 데이터를 보호하는 안전장치입니다.
MySQL은 데이터 변경 내용을 먼저 로그로 기록합니다. DBMS는 데이터 파일의 쓰기보다 읽기 성능을 고려한 자료구조를 가지고 있기 때문에, 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요합니다. 이에 따라 변경된 데이터를 데이터 파일에 기록하려면 상대적으로 큰 비용이 듭니다. 성능 저하를 방지하기 위해 데이터베이스 서버는 쓰기 비용이 낮은 자료구조를 가진 리두 로그를 사용하며, 비정상 종료 시 리두 로그의 내용을 이용해 데이터 파일을 복구합니다.
데이터베이스 서버는 ACID와 성능 모두를 고려해야 하므로, 데이터 파일뿐만 아니라 InnoDB 버퍼 풀이나 로그 버퍼와 같은 자료구조도 사용합니다.
MySQL 서버가 비정상 종료되면, 일관되지 않은 데이터를 두 가지 형태로 가질 수 있습니다.
- 커밋되었지만 데이터 파일에 기록되지 않은 데이터
- 롤백되었지만 데이터 파일에 이미 기록된 데이터
1번의 경우, 리두 로그에 저장된 데이터를 데이터 파일에 복사하면 됩니다.
2번의 경우, 언두 로그로 해결하지만, 변경 상태 확인을 위해 리두 로그가 필요합니다.
트랜잭션이 커밋될 때마다 리두 로그를 디스크에 기록하는 작업은 부하를 유발합니다. 따라서, 리두 로그를 어느 주기로 디스크에 동기화할지 결정하는 시스템 변수를 제공합니다.
InnoDB 스토리지 엔진의 리두 로그 파일 전체 크기는 버퍼 풀의 효율성을 결정합니다. 리두 로그 파일의 전체 크기를 InnoDB 버퍼 풀의 크기에 맞게 선택하면, 변경된 내용을 버퍼 풀에 모아 한 번에 디스크에 기록할 수 있습니다. 그러나 사용량이 많은 DBMS 서버에서는 리두 로그의 기록 작업이 큰 문제가 될 수 있습니다. 이를 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링한다. 이러한 리두 로그 버퍼링에 사용되는 공간이 로그 버퍼다.
리두 로그 아카이빙
MySQL 8.0에서 리두 로그를 아카이빙할 수 있는 기능이 추가됐다.
리두 로그 아카이빙 기능은 데이터 변경이 많아서 리두 로그가 덮어쓰인다고 하더라도 백업이 실패하지 않게 해준다.
리두 로그 활성화 및 비활성화
InnoDB 스토리지 엔진의 리두 로그는 하드웨어, 소프트웨어 등의 문제로 인해 MySQL 서버가 비정상적으로 종료되었을 때, 데이터 파일에 기록되지 못한 트랜잭션을 복구하기 위해 항상 활성화되어 있습니다. 트랜잭션이 커밋되어도 데이터 파일은 즉시 디스크로 동기화되지 않지만, 리두 로그는 항상 디스크에 기록됩니다.
MySQL 8.0부터는 리두 로그를 수동으로 활성화하거나 비활성화할 수 있습니다. 데이터 복구나 대용량 데이터 적재 시 리두 로그를 비활성화하여 적재 시간을 단축시킬 수 있습니다.
데이터 적재 작업 후에는 반드시 리두 로그를 다시 활성화해야 합니다. 리두 로그가 비활성화된 상태에서 서버가 비정상 종료되면 마지막 체크포인트 이후의 데이터는 복구할 수 없게 됩니다. 더욱이, 마지막 체크포인트 시점의 데이터도 일관성이 없을 수 있습니다. 예를 들어, 10시에 실행되고 10시 1분에 비정상 종료된 경우, 10시부터 10시 1분 사이의 다양한 시점의 데이터를 섞여있게 됩니다.
따라서, 데이터의 중요성과 관계없이 서비스 도중에는 리두 로그를 활성화하여 MySQL 서버가 정상 종료되더라도 특정 시점의 일관된 데이터를 유지할 수 있도록 해야 합니다.
어댑티브 해시 인덱스
어댑티브 해시 인덱스는 InnoDB 스토리지 엔진이 자주 요청되는 데이터에 대해 자동으로 생성하는 인덱스로, B-Tree 검색 시간을 줄이는 기능을 제공합니다. 자주 읽히는 데이터 페이지의 키 값을 활용하여 해시 인덱스를 생성하고, 필요할 때 어댑티브 해시 인덱스를 검색하여 레코드가 저장된 데이터 페이지를 빠르게 찾아갈 수 있습니다.
인덱스의 키 값은 B-Tree 인덱스의 고유번호(Id)와 실제 키 값 조합으로 생성됩니다. 이렇게 하는 이유는 InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스가 기본적으로 하나만 존재하므로, 모든 B-Tree 인덱스에 대한 어댑티브 해시 인덱스가 하나의 해시 인덱스에 저장되며 특정 키 값이 어느 인덱스에 속한 것인지 구분해야 하기 때문입니다.
데이터 페이지 주소는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 나타내며, 이는 InnoDB 버퍼 풀에 로딩된 페이지의 주소를 의미합니다. 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 해당 데이터 페이지가 버퍼 풀에서 사라지면 어댑티브 해시 인덱스에서도 해당 페이지 정보가 사라집니다.
MySQL 8.0부터는 내부 잠금(세마포어) 경합을 줄이기 위해 어댑티브 해시 인덱스의 파티션 기능을 제공합니다. 기본 값은 8개의 파티션으로, 어댑티브 해시 인덱스가 성능에 큰 도움이 되는 경우 파티션 개수를 더 많이 설정함으로써 내부 잠금 경합을 줄이는 데 도움이 됩니다.
어댑티브 해시 인덱스가 도움이 되는 경우
- 디스크 데이터가 버퍼 풀 크기와 비슷하여 디스크 읽기가 적은 경우
- 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
- 쿼리 데이터가 일부 데이터에만 집중되는 경우
어댑티브 해시 인덱스가 도움이 되지 않는 경우
- 디스크 읽기가 많은 경우
- 특정 패턴의 쿼리가 많은 경우 (예: 조인, LIKE 패턴 검색)
- 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
어댑티브 해시 인덱스는 데이터 페이지를 메모리에서 더 빠르게 접근하는 기능이지만, 디스크에서 데이터를 빈번하게 읽어야 하는 경우 효과가 없습니다. 또한 메모리를 상당히 사용하며, 해시 인덱스 효율이 없는 경우에도 InnoDB가 계속 해시 인덱스를 사용할 것입니다.
테이블 삭제 시 어댑티브 해시 인덱스가 영향을 미칩니다. 테이블의 인덱스가 어댑티브 해시 인덱스에 적재되어 있다면, InnoDB 스토리지 엔진은 테이블의 모든 데이터 페이지를 어댑티브 해시 인덱스에서 제거해야 합니다. 이 과정은 CPU 자원을 많이 사용하며 데이터베이스 서버의 처리 성능을 저하시킬 수 있습니다.
MySQL 로그 파일
에러 로그 파일
MySQL이 실행되는 도중에 발생하는 경고나 메시지가 출력되는 로그 파일이다.
자주 보이는 메시지
- MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
- 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 트랜잭션 복구 메시지
- 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
- 비정상적으로 종료된 커넥션 메시지
- 모니터링 또는 상태 조회 명령의 결과 메시지
- MySQL 종료 메시지
제네럴 쿼리 로그 파일
MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토해 볼 때가 있다.
이때는 쿼리 로그를 활성화해서 쿼리를 쿼리 로그파일로 기록하게 한 다음 그 파일을 검토하면 된다.
쿼리 로그 파일에는 시간 단위로 실행됐던 쿼리의 내용이 모두 기록된다.
제네럴 쿼리 로그는 실행되기 전에 MySQL이 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록된다.
슬로우 쿼리 로그
슬로우 커리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다.
슬로우 쿼리 로그는 쿼리를 실행한 후 실제 소요된 시간을 기준으로 슬로우 쿼리 로그에 기록할지 여부를 판단하기 때문에 반드시 쿼리가 정상적으로 실행이 완료돼야 슬로우 쿼리 로그에 기록될 수 있다.
슬로우 쿼리 로그 파일에 기록되는 쿼리는 일단 정상적으로 실행이 완료됐고 실행하는 데 걸린 시간이 long_query_time에 정의된 시간보다 많이 걸린 쿼리인 것이다.
