Real MySQL 8.0 - 4장. 아키텍처

MySQL의 구조를 한 문장으로 줄이면 **"두뇌와 손발이 분리돼 있고, 둘은 정해진 창구로만 대화한다"**가 된다. 두뇌가 MySQL 엔진, 손발이 스토리지 엔진, 창구가 핸들러 API다. 이 분리가 MySQL을 다른 DBMS와 가장 다르게 만드는 지점이고, 동시에 다른 DBMS엔 없는 장점과 다른 DBMS엔 없는 골칫거리를 동시에 만든다.

그리고 솔직히 말하면 이 장의 8할은 InnoDB 내부 이야기다. 4.1이 큰 그림을 그리고, 4.2가 버퍼 풀·언두·리두를 파고들고, 4.3(MyISAM)과 4.4(로그 파일)는 상대적으로 짧다.

# 4.1 MySQL 엔진 아키텍처

# 두뇌와 손발, 그리고 핸들러 API

MySQL 서버는 크게 두 덩어리다.

MySQL 엔진 — 커넥션 핸들러, SQL 파서, 전처리기, 옵티마이저. 즉 "무엇을 어떻게 가져올지 정하는" 쪽이다.
스토리지 엔진 — 실제로 디스크에서 데이터를 읽고 쓰는 쪽이다.

책이 든 비유가 이해에 잘 붙는다. 옵티마이저가 경영진, 실행 엔진이 중간 관리자, 핸들러(스토리지 엔진)가 실무자다. 실행 엔진은 자기가 직접 일하지 않는다. "이 조건으로 레코드 읽어와"라고 핸들러에 요청하고, 받은 결과를 다음 요청의 입력으로 연결하는 역할만 한다.

MySQL 엔진은 하나뿐이지만 스토리지 엔진은 테이블마다 다르게 지정할 수 있다.

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

이렇게 만들면 이후 이 테이블의 모든 읽기·쓰기는 InnoDB가 담당한다. 이때 엔진과 스토리지 엔진 사이에 오가는 요청을 핸들러 요청이라 하고, 그 창구가 핸들러 API다. 이 창구가 몇 번 열렸는지는 상태 변수로 볼 수 있다.

SHOW GLOBAL STATUS LIKE 'Handler%';
-- Handler_read_key, Handler_read_next, Handler_write ...

지금은 그냥 넘어가도 되지만, Handler_로 시작하는 상태 변수는 나중에 옵티마이저와 실행 계획을 볼 때 계속 나온다. "MySQL 엔진이 스토리지 엔진에 몇 번 말을 걸었나"를 재는 눈금이라고 기억해두면 된다.

🐘 PostgreSQL 비교 — 이 "스토리지 엔진을 갈아끼운다"는 발상 자체가 MySQL 고유다. PostgreSQL은 스토리지가 하나(힙)뿐이라 ENGINE= 같은 선택지가 없다. PG 12부터 CREATE ACCESS METHOD로 테이블 접근 방식을 확장할 길이 열렸지만(zheap 등), 실무에서 고를 일은 없다고 봐도 된다.

# 스레딩 구조 — 쓰기는 미뤄도 읽기는 못 미룬다

MySQL은 프로세스가 아니라 스레드 기반으로 작동한다. 스레드는 포그라운드(= 클라이언트 = 사용자 스레드)와 백그라운드로 나뉜다.

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

실행해보면 대부분이 백그라운드고, 실제 사용자 요청을 처리하는 포그라운드는 몇 개 안 된다. 포그라운드 스레드는 접속한 클라이언트 수만큼 있다가, 커넥션이 끊기면 죽지 않고 스레드 캐시로 반환된다. 이 캐시에 유지되는 최대 개수가 thread_cache_size다.

여기서 이 절의 핵심 비대칭이 나온다. 쓰기는 미뤄도 되지만 읽기는 절대 미룰 수 없다.

쓰기: 사용자가 INSERT를 날리고 커밋하면, 실제 디스크 기록은 백그라운드(쓰기 스레드)가 나중에 해도 된다. 리두 로그만 남아 있으면 복구되니까.
읽기: SELECT는 "10초 뒤에 결과 줄게"가 성립하지 않는다. 지금 당장 디스크에서 읽어와야 한다.

그래서 innodb_write_io_threads는 넉넉히 잡을 이유가 있어도 innodb_read_io_threads는 많이 늘릴 필요가 없다. 일반 내장 디스크면 2~4개면 충분하고, DAS나 SAN처럼 좋은 스토리지를 쓸 때 디스크를 최대한 활용할 만큼 올린다.

참고로 MyISAM은 쓰기 버퍼링이 아예 없다. 포그라운드 스레드가 직접 디스크에 쓴다. 지연된 쓰기가 있긴 하지만 일반적으로 쓰는 방식은 아니다.

🐘 PostgreSQL 비교 — PostgreSQL은 커넥션당 프로세스를 fork한다. 그래서 커넥션 하나를 만드는 비용이 MySQL 스레드보다 훨씬 비싸고, thread_cache_size 같은 재사용 장치도 없다. PgBouncer 같은 외부 커넥션 풀러가 PG에서 사실상 필수인 게 이 때문이다.

# 메모리 — 글로벌은 한 번, 로컬은 커넥션 수만큼

메모리 영역은 여러 스레드가 공유하느냐로 갈린다.

글로벌 메모리 영역 — 스레드 수와 무관하게 하나만 할당되고 모든 스레드가 공유한다.

테이블 캐시
InnoDB 버퍼 풀
InnoDB 어댑티브 해시 인덱스
InnoDB 리두 로그 버퍼

로컬(세션) 메모리 영역 — 각 클라이언트 스레드 전용이고 절대 공유되지 않는다.

정렬(소트) 버퍼
조인 버퍼
바이너리 로그 캐시
네트워크 버퍼

여기가 사고가 나는 지점이다. 로컬 영역은 커넥션 수만큼 곱해진다. 소트 버퍼를 "넉넉하게" 잡았는데 커넥션이 수백 개 붙으면 그대로 메모리 부족으로 이어진다. 그래서 글로벌 영역은 좀 크게 잡아도 되지만 로컬 영역은 신중하게, 최악의 경우를 가정하고 잡아야 한다.

로컬 영역도 두 종류로 다시 갈린다.

커넥션이 열려 있는 내내 유지: 네트워크 버퍼
쿼리 실행하는 순간에만 할당됐다 해제: 소트 버퍼, 조인 버퍼

# 플러그인과 컴포넌트

MySQL에서 플러그인은 스토리지 엔진만이 아니다. 인증 방식(caching_sha2_password), 전문 검색 파서, 쿼리 재작성까지 전부 플러그인이다.

SHOW ENGINES;   -- Support 컬럼: YES / DEFAULT / NO / DISABLED
SHOW PLUGINS;   -- 스토리지 엔진 외 인증·감사 플러그인까지

SHOW ENGINES의 Support 값은 이렇게 읽는다.

YES: 서버에 포함돼 있고 사용 가능
DEFAULT: YES와 같은데 필수 스토리지 엔진 (없으면 MySQL이 시작조차 안 된다)
NO: 서버에 포함되지 않음
DISABLED: 포함은 됐지만 파라미터로 비활성화됨

그런데 플러그인에는 구조적 한계가 있었다.

플러그인은 오직 MySQL 서버하고만 대화할 수 있다. 플러그인끼리는 통신 불가.
서버의 변수나 함수를 직접 호출해서 캡슐화가 안 된다.
플러그인 간 의존 관계를 설정할 수 없어 초기화 순서를 못 정한다.

그래서 8.0이 컴포넌트를 내놨다. 대표 사례가 비밀번호 검증인데, 5.7에서는 플러그인이었던 게 8.0에서 컴포넌트로 바뀌었다.

INSTALL COMPONENT 'file://component_validate_password';
SELECT * FROM mysql.component;

기능 자체는 거의 같지만(시스템 변수 이름만 조금 다르다), 플러그인의 단점을 보완하려고 도입된 구조이니 앞으로 새로 나오는 확장은 컴포넌트 쪽이라고 보면 된다.

# 쿼리 실행 구조

쿼리 하나가 들어오면 이 순서로 흐른다.

쿼리 파서 — 문장을 토큰으로 쪼개 트리를 만든다. 문법 오류는 여기서 걸린다.
전처리기 — 토큰이 가리키는 테이블·칼럼·함수가 실제로 있는지, 접근 권한이 있는지 확인한다. 없거나 권한 없는 건 여기서 걸러진다.
옵티마이저 — 어떻게 하면 저렴하고 빠를지 결정한다. DBMS의 두뇌.
실행 엔진 — 계획대로 핸들러에 요청하고 결과를 이어붙인다.
핸들러(스토리지 엔진) — 실제로 디스크에서 읽고 쓴다.

# 쿼리 캐시는 8.0에서 사라졌다

쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시해뒀다가 같은 SQL이 오면 즉시 반환하는 기능이었다. 빠르긴 했다. 문제는 테이블이 하나라도 변경되면 그 테이블과 관련된 캐시를 전부 무효화해야 했다는 점이다. 이게 동시 처리 성능을 심각하게 갉아먹었고, 버전이 올라가며 버그의 원인이 되기도 했다.

결국 8.0에서 기능과 관련 시스템 변수가 모두 제거됐다. 저자는 이걸 잘한 선택이라고 본다. 쿼리 캐시가 이득인 환경(데이터 변경이 거의 없고 조회만 하는 서비스)이 현실에 흔치 않았기 때문이다.

🐘 PostgreSQL 비교 — PostgreSQL엔 이런 결과 캐시가 처음부터 없었다. MySQL이 8.0에서 걷어낸 걸 PG는 만든 적이 없는 셈이다. PG에서 비슷한 걸 원하면 애플리케이션 레벨이나 Redis 같은 외부 캐시를 쓴다.

# 스레드 풀 — 커뮤니티 에디션엔 없다

먼저 알아둘 것. MySQL 커뮤니티 에디션은 스레드 풀을 지원하지 않는다. 엔터프라이즈 에디션에 내장돼 있거나, Percona Server가 플러그인(thread_pool.so)으로 제공하는 걸 설치해야 한다.

스레드 풀은 사용자 요청을 처리하는 스레드 개수를 제한해서, 제한된 수의 스레드가 CPU를 최대한 잘 활용하게 만드는 기능이다. 주요 파라미터는 이렇다.

thread_pool_size: 스레드 그룹 개수. 기본적으로 CPU 코어 수와 맞추는 게 좋다.
thread_pool_oversubscribe: 처리할 요청이 몰릴 때 추가로 더 받아줄 개수(기본 3). 너무 크면 스케줄링 부담으로 오히려 비효율적이다.
thread_pool_stall_limit: 스레드 그룹의 작업이 이 시간(밀리초) 안에 안 끝나면 새 스레드를 추가한다. 응답 시간에 민감하면 낮추되, 0에 가깝게 두는 건 권장하지 않는다. 0은 사실상 스레드 풀을 안 쓰는 것과 같다.
thread_pool_max_threads: 전체 상한.

여기서 저자의 냉정한 평가를 기억해둘 만하다. 많은 사람이 스레드 풀을 깔면 성능이 두 배쯤 오를 거라 기대하지만, 실제 서비스에서 눈에 띄는 성능 향상을 보인 경우는 드물었다. 스레드 풀의 목적은 성능 향상이 아니라 CPU 자원 소모를 줄이는 것이다.

# 8.0의 트랜잭션 지원 메타데이터

조용하지만 큰 변화다. 5.7까지는 테이블 구조가 FRM 파일에, 스토어드 프로그램이 TRN·TRG·PAR 같은 파일에 저장됐다. 파일 기반이라 스키마 변경 도중 서버가 비정상 종료하면 일관되지 않은 어중간한 상태로 남았다. 사용자들이 "데이터 딕셔너리가 깨졌다"고 부르던 게 이 현상이다.

8.0은 이 메타데이터를 전부 InnoDB 테이블에 저장하도록 바꿨다. 그 결과 스키마 변경이 완전한 성공 아니면 완전한 실패로 정리된다. 시스템 테이블과 데이터 딕셔너리는 통째로 mysql.ibd 하나에 들어가니, 데이터 디렉터리에 있는 이 파일은 각별히 주의해야 한다.

InnoDB가 아닌 엔진(MyISAM, CSV 등)의 테이블은 메타 정보를 따로 둘 곳이 필요해서 SDI(.sdi, JSON 직렬화 포맷) 파일을 쓴다. 기존 .FRM과 같은 역할이다.

🐘 PostgreSQL 비교 — 8.0이 여기서 얻은 "원자적 DDL"은 PostgreSQL이 처음부터 갖고 있던 것이다. PG는 카탈로그(pg_class 등)가 애초에 일반 테이블이라 DDL이 MVCC 안에서 돈다. 심지어 PG는 DDL을 BEGIN/ROLLBACK으로 감쌀 수 있는데, MySQL은 8.0에서도 DDL이 암묵적 커밋을 일으킨다. 이 장이 자랑하는 개선이 PG 기준으론 출발선인 셈이다.

# 4.2 InnoDB 스토리지 엔진 아키텍처

InnoDB는 MySQL 스토리지 엔진 중 유일하게 레코드 기반 잠금을 제공한다. 그래서 동시성 처리가 뛰어나고 안정적이다.

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

InnoDB의 모든 테이블은 PK 순서로 디스크에 저장된다. 그리고 모든 세컨더리 인덱스는 레코드의 물리 주소 대신 PK 값을 논리적 주소로 갖는다.

이 때문에 PK 기반 접근이 상당히 빠르고, 옵티마이저가 PK를 다른 인덱스보다 비중 있게(가중치 높게) 취급한다. 오라클의 IOT(Index organized table)와 같은 구조다.

반면 MyISAM은 클러스터링이 없다. PK는 그냥 유니크 제약이 붙은 세컨더리 인덱스일 뿐이고, PK를 포함한 모든 인덱스가 물리적 주소값(ROWID)을 가진다.

🐘 PostgreSQL 비교 — PostgreSQL은 클러스터링이 없어서 오히려 MyISAM 쪽에 가깝다. 힙 테이블이라 PK도 그냥 유니크 인덱스고, 인덱스는 ctid라는 물리 주소를 가리킨다. CLUSTER 명령이 있긴 하지만 일회성 재정렬일 뿐 이후 유지되지 않는다. 그래서 "PK 조회가 구조적으로 빠르다"는 이 절의 이야기를 PG에 그대로 옮기면 안 된다.

# 외래 키 지원

외래 키는 InnoDB에서만 지원된다(MyISAM, MEMORY엔 없다). 주의할 점이 몇 가지 있다.

부모 테이블과 자식 테이블 양쪽 모두에 인덱스가 필요하다.
변경할 때마다 상대 테이블에 데이터가 있는지 체크하므로 잠금이 여러 테이블로 전파되고, 그래서 데드락이 발생하기 쉽다.

수동으로 데이터를 적재하거나 스키마 변경 같은 관리 작업을 할 때 외래 키가 걸림돌이 되면 일시적으로 끌 수 있다.

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

여기서 함정 두 개. 첫째, 체크를 껐다고 해서 부모-자식 관계가 깨져도 된다는 뜻이 아니다. 다시 켜기 전에 일관성은 직접 맞춰줘야 한다. 둘째 — 이게 더 놓치기 쉬운데 — 체크를 끄면 ON DELETE CASCADE, ON UPDATE CASCADE도 같이 무시된다. 부모를 지웠는데 자식이 그대로 남는다는 뜻이다.

이 변수는 GLOBAL과 SESSION 모두에 설정할 수 있으니, 작업할 때는 반드시 현재 작업을 실행하는 세션에서 꺼야 한다.

# MVCC와 언두 로그

MVCC(Multi Version Concurrency Control)의 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 것이다. InnoDB는 이걸 언두 로그로 구현한다.

동작을 따라가 보자. 이런 레코드가 있고,

INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울');
COMMIT;

여기에 UPDATE를 날리면,

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

버퍼 풀은 즉시 새 값(경기)으로 바뀌고, 이전 값(서울)은 언두 로그로 복사된다. 디스크의 데이터 파일은 아직 안 바뀌었을 수도 있고 이미 바뀌었을 수도 있다(백그라운드 쓰기 스레드가 언제 내려쓰는지에 달렸다).

이 상태에서 아직 커밋을 안 했는데 다른 커넥션이 이 레코드를 읽으면 뭐가 나올까? 격리 수준이 답을 정한다.

READ_UNCOMMITTED: 버퍼 풀의 변경 중인 값(경기)을 그대로 읽는다.
READ_COMMITTED 이상(REPEATABLE_READ, SERIALIZABLE): 언두 영역의 이전 값(서울)을 읽는다.

이 과정이 바로 MVCC다. 하나의 레코드에 대해 필요한 만큼 여러 버전이 동시에 관리된다.

커밋하면? InnoDB는 아무것도 하지 않는다. 이미 버퍼 풀엔 새 값이 있으니 그대로 확정이다. 롤백하면 언두의 백업을 버퍼 풀로 되돌린다.

여기가 이 장에서 가장 실무적인 대목이다. 커밋했다고 언두가 바로 삭제되는 게 아니다. 그 언두 영역을 필요로 하는 트랜잭션이 하나도 없을 때 비로소 삭제된다. 즉 트랜잭션을 오래 열어두면 언두가 계속 쌓이고, 그 언두를 스캔해야 하는 쿼리들이 느려지면서 서버 전체가 느려진다. 결론은 단순하다 — 트랜잭션은 시작했으면 최대한 빨리 커밋하거나 롤백하라.

