4장 - InnoDB 스토리지 엔진 아키텍처

CS/Real MySQL 8.0 요약

4장 - InnoDB 스토리지 엔진 아키텍처

Junuuu 2023. 10. 16. 00:01

개요

이전 챕터인 MySQL 엔진 아키텍처에 이어 InnoDB 스토리지 엔진 아키텍처에 대해 알아보고자 합니다.

InnoDB 스토리지 엔진 아키텍처

InnoDB는 MySQL에서 가장 많이 사용됩니다.

거의 유의할게 레코드 기반 잠금을 제공하기 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어납니다.

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

InnoDB의 모든 테이블은 기본적으로 PK를 기준으로 클러스터링 되어 저장됩니다.

즉, PK의 순서대로 디스크에 저장되며 세컨더리 인덱스는 레코드의 주소 대신 PK의 값을 논리적인 주소로 사용합니다.

이로 인해 PK로 인한 레인지 스캔은 굉장히 빨리 처리되며 실행계획에서도 다른 인덱스보다 PK가 선택될 확률이 높습니다.

외래 키 지원

외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하며 MyISAM이나 Memory 테이블에서는 사용할 수 없습니다.

대게 서버 운영의 불편함 때문에 서비스용 데이터베이스에는 생성하지 않는 경우도 자주 있지만, 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 수행합니다.

InnoDB에서는 부모 테이블이나 자식 테이블에 데이터를 체크할 때 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래 키의 존재를 주의해야 합니다.

수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업도 테이블의 관계를 명확하게 파악해서 순서대로 작업하지 않는다면 실패할 수 있습니다.

MVCC(Multi Version Concurrency Control)

Multi Version의 뜻은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미입니다.

MVCC의 가장 큰 목적으로는 잠금을 사용하지 않는 일관된 읽기를 제공합니다.

InnoDB에서는 Undo log를 이용하여 이 기능을 구현합니다.

테이블을 만들고, INSERT 쿼리를 수행하게 되면 위와 같은 그림의 형태를 띱니다.

InnoDB 버퍼 풀과 디스크에 데이터가 저장되어 있습니다.

여기에서 UPDATE 쿼리가 실행되면 커밋 여부와 관계없이 버퍼 풀은 경기라는 값으로 업데이트됩니다.

이때 경기라는 값으로 변경하기 전에 서울이라는 값을 언두 로그에 복사해 둡니다.

이제 다시 m_id가 12인 member를 조회하면 어떻게 될까요?

트랜잭션 격리 수준에 따라 달라지게 됩니다

READ_UNCOMMITTED라면 InnoDB 버퍼 풀이 현재 가진 상태를 읽어 반환합니다.

하지만 READ_COMMITTED나 그 이상 격리 수준인 경우에는 언두 영역의 데이터를 반환합니다.

이 상황에서 커밋을 수행하면 현재 상태를 영구적인 데이터로 만들고, 롤백을 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고 언두 영역에는 해당 내용을 삭제합니다.

커밋이 일어난다고 해서 언두 영역의 데이터가 바로 삭제되지는 않으며, 해당 데이터가 필요로 하는 트랜잭션이 없을 때 비로소 삭제됩니다.

잠금 없는 일관된 읽기

InnoDB 스토리지 엔진은 MVCC 기술을 이용하여 잠금을 걸지 않고 읽기 작업을 수행합니다.

이로 인해 SERIALIZABLE이 아닌 격리 수준에서는 순수 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행됩니다.

하지만 트랜잭션이 오랫동안 활성화되어 있다면 일관된 읽기를 위해 언두 로그가 계속 유지되기 때문에 가능한 한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋습니다.

자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 교착 상태에 빠지지 않았는지 체크합니다.

언두 로그의 트랜잭션량을 더 적게 가진 트랜잭션이 강제종료 됩니다. (롤백으로 인한 부하가 덜 유발되기 때문)

스토리지 엔진에서는 상위 레이어인 MySQL엔진에서 관리되는 Table Lock은 볼 수 없어 데드락 감지가 불확실할 수 있습니다.

innodb_table_locks 시스템 변수를 활성화하면 스토리지 엔진의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감시할 수 있습니다.

특별한 이유가 없다면 해당 시스템 변수를 활성화하는 것이 좋습니다.

물론 동시 처리 스레드가 매우 많아지거나, 잠금 개수가 많아지면 데드락 감지 스레드가 느려질 수 있습니다.

데드락 감지 스레드는 잠금 목록을 검사하기 위해 잠금 상태가 변경되지 않도록 Wait-for-List를 잠금을 걸고 데드락 스레드를 찾습니다.

이 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리하는 스레드는 대기하면서 서비스에 악영향을 미치게 됩니다.

시스템 변수로 데드락 감지 스레드를 OFF 할 수 있지만 이런 경우 데드락이 발생하면 무한 대기를 수행합니다.

따라서 기본값인 50초보다 더 낮은 시간으로 변경해서 사용하는 것이 권장됩니다.

InnoDB 버퍼 풀

스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 캐시해 두는 공간입니다.

쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 수행합니다.

CUD와 같이 데이터를 변경하는 작업은 데이터 파일의 디스크 랜덤 I/O 작업을 발생시킵니다.

버퍼 풀이 일괄작업을 처리해 주면 디스크 랜덤 I/O 작업을 줄일 수 있습니다.

버퍼풀이라는 거대한 메모리 공간을 페이지 크기의 조각의 데이터를 읽어 저장하고 관리합니다.

디스크로 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼풀의 메모리에 유지해서 디스크 읽기를 최소하 하는 것이 목적입니다.

InnoDB 스토리지 엔진에서 데이터를 찾는 과정

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사

InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색하고 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색, 이미 데이터가 있자면 해당 페이지가 더 오래 유지되도록 작업

2. 디스크에서 필요한 데이터를 버퍼 풀에 적재

3. 필요한 데이터가 자주 접근되었다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

버퍼 풀과 리두 로그

MVCC를 위해 언두 로그를 사용한다는 것은 위에서 알게 되었는데 리두 로그는 무엇일까요?

DB 장애 발생 시 복구에 사용되는 로그입니다.

실제로 Database 커밋이 발생하면 바로 디스크 영역에 들어가지 않고 메모리 영역에 들어가게 됩니다.

InnoDB는 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링을 제공합니다.

단순히 버퍼 풀의 메모리 공간을 늘리게 되면 캐시 기능만 향상이 되며 쓰기 버퍼링 기능까지 향상하려면 InnoDB 버퍼 풀과 리두 로그의 관계를 이해해야 합니다.

버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지와 함께 변경된 데이터를 가진 더티 페이지를 가지고 있습니다.

더티 페이지는 언젠가 디스크에 기록되어야 합니다.

리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용합니다.

데이터 변경이 발생하면 로그 파일에 기록된 엔트리는 새로운 로그 엔트리로 덮어씌워집니다.

InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화합니다.

이때 더티 페이지가 많으면 디스크 쓰기 폭발 현상이 발생할 수 있는데 시스템 변수 설정을 통해 일정 수준 이상의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록하게 할 수 있습니다.

버퍼풀 상태 백업 및 복구

버퍼 풀은 쿼리 성능과 밀접한 영향이 있습니다.

쿼리 요청이 매우 빈번한 서버는 셧다운 하고 시작하면 평상시보다 성능이 1/10도 안될 수 있습니다.

이전에는 버퍼 풀에 이미 데이터가 존재하기 때문에 디스크 I/O가 발생하지 않았기 때문입니다.

MySQL 5.6부터는 버퍼 풀 덤프 및 적재 기능이 도입되어 버퍼풀을 백업해 두었다가 다시 복구할 수 있습니다.

체인지 버퍼

레코드가 INSERT UPDATE 될 때는 데이터 파일을 변경하는 것과 더불어 인덱스를 업데이트해야 합니다.

테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모합니다.

인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 하지만 그렇지 않고 디스크에서 읽어와야 한다면 이때 체인지 버퍼를 활용합니다.

체인지 버퍼라는 임시 공간에 저장해 두고 사용자에게는 결과를 바로 반환하면서 성능 향상을 합니다.

이때 사용자에게 결과를 전달하기 전에 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없습니다.

체인지 버퍼에 임시 저장된 인덱스 레코드 조각은 백그라운드 스레드에 의해 병합되는에 이 스레드를 체인지 버퍼 머지 스레드라 합니다.

리두 로그 및 로그 버퍼

ACID 중 D에 해당하는 영속성 영역과 가장 밀접하게 관련 있습니다.

서버가 비정상적으로 종료되었을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치입니다.

대부분의 데이터베이스 서버는 로그로 먼저 기록합니다. (데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요해 상대적으로 큰 비용)

커밋되었지만 데이터 파일에 기록되지 않은 데이터는 리두 로그에 저장된 데이터를 파일에 다시 복사하면 됩니다.

하지만 롤백되었지만 데이터 파일에 이미 기록된 데이터는 리두 로그로는 해결할 수 없습니다.

리두 로그를 통해 해당 변경이 커밋되었는지, 롤백되었는지, 트랜잭션의 실행 중간 상태였는지 확인할 수 있습니다.

트랜잭션이 커밋될 때마다 리두 로그를 디스크에 기록하는 작업은 많은 부하를 유발합니다.

보통 주기를 가지고 디스크에 동기화를 수행합니다.

리두 로그 버퍼링에 사용되는 공간이 로그 버퍼입니다.

어뎁티브 해시 인덱스

일반적으로 인덱스라고 하면 사용자가 생성해 둔 B-Tree 인덱스를 의미합니다.

여기서의 Adaptive Hash Index는 사용자가 생성하는 것이 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스입니다.

B-Tree 검색 시간을 줄여주기 위해 도입된 기능이며, 이로 인해 B-Tree를 루트노드부터 리프 노드까지 찾아가는 비용이 적어지게 됩니다.

어댑디트 해시 인덱스를 ㅅ용하면 쿼리의 처리량은 2배 가까이 늘어나고 CPU의 사용량은 오히려 감소합니다.

B-Tree 검색이 줄어들면서 내부 잠금의 횟수도 획기적으로 줄어듭니다.

매우 좋은 옵션으로 보이지만 크게 도움이 되지 않는 상황들도 있습니다.

디스크 읽기가 많은 경우
JOIN이나 LIKE 패턴의 쿼리가 많은 경우
매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

성능 향상에 도움이 되는 경우

디스크의 데이터가 InnoDB 버퍼 풀과 비슷한 경우 (디스크 읽기가 많지 않은 경우)
동등 조건 검색(==, In 연산자)이 많은 경우
쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

결국 인덱스이다 보니 테이블 삭제 또는 변경 작업에도 영향을 받고 더 많은 CPU 자원을 사용하게 됩니다.

MySQL 로그 파일

대게 서버가 문제가 발생했을 때는 로그 파일을 자세히 보다 보면 원인을 쉽게 찾아 해결할 수 있습니다.

에러 로그 파일의 위치는 my.cnf에서 log_eror라는 이름의 파라미터로 정의된 경로에 생성됩니다.

.err라는 확장자가 붙은 파일로 생성됩니다.

실행되는 쿼리를 검토해보고 싶을 수 있습니다.

general_log_file이라는 이름의 파라미터로 쿼리 로그 파일의 경로를 설정할 수 있습니다.

슬로 쿼리를 보고 싶을 수 있습니다.

시스템변수(long_query_time)에서 설정한 이상의 소요된 쿼리가 모두 기록됩니다.

참고자료

Real MySQL 8.0