4.2 InnoDB 스토리지 엔진 아키텍처(1)

InnoDB 구조

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반 잠금을 제공하며, 그 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

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

InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 자장된다.

프라이머리 키 값의 순서대로 디스크에 저장되며, 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.
쿼리의 실행계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정된다.

외래 키 지원

외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다.

외래 키는 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 자주 있다. 그렇다 하더라도 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다.

InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요함
변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로, 잠금이 여러 테이블로 전파됨
그로인한 데드락이 발생할 때가 많으므로 개발할때도 외래 키의 존재에 주의하는 것이 좋음

수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업이 실패할 수 있다. 부모 테이블과 자식 테이블의 관계를 명확히 파악해서 순서대로 작업한다면 문제없이 실행될 수 있지만 외래키가 복잡하게 얽힌 경우에는 간단하지 않다.

foreign_key_checks 시스템 변수를 OFF로 설정하면 외래키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다. 외래키 체크 작업을 일시적으로 멈추면 대략 레코드 적재나 삭제 등의 작업도 부가적인 체크가 필요 없기 때문에 훨씬 빠르게 처리할 수 있다.

1
2
3
4
5
SET foreign_key_checks=OFF;

/* 작업 수행 ... */

SET foreign_key_checks=ON;

외래키 체크를 일시적으로 중지한 상태에서 외래키 관계를 가진 부모 테이블의 레코드를 삭제했다면 반드시 자식 테이블의 레코드도 살제하여 일관성을 맞춰준 후 다시 외래키 체크 기능을 활성화 해야 한다.

foreign_key_checks가 비활성화되면 외래키 관계의 부모 테이블에 대한 작업도 무시한다.(ON DELETE CASCADE, ON UPDATE CASCADE)

MVCC - Multi Version Concurrency Control

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다.

InnoDB는 언두 로그(Undo log)를 이용해 이 기능을 구현한다.

멀티 버전: 하나의 레코드에 대해 여러 개의 버전이 동시에 관리

1
2
3
4
5
6
7
CREATE TABLE member (
    m_id INT NOT NULL,
    m_name VARCHAR(20) NOT NULL,
    m_area VARCHAR(100) NOT NULL,
    PRIMARY KEY (m_id),
    INDEX ix_area (m_area)
);

1
2
INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울');
COMMIT;

1
UPDATE member SET m_area='경기' WHERE m_id=12;

UPDATE 문장이 실행되면 커밋 실행 여부와 관계 없이 InnoDB의 버퍼풀은 새로운 값인 ‘경기’로 업데이트 된다. 그리고 디스크의 데이터 파일에는 체크포인트나 InnoDB의 Write 스레드에 의해 새로운 값으로 업데이트돼 있을 수도 있고 아닐 수도 있다.(InnoDB가 ACID를 보장하기 때문에 일반적으로는 InnoDB의 버퍼풀과 데이터 파일은 동일한 상태라고 가정해도 무방함)

아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 다음 같은 쿼리로 작업 중인 레코드를 조회한다면, MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준(Isolation level)에 따라 다르다.

READ_UNCOMMITED: InnoDB 버퍼풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다.
READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE: 아직 커밋되지 않았기 때문에 InnoDB 버퍼풀이나 데이터 파일에 있는 내용 대신 변경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다.

이러한 과정을 DBMS에서는 MVCC라고 표현한다. 즉 하나의 레코드(회원번호가 12인 레코드)에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 다르다.

트랜잭션이 길어지면 언두에서 관리하는 예전 데이터가 삭제되지 못하고 오랫동안 관리되어야 하며, 자연히 언두 영역이 저장되는 시스템 테이블 스페이스의 공간이 많이 늘어나는 상황이 발생할 수 있다.

UPDATE 쿼리가 실행되면 InnoDB 버퍼 풀은 즉시 새로운 데이터로 변경되고 기존 데이터는 언두영역으로 복사된다.

COMMIT: InnoDB는 더 이상의 변경 작업 없이 지금의 상태를 영구적인 데이터로 만들어 버린다.
ROLLBACK: 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제한다.

커밋이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되지는 않고, 언두 영역을 필요로 하는 트랜잭션이 없을때 삭제된다.

잠금 없는 일관된 읽기 - Non-Locking Consistent Read

InnoDB 스토리지 엔진은 MVCC 기술을 이용해 감금을 걸지 않고 읽기 작업을 수행한다. 잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다.

격리수준이 SERIALIZABLE이 아닌 READ_UNCOMMITED나 READ_COMMITED, REPEATEABLE_READ 수준인 경우 INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계 없이 항상 잠금을 대기하지 않고 바로 실행된다.

특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았다 하더라도 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다. 이를 ‘잠금 없는 일관된 읽기’ 라고 표현하며, InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용한다.

오랜 시간 동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 때가 가끔 있는데, 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 발생하는 문제이다.

따라서 트랜잭션이 시작됐다면 가능한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.

자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록(Wait-for List)을 그래프 형태로 관리한다. InnoDB 스토리지 엔진은 데드락 감지 스레드를 통해 주기적으로 잠금 대기를 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료한다.

트랜잭션의 언두 로그양이 적은 트랜잭션이 롤백 해도 처리한 내용이 적기 때문에 선택된다.

InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금(LOCK TABLES 명령으로 잠긴 테이블)은 볼 수가 없어 데드락 감지가 불확실 할 수 있는데, innodb_table_locks 시스템 변수를 활성화 하면 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금 까지 감지할 수 있게 된다.

일반적인 서비스에서는 데드락 감지 스레드가 데드락을 찾아내는 작업은 부담되지 않지만, 동시 처리 스레드가 매우 많아지거나 트랜잭션이 가진 잠금 개수가 많아지면 데드락 감지 스레드가 느려진다.

데드락 감지 스레드는 잠금 목록을 검사해야 하기 때문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트(잠금 테이블)에 새로운 잠금을 걸고 데드락 스레드를 찾게 되는데, 데드락 감시 스레드가 느려지면 서비스 쿼리를 처리중인 스레드는 더는 작업을 진행하지 못하고 대기하며 서비스에 악영항을 미치게 된다. 이렇게 동시 처리 스레드가 매우 많은 경우 데드락 감지 스레드는 더 많은 CPU 자원을 소모할 수도 있다.

innodb_deadlock_detect 시스템 변수를 활용하여 데드락 감지 스레드를 비활성화 할 수 있다. 이럴 경우 데드락 상황 발생시 무한정 대기할 수도 있지만, innodb_lock_wait_timeout 시스템 변수를 활성화하면 일정 시간이 지났을 경우 요청 실패하고 에러 메시지를 반환하게 만들 수 있다.

데드락 감시 스레드가 부담되어 innodb_deadlock_detect를 OFF로 설장해서 비활성화 하는 경우에는 innodb_lock_time_wait_timeout을 기본값인 50초보다 훨씬 낮은 시간으로 변경하여 사용할 것을 권장한다.

자동화된 장애 복구

InnoDB에는 손실이나 장애로 부터 데이터를 보호하기 위한 여러가지 메커니즘이 탑재돼있다. 그러한 메커니즘을 이용해 MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된(Partial write)데이터 페이지 등에 대한 인련의 복구 작업이 자동으로 진행된다.

InnoDB 스토리지 엔진은 매우 견고해서 데이터 파일이 손상되거나 MySQL 서버가 시작되지 못하는 경우는 거의 발생하지 않지만, 디스크나 하드웨어 이슈로 InnoDB 스토리지 엔진이 자동으로 복구를 못 하는 경우도 발생할 수 있는데, 한번 문제가 생기면 복구하기 쉽지 않다.

InnoDB 데이터 파일은 기본적으로 서버가 시작될 때 자동 복구를 수행하며, 자동으로 복구될 수 없는 손상이 있다면 서버가 종료된다.

장애 복구 대응

MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정하여 시작해야 한다.

6: 로그 파일 손상
1: 테이블의 데이터 파일이 손상
어떤 부분이 문제인지 알 수 없다면 1~6까지 변경하며 재실행

이후 서버가 가동되고 InnoDB 테이블이 인식된다면 mysqldump를 이용해 데이터를 가능한 만큼 백업하고 그 데이터로 MySQL 서버의 DB와 테이블을 다시 생성하는 것이 좋다.

`InnoDB_force_recovery` 옵션

1(SRV_FORCE_IGNORE_CORRUPT):
테이블스페이스의 데이터나 인덱스 페이지에서 손상된 부분이 발견되도 무시하고 서버를 시작한다.
- ‘Database page corruption on disk or a failed’ 출력되는 경우가 많다.
- mysqldump나 SELECT INTO OUTFILE ...를 이용해 덤프하여 데이터베이스를 다시 구축하는 것이 좋다.
2(SRV_FORCE_NO_BACKGROUND):
백그라운드 스레드 가운데 메인 스레드를 시작하지 않고 MySQL 서버를 시작한다.
- 메인 스레드가 언두 데이터를 삭제하는 과정에서 장애가 발생했을때 사용
3(SRV_FORCE_NO_TRX_UNDO):
일반적으로 MySQL 서버는 재실행시 언두 영역의 데이터를 먼저 파일에 적용하고 리두 로그의 내용을 다시 덮어써서 장애 시점의 데이터 상태를 만들어 낸 후, 최종적으로 커밋되지 않은 트랜잭션의 작업을 롤백하지만 3으로 설정시 롤백하지 않고 그대로 나둔다.
- 커밋되지 않고 종료된 트랜잭션은 계속 그 상태로 남아있게 된다.
- 백업 후 데이터베이스를 다시 구축하는 것이 좋다.
4(SRV_FORCE_NO_IBUF_MERGE):
InnoDB는 INSERT, UPDATE, DELETE 등의 데이터 변경으로 인한 인덱스 변경 작업을 상황에 따라 즉시처리 혹은 버퍼에 두고 나중에 처리할 수 있다. 인서트 버퍼를 통해 처리가 될 경우, 비정상 종료시 병합 될지 알 수 없기 때문에, 인서트 버퍼의 손상을 감지하면 에러를 발생시켜 MySQL 서버의 실행을 막는다.
- 인서트 버퍼의 내용을 무시하고 강제로 MySQL을 실행시킨다.
- 인서트 버퍼는 실제 데이터와 관련된 부분이 아니라, 인덱스에 관련된 부분이므로 테이블을 텀프한 후 다시 데이터베이스를 구축하면 데이터의 손실 없이 복구할 수 있다.
5(SRV_FORCE_NO_UNDO_LOG_SCAN):
MySQL 서버가 종료되는 시점에 처리중인 트랜잭션이 있을 경우 별도의 처리 없이 커넥션을 강제로 끊어버리고 종료된다. MySQL 서버가 재실행되면 InnoDB 엔진은 언두 레코드를 이용해 데이터 페이지를 복구하고 리두 로그를 적용해 종료 시점의 상태로 만들고, 커밋되지 않은 트랜잭션에서 변경한 작업은 모두 롤백 처리한다. 이때 InnoDB 스토리지 엔진이 언두 로그를 사용할 수 없다면 에러가 발생하여 MySQL 서버가 실행될 수 없다.
- 언두 로그를 모두 무시하고 실행한다.
- MySQL 서버가 종료되던 시점에 커밋되지 않았던 작업도 모두 커밋된 것처럼 처리되어 잘못된 데이터가 남을 수 있다.
- 데이터를 백업하고, 데이터베이스를 새로 구축해야한다.
6(SRV_FORCE_NO_LOG_REDO):
InnoDB 스토리지 엔진의 리두 로그가 손상되면 MySQL 서버가 실행되지 못한다. 해당 복구 모드로 실행하면 리두 로그를 무시하고 서버가 실행된다.
트랜잭션이 커밋됐다 하더라도 리두 로그에만 기록되고 데이터 파일에 기록되지 않은 데이터는 모두 무시되므로 마지막 체크 포인트시점의 데이터만 남게 된다.
- 기존 InnoDB의 리두 로그는 모두 삭제 또는 백업하고 MySQL 서버를 시작하는 것이 좋다.
- 데이터를 백업하고 MySQL 서버를 새로 구축하는 것이 좋다.

위 방법을 수행해도 MySQL서버가 시작되지 않으면 백업을 이용해 다시 구축하는 방법밖에 없다. 백업이 있다면 마지막 백업으로 데이터베이스를 다시 구축하고, 바이너리 로그를 사용해 최대한 장애 시점까지의 데이터를 복구할 수도 있다.

마지막 풀 백업 시점부터 장애 시점까지의 바이너리 로그가 있다면 이용하는 것이 데이터 손실이 더 적을 수 있다.

백업은 있지만 복제의 바이너리 로그가 없거나 손실되었다면, 마지막 백업 시점가지만 복구할 수 있다.