🐘 PostgreSQL 비교 — 메커니즘이 정반대다. InnoDB는 최신 버전만 데이터 파일에 두고 이전 버전을 언두로 밀어낸다. PostgreSQL은 반대로 힙에 새 튜플을 덧붙이고 옛 튜플을 죽은 채로 남긴다. 그래서 PG엔 언두가 없는 대신 VACUUM이 필요하다. 재미있는 건 증상이 똑같다는 점이다. "오래 열린 트랜잭션이 언두 정리를 막아 InnoDB가 느려진다"는 여기 교훈은, PG에선 "오래 열린 트랜잭션이 VACUUM을 막아 테이블이 부풀어 오른다(bloat)"로 그대로 나타난다. 트랜잭션 빨리 닫으라는 결론은 양쪽이 같고 이유만 다르다.

# 잠금 없는 일관된 읽기

SERIALIZABLE이 아닌 격리 수준(READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ)에서 순수한 읽기(SELECT)는 잠금을 기다리지 않는다. 다른 트랜잭션이 그 레코드를 변경하고 아직 커밋을 안 했더라도, 변경 전 데이터를 언두에서 가져와 바로 반환한다.

이것도 결국 앞의 이야기와 같은 곳으로 간다. 오래 열린 트랜잭션 때문에 언두를 못 지우면 서버가 느려진다.

# 자동 데드락 감지

InnoDB는 잠금 대기 목록을 그래프(Wait-for List) 로 관리하고, 데드락 감지 스레드가 주기적으로 검사해서 교착에 빠진 트랜잭션을 찾아 강제 종료시킨다.

이때 누굴 죽일까? 언두 레코드를 더 적게 가진 쪽을 롤백한다. 언두가 적다는 건 롤백해도 처리할 내용이 적다는 뜻이고, 그만큼 서버 부하가 덜하기 때문이다.

문제는 동시 처리 스레드가 아주 많아질 때다. 트랜잭션마다 가진 잠금 개수가 많으면 감지 스레드가 검사해야 할 목록이 길어져 CPU를 잡아먹고, 잠금 목록 자체에 새로 잠금을 걸어야 해서 경합이 생긴다. 그래서 MySQL은 innodb_deadlock_detect=OFF로 감지를 끌 수 있게 해뒀는데 — 끄면 데드락에 빠진 트랜잭션이 무한정 대기하게 된다. 그래서 반드시 innodb_lock_wait_timeout(기본 50초)을 훨씬 낮은 값으로 같이 조정해야 한다.

책은 구글이 실제로 이렇게 쓴다고 소개한다. PK 기반 단순 조회·변경이 대부분이라 데드락이 애초에 거의 없고, 대신 동시 트랜잭션이 워낙 많아 감지 스레드가 성능을 갉아먹는 경우다.

🐘 PostgreSQL 비교 — PG도 데드락을 감지하지만 접근이 다르다. InnoDB처럼 주기적으로 그래프를 검사하는 게 아니라, 잠금을 기다리다 deadlock_timeout(기본 1초)이 지나야 그때 검사를 시작한다. 그래서 "감지 스레드가 CPU를 먹는다"는 이 절의 고민이 PG에선 잘 생기지 않는다. innodb_lock_wait_timeout에 대응하는 건 lock_timeout이다.

# 자동화된 장애 복구

InnoDB는 시작할 때 완료되지 못한 트랜잭션이나 일부만 기록된(Partial write) 페이지를 자동으로 복구한다. 자동으로 복구할 수 없는 손상이면 복구를 멈추고 서버가 종료된다.

이때 innodb_force_recovery를 1~6으로 설정해 강제로 띄운다. 숫자가 클수록 검사를 더 많이 건너뛰므로 데이터 손실 위험이 커진다. 반드시 1부터 올려가며 시도해야 한다.

값	의미	요지
1	`SRV_FORCE_IGNORE_CORRUPT`	손상된 페이지를 무시하고 시작
2	`SRV_FORCE_NO_BACKGROUND`	백그라운드 스레드 없이 시작
3	`SRV_FORCE_NO_TRX_UNDO`	언두를 데이터 파일에 적용하지 않음
4	`SRV_FORCE_NO_IBUF_MERGE`	인서트 버퍼 병합을 하지 않음
5	`SRV_FORCE_NO_UNDO_LOG_SCAN`	언두 로그를 모두 무시
6	`SRV_FORCE_NO_LOG_REDO`	리두 로그도 모두 무시

innodb_force_recovery가 0이 아니면 SELECT 외의 INSERT·UPDATE·DELETE는 수행할 수 없다. 그러니 일단 뜨면 mysqldump로 최대한 뽑아내고 DB를 다시 만드는 것이 정석이다.

# 버퍼 풀 — InnoDB의 심장

버퍼 풀은 InnoDB에서 가장 핵심적인 부분이다. 디스크의 데이터와 인덱스를 메모리에 캐시하고, 동시에 쓰기 작업을 지연시켜 일괄 처리하는 버퍼 역할도 겸한다. 랜덤한 디스크 쓰기를 모아서 처리하니 횟수가 줄어든다.

# 크기 설정 — "80%"는 근거 없다

인터넷에 "전체 메모리의 80%를 버퍼 풀에" 같은 글이 많지만 저자는 그렇게 단순하지 않다고 못 박는다. 운영체제와 각 클라이언트 스레드가 쓰는 메모리도 무시할 수 없기 때문이다.

권장은 이렇다.

운영체제 전체 메모리가 8GB 미만이면 50% 정도만
그 이상이면 50%에서 시작해 조금씩 올려가며 최적점을 찾는다
예: 50GB 이상이라면 대략 15GB를 OS와 다른 프로그램에 남기고 나머지를 버퍼 풀로

5.7부터는 동적으로 크기 조절이 가능해졌다. 다만 128MB 청크 단위로 옮기는 무거운 작업이라 서비스 중에 바꾸는 건 크리티컬하고, 특히 줄이는 작업은 하지 않는 게 좋다.

innodb_buffer_pool_instances로 버퍼 풀을 여러 개로 쪼개면 내부 잠금 경합을 줄일 수 있다. 기본값은 8인데 전체 크기가 1GB 미만이면 1개만 생성된다. 40GB 이하면 기본값을 그대로 두고, 그보다 크면 인스턴스당 5GB 정도가 되게 조정한다.

# 구조

버퍼 풀은 innodb_page_size 조각으로 쪼개 관리되고, 세 개의 자료 구조가 있다.

LRU 리스트: 디스크에서 읽은 페이지를 최대한 오래 유지해 디스크 읽기를 줄인다. 실제로는 LRU와 MRU가 결합된 형태다.
플러시 리스트: 디스크와 동기화되지 않은 페이지(더티 페이지) 목록.
프리 리스트: 아직 사용자 데이터로 채워지지 않은 빈 페이지.

Read Ahead로 미리 읽어둔 페이지가 정작 쿼리에 안 쓰이면 나이를 먹어(Aging) LRU 끝으로 밀려나고 결국 제거된다.

# 데이터를 찾는 과정 — LRU 리스트는 순수 LRU가 아니다

책이 4단계 목록으로 짧게 지나가는데, 왜 이렇게 복잡한지를 모르면 외울 수밖에 없어서 순서대로 짚었다.

비유부터. 버퍼 풀은 책상이고 디스크는 창고다. 창고까지 다녀오는 게 느리니 한 번 꺼낸 자료는 최대한 책상에 오래 둔다. 책상은 좁으니 언젠가 뭔가를 돌려보내야 하고, "제일 오래 안 쓴 걸 치운다"가 LRU다.

문제는 순진한 LRU가 대량 읽기에 무너진다는 것이다. 새로 꺼낸 자료를 무조건 책상 정중앙(= 가장 최근 위치)에 놓는다면, 풀 테이블 스캔 한 번에 한 번 쓰고 버릴 자료 수만 장이 정중앙을 차지하면서 하루 종일 쓰던 자료를 전부 창고로 밀어낸다. 스캔 한 방에 캐시가 통째로 오염된다.

그래서 InnoDB는 책상을 두 구역으로 나눈다. 안쪽 Young(New) 서브리스트가 단골 구역, 바깥쪽 Old 서브리스트가 임시 보관 구역이다. 새로 읽은 페이지는 정중앙이 아니라 Old의 헤드에 놓는다. 이게 midpoint insertion strategy이고, 기본적으로 Old가 전체의 37%(innodb_old_blocks_pct)다. 위에서 "LRU와 MRU가 결합된 형태"라고 한 게 이 구조다.

승격 규칙이 핵심이다. Old에 놓인 페이지가 한 번 더 읽히면 그때 Young의 헤드로 옮긴다(Make Young). 한 번 읽고 마는 스캔 페이지는 Old 구역에서만 놀다 밀려나 사라지고, Young의 단골 페이지는 건드려지지 않는다.

이걸 알고 책의 4단계를 다시 보면 말이 된다.

버퍼 풀에 있나 확인 — 어댑티브 해시 인덱스로 먼저 찾고, 없으면 B-Tree를 탄다. 그래도 없으면 디스크에서 읽어 Old의 헤드에 적재한다.
Old의 페이지가 실제로 읽히면 Young의 헤드로 이동(Make Young).
오래 안 쓰이면 Old의 테일로 밀려나다 결국 제거(Eviction).
자주 접근된 페이지의 키는 어댑티브 해시 인덱스에 추가 — 다음엔 B-Tree 탐색조차 건너뛴다.

# "적재됐지만 안 쓰인다"와 "스캔이라서 여러 번 읽힌다"는 다른 문제다

바로 위의 "Read Ahead로 미리 읽어둔 페이지가 정작 쿼리에 안 쓰이면"이라는 문장을 동일 페이지의 레코드를 연속으로 읽는 케이스로 착각했는데, 둘은 완전히 다른 문제고 방어선도 다르다.

① 리드 어헤드 — 적재는 됐는데 아예 안 읽힌다. InnoDB는 익스텐트(64페이지) 안에서 56페이지(innodb_read_ahead_threshold) 이상이 순차 접근되면 다음 익스텐트 64페이지를 통째로 미리 당겨온다. 쿼리가 그 지점에 도달했을 때 디스크를 안 기다리게 하려는 것이다. 그런데 이건 추측이라서, LIMIT이 걸렸거나 WHERE 조건에 스캔이 중간에 끝나면 당겨온 64페이지는 단 한 번도 읽히지 않고 사라진다.

여기서 적재(load) ≠ 읽힘(access) 이라는 게 중요하다. 4단계의 2번이 굳이 "실제로 읽히면"이라고 쓴 이유가 이것이다. 승격 기준은 적재가 아니라 읽힘이라서, 리드 어헤드가 페이지를 아무리 쏟아부어도 전부 Old에만 쌓이다 밀려난다.

② innodb_old_blocks_time — 읽히긴 하는데 스캔이라서 읽힌다. 착각했던 그 케이스가 여기다. 페이지 하나에 레코드가 수십 개니, 풀 스캔이 그 페이지를 훑는 동안 접근 횟수가 실제로 올라간다. 진짜 재접근이 맞으니 midpoint 규칙만으로는 못 거른다. 그래서 시간 축을 하나 더 건다 — 첫 접근 후 1초(기본 1000ms) 안의 접근은 승격으로 쳐주지 않는다. 스캔의 연타는 밀리초 단위로 끝나니 전부 무시되고, 1초 뒤에도 또 찾는다면 그건 스캔이 아니라 진짜 재사용이다.

	적재됨	읽힘	막는 장치
리드 어헤드가 당겨온 페이지	O	X (아예 안 읽힘)	midpoint insertion
풀 스캔이 훑는 페이지	O	O (짧은 시간에 여러 번)	`innodb_old_blocks_time`

정리하면 midpoint insertion이 "안 읽힌 페이지"를 거르고, innodb_old_blocks_time이 "읽히긴 했는데 스캔이라서 읽힌 페이지"를 거른다. 후자가 전자의 구멍을 메우는 순서다.

실제로 알아야 할 건 한 문장이다 — InnoDB의 LRU는 스캔 저항성(Scan Resistance) 을 위해 midpoint insertion을 쓴다. 3/8 지점에 넣고, 재접근해야 승격시키고, 1초 유예를 둔다. 이 셋이 전부 "대량 스캔이 캐시를 밀어내지 못하게"라는 하나의 목적을 향한다. innodb_old_blocks_pct나 innodb_old_blocks_time을 직접 튜닝할 일은 거의 없으니 이런 손잡이가 있다는 것만 기억하면 된다.

🐘 PostgreSQL 비교 — 책상을 정리하는 방식이 아예 다르다. InnoDB는 페이지를 한 줄로 세워놓고 "단골 구역"과 "임시 구역"으로 나눴지만, PG의 버퍼 캐시(shared_buffers)는 줄을 세우지 않는다. 대신 페이지마다 인기 점수(0~5점) 를 붙여두고, 시계 바늘처럼 생긴 포인터가 책상을 빙글빙글 돌면서 지나치는 페이지마다 점수를 1점씩 깎는다. 그러다 점수가 0인 페이지를 만나면 그 자리를 내주게 한다. 페이지가 읽힐 때마다 점수는 다시 차오르니, 자주 쓰이는 페이지는 바늘이 몇 바퀴를 돌아도 살아남고 한 번 쓰고 만 페이지는 금방 0이 되어 밀려난다. 결과적으로 LRU와 비슷한 효과인데, 페이지를 리스트 앞뒤로 옮기는 작업이 없어서 그만큼 내부 경합이 적다는 게 장점이다. 이 방식을 clock sweep이라 부른다.

대량 스캔을 막는 수단도 정반대다. InnoDB가 "새로 읽은 페이지는 임시 구역에 놔라"라고 책상을 구역으로 나눠서 막는다면, PG는 스캔에게 좁은 전용 책상을 따로 내준다. 버퍼 캐시 전체의 1/4보다 큰 순차 스캔이 들어오면 256KB짜리 작은 공간만 배정하고 스캔은 그 안에서만 페이지를 돌려쓰게 한다. 캐시 전체를 건드릴 기회 자체를 안 주는 셈이다. 목적은 같고 수단이 반대다.

그리고 PG엔 리드 어헤드가 오랫동안 아예 없었다. 미리 당겨오는 일을 엔진이 하지 않고 운영체제에 통째로 맡겨뒀다. PG가 OS의 파일 캐시에 기대는 설계라 가능했던 선택인데, 클라우드 디스크처럼 한 번 읽는 데 오래 걸리는 환경에선 약점이었다. PG 17과 18에 와서야 엔진이 직접 미리 읽는 기능이 들어오는 중이다. 어댑티브 해시 인덱스에 해당하는 것도 없어서 PG는 매번 B-Tree를 탄다.

# 버퍼 풀과 리두 로그의 관계 — 이 절의 하이라이트

버퍼 풀은 리두 로그와 매우 밀접하다. 그런데 버퍼 풀을 무작정 키운다고 쓰기 버퍼링이 좋아지지 않는다. 왜 그런지 이해하려면 리두 로그가 어떻게 생겼는지부터 봐야 한다. (리두 로그 자체는 4.2.11에서 다룬다. 여기선 "디스크에 아직 못 쓴 변경 사항을 순차로 덧붙여 적어두는 일지" 정도로 충분하다.)

리두 로그는 "1개 이상의 고정 크기 파일을 연결해서 순환고리처럼" 쓴다. 로그인데 고정 크기라는 게 이상하게 들린다 — 꽉 차면 옛날 기록이 사라질 텐데? 리두 로그가 보관용 기록이 아니라서 성립하는 구조다. 리두 로그의 유일한 임무는 "커밋됐다"와 "디스크 데이터 파일에 실제로 반영됐다" 사이의 시간차를 버티는 것이다. 어떤 변경이 데이터 파일에 제대로 기록되고 나면 그 변경의 리두 기록은 그 순간 쓰레기가 된다. 크래시가 나도 이미 디스크에 있으니 복구할 게 없다. 그러니 리두 로그가 감당할 최대량은 전체 역사가 아니라 "아직 데이터 파일에 반영 안 된 작업량" 뿐이고, 이건 유한하다. 다 쓴 자리는 재사용하면 된다.

원형 트랙을 상상하면 정확하다. 트랙 위를 두 명이 같은 방향으로 돈다.

앞선 주자 = 쓰기 지점. 변경이 생길 때마다 일지를 적으며 나아간다.
뒤따르는 주자 = 체크포인트. 더티 페이지가 디스크에 반영될 때마다 "여기까진 정리 끝"이라며 따라 붙는다.

두 주자 사이의 구간이 곧 "아직 정리가 안 끝난 작업" 이고, 이게 활성 리두 로그(Active Redo Log) 다. 이 구간은 절대 덮어쓰면 안 된다 — 덮어쓰는 순간 "디스크엔 아직 없는데 메모도 사라진" 변경이 생기고, 그게 곧 데이터 유실이다. 반대로 체크포인트 주자가 이미 지나간 뒤쪽은 전부 재사용 가능한 빈 공간이다.

