버퍼 풀과 페이지

행 하나를 읽는데 왜 16KB를 통째로 읽나

InnoDB는 디스크의 데이터를 행 단위가 아니라 페이지(기본 16KB)라는 덩어리로 읽고 씁니다. 한 번 읽은 페이지는 메모리의 버퍼 풀에 올려 두고, 그다음부터는 디스크에 가지 않고 메모리에서 읽습니다. 고칠 때도 메모리의 페이지만 고치고 디스크에는 나중에 씁니다.

일부만 실행으로 확인 · MySQL 8.4.11에서 밀려남이 없는 여섯 조합의 디스크 읽기와 쓰기를 실행으로 대조. 칸 수와 밀려나는 순서는 개념 모형

이 실습에 나오는 개념

버퍼 풀: InnoDB가 디스크에서 읽은 페이지를 올려 두는 메모리 영역이다. 필요한 페이지가 버퍼 풀에 있으면 디스크에 가지 않고 메모리에서 읽고, 없으면 디스크에서 읽어 와 버퍼 풀에 넣는다. 크기는 innodb_buffer_pool_size로 정하며, 가득 차면 오래 쓰이지 않은 페이지부터 내보내 자리를 만든다. 디스크는 메모리보다 훨씬 느리므로, 자주 쓰는 데이터가 버퍼 풀에 들어갈 만큼 크기를 주는 것이 성능에 큰 영향을 준다.
페이지(InnoDB): InnoDB가 데이터를 디스크에 저장하고 읽고 쓰는 기본 단위다. 기본 크기는 16KB이며, 한 페이지 안에 여러 행이 함께 들어 있다. 행 하나만 필요해도 그 행이 든 페이지를 통째로 읽어 오므로, 같은 페이지에 있는 다른 행들도 함께 메모리에 올라온다. 테이블의 행뿐 아니라 인덱스도 페이지 단위로 저장된다. 그래서 가까이 저장된 행을 이어서 읽는 작업은 디스크를 덜 읽고, 흩어진 행을 읽는 작업은 행마다 다른 페이지를 읽게 된다.
더티 페이지와 플러시: 더티 페이지는 버퍼 풀에서 내용을 고쳤지만 아직 디스크의 데이터 파일에는 쓰지 않은 페이지다. InnoDB는 행을 고칠 때 메모리의 페이지만 바꾸고, 데이터 파일에 쓰는 일(플러시)은 백그라운드 스레드가 나중에 모아서 한다. 커밋은 플러시를 기다리지 않으며, 그 사이에 서버가 꺼지면 Redo 로그로 변경을 다시 적용해 복구한다. 더티 페이지를 버퍼 풀에서 내보내려면 먼저 디스크에 써야 한다.
캐시 제거(Eviction) 정책: 캐시의 메모리가 가득 찼을 때 어떤 데이터를 지울지 정하는 규칙이다. 가장 오래 사용되지 않은 것을 지우는 LRU, 사용 횟수가 가장 적은 것을 지우는 LFU 등이 있다. Redis에서는 maxmemory-policy 설정으로 지정한다.
Undo 로그와 Redo 로그: Undo 로그는 변경 전의 데이터를 기록한 것으로, 트랜잭션을 롤백할 때 쓰이며, InnoDB에서는 다른 트랜잭션에 과거 버전을 보여 줄 때(MVCC)도 쓰인다. Redo 로그는 변경 내용을 기록한 것으로, 커밋된 변경이 데이터 파일에 완전히 쓰이기 전에 장애가 나더라도 재시작 시 이를 다시 적용해 복구하는 데 쓰인다.

이렇게 바꿔 보세요

처음 설정으로 — 2번 행은 처음 읽는데도 디스크에 가지 않습니다. 커밋해도 데이터 파일에는 바로 쓰지 않습니다.
페이지 넷을 세 칸에 — 칸이 모자라 1번 행의 페이지가 밀려나고, 다시 읽을 때 디스크에 또 갑니다.
칸이 넉넉하면 — 한 번 읽은 페이지가 모두 남아, 마지막 조회는 메모리에서 끝납니다.
자주 읽는 페이지는 남는다 — 밀려나는 것은 가장 오래 쓰이지 않은 페이지입니다.
고친 페이지가 밀려나면 — 디스크에 먼저 쓴 다음에야 내보낼 수 있습니다.
칸이 둘뿐이면 — 거의 모든 조회가 디스크로 갑니다.

처음 설정으로 실행하면

item 테이블의 행 16개는 디스크의 데이터 파일에 페이지 네 개(A, B, C, D)로 나뉘어 들어 있습니다. 한 페이지는 16KB이고 행이 4개씩 들어 있습니다. 버퍼 풀(메모리)에는 아직 이 테이블의 페이지가 없습니다.
SELECT … WHERE id = 1: 1번 행은 페이지 A에 들어 있습니다. 페이지 A가 버퍼 풀에 없으므로 디스크에서 페이지 A를 통째로(16KB) 읽어 빈 칸에 올립니다. 같은 페이지의 2, 3, 4번 행도 함께 메모리에 올라옵니다.
UPDATE item SET name = 'changed' WHERE id = 1: 버퍼 풀에 올라와 있는 페이지 A에서 1번 행을 고칩니다. 디스크는 읽지도 쓰지도 않습니다. 이렇게 메모리의 내용이 디스크와 달라진 페이지를 더티 페이지라고 합니다.
COMMIT: 무엇을 바꿨는지 적은 기록(Redo 로그)만 디스크에 쓰고 커밋이 끝납니다. 페이지 A는 더티 상태 그대로 버퍼 풀에 있고, 데이터 파일에는 아직 쓰지 않았습니다. 데이터 파일에 쓰는 일은 나중으로 미룹니다.
SELECT … WHERE id = 2: 2번 행은 페이지 A에 들어 있습니다. 페이지 A가 이미 버퍼 풀에 있으므로 디스크에 가지 않고 메모리에서 바로 읽습니다. 2번 행을 읽는 것은 처음이지만, 같은 페이지의 다른 행을 읽을 때 페이지째로 올라와 있었습니다.
SELECT … WHERE id = 1: 1번 행은 페이지 A에 들어 있습니다. 페이지 A가 이미 버퍼 풀에 있으므로 디스크에 가지 않고 메모리에서 바로 읽습니다. 고친 값 changed가 읽힙니다. 디스크의 데이터 파일에는 아직 옛 값 item1이 있습니다.
얼마 뒤: 백그라운드 스레드가 더티 페이지 A를 데이터 파일에 씁니다. 페이지는 버퍼 풀에 그대로 남고 더티 표시만 없어집니다. 언제 쓸지는 InnoDB가 정하며, 커밋은 이것을 기다리지 않습니다.

실행 결과

InnoDB가 디스크에서 읽는 단위는 행이 아니라 페이지입니다. 1번 행을 읽으면 같은 페이지의 2, 3, 4번 행도 함께 메모리에 올라오므로, 가까이 저장된 행을 이어서 읽을 때는 디스크에 다시 가지 않습니다.
행을 고쳐도 메모리의 페이지만 바뀌고, 데이터 파일에는 나중에 씁니다. 그 사이에 서버가 꺼져도 커밋한 내용을 잃지 않는 것은 Redo 로그 덕분입니다("Undo와 Redo 로그" 실습).

코드

-- item 테이블: 행 16개. 행 하나가 약 3.5KB라서 16KB 페이지 하나에 4개씩 들어간다
--   페이지 A: 1~4번 행    페이지 B: 5~8번 행
--   페이지 C: 9~12번 행   페이지 D: 13~16번 행
-- 시작할 때 버퍼 풀에는 이 테이블의 페이지가 하나도 없다

SELECT name FROM item WHERE id = 1;

START TRANSACTION;
UPDATE item SET name = 'changed' WHERE id = 1;
COMMIT;

SELECT name FROM item WHERE id = 2;
SELECT name FROM item WHERE id = 1;

이 모형의 전제

칸이 2~4개뿐인 버퍼 풀과 "가장 오래 쓰이지 않은 페이지를 내보낸다"는 단순한 규칙으로 만든 개념 모형입니다. 실제 InnoDB는 목록을 새 구간과 오래된 구간으로 나누고 새로 읽은 페이지를 목록의 중간에 넣는 변형된 LRU를 쓰며, 더티 페이지는 백그라운드의 페이지 클리너 스레드가 목록의 끝 쪽을 훑어 미리 써 둡니다(MySQL 8.4 참조 설명서 17.5.1 Buffer Pool, 17.8.3.5 Configuring Buffer Pool Flushing). 또 실제 조회는 행이 든 페이지 말고도 인덱스의 위쪽 페이지를 함께 읽습니다. 화면의 칸 수와 순서는 실제와 같지 않습니다. 실제로 확인한 것은 다음과 같습니다(이 PC의 MySQL 8.4.11). 페이지 크기는 16384바이트이고, 버퍼 풀 134217728바이트는 8192페이지였습니다. 행 하나가 약 3.5KB인 16행짜리 테이블을 만들어 잎 페이지마다 4행씩 들어간 것을 확인한 뒤, 밀려나는 일이 없는 여섯 조합(같은 페이지 순서 둘, 칸이 4개인 경우 넷)을 화면과 같은 순서로 실행해 조회마다 그 테이블의 데이터 파일을 읽었는지를 대조했습니다. 처음 읽는 페이지에서만 파일 읽기와 Innodb_buffer_pool_reads가 늘었고(첫 조회는 인덱스의 위쪽 페이지까지 3번, 그다음 새 페이지는 1번), 이미 올라온 페이지의 다른 행이나 같은 행을 읽을 때는 0이었습니다. UPDATE와 COMMIT 직후에는 파일 읽기와 쓰기가 0번이고 더티 페이지가 1개였으며, 데이터 파일에는 나중에 쓰였습니다(약 5초 뒤에 저절로 쓰인 경우도 있었고, 20초가 지나도 쓰이지 않아 그 테이블만 강제로 쓰게 한 경우도 있었습니다). 버퍼 풀이 가득 찰 때의 동작은 다른 테이블에 수십 MB를 넣어 확인했습니다. 버퍼 풀의 빈 페이지가 20개 안팎까지 줄었고, 그동안 건드리지 않은 테이블 여섯 개의 페이지 24개는 모두 버퍼 풀에서 사라져 다시 읽을 때 디스크에서 읽혔으며, 계속 읽은 테이블의 페이지 4개는 남아 있었습니다. 칸이 모자라 밀려나는 여덟 조합은 실제 버퍼 풀의 칸 수를 바꿀 수 없어 그대로 실행하지 못했고, 밀려나기 전까지의 읽기만 대조했습니다. 오래 쓰이지 않은 페이지부터 내보낸다는 순서는 문서 17.5.1의 설명을 따랐습니다. 더티 페이지는 디스크에 쓴 다음에야 내보낸다는 것은 InnoDB의 동작으로 알려진 내용이지만, 이번에는 실행으로도 문서의 문장으로도 직접 확인하지 못했습니다. 다른 사람과 함께 쓰는 서버라서 전체 상태 값에는 다른 세션의 활동이 섞일 수 있습니다.

앞 실습: Undo와 Redo 로그 · 다음 실습: 복제 지연과 CAP

이 실습에 나오는 개념

이 실습에서 보는 것

설정

실행 전에 결과 예측하기

코드

그림

실행 비교