04. 아키텍처
4.2 InnoDB 스토리지 엔진 아키텍처
MySQL의 스토리지 엔진 가운데 가장 많이 사용되는 InnoDB 스토리지 엔진이다. InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하기 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.
4.2.1 프라이머리 키에 의한 클러스터링
InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장된다. 즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며, 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다. 결과적으로 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정된다. 오라클 DBMS의 IOT(Index organized table)와 동일한 구조가 InnoDB에서는 일반적인 테이블의 구조가 되는 것이다.
InnoDB 스토리지 엔진과 달리 MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다. 그래서 MyISAM 테이블에서는 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다. 프리아머리 키는 유니크 제약을 가진 세컨더리 인덱스일 뿐이다. 그리고 MyISAM 테이블의 프라이머리 키를 포함한 모든 인덱스는 물리적인 레코드의 주소 값을 가진다.
따라서 MyISAM에는 클러스터링 개념이 없으므로 프라이머리 키도 그냥 유니크 인덱스일 뿐이다. MyISAM에서 모든 인덱스는 레코드의 실제 물리적 주소를 가리킨다. InnoDB는 PK가 특별한 위치(클러스터링 인덱스)라 구조적으로 완전히 다르다.
4.2.2 외래 키 지원
외래 키는 InnoDB만 지원하고 MyISAM, MEMORY에서는 사용할 수 없다. 서비스 환경에서는 성능/운영 문제 때문에 잘 안 쓰기도 하지만, 개발 환경에서는 데이터 참조 관계를 잡는 좋은 가이드가 되기도 한다. 단, InnoDB에서 외래 키는 양쪽 컬럼에 인덱스 필요하고, 변경 시 존재 여부 검사와 잠금 전파로 인해 데드락 위험이 있다.
외래 키 때문에 적재/스키마 변경이 실패할 수 있는데, 이런 경우에는 foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다. 외래 키 체크 작업을 일시적으로 멈추면 대략 레코드 적재나 삭제 등의 작업도 부가적인 체크가 필요 없기 때문에 훨씬 빠르게 처리할 수 있다.
외래 키 체크를 껐다고 해서 부모-자식 관계가 깨진 상태로 둬도 된다는 뜻은 아니다. 체크 해제 중에 부모 레코드를 삭제했다면 자식 레코드도 직접 삭제해 일관성을 맞춘 후에 활성화해야 한다. foreign_key_checks가 OFF면 외래키 관계의 부모 테이블에 대한 작업 ON DELETE CASCADE / ON UPDATE CASCADE 옵션도 동작하지 않는다.
참고) foreign_key_checks 시스템 변수는 적용 범위를 GLOBAL과 SESSION 모두 설정 가능한 변수다. 그래서 이런 작업을 할 때는 반드시 현재 작업을 실행하는 세션에서만 외래 키 체크 기능을 멈춰야 한다. 또한, 작업이 완료되면 현재 세션을 종료하거나 현재 세션의 외래 키 체크를 다시 활성화해야 한다.
4.2.3 MVCC(Multi Version Concurrency Control)
MVCC는 레코드 단위의 트랜잭션을 지원하는 DBMS가 제공하는 기능으로, 잠금을 사용하지 않고도 일관된 읽기를 보장하기 위해 사용된다. InnoDB는 Undo Log를 이용해 이를 구현하며, 하나의 레코드에 대해 여러 버전을 동시에 관리한다. 이를 이해하기 위해 격리 수준(Isolation level)이 READ_COMMITED인 MySQL 서버에서 InnoDB 스토리지 엔진을 사용하는 테이블의 데이터 변경을 어떻게 처리하는지 아래 사진을 통해 볼 수 있다.
INSERT INTO member(m_id, m_name, m_area) values(12,'홍길동','서울');
COMMIT;
UPDATE member SET m_area='경기' WHER m_id=12;
UPDATE가 실행되면 무슨 일이 생기나?
UPDATE member SET city='경기' WHERE m_id=12; 실행하면,
- InnoDB 버퍼 풀: 메모리 캐시에 있는 레코드 값이 '경기'로 바뀐다.
- 디스크(데이터 파일): 바로 반영될 수도 있고, 아직 아닐 수도 있다. (InnoDB가 쓰기 지연을 하기 때문)
즉, 메모리 버퍼 풀은 최신 값인데, 디스크는 나중에 동기화될 수 있다.
다른 사용자가 같은 레코드를 조회하면?
여기서 중요한 건 트랜잭션이 COMMIT 됐는가? 와 격리 수준이다.
- READ UNCOMMITTED (가장 낮은 격리 수준)
- InnoDB 버퍼 풀이나 데이터 파일로부터 변경되지 않은 데이터를 읽어서 반환한다.
- 즉, 다른 사용자가 조회하면 커밋이 안 됐어도 바뀐 값 '경기'를 그대로 본다.
- Dirty Read(더티 리드)가 발생한다.
- READ COMMITTED 이상 (REPEATABLE_READ, SERIALIZABLE)
- 아직 COMMIT 전이면 Undo Log에 저장된 옛날 값(ex. '서울')을 보여준다.
- 즉, 사용자는 아직 확정되지 않은 값은 못 보고, 이전 커밋된 값만 보게 된다.
- 이게 바로 MVCC(Multi Version Concurrency Control) 동작이다.
"멀티 버전"이라는 말의 의미
한 레코드가 동시에 여러 버전을 가질 수 있다는 뜻이다.
버전 1: COMMIT 된 이전 값 (Undo Log에 저장됨), 버전 2: 아직 COMMIT 되지 않은 새 값 (버퍼 풀에 있음)
어떤 버전을 보느냐는 격리 수준과 트랜잭션 상태에 따라 달라진다.
왜 Undo Log가 계속 쌓이나?
트랜잭션이 빨리 끝나면(Commit/Rollback) Undo Log도 빨리 지워진다. 하지만 트랜잭션이 오래 열려 있으면, 예전 데이터 버전들이 계속 보관돼야 하기 때문에 Undo 영역이 점점 커진다. 그래서 긴 트랜잭션은 시스템에 부담을 준다.
1. UPDATE 실행 시, 새 값은 버퍼 풀에 저장되고, 옛 값은 Undo 영역에 복사된다.
2. COMMIT 시: 지금 상태를 그대로 확정(영구 저장)한다.
3. ROLLBACK 시: Undo 영역의 옛 값을 복구하고 Undo는 삭제된다.
단, Undo 데이터는 다른 트랜잭션이 필요 없어질 때 비로소 삭제된다.
4.2.4 잠금 없는 일관된 읽기(Non-Locking Consistent Read)
InnoDB는 MVCC를 써서 SELECT 할 때 잠금을 안 걸고도 읽기가 가능하다. 그래서 다른 트랜잭션이 데이터를 바꾸고 있어도 언두 로그에 있는 이전 값을 읽을 수 있기 때문에 방해받지 않는다. 이걸 잠금 없는 일관된 읽기라고 한다. 격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ 수준인 경우 INSERT와 연결되지 않은 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다.
트랜잭션을 오래 끌면 언두 로그를 계속 유지해야 해서 서버가 느려질 수 있다. 그래서 트랜잭션은 빨리 커밋이나 롤백으로 트랜잭션을 완료하는 것이 좋다.
4.2.5 자동 데드락 감지
InnoDB는 교착 상태를 막기 위해 잠금 대기 목록을 그래프(Wait-for List)로 관리하고, 데드락 감지 스레드가 이를 주기적으로 확인해서 데드락이 발생하면 트랜잭션 하나를 강제로 종료한다. 이때 언두 로그가 적은 트랜잭션을 롤백하는데, 언두 처리 부담이 적고 트랜잭션 강제 롤백으로 인한 서버 부하도 줄일 수 있기 때문이다.
다만 InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서도 관리되는 테이블 잠금(LOCK TABLES 명령으로 잠긴 테이블)을 기본적으로 볼 수 없지만, innodb_table_locks 변수를 활성화하면 InnoDB 스토리지 엔진 내부의 레코드 잠금부터 테이블 레벨의 잠금까지 감지할 수 있다. 따라서 보통은 켜두는 게 좋다.
하지만 동시 처리 스레드가 많거나 각 트랜잭션이 가진 잠금이 많으면 데드락 감지 스레드가 느려진다. 데드락 감지 스레드는 잠금 목록을 검사해야 하기 때문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트(잠금 테이블)에 새로운 잠금을 걸고 데드락 스레드를 찾게 된다. 따라서 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업을 진행하지 못하고 대기하면서 서비스에 악영향을 미치게 되면서 더 많은 CPU 자원을 소모할 수도 있다.
이럴 땐 innodb_deadlock_detect를 꺼서 데드락 감지 스레드를 아예 막으면 된다. 물론 이 경우엔 InnoDB 스토리지 엔진 내부에서 2개 이상의 트랜잭션이 상대방이 가진 잠금을 요구하는 상황이 발생하게 되면 누군가 중재하지 않기 때문에 무한정 대기하게 된다. 따라서 innodb_lock_wait_timeout을 짧게 설정해서 일정 시간 안에 잠금을 얻지 못하면 자동으로 에러가 나도록 하는 것이 좋다.
4.2.6 자동화된 장애 복구
InnoDB는 장애나 손실로부터 데이터를 보호하기 위해 자동 복구 기능(여러 메커니즘)을 제공한다. MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된(Partial write) 데이터 페이지가 있으면 자동으로 복구 작업을 수행한다. 그래서 InnoDB는 웬만해서는 데이터 파일 손상이나 서버 시작 불가 같은 문제가 발생하지 않는다.
하지만 서버와 무관하게 디스크나 하드웨어 문제로 자동 복구가 불가능한 경우가 있다. 이때는 innodb_force_recovery 변수를 설정해서 서버를 시작해야 한다. 이 설정 값은 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 한다. 1부터 6까지 단계가 있으며 값이 커질수록 데이터 손실 가능성은 커지고 복구 가능성은 줄어든다. 어떤 문제가 있는지 알 수 없으면 1부터 차례대로 올려가며 재시도한다.
- InnoDB의 로그 파일이 손상됐다면 6으로 설정하고,
- InnoDB 테이블의 데이터 파일이 손상됐다면 1로 설정한다.
- 어떤 부분이 문제인지 알 수 없다면 해당 변수를 1~6까지 변경하면서 재시작해본다. 해당 변수 값이 커질수록 심각한 상황이어서 데이터 손실 가능성이 커지고, 복구 가능성은 적어진다.
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): 로그 리두를 실행하지 않는다. 크래시 직전의 변경 사항을 복구하지 않고 서버를 시작한다.
어떤 문제가 있는지 모를 땐 1부터 하나씩 올리면서 시도한다. 그래도 서버가 안 올라오면 결국 새로 구축해서 백업으로 복구해야 한다. 이때 풀 백업과 바이너리 로그가 있으면 장애 시점 직전까지 데이터를 되살릴 수 있고, 바이너리 로그가 없으면 마지막 백업 시점까지만 복구할 수 있다.
4.2.7 InnoDB 버퍼 풀
버퍼 풀(Buffer Pool) 은 InnoDB의 핵심 캐시 공간이다. 디스크에서 직접 읽고 쓰면 느리기 때문에, 데이터나 인덱스를 메모리에 올려두고 쓰는 거다. 쓰기도 모아서 처리하기 때문에 디스크에 여기저기 랜덤하게 쓰는 걸 줄여준다.
4.2.7.1 버퍼 풀의 크기 설정
일반적으로 전체 물리 80%로 설정하라는 말이 있지만 단순하게 정할 값은 아니다. 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해야 한다. MySQL 서버 내에서 메모리를 필요로 하는 부분은 크게 없지만 가끔 레코드 버퍼가 상당한 메모리를 사용하기도 한다.
레코드 버퍼는 클라이언트 세션이 테이블의 레코드를 읽거나 쓸 때 임시로 쓰는 공간이다. 커넥션 수가 많고 동시에 접근하는 테이블이 많으면 그만큼 이 공간도 많이 필요하다. 따로 설정할 수 있는 값은 없고, 전체 커넥션 수와 각 커넥션에서 접근하는 테이블 수에 따라 자동으로 크기가 정해진다. 필요 없으면 해제되기도 하므로 정확한 크기를 미리 계산할 수는 없다.
MySQL 5.7부터는 innodb_buffer_pool_size로 버퍼 풀 크기를 동적으로 늘리고 줄일 수 있다. 크게 늘리는 건 문제없지만, 줄이는 건 위험하니 웬만하면 작게 시작해서 크게 늘리는 방향으로 가는 것이 좋다. (처음 준비하는 경우 운영체제의 메모리 공간이 8GB 미만이면 50% 정도만, 그 이상이라면 50%에서 시작해서 조금 올라가면서 최적점을 찾는다. 50GB 이상이라면, 대략 15~30GB 정도 운영체제와 다른 응용 프로그램을 위해 남겨놓고 나머지를 할당한다.) 버퍼 풀은 내부적으로 128MB 단위(청크)로 나눠서 관리한다.
InnoDB 버퍼 풀은 원래 하나의 거대한 공간을 세마포어로 관리했는데, 이 때문에 동시에 여러 스레드가 접근하면 내부 잠금 경합이 자주 발생했다. 이를 줄이기 위해 버퍼 풀을 여러 개의 작은 단위로 쪼개 관리할 수 있게 개선된 것이다. 이렇게 나뉜 각각의 버퍼 풀을 버퍼 풀 인스턴스라고 부른다. innodb_buffer_pool_instances 변수로 인스턴스 개수를 조정할 수 있다. (기본값은 8개지만, 전체 버퍼 풀 크기가 1GB 미만이면 자동으로 1개만 생성된다. 일반적으로는 40GB 이하일 때 8개 정도 유지하면 되고, 메모리가 훨씬 크다면 인스턴스 하나당 약 5GB 정도가 되도록 설정하는 것이 권장된다.)
4.2.7.2 버퍼 풀의 구조
InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기(innodb_page_size)의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다. 버퍼 풀 안의 페이지는 세 가지 리스트로 관리된다.
- Free List → 아직 아무 데이터도 없는 빈 페이지 목록. 새 데이터가 필요할 때 여기서 꺼내 쓴다.
- LRU(Least Recently Used) List → 엄밀하게 MRU 리스트와 결합된 형태이다. Old 서브리스트 영역은 LRU, New 서브리스트 영역은 MRU라고 생각하면 된다. 오래 안 쓰인 페이지는 밀려나고, 자주 쓰인 페이지는 앞으로 와서 계속 살아남는다. (디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하기 위한 목적)
- Flush List → 디스크에 아직 기록되지 않은 변경된 데이터(Dirty Page) 목록이며, 나중에 디스크에 기록해야 하는 리스트다.
데이터 찾는 과정
- SELECT로 데이터를 찾을 때, 먼저 버퍼 풀에 있는지 확인한다. 있으면 그대로 쓰고, 없으면 디스크에서 읽어서 버퍼 풀에 넣는다.
- 가져온 페이지는 처음엔 LRU 앞쪽에 들어가고, 실제로 사용되면 MRU 영역으로 승격된다.
- 오래 안 쓰이면 LRU 끝으로 밀려나고, 결국 제거된다.
- 어떤 페이지가 자주 접근되면 그 키를 어댑티브 해시 인덱스에 등록해서 더 빠르게 찾을 수 있다.
- UPDATE 같은 변경이 발생하면,
- 변경 내용을 Redo Log에 먼저 기록하고 -> 버퍼 풀 페이지에 변경 반영하고 -> 페이지는 Flush List에 올라간다.
- 나중에 체크포인트 과정에서 Flush List 페이지와 Redo Log가 디스크에 반영된다.
4.2.7.3 버퍼 풀과 리두 로그
InnoDB의 버퍼 풀은 메모리에 데이터를 캐싱하는 공간이라 크기가 클수록 디스크 접근을 줄여 쿼리 성능이 빨라진다. 하지만 버퍼 풀만으로 디스크의 모든 데이터를 다 담을 수 있을 정도라면 더 이상 늘려도 성능에는 추가 이득은 없을 것이다. (데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데, 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐시 기능만 향상시키는 것이다.)
- 버퍼 풀에는 디스크에서 읽은 깨끗한 페이지(클린 페이지)와 수정된 페이지(더티 페이지)가 함께 있다.
- 더티 페이지는 언젠가는 디스크에 기록돼야 하지만, 버퍼 풀 안에 무한정 둘 수는 없다.
- 리두 로그는 변경 내역을 기록하는 고정 크기 파일 묶음이다.
- 순환 구조라서, 일정 시간이 지나면 예전 로그를 덮어써야 한다.
- 그런데 더티 페이지가 아직 디스크에 내려가지 않았다면, 그와 관련된 로그는 덮어쓸 수 없다.
- 이렇게 덮어쓸 수 없는 공간을 활성 리두 로그(active redo log)라고 부른다.
- 리두 로그의 기록 위치는 계속 증가하는데, 이 위치 값을 LSN(Log Sequence Number)이라고 한다.
- InnoDB는 주기적으로 체크포인트를 만들어 더티 페이지를 디스크로 기록하고, 그 시점의 LSN을 "안전하게 덮어쓸 수 있는 기준점"으로 삼는다.
- 가장 최근 체크포인트 LSN = 활성 로그의 시작점
- 활성 로그의 끝 = 마지막 로그 LSN (계속 늘어남)
- 체크포인트 에이지(Checkpoint Age) = (마지막 로그 LSN) - (가장 최근 체크포인트 LSN)
- 즉, 아직 디스크에 기록되지 않은 변경 내역의 크기이다.
InnoDB의 더티 페이지는 리두 로그 엔트리와 연결돼 있어서, 체크포인트 시점보다 작은 로그와 관련된 더티 페이지는 반드시 디스크로 내려가야 한다.
이제 버퍼 풀의 더티 페이지 비율과 리두 로그 파일 크기의 관계를 예제로 보자.
1. 버퍼 풀 100GB, 리두 로그 파일 100MB
- 리두 로그 크기가 100MB밖에 안 되므로 체크포인트 에이지도 최대 100MB까지만 가능하다.
- 리두 로그 엔트리 평균 크기를 4KB라고 하면, 100MB / 4KB = 약 25,600개의 로그 엔트리를 저장할 수 있다.
- 데이터 페이지 크기를 16KB라고 가정하면, 25,600 × 16KB = 약 400MB 정도의 더티 페이지만 버퍼 풀에 둘 수 있다.
- 즉, 버퍼 풀이 100GB나 되더라도 실제로는 400MB만 쓰기 버퍼링 효과를 볼 수 있다. 사실상 버퍼 풀이 매우 작게 동작하는 것과 같다.
2. 버퍼 풀 100MB, 리두 로그 파일 100GB
- 같은 방식으로 계산하면 약 400GB까지 더티 페이지를 가질 수 있는 구조가 된다.
- 하지만 버퍼 풀이 100MB밖에 안 되므로 실제로는 100MB까지만 더티 페이지를 들고 있을 수 있다.
- 즉, 리두 로그를 크게 잡아도 버퍼 풀이 작으면 효과가 없다.
두 경우 모두 좋은 설정은 아니다.
- 1번은 리두 로그가 너무 작아서 쓰기 버퍼링 효과가 거의 없다.
- 2번은 이론적으로는 문제없어 보이지만 실제로 운영하면 위험하다. 버퍼 풀에 더티 페이지가 많이 쌓인 상태에서 갑자기 버퍼 풀이 필요해지면, InnoDB가 엄청난 양의 더티 페이지를 한 번에 디스크로 내려야 하는 상황이 생긴다. 그 순간 디스크 I/O 폭발이 일어나면서 성능이 크게 떨어질 수 있다. 만약 리두 로그가 무조건 크면 좋았다면, 오라클이 기본값을 1~200GB로 잡아놨을 것이다. 하지만 실제 기본값은 훨씬 작다. 그 이유는 너무 크게 잡아도 안정적이지 않기 때문이다.
따라서, 처음에 리두 로그 크기를 정확히 정하기 어렵다면, 버퍼 풀이 100GB 이하인 서버에서는 리두 로그 전체 크기를 보통 5~10GB 정도로 설정하는 게 좋다. 그리고 운영 상황을 보면서 필요할 때 조금씩 늘려 최적점을 찾는 방식이 권장된다.
참고) 버퍼 풀은 데이터 페이지를 통째로 담고, 리두 로그는 변경 내용만 기록하므로 버퍼 풀 크기와 리두 로그 크기가 같을 필요는 전혀 없다. 리두 로그는 버퍼 풀보다 훨씬 작은 공간이면 충분하다.
4.2.7.4 버퍼 풀 플러시(Buffer Pool Flush)
MySQL 5.6까진 더티 페이지(아직 디스크에 안 내려간 데이터)를 디스크로 기록할 때 한꺼번에 확 몰아서 쓰는 경우가 많아서 갑자기 디스크 I/O가 폭증하고, 그 순간에 사용자 쿼리 성능이 떨어지는 문제가 자주 생겼다. 이후, 8.0으로 오면서는 이 과정이 훨씬 부드럽게 바뀌어서, 보통 서비스에서는 갑자기 디스크 쓰기가 몰려 성능이 떨어지는 현상이 거의 없게 됐다.
버퍼 풀 플러시(Buffer Pool Flush)는 더티 페이지를 디스크에 내려보내는 작업으로 관련된 시스템 설정을 볼 예정이지만, 특별히 서비스를 운영할 때 성능문제가 발생하지 않는 상태라면 굳이 조정할 필요는 없다.
InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 다음과 같이 2개의 플러시 기능을 백그라운드로 실행한다.
4.2.7.4.1 플러시 리스트 플러시
InnoDB는 리두 로그 공간 재활용을 위해 오래된 로그 공간을 비우는데, 이를 위해 먼저 버퍼 풀의 더티 페이지를 디스크에 기록해야 한다. 이 과정은 플러시 리스트 플러시 함수가 주기적으로 실행되며, 오래된 페이지부터 순서대로 디스크에 동기화된다. 얼마나 자주/얼마나 많은 페이지를 쓰느냐에 따라 쿼리 성능에 영향을 주므로 관련 시스템 변수를 제공한다.
관련 시스템 변수와 기능
(1) innodb_page_cleaners
- 더티 페이지를 디스크에 쓰는 스레드를 클리너 스레드(Cleaner Thread)라 한다.
- innodb_page_cleaners : 클리너 스레드 개수 설정.
- 버퍼 풀 인스턴스 수(innodb_buffer_pool_instances) 보다 많으면 자동으로 맞춰진다.
- 적으면 하나의 스레드가 여러 인스턴스를 담당한다.
- innodb_page_cleaners = innodb_buffer_pool_instances와 동일하게 설정하는 것이 좋다.
(2) innodb_max_dirty_pages_pct
- 버퍼 풀 전체 페이지 중 최대 더티 페이지 비율(%)이다. 기본적으로 90%까지 가능하다.
- 비율이 높을수록 여러 번의 쓰기를 모아 한 번에 처리하고 → 쓰기 효율이 높아진다.↑
- 너무 높으면 디스크 쓰기 폭증 위험하다. 보통 기본값 유지를 권장한다.
(3) innodb_max_dirty_pages_pct_lwm
- 더티 페이지 비율이 특정 수준(Low Water Mark)을 넘으면 조금씩 디스크로 기록하도록 유도한다.
- 기본값은 10%이다.
- 만약 더티 페이지는 적은데도 디스크 쓰기가 과도하다면 값을 높여 조정한다.
(4) innodb_io_capacity / innodb_io_capacity_max
- IInnoDB가 "내가 디스크에 얼마나 쓰기/읽기를 해도 되지?" 하고 참고하는 허용치이다.
- innodb_io_capacity: 일반 상황에서 디스크 처리 가능량.
- innodb_io_capacity_max: 최대 성능 기준 처리 가능량.
- 이 값이 의미하는 IO작업은 백그라운드 스레드가 수행하는 디스크 작업(버퍼 풀의 더티 페이지 쓰기)이다. 하지만 사용자의 쿼리를 처리하기 위해 디스크 읽기도 해야 하기 때문에 디스크의 최대 성능을 그대로 설정하지 말고, 일부 여유를 남겨서 설정해야 한다.
(5) innodb_adaptive_flushing / innodb_adaptive_flushing_lwm
- 많은 서버를 관리할 때 IO 설정(innodb_io_capacity, innodb_io_capacity_max)이 번거로워서 제공되는 어댑티브 플래시 기능이다.
- 단순 버퍼 풀의 더티 페이지 비율, 위 IO 설정 값 대신 리두 로그 증가 속도를 기준으로 더티 페이지 쓰기 량을 동적으로 조절한다.
- innodb_adaptive_flushing_lwm는 리두 로그 공간 사용률이 일정 수준 이상(기본 값 10%) 일 때만 작동한다.
(6) innodb_flush_neighbors
- 디스크에 더티 페이지를 기록할 때, 근처 페이지도 더티면 같이 기록할지 여부.
- HDD 환경에서는 디스크 읽고 쓰는 것이 매우 고비용 작업이기 때문에 활성화(1 or 2)하는 것이 디스크 IO 절감 효과가 있다.
- SSD 환경은 기본값(비활성)을 유지하길 권장한다.
4.2.7.4.2 LRU 리스트 플러시
InnoDB는 새로운 데이터 페이지를 버퍼 풀에 읽어오기 위해, LRU(Least Recently Used) 리스트에서 오래 안 쓰인 페이지들을 제거해야 한다. 이때 LRU 리스트 플러시 함수가 사용된다.
LRU 리스트의 끝부분부터 시작해, 최대 innodb_lru_scan_depth에 지정된 개수만큼 페이지를 스캔한다. 스캔 중에 발견한 더티 페이지는 디스크에 동기화 후 프리(Free) 리스트로 이동한다. InnoDB는 버퍼 풀 인스턴스 단위로 동작한다. 따라서 실제 스캔 개수 = innodb_buffer_pool_instances × innodb_lru_scan_depth이다.
4.2.7.5 버퍼 풀 상태 백업 및 복구
InnoDB 버퍼 풀은 쿼리 성능과 직결된다. 서버를 셧다운 후 재시작하면 버퍼 풀이 비어 있기 때문에, 쿼리 성능이 평소의 1/10 이하로 떨어지는 경우가 많다. 버퍼 풀에 자주 쓰이는 데이터가 미리 적재된 상태를 워밍업(Warming Up)이라 하며, 워밍업 된 상태에서는 수십 배 빠른 성능을 보인다. MySQL 5.5 시절에는 서버 재시작 시 강제로 주요 테이블/인덱스를 풀 스캔 하여 워밍업을 했었다.
MySQL 5.6부터는 버퍼 풀 덤프(백업) 및 로드(복구) 기능이 도입되었다. 셧다운 전에 버퍼 풀 상태를 저장하고, 재시작 후 다시 불러올 수 있다.
버퍼 풀 상태 백업
SET GLOBAL innodb_buffer_pool_dump_now = ON;
버퍼 풀 상태 복구
SET GLOBAL innodb_buffer_pool_load_now = ON;
- 백업된 내용은 데이터 디렉터리의 ib_buffer_pool 파일에 기록된다.
- 이 파일은 페이지 자체가 아니라 LRU 리스트에 있는 페이지 메타 정보만 저장하기 때문에 크기가 작고(수십 MB 이하), 백업 속도도 매우 빠르다.
- 하지만 복구 시에는 메타 정보 기반으로 실제 테이블 데이터를 다시 디스크에서 읽어오기 때문에, 버퍼 풀 크기에 따라 시간이 오래 걸릴 수 있다.
- 따라서 InnoDB 스토리지 엔진은 버퍼 풀을 다시 복구하는 과정이 어느 정도 진행됐는지 확인할 수 있게 상태 값을 제공한다.
복구 진행 상황 확인
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'\G
복구 도중 시간이 너무 오래 걸려 중단
SET GLOBAL innodb_buffer_pool_load_abort = ON;- 버퍼 풀 복구 과정은 디스크 읽기를 많이 사용하기 때문에, 복구 도중 서비스 재개는 권장되지 않는다.
- 급히 서비스를 시작해야 한다면 복구를 중단하고 서비스만 올리는 것이 낫다.
수동으로 매번 덤프/로드를 실행하는 것은 번거롭고 까먹기 쉽다. InnoDB는 이를 자동화할 수 있는 옵션을 제공한다.
- innodb_buffer_pool_dump_at_shutdown → 셧다운 시 자동 백업
- innodb_buffer_pool_load_at_startup → 재시작 시 자동 복구
참고) ib_buffer_pool은 반드시 셧다운 하기 직전의 파일일 필요는 없다. ib_buffer_pool 파일에 기록된 데이터 페이지 정보가 실제로 존재하지 않는 경우도 있는데, 이 경우 단순히 무시된다. 따라서 이 파일 내용 때문에 MySQL 서버가 비정상 종료되거나 문제를 일으키지는 않는다.
4.2.7.6 버퍼 풀의 적재 내용 확인
MySQL 5.6 이전 방식
- information_schema.innodb_buffer_page 테이블을 통해 InnoDB 버퍼 풀에 어떤 테이블 페이지가 올라와 있는지 확인 가능하다.
- 하지만 버퍼 풀이 클 경우 조회 자체가 큰 부하를 일으켜 서비스 쿼리가 느려지는 문제 발생한다.
- 그래서 실제 서비스용 서버에서는 사실상 사용하기 어렵다.
MySQL 8.0 개선
- information_schema.innodb_cached_indexes 테이블이 새로 도입됐다.
- 이를 통해 테이블의 인덱스별로 버퍼 풀에 적재된 페이지 수를 확인 가능하다.
조금 응용하면 특정 테이블이 전체 페이지 중 얼마나 버퍼 풀에 적재돼 있는지 비율도 조회 가능하다.
- MySQL 서버는 인덱스별 전체 페이지 개수를 직접 알려주지 않는다.
- 따라서 테이블 단위로 전체 페이지 수와 캐시된 페이지 수만 비교할 수 있다.
- 즉, “테이블의 각 인덱스별 캐시 비율”은 확인 불가하고, “테이블 전체 캐시 비율”만 계산 가능하다.
4.2.8 Double Write Buffer
파셜 페이지(Partial-page) 문제
- InnoDB 리두 로그는 공간 낭비를 막기 위해 페이지 전체가 아닌 변경된 부분만 기록한다.
- 따라서 더티 페이지를 디스크로 기록할 때 일부만 기록되면 해당 페이지를 복구할 수 없을 수도 있다.
- 이런 현상을 파셜 페이지(Partial-page), 톤 페이지(Torn-page)라고 한다.
- 원인은 하드웨어 오작동, 시스템 비정상 종료 등이 있다.
Double Write 기법
이 문제를 막기 위해 Double Write Buffer를 사용한다. 더티 페이지(A~E)를 실제 데이터 파일에 랜덤 쓰기 하기 전에, 먼저 시스템 테이블스페이스 내 Double Write 버퍼에 모아서 한 번에 순차적으로 기록한다. 이후 각 페이지를 데이터 파일의 적절한 위치에 랜덤 쓰기를 실행한다.
- 데이터 파일에 정상적으로 기록됐다면 Double Write 버퍼는 필요 없어진다.
- 만약 일부 페이지가 쓰기 도중 실패했다면, InnoDB는 재시작 시 Double Write 버퍼와 데이터 파일을 비교해 불일치하는 페이지를 복구한다.
- Double Write 기능은 innodb_doublewrite 시스템 변수로 제어 가능하다.
HDD 환경에선 순차 쓰기 비용이 크지 않아 부담이 적기 때문에 → Double Write 활성화가 일반적이다. SSD 환경에선 랜덤 IO와 순차 IO 비용 차이가 적어, Double Write는 성능 부담이 크다.
그러나 데이터 무결성이 중요한 서비스라면 활성화 권장한다. 반대로, 성능을 위해 innodb_flush_log_at_trx_commit 값을 1이 아닌 값으로 두었다면 Double Write를 비활성화하는 편이 낫다.
4.2.9 언두 로그
InnoDB는 트랜잭션과 격리 수준을 보장하기 위해, 변경되기 전 데이터(이전 버전)를 따로 저장한다. 이 백업된 데이터를 언두 로그(Undo Log)라고 한다.
- 트랜잭션 롤백: 커밋 전 변경이 취소되면 언두 로그를 이용해 원래 값으로 복구한다.
- 격리 수준 보장: 다른 트랜잭션이 변경 중인 데이터를 읽을 때, 언두 로그의 이전 버전을 반환한다.
언두 로그는 InnoDB 스토리지 엔진에서 매우 중요한 역할을 담당하지만 관리 비용도 많이 필요하다.
여기서는 언두 로그가 어떤 문제점을 가지고 있고, 이를 위해 InnoDB 스토리지 엔진이 어떤 기능을 제공하는지 살펴보자.
4.2.9.1 언두 로그 레코드 모니터링
언두 영역은 INSERT, UPDATE, DELETE 실행 시 변경 전 데이터를 저장한다. UPDATE member... 실행 시 버퍼 풀에는 새 값이 반영되지만, 언두 로그에는 이전 값이 저장된다. 커밋하면 언두 로그는 불필요해지고, 롤백 시 언두 로그를 이용해 복구한다.
언두 로그의 용도는 다음과 같다.
- 트랜잭션 롤백 대비
- 트랜잭션 격리 수준 보장 (MVCC, 높은 동시성 제공)
문제점 (MySQL 5.5 이전)
- 언두 로그 공간은 한 번 커지면 줄어들지 않는다. 예를 들면, 1억 건 100GB를 DELETE 하면, → 언두 로그 공간도 테이블 크기만큼 증가한다.
- 트랜잭션이 길어지면 언두 로그가 계속 유지되어 디스크 공간 증가와 성능 저하가 발생한다. B, C 트랜잭션은 완료됐지만, 가장 먼저 시작된 A가 완료되지 않은 상태에서 B, C는 변경을했으므로 변경 이전의 데이터를 언두 로그에 백업했을 것이다. 하지만 먼저 시작된 A가 활성 상태이기 때문에 B,C 완료 여부와 관계없이 B, C의 언두로그는 삭제되지 않는다.
개선 (MySQL 5.7/8.0 이후)
- 언두 로그 공간을 순환 사용하며, 필요시 자동으로 축소 가능해졌다.
- 하지만 여전히 장시간 열린 트랜잭션은 여전히 성능에 악영향을 준다. 따라서 트랜잭션 관리가 중요하다.
모니터링 방법
- 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';
서버별 언두 로그 레코드 건수의 기준치를 정하고, 급증 여부를 모니터링하는 것이 바람직하다.
4.2.9.2 언두 테이블스페이스 관리
언두 테이블스페이스(Undo Tablespace)는 언두 로그가 저장되는 공간이다.
버전별 변화
- MySQL 5.6 이전: 언두 로그는 시스템 테이블스페이스(ibdata.ibd)에 저장 → 서버가 초기화될 때 생성되기에 확장 한계가 있다.
- MySQL 5.6: innodb_undo_tablespaces 도입 → 별도 언두 로그 파일 사용 가능하다.
- MySQL 8.0.14 이후: innodb_undo_tablespaces 폐지하고, 항상 별도 언두 로그 파일을 사용 가능하다.
구조
- 하나의 언두 테이블스페이스 = 1~128개의 롤백 세그먼트.
- 각 롤백 세그먼트 = 여러 언두 슬롯(InnoDB의 페이지 크기를 16바이트로 나눈 값)
- InnoDB 페이지 크기가 16KB라면 롤백 세그먼트는 1024개 슬롯.
- 하나의 트랜잭션은 보통 약 2개 슬롯 사용.
최대 동시 트랜잭션 수 계산
(페이지 크기 / 16) × (롤백 세그먼트 개수) × (언두 테이블스페이스 개수)- 기본 설정(16KB 페이지, undo tablespaces=2, rollback segments=128)
→ 약 2,097,152개 동시 트랜잭션 처리 가능하다. - 실제 서비스에서는 기본값으로도 충분한 경우가 많다.
MySQL 8.0 개선
CREATE UNDO TABLESPACE, DROP UNDO TABLESPACE 명령으로 동적 추가/삭제가 가능하다.
Undo Tablespace Truncate (공간 반납)
- 언두 테이블스페이스에서 불필요하거나 과도하게 할당된 공간을 잘라내 운영체제(OS)에 반납하는 기능이다.
- MySQL 8.0부터 자동과 수동 두 방식 지원한다.
1) 자동 모드
- 트랜잭션이 커밋되면, 언두 로그의 이전 데이터는 불필요해진다.
- InnoDB의 퍼지 스레드(Purge Thread)가 주기적으로 불필요한 언두 로그를 삭제하고 공간을 잘라낸다. → 이를 Undo Purge라고 한다.
- innodb_undo_log_truncate=ON 이면 자동 실행한다.
- 잘라내기 빈도는 innodb_purge_rseg_truncate_frequency 값으로 조정 가능하다.
2) 수동 모드
- innodb_undo_log_truncate=OFF 상태이거나 자동 모드가 기대만큼 효과적이지 않을 때 사용한다.
- 방법: 언두 테이블스페이스를 비활성화(SET INACTIVE)하면 → 퍼지 스레드가 비활성 공간을 정리 후 OS에 반납하고 → 다시 활성화(SET ACTIVE)한다.
- 특별히 필요할 때만 사용하며, 테이블스페이스가 최소 3개 이상 있어야 한다.
4.2.10 체인지 버퍼
RDBMS에서 INSERT, UPDATE 시에는 데이터 파일 변경과 인덱스 업데이트가 함께 수행된다. 인덱스 업데이트는 랜덤 디스크 읽기가 필요해 비용이 크다. InnoDB는 변경할 인덱스 페이지가 버퍼 풀에 있으면 즉시 반영하지만, 그렇지 않으면 → 체인지 버퍼(Change Buffer)라는 임시 공간에 저장해 두고 결과를 반환한다. 이후 백그라운드 스레드에 의해 실제 디스크와 병합한다. 이는 체인지 버퍼 머지 스레드(Merge thread)라 한다. 단, 유니크 인덱스는 중복 여부를 즉시 확인해야 하므로 체인지 버퍼를 사용할 수 없다.
발전 과정
- MySQL 5.5 이전: INSERT만 지원 → 이름도 “Insert Buffer”.
- MySQL 5.5 이후: 점차 확장되며 8.0부턴 innodb_change_buffering 시스템 변수로 어떤 작업을 버퍼링 할지 제어 가능하다.
innodb_change_buffering 설정값
- all : 모든 인덱스 관련 작업 (inserts + deletes + purges) 버퍼링
- none : 버퍼링 사용 안 함
- inserts : 인덱스 추가 작업만 버퍼링
- deletes : 인덱스 삭제 마킹 작업만 버퍼링
- changes : 추가·삭제 작업만 (inserts + deletes) 버퍼링
- purges : 인덱스 아이템을 영구 삭제하는 작업만 버퍼링 (백그라운드 처리)
메모리 사용
- 기본적으로 버퍼 풀 메모리의 25%까지 사용할 수 있다.
- 필요시 최대 50%까지 확장 가능하다.
- innodb_change_buffer_max_size 시스템 변수로 비율 조정 가능하다.
모니터링 방법
1. 체인지 버퍼 메모리 사용량 확인
SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME = 'memory/innodb/ibuf0 ibuf';
2. 체인지 버퍼 처리 현황 확인
SHOW ENGINE INNODB STATUS\G
4.2.11 리두 로그 및 로그 버퍼
리두 로그(Redo Log): ACID 중 D (Durability, 영속성)을 보장하는 핵심 기능이다. MySQL 서버가 비정상 종료될 때, 데이터 파일에 기록되지 못한 데이터를 보호하는 안전장치이다.
- 데이터 변경 시
- 데이터 파일은 랜덤 I/O로 쓰기 비용이 크다.
- 리두 로그는 쓰기 최적화된 구조를 사용해서 → 변경 내용을 먼저 로그에 기록하고 나중에 데이터 파일에 반영한다.
- 비정상 종료 시
- 커밋됐지만 반영되지 못한 데이터를 → 리두 로그에서 복구한다.
- 롤백됐지만 기록된 데이터를 → 언두 로그(Undo Log)로 복구한다.
따라서 리두 로그 + 언두 로그가 함께 트랜잭션 복구를 담당한다.
리두 로그 기록 주기 (innodb_flush_log_at_trx_commit)
- 0: 1초마다 로그 기록 및 동기화하면 → 서버 비정상 종료 시 최대 1초 치 데이터 유실 가능하다.
- 1: 매 커밋마다 디스크로 로그 기록되고 동기화까지 한다. 가장 안전하다.(기본값, 권장).
- 2: 커밋 시 로그 기록만 하고, 동기화는 1초마다 한다. → OS 버퍼에는 남아 있으므로 MySQL만 죽으면 안전하고, OS까지 죽으면 1초 치 유실 가능하다.
DDL(스키마 변경) 실행 시에는 즉시 동기화된다. innodb_flush_log_at_timeout으로 동기화 주기를 조정 가능하다.(기본 1초)
리두 로그 크기 설정
- InnoDB 스토리지 엔진이 가지고 있는 버퍼풀이 효율성을 결정하기 때문에 중요하다.
- 리두 로그 전체 크기 = innodb_log_file_size(파일 크기) × innodb_log_files_in_group(파일 개수)이다.
- 로그 크기가 너무 작으면 → 디스크 기록이 자주 발생해 성능 저하.
- 너무 크면 → 장애 시 복구 시간이 길어진다.
- 따라서 버퍼 풀 크기와 DBMS 특성에 맞게 적절히 설정해야 한다.
로그 버퍼 (Log Buffer)
- 사용량(변경 작업)이 많은 DBMS 서버의 경우엔 리두 로그의 기록 작업이 문제가 되는데, 이걸 보완하기 위해 ACID 속성을 보장하는 수준에서 버퍼링 한다.
- 트랜잭션 변경 내용을 일시적으로 모아두는 공간이다. (버퍼링에 사용되는 공간)
- 기본값은 16MB → BLOB, TEXT 같은 대용량 데이터를 자주 다루면 크게 설정 권장한다.
4.2.11.1 리두 로그 아카이빙
- MySQL 8.0부터 리두 로그를 아카이빙 할 수 있는 기능이 추가됐다.
- 백업 툴(MySQL Enterprise Backup, Xtrabackup 등)에서 데이터 파일 복사 중 리두 로그가 덮어써지지 않도록 보호한다.
- 설정
- innodb_redo_log_archive_dirs → 아카이빙 디렉터리 지정.
- innodb_redo_log_archive_start(label, subdir) → 아카이빙 시작.
- innodb_redo_log_archive_stop() → 아카이빙 종료.
- 아카이빙 시작 세션이 끊기면 자동 중지가 되면서 파일이 삭제된다.
4.2.11.2 리두 로그 활성화/비활성화
- 기본적으로 복구하기 위해 항상 활성화되어 있다.
- MySQL 8.0부터는 수동으로 ENABLE / DISABLE 가능하다.
- 데이터를 복구하거나 대량 데이터 적재 시 성능 향상을 위해 일시 비활성화해서 시간을 단축시킬 수 있다.
ALTER INSTANCE DISABLE INNODB REDO_LOG;
-- 대량 데이터 적재
ALTER INSTANCE ENABLE INNODB REDO_LOG;
-- 확인
SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';- 비활성화 상태에서 서버가 죽으면 → 복구가 불가능하다.
- 심하면 서버가 아예 기동 되지 않을 수 있음(innodb_force_recovery=6 필요).
- 따라서 서비스 운영 중에는 항상 활성화 유지해야 한다.
- 데이터 손실 감수 가능하다면, 비활성화보다는 innodb_flush_log_at_trx_commit을 0이나 2로 설정하는 것이 권장된다.
4.2.12 어댑티브 해시 인덱스(AHI)
일반 인덱스는 사용자가 만든 B-Tree 인덱스를 뜻하지만, 어댑티브 해시 인덱스(AHI)는 InnoDB가 자주 조회되는 키를 자동으로 해시 형태로 캐시해 주는 기능이다. 사용자가 켜고/끄는 옵션은 innodb_adaptive_hash_index (ON/OFF)이다.
동작 방식
- 빈번히 읽히는 B-Tree 키를 관찰 → “키(= 인덱스 ID+실제키)” → “데이터 페이지 메모리 주소(버퍼 풀)” 매핑을 해시 테이블에 저장한다.
- 이후 같은 키 조회 시 B-Tree 루트→브랜치→리프 탐색을 생략하고 바로 해당 페이지로 점프한다.
- AHI 엔트리는 버퍼 풀에 페이지가 있을 때만 유지되고, 페이지가 버퍼 풀에서 빠지면 AHI에서도 제거된다.
B-Tree 탐색 생략으로 CPU 소모가 줄어들며, 내부 잠금(세마포어) 경합도 줄어든다. 동등 비교/IN 조건 위주의 반복 조회에서 TPS/QPS가 증가한다(처리량 증가).
도움 되는 경우는
- 디스크 읽기가 많지 않고(데이터가 버퍼 풀에 “대부분” 있다.),
- 동등 조건, IN이 많고,
- 데이터 접근이 특정 핫 세트에 집중되는 경우
도움 되는 않는 경우는
- 디스크 읽기 비중이 큰 워크로드(버퍼 풀 미스 잦음),
- 조인/LIKE 패턴 검색 등 키 탐색 패턴이 다양할 때,
- 아주 큰 테이블을 넓게 스캔하는 경우
AHI는 메모리에 올라와 있는 페이지 접근을 빠르게 해주는 가속기지, 디스크 I/O 자체를 줄여주진 않는다.
비용/주의점
- 메모리 사용: AHI도 메모리를 꽤 먹을 수 있다. 사용하는 메모리를 확인하고 많을 경우 비활성화하는 것이 나을 수도 있다.
- 불필요한 조회 오버헤드: 활성화 시 InnoDB는 해시도 한 번 확인하므로, 히트율이 낮으면 괜한 비용이다.
- DDL/테이블 삭제 영향: 해당 테이블 관련 AHI 엔트리를 싹 지워야 해서 DROP/ALTER 시 CPU 소모가 높아지고, 시간 지연될 수 있다.(특히 INSTANT DDL도 영향 있다.)
설정 및 튜닝
- ON/OFF: innodb_adaptive_hash_index (효과 없거나 DDL 지연이 문제면 OFF 고려)
- 파티션 수(경합 완화): innodb_adaptive_hash_index_parts (기본 8, 경합 심하면 ↑) → 파티셔닝으로 AHI 내부 잠금 경합을 분산한다.
상태 확인
SHOW ENGINE INNODB STATUS\G- X.xx hash searches/s : 초당 해시로 처리된 검색 수
- Y.yy non-hash searches/s : 해시 미사용 검색 수
→ 히트율(hash / (hash+non-hash))과 CPU 사용량, 메모리 사용량을 함께 보고 판단한다.
메모리 사용량:
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';→ CPU가 빡빡하고(높은 사용률) AHI 히트율이 의미 있게 나오면(ex. 20~30%+) 유지/확대(parts↑).
→ CPU 여유가 많고 히트율 낮음 + AHI 메모리 사용 큼 → 비활성화 고려(버퍼 풀에 메모리 돌림)
→ 대형 DDL/DROP을 자주 수행 → 비활성화로 작업 시간 안정화.
4.2.13 InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교
MySQL의 스토리지 엔진은 시대에 따라 중심이 달라졌다. 과거에는 MyISAM이 기본이었지만, 지금은 InnoDB가 사실상 모든 기능을 담당한다. MEMORY 엔진은 이름 때문에 주목받기도 했지만 실제로는 제한적이다.
InnoDB
- 현재 기본 엔진 (MySQL 5.5~) : 8.0부터는 시스템 테이블까지 모두 InnoDB로 바뀌었다.
- 트랜잭션, MVCC, 외래 키 지원 → 안정성과 동시성에 강점을 가진다.
- 읽기·쓰기 모두 안정적, 대규모 트랜잭션 처리에 적합하다.
- 전문 검색, 공간 좌표 검색도 지원한다. (과거엔 MyISAM 전용이었음)
- 따라서 지금은 MySQL에서 사실상 유일한 선택지이다.
MyISAM
- 과거 기본 엔진 (MySQL 5.1 이하)
- 과거 기준으로 단순 구조와 빠른 읽기 성능이 장점이었다.
- 트랜잭션 미지원
- 테이블 단위 잠금 → 동시성 처리에 취약하다.
- 따라서 8.0 이후 사실상 의미 없으며, 향후 버전에서 완전히 사라질 가능성이 높다.
MEMORY
- 데이터를 모두 메모리에 저장 → 단일 스레드 성능은 빠르다.
- 하지만 테이블 단위 잠금 때문에 동시성 처리 성능이 낮다.
- MySQL 5.7까지는 내부 임시 테이블로 사용했다.
- TempTable 엔진이 기본으로 대체하고 있다. 따라서 MEMORY를 선택할 이유는 거의 없다.(레거시 호환용)
마무리
사실 이번 장은 길고 어렵게 느껴져서, 마무리로 아키텍처 관점에서 정리해보려 한다.
이전 장에서 MySQL 서버는 크게 MySQL엔진과 스토리지 엔진으로 나눠볼 수 있었다. 스토리지 엔진은 실제 데이터를 디스크에 저장하거나 읽어오는 손발 역할을 담당한다. 쉽게 말하면, MySQL에서 데이터를 물리적으로 관리하는 모듈이다.
어떤 방식으로 저장·검색·동시성 제어를 할지 정의한다. InnoDB, MyISAM, MEMORY 같은 여러 종류가 있고, 지금은 InnoDB가 기본값이다. 즉, SQL은 MySQL 엔진이 해석하고, 실제 데이터 파일 관리와 트랜잭션·잠금 같은 디테일한 처리는 스토리지 엔진이 맡는다.
그중 InnoDB는 “안정성과 동시성”을 책임지는 핵심 엔진이다. 크게 보면 세 덩어리로 이해하면 편하다.
첫째, 버퍼 풀(Buffer Pool) 은 속도를 담당한다. 디스크 대신 메모리에 자주 쓰는 페이지를 올려두고, 변경도 한데 모아 뒀다가 한 번에 디스크로 내려 보낸다. 내부엔 빈 공간 목록(Free), 최근 사용 정도로 정리하는 목록(LRU), 아직 디스크에 못 내린 더티 페이지 목록(Flush)이 있어 캐시처럼 굴러간다. 덕분에 읽기는 메모리에서 빠르게, 쓰기는 묶음 처리로 효율적으로 된다.
둘째, 로그(redo/undo)와 로그 버퍼는 안전과 일관성을 담당한다. 변경 사항은 먼저 리두 로그(Redo)에 기록돼 장애가 나도 복구할 수 있고, 언두 로그(Undo)는 “이전 버전”을 들고 있어 롤백과 MVCC(잠금 없는 일관된 읽기)를 가능하게 한다. 이 로그들도 메모리의 로그 버퍼를 거쳐 기록돼 불필요한 디스크 왕복을 줄인다. 정리하면 쓰기 안정성은 리두, 과거 보기/롤백은 언두이다.
셋째, 백그라운드 스레드 가 뒤에서 묵묵히 청소를 한다. 더티 페이지를 디스크로 내리는 플러시, 체인지 버퍼를 실제 인덱스와 합치는 머지, 필요 없어진 언두를 비우는 퍼지, 데드락을 감지하는 스레드까지. 이 보이지 않는 일꾼들 덕분에 앞단의 서비스 쿼리는 가벼워지고 응답은 고르게 유지된다.
부가 기능도 있다. 인덱스 페이지가 메모리에 없을 때 인덱스 변경을 임시로 쌓아두는 체인지 버퍼, 자주 조회되는 키를 해시로 기억해 바로 페이지로 점프하는 어댑티브 해시 인덱스, 페이지가 반쯤만 써져 깨지는 걸 막는 더블라이트 버퍼, 재시작 시 캐시를 빠르게 되살리는 버퍼 풀 덤프/로드 같은 것들이다. 각각 “쓰기 비용 줄이기”, “반복 조회 가속”, “데이터 무결성”, “워밍업”을 목표로 한다.
결국 InnoDB 아키텍처는 이렇게 정리할 수 있다. 버퍼 풀로 속도를 끌어올리고, 리두/언두 로그로 안정성을 지키며, 백그라운드 스레드로 꾸준히 정리한다. 필요한 경우 보조 기능들로 미세 튜닝을 한다. 그래서 오늘날 MySQL 8.0 세계에서는, 별다른 이유가 없다면 InnoDB 하나로 대부분의 요구 사항을 커버할 수 있다.
'책책책 책을 읽어요 > RealMySQL 8.0' 카테고리의 다른 글
| [RealMySQL 8.0] 04. MySQL 아키텍처(4.3 MyISAM 스토리지 엔진 아키텍처, 4.4 MySQL 로그 파일) (0) | 2025.10.07 |
|---|---|
| [RealMySQL 8.0] 04. MySQL 아키텍처(4.1 MySQL 엔진 아키텍처) (3) | 2025.08.26 |