뒷주자를 앞으로 미는 힘이 무엇인지가 제일 헷갈린다. 리두 로그를 디스크에 쓰는 게 아니다 — 리두 로그는 커밋 시점에 이미 디스크에 다 쓰여 있다. 그게 애초에 "커밋했다"고 대답할 수 있었던 근거다. 체크포인트를 전진시키는 건 버퍼 풀의 더티 페이지를 데이터 파일에 내리는 작업이다. 페이지 클리너가 더티 페이지를 제자리에 갖다 놓으면 그 페이지에 해당하는 리두 구간이 그제서야 쓸모없어지고, 그만큼 체크포인트가 나간다. 즉 디스크 쓰기가 두 종류인데 서로 완전히 다른 일이다.

	언제	어디에	성격
리두 로그 쓰기	커밋 시점 (이미 끝남)	리두 로그 파일	순차, 작음, 빠름
더티 페이지 플러시	나중에, 백그라운드	데이터 파일	랜덤, 큼, 느림 ← 이게 뒷주자를 민다

이 구분이 무너지면 WAL의 존재 이유 자체가 무너진다. "리두 로그를 디스크에 써야 체크포인트가 전진한다"면, 어차피 디스크에 쓸 거 처음부터 데이터 파일에 직접 쓰면 되지 않나? 리두 로그가 있는 이유는 정확히 그 반대다 — 느린 창고 정리(랜덤 쓰기)를 커밋 경로에서 빼내 나중으로 미루고, 빠른 일지 쓰기(순차)만 커밋 경로에 남긴 것이다. 그래서 트랙이 꽉 차 쓰기가 멈추는 상황은 "미뤄둔 창고 정리가 한계까지 밀렸다" 는 뜻이지 일지 쓰기가 밀린 게 아니다. 위에서 본 innodb_io_capacity나 innodb_page_cleaners가 이 문제와 직결되는 것도 그래서다 — 전부 뒷주자를 얼마나 빨리 달리게 할 것인가의 손잡이다.

여기서 LSN(Log Sequence Number)이 등장한다. 체크포인트가 발생하면 그 시점의 LSN이 기록되고, 최근 체크포인트 LSN과 마지막 리두 엔트리 LSN의 차이 = 두 주자 사이의 거리 = 체크포인트 에이지다. 체크포인트보다 작은 LSN을 가진 더티 페이지는 모두 디스크로 동기화돼야 한다. 참고로 LSN 자체는 순환하지 않는다. 64비트로 증가만 하고, 그 값을 트랙 길이로 나눈 나머지가 실제 파일 위치가 된다. 논리적으로는 무한한 번호표, 물리적으로만 재사용인 구조다.

여기서 방향을 뒤집기 쉽다 — 체크포인트 에이지는 "쓸 수 있는 공간"이 아니라 그 반대다. 이름이 시간처럼 들리는데 실제 단위는 바이트라 공간으로 읽게 되고, 그러다 뒤집힌다. 정확히는 덮어쓰면 안 되는 구간이고, 가용 공간은 그 나머지다.

전체 트랙 (고정, innodb_redo_log_capacity)
├── 체크포인트 에이지 = 활성 리두 로그  → 덮어쓰기 ✗
└── 나머지                              → 재사용 가능 ✓

가용 공간 = 전체 트랙 − 체크포인트 에이지

즉 에이지가 커질수록 가용 공간은 줄어든다. 둘은 같은 게 아니라 서로를 갉아먹는 관계다. 한마디로 정리하면 체크포인트 에이지는 "지금 당장 서버가 죽으면, 재시작할 때 다시 실행해야 할 작업량" 이다. 이 한 문장에서 나머지가 전부 따라 나온다 — 왜 못 덮어쓰는가(덮어쓰면 복구할 재료가 사라지니까), 왜 리두가 크면 복구가 느린가(에이지가 커질 수 있는 만큼 다시 실행할 양도 느니까), 왜 이게 더티 페이지 양의 상한인가(반영 안 된 작업 = 더티 페이지니까).

이름의 유래도 여기 있다. "마지막 체크포인트가 얼마나 오래된 것이냐" 를 재는데, 그 척도가 시계가 아니라 "그 이후로 리두를 몇 바이트나 썼냐" 다. 체크포인트를 찍은 뒤로 일을 많이 벌였을수록 그 체크포인트는 낡은 것이 된다. 그래서 나이다.

두 주자는 직접 볼 수 있다.

SHOW ENGINE INNODB STATUS\G

---
LOG
---
Log sequence number          1234567890   ← 앞선 주자 (쓰기 지점)
Last checkpoint at           1234000000   ← 뒤따르는 주자 (체크포인트)

이 둘의 차이가 체크포인트 에이지다. 부하를 걸면 벌어지고, 플러시가 따라잡으면 좁아지는 게 눈에 보인다.

"1개 이상의 파일을 연결해서" 는 물리적으로는 파일 여러 개, 논리적으로는 고리 하나라는 뜻이다. 기본은 ib_logfile0, ib_logfile1 두 개이고, 0번을 채우면 1번으로 넘어가고 1번도 차면 다시 0번 맨 앞으로 돌아와 덮어쓴다. 파일 경계는 InnoDB 입장에서 의미 없는 이음매다. 굳이 나눈 건 옛 파일시스템의 크기 제한과 관리 편의 때문이지 구조적 이유가 아니다. 그래서 8.0.30부터는 innodb_log_file_size와 innodb_log_files_in_group이 사라지고 innodb_redo_log_capacity 하나로 총 용량만 지정하게 바뀌었다(파일 쪼개기는 InnoDB가 알아서 한다). 책은 8.0 초기 기준이라 옛 변수로 설명한다.

트랙이 꽉 찬다는 건 무슨 뜻인가. 쓰기 주자가 한 바퀴를 돌아 뒤에서 체크포인트 주자를 따라잡으려는 순간이다. 더 쓰면 덮어쓰게 되니 InnoDB는 어떻게든 막아야 하고, 방법은 하나뿐이다 — 미친 듯이 더티 페이지를 디스크로 밀어 넣어 체크포인트 주자를 강제로 앞으로 밀어낸다. 이때 사용자 쿼리는 멈춰 선다.

이걸 알고 나면 책의 사고 실험이 명쾌해진다.

버퍼 풀 100GB + 리두 로그 100MB인 경우: 트랙이 100MB짜리라 두 주자 사이 간격도 최대 100MB다. 체크포인트 에이지가 100MB를 못 넘으니 더티 페이지도 100MB어치(16KB 페이지 기준 약 6,400개)밖에 못 가진다. 버퍼 풀이 100GB여도 쓰기 버퍼링 효과가 사실상 없다.
버퍼 풀 100MB + 리두 로그 100GB인 경우: 트랙이 아무리 길어도 책상이 좁다. 어차피 더티 페이지는 100MB가 한계다.

둘 다 좋은 설정이 아니다. 리두 로그가 부족하면 위에서 본 "따라잡히기 직전의 발작적 플러시"가 자주 터지고, 리두가 너무 크면 크래시 후 읽어야 할 구간이 길어져 복구 시간이 늘어난다. 결론은 리두 로그를 대략 5~10GB 수준에서 시작해 필요할 때 조금씩 늘려가며 최적값을 찾는 것이다.

🐘 PostgreSQL 비교 — PG의 WAL은 고리를 돌지 않는다. 트랙이 아니라 앞으로만 뻗는 직선 도로에 가깝다. 16MB짜리 파일을 계속 새로 만들어 나간다(이유는 4.2.11에서 다룬다 — PG의 WAL은 복구 전용이 아니라 복제와 시점 복구까지 겸해서, 디스크 반영이 끝났어도 함부로 버릴 수가 없다). 물론 PG도 무한정 쌓아두진 않고 필요 없어진 파일은 이름만 바꿔 재활용한다.

여기서 재밌는 대칭이 생긴다. "밀린 작업이 감당 못 할 만큼 쌓였다"는 똑같은 압력을 두 DB가 정반대 방식으로 받아낸다. InnoDB는 트랙 길이가 정해져 있으니 디스크는 안 차는 대신 쿼리가 멈춰 선다. PG는 멈출 트랙이 없으니 쿼리는 계속 돌아가는 대신 WAL 파일이 계속 불어나 디스크를 채운다(복제본이 뒤처지거나 아카이빙이 밀릴 때 실제로 터지는 장애다). 같은 문제를 한쪽은 지연으로, 한쪽은 용량으로 청구받는 셈이다.

# 버퍼 풀 플러시

5.6까지는 더티 페이지 플러시가 부드럽지 못해서 갑작스러운 디스크 쓰기 폭증이 있었지만, 5.7~8.0으로 오면서 대부분 해결됐다. 특별히 성능 문제가 없다면 굳이 손댈 필요 없는 변수들이지만 이름은 알아두면 좋다.

innodb_page_cleaners: 더티 페이지를 디스크로 내리는 클리너 스레드 개수. innodb_buffer_pool_instances와 동일하게 맞추는 게 좋다.
innodb_max_dirty_pages_pct: 더티 페이지 최대 비율(기본 90%). 가능하면 기본값 유지.
innodb_max_dirty_pages_pct_lwm: 기본 10%. 더티 비율이 낮은데도 디스크 쓰기가 많이 발생하면 조금 높여본다.
innodb_io_capacity / innodb_io_capacity_max: 디스크 읽고 쓰기 용량. 주의 — 현재 디스크가 초당 1000 IOPS를 처리한다고 이 값을 그대로 1000으로 넣으면 안 된다. 이 값은 백그라운드 스레드의 작업량이고, 대부분 버퍼 풀의 더티 페이지 쓰기라 사용자 쿼리 처리에 해당하지 않는다.
innodb_adaptive_flushing: 기본 ON. 켜져 있으면 io_capacity에 의존하지 않고 자체 알고리즘(리두 로그 증가 속도 분석)으로 조절한다.
innodb_flush_neighbors: 더티 페이지를 쓸 때 근처 페이지 중 더티인 걸 같이 묶어서 쓴다. HDD면 1이나 2, SSD면 기본값 0(비활성)을 유지하는 게 좋다.

🔧 곁가지 — SSD엔 왜 seek가 없나 flush neighbors는 "흩어진 랜덤 쓰기를 근처끼리 묶어 순차처럼 만들면 빠르다"는 HDD의 전제 위에서만 이득이 나는 기능이다. HDD는 빙글빙글 도는 자기 원판(platter)과 그 위를 오가는 기계식 헤드로 되어 있어, 데이터를 찾을 때마다 헤드를 트랙까지 물리적으로 옮기고(seek), 원하는 섹터가 헤드 밑으로 돌아올 때까지 원판 회전을 기다리는(회전 지연) 기계 동작이 붙는다 — LP판에서 특정 곡에 바늘을 갖다 대고 판이 그 지점까지 돌기를 기다리는 것과 같다. 흩어진 쓰기를 뭉치면 이 seek 왕복이 줄어 랜덤 쓰기가 준(準)순차처럼 되니 이득이다. 반면 SSD는 원판도 헤드도 없는 NAND 플래시(반도체) 라, 어느 위치든 전자적 주소 지정으로 바로 접근한다(접근 시간이 ms가 아니라 µs 단위다). 움직일 부품이 없으니 seek라는 개념 자체가 존재하지 않고, 랜덤 ≈ 순차다. 뭉쳐서 아낄 seek가 0인데, 안 급한 이웃 페이지까지 미리 쓰면 NAND 특유의 "덮어쓰지 못하고 먼저 지운 뒤 써야 하는" 제약 탓에 쓰기 증폭(Write Amplification) 부담만 늘어난다. 그래서 SSD 기본값이 0(비활성)인 것이다.

참고로 NAND의 저장 원리는 자기(磁氣)가 아니라 전하(電荷) 다 — 자석의 N/S로 기록하는 건 HDD고, NAND는 절연막으로 막힌 플로팅 게이트에 전자를 가둬(전원이 꺼져도 안 빠져나가니 비휘발성) 그 유무로 0/1을 담는다. 또 OS가 보는 주소가 곧 물리 셀은 아니다 — 중간의 FTL(Flash Translation Layer) 이 논리 주소를 실제 셀에 매핑하고, 덮어쓰기 불가·웨어 레벨링 때문에 같은 논리 주소라도 물리 위치는 계속 옮겨 다닌다. 즉 seek가 없다는 건 "논리 주소 기준 랜덤 접근이 일정하다"는 뜻이지, 물리 위치가 고정이라는 뜻은 아니다.

# 이 변수들이 실제로 어떻게 맞물리는가

이름만 봐선 서로 어떻게 얽히는지 안 보인다. 출발점은 딱 하나다 — 더티 페이지엔 "들어오는 흐름"과 "빠지는 흐름" 두 개가 있고, 둘은 완전히 별개라는 것.

들어오는 흐름 = 더티 페이지 발생. UPDATE 한 방이면 책상 위 페이지가 고쳐져 더티가 된다. 이 속도를 막는 변수는 없다. 트랜잭션이 페이지를 고치겠다는데 "더티 한도 초과라 안 돼"라고 거절할 방법이 없다 — 수정은 반드시 메모리에서 먼저 일어나기 때문이다.
빠지는 흐름 = 페이지 클리너의 플러시. 이 속도의 상한이 io_capacity다.

발생 > 플러시면 책상에 더티가 쌓인다. "디스크로 기록되는 것보다 더 많은 더티 페이지가 생기면 계속 증가한다" 는 말이 정확히 이거다. 발생 쪽엔 밸브가 없으니, 조절할 수 있는 건 오로지 빠지는 쪽뿐이다.

여기서 io_capacity를 오해하기 쉽다. 이건 "이 값 넘으면 플러시 시작"하는 스위치가 아니라, "초당 이만큼까지만 써라"라는 배수구 굵기다. 넘으면 쓰는 게 아니라 그게 곧 쓰는 속도의 상한이다. 그래서 이 값을 워크로드보다 얇게 잡으면 폭발을 막기는커녕 오히려 더티가 더 잘 쌓인다. (그래서 위 목록에서 "IOPS 1000이라고 1000 넣지 말라"고 못 박은 것이다.)

그리고 플러시는 스위치가 아니라 항상 돌아가는 중이다. 페이지 클리너 스레드는 대략 1초에 한 번 루프를 돌며 "이번 라운드에 몇 개 쓸까"를 정한다. 이 개수가 흔히 말하는 "플러시 강도"다.

강도 낮음:  이번 라운드 → 20개만 써
강도 높음:  이번 라운드 → 200개 써       ← io_capacity가 여기 상한
비상:       이번 라운드 → 2000개 몰아 써  ← io_capacity_max까지 풀림 = "쓰기 폭발"

무엇을 보고 개수를 정하나? adaptive_flushing이 두 가지를 본다 — 리두 로그가 얼마나 빨리 차는가, 그리고 더티 비율이 lwm~max 어디쯤인가. 여기서 lwm과 max의 역할이 갈린다.

lwm(기본 10%) = 미리 강하게 밀기 시작하는 선. 이 선을 넘으면 강도를 조금씩 올리기 시작한다.
max(기본 90%) = 천장. 여기 닿으면 있는 힘껏(io_capacity_max까지) 민다.

더티 비율:  0% ──── lwm(10%) ──────────────── max(90%) ──── 100%
                    │ 여기부터 강도 ↑           │ 여기서 최대 강도
평소 ───────────────┤   (adaptive flushing)     │
                    └───────────────────────────┘ ← 90% 닿기 전에 눌러줌

lwm이 없다고 상상하면, 89%든 50%든 평소 페이스로만 빼다가 90% 찍는 순간 발작적으로 몰아치는 절벽이 된다. lwm은 그 절벽을 완만한 경사로 바꿔 90%에 닿기 전에 눌러준다.

그럼 max를 낮게(예: 30%) 잡으면? 결과적으로 더티를 적게 유지해 폭발 위험은 준다. 하지만 공짜가 아니라 트레이드오프다.

`max_dirty_pages_pct`	장점	단점
높게(90%)	같은 페이지 여러 번 수정을 모아 한 번에 플러시 → 디스크 쓰기 횟수 ↓	더티 많이 쌓임 → 폭발 위험, 크래시 복구 오래
낮게(30%)	더티 적게 유지 → 폭발·복구 위험 ↓	배칭 이득 상실 → 디스크 쓰기가 잦아짐

낮게 잡는 건 "90%까지 안 쌓이게 예방"이 아니라 천장 자체를 30%로 내리는 것이다. 대신 더티를 못 모으니 쓰기가 잦아진다.

마지막으로 이 조절을 누가 하나. CPU라는 하드웨어가 아니라 페이지 클리너라는 소프트웨어 스레드다. "몇 개 쓸까"를 정하는 계산은 CPU에서 도는 가벼운 로직이고, 실제 쓰기의 부담은 디스크 I/O에 걸린다. 그래서 플러시가 느린 근본 원인도, io_capacity를 맞추는 기준도 CPU가 아니라 스토리지의 초당 I/O 능력이다.

실제로 알아야 할 건 이렇게 갈린다 — 언제 세게 플러시할지는 리두 로그 속도와 lwm/max가 정하고, 얼마나 빨리 쓸지는 io_capacity가 정한다. adaptive_flushing이 ON이면 이 계산을 InnoDB가 알아서 하니 대부분 손댈 일이 없고, 손대야 할 상황이 오면 "발생은 못 막으니 빠지는 쪽(io_capacity)을 스토리지에 맞게 키운다" 가 기본 방향이다.

🐘 PostgreSQL 비교 — 구조는 똑같다. bgwriter/checkpointer가 shared_buffers의 더티를 내리고, 체크포인트가 몰리면 I/O 스파이크가 난다. PG는 이걸 checkpoint_completion_target으로 플러시를 체크포인트 주기 내내 시간에 걸쳐 분산시켜 완화한다. InnoDB의 adaptive flushing이 매 라운드 개수를 다시 계산해 스파이크를 눌러주는 것과 목적은 같고, 한쪽은 "얼마나 넓게 펴 바를까(시간)", 한쪽은 "이번 초에 몇 개 쓸까(개수)" 로 손잡이의 결이 다를 뿐이다.

# 워밍업 — 버퍼 풀 백업과 복구

버퍼 풀이 데이터로 채워진 상태를 워밍업(Warming Up) 이라 한다. 워밍업된 서버는 그렇지 않은 서버보다 몇십 배 빠를 수 있다. 서버를 재시작하면 이 상태가 날아가서, 예전엔 서비스 오픈 전에 주요 테이블을 풀 스캔해 강제로 데우곤 했다.

5.6부터는 그냥 백업하고 복구하면 된다.

-- 셧다운 전에 버퍼 풀 상태 백업
SET GLOBAL innodb_buffer_pool_dump_now=ON;
-- 재시작 후 복구
SET GLOBAL innodb_buffer_pool_load_now=ON;

수동으로 하려면 잊어버리기 쉬우니 설정 파일에 innodb_buffer_pool_dump_at_shutdown과 innodb_buffer_pool_load_at_startup을 넣어두면 자동화된다.

백업 파일은 데이터 디렉터리의 ib_buffer_pool인데, 크기가 아주 작다(몇 MB). 실제 페이지가 아니라 LRU 리스트에 적재된 페이지의 메타 정보만 저장하기 때문이다. 그래서 복구할 때는 그 메타 정보로 디스크에서 다시 읽어와야 해서 시간이 꽤 걸린다. 진행 상황은 이렇게 본다.

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'\G
-- Value: Buffer pool(s) dump completed at 200712 23:38:58

복구가 너무 오래 걸려 중간에 멈추려면 innodb_buffer_pool_load_abort=ON을 쓴다.

# 적재 내용 확인

8.0부터 information_schema.innodb_cached_indexes가 생겨서 테이블의 인덱스별로 몇 개 페이지가 적재됐는지 확인할 수 있다.

SELECT it.name table_name, ii.name index_name, ici.n_cached_pages
  FROM information_schema.innodb_tables it
  INNER JOIN information_schema.innodb_indexes ii ON ii.table_id = it.table_id
  INNER JOIN information_schema.innodb_cached_indexes ici ON ici.index_id = ii.index_id
 WHERE it.name = CONCAT('employees','/','employees');

# "인덱스별로 페이지가 적재된다"가 무슨 뜻인가

이 숫자를 제대로 읽으려면 "왜 페이지가 인덱스별로 나뉘는지" 부터 짚어야 한다. 앞의 «프라이머리 키에 의한 클러스터링»과 이어지는 이야기다.

InnoDB에선 테이블 데이터가 평평한 더미로 있지 않다. PK 기준으로 정렬된 B+Tree(클러스터 인덱스)로 조직돼 저장되고, 실제 행은 그 트리의 잎(leaf) 페이지 안에 산다. 즉 "테이블 데이터 = PK B+Tree의 잎 페이지" 다. 세컨더리 인덱스를 하나 걸면 그 인덱스만을 위한 별개의 B+Tree가 하나 더 생긴다(잎엔 인덱스 컬럼 값 + PK만). 그래서 users(PK + email + age) 테이블은 디스크에 B+Tree 3개로 존재한다.

users 테이블 = B+Tree 3개
  ├─ PK(클러스터) B+Tree   ← 잎에 실제 행 전체
  ├─ email 세컨더리 B+Tree  ← 잎에 (email + PK)
  └─ age 세컨더리 B+Tree    ← 잎에 (age + PK)

핵심은 버퍼 풀이 레코드가 아니라 16KB 페이지 단위로 캐싱한다는 것, 그리고 올라온 페이지는 반드시 이 세 B+Tree 중 하나에 소속된다는 것이다. 그러니 버퍼 풀의 페이지들을 소속 트리별로 묶어 셀 수 있고, 그게 바로 n_cached_pages다. (참고로 세컨더리 인덱스가 담는 PK 값은 "참조"지만, 그 참조가 담긴 페이지 자체는 버퍼 풀에 실물로 올라온다 — 버퍼 풀은 위치 쪽지가 아니라 페이지 실물을 담는다.)

# 주의 ① — 이건 "적재된 개수"지 "전체 대비 비율"이 아니다

책이 "적재 비율은 확인할 수 없다"고 한 게 헷갈리는데, 모순이 아니라 분자·분모 얘기다.

항목	구할 수 있나	출처
인덱스별 적재된 페이지 수(분자)	✅	`n_cached_pages`
인덱스별 전체 페이지 수(분모)	❌	인덱스 단위로 안 줌
인덱스별 적재 비율(분자÷분모)	❌	분모가 없어서

n_cached_pages는 "지금 버퍼 풀에 올라온 페이지 수"(분자) 다. 분모(그 인덱스가 디스크에 총 몇 페이지인지)는 MySQL이 인덱스별로 알려주지 않는다. information_schema.TABLES의 INDEX_LENGTH는 모든 세컨더리 인덱스를 합친 추정치라 인덱스 하나로 쪼갤 수 없다. 그래서 개수(O)는 알아도 비율(X)은 못 낸다.

# 주의 ② — 이 값은 늘 "부분집합"이고 계속 바뀐다

조회는 인덱스 전체를 올리지 않는다. 트리를 타고 내려가며 건드린 페이지만 적재한다(단건 조회는 루트→내부→잎 3~4개뿐). 풀스캔이면 잎을 다 읽긴 하지만, 앞의 LRU 스캔 저항성(Old 서브리스트) 때문에 재접근 없는 페이지는 금세 밀려나 전부 남지는 않는다.

인덱스 전체 페이지:  [1][2][3][4][5]...[100]...[1000]   ← 디스크엔 다 있음
버퍼 풀에 적재됨:    [1]      [5]        [100]           ← 건드리고 살아남은 것만

그래서 어느 순간이든 적재된 페이지 집합은 (최근 건드린 페이지) ∩ (아직 안 밀려난 페이지) 라는 부분집합이고, 구멍이 뚫린 형태로 시시각각 바뀐다. 단, 개별 페이지의 내용은 디스크 원본과 같다(수정했으면 더티라 메모리 쪽이 최신일 뿐) — 상이한 건 어느 페이지가 들어있느냐(집합 구성) 이지 페이지 내용이 아니다.

# 그래서 이걸 왜 보나

버퍼 풀(유한한 RAM)을 모든 인덱스가 나눠 쓰므로, 이 값은 "성능에 중요한 인덱스가 실제로 메모리에 데워져 있나" 를 진단한다 — ① 특정 인덱스가 콜드(적재 거의 0)면 그 인덱스를 타는 쿼리가 매번 디스크를 친다는 느린 쿼리의 원인이 되고, ② 핵심 인덱스 적재량이 안 차고 계속 밀려나면 버퍼 풀(RAM) 부족 신호이며, ③ 안 쓰는 큰 인덱스가 캐시를 점유하면 삭제 후보다. 다만 적재량은 "접근 빈도"가 아니므로(작은 인덱스는 아무리 자주 써도 몇 페이지뿐), 빈도는 performance_schema의 인덱스 사용 통계를 따로 봐야 한다.

🐘 PostgreSQL 비교 — 튜닝 감각이 정반대다. 이 절은 버퍼 풀을 메모리의 50% 이상으로 키우라고 하지만, PG의 shared_buffers 권장치는 RAM의 25% 정도로 훨씬 보수적이다. 이유는 캐싱을 누가 책임지느냐가 다르기 때문이다. InnoDB는 운영체제의 파일 캐시를 건너뛰고 디스크와 직접 주고받도록 설정해두고(O_DIRECT) 캐싱을 자기가 전부 떠안는다. 반면 PG는 운영체제의 파일 캐시에 일부러 얹혀 산다. 같은 데이터가 OS 캐시에도 있고 shared_buffers에도 있는 이중 캐싱이 PG에선 자연스러운 상태이고, 그래서 자기 몫은 작게 잡고 나머지를 OS에 양보하는 것이다. MySQL 감각으로 shared_buffers를 80% 잡으면 OS가 쓸 캐시를 뺏는 셈이라 오히려 손해다.

# Double Write Buffer

리두 로그는 공간 낭비를 막으려고 페이지의 변경된 부분만 기록한다. 그래서 더티 페이지를 디스크에 쓰다가 일부만 기록되는 현상이 발생하면 리두로 복구할 수 없다. 이걸 파셜 페이지(Partial-page) 또는 톤 페이지(Torn-page) 라 한다. 하드웨어 오작동이나 비정상 종료로 생긴다.

InnoDB는 이걸 막으려고 Double-Write를 쓴다. 더티 페이지들을 실제 데이터 파일에 쓰기 전에, 시스템 테이블스페이스의 DoubleWrite 버퍼에 한 번 순차로 몰아서 기록한다. 그 다음 실제 위치에 랜덤하게 쓴다. 재시작할 때 둘을 비교해서 정상적으로 기록되지 않은 페이지가 있으면 DoubleWrite 쪽 내용으로 복사한다.

innodb_doublewrite로 제어하는데, HDD처럼 한 번의 순차 쓰기가 부담 없는 시스템에선 부담이 적고, SSD처럼 랜덤 I/O와 순차 I/O 비용 차이가 적은 곳에선 상당히 부담된다. 데이터 무결성이 중요하면 켜는 게 좋다.

주의할 조합 — innodb_flush_log_at_trx_commit이 1이 아닌 상태에서 DoubleWrite를 끄는 건 잘못된 선택이다.

🐘 PostgreSQL 비교 — 찢어진 페이지 문제는 PG도 똑같이 겪지만, 성한 사본을 어디에 두느냐가 다르다. InnoDB는 실제 위치에 쓰기 전에 별도의 공간에 한 번 더 써두는 방식이다. PG는 그런 전용 공간을 따로 두지 않는다. 대신 체크포인트 직후 어떤 페이지를 처음 변경할 때, 변경된 부분만이 아니라 그 페이지 전체를 로그(WAL)에 통째로 적어둔다. 페이지가 찢어져도 로그 안에 온전한 사본이 남아 있으니 그걸로 덮어쓰면 된다. 이 동작을 켜고 끄는 설정이 full_page_writes다. 목적은 같고, 사본을 전용 버퍼에 두느냐 로그에 섞어 두느냐만 다른 셈이다.

# 언두 로그

언두 로그의 용도는 두 가지다.

트랜잭션 보장 — 롤백되면 이전 데이터로 복구
격리 수준 보장 — 변경 중인 레코드를 다른 커넥션이 읽을 때 언두의 백업본을 보여줌(= MVCC)

언두 로그는 매우 중요한 역할을 하지만 관리 비용도 많이 든다.

# 언두 로그 모니터링

앞서 말한 대로 오래 열린 트랜잭션이 언두를 급증시킨다. 그래서 언두 레코드 건수를 평상시 값 기준으로 모니터링하는 게 좋다.

-- 모든 버전에서 가능
SHOW ENGINE INNODB STATUS\G
-- History list length 31

-- 8.0에서 가능
SELECT count FROM information_schema.innodb_metrics
 WHERE subsystem='transaction' AND name='trx_rseg_history_len';

5.5까지는 언두 공간이 한 번 늘어나면 절대 줄어들지 않아서 서버를 새로 구축하는 것 말고 방법이 없었다. 5.7과 8.0은 언두를 돌아가며 순차적으로 재사용해 공간이 줄고, 필요하면 자동으로 줄이기도 한다.

# 언두 테이블스페이스

언두가 저장되는 공간이다. 버전별로 위치가 계속 바뀌었다.

5.6 이전: 시스템 테이블스페이스(ibdata.ibd)에 저장 → 서버 초기화 때 생성돼서 확장에 한계
5.6: innodb_undo_tablespaces 도입. 2 이상이면 별도 파일로 분리
5.7: 기본값 0 (여전히 시스템 테이블스페이스)
8.0.14부터: 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록. innodb_undo_tablespaces는 효력이 없어져 Deprecated

구조는 3단계다. 언두 테이블스페이스 1개 = 128개 이하의 롤백 세그먼트, 롤백 세그먼트 1개 = 여러 개의 언두 슬롯이다. 롤백 세그먼트 하나가 갖는 슬롯 수는 페이지 크기 / 16으로 정해진다(16KB면 1024개).

트랜잭션 하나가 필요로 하는 언두 슬롯은 문장 특성에 따라 최대 4개인데, 임시 테이블을 안 쓰면 대략 2개면 된다. 그래서 최대 동시 트랜잭션 수는 이렇게 나온다.

최대 동시 트랜잭션 수 = (InnoDB 페이지 크기 / 16) × 롤백 세그먼트 개수 × 언두 테이블스페이스 개수

기본 설정(16KB, innodb_undo_tablespaces=2, innodb_rollback_segments=128)이면 대략 131,072개다. 일반 서비스에 차고 넘치니 기본값을 그대로 유지하면 된다. 언두 공간이 남는 건 문제가 아니지만 슬롯이 부족하면 트랜잭션을 시작조차 못 하는 심각한 문제가 된다.

8.0부터는 CREATE UNDO TABLESPACE / DROP TABLESPACE로 동적 추가·삭제도 된다.

# 체인지 버퍼

레코드를 INSERT하거나 UPDATE할 때는 데이터 파일만 바꾸는 게 아니라 인덱스도 업데이트해야 한다. 그런데 인덱스 업데이트는 랜덤 디스크 읽기를 유발해서 비싸다.

그래서 InnoDB는 변경할 인덱스 페이지가 버퍼 풀에 이미 있으면 바로 업데이트하고, 없으면 디스크에서 읽어오지 않고 임시 메모리 공간에 저장해뒀다가 나중에 병합한다. 이 임시 공간이 체인지 버퍼다. 병합은 백그라운드의 머지 스레드(Merge thread) 가 한다.

중요한 제약 — 유니크 인덱스는 체인지 버퍼를 쓸 수 없다. 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하기 때문이다.

5.5 이전엔 INSERT만 가능해서 이름이 "인서트 버퍼"였고, 5.5부터 DELETE·UPDATE까지 확대됐다. innodb_change_buffering으로 어떤 작업에 쓸지 정한다.

all: 모든 인덱스 관련 작업 버퍼링 (inserts + deletes + purges)
none: 버퍼링 안 함
inserts: 인덱스에 새 아이템을 추가하는 작업만
deletes: 기존 아이템을 삭제하는 작업(삭제 마킹)만
changes: 추가와 삭제(inserts + deletes)
purges: 영구적으로 삭제하는 백그라운드 작업만

기본적으로 버퍼 풀의 25%까지 쓸 수 있고, 필요하면 innodb_change_buffer_max_size로 최대 50%까지 올릴 수 있다.

🐘 PostgreSQL 비교 — 체인지 버퍼에 대응하는 기능이 PG엔 없다. 다음에 나올 어댑티브 해시 인덱스도 마찬가지다. PG의 해시 인덱스는 CREATE INDEX ... USING hash로 사람이 직접 만드는 것이지 자동 생성이 아니다.

# 리두 로그와 로그 버퍼

리두 로그는 트랜잭션의 ACID 중 D(Durable, 영속성) 를 담당한다. 한 줄로 말하면 "디스크에 아직 못 쓴 변경 사항을 적어두는, 순차로만 덧붙이는 일지" 다.

# 왜 있어야 하나

버퍼 풀 절의 비유를 그대로 이어가면, 버퍼 풀이 책상이고 디스크가 창고였다. 데이터를 고치는 일은 전부 책상 위에서 일어나고, 창고의 원본과 달라진 페이지가 더티 페이지다.

여기서 딜레마가 생긴다. 커밋할 때마다 고친 페이지를 창고의 제자리에 갖다 놓으면 안전하지만 너무 느리다. 고친 페이지들은 창고 여기저기 흩어져 있으니 랜덤 쓰기가 되고, 한 줄 고치자고 16KB 페이지를 통째로 써야 한다. 거의 모든 DBMS의 데이터 파일이 쓰기보다 읽기 성능을 고려한 자료 구조라서 생기는 비용이다. 그렇다고 안 갖다 놓으면 서버가 죽는 순간 책상 위 내용이 전부 날아간다. 커밋했다고 사용자에게 대답해놓고 잃어버리는 것은 DB가 절대 하면 안 되는 일이다.

그래서 타협한다. 창고 정리는 나중에 몰아서 하되, "무엇을 고쳤는지"만 일지에 그때그때 적는다. 커밋 시점에 일지만 디스크에 확실히 적히면 커밋을 성사시킨다. 실제 페이지는 아직 책상에 더티인 채로 남아 있어도 괜찮다 — 죽어도 일지가 남아 있으니까. 재시작하면 일지를 읽으면서 "고쳤다고 적혀 있는데 창고엔 반영이 안 된 것"들을 다시 실행한다. 이 "다시 실행한다"가 redo라는 이름의 뜻이고, 4.2.6 자동화된 장애 복구가 하는 일이 이것이다.

이 원칙을 WAL(Write-Ahead Logging) 이라 부른다 — 데이터 페이지보다 로그를 먼저 써라. InnoDB만의 발명이 아니라 거의 모든 상용 DB의 공통 설계다.

# 그런데 일지도 결국 디스크 쓰기 아닌가

맞다. 리두 로그도 디스크에 쓴다. 그런데도 훨씬 싼 이유가 세 가지다.

순차 쓰기다. 일지는 항상 맨 뒤에 한 줄 덧붙이기만 하니 디스크 헤드가 돌아다닐 필요가 없다. 흩어진 페이지에 랜덤으로 쓰는 것과는 비용이 자릿수 단위로 차이 난다.
양이 훨씬 적다. 페이지 전체(16KB)가 아니라 변경된 부분만 적는다. 참고로 이 성질이 곧 Double Write Buffer가 존재하는 이유다 — 페이지가 반쯤 쓰이다 찢어지면, 변경분만 가진 리두 로그로는 복구할 방법이 없으니까.
모아서 쓸 수 있다. 여러 트랜잭션의 커밋을 한 번에 묶어 기록한다(그룹 커밋).

그리고 리두 로그조차 매번 디스크에 직행하지 않는다. 로그 버퍼라는 메모리 공간에 먼저 쌓아뒀다가 내린다. 이 절 제목의 "로그 버퍼"가 이것이고, 언제 내릴지를 정하는 손잡이가 바로 아래의 innodb_flush_log_at_trx_commit이다.

# 리두 로그에 적히는 건 데이터가 아니다

여기서 오해가 둘 생기는데 세트로 딸려 다닌다. 하나는 리두 로그에 데이터가 들어있다는 그림, 다른 하나는 그 데이터가 나중에 제자리로 옮겨진다는 그림이다. 둘 다 틀렸다.

리두 로그에 적히는 건 "무엇을 바꿨는지에 대한 서술"이지 데이터의 사본이 아니다. 창고 비유로 정확히 말하면 이렇다.

리두 로그 = "7번 상자의 라벨을 A에서 B로 바꿔라"라고 적은 작업 지시서
데이터 파일 = 창고에 있는 실제 상자 (아직 라벨이 A)
버퍼 풀 = 책상에 꺼내놓은, 이미 라벨을 B로 바꿔놓은 상자

플러시는 책상 위의 상자를 창고 제자리에 갖다 놓는 일이지, 지시서를 창고로 옮기는 게 아니다. 지시서는 서랍에 있다가 상자가 무사히 자리를 잡으면 찢어버린다. 즉 같은 변경이 두 군데에 독립적으로 존재한다 — 리두 로그엔 서술로, 버퍼 풀엔 실물로. 데이터 파일로 내려가는 건 실물 쪽이다. 데이터가 임시 위치에서 정식 위치로 이사 가는 구조가 아니라, 정식 위치는 계속 낡은 채로 있고 그걸 고칠 지시서가 따로 있는 구조다.

그래서 리두 로그는 정상 운영 중엔 읽히지도 않는다. 쓰기 전용이다. 서버가 정상적으로 살아있는 한 리두 로그 파일은 단 한 바이트도 읽히지 않는다. 보험 증서는 서랍에 넣어두는 것 자체가 목적이고, 불이 나야 꺼낸다.

[정상 운영]
  버퍼 풀 (더티 페이지) ──플러시──▶ 데이터 파일      ← 실제 데이터 흐름
        │
        └──(커밋 때 일지만 적음)──▶ 리두 로그         ← 적어두고 끝. 안 읽음.

[크래시 복구 — 이때만]
  리두 로그 ──읽어서 재실행──▶ 데이터 파일

이 그림의 증거가 바로 Double Write Buffer(4.2.8)다. 만약 리두 로그에 데이터 자체가 들어있어서 그걸 제자리로 옮기는 구조라면 Double Write Buffer는 존재할 이유가 없다 — 페이지가 찢어져도 리두에서 온전한 걸 가져오면 되니까. 그런데 안 된다. 지시서는 "바꿔라"이지 "이게 완성본이다"가 아니라서, 적용하려면 멀쩡한 원본 상자가 먼저 있어야 한다. 반쯤 부서진 상자에 대고 "라벨을 B로 바꿔라"를 실행할 수는 없다. 그래서 온전한 사본을 따로 보관하는 Double Write Buffer가 필요한 것이다.

🐘 PostgreSQL 비교 — PG의 full_page_writes가 이걸 뒤집어서 증명한다. PG는 체크포인트 후 어떤 페이지를 처음 변경할 때만 페이지 전체를 통째로 로그에 넣는다(4.2.8 참고). 굳이 그런 특별 취급이 필요하다는 것 자체가, 평소의 로그 기록은 전체 사본이 아니라 변경 서술이라는 뜻이다. 이 점은 InnoDB와 PG가 똑같다.

# 리두 로그와 언두 로그 — 방향이 정반대다

이름이 비슷해서 제일 많이 꼬이는 지점이라 짚어둔다.

	무엇을 적나	무엇을 위해
리두 로그	변경 후의 값 ("이렇게 고쳤다")	크래시 복구 — 내구성(D)
언두 로그	변경 전의 값 ("원래 이랬다")	롤백과 MVCC — 원자성(A)·격리성(I)

리두는 "다시 해라", 언두는 "되돌려라" 로 기억하면 안 꼬인다. 그래서 비정상 종료 후 데이터 파일이 일관되지 않은 두 가지 경우도 담당이 갈린다.

커밋됐는데 데이터 파일에 기록되지 않은 데이터 → 리두 로그에 있는 걸 데이터 파일에 복사하면 된다.
롤백됐는데 데이터 파일에 이미 기록된 데이터 → 리두 로그로는 해결할 수 없다. 언두 로그의 내용을 가져와 복사해야 한다. 그런데 어느 시점으로 되돌릴지 판단하려면 결국 리두 로그도 필요하다.

즉 복구는 리두로 커밋된 것을 전부 다시 살려낸 다음, 언두로 미커밋 트랜잭션을 되돌리는 순서로 진행된다. 둘 다 필요한 이유가 이것이다.

# `innodb_flush_log_at_trx_commit`

내구성을 조절하는 손잡이다.

값	동작	최대 손실
0	1초에 한 번 리두 로그를 `write` + `sync`	최대 1초
1	매 커밋마다 `write` + `sync` (기본값, 권장)	없음
2	매 커밋마다 `write`, `sync`는 1초에 한 번	MySQL만 죽으면 없음 / OS까지 죽으면 최대 1초

2가 미묘한데, 커밋할 때마다 OS의 메모리 버퍼까지는 기록되기 때문에 MySQL 서버만 비정상 종료하면 데이터가 사라지지 않는다. MySQL과 OS가 모두 비정상 종료해야 최대 1초를 잃는다.

주의할 점 — 0이나 2로 설정해도 디스크 동기화가 항상 1초 간격인 건 아니다. 스키마 변경 DDL이 실행되면 리두 로그가 디스크로 동기화되므로 1초보다 짧을 수도 있다. 간격은 innodb_flush_log_at_timeout(기본 1초)으로 바꿀 수 있지만 일반적인 서비스에서 건드릴 이유는 없다.

리두 로그 파일 전체 크기는 innodb_log_file_size × innodb_log_files_in_group으로 결정된다. 로그 버퍼 크기는 기본값 16MB 수준이 적합하고, BLOB이나 TEXT처럼 큰 데이터를 자주 변경하면 더 크게 잡는다.

# 리두 로그 아카이빙 (8.0 신규)

백업 툴(MySQL 엔터프라이즈 백업, Xtrabackup)은 데이터 파일을 복사하는 동안 리두 로그에 쌓이는 내용도 계속 추적해서 복사한다. 그런데 데이터 변경이 너무 많으면 리두 로그가 덮어써지는 속도가 복사 속도보다 빨라져 백업이 실패한다. 아카이빙은 이걸 막아준다.

SET GLOBAL innodb_redo_log_archive_dirs='backup:/var/log/mysql_redo_archive/';
DO innodb_redo_log_archive_start('backup','20200722');
-- ...
DO innodb_redo_log_archive_stop();

디렉터리는 운영체제(mysql) 유저만 접근 가능해야 한다(chmod 700). 그리고 archive_start를 실행한 세션이 계속 연결돼 있어야 한다. 끊기면 InnoDB가 아카이빙을 자동으로 중단해버린다.

# 리두 로그 활성화/비활성화 (8.0 신규)

8.0부터 수동으로 끄고 켤 수 있다. 대량 데이터 적재 시간을 단축하는 용도다.

ALTER INSTANCE DISABLE INNODB REDO_LOG;
LOAD DATA ...;
ALTER INSTANCE ENABLE INNODB REDO_LOG;

여기서 정말 조심해야 한다. 리두 로그를 비활성화한 상태에서 서버가 비정상 종료하면 복구가 불가능하다. innodb_force_recovery로 겨우 띄우더라도 데이터는 마지막 체크포인트 시점의 일관된 상태가 아니다. 그러니 다시 켜는 것을 절대 잊으면 안 된다. 상태는 이렇게 확인한다.

SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';

🐘 PostgreSQL 비교 — 리두 로그에 그대로 대응하는 것이 PG의 WAL이다. 로그를 먼저 쓰고 데이터 페이지는 나중에 내린다는 원칙도, LSN 개념도 양쪽 다 똑같다. innodb_flush_log_at_trx_commit에 해당하는 손잡이는 **synchronous_commit**이다.

결정적인 차이는 로그가 몇 개냐다. InnoDB의 리두 로그는 크래시 복구 전용이다. 복제를 하거나 "어제 오후 3시 상태로 되돌리기" 같은 걸 하려면 바이너리 로그(binlog) 라는 완전히 별개의 로그가 필요하다. 즉 MySQL은 커밋할 때마다 로그를 두 군데에 쓴다. 그런데 이 둘의 기록 순서가 어긋나면 복제본이 원본과 달라져버리므로, 커밋할 때 "두 곳 다 성공했을 때만 진짜 커밋"으로 묶어주는 절차가 추가로 붙는다. MySQL 커밋이 짊어지는 오버헤드가 여기서 나온다. PG는 WAL 하나가 복구·복제·시점 복구를 전부 담당해서 이중 기록도, 묶어주는 절차도 없다.

대신 PG의 WAL은 InnoDB 리두처럼 정해진 크기 안에서 덮어쓰며 도는 게 아니라 파일로 차곡차곡 쌓인다(max_wal_size, checkpoint_timeout으로 조절). 쌓이니까 따로 보관해뒀다가 원하는 시점으로 되돌릴 수 있는 것이다. 바로 위에서 본 8.0의 리두 로그 아카이빙은 이 격차를 일부 메우려는 시도로 읽으면 된다.

그리고 PG엔 언두 로그가 아예 없다. 이전 버전을 언두로 밀어내는 대신 힙에 새 튜플을 덧붙이는 구조라(4.2.3 참고) 되돌릴 것이 따로 없고, PG의 WAL은 순수하게 리두 역할만 한다. 대신 죽은 튜플이 쌓여서 VACUUM이 필요해지는 것이 그 대가다.

# 어댑티브 해시 인덱스

이름이 헷갈리는데, 사용자가 만든 인덱스가 아니다. InnoDB가 자주 요청되는 데이터에 대해 자동으로 생성하는 해시 인덱스다(innodb_adaptive_hash_index로 켜고 끈다).

원리는 이렇다. B-Tree 인덱스에서 값을 찾으려면 루트 → 브랜치 → 리프를 거쳐야 한다. 이 과정이 느릴 수도 빠를 수도 있는데, 어쨌든 비용이다. 어댑티브 해시 인덱스는 자주 읽히는 데이터 페이지의 키 값으로 해시 인덱스를 만들어서, B-Tree 탐색 없이 즉시 데이터 페이지에 접근하게 한다.

해시 키는 "B-Tree 인덱스의 고유번호(id) + 실제 키 값" 조합이다. 모든 B-Tree 인덱스의 어댑티브 해시 인덱스가 하나의 해시 인덱스에 같이 저장되기 때문에, 어느 인덱스 소속인지 구분하려고 고유번호가 들어간다. 값은 버퍼 풀에 로딩된 페이지의 메모리 주소다. 그래서 버퍼 풀에 올라온 페이지에 대해서만 관리되고, 페이지가 버퍼 풀에서 없어지면 해당 정보도 사라진다.

효과는 극적일 수 있다. 책의 예시에서 어댑티브 해시 인덱스를 끈 상태로 초당 20,000 쿼리를 처리하며 **CPU가 100%**였는데, 켜니까 처리량이 2배 가까이 늘면서 CPU 사용률은 오히려 떨어졌다. B-Tree 탐색이 줄면서 InnoDB 내부 잠금(세마포어) 횟수도 확 줄었기 때문이다.

하지만 공짜가 아니다. 이게 이 절의 핵심이다.

메모리를 상당히 쓴다. 해시 인덱스도 저장 공간이 필요하다.
테이블 삭제·변경이 매우 느려진다. 테이블을 DROP하거나 ALTER하면 그 테이블이 가진 모든 페이지 정보를 어댑티브 해시 인덱스에서 제거해야 한다. 그동안 CPU를 잡아먹고 DB 전체 성능이 느려진다. Online DDL도 포함이고, 8.0.20의 INSTANT 알고리즘도 상당한 시간이 걸린다.

도움이 되지 않는 경우:

디스크 읽기가 많은 경우
특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

도움이 되는 경우:

디스크의 데이터가 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 적은 경우)
동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

판단은 상태 값으로 한다.

SHOW ENGINE INNODB STATUS\G
-- 1.03 hash searches/s, 2.64 non-hash searches/s

이 예시는 초당 3.67(= 2.64 + 1.03)번의 검색 중 1.03번만 해시 인덱스를 썼다는 뜻이니 히트율 28% 정도다. 저자는 28%면 비활성화하는 편이 나을 수도 있다고 본다. 메모리 사용량까지 같이 봐야 한다.

SELECT event_name, current_number_of_bytes_used
  FROM performance_schema.memory_summary_global_by_event_name
 WHERE event_name='memory/innodb/adaptive hash index';

# InnoDB vs MyISAM vs MEMORY

결론부터. MyISAM과 MEMORY는 8.0에서 더 이상 쓸 이유가 없다.

5.5부터 InnoDB가 기본 엔진이 됐지만 mysql DB의 시스템 테이블은 여전히 MyISAM이었고, 전문 검색과 공간 좌표 검색도 MyISAM에서만 지원됐다. 8.0에서 이 모든 게 InnoDB로 넘어왔다. 시스템 테이블도 전부 InnoDB로 교체됐고 공간 좌표·전문 검색도 InnoDB가 지원한다. 저자는 MyISAM이 도태됐으며 이후 버전에서는 없어질 것으로 예상한다.

MEMORY는 "메모리"라는 이름 때문에 과대평가를 받지만 동시 처리 성능에서 InnoDB를 따라갈 수 없다. 모든 처리를 메모리에서만 하니 빠를 것 같지만 테이블 수준의 잠금을 쓰기 때문에 제대로 성능을 못 낸다. 하나의 스레드에서만 데이터를 처리하면 InnoDB보다 빠르지만, 온라인 트랜잭션 처리는 동시 처리 성능이 훨씬 중요하다.

내부 임시 테이블 용도도 8.0에서 TempTable 엔진이 가져갔다(internal_tmp_mem_storage_engine, 기본값 TempTable). MEMORY 엔진은 가변 길이 칼럼을 지원하지 않는 문제가 있었기 때문이다. MEMORY는 이전 버전 호환성 유지 차원일 뿐 향후 제거될 것으로 보인다.

# 4.3 MyISAM 스토리지 엔진 아키텍처

# 키 캐시 — 인덱스만 캐시한다

InnoDB의 버퍼 풀과 비슷한 게 MyISAM의 키 캐시(Key cache, 키 버퍼) 다. 다만 결정적 차이가 있다. 이름 그대로 인덱스만 대상으로 작동한다. 데이터는 캐시하지 않는다.

키 캐시가 얼마나 효율적인지는 이 식으로 본다.

키 캐시 히트율 = 100 - (Key_reads / Key_read_requests × 100)

SHOW GLOBAL STATUS LIKE 'Key%';

매뉴얼은 99% 이상 유지를 권장한다. 미만이면 key_buffer_size를 더 크게 잡는다. 32비트 OS는 하나의 키 캐시에 4GB 이상을 못 주고, 64비트는 OS_PER_PROCESS_LIMIT까지 가능하다. 4GB 이상 쓰려면 이름 붙인(named) 키 캐시를 따로 만들어야 한다.

key_buffer_size = 4GB
kbuf_board.key_buffer_size = 2GB
kbuf_comment.key_buffer_size = 2GB

CACHE INDEX db1.board, db2.board IN kbuf_board;
CACHE INDEX db1.comment, db2.comment IN kbuf_comment;

기본 키 캐시 이외의 명명된 키 캐시는 어떤 인덱스를 캐시할지 MySQL에 알려줘야만 메모리를 할당받는다.

# 운영체제 캐시에 얹혀 산다

MyISAM은 데이터에 대한 캐시나 버퍼링 기능이 없다. 그래서 데이터 읽기·쓰기는 항상 운영체제의 파일 시스템 캐시에 의존한다.

운영체제의 캐시는 InnoDB처럼 데이터의 특성을 알고 전문적으로 관리하지는 못하지만, 없는 것보단 낫다. 문제는 전체 메모리가 8GB인데 MySQL이나 다른 애플리케이션이 메모리를 모두 써버리면 운영체제가 캐시로 쓸 공간이 없어진다는 점이다. 그러면 MyISAM 테이블에 대한 쿼리 처리가 느려진다.

그래서 MyISAM을 주로 쓴다면 키 캐시를 최대 물리 메모리의 40%를 넘지 않게 하고, 나머지는 운영체제가 파일 시스템 캐시로 쓸 수 있게 남겨두는 게 좋다.

# 데이터 파일과 ROWID

InnoDB 테이블이 PK로 클러스터링되는 것과 달리, MyISAM은 클러스터링이 없다. 데이터 파일이 힙(Heap) 공간처럼 활용된다. PK 값과 무관하게 INSERT되는 순서대로 저장되고, 저장된 모든 레코드는 ROWID라는 물리적 주솟값을 가진다. PK든 세컨더리든 모든 인덱스가 이 ROWID를 포인터로 가진다.

ROWID는 두 방식이다.

고정 길이 ROWID — 테이블 생성 시 MAX_ROWS 옵션을 명시하면 레코드 수가 한정된 테이블이 만들어지고, 4바이트 정수를 ROWID로 쓴다. 자주 사용되진 않는다.
가변 길이 ROWID — MAX_ROWS를 안 주면 myisam_data_pointer_size(기본 7, 2~7바이트) 만큼의 공간을 쓴다. 첫 바이트에 ROWID 길이를 저장하고 나머지가 실제 ROWID다. 기본값이 7이면 6바이트가 실제 ROWID이므로 최대 256TB(2^(8×6))까지 저장 가능하다. 그 이상 필요하면 myisam_data_pointer_size를 8로 올려 64PB까지 늘린다.

# 4.4 MySQL 로그 파일

MySQL에 문제가 생겼을 때 로그 파일을 자세히 확인하는 습관을 들일 필요가 있다. 많은 사용자가 로그 내용을 무시하고 다른 방법으로 해결하려 애쓰는데, 사실 원인이 여기 다 적혀 있는 경우가 많다.

# 에러 로그 파일

log_error 파라미터로 경로를 지정하고, 없으면 데이터 디렉터리에 .err 확장자로 생성된다. 눈여겨볼 메시지는 이렇다.

시작 과정의 정보와 에러 — 'mysqld: ready for connections'가 뜨면 정상적으로 기동한 것이다. 설정 파일의 변수명을 잘못 썼거나 값을 인식하지 못하면 에러 메시지를 출력하고 시작하지 못한다. 무시(ignore)되는 게 아니다.

비정상 종료 후의 InnoDB 복구 메시지 — 마지막 종료가 비정상이었으면 완료되지 못한 트랜잭션을 정리하고 리두를 다시 적용하는 재시작 작업이 일어난다. 이 과정에서 간단한 메시지가 출력되는데, 복구가 안 되면 에러 메시지가 나오고 서버는 종료된다. 이때 innodb_force_recovery가 필요하다.

Aborted connection — 클라이언트 애플리케이션이 정상적으로 접속을 종료하지 못한 경우다. 아주 많이 쌓이면 애플리케이션의 커넥션 종료 로직을 점검해야 한다. max_connect_errors가 너무 낮으면 정상적인 클라이언트인데도 "Host 'host_name' is blocked" 에러가 날 수 있다. 다만 값을 올리기 전에 왜 에러가 발생했는지 원인을 먼저 살펴보는 게 맞다.

모니터링 결과 — SHOW ENGINE INNODB STATUS 같은 InnoDB 모니터링을 활성화한 채로 두면 에러 로그가 매우 커져서 파일 시스템 공간을 다 써버릴 수도 있다. 쓰고 나면 반드시 비활성화해야 한다.

종료 메시지 — 이게 실전에서 요긴하다. MySQL이 아무도 모르게 재시작된 경우, 에러 로그의 마지막 메시지가 왜 종료됐는지 확인하는 유일한 방법이다.

'Received SHUTDOWN from user ...' → 누군가 정상적으로 종료시킨 것
종료 관련 메시지 없이 스택 트레이스와 16진수 주소값이 잔뜩 출력됨 → 세그멘테이션 폴트로 비정상 종료한 것

후자는 MySQL의 버그와 연관됐을 가능성이 높으니, 스택 트레이스를 참조해 버그를 찾고 버전을 업그레이드하거나 회피책(Workaround)을 찾는 게 최적의 방법이다.

# 제너럴 쿼리 로그

실행된 쿼리의 전체 목록을 시간 순서대로 기록한다. 슬로우 쿼리 로그와 결정적으로 다른 점이 있다.

제너럴 쿼리 로그는 쿼리 실행 전에 기록된다. 그래서 쿼리 실행 중에 에러가 발생해도 로그에 남는다. (슬로우 쿼리 로그는 정상적으로 완료돼야 남는다.)

general_log_file로 경로를 정하고, log_output을 TABLE로 하면 파일이 아니라 테이블(mysql.general_log)에 저장된다.

🐘 PostgreSQL 비교 — PG에선 log_statement = all이 같은 역할을 한다.

# 슬로우 쿼리 로그

long_query_time에 설정한 시간보다 오래 걸린 쿼리가 모두 기록된다. 초 단위지만 소수점(마이크로초)까지 설정 가능하다.

기록 시점을 주의해야 한다. 슬로우 쿼리 로그는 쿼리가 정상적으로 실행 완료돼야 기록된다. 실행 시간을 기준으로 판단하기 때문이다. 그래서 잠금 대기만 하다가 실패한 쿼리는 안 남는다.

로그의 한 항목은 이렇게 생겼다.

# Time: 2020-07-19T15:44:22.178484+09:00
# User@Host: root[root] @ localhost []  Id: 14
# Query_time: 1.180245  Lock_time: 0.002658  Rows_sent: 1  Rows_examined: 2844047
use employees;
SET timestamp=1595141060;
select emp_no, max(salary) from salaries;

읽는 법이 중요하다.

Time — 쿼리가 시작된 시각이 아니라 종료된 시각이다. 시작 시점을 알려면 Time - Query_time을 해야 한다.
Query_time — 쿼리 실행에 걸린 전체 시간.
Lock_time — 여기가 함정이다. 이건 MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 시간만 표시한다. InnoDB의 레코드 잠금은 포함되지 않는다. 그래서 InnoDB 테이블에 접근하는 쿼리의 Lock_time 값은 튜닝이나 쿼리 분석에 별로 도움이 되지 않는다. 예시의 0.002658처럼 아주 작은 값은 무시해도 된다.
Rows_examined vs Rows_sent — 실제 처리를 위해 접근한 레코드 수 대 클라이언트로 보낸 레코드 수. 이 둘이 크게 벌어지면 인덱스 튜닝 여지가 있다. 예시에서 284만 건을 읽어 1건을 보냈으니 명백히 개선 대상이다. (GROUP BY나 COUNT(), MIN(), MAX() 같은 집계는 당연히 벌어지니 예외다.)

로그가 쌓이면 직접 읽기 어려우니 Percona Toolkit의 pt-query-digest 로 분석한다.

pt-query-digest --type='slowlog' mysql-slow.log > parsed_mysql-slog.log

결과는 세 그룹으로 나뉜다.

슬로우 쿼리 통계 — 전체 쿼리의 실행 시간, 잠금 대기 시간 등의 평균·최소·최대값
실행 빈도 및 누적 실행 시간순 랭킹(Profile) — Query ID는 정규화된 쿼리(리터럴 제거)의 해시라서 같은 모양의 쿼리는 동일한 ID를 갖는다. --order-by로 정렬 기준을 바꿀 수 있다.
쿼리별 실행 횟수와 누적 실행 시간 상세 — 쿼리별 응답 시간 히스토그램 등

🐘 PostgreSQL 비교 — PG의 대응 설정은 log_min_duration_statement(밀리초 단위)다. 그런데 분석 방식이 더 편한데, pt-query-digest처럼 로그를 파싱하는 게 아니라 pg_stat_statements 확장이 인메모리로 집계해줘서 그냥 SQL로 조회하면 된다. 로그 기반 분석을 원하면 pgBadger를 쓴다.

# 곁가지 — 책 밖에서 궁금했던 것

읽다 보면 자연스럽게 떠오르는데 책은 답하지 않는 질문이 둘 있었다. 정리해둔다.

# 애초에 왜 엔진이 둘로 나뉘어 있나

설계가 아니라 역사다. 누군가 "엔진을 나누는 게 좋겠다"고 결정한 적이 없다.

MyISAM은 MySQL의 친자식이다. 1995년부터 있던 ISAM의 후계로 3.23부터 기본 엔진이었다. 당시 MySQL의 포지션은 "LAMP 스택의 빠른 조회용 DB"였고, 트랜잭션은 필요 없었다. 오히려 트랜잭션 오버헤드가 없는 게 세일즈 포인트였다.

반면 InnoDB는 입양아다. MySQL AB가 만든 게 아니라 핀란드의 Innobase Oy에서 Heikki Tuuri가 만들었고, 2001년 3.23.34a에 외부 부품으로 끼워졌다. 그러니까 순서가 이렇다.

MySQL이 트랜잭션이 필요해졌는데 직접 만들 여력이 없었고
→ 남의 엔진을 끼울 구멍(핸들러 API)을 팠고
→ 그 구멍이 나중에 "플러그인 스토리지 엔진 아키텍처"라는 이름을 얻었다

소켓이 먼저 있어서 부품을 고른 게 아니라, 부품을 끼우려고 소켓을 판 것이다. 우아한 설계 원칙이 아니라 필요에 의한 봉합선이었다. (뒷이야기: 2005년 오라클이 Innobase를 인수해 MySQL의 심장을 경쟁사가 갖게 되자, MySQL AB는 Falcon이라는 대체 엔진을 개발했다. 2010년 오라클이 MySQL까지 인수하며 Falcon은 폐기됐고 InnoDB가 5.5부터 기본이 된다.)

그럼 "워크로드별로 고른다"는 명분은 가짜였나? 절반은 진짜였다. 트레이드오프가 임의적이지 않다는 걸 보여주는 예가 COUNT(*)다.

SELECT COUNT(*) FROM big_table;   -- WHERE 없이

MyISAM은 O(1) 이다. 메타데이터에 행 개수를 그냥 적어둔다. InnoDB는 풀 스캔을 해야 한다. 왜? MVCC 때문이다. 트랜잭션마다 보이는 행이 다른데 "정답 개수" 하나를 어디에 적어두겠나. 트랜잭션 A는 3건, B는 5건이 보이는 게 정상인 세계에선 단일한 카운트라는 개념 자체가 성립하지 않는다. 즉 MyISAM이 빠른 건 게을러서가 아니라 동시성을 포기하고 얻은 대가다.

그런데 이 명분은 증발했다. InnoDB가 MyISAM의 장점을 하나씩 다 흡수했기 때문이다 — 전문 검색은 5.6에서, 공간 좌표 인덱스는 5.7에서, 시스템 테이블 자리는 8.0에서 가져갔다. 반면 MyISAM의 치명적 약점은 그대로다. 테이블 단위 잠금, 트랜잭션 없음, 외래 키 없음, 그리고 무엇보다 크래시 복구가 없다. 이 장에서 InnoDB가 리두·언두·DoubleWrite로 그 난리를 치는 이유가 바로 그건데 MyISAM엔 그 장치가 아예 없다. 죽으면 REPAIR TABLE이고 데이터를 잃을 수도 있다.

그리고 이 봉합선은 공짜가 아니었다. 잘 언급되지 않는 대가가 있다.

옵티마이저가 눈이 어둡다. 핸들러 API라는 경계 때문에 옵티마이저는 엔진 내부를 못 보고 거친 통계만 갖고 계획을 짠다. MySQL 옵티마이저가 오래 뒤처졌던 구조적 이유다.
엔진을 섞은 트랜잭션은 원자적이지 않다. MyISAM과 InnoDB를 한 트랜잭션에서 건드리면 롤백이 반쪽만 된다.
같은 일을 두 번 만들었다. 키 캐시(MyISAM)와 버퍼 풀(InnoDB), 서로 다른 잠금 체계. 4.3절에서 키 캐시를 따로 배워야 했던 게 이 중복 때문이다.

🐘 PostgreSQL 비교 — COUNT(*)가 느린 건 PG도 똑같다. 같은 MVCC 이유다. 이건 InnoDB의 결함이 아니라 MVCC의 청구서다. 정확한 카운트가 상시 필요하면 별도 카운터 테이블이나 근사치(information_schema.tables의 TABLE_ROWS)를 쓰는 게 정석인 것도 양쪽이 같다.

# 그럼 MySQL은 어디까지 확장할 수 있나

"확장하려면 엔진을 통째로 만들어야 하나?"가 궁금했는데, 답은 "아니오, 하지만 정작 중요한 건 어차피 못 한다" 였다.

먼저 MySQL에도 확장 개념은 있다. 4.1.4절에서 본 대로 SHOW PLUGINS의 Type에는 STORAGE ENGINE 말고도 AUTHENTICATION, AUDIT, FTPARSER, DAEMON이 있다. UDF로 C 함수를 꽂을 수도 있다.

CREATE FUNCTION my_func RETURNS INTEGER SONAME 'my_udf.so';

문제는 소켓의 위치다. 스토리지 엔진은 타입 시스템보다 아래에 있다. 타입·연산자·함수·옵티마이저는 전부 핸들러 API 위쪽, MySQL 엔진 레이어에 있다. 그래서 InnoDB를 능가하는 엔진을 맨땅부터 만들어도 새로운 데이터 타입 하나 추가할 수 없다. 소켓이 엉뚱한 층에 뚫려 있다.

            MySQL                                PostgreSQL
    ┌────────────────────┐                ┌────────────────────┐
    │ 파서 / 전처리기      │                │ 파서                │
    │ 타입·연산자·함수     │ ← 확장 불가     │ 타입·연산자·함수     │ ← CREATE TYPE/OPERATOR
    │ 옵티마이저          │ ← 관여 불가     │ 옵티마이저          │ ← opclass로 관여 가능
    ├────────────────────┤ ← 핸들러 API    │ 인덱스 AM          │ ← GiST/GIN 확장 가능
    │ 스토리지 엔진        │ ← 여기만 열림   ├────────────────────┤
    └────────────────────┘                │ 힙 (하나)           │ ← TAM(PG12+), 거의 안 씀
                                          └────────────────────┘

MySQL은 스택의 아래쪽을 열었고, PostgreSQL은 위쪽을 열었다. 봉합선이 정반대 끝에 있다.

가장 깔끔한 증거가 공간 데이터다. PostGIS는 PG 코어가 아니다. 서드파티 확장이고 CREATE EXTENSION postgis; 한 줄이면 geometry 타입, ST_Contains 연산자, GiST 연산자 클래스가 다 딸려 온다. 결정적으로 그 연산자가 인덱스를 탄다. 코어는 한 줄도 안 고친다. 반면 MySQL은 공간 지원을 오라클이 서버 코어에 직접 박아 넣어야 했다. 외부인이 플러그인으로 PostGIS 같은 걸 만들 방법이 없었다.

실제로 소켓에 꽂힌 엔진들을 보면 천장이 보인다. InnoDB(B-Tree+MVCC), MyRocks(RocksDB의 LSM 트리로 저장), TokuDB(Fractal Tree), Spider(샤딩), NDB(클러스터링) — 전부 "바이트를 어떻게 눕히느냐"의 변주고, 타입이나 연산자를 추가한 건 하나도 없다. MyRocks는 이 소켓이 진짜 쓸모 있다는 좋은 증거이긴 하지만(페이스북이 저장 효율을 위해 LSM 트리를 통째로 갈아끼웠다), 그게 소켓이 할 수 있는 일의 전부다.

UDF도 한계가 뚜렷하다. 스칼라 함수 하나일 뿐이라 타입 정의도, 연산자 정의도, 커스텀 인덱스도 안 되고 — 옵티마이저 입장에서 그냥 블랙박스다. 비용도 선택도(selectivity)도 모르고 인덱스를 태울 수도 없다.

정리하면 차이는 "슬롯이냐 조립이냐" 다. MySQL은 벤더가 미리 정해둔 슬롯에만 꽂을 수 있다. FTPARSER 슬롯이 있는 건 MySQL이 "전문 검색 파서는 열어주자"고 결정했기 때문이고, 열어주지 않은 곳은 확장할 방법이 없다. PG는 타입·연산자·인덱스가 조립 가능한 시스템이라 PG 개발자가 예상 못 한 것도 사용자가 만들 수 있다(벡터 검색 pgvector가 좋은 예다 — 코어는 벡터를 모르는데 확장이 타입·연산자·인덱스를 다 들고 들어온다).

🐘 PostgreSQL 비교 — 재미있는 대칭이 있다. "다른 서버의 테이블을 내 테이블처럼 쓰기" 라는 같은 문제를 MySQL은 FEDERATED 스토리지 엔진으로, PG는 postgres_fdw 같은 확장(FDW) 으로 푼다. 문제는 같은데 각자 자기 봉합선에서 접근한 것이다. 참고로 저장 계층 확장은 PG도 12부터 열었지만(Table Access Method) 거의 안 쓰인다. MySQL이 연 문은 PG도 늦게나마 열었는데 인기가 없었고, PG가 연 문은 MySQL이 아직 열지 않았다. "MySQL은 확장 생태계가 빈약하다"는 인상의 구조적 원인이 이것이다.

# MyISAM은 왜 읽기가 굶는가 — 버퍼링이 아니라 잠금이다

"MyISAM은 포그라운드 스레드가 쓰기까지 직접 처리한다"(4.1.2.1)를 읽으면 자연스럽게 이런 의문이 든다. 읽기와 쓰기가 한꺼번에 몰리면 읽기 응답이 밀리지 않나? 결론은 맞는데 범인이 다르고, 실제로는 더 나쁘다.

스레드는 범인이 아니다. 커넥션마다 자기 포그라운드 스레드가 있다. 커넥션 A가 UPDATE로 자기 스레드를 붙잡고 있어도 커넥션 B의 SELECT는 B의 스레드가 처리한다. "포그라운드가 쓰기까지 한다"의 진짜 의미는 쓴 사람 본인이 기다린다는 것이지 남을 늦춘다는 게 아니다.

진짜 범인은 테이블 잠금이고, MySQL은 대놓고 쓰기를 편든다. MyISAM은 테이블 단위로 잠그니 UPDATE 하나가 테이블 전체를 막는다. 그런데 여기서 한 발 더 나간다 — MySQL의 테이블 잠금 큐는 쓰기에 더 높은 우선순위를 준다. 잠금이 풀리면 쓰기 대기 큐를 먼저 비우고 그 다음에 읽기 큐를 본다. 결과적으로 UPDATE가 꾸준히 들어오는 한 SELECT는 계속 밀린다. 밀리는 정도가 아니라 굶어 죽는다(starvation). 큰 테이블에 UPDATE 하나가 몇 분 돌면 그 테이블의 모든 SELECT가 멈추고 사이트가 뻗는 게 MyISAM 시절의 전형적 장애다.

밸브가 있긴 하다. INSERT LOW_PRIORITY / SELECT HIGH_PRIORITY로 문장별로 뒤집거나, low_priority_updates=1로 전역으로 뒤집는다. 굶주림 방지용 max_write_lock_count도 있는데 — 기본값이 4294967295라 사실상 꺼져 있다. 가장 중요한 건 concurrent_insert(기본 1)로, 테이블 중간에 구멍(삭제된 자리)이 없으면 읽기와 동시에 끝에 append를 허용한다. MyISAM이 로그·통계 테이블에서 그럭저럭 버틴 게 이 덕분이다. 테이블을 망가뜨리는 건 UPDATE와 DELETE다 — 구멍을 만들고 진짜 배타적 잠금을 요구한다.

그럼 "쓰기 버퍼링이 없어서"라고 정리해도 되나? 안 된다. 두 개의 독립된 문제를 합치는 것이다. 사고 실험으로 갈라진다.

MyISAM에 완벽한 쓰기 버퍼를 달아줘도 SELECT는 여전히 막힌다. 디스크에 안 쓰더라도 테이블의 메모리 상태를 바꾸려면 배타적 잠금은 잡아야 하니까.
InnoDB에서 버퍼링을 전부 걷어내도 SELECT는 안 막힌다. 레코드 잠금 + MVCC니까 읽는 쪽은 언두에서 이전 버전을 가져간다. 쓰기만 느려질 뿐이다.

	쓰기 버퍼링 있음	쓰기 버퍼링 없음
테이블 잠금	읽기 막힘 (짧게)	읽기 막힘 (길게) ← MyISAM
레코드 잠금 + MVCC	안 막힘 (InnoDB)	안 막힘 (느릴 뿐)

버퍼링은 막히는 시간을 정하지, 막히느냐 여부를 정하지 않는다. 여부를 정하는 건 잠금 단위다. 다만 버퍼링이 없으면 잠금을 쥔 시간이 길어지니 가중 요인이긴 하다.

그리고 더 중요한 게 있다. MyISAM에 버퍼링이 없는 건 깜빡한 게 아니다. 쓰기를 미루려면 리두 로그가 있어야 한다. 미뤄둔 사이 서버가 죽어도 복구할 근거가 있어야 하니까. 4장의 "쓰기는 미뤄도 되지만 읽기는 못 미룬다"가 성립한 게 바로 리두 로그 덕분이었다. MyISAM은 트랜잭션이 없으니 리두도 없고, 리두가 없으니 쓰기를 미룰 자격이 없다.

트랜잭션 없음 (뿌리)
├── 리두 로그 없음  → 쓰기 버퍼링 불가         → 잠금을 오래 쥠 (가중 요인)
└── MVCC 없음      → 읽기가 잠금을 피할 길 없음 → 테이블 잠금      (원인)

버퍼링 부재와 읽기 굶주림은 부모-자식이 아니라 형제다. 한 문장으로 압축한다면 "트랜잭션이 없어서 그렇다" 가 가장 정확하고, 4장 전체를 꿰는 문장이기도 하다.

# 테이블 잠금과 레코드 잠금

차이는 잠그는 단위 하나뿐이고, 나머지는 전부 여기서 파생된다. 도서관에 비유하면 테이블 잠금은 책 한 권 수정하겠다고 도서관 문을 잠그는 것이고, 레코드 잠금은 그 책에만 "대출 중" 딱지를 붙이는 것이다.

100만 행 테이블에 서로 다른 행을 건드리는 UPDATE 10개가 동시에 들어오면, 테이블 잠금은 10개가 줄을 서고(겹치는 행이 없는데도) 레코드 잠금은 10개가 동시에 처리된다.

그런데 레코드 잠금은 공짜가 아니다. 테이블 잠금이 멍청해서 있는 게 아니다.

	테이블 잠금	레코드 잠금
관리 비용	잠금 1개만 추적	100만 행 UPDATE면 잠금 100만 개 추적
메모리	거의 안 씀	잠금 구조체만큼 씀
동시성	없음	높음
데드락	거의 안 생김	생김

데드락 항목이 재밌는데, MyISAM의 테이블 잠금은 사실상 데드락 프리다. 문장 시작 시 필요한 테이블 잠금을 한 번에 전부 잡아버려서 순환 대기가 생길 구조가 아니다. 반면 InnoDB는 레코드를 하나씩 잠가 나가니 A가 1번을 쥐고 2번을, B가 2번을 쥐고 1번을 기다리는 상황이 생긴다. 4.2.5절의 자동 데드락 감지 스레드가 존재하는 이유가 정확히 이것이고, 동시 스레드가 많아지면 그 감지 스레드가 CPU를 먹는다는 이야기도 결국 레코드 잠금의 청구서였다. 즉 레코드 잠금은 동시성을 사고 관리 비용과 데드락을 지불한 거래다.

그리고 실무에서 제일 많이 뒤통수를 치는 함정 — InnoDB의 레코드 잠금은 사실 "인덱스 잠금"이다. 행이 아니라 인덱스 레코드를 잠근다.

-- m_area 칼럼에 인덱스가 없다면?
UPDATE member SET m_name='X' WHERE m_area='서울';
-- 풀 스캔하면서 지나간 모든 레코드에 잠금 → 사실상 테이블 전체가 잠긴다

실제 변경되는 건 몇 건뿐인데 스캔한 전부를 잠근다. 레코드 잠금 엔진을 쓰는데 MyISAM처럼 동작하는 것이다. 그래서 "인덱스를 잘 만들어야 한다"는 조회 속도만의 문제가 아니라 동시성 문제이기도 하다. InnoDB인데 왜 테이블이 통째로 잠기냐는 질문의 답은 거의 항상 "인덱스가 없어서"다.

마지막으로 두 잠금은 서로 다른 층에 산다. 테이블 잠금은 MySQL 엔진 레벨(핸들러 API 위쪽), 레코드 잠금은 스토리지 엔진 레벨(InnoDB 내부)이다. 그래서 슬로우 쿼리 로그의 Lock_time이 테이블 잠금만 재고 InnoDB 레코드 잠금은 못 쟀던 것이다 — 계측하는 층이 다르니까. 4.4.3절에서 그냥 넘어갔던 그 함정이 여기서 구조적으로 설명된다. 같은 이유로 4.2.5절의 innodb_table_locks도 이해된다. 데드락 감지 스레드는 원래 자기 층만 보는데, 이 변수를 켜면 위층의 테이블 잠금까지 감지 대상에 넣어준다.

🐘 PostgreSQL 비교 — 구현이 꽤 다르다. InnoDB는 레코드 잠금을 메모리의 잠금 구조체로 관리해서 잠그는 행이 많아지면 메모리를 쓴다. PG는 튜플 헤더 자체에 표시한다(xmax에 잠근 트랜잭션 ID를 박아넣음). 그래서 PG는 수백만 행을 잠가도 잠금 메모리가 늘지 않는다. 참고로 두 DBMS 모두 잠금 에스컬레이션이 없다 — 행을 너무 많이 잠갔다고 테이블 잠금으로 멋대로 승격시키지 않는다(SQL Server는 승격시킨다).

갭 락(Gap lock), 넥스트 키 락(Next-key lock), 인텐션 락 같은 나머지 잠금은 5장(트랜잭션과 잠금)이 통째로 다루는 주제이니 여기선 넘어간다. 지금은 "단위가 다르다" 와 "InnoDB는 인덱스를 잠근다" 두 개만 챙기면 5장이 훨씬 수월하다.

# 쿼리 파서는 왜 굳이 트리를 만드나

4.1.6.1에서 파서가 "토큰으로 분리해 트리 형태의 구조로 만든다"고 하고 넘어갔는데, 왜 하필 트리일까? SQL이 평평하지 않기 때문이다. 토큰 리스트는 일직선인데 SQL의 의미는 중첩돼 있다. 그 낙차를 메우는 게 트리다.

2 + 3 * 4를 생각하면 바로 보인다. 토큰만 늘어놓으면 답이 14인지 20인지 알 수 없다. "곱하기가 먼저"라는 정보를 어딘가 적어둬야 하는데 일직선 리스트에는 그 자리가 없다. 트리는 그 자리를 만들어준다.

① 우선순위를 기록할 곳이 필요하다. SQL에서 이게 그대로 재현되고, 실무에서 사람 잡는 버그가 된다.

WHERE a = 1 OR b = 2 AND c = 3

토큰으로는 일직선이지만 의미는 둘로 갈린다. (a=1 OR b=2) AND c=3인가, a=1 OR (b=2 AND c=3)인가? 정답은 후자다. AND가 OR보다 우선순위가 높다. 많은 사람이 전자로 읽고 버그를 만든다. 트리는 그 판정을 구조로 못 박는다.

       OR
      /  \
  (a=1)   AND
         /   \
     (b=2)  (c=3)

② SQL은 재귀적이다. 이게 더 근본적이다.

SELECT * FROM t
 WHERE id IN (SELECT id FROM u
               WHERE x > (SELECT AVG(x) FROM v));

SELECT 안에 SELECT, 그 안에 또 SELECT. 깊이에 제한이 없다. 일직선 토큰으로는 "이 SELECT가 저 WHERE 안에 속한다"를 표현할 방법이 없다. 재귀 문법의 자연스러운 산출물이 재귀 자료구조, 즉 트리다. 트리를 고른 게 아니라 트리가 될 수밖에 없다.

③ 스코프는 트리에서만 정의된다. 4.1.6.2에서 전처리기가 "파서 트리를 기반으로" 객체 존재와 권한을 확인한다고 했는데, 왜 트리 기반이어야 할까?

SELECT * FROM outer_t
 WHERE x > (SELECT AVG(x) FROM inner_t WHERE id = outer_t.id);

안쪽 x는 inner_t의 것이지만 outer_t.id는 바깥을 참조한다(상관 서브쿼리). 이걸 판정하려면 "지금 어느 스코프 안인가" 를 알아야 하고, 스코프는 부모를 거슬러 올라가며 이름을 찾는 것이라 트리에서만 정의된다. 일직선 리스트에는 부모가 없다.

④ 옵티마이저가 변형하려면 트리여야 한다. 4장 맥락에선 이게 제일 중요하다. 그림 4.6의 옵티마이저 박스 안에 "쿼리 변환" 이라고 적혀 있었다. 옵티마이저 일의 절반이 쿼리를 다시 쓰는 것이다 — 서브쿼리를 조인으로 바꾸고, 조건을 밀어 넣고(predicate pushdown), 상수를 접고(WHERE x > 1+2 → WHERE x > 3), 불필요한 조건을 지운다.

이걸 토큰 리스트 위에서 하려면 문자열 치환 지옥이다. 트리에선 서브트리를 통째로 들어내고 다른 걸 꽂으면 끝이다. 즉 트리는 "변형 가능한 자료구조"라서 선택됐다. 옵티마이저가 존재하는 한 트리는 필연이다.

재미있는 덤 하나 — 괄호는 트리에 남지 않는다. 괄호는 트리를 어떻게 만들지 파서에게 알려주는 용도로만 존재하고, 트리가 완성되면 구조 자체에 흡수된다. (a+b)*c와 a+b*c는 토큰이 거의 같은데 트리가 다르고, 그 트리 모양이 곧 괄호의 의미다.

그리고 이 트리는 사라지지 않고 실행 계획의 뼈대가 된다. 8.0.16부터는 눈으로 볼 수 있다.

EXPLAIN FORMAT=TREE SELECT ...;

출력이 말 그대로 트리로 나온다. 들여쓰기된 그 계층이 이 이야기의 종착점이고, 10장에서 다시 만난다.

🐘 PostgreSQL 비교 — PG는 "트리는 변형하려고 있다"를 더 노골적으로 드러낸다. 파서 → 애널라이저 → 리라이터(Rule System) → 플래너로 단계가 명확히 갈리는데, 뷰가 아예 규칙(rule)으로 구현돼 있다. 뷰를 조회하면 리라이터가 쿼리 트리에서 뷰 노드를 뜯어내고 뷰 정의 트리를 그 자리에 붙인다. 트리 치환이 문서화된 1급 기능인 셈이다. MySQL은 이걸 별도 단계로 분리하지 않고 옵티마이저 안에서 처리한다(뷰의 MERGE/TEMPTABLE 알고리즘).

# 그림 4.7 / 4.8을 읽는 법 — 선순위 큐

4.1.9절의 그림 4.7(요청이 유입된 순서)과 4.8(선순위/후순위 큐로 재배치된 순서)은 그림이 헷갈리게 그려져 있다. 화살표를 따라 읽으면 COMMIT → QUERY → BEGIN이 나와서 트랜잭션 순서와 정반대가 된다.

함정은 화살표가 시간 축이 아니라는 것이다. 공항의 지그재그 대기줄을 떠올리면 된다. 새로 온 사람은 왼쪽 위로 들어와서 줄을 따라 걸어가고, 창구는 왼쪽 아래에 있다. 화살표는 사람이 걸어가는 방향일 뿐이다. 그러니 시간 순서로 읽으려면 왼쪽 아래부터 화살표를 거슬러 올라가야 한다.

그렇게 읽으면 등장인물 3명(대머리·수염·여성)의 요청이 이렇게 정리된다.

그림 4.7 (유입된 순서 = FIFO로 처리하면 이 순서)
  B(대머리) B(수염) B(여성) → Q(대머리) Q(수염) Q(여성) → C(대머리) C(수염) C(여성)

그림 4.8 (선순위 큐로 재배치)
  B(대머리) B(수염) B(여성) → Q(대머리) C(대머리) → Q(수염) C(수염) → Q(여성) C(여성)

BEGIN → QUERY → COMMIT으로 정상이고, 모든 라운드에서 대머리 → 수염 → 여성 순서가 유지된다. 원래 방향으로 읽으면 "커밋하고 나서 쿼리를 던지고 그 다음에 트랜잭션을 시작"하는 게 되어 애초에 불가능한 순서다. 말이 안 되면 읽는 방향이 틀린 것이다.

두 그림의 차이는 선순위 큐 on/off이지, 스레드 풀 유무가 아니다. 4.7은 "스레드 풀을 안 쓴 경우"가 아니라 재배치하기 전의 원본 순서다. 둘 다 스레드 풀이 깔려 있고, 스레드가 모두 사용 중인 포화 상태를 전제한다. 스레드가 놀고 있으면 요청이 오는 족족 실행되니 큐도 재배치도 없다.

	스레드 풀 없음	스레드 풀 + FIFO	스레드 풀 + 선순위 큐
스레드	커넥션당 1개	그룹당 제한	그룹당 제한
큐	없음	있음 (1개, FIFO)	있음 (2개, 우선순위)
순서 결정자	OS 스케줄러	도착 순서	MySQL이 재배치
해당 그림	—	4.7	4.8

여기서 인과관계가 중요하다. 스레드 풀이 없으면 큐 자체가 없다. 커넥션마다 자기 스레드가 있으니 바로 실행되고, 순서는 OS 스케줄러가 정하는데 OS는 트랜잭션이 뭔지 모른다. 스레드 풀이 동시 실행을 제한하면서 부산물로 큐가 생기고, 큐가 생겨야 비로소 "순서를 고를 수 있게" 된다. 선순위 큐는 스레드 풀의 부작용을 자산으로 바꾼 것이다. 그리고 포화 상태에서 스레드를 더 늘리는 건 스레드 풀을 쓰는 이유를 지우는 짓이니, 손댈 수 있는 레버가 순서밖에 없다.

그래서 왜 4.8이 빠른가 — "잠금은 쥐고 CPU는 안 쓰는" 좀비 구간 때문이다. 이게 급소다.

FIFO
  대머리 QUERY 실행 ─── 잠금 획득 🔒
     → 응답 → 클라이언트가 COMMIT 전송 → 큐 진입
     → ▓▓▓▓▓▓▓▓▓▓▓  앞에 선 수염QUERY·여성QUERY가 끝나길 대기
     →              이 구간 내내 잠금 🔒 을 쥔 채 CPU는 안 씀
     → 겨우 COMMIT 실행 → 잠금 해제 🔓

선순위 큐
  대머리 QUERY 실행 ─── 잠금 획득 🔒
     → 응답 → COMMIT 전송 → 선순위 큐로 → 바로 실행 → 잠금 해제 🔓

▓ 구간은 일은 안 하면서 자원만 붙잡고 있는 좀비 상태다. FIFO는 이 구간을 길게 만들고 선순위 큐는 짧게 만든다. 총 작업량은 9개로 똑같은데 빨라지는 이유가 이것이다. 재배치는 일을 줄이는 게 아니라 서로 밟는 시간을 줄인다. 참고로 COMMIT이 큐에서 기다리는 원인은 QUERY가 느려서가 아니라 그냥 큐가 차 있어서다. 앞선 QUERY가 0.1ms에 끝나도 앞에 20개가 줄 서 있으면 똑같이 기다린다.

오해하기 쉬운 것 — 이건 트랜잭션 직렬화가 아니다. B, B, B 이후 세 트랜잭션은 전부 열려 있다. 4.8에서 대머리의 Q·C가 처리되는 동안 수염과 여성의 트랜잭션이 닫혀 있는 게 아니라, 그저 그 순간 CPU를 못 받고 있을 뿐이다. 큐에 줄 서는 건 트랜잭션이 아니라 문장(statement) 이다. 그래서 "대기"를 두 종류로 갈라야 한다.

스레드 풀 큐 대기 = CPU를 기다림 (스케줄러가 만듦, 무조건 발생)
잠금 대기 = 남이 쥔 행을 기다림 (InnoDB가 만듦, 같은 행을 건드릴 때만)

선순위 큐의 목적은 ①을 똑똑하게 배치해서 ②를 줄이는 것이다. 완전히 다른 층이다. 덧붙여 BEGIN은 잠금을 잡지 않으므로 실제로 겹쳐서 문제가 되는 구간은 QUERY ~ COMMIT뿐이고, 그래서 BEGIN은 후순위 큐로 밀린다 — 아직 아무것도 안 쥔 트랜잭션은 급할 게 없으니까. 반대로 선순위 큐가 우대하는 건 COMMIT만이 아니라 "이미 열린 트랜잭션에 속한 모든 문장" 이다.

마지막으로 4.7은 FIFO고 4.8은 FIFO를 깬 것이다. 다만 완전히 버린 건 아니고 큐 하나하나는 여전히 FIFO인데 큐가 둘이라 전체로는 FIFO가 아닌 다단계 큐(multi-level queue)다. 그리고 여기서 데자뷰가 있어야 한다 — MyISAM의 쓰기 우선 큐와 똑같은 구조다.

	MyISAM 테이블 잠금	Percona 스레드 풀
우선 처리	쓰기 큐	선순위 큐(진행 중 트랜잭션)
밀리는 쪽	읽기 큐	후순위 큐(새 트랜잭션)
결과	읽기 굶주림	후순위 굶주림
밸브	`max_write_lock_count`	티켓 방식(`thread_pool_high_prio_tickets` 계열)

우선순위 큐를 도입하는 순간 굶주림이 따라온다. 예외가 없다. FIFO의 유일한 장점이 굶주림이 원천적으로 불가능하다는 것인데, 그걸 포기한 대가를 어딘가에서 갚아야 한다. 그래서 둘 다 "일정 횟수 우대받으면 강등"시키는 밸브를 달아뒀다.

# 격리 수준 요약 (4.2.3을 읽기 위한 최소한)

4.2.3 MVCC와 4.2.4 잠금 없는 일관된 읽기는 격리 수준을 이미 안다고 전제하고 설명한다. 정작 격리 수준 자체는 5.4절에서 다루므로, 4장을 읽는 데 필요한 만큼만 정리해둔다.

격리 수준이 하는 일은 딱 하나다 — "변경 중인 데이터를 다른 트랜잭션이 어디까지 볼 수 있는가"를 정한다. InnoDB에서는 이게 곧 "버퍼 풀의 최신값을 볼 것이냐, 언두의 이전 값을 볼 것이냐" 의 판정 기준이 된다.

격리 수준	무엇을 보나	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	버퍼 풀의 변경 중인 값	발생	발생	발생
READ COMMITTED	언두의 커밋된 최신 버전	없음	발생	발생
REPEATABLE READ ← InnoDB 기본	언두의 트랜잭션 시작 시점 버전	없음	없음	발생(InnoDB는 없음)
SERIALIZABLE	읽기도 잠금을 걺	없음	없음	없음

각 부작용이 무슨 뜻인지만 짚으면:

Dirty Read — 아직 커밋 안 된 값을 읽는 것. 그 트랜잭션이 롤백하면 존재한 적 없는 값을 본 셈이 된다. READ UNCOMMITTED가 4.2.3에서 "버퍼 풀의 변경 중인 데이터를 그대로 읽는다"고 한 게 이것이고, 표준에서 격리 수준으로 쳐주지도 않을 만큼 위험하다.
Non-Repeatable Read — 같은 트랜잭션 안에서 같은 SELECT를 두 번 했는데 결과가 다른 것. 중간에 다른 트랜잭션이 커밋해버려서다.
Phantom Read — 없던 레코드가 갑자기 나타나는 것.

여기서 두 가지가 4장과 직접 이어진다.

① REPEATABLE READ가 InnoDB 기본값인 이유. 트랜잭션 시작 시점의 번호보다 작은 언두 데이터만 보게 해서, 트랜잭션 내내 같은 결과를 보장한다. 4.2.3에서 "READ_COMMITTED나 그 이상의 격리 수준에서는 언두 영역의 데이터를 반환한다"고 한 그 메커니즘이다. 그리고 표준상 REPEATABLE READ는 팬텀 리드가 발생해야 하는데 InnoDB는 발생하지 않는다 — 갭 락과 넥스트 키 락이 막아주기 때문이다(5장 주제).

② SERIALIZABLE만 예외인 이유. 4.2.4가 "SERIALIZABLE이 아닌 격리 수준에서는 읽기가 잠금을 기다리지 않는다"고 한 게 여기서 설명된다. SERIALIZABLE은 순수한 SELECT조차 공유 잠금을 획득해서, 그동안 다른 트랜잭션이 그 레코드를 변경하지 못한다. 즉 "잠금 없는 일관된 읽기"라는 InnoDB의 무기를 스스로 꺼버리는 격리 수준이다. 그만큼 동시성이 떨어져 실무에선 거의 쓰지 않는다.

현재 값은 transaction_isolation 시스템 변수로 확인한다.

SELECT @@transaction_isolation;   -- 기본값: REPEATABLE-READ

🐘 PostgreSQL 비교 — 차이가 꽤 크고 실무에 직접 영향을 준다. 첫째, 기본값이 다르다. PG는 READ COMMITTED, MySQL은 REPEATABLE READ다. 같은 코드를 옮기면 같은 트랜잭션 안에서 두 번 읽었을 때 동작이 달라진다. 둘째, PG엔 READ UNCOMMITTED가 사실상 없다. 요청해도 READ COMMITTED로 동작한다 — 힙에 새 튜플을 덧붙이는 MVCC 구조상 미커밋 데이터를 볼 방법 자체가 없기 때문이다. 그래서 PG는 실질적으로 3단계다. 셋째, SERIALIZABLE의 구현이 정반대다. InnoDB는 잠금으로 막아서 다른 트랜잭션을 기다리게 한다. PG는 잠그지 않고 일단 다 진행시킨 다음, 커밋할 때 "이것들이 정말 한 줄로 세워서 실행한 것과 같은 결과인가"를 따져보고 아니면 트랜잭션을 실패시킨다(could not serialize access 에러). 기다리는 게 아니라 죽는다. 그래서 PG에서 SERIALIZABLE을 쓰려면 재시도 로직이 필수다. 이 방식을 SSI(Serializable Snapshot Isolation)라 부른다.

# 데드락 감지를 끈다는 게 정확히 무슨 뜻인가

4.2.5절을 읽고 innodb_deadlock_detect=OFF가 뭘 없애는 건지 헷갈렸는데, 정리하면서 오해 세 개를 걷어냈다.

① "데드락 감지 테이블" 같은 건 없다. 책이 말하는 건 잠금 테이블(잠금 목록) 이고, 이건 데드락 감지용 자료구조가 아니다. 그냥 "지금 누가 어떤 행에 잠금을 걸고 있는지"를 기록하는 InnoDB의 장부다. 레코드 잠금을 걸고 푸는 모든 트랜잭션이 이걸 쓴다. 데드락 감지 스레드는 그 장부를 읽는 여러 이용자 중 하나(그것도 유난히 무거운 하나) 일 뿐이다.

② 그래서 감지를 꺼도 잠금 테이블은 계속 쓰인다. 이게 핵심이다. 잠금 테이블은 레코드 잠금 그 자체의 구현이라 없앨 수가 없다. 없애면 누가 뭘 잠갔는지 아무도 모르니 잠금을 걸 수가 없다.

감지 ON :  [잠금 테이블 읽고 쓰기(모든 트랜잭션)]  +  [감지 스레드의 주기적 전체 스캔]
감지 OFF:  [잠금 테이블 읽고 쓰기(모든 트랜잭션)]                     ← 그대로!
                                                     ↑ 이것만 사라짐

즉 이득이 나는 이유는 장부를 없애서가 아니라, 장부를 통째로 훑으면서 잠가버리는 무거운 이용자 하나를 내쫓아서다.

③ 느려지는 이유는 "장부가 커서 조회가 느려져서"가 아니다. 그러면 감지를 꺼도 똑같이 느려야 한다. 실제로는 감지 스레드가 장부 전체를 훑는 동안 장부에 잠금을 걸어두기 때문이다(스캔 중에 목록이 바뀌면 안 되니까). 장부가 클수록 스캔이 오래 걸리고, 그동안 잠금을 걸거나 풀려는 서비스 스레드 전체가 대기한다. 부하의 원인은 목록의 길이이지 접근 빈도가 아니다.

정확한 사슬은 이렇다.

동시 스레드 ↑ / 트랜잭션당 잠금 ↑
   → 감지 스레드가 훑을 목록이 길어짐        ← 부하의 원인
   → 스캔이 느려짐 (+ CPU 더 소모)
   → 스캔하는 동안 잠금 테이블을 잠가둠       ← 전파 경로
   → 서비스 스레드들이 잠금 획득/해제를 못 함
   → 전체 성능 저하

덧붙여 희생자 선정도 정확히 해두면 — 감지 스레드는 먼저 순환 대기(사이클) 를 찾고, 그 사이클에 걸린 트랜잭션들 중에서 언두가 적은 놈을 고른다. 서버 전체에서 언두가 제일 적은 트랜잭션을 죽이는 게 아니다.

그럼 감지를 끄면 데드락은 어떻게 풀리나 — 타임아웃이 곧 해소다.

A는 B의 잠금을, B는 A의 잠금을 기다림   ← 교착
   → A가 innodb_lock_wait_timeout을 넘김
   → A의 쿼리가 에러 반환 "Lock wait timeout exceeded..."
   → 애플리케이션이 ROLLBACK
   → A의 잠금 해제 🔓 → B가 진행         ← 데드락 풀림

서버 재시작 같은 건 필요 없다. 애초에 데드락은 장애가 아니라 정상 운영 중에 늘 일어나는 일이고, 재시작으로 대응해야 하는 물건이면 아무도 DB를 못 쓴다. 책의 "무한 대기하게 될 것이다"는 "아무 중재 장치도 없다면"이라는 가정이고, 바로 다음 문장이 "하지만 innodb_lock_wait_timeout을 활성화하면..."으로 받는다. 타임아웃은 기본값 50초로 항상 켜져 있어서 실제로 무한 대기는 일어나지 않는다.

다만 여기 함정이 하나 있다. 타임아웃은 기본적으로 그 문장만 롤백한다(innodb_rollback_on_timeout 기본값이 OFF). 트랜잭션은 여전히 열려 있고 다른 잠금은 그대로 쥐고 있다. 그러니 애플리케이션이 에러를 받고 롤백해줘야 잠금이 풀린다. 앱이 에러를 삼키고 다음 문장을 계속 던지면 상대는 계속 굶는다. 정확히는 — 타임아웃은 무한 대기를 끊어주는 것이고, 실제 해소는 앱이 롤백할 때 완성된다. 그래서 재시도 로직이 필수다(감지를 켜두든 꺼두든 마찬가지다. 어느 쪽이든 앱은 에러를 받는다).

그리고 이 맞바꿈의 진짜 대가 — 감지와 타임아웃은 성격이 완전히 다르다.

	데드락 감지	`innodb_lock_wait_timeout`
정확도	정확함 — 진짜 순환 대기만 골라냄	무차별 — 오래 기다리면 이유 불문 실패
속도	즉시 감지	timeout만큼 기다려야 알 수 있음
희생자	언두가 적은 쪽(롤백이 싼 쪽)을 골라서	기다린 놈(선택권 없음)
대상	데드락만	모든 잠금 대기

즉 "정밀한 중재자"를 버리고 "무차별 칼"로 바꾸는 거래다. 진짜 데드락도 타임아웃을 꽉 채우고서야 실패하고(그동안 잠금은 계속 쥔 채로), 데드락이 아닌 정상적인 잠금 대기까지 애먼 실패를 한다. 그래서 이건 데드락이 구조적으로 거의 없다는 확신이 있을 때만 유효하다. 책이 든 구글 사례가 정확히 그 조건이다 — PK 기반 단순 조회·변경이 대부분이라 데드락이 애초에 안 생기고, 대신 동시 트랜잭션이 워낙 많아 감지 스레드 쪽이 더 문제였던 것이다. 접근 순서를 통제 못 하는 일반적인 서비스에서는 끄면 안 된다.

🐘 PostgreSQL 비교 — PG에는 이 문제 자체가 없다. 상시 도는 감지 스레드가 없기 때문이다. PG는 잠금을 기다리는 프로세스가 deadlock_timeout(기본 1초)을 넘겨서야 그때 처음 순환 검사를 한다. 즉 게으른 감지다. 평상시엔 아무도 장부를 훑지 않으니 "감지 스레드가 CPU를 먹고 잠금 목록을 잠가서 전체가 느려진다"는 이 절의 고민이 성립하지 않는다. 대신 PG는 진짜 데드락을 감지하는 데 최소 deadlock_timeout만큼 늦는데, 데드락이 드물다는 전제에선 이게 더 남는 장사다. innodb_lock_wait_timeout에 대응하는 건 별도로 lock_timeout이 있다.

# 정리

4장에서 실무로 들고 갈 것을 추리면 이렇다.

트랜잭션은 최대한 빨리 커밋/롤백하라. 오래 열어두면 언두가 안 지워져 서버 전체가 느려진다. 이 장에서 가장 반복되는 메시지다.
버퍼 풀 "80%"는 근거 없다. 8GB 미만이면 50%, 그 이상이면 50%부터 올려가며 찾는다.
버퍼 풀만 키워도 소용없다. 리두 로그 크기가 더티 페이지 한계를 정하므로 5~10GB 수준에서 같이 맞춰야 한다.
Lock_time은 InnoDB 레코드 잠금을 포함하지 않는다. 슬로우 쿼리는 Rows_examined 대 Rows_sent를 봐라.
어댑티브 해시 인덱스는 공짜가 아니다. 히트율을 보고 판단하고, DDL이 느려진다는 걸 기억하라.
foreign_key_checks=OFF는 CASCADE도 끈다.
8.0에서 쿼리 캐시는 사라졌고, MyISAM과 MEMORY는 쓸 이유가 없다. 엔진 선택은 이제 고민거리가 아니다. 레거시에서 MyISAM을 만나면 그건 선택이 아니라 화석이니 마이그레이션 후보로 보면 된다.
엔진을 섞지 마라. 한 트랜잭션에 MyISAM이 끼면 원자성이 깨진다.
COUNT(*)가 InnoDB에서 느린 건 버그가 아니라 MVCC의 필연적 결과다.
인덱스는 조회 속도만의 문제가 아니라 동시성 문제다. InnoDB의 레코드 잠금은 인덱스 잠금이라, 인덱스를 못 타면 스캔한 레코드를 전부 잠근다.
AND가 OR보다 우선순위가 높다. WHERE a=1 OR b=2 AND c=3은 a=1 OR (b=2 AND c=3)이다. 헷갈리면 그냥 괄호를 쳐라. 공짜다.

반대로 지금 외울 필요 없는 것들도 있다. 버퍼 풀 플러시 관련 변수들(innodb_io_capacity, innodb_adaptive_flushing 등)은 5.7~8.0에서 대부분 알아서 잘 처리되니 문제가 생겼을 때 찾아보면 되고, 언두 테이블스페이스의 롤백 세그먼트·슬롯 계산식도 기본값이 13만 개 동시 트랜잭션을 감당하므로 손댈 일이 거의 없다. innodb_force_recovery 1~6의 세부 의미도 장애가 났을 때 매뉴얼을 보면 된다.

← Real MySQL 8.0 - 3장. 사용자 및 권한 Swift - 기초 문법 정리 →