MySQL InnoDB storage

InnoDB 스토리지 엔진

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

InnoDB스토리지 엔진의 특징

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

InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다. 즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며, 이로 인해 프라이머리 키에 의한 레인지 스캔은 상당히 빨리 처리될 수 있다. 결과적으로 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정( 쿼리의 실행 계획에서 다른 보조 인덱스보다 프라이머리 키가 선택될 학률이 높음 )된다. 오라클 DBMS의 IOT( Index Organized Table ) 와 동일한 구조가 InnoDB에서는 일만적인 테이블의 구조가 되는 것이다. 

잠금이 필요 없는 일관된 읽기 ( Non-locking consistent read )

InnoDB 스토리지 엔진은 MVCC( Multi Version Concurrency Control ) 라는 기술을 이용해 락을 걸지 않고 읽기 작업을 수행한다. 락을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 락을 기다리지도 않고 읽기 작업이 가능하다. ( SERIALIZABLE 격리 수준은 제외 )

외래 키 지원

외래 키에 대한 지원은 InnoDb 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다. 외래 키는 여러 가지 제약사항 탓으로 인해 실무에서는 잘 사용하지 않기 때문에 그렇게 필수적이지는 않지만 개발 환경의 데이터베이스에서는 좋은 가이드 역활을 할 수 있다. InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고 그로 인해 데드락이 발생할 때가 많다.

자동 데드락 감지

InnoDB는 그래프 기반의 데드락 체크 방식을 사요하기 때문에 데드락이 발생함과 동시에 바로 감지되고 감지된 데드락은 관련 트랜잭션 중에서 ROLLBACK이 가장 용이한 트랜잭션 ( ROLLBACK 을 했을 때 복구 작업이 가장 작은 트랜잭션, 즉 레코드를 가장 적게 변경한 트랜잭션) 을 자동적으로 강제 종료해 버린다. 따라서 데드락 때문에 쿼리가 제한시간( Timeout )에 도달한다거나 슬로우 쿼리로 기록되는 경우는 많지 않다.

자동화된 장애 복구

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

오라클의 아키텍처 적용

InnoDB 스토리지 엔진의 기능은 오라클 DBMS의 기능과 상당히 비슷한 부분이 많다. 대표적으로 MVCC기능이 제공된다는 것과 언두( Undo )데이터가 시스템 테이블 스페이스에 관리된다는 것, 그리고 테이블 스페이스의 개념 등이 있으며 이 외에도 상당히 흡사한 부분이 많아서 오라클에 익숙한 사용자에게는 InnoDB의 많은 부분들이 상당히 친숙할 것이다.

InnoDB 버퍼 풀

InnoDB스토리지 엔진에서 가장 핵심적인 부분으로 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다. 쓰기 작업을 지연시켜 일괄적업으로 처리할 수 있기 해주는 버퍼 역활도 같이한다. 일반적인 애플리케이션에서는 INSERT나 UPDATE 그리고 DELETE와 같이 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시킨다. 하지만 버퍼 풀이 이러한 변경된 데이터를 모아서 처리하게 되면 랜덤한 디스크 작업의 회수를 줄일 수 있다.

MyISAM 키 캐시가 인덱스의 캐시만을 주로 처리하는 데 비해 InnoDB의 버퍼 풀은 데이터와 인덱스 모두 캐시하고 쓰기 버퍼링의 역할까지 모두 처리하고 있는 것이다. 그 밖에도 InnoDB의 버퍼 풀은 많은 백그라운드 작업의 기반이 되는 메모리 공간이다. 따라서 InnoDB의 버퍼 풀 크기를 설정하는 파라미터 ( innodb_buffer_pool_size )는 신중하게 설정하는 것이 좋다. 일반적으로 전체 물리적인 메모리의 80%정도를 InnoDB의 버퍼 풀로 설정하라는 내용의 글이 있는데 그렇게 단순하게 설정해서 되는 값이 아니며, 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정해야 한다.

일반적으로 전체 장착된 물리 메모리의 50~80%수준에서 버퍼 풀의 메모리 크기를 결정한다.

InnoDB 버퍼 풀은 아직 디스크에 기록되지 않은 변경된 데이터를 가지고 있다( 이러한 데이터를 가지고 있는 페이지를 더티페이지 ( Dirty Page ) 라고 한다 ) 이러한 더티 페이지는 InnoDB에서 주기적으로 또는 어떤 조건이 되면 체크포인트 이벤트가 발생하는데 이때 Write 스레드가필요한 만큼의 더티 페이지만 디스크로 기록한다. 체크 포인트가 발생한다고 해서 버퍼 풀의 모든 더티페이지를 디스크로 기록하는 것은 아니다.

언두( Undo ) 로그

언두 영역은 UPDATE 문장인다 DELETE와 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터(이전 데이터 ) 를 보관하는 곳이다.

UPDATE문을 실행하면 트랜잭션을 커밋하지 않아도 실제 데이터 파일 ( 데이터/인덱스 버퍼 ) 내용은 UPDATE시의 데이터로 변경된다. 그리고 변경되기 전의 값이 언두 영역에 백업 되는 것이다. 이 상태에서 만약 사용자가 커밋하게 되면 현재 상태가 그대로 유지되고, 롤백하게 되면 언두 영역의 백업된 데이터를 다시 데이터 파일 ( 데이터/인덱스 버퍼 ) 로 복구한다.

언두의 데이터는 크게 두 가지 용도로 사용되는데, 첫 번째 용도가 위에서 언급한 트랜잭션의 롤백 대비용이다. 두 번째 용도는 트랜잭션의 격리 수준을 유지 하면서 높은 동시성을 제공하는 데 사용된다. 트랜잭션의 격리 수준이라는 개념이 있는데, 이는 동시에 여러 트랜잭션이 데이터를 변경하거나 조회할 때, 한 트랜잭션의 작업 내용이 다른 트랜잭션에 어떻게 보여질지를 결정하는 기준이다. 

인서트 버퍼 ( Insert Buffer )

RDBMS에서 레코드가 INSERT 되거나 UPDATE될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트 하는 작업도 필요하다, 그런데 인덱스를 업데이트 하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모하게 된다, 그래서 InnoDB 는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키게 되는데 이때 사용하는 임시 메모리 공간을 인서트 버퍼 ( Insert Buffer )라고 한다.

사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 인서트 버퍼를 사용할 수 없다. 인서트 버퍼에 임시로 저장돼 있는 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해병합되는데, 이 스레드를 인서트 버퍼 머지 스레드( Merge Thread ) 라고 한다. MySQL 5.5 이전 버전까지는 INSERT 작업에 대해서만 이러한 버퍼링이 가능했는데, MySQL 5.5부터는 INSERT난 DELETE로 인해 키를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 될 수 있게 개선됐다. 또 MySQL 5.5 이전 버전에서는 별도의 파라미터 설정 없이 기본적으로 기능 활성화됐지만 MySQL 5.5부터는 innodb_change_buffering 이라는 설정 파라미터가 새로 도입되어 작업의 종류별로 인서트 버퍼를 활성화 할 수 있으며, 인서트 버퍼가 비효율적일 때는 인서트 버퍼를 사용하지 않게 설정할 수 있도록 개선됐다.

리두 ( Redo )로그 및 로그 버퍼

쿼리 문장으로 데이터를 변경하고 커밋하면 DBMS는 데이터의 ACID를 보장하기 위해 즉시 변경된 데이터의 내용을 데이터 파일로 기록해야 한다. 하지만 이러한 데이터 파일의 변경 작업은 순차적으로 많은 데이터를 한꺼번에 변경하는 것이 아니고 랜덤하게 디스크에 기록해야 하기 때문에 디스크를 상당히 바쁘게 만드는 작업이다. 그래서 이러한 부하를 줄이기 위해 대부분의 DBMS에는 변경된 데이터를 버퍼링해 두기 위해 InnoDB 버퍼 풀과 같은 장치가 포함돼 있다. 하지만 이 장치 만으로는 ACID를 보장할 수 없는데 이를 위해 변경된 내용을 순차적으로 디스크에 기록하는 로그파일을 가지고 있다. 더 정확한 명칭은 리두 로그이며, 일반적으로 DBMS에서 로그라 하면 이 두 로그를 지칭하는 경우가 많다.

MySQL 서버 자체가 사용하는 로그 파일은 사람들의 눈으로 확인할 수 있는 내용이 아니라서 편집기로 열어볼 수 없으며, 열어볼 필요도 없다. 리두 로그 덕분에 DBMS 데이터는 버퍼링을 통한 한꺼번에 디스크에 변경된 내용을 처리할 수 있고 그로 인해 상당한 성능 향상을 기대할 수 있게 됐다. 하지만 사용량( 특히 변경 작업 ) 이 매우 많은 DBMS 서버의 경우에는 이 이루 로그의 기록 작업이 큰 문제가 되는데, 이러한 부분을 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링하게 된다. 이러한 리두 로그 버퍼링에 사용되는 공간이 로그 버퍼다.

로그 버퍼의 크기는 일반적으로 1~8MB 수준에서 설정하는 것이 적합한데, 만약 BLOB이나 TEXT와 같이 큰 데이터를 자주 변경하거나 하는 경우에는 더 크게 설정하는 것이 좋다.

주의

ACID는 데이터베이스에서 트랜잭션의 무결성을 보장하기 위해 반드시 필요한 4가지 요소(기능)를 의미한다.

A : Atomic의 첫글자, 트랜잭션은 원자성 작업이어야 함을 의미함

C : Consistent 의 첫글자, 일관성을 의미

I : Isolated 의 첫글자 격리성을 의미

D : Durable의 첫글자 한번 저장된 데이터는 지속적으로 유지돼야 함을 의미한다.

일관성과 격리성은 쉽게 정의하기는 힘들지만 이 두가지 속성은 서로 다른 두 개의 트랜잭션에서 동일 데이터를 조회하고 변경하는 경우에도 상호 간섭이 없어야 한다는 것을 의미한다.

MVCC( Multi Version Concurrency Control )

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며 MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다. InnoDB는 언두 로그를 이용해 이 기능을 구현한다. 여기서 멀티 버전이라 함은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미다 이해를 위해 격리수준 ( Isolation level) 이 READ_COMMITTED인 MySQL 서버에서 InnoDB스토리지 엔진을 사용하는 테이블의 데이터 변경을 어떻게 처리하는지 이해할 필요 가 있다.

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

아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 작업중인 레코드를 조회하면 어디에 있는 데이터를 조회하게 될 것인가?

이 질문에 대한 답은 MySQL 초기화 파라미터에 설정된 격리수준 ( Isolation level )에 따라 다르다. 만약 격리 수준이 READ_UNCOMMITTED인 경우에는 InnoDB 버퍼 풀이나 데이터 파일로 부터 변경되지 않은 데이터를 읽어서 반환한다. 즉, 데이터가 커밋됐든 아니듯 변경된 상태의 데이터를 반환한다. 그렇지 않고 READ_COMMITTED나 그이상의 격리수준( REPEATABLE_READ, SERIALIZABLE) 인 경우에는 아직 커밋되지 않았기 때문에 InnoDB 버퍼 풀이나 데이터 파일에 있는 내용 대신 변경되지 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다. 이러한 과정을 DBMS에서는 MVCC라고 표혀한다. 즉 하나의 레코드에 대해 2개의 버전이 유지되고 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지느 ㄴ구조다. 여기서는 한 개의 데이터만 가지고 설명했지만 관리해야하는 예전 버전의 데이터는 무한히 많아질 수 있다. ( 트랜잭션이 길어지면 언두에서관리하는 예전 데이터가 삭제되지 못하고 오랫동안 관리돼야 하며, 자연히 언두 영역이 저장되는 시스템 테이블 스페이스의 광간이 많이 늘어나야 하는 상황이 발생할 수도 있다 )

잠금 없는 일관된 읽기 ( Non-locking consistent read )

InnoDB에서 격리 수준이 SERIALIZABLE이 아닌 READ-UNCOMMITTED나 READ-COMMITTED 그리고 REPEATABLE-READ 수준인 경우 INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다. 특정사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았다 하더라도 이 변경 탤ㄴ잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다. 이를  잠금 없는 일관딘 읽기 라고 표현하며, InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두( Undo ) 로그를 사용한다.

오랜 시간 동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 때가 가끔 있는데, 바로 이러한 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 발생하는 문제다. 따라서 트랜잭션이 시작됐다면 가능한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.