15장. 체인지 버퍼 — 그럼에도 견디는 이유

{/* 삽화 자리 — 손님마다 창고 안쪽까지 다녀오는 대신, 메모지에 적어 카운터에 쌓아두고 나중에 한 번에 처리하는 사람 */}

난수 키로 400만 행을 넣으면서 체인지 버퍼를 꺼봤습니다. 인덱스가 청구한 디스크 읽기가 11,145번에서 407,461번으로 늘었어요. 36.6배입니다. 그 39만 6천 번, 갔어야 할 왕복의 97.3%를 InnoDB가 조용히 안 가고 있었던 겁니다.

면접 실전 질문: ① 체인지 버퍼가 뭔가요? ② 왜 유니크 인덱스에는 안 통하나요? ③ 난수 키를 쓰는데도 왜 버틸 수 있나요?

배경 — 13장에서 한 번도 안 일한 장치

13장의 ibuf 병합 0은 규모 탓이었습니다 — 인덱스 93MB에 버퍼풀 1GB. 고칠 페이지가 늘 메모리에 있으니 미룰 일이 없었죠.

그런데 제가 이 이야기를 처음 쓴 자리는 그 조건이 아니었습니다. 운영 테이블은 4.6GB였고, 거기에 CUID2 난수 키 인덱스가 걸려 있었어요. 14장에서 본 대로 난수 키는 트리 여기저기를 건드립니다. 인덱스가 버퍼풀에 안 들어가면, 고칠 페이지를 매번 디스크에서 읽어와야 해요.

그래도 견뎠습니다. 제가 그때 쓴 답이 체인지 버퍼였고, 이것도 재보지 않았습니다.

그리고 13장이 미뤄둔 판정이 하나 있습니다. “난수 키 하나가 단조 증가 키 둘보다 비싸다”를 원문 조건에서도 재보기로 했죠. 이 장이 그 자리입니다.

스토리 — 쓰기에 읽기가 딸려 온다

세컨더리 인덱스에 값 하나를 넣는다고 해봅시다.

값이 들어갈 리프 페이지를 찾아야 합니다. 그 페이지가 버퍼풀에 있으면 고치면 끝이에요. 없으면 디스크에서 읽어온 뒤 고쳐야 합니다.

여기가 함정입니다. 쓰기 한 번에 읽기 I/O가 딸려 옵니다. 그리고 이 읽기는 순차가 아니라 랜덤이에요 — 어느 페이지가 필요할지는 키 값이 정합니다.

단조 증가 키는 이 일이 거의 안 생깁니다. 늘 맨 오른쪽 페이지에 붙으니, 그 페이지 하나만 메모리에 있으면 돼요.

난수 키는 매번입니다. LEFT(MD5(id),20)이 어디에 떨어질지 모르니, 인덱스가 메모리보다 크면 넣을 때마다 디스크를 한 번 긁어야 합니다.

InnoDB의 답이 체인지 버퍼입니다. 페이지를 읽지 않고 “이 페이지에 이런 변경이 있었다”를 따로 적어둬요. 나중에 그 페이지를 진짜로 읽을 일이 생기거나 백그라운드가 정리할 때 병합합니다.

랜덤 쓰기 하나하나를 랜덤 읽기로 바꾸는 대신, 모아뒀다 한 번에 처리하는 겁니다.

📄 문서 기반 (미검증) — 여기에 전제가 둘 붙습니다. 유니크 인덱스에는 안 통합니다 — 중복 검사를 하려면 어차피 페이지를 읽어야 하니까요. 그리고 페이지가 이미 버퍼풀에 있으면 체인지 버퍼를 거치지 않습니다. 13장에서 병합이 0이었던 게 두 번째 이유였고요.

재는 방법은 단순합니다. 변수를 바꿔가며 카운터 증가분을 잡으면 돼요.

SET GLOBAL innodb_change_buffering = 'none';   -- 또는 'all'
 
-- 삽입 전후로 이 둘을 찍는다
SELECT VARIABLE_VALUE FROM performance_schema.global_status
 WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads';        -- 디스크에서 실제로 가져온 페이지
SELECT COUNT FROM information_schema.INNODB_METRICS
 WHERE NAME = 'ibuf_merges';                              -- 미뤄뒀다 나중에 반영한 횟수

설명은 여기까지입니다. 이제 꺼봅니다.

핵심 — 39만 6천 번을 안 읽고 있었습니다

인덱스가 버퍼풀보다 확실히 크도록 판을 다시 깔았습니다. 400만 행, 테이블 369MiB, 난수 인덱스 리프 12,836장 = 200MiB. 버퍼풀은 128MiB입니다.

같은 데이터를 넣으면서 innodb_change_buffering을 all과 none으로 바꿔 재봤어요.

✅ 실측 (MySQL 8.0.46 / Docker / Apple M1 / macOS, 2026-08. 400만 행, 테이블 369MiB, 버퍼풀 128MiB / 인스턴스 1개. 각 조건 1회입니다. 재현: docker compose exec -T mysql mysql -uroot -pbookroot bookdb < ch15/seed-big.sql 뒤 BP_SIZE=128M BP_INSTANCES=1 BP_CHUNK=128M docker compose up -d --force-recreate mysql, 그리고 ./ch15/changebuffer.sh. 전체 소스: docs/book/code/db-index/ch15/changebuffer.sh)

먼저 읽는 법입니다. 물리 read의 기준선이 4만 7천대예요. 테이블이 369MiB인데 버퍼풀이 128MiB니, 인덱스가 하나도 없어도 원본을 읽느라 그만큼 나갑니다. 그러니 인덱스가 실제로 청구한 건 기준선을 뺀 증가분입니다.

물리 read가 36.6배입니다. 차이는 396,316번이에요.

체인지 버퍼가 없었으면 갔어야 할 디스크 왕복 40만 7천 번 중 39만 6천 번, 97.3%를 안 갔습니다. 400만 행으로 치면 열 행에 한 번꼴로 디스크를 아낀 거고요.

대조군 — 껐는지 켰는지도 모릅니다

같은 표에서 단조 증가 줄을 보세요. 원값 그대로 비교하면 됩니다.

체인지 버퍼를 껐는데 아무 일도 안 일어납니다.

당연합니다. 단조 증가 키는 늘 맨 오른쪽 페이지에 붙으니 그 페이지가 항상 메모리에 있어요. 미룰 일이 없으니 껐는지 켰는지도 모릅니다. 표의 ibuf 병합 열이 0인 것도 같은 이야기고요.

이게 이 장의 대조군입니다. 체인지 버퍼는 난수 키에만 일합니다.

유니크 인덱스에서는 끈 것과 같습니다

여기까지는 “체인지 버퍼가 있다/없다”였습니다. 그런데 켜두고도 안 통하는 경우가 있어요. 유니크 인덱스입니다.

값을 넣기 전에 중복인지 봐야 하고, 중복 검사를 하려면 어차피 그 페이지를 읽어야 하니까요. 미룰 방법이 없습니다.

같은 난수 키에 UNIQUE만 붙여 재봤습니다.

✅ 실측 (같은 환경. rnd1은 400만 행에 고유값 400만 개라 유니크 제약을 걸 수 있습니다)

유니크 인덱스는 체인지 버퍼를 끈 것과 정확히 같은 자리에 착지합니다. 409,912와 407,461이에요. 병합 횟수도 12,766에서 1,043으로 무너지고요.

켜져 있는데도 못 쓰는 겁니다. 그러니 난수 키에 유니크 제약이 필요하다면, 이 완충이 통째로 빠진다는 걸 계산에 넣어야 합니다.

그런데 시간은 1.25배밖에 안 벌었습니다

물리 read가 36.6배인데 시간은 62,195에서 77,957로 1.25배입니다. 이 격차가 어디서 나오는지 봐야 합니다.

8장에서 본 그 이야기예요. 페이지 요청이 시간이 되려면 그 읽기가 비싸야 합니다. 이 실험의 랜덤 읽기 한 번을 시간으로 환산하면 이렇습니다.

(77,957 − 62,195) ms ÷ 396,316회 ≈ 40 µs

8장에서 같은 계산이 6.5µs로 나왔고, 거기서 이렇게 적었습니다 — “NVMe SSD의 16KB 랜덤 읽기는 보통 50~100µs인데 열 배쯤 빠릅니다. Docker Desktop for Mac은 호스트 페이지 캐시가 뒤에 있어서 물리 read의 상당수가 진짜 디스크가 아닙니다.”

이 장의 40µs는 그 50~100µs 대역 근처로 올라왔습니다. 버퍼풀 128MiB에 데이터가 1GiB 가까이 되니 호스트 캐시도 못 버티는 거예요. 8장이 열어둔 질문에 이 장이 반쯤 답한 셈입니다.

📄 그리고 이건 저장장치가 빠른 환경의 값입니다. 느린 디스크나 네트워크 스토리지에서는 같은 36.6배가 시간에 더 크게 반영됩니다. 얼마나 큰지는 재보지 않았어요.

언제 깨어나는가

버퍼풀을 키워 다시 재봤습니다.

✅ 실측 (같은 환경, 각 1회. 재현: 버퍼풀만 바꿔 재기동한 뒤 ./ch15/changebuffer.sh)

512MiB에서는 병합이 0입니다. 13장에서 본 그 0이에요 — 미룰 일이 없으면 이 장치는 아무것도 안 합니다.

다만 “인덱스가 들어가서”라고 단정하진 않겠습니다. 같은 버퍼풀을 놓고 다투는 게 인덱스만이 아니거든요 — 원본 b_src 369MiB, 새로 쓰는 클러스터드 369MiB, 인덱스 200MiB로 워킹셋이 900MiB를 넘습니다. 512MiB에도 다 안 들어가요. 확실한 건 128MiB와 512MiB 사이에서 이 장치가 잠든다는 것이고, 인덱스 단독 크기 200MiB가 그 사이에 있다는 것입니다.

📄 1,024MiB에서도 재보려 했는데 결과가 이상했습니다. 난수 1개가 20분, 다시 재니 50분이 나왔어요. 물리 read는 4만대로 오히려 적고 병합도 0인데 시간만 폭발합니다. 원인을 못 밝혀서 이 표에서 뺐습니다. 버퍼풀을 키웠는데 느려지는 건 InnoDB의 성질이 아니라 이 실험 환경의 무언가로 보이지만, 확인하지 못했습니다.

13장이 미뤄둔 판정 — 시간 축만 뒤집힙니다

13장에서 제 문장을 재판대에 올렸습니다. “CUID 난수 키 하나가 단조 증가 키 둘을 합친 것보다 비싸다”요.

거기서는 네 축 전부 틀렸다고 나왔습니다. 다만 그 측정은 전부 메모리 안이었고, 원문이 말한 자리는 4.6GB 테이블이었어요. 그래서 “원문 조건에서도 틀렸는가”만 이 장으로 넘겼습니다.

먼저 밝힐 게 있습니다. 이 장이 닫은 건 그 조건 셋 중 하나뿐입니다. 13장이 적어둔 격차는 ① 메모리 안 ② 단일 트랜잭션 ③ 더티 페이지 write-back 제외였는데, 여기서 닫은 건 ①이에요. 여전히 INSERT ... SELECT 한 방이고 행당 커밋 핫패스가 아닙니다. (③은 절반쯤 닫혔습니다 — 버퍼풀 128MiB에 데이터 900MiB가 넘으니 삽입 중에 플러시가 강제로 일어나 벽시계에 들어옵니다.)

그러니 이건 “원문 조건”이 아니라 **“인덱스가 버퍼풀을 넘는 조건”**입니다. 그 조건에서의 결과입니다.

✅ 실측 (같은 환경. 단조 2개는 환산이 아니라 실제로 두 개를 걸어 잰 값입니다)

버퍼풀 하나 바꿨는데 판정이 뒤집힙니다.

128MiB에서는 난수 1개가 단조 2개보다 2.89배 비쌉니다 — 제 문장이 맞아요. 512MiB에서는 0.81배로 오히려 싸고요 — 제 문장이 틀립니다.

그런데 구조 축은 어느 쪽에서도 안 뒤집힙니다.

버퍼풀을 바꿔도 리프 장수는 그대로고, 자리는 난수 1개가 단조 2개보다 쌉니다. 13장의 판정이 이 규모에서도 유지되는 거예요.

정리하면 이렇습니다.

그러니 시간 축에서만, 그것도 특정 조건에서만 맞았습니다.

그리고 정확히 말하면 이렇습니다. 저는 재보지 않고 썼고, 그게 한 조건에서 맞았습니다. 조건을 알고 쓴 게 아니라 조건을 모른 채 맞은 거예요. 이 장이 없었으면 저는 지금도 그게 어느 쪽인지 몰랐을 겁니다.

문장이 틀렸던 게 문제가 아니라 문장에 조건이 없었던 게 문제였습니다. 이 책이 수치마다 환경을 적는 이유가 이거예요.

공짜는 아닙니다

여기까지만 보면 체인지 버퍼가 마법처럼 보입니다. 값도 있어요.

📄 문서 기반 (미검증) — 셋입니다.

1. 버퍼풀을 먹습니다. innodb_change_buffer_max_size 기본값이 25%라, 128MiB 풀이면 최대 32MiB를 가져갑니다. 뒤집어 보면 이 장의 36.6배는 보수적인 값이에요 — none 조건은 그 32MiB를 페이지에 다 쓸 수 있었으니까요.
2. 병합은 나중에 청구됩니다. 대량 적재 직후 조회나 DDL이 이유 없이 느리다면 밀린 병합일 수 있습니다. 이 실험에서도 끝난 시점에 안 갚은 항목이 남아 있었어요(표의 남은 ibuf 열).
3. 크래시 복구 때 재생해야 합니다.

그래서 실무에서는

마지막 줄이 이 장에서 제일 실용적입니다. 128MiB와 512MiB 사이에 병합 0과 12,766이 있고, 소요 시간으로는 17,794와 62,195가 있어요. 인덱스가 자라는 속도는 완만한데 이 구간을 지나면 비용이 확 달라집니다.

정리

• 세컨더리 인덱스에 값을 넣으려면 그 값이 들어갈 리프 페이지가 필요합니다. 버퍼풀에 없으면 쓰기 한 번에 랜덤 읽기가 딸려 옵니다.
• 체인지 버퍼는 그 페이지를 읽지 않고 “이런 변경이 있었다”를 적어둡니다. 나중에 병합해요.
• 실측: 난수 키 400만 행에서 체인지 버퍼를 끄면 인덱스가 청구한 물리 read가 11,145 → 407,461로 36.6배가 됩니다. 갔어야 할 왕복의 97.3%, 열 행에 한 번꼴로 디스크를 아낀 셈이에요.
• 단조 증가 키에는 아무 영향이 없습니다(시간 1.5%, 물리 read 0.4% 차이). 늘 맨 오른쪽 페이지에 붙으니 미룰 일이 없어서요. 체인지 버퍼는 난수 키에만 일합니다.
• 유니크 인덱스에서는 켜둬도 끈 것과 같습니다. 물리 read 증가분이 409,912로, 체인지 버퍼를 끈 407,461과 같은 자리예요. 중복 검사를 하려면 어차피 페이지를 읽어야 하니까요.
• 물리 read는 36.6배인데 시간은 1.25배입니다. 랜덤 읽기 한 번이 약 40µs라서요. 8장에서 같은 계산이 6.5µs였고 “NVMe라면 50~100µs여야 한다”고 열어뒀는데, 이 장이 그 대역 근처로 올라왔습니다.
• 체인지 버퍼는 평소에 잡니다. 512MiB에서는 병합이 0이고 128MiB에서 12,766으로 뜁니다. 그 사이 어딘가에서 깨어나요. 1,024MiB는 결과가 이상해서(20분·50분) 뺐고, 원인은 못 밝혔습니다.
• 13장이 미뤄둔 판정의 답: 시간 축만, 그것도 조건에 따라 뒤집힙니다. 128MiB에서 난수 1개는 단조 2개의 2.89배지만 512MiB에서는 0.81배예요. 반면 자리는 어느 조건에서도 난수 1개가 쌉니다(리프 12,836 대 16,990).
• 그러니 문장이 틀렸던 게 아니라 문장에 조건이 없었던 게 문제였습니다. 그리고 정확히는 — 재보지 않고 썼는데 한 조건에서 맞은 거예요. 조건을 알고 쓴 게 아닙니다.
• 📄 공짜는 아닙니다. 버퍼풀의 최대 25%를 쓰고, 병합은 나중에 청구되고, 크래시 복구 때 재생해야 합니다.

생각해볼 질문

1. 유니크 인덱스에서는 이 완충이 통째로 빠졌습니다. 그럼 난수 키에 유니크 제약이 꼭 필요할 때는 어떤 선택지가 있을까요?
2. 이 장의 물리 read 36.6배는 시간으로 1.25배였습니다. 랜덤 읽기가 40µs가 아니라 5ms인 저장장치라면 두 숫자는 각각 어떻게 될까요? (8장의 54MB/s를 떠올려보세요)
3. 128MiB에서는 난수 1개가 단조 2개보다 2.89배 비싸고, 512MiB에서는 0.81배로 쌉니다. 그럼 지금 내 테이블은 어느 쪽인지 어떻게 알 수 있을까요?

4부의 세 장이 전부 쓰기가 무는 값이었습니다. 키 모양이 정하는 계수(13장), 분할이 남기는 빈 공간(14장), 그리고 그걸 받아내는 완충(15장)이요.

그런데 이 값을 왜 치르고 있었죠. 읽기 때문입니다.

3부에서 인덱스 경로가 비쌌던 이유가 하나 있었습니다. 왕복이요 — 세컨더리 인덱스로 찾은 행마다 클러스터드 인덱스로 다시 내려가는 것. 12장은 그 왕복 때문에 옵티마이저가 13.1%에서 인덱스를 버렸고, 실제 교차점은 26~34%였습니다.

그럼 왕복이 아예 없으면 어떻게 될까요.

▶ 16장. 커버링 인덱스 — 왕복을 없애는 값