InnoDB 아키텍처
클러스터링
기본적으로 InnoDB의 모든 테이블은 PK를 기준으로 순서대로 클러스터링되어 저장됩니다. 또한 모든 세컨더리 인덱스는 레코드의 주소 대신 PK 값을 논리적인 주소로 사용(물리적인 주소가 아니라, 세컨더리 인덱스가 PK 값을 가리키는 포인터로 구성됨)합니다. 이러한 특징 때문에 PK를 이용한 스캔이 상당히 빨라서, 기본적으로 다른 보조 인덱스에 비해 PK가 쿼리 실행 계획에서 비중이 높게 설정됩니다.
외래키 지원
MyISAM이나 MEMORY 테이블에서는 지원하지 않습니다. InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요합니다. 특히 데이터가 있는지 체크하는 작업에서 잠금이 여러 테이블로 전파되고 그로 인해 데드락이 발생할 때가 많아서 주의해야 합니다.
외래 키 체크를 일시적으로 해제하는 'SET foreign_key_checks=OFF;' 를 실행하면 외래 키 검사를 안하게 되면서 매우 빠른 쿼리 처리 속도를 보일 수 있습니다. 이때는 부모 테이블에 대한 작업(CASCADE 등)도 무시하게 됩니다.
MVCC (Multi Version Concurrency Control)
InnoDB는 하나의 레코드에 대해서 여러 개의 버전이 유지되고 필요에 따라 어느 데이터가 보여지는 지가 여러 가지 상황에 따라 달라집니다. 이런 구조를 MVCC라고 합니다. 이 여러 개의 버전은 InnoDB 버퍼 풀과 언두 로그를 이용해서 관리됩니다. 다음은 멀티 버전의 레코드가 관리되는 예시입니다.
먼저 테이블에 한 건의 레커드를 INSERT한 뒤에, 해당 사항이 커밋되었다고 가정합시다.
| InnoDB 버퍼 풀 | 언두 로그 | Disk |
| m_id: 1, m_name: 눈사람, m_city: seoul | m_id: 1, m_name: 눈사람, m_city: seoul |
그림 표와 같이 영역 별로 데이터의 상태가 형성될 것입니다. 여기서 m_id가 1인 유저의 m_city를 busan으로 바꾸는 쿼리가 실행되고 커밋이나 롤백 이전이라면, 데이터는 아래와 같이 형성됩니다.
| InnoDB 버퍼 풀 | 언두 로그 | Disk |
| m_id: 1, m_name: 눈사람, m_city: busan | m_id: 1, m_city: seoul | m_id: 1, m_name: 눈사람, m_city: ? |
InnoDB에서 디스크에 데이터를 작성하는 일은 백그라운드 스레드가 담당합니다. 일반적으로 ACID Transaction을 보장하기 때문에 InnoDB 버퍼 풀과 Disk는 상태가 동일하다고 봐도 무방합니다. 일단 여기서는 커밋이나 롤백 전이고, 백그라운드 스레드의 실행 여부에 Disk의 상태가 결정되기 때문에 m_city는 unknown입니다. 언두 로그에는 업데이트 전 값만 저장됩니다. 이때 사용자가 이 레코드를 조회한다면 MySQL에 설정된 격리 수준에 따라서 읽는 위치가 달라집니다.
READ_UNCOMMITTED: InnoDB 버퍼 풀
READ_COMMITTED 이상의 격리 수준: 언두 로그
커밋되지 않은 것도 읽을 수 있는 READ_UNCOMMITTED를 제외한 경우에는 전부 언두 로그를 확인하고 데이터를 반환합니다. 여기서 언두 로그의 예전 버전 데이터는 무한히 많아질 수 있습니다. 그렇기 때문에 Multi Version이라고 합니다.
언두 로그를 필요로 하는 트랜잭션이 더는 없을 때 언두 로그의 데이터가 삭제됩니다. 이는 트랜잭션의 단위가 길고 변경이 많을 수록 언두 로그에 할당되는 데이터 공간이 많이 늘어나는 상황이 발생할 수 있다는 이야기입니다. 또한 커밋 직후에 바로 언두 로그가 비워지는 것도 아니고, 비워지는 시기는 트랜잭션의 사이클에 종속적입니다.
Non-Locking Consistent Read
InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행합니다. SERIALIZABLE을 제외한 격리 수준인 경우에 INSERT와 연결되지 않은 순수한 SELECT 작업은 항상 잠금을 대기하지 않고 바로 실행됩니다. 특히 테이블을 읽는 중에 다른 UPDATE 요청이 발생했는데 커밋 전이라면, READ_COMMITTED와 REPEATABLE_READ의 경우에는 변경 전의 데이터를 언두 로그를 사용해서 결과를 반환합니다. 이를 '일관된 읽기'라고 합니다.
자동 데드락 감지
InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 있는지 체크하기 위해 잠금 목록을 그래프 형태로 관리합니다. 데드락 감지 스레드가 존재해서 주기적으로 잠금 대기 그래프를 검사해서, 교착 상태에 빠진 트랜잭션을 찾아 그중 언두 로그의 양이 더 적은 트랜잭션을 강제 종료합니다. 언두 로그의 양이 더 적은 것을 종료하는 이유는 롤백할 내용이 적어서 강제 롤백으로 인한 DB 서버의 부하를 줄이기 위해서 입니다.
데드락 감지 스레드는 잠금 목록을 검사할 때 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 잠금 테이블에 새로운 잠금을 걸고 데드락 스레드를 찾습니다. 이 부분에서 동시 처리 스레드가 매우 많은 경우나 하나의 트랜잭션에서 너무 많은 락을 사용하는 경우에는 데드락 감지 스레드가 할 일이 많아져서 더 많은 CPU 자원을 소모할 수도 있습니다. 일반적으로는 데드락 감지는 크게 부담이 되는 업무는 아닙니다만, 이러한 상황에서의 데드락의 지연은 서비스 쿼리에 대한 지연으로 이어지기 때문에 이런 문제를 해결하기 위한 innodb_deadlock_detect 시스템 변수를 0으로 설정하면 데드락 감지 스레드의 동작을 멈추는 방법이 있습니다. 물론 Off를 해버린 상태에서 데드락이 발생하면 무한정 대기 상태가 발생합니다. 여기서 innodb_lock_wait_timeout 시스템 변수를 활성화하면 잠금을 설정한 시간 동안 획득하지 못하면 쿼리가 실패하고 에러를 반환하기 때문에 데드락 감지 스레드를 Off로 해놓아도 무한정 경합을 막을 수 있습니다. 여기서 데드락 스레드 작업을 Off로 해놓고 lock_wait_timeout을 설정해 놓는다고 하면 기본 값 50초보다 훨씬 낮은 시간으로 변경하면 좋습니다.
일례로 구글의 사례를 들 수 있습니다. 구글에서는 PK 기반의 조회 및 변경이 아주 높은 빈도로 실행되는 서비스가 많았는데, 이런 서비스는 매우 많은 트랜잭션을 동시에 실행하기 때문에 데드락 감지 스레드가 상당한 기능 저하를 일으킵니다. 따라서 구글은 MySQL 서버의 소스코드를 변경해서 데드락 감지 스레드를 활성,비활성화할 수 있도록 구성했고, 이 기능을 오라클에 요청해서 MySQL에 반영된 것입니다. 따라서 인덱스를 매우 높은 빈도로, 동시적으로 사용하는 서비스에서 해당 값을 튜닝해서 성능을 비교해보면 매우 유의미할 수 있습니다.
자동 장애 복구
InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행합니다. 이 단계에서 자동으로 복구될 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 서버는 종료됩니다. 이때는 MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야합니다. 이 설정값은 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 합니다.
innodb_force_recovery는 1~6으로 설정할 수 있습니다. 어떤 부분에서 장애가 발생했는지 알 수 없다면 1부터 6까지 변경하면서 MySQL을 재시작해보면 됩니다. 값이 커질수록 더 심각한 상태임을 의미하며, 데이터 손실 가능성은 커지고 복구 가능성은 적어집니다. 다음은 1~6의 내용입니다.
| 값 | innodb_force_recovery | 상세 내용 |
| 1 | SRV_FORCE_IGNORE_CORRUPT | InnoDB의 테이블스페이스의 데이터나 인덱스 페이지에서 손상된 부분이 발견돼도 무시하고 MySQL 서버를 시작. Database page corruption on disk or a failed 메시지가 출력될 때는 대부분 이 경우. 덤프해서 데이터베이스를 다시 구축하는 것이 좋음. |
| 2 | SRV_FORCE_NO_BACKGROUND | 백그라운드 스레드 중 메인 스레드를 시작하지 않고 MySQL 서버를 시작. 메인 스레드가 불필요한 언두 데이터를 주기적으로 삭제(Undo purge)하는 역할을 함. InnoDB의 메인 스레드가 언두 데이터를 삭제하는 과정에서 장애가 발생했을 시에 이 모드로 복구. |
| 3 | SRV_FORCE_NO_TRX_UNDO | 일반적으로 서버를 다시 시작하면 리두 로그의 데이터를 먼저 데이터 파일에 적용하고 그 다음에 언두 로그의 내용을 다시 덮어써서 장애 직전 시점의 데이터를 만들어 냄. 커밋되지 않은 트랜잭션의 작업을 롤백하지 않고 계속 그 상태로 남아있게 놔두고 MySQL 서버를 시작. 덤프해서 데이터베이스를 다시 구축하는 것이 좋음. |
| 4 | SRV_FORCE_NO_IBUF_MERGE | InnoDB 스토리지 엔진이 인서트 버퍼의 내용을 무시하고 MySQL이 시작되게 함. 인덱스 변경 작업을 저장해놓는 인서트 버퍼에서 장애가 발생하면 InnoDB에서 에러를 뱉고 MySQL 서버가 시작하지 못함. 덤프해서 데이터베이스를 다시 구축하면 데이터 손실 없이 복구 가능. |
| 5 | SRV_FORCE_NO_UNDO_LOG_SCAN | InnoDB 엔진이 언두 로그를 모두 무시하고 MySQL을 시작. 커밋되지 않은 작업도 모두 커밋된 것으로 처리해서 오염된 데이터가 남음. 덤프해서 데이터베이스를 다시 구축하는 것이 좋음. |
| 6 | SRV_FORCE_NO_LOG_REDO | InnoDB 엔진이 리두 로그를 모두 무시한 상태로 MySQL 서버를 시작. 마지막 체크포인트 시점의 데이터만 남게 됨. 기존 InnoDB의 리두 로그는 모두 삭제하고 MySQL 서버를 시작하는 것이 좋음. 덤프해서 데이터베이스를 다시 구축하는 것이 좋음. |
이러한 방법으로도 서버가 시작되지 않으면 백업을 이용해서 다시 구축하는 방법 밖에 없습니다. 백업을 이용해서 데이터베이스를 새로 구축하면 바이너리 로그를 사용해서 최대한 장애 시점까지 데이터를 복구해야합니다.
버퍼 풀
디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간입니다. 쓰기 작업을 지연시키는 버퍼의 역할도 합니다. 이 쓰기 지연으로 INSERT, UPDATE, DELETE와 같은 작업으로 인한 랜덤 디스크 작업 횟수를 줄일 수 있습니다.
버퍼 풀 구조
InnoDB는 버퍼 풀이라는 메모리 공간을 페이지로 쪼개서 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장합니다. 버퍼 풀의 페이지 크기 조각을 관리하기 위해서 InnoDB 스토리지 엔진은 크게 LRU, Flush, Free 리스트라는 3개의 자료 구조를 관리합니다.
- Free 리스트
- 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록
- 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우에 사용
- Flush 리스트
- 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리.
- 한번이라도 변경된 페이지는 플러시 시르트에 관리되고 특정 시점이 되면 디스크로 기록돼야함.
- 데이터가 변경되면 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영함. 따라서 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결됨.
- InnoDB 엔진은 체크 포인트를 발생시켜서 디스크의 리두 로그와 데이터 페이지의 상태를 동기화하게 됨.
- LRU 리스트
- 실질적으로는 LRU와 MRU의 결합된 형태
- New 서브리스트는 MRU, Old 서브리스트는 LRU
- 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것이 목적.
LRU 리스트의 관점에서 InnoDB 스토리지 엔진에서 데이터를 찾는 과정은 아래와 같습니다.
- 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사합니다.
- 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가합니다.
- 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동합니다.
- 버퍼 풀에 상주하는 데이터 페이지는 얼마나 최근에 접근했었는지에 따라서 Age가 부여되고, 상주하는 동안 오랫동안 사용되지 않으면 해당 페이지는 버퍼 풀에서 제거됩니다. 이를 Aging이라고 합니다. 쿼리에 의해 데이터 페이지가 사용되면 다시 젊어지고 MRU의 헤더 부분으로 옮겨집니다.
- 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가합니다.
버퍼 풀 튜닝
OS와 클라이언트 스레드가 사용할 메모리를 종합적으로 고려해서 버퍼 풀의 크기를 설정해야합니다. 클라이언트 세션에서 커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 공간이 생각보다 메모리를 많이 필요로 할 수 있습니다. 일반적으로 MySQL 서버 내에서 메머리를 필요로 하는 부분은 크게 없지만, 위와 같은 독특한 상황도 존재함을 알아야합니다. 다만 이 레코드 버퍼 공간은 별도로 설정할 수 없으며 동적으로 해제되기도 하므로 정확한 공간의 크기를 계산할 수 없습니다.
가능하다면 InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정하고 상황에 따라서 증가시키는 방법이 최적입니다. 이미 구현된 서버가 존재하는 경우에는 그 서버의 메모리 설정을 기준으로 InnoDB 버퍼 풀의 크기를 조정하면 됩니다. 처음으로 MySQL 서버를 준비한다면 아래와 같은 가이드라인이 기준이 될 수 있습니다.
- OS 전체 메모리 공간이 8GB 미만이라면 50% 정도만 InnoDB 버퍼 풀로 설정하고 나머지 메모리 공간은 MySQL 서버와 운영체제, 다른 프로그램이 사용할 수 있는 공간으로 확보
- OS 전체 메모리 공간이 그 이상이라면 50%에서 시작해서 조금씩 올려가면서 최적점을 찾습니다.
- 50GB 이상의 전체 메모리 공간이라면 15~30GB를 다른 응용 프로그램을 위해서 남겨두고 나머지를 InnoDB 버퍼 풀로 할당합니다.
innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있고, 동적으로 버퍼 풀의 크기를 조절할 수 있습니다. 이때 버퍼 풀의 크기를 조절하는 것은 중요한 변경이므로 가능하면 서버가 한가한 시점을 골라서 변경하는 것이 좋고, 특히나 버퍼 풀 사이즈를 줄이는 작업은 서비스 영향도가 매우 크므로 가능하면 하지 않는 것이 좋습니다. 버퍼 풀은 128MB 청크 단위로 쪼개어 관리되기 때문에 풀을 늘리거나 줄이는데는 128MB 단위로 처리됩니다.
버퍼 풀은 세마포어를 이용해서 잠금을 관리하는데, 하나의 버퍼 풀을 사용하면 잠금 경합이 많이 발생합니다. 이에 버퍼 풀을 여러 개로 쪼개서 관리할 수 있게 개선해서 경합이 분산되도록 했는데, innoodb_buffer_pool_instance를 이용해서 버퍼 풀을 여러 개로 분리해서 관리할 수 있습니다. 전체 버퍼 풀을 위한 메모리가 1GB 미만이면 1개만 생성되고, 기본적으로는 8개로 생성됩니다. 버퍼 풀로 할당할 수 있는 메모리 공간이 40GB 이하 수준이라면 기본 값을 유지하고, 메모리가 크다면 인스턴스 당 5GB 정도가 되게 인스턴스 개수를 설정하는 것이 좋습니다.
리두 로그와의 관계
리두 로그는 트랜잭션의 저장 사항을 로그로 저장해놓는 파일입니다. InnoDB에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용해서, 데이터 변경이 계속 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는 어느 시점에 다시 새로운 로그 엔트리로 덮어 쓰입니다. 이에 따라서 재사용 가능 공간과 재사용 불가능한 공간을 구분해서 관리해야 합니다. 재사용 불가능한 공간을 활성 리두 로그라고 합니다. (Active Redo Log)
리두 로그 파일의 공간은 매번 기록될 때마다 로그 포지션은 계속 증가합니다. 이를 LSN(Log Sequence Number)이라고 합니다. 체크포인트가 발생하면 가장 최근의 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 되고, 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지 (Checkpoint Age)라고 합니다. Checkpoint age가 곧 활성 리두 로그 공간의 크기를 의미합니다. 또한 활성 리두 로그 공간의 LSN보다 작은 LSN을 가진 공간의 더티 페이지는 모두 디스크와 동기화돼야 합니다.
여기서 중요한 점은 버퍼 풀의 크기는 크지만 리두 로그 파일의 크기가 그에 비해 현저히 작은 경우, 쓰기 버퍼링의 효과는 거의 보지 못합니다. 즉, 데이터 캐시에 의한 읽기 성능 향상의 효과만 보게 되는 것입니다. 가령 리두 로그 파일의 크기가 100MB일 경우에는 체크포인트 에이지도 최대 100MB만 허용됩니다. 또한 평균 리두 로그 엔트리가 4KB였다면 100KB/4KB= 25600개 정도의 더티 페이지만 버퍼 풀에 보관할 수 있게 됩니다. 데이터 페이지가 16KB라면 허용 가능한 전체 더티 페이지의 크기는 400MB 수준이므로, 변경된 내용을 400MB 밖에 저장할 수 없는 것입니다. 만일 리두 로그 파일의 크기가 100GB라면 400GB 정도의 더티 페이지를 가질 수 있을 것이고, 쓰기 버퍼링 효과가 커질 것입니다. 다만 더티 페이지의 비율이 너무 높은 상태를 가진다면 디스크에 내용을 작성할 때 매우 많은 더티 페이지를 한 번에 기록해야하는 상황이 올 수 있습니다. 따라서 리두 로그 파일의 전체 크기도 최적화가 필요하고, 전체 크기를 대략 5~10GB 수준으로 선택한 뒤에 조금씩 늘려가면서 최적값을 선택하는 것이 좋습니다.
플러시
더티 페이지 플러시에서 위에서 살펴본 바와 같이 갑작스럽게 디스크 기록이 폭증하면 사용자 쿼리 처리 성능에 안 좋은 영향을 미칩니다. 이를 해결한 InnoDB에서 더티 페이지 디스크 쓰기 동기화와 관련된 시스템은 리스트 플러시, LRU 플러시입니다.
리스트 플러시
리두 로그 공간은 고정 크기로 제한되어 있고, 이를 재활용한다고 했습니다. 따라서 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워줘야 합니다. 즉, 오래된 리두 로그 공간의 더티 페이지가 디스크로 주기적으로 동기화돼야 합니다. 이때 InnoDB에서는 플러시 리스트 플러시 함수를 호출해서 오래전에 변경된 더티 페이지 순서대로 디스크에 동기화하는 작업을 수행합니다. 이때 언제부터 얼마나 많은 더티 페이지를 한 번에 디스크에 기록하는가가 사용자 쿼리 처리에 중요한 영향을 미칩니다.
이런 동기화 작업을 수행하는 백그라운드 스레드를 클리너 스레드라고 합니다. innodb_page_cleaners 시스템 변수의 설정 값으로 클리너 스레드의 개수를 설정할 수 있습니다. 이때 하나의 버퍼 풀 인스턴스에는 최대 하나의 클리너 스레드만 할당될 수 있습니다. 또한 클리너 스레드가 인스턴스 개수에 비해 모자라면 클리너 스레드를 재사용해서 여러 개의 버퍼 풀 인스턴스를 처리합니다. 가능하면 클리너 스레드는 인스턴스의 개수와 동일한 값으로 설정하는 것이 좋습니다.
더티 페이지는 기본 설정으로 버퍼 풀의 90%까지 차지할 수 있습니다. 여기서 디스크로 기록되는 더티 페이지의 개수보다 쌓이는 더티 페이지의 개수가 더 많으면 버퍼 풀에 더티 페이지의 비율이 계속해서 증가하게 되고, 이 비율이 90%를 넘기면 InnoDB가 더티 페이지를 디스크로 기록해야 한다고 판단합니다. 이 설정이 innodb_max_dirty_pages_pct 입니다. 이때 디스크 쓰기가 폭증하는데, 이러한 문제를 완화하기 위해서 InnoDB는 일정 수준 이상의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록합니다. 이 기준이 innodb_max_dirty_pages_lwm 시스템 변수 값입니다. 기본 값이 10%이기 때문에 더티 페이지의 비율이 얼마 되지 않는 상태에서 디스크 쓰기가 많이 발생하면서 더티 페이지 비율이 너무 낮은 값으로 머물러있다면 이 시스템 변수를 좀 더 높은 값으로 튜닝하는 것이 쓰기 버퍼링 성능을 극대화하는 방법이 될 것입니다.
데이터베이스 서버에서 어느 정도의 디스크 읽고 쓰기가 가능한 지를 innodb_io_capacity와 innodb_io_capacity_max 시스템 변수로 설정할 수 있습니다. 전자는 일반적인 수준에서, 후자는 최대 성능에서 디스크가 성능을 발휘할 때 어느 정도의 디스크 IO가 가능한지를 설정하는 변수입니다. 여기서 디스크 IO는 대부분 InnoDB 버퍼 풀의 더티 페이지 쓰기에 해당합니다. 여기서 현재 디스크가 초당 1000 IOPS를 처리할 수 있다고 해서 이 값을 그대로 이 시스템 변수에 설정하면 안됩니다. 사용자 쿼리를 위한 디스크 IO도 고려해야하기 때문입니다. 또한 설정한 값 그대로의 값으로 디스크 IO를 보장하는 것도 아닙니다. 내부적으로 최적화 알고리즘을 기준으로 적당히 계산된 횟수만큼 더티 페이지 쓰기를 하기 때문입니다. 따라서 어느 정도 디스크 쓰기를 하는지 관찰하면서 분석하고 패턴을 분석하는 것이 중요합니다.
이런 분석 과정에서 관리할 MySQL 서버가 너무 많다면 서버의 트래픽을 관측하면서 io capacity 값을 수정하는 것은 매우 번거로울 것이므로, InnoDB에서는 어댑티브 플러시 기능을 제공합니다. 기본적으로 사용하도록 설정돼있으며, 활성화되면 더티 페이지 비율이나 io capacity에 의존하지 않는 새로운 알고리즘을 반영합니다. 더티 페이지의 작성은 근본적으로 리두 로그의 양이라고 할 수 있으므로 이 알고리즘은 리두 로그의 증가 속도를 분석해서 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 진행합니다. innodb_adaptive_flushing_lwm 시스템 변수에 따라서 이 값보다 높은 활성 리두 로그의 공간을 가지게 되면 어댑티브 플러시 알고리즘이 실행됩니다. 기본 설정값은 10%입니다.
innodb_flush_neighbors는 더티 페에지를 디스크에 기록할 때 이웃한 페이지 중에서 더티 페이지가 있다면 디스크로 기록하게 해주는 기능을 활성화하는 시스템 변수입니다. 이는 디스크 쓰기를 극적으로 줄이는 설정으로 HDD에서는 활성화하고, SSD에서는 비활성화하는 것이 좋습니다.
LRU 플러시
LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 리스트의 끝부분부터 시작해서 innodb_lru_scan_depth 시스템 변수 값의 개수만큼 스캔합니다. 스캔된 더티 페이지는 디스크에 동기화되며 클린 페이지는 즉시 프리 리스트로 페이지를 옮깁니다. LRU 리스트 스캔은 인스턴스별로 최대 innodb_lru_scan_depth 개수만큼 스캔하게 됩니다.
상태 백업 및 복구
워밍 업이 되지 않은 상태의 서버는 돼있는 서버보다 몇 십배의 쿼리 성능 차이를 보입니다. 이에 버퍼 풀 덤프 및 적재 기능을 사용해서 MySQL 서버를 재시작해야하는 경우에 서버를 셧다운 하기 전에 innodb_buffer_pool_dump_now 시스템 변수를 이용해서 InnoDB 버퍼 풀의 상태를 백업할 수 있습니다. 그리고 다시 서버를 다시 시작하고 innodb_buffer_pool_load_now 시스템 변수를 이용하면 백업된 버퍼 풀의 상태를 다시 복구할 수 있습니다.
버퍼 풀을 덤프할 때 메타 데이터만 백업해놓기 때문에 버퍼 풀이 커도 몇십 MB 정도의 파일크기로 그칩니다. 다만 버퍼 풀을 복구하는 과정에서는 디스크에서 실제 데이터를 읽어와야 하기 때문에 상당한 시간이 걸릴 수도 있습니다. 버퍼 풀 복구가 너무 오래걸리는데 서비스를 급히 시작해야한다면 innodb_buffer_pool_load_abort를 이용해서 복구를 중단할 수 있습니다.
이런 수동 백업 및 복구 과정을 자동화할 수 있는데, innodb_buffer_pool_dump_at_shutdown과 innodb_buffer_pool_load_at_startup 설정을 서버 설정 파일에 넣어두면 자동으로 버퍼 풀의 백업과 복구를 수행합니다.
상태 확인
Innodb_cached_indexs 테이블을 이용해서 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있습니다. MySQL 서버에서는 개별 인덱스별로 전체 페이지 개수가 몇 개인지는 알려주지 않으므로 테이블 단위로 전체 데이터 페이지 개수와 InnoDB 버퍼 풀에 적재된 데이터 페이지 개수의 합을 조회해야 합니다.
Double Write Buffer
리두 로그는 공간 효율성을 위해서 변경된 내용만을 기록합니다. 이때 디스크 파일로 플러시할 때 하드웨어의 오작동, 시스템의 비정상 종료 등의 이유로 일부만 기록되는 문제가 발생하면 그 페이지의 내용은 복구할 수 없을 수도 있습니다. 이런 현상을 Partial-page, Torn-page라고 합니다.
이러한 비정상적인 동작의 문제를 해결하기 위한 방법으로 Double write buffer가 존재합니다. 실제 데이터 파일에 변경 내용을 기록하기 전에 'A'~'E'까지의 더티 페이지를 우선 묶어서 한 번의 디스크 쓰리고 시스템 테이블 스페이스의 DoubleWrite 버퍼에 기록하고 데이터 페이지를 개별로 파일로 기록합니다. 여기서 중간에 비정상적으로 종료된다면 InnoDB가 재시작될 때 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사합니다. 이 기능의 활성 유무는 innodb_doublewrite 시스템 변수로 설정할 수 있습니다.
HDD는 어차피 한 번의 순차 디스크 쓰기를 하는 것이기 때문에 부담이 되지 않지만 SSD와 같이 랜덤 IO나 순차 IO의 비용이 비슷한 저장 장치에서는 부담이 큽니다. 따라서 이런 점을 고려해서 사용 여부를 결정해야합니다. 그래도 데이터 무결성이 매우 중요한 서비스에서는 활성화를 고려하는 것이 좋습니다. 특히나 리두 로그는 동기화하지 않으면서 DoubleWrite만 활성화하는 것은 잘못된 선택입니다. 성능이 더 중요하다면 비활성화하는 것이 좋습니다.
언두 로그
언두 로그는 트랜잭션의 롤백과 격리 수준 보장에 사용됩니다. 매우 중요하지만 그 만큼 관리 비용도 많이 필요합니다.
모니터링
대용량의 데이터를 처리하는 트랜잭션, 트랜잭션이 오랜 시간 동안 실행될 때 등의 상황에서 언두 로그의 양은 급격히 증가할 수 있습니다. 많은 양의 데이터를 업데이트 처리하는 쿼리의 경우에는 대용량의 데이터 자체를 언두 로그에 저장해야합니다. 트랜잭션이 오랜 시간동안 열려있는 동안 다른 트랜잭션이 해당 데이터를 계속해서 조작하는 경우에는, 가장 긴 트랜잭션의 시간만큼 언두 로그에 데이터가 쌓입니다. 이때 데이터를 조회하는 서비스 쿼리가 발생하면 언두 로그에서 필요한 만큼 데이터를 스캔해야하기 때문에 서비스 쿼리의 성능이 전반적으로 떨어지게 됩니다.
따라서 활성 상태의 트랜잭션이 장시간 유지된는 것은 성능상 좋지 못하므로 언두 로그가 얼마나 증가했는지는 항상 모니터링하는 것이 좋습니다. MySQL 서버별로 얼마나 많은 데이터를 변경하느냐에 따라서 평상시 언두 로그 건수는 다를 수 있으므로, 안정적인 시점의 언두 로그 건수를 기준으로 언두 로그의 급증 여부를 모니터링하는 것이 좋습니다. 아래는 언두 로그 건수를 확인하는 쿼리입니다. INSERT에 대한 언두 로그 건수는 별도로 관리되기 때문에 아래의 코드는 UPDATE와 DELETE 문장으로 인한 언두 로그 개수만 표시됩니다.
SELECT count
FROM information_schema.innodb_metrics
WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';
언두 테이블스페이스
언두 로그가 저장되는 공간을 언두 테이블스페이스라고 합니다. 8.0 이후부터 언두 로그는 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록되도록 변경됐습니다.
하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯을 가집니다. 하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수만큼의 언두 슬롯을 가집니다. 즉, InnoDB의 페이지 크기가 16KB라면 하나의 롤백 세그먼트는 1024개의 언두 슬롯을 갖게 됩니다. 하나의 트랜잭션이 필요로하는 언두 슬롯의 개수는 쿼리문의 특성에 따라서 최대 4개 까지의 언두 슬롯을 사용할수 있는데, 일반적으로는 2개를 필요로한다고 생각하면 됩니다. 이에 따라서 다음과 같은 수식을 도출할 수 있습니다.
최대 동시 트랜잭션 수 = (InnoDB 페이지의 크기)/ 16 * (롤백 세그먼트 개수) * (언두 테이블스페이스 개수)
기본 설정값을 바탕으로 계산하면 언두 테이블스페이스는 2, 롤백 세그먼트 개수는 128이므로 131072개 정도의 트랜잭션이 동시에 처리가 가능합니다. 언두 로그 슬롯이 남는 경우는 상관없지만, 부족한 경우에는 트랜잭션을 시작조차 할 수 없으므로 언두 테이블스페이스의 수정이 필요할 경우에는 필요한 동시 트랜잭션 개수에 맞게 롤백 세그먼트의 개수를 설정해야 합니다.
언두 테이블스페이스는 8.0 버전 이후부터 동적으로 추가 및 삭제가 가능합니다.
CREATE UNDO TABLESPACE [이름];
DROP TABLESPACE [이름];
언두 테이블 스페이스에서 트랜잭션이 커밋되면 언두 로그에 복사된 이전 값은 필요가 없어집니다. 이런 값들을 주기적으로 삭제하고 OS로 반납하는 백그라운드 스레드가 퍼지 스레드(Perge thread)입니다. 자동 모드와 수동 모드로 언두 로그를 관리할 수 있는데, 자동 수행 시에 더, 혹은 덜 빈번하게 튜닝하고 싶으면 innodb_purge_rseg_truncate_frequency 시스템 변수의 값을 조정해야 합니다. 수동 모드에서는 언두 테이블스페이스가 최소 3개 이상은 돼야 작동합니다.
체인지 버퍼
RDBMS에서는 레코드가 INSERT되거나 UPDATE될 때 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요합니다. 인덱스 업데이트는 랜덤 디스크 IO를 발생시켜 많은 자원을 소모하기 때문에 InnoDB에서는 변경할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 디스크에 있으면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 높힙니다. 이 임시 메모리 공간을 체인지 버퍼라고 합니다.
유니크 인덱스는 체인지 버퍼를 사용할 수 없습니다. 체인지 버퍼에 임시로 저장된 레코드 조각은 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드(Merge thread)라고 합니다. 작업의 종류별로 체인지 버퍼를 활성화할 수 있으며, 사용하지 않게도 설정할 수 있습니다. 또한 체인지 버퍼는 기본 값으로 버퍼 풀에 할당된 메모리 공간의 25%까지 사용할 수 있고, 50%까지 늘릴 수 있습니다. 이는 innodb_change_buffer_max_size 시스템 변수에 비율을 설정하면 됩니다.
리두 로그 및 로그 버퍼
대부분의 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록합니다. 거의 모든 DBMS에서 데이터 파일은 쓰기보다는 읽기 성능에 초점을 맞춘 자료 구조를 가지고 있기 때문에 쓰기 작업은 랜덤 디스크 IO를 유발합니다. 이로 인한 성능 저하를 막기 위해서 쓰기 비용이 적은 리두 로그를 먼저 작성하며, 비정상 종료가 발생하면 리두 로그로 서버가 종료되기 직전의 상태로 복구합니다. 리두 로그 또한 InnoDB 버퍼 풀, 로그 버퍼 등의 버퍼링할 수 있는 자료 구조가 마련돼있습니다. 커밋이 됐든 롤백이 됐든 어쨌든 리두 로그는 그 변경이 커밋됐는지, 롤백됐는지, 실행 중간 상태였는지를 확인하기 위해서 리두 로그가 필요합니다.
데이터베이스 서버에서 리두 로그는 장애 발생 시 장애 직전 시점까지의 복구를 위해서 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정해놓는 것을 권장합니다. 하지만 이는 많은 부하를 유발합니다. 따라서 InnoDB는 어느 주기로 디스크에 동기화할지 innodb_flush_log_at_trx_commit 시스템 변수를 이용해서 결정합니다. 이는 3개의 값을 가질 수 있습니다. 이때 0과 2에서는 디스크 동기화 간격을 innodb_flush_log_at_timeout 시스템 변수로 설정할 수 있습니다.
- 0: 1초에 한 번씩 리두 로그를 디스크로 기록하고 동기화 실행
- 1: 매번 트랜잭션이 커밋될 때마다 디스크로 기록되고 동기화 실행
- 2: 매번 트랜잭션이 커밋될 때마다 디스크로 기록하고 1초마다 동기화 실행
변경 작업이 많은 DBMS 서버의 경우에는 리두 로그의 기록 작업이 큰 문제가 됩니다. 이러한 부분을 보완하기 위해서 ACID 속성을 보장하는 수준에서 버퍼링하는데, 이러한 리두 로그 버퍼링이 이용되는 공간이 로그 버퍼입니다. 기본값은 16MB으로, BLOB이나 TEXT와 같이 데이터를 자주 변경하는 경우에는 더 크게 설정하는 것이 좋습니다.
이러한 리두 로그도 8.0 버전부터 활성화 또는 비활성화를 할 수 있는데, 비활성화를 하면 대량의 데이터를 한 번에 적재하는 경우에 적재 시간을 단축할 수 있습니다. 다만 활성화하는 것을 잊을 경우에 비정상적인 종료가 발생하면 일관된 데이터가 아니라 다양한 시점의 데이터를 가지게 될 수 있습니다. 또한 마지막 체크포인트 이후 시점의 데이터는 모두 복구할 수 없게 됩니다. 따라서 데이터가 일부 손실돼도 괜찮아도 비활성화보다는 innodb_flush_log_at_trx_commit 시스템 변수를 0 또는 2로 설정해서 사용하는 것이 좋습니다.
어댑티브 해시 인덱스
어댑티브 해시 인덱스는 InnoDB에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이며 사용자는 innodb_adaptive_hash_index 시스템 변수를 이용해서 어댑티브 해시 인덱스를 활성화 또는 비활성화할 수 있습니다.
일반적으로 B-Tree 인덱스에서 특정 값을 찾는 과정이 매우 빠르다고 많은 사람들이 생각합니다만, 데이터베이스 서버가 얼마나 많은 일을 하느냐에 따라서 이는 빨라질 수도 있고, 느려질 수도 있습니다. 이런 작업을 동시에 몇 개 실행한다고 해서 성능 저하가 보이지는 않지만, 몇천 개의 스레드로 실행하면 컴퓨터의 CPU에서 경합이 발생해서 쿼리의 성능은 떨어집니다. 어댑티브 해시 인덱스는 이런 B-Tree 검색 시간을 줄여주기 위해서 도입된 기능입니다. 자주 읽히는 데이터 페이지의 키 값을 이용해서 인덱스를 만들고 필요할 때마다 어댑티브 ㅇ해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있도록 합니다. B-Tree에서 루트 노드부터 리프 노드까지 찾아가는 비용이 제거되고 쿼리 성능은 그만큼 빨라집니다. 이에 따라서 더 많은 동시 쿼리 처리를 할 수 있게 됩니다.
해시 인덱스는 인덱스 키 값과 데이터 페이지 주소의 쌍으로 관리됩니다. 여기서 인덱스 키 값은 B-Tree 인덱스의 고유번호와 B-Tree 인덱스의 실제 키 값의 조합으로 생성됩니다. 모든 B-Tree 인덱스에 대한 어댑티브 해시 인덱스가 하나의 해시 인덱스에 저장되기 때문에 특정 키 값이 어느 인덱스에 속한 것인지도 구분해야하기 때문입니다. 데이터 페이지 주소는 버퍼 풀에 로딩된 페이지의 주소를 의미합니다. 즉, 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리됩니다. 해당 페이지가 없어지면 어댑티브 해시 인덱스 또한 정보가 제거됩니다.
8.0부터 어댑티브 해시 인덱스의 파티션 기능을 제공해서 innodb_adaptive_hash_index_parts 시스템 변수를 이용해서 파티션 개수를 변경할 수 있습니다. 기본 값은 8이며 어댑티브 인덱스가 성능에 많은 도움이 된다면 파티션 개수를 더 많이 설정해서 내부 잠금 경합을 줄여 성능을 더 높힐 수 있을 것입니다. 다음은 도움이 되는 경우의 예시입니다.
- 디스크의 데이터가 InnoDB 버퍼 풀의 크기와 비슷한 경우
- 동등 조건 검색(IN 연산자 등)이 많은 경우
- 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
어댑티브 해시 인덱스가 도움이 되지 않고, 오히려 성능 저하를 유발하는 경우도 있습니다. 또한 활성화되면 InnoDB는 그 값이 해시 인덱스에 있든 없든 일단 검색해봐야하는 오버헤드가 생깁니다. 효율이 없는 경우에는 불필요한 오버헤드가 생긴다는 의미입니다. 다음은 도움이 되지 않는 경우의 예시입니다.
- 디스크 읽기가 많은 경우
- 특정 패턴의 쿼리가 많은 경우(Join이나 LIKE 패턴 검색)
- 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
또한 인덱스가 2개가 있는 꼴이므로 어댑티브 해시 인덱스의 도움을 많이 받을수록 테이블 삭제 또는 변경 작업 등의 DDL 작업은 더욱 더 시간이 오래 걸립니다. 따라서 사용 시에는 이 부분을 알아두고 사용해야 합니다.
어댑티브 해시 인덱스가 도움이 되는지 안 되는지를 판단하려면 서버의 상태 값들을 살펴보는 것이 좋습니다.
SHOW ENGINE INNODB STATUS\G
여기서 해시 인덱스를 사용한 쿼리와 그렇지 못한 쿼리의 비율을 확인할 수 있습니다. 효율성은 검색 횟수가 아니라 두 값의 비율, 해시 인덱스가 사용 중인 메모리 공간, 서버의 CPU 사용량을 종합적으로 판단해야 합니다. CPU 사용량이 적으면서도 히트율이 높다면 어댑티브 해시 인덱스를 비활성화해서 InnoDB 버퍼 풀이 더 많은 메모리를 사용할 수 있게 유도하는 것도 좋은 방법이 될 수 있습니다.
❓테이블 잠금
테이블 잠금의 경우에는 MySQL 엔진에서 관리돼서 그보다 하위 레이어인 스토리지 엔진은 확인할 수 없습니다. 여기서 innodb_table_locks 시스템 변수를 활성화하면 테이벌 레벨의 잠금까지 InnoDB 스토리지 엔진에서 감지할 수 있게됩니다.
❓왜 리두 로그는 동기화하지 않으면서 DoubleWrite만 활성화하는 것은 잘못된 선택인가?
데이터 무결성 보호 수준이 불균형해지기 때문입니다. DoubleWrite만 켜고 리두 로그 동기화를 하지 않으면 페이지 깨짐 현상은 막았지만, 정작 어떤 변경을 해야 하는지 모르는 상태가 됩니다. 즉, committed 트랜잭션이 누락되면 데이터 지속성이 깨져서 DoubleWrite을 해도 데이터 손실 가능성이 있다는 뜻입니다. 결국 그 트랜잭션은 롤백된 것처럼 취급되거나 누락될 것입니다.
❓왜 Undo tablespace truncate 작업에서 수동 모드에서는 테이블스페이스가 3개 이상이어야 작동하는가?
온라인 truncate를 안전하게 수행하면서 자동 truncate 기능도 동시에 지원하기 위함입니다.
기본적으로 자동 truncate에 경우에도 최소 2개가 필요합니다. 한 쪽을 청소하려고 잠금을 걸어 놓은 경우에도 서버는 계속해서 Undo가 생기기 때문입니다. 이에 다른 쪽에 언두 로그를 작성합니다. 1개만 있으면 서버 멈춤 현상이 발생할 것입니다.
수동 truncate의 경우에는 관리자가 직접 언두 테이블 스페이스를 비우라고 명령하는 것인데, 이 과정에서도 자동 truncate가 켜질 수 있습니다. 수동으로 하나를 비우는 중이므로 최소 3개가 있어야 1개를 수동으로 비우는 중과 동시에 자동 truncate를 처리할 수 있게 됩니다. 만약 2개만 있는 상태에서 수동으로 inactive를 시도하면 InnoDB가 해당 truncate을 거부합니다.
참고 문헌
Real MySQL 8.0
https://jwkim96.tistory.com/261
[MySQL] 4. 아키텍처(2)
Real MysQL 8.0 1권을 읽고 정리한 내용입니다. 잘못된 내용이 있을 경우 댓글로 남겨주시면 감사하겠습니다🙏 InnoDB 스토리지 엔진 아키텍처 MySQL 의 스토리지 엔진 중 가장 많이 사용되는 InnoDB 스
jwkim96.tistory.com
'Backend > DB' 카테고리의 다른 글
| [DB] 데이터 모델링 (0) | 2026.05.30 |
|---|---|
| [DB] 비관적 락과 낙관적 락 (0) | 2026.05.01 |
| [MySQL] MySQL에서의 트랜잭션과 잠금 분석 (0) | 2026.03.26 |
| [MySQL] MySQL 엔진 아키텍처 분석 (0) | 2026.03.05 |