MySQL - 엔진 아키텍쳐2

InnoDB 스토리지 엔진 아키텍처

MySQL의 스토리지 엔진 가운데 가장 많이 사용되는 엔진은 InnoDB 스토리지 엔진이라 할 수 있다. InnoDB의 특징이라 하면 MySQL에서 사용할 수 있는 스토리지 엔진중 거의 유일하게 레코드 기반의 잠금을 제공하기에 높은 동시성 처리가 가능하며 안정적이면서 성능이 뛰어나다.

프라미어리 키(PK)에 의한 클러스터링

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

참고
클러스터링 : 유사한 성격을 가진 개체를 묶어 그룹으로 구성하는 것

즉, PK 값의 순서대로 디스크에 저장된다는 뜻이다. 모든 세컨더리(Secondary) 인덱스는 레코드의 주소 대신 PK 값을 논리적 주소로 사용한다. PK 값이 클러스터링 인덱스이기 때문에 PK를 사용한 레인지 스캔(Range scan)은 상당히 빨리 처리될 수 있다.

결과적으로 쿼리의 실행 계획에 PK는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정(쿼리의 실행 계획에서 다른 보조 인덱스보다 PK가 선택 될 확률이 높음)된다.

참고
오라클 DBMS의 IOT(Index organized table)와 동일한 구조가 InnoDB에서는 일반적인 테이블 구조가 된다.

InnoDB와 달리 MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다. 그래서 MyISAM 테이블에서는 PK와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다. PK는 단지 유니크 제약을 가진 세컨더리 인덱스일 뿐이다. 그리고 MyISAM 테이블의 PK를 포함한 모든 인덱스는 물리적인 레코드의 주소값(ROWID)을 가진다.

외래 키(FK) 지원

FK에 대한 지원은 InnoDB 스토리지 엔진 단계에서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다. FK는 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 자주 있는데, 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다.

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

FK가 복잡하게 얼혀 부모 테이블과 자식 테이블의 관계를 명확히 파악하기 힘들어 순서대로 작업을 못하는 상황이라면 수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업이 실패할 수 있다. 또한 서비스에 문제가 있어서 긴급하게 뭔가 조치를 해야되는 상황에선 더 조급해질 수 있다.

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

그러나, FK 체크를 일시적으로 해제했다고 해서 부모, 자식 테이블 간의 관계가 깨진 상태로 유지됨을 의미하진 않는다. 예를 들어, FK 체크를 해제한 상태에서 FK 관계를 가진 부모 테이블의 레코드를 삭제했다면 반드시 자식 테이블의 레코드도 삭제하여 일관성성을 맞춰준 후 FK 체크 기능을 활성화 해야한다. 또한, FK 체크가 일시적으로 해제되면 FK 관계의 부모 테이블에 대한 작업(ON DELETE/UPDATE CASCADE)도 무시하게 된다.

참고

foreign_key_checks 시스템 변수는 적용 범위를 GLOBAL, SESSION 모두 설정 가능한 변수이다. 그래서 이런 작업을 할 때는 반드시 현재 작업을 실행하는 세션에서만 FK 체크 기능을 멈추게 해야한다. 물론, SESSION 키워드를 명시하지 않으면 자동으로 현재 세션의 설정만 변경한다.

그리고, 작업이 완료되면 반드시 현재 세션을 종료하거나 현재 세션의 FK 체크기능을 다시 활성화해야한다.

MVCC(Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는데 있다. 잠금을 사용하지 않으면 다른 트랜잭션이 가지고 있는 잠금을 기다릴 필요가 없어진다는 큰 장점이 있다. InnoDB는 언두 로그(Undo log)를 이용해 이 기능을 구현한다.

여기서 멀티 버전이라 함은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미이다. 이해를 위해 격리 수준(isolation level)이 READ_COMMITTED인 MySQL 서버에서 InnoDB 스토리지 엔진을 사용하는 테이블의 데이터 변경을 어떻게 처리하는지 그림으로 살펴보자.

우선 다음과 같은 테이블에 한 건의 레코드를 INSERT한 다음 UPDATE해서 발생하는 변경 작업 및 절차를 확인해보자.

mysql> 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)
       );

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

INSERT 문이 실행되면 데이터베이스의 상태는 그림과 같은 상태로 바뀐다.

그림 4.11은 MEMBER 테이블에 UPDATE 문장이 실행될 때의 처리 절차를 그림으로 보여준다.

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

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

참고
ACID : 철자 순서대로 원자성, 일관성, 고립성, 지속성를 의미하며 데이터베이스 트랜잭션이 안전하게 수행된다는 것을 보장하기 위한 성질

그러면 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 다음 같은 쿼리로 작업 중인 레코드를 조회하면 어디에 있는 데이터를 조회하게 될까?

mysql> SELECT * FROM member WHERE m_id=12;

이 질문의 답은 MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준(Isolation level)에 따라 다르다는 것이다. 격리 수준 값과 역할은 다음과 같다.

READ_UNCOMMIT
InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다.
즉, 데이터의 커밋여부에 상관없이 변경된 데이터를 읽어서 반환한다.
READ_COMMIT, REPEATABLE_READ, SERIALIZABLE
아직 커밋이 되지 않은 경우에는 내용이 변경되기 이전의 내용을 보관하고 있는 언두(Undo) 영역의 데이터를 반환한다.
즉, 하나의 레코드(회원번호가 12인 레코드)에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조다. 여기서는 한 개의 데이터만 가지고 설명했지만 관리해야 하는 예전 버전의 데이터는 무한히 많아질 수 있다.

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

지금까지 UPDATE 쿼리가 실행되면 InnoDB 버퍼 풀은 즉시 새로운 데이터로 변경되고 기존 데이터는 언두 영역으로 복사되는 과정을 살펴봤는데, 이 상태에서 COMMIT 명령을 실행하면 InnoDB는 더 이상의 변경 작업 없이 지금의 상태를 영구적인 데이터로 만들어버린다.
하지만, ROLLBACK을 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제해버린다.

COMMIT이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는 것은 아니다. 이 언두 영역을 필요로 하는 트랜잭션이 더 이상 없는 경우 삭제 된다. 즉, ROLLBACK을 언두 영역을 통해 한다.

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

InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다. 잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다.
격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMIT, REPEATABLE_READ 단계인 경우 INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계 없이 항상 잠금을 대기하지 않고 바로 실행된다.

특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았다 하더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다.
InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두 로그(Undo log)를 사용한다.

그림 변경중인(COMMIT 전) 레코드 읽기
오랜 시간 동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 때가 가끔 있는데, 바로 이러한 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문이다. 따라서, 트랜잭션이 시작됬다면 가능한 빨리 ROLLBACK이나 COMMIT을 통해 트랜잭션을 완료하는 것이 좋다.

자동 데드락 감지

시작하기 앞서 데드락이 무엇인지에 대해 간단히 알아보고 가자.

데드락이란 두 개 이상의 프로세스나 스레드가 서로 자원을 얻지 못해서 다음 처리를 하지 못하는 상태 즉, 무한히 다음 자원을 기다리게 되는 상태를 말한다. 주로, 시스템적으로 한정된 자원을 여러 곳에서 사용하려고 할 때 발생한다.

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 목록을 그래프(Wait-for List) 형태로 관리한다.

참고
교착상태 : 두 개 이상의 작업들이 서로 상대방의 작업이 끝나기만을 기다리고 있어 결과적으로 아무것도 못하는 상태

InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랙잭션을 찾아서 그 중 하나를 강제 종료시킨다. 이 때, 어느 트랜잭션을 먼저 강제종료할 것인지를 판단하는 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜 잭션이 높은 우선순위를 가진다.
트랜잭션이 언두 로그 레코드를 적게 가졌다는 이야기는 롤백을 해도 언두 처리에 관한 비용이 더 적다는 것이며, 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문이다. 어찌보면 이는 비용과 부작용을 고려했을 때 당연하다.

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

일반적인 서비스에서는 데드락 감지 스레드가 트랜잭션의 잠금 목록을 검사해서 데드락을 찾아내는 작업은 크게 부담되지 않는다. 하지만 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금 개수가 많아지면 데드락 감지 스레드가 느려진다. 데드락 감지 스레드는 잠금 목록을 검사해야 하기 때문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트(잠금 테이블)에 새로운 잠금을 걸고 데드락 스레드를 찾게 된다.

이렇게 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업을 진행하지 못하고 대기하면서 서비스에 악영향을 미치게 된다. 동시 처리 스레드가 매우 많은 경우 데드락 감지 스레드는 더 많은 CPU 자원을 소모할 수 있다.

이런 문제점을 해결하기 위해 MySQL 서버는 innodb_deadlock_detect 시스템 변수를 제공하며, 이 값을 OFF로 설정하면 데드락 감지 스레드는 더는 작동하지 않게 된다.
데드락 감지 스레드가 작동하지 않으면 InnoDB 스토리지 엔진 내부에서 2개 이상의 트랜잭션이 상대방이 가진 잠금을 요구하는 상황(데드락 상황)이 발생해도 누군가가 중재를 하지 않기 때문에 무한정 대기하게 될 것이다.

하지만 innodb_lock_wait_timeout 시스템 변수를 활성화하면 이런 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환하게 된다. innodb_lock_wait_timeout은 초 단위로 설정할 수 있으며 잠금을 설정한 시간 동안 획득하지 못하면 쿼리는 실패하고 에러를 반환한다.

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

자동화된 장애 복구

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

InnoDB 스토리지 엔진은 매우 견고해서 데이터 파일이 손상되거나 MySQL 서버가 시작되지 못하는 경우는 거의 발생하지 않는다. 하지만 MySQL 서버와 무관하게 디스크나 서버 하드웨어 이슈로 InnoDB 스토리지 엔진이 자동으로 복구를 못하는 경우도 발생할 수 있는데, 일단 한 번 문제가 생기면 복구하기가 쉽지 않다.
InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다. 이 단계에서 자동으로 복구될 수 없는 손상이 있다면 자동 복구를 즉시 멈추고 MySQL 서버는 종료된다.

이 때는 MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 한다. 이 설정값은 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 한다.

• InnoDB의 로그 파일이 손상됬다면 6으로 설정하고 MySQL 서버를 기동한다.
• InnoDB 테이블의 데이터 파일이 손상됬다면 1로 설정하고 MySQL 서버를 기동한다.
• 어떤 부분이 문제인지 알 수 없다면 innodb_force_recovery 설정값을 1부터 6까지 변경하면서 MySQL을 재시작해본다.
• 즉, innodb_force_recovery 값이 커질수록 그만큼 심각한 상황이어서 데이터 손실 가능성이 커지고 복구 가능성은 작아진다.

일단 MySQL 서버가 기동되고 InnoDB 테이블이 인식된다면 mysqldump를 이용해 데이터를 가능한 만큼 백업하고 그 데이터로 MySQL 서버의 DB와 테이블을 다시 생성하는 것이 좋다. InnoDB의 복구를 위해 innodb_force_recovery 옵션에 설정 가능한 값은 1부터 6까지 인데, 각 숫자값에 대한 설명은 직접 검색하여 참고해보자.

참고

innodb_force_recovery가 0이 아닌 복구 모드에서는 SELECT 이외에 INSERT, UPDATE, DELETE 같은 쿼리는 수행할 수 없다.

이 같이 진행했음에도 MySQL 서버가 시작되지 않으면 백업을 이용해 다시 구축하는 방법밖에 없다. 백업이 있다면 마지막 백업으로 데이터베이스를 새로 구축하고, 바이너리 로그를 사용해 최대한 장애 시점까지의 데이터를 복구할 수 있다.
마지막 풀 백업 시점부터 장애 시점까지의 바이너리 로그가 있다면 InnoDB의 복구를 이용하는 것 보다 풀 백업과 바이너리 로그로 복구하는 편이 데이터 손실이 더 적을 수 있다.

백업은 있지만 복제의 바이너리 로그가 없거나 손실됬다면 마지막 백업 시점까지만 복구할 수 있다. 더 자세한 내용은 MySQL 메뉴얼의 innodb_force_recovery 시스템 변수의 내용을 참조한다.

InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할을 같이 한다.

일반적으로 어플리케이션에는 INSERT, UPDATE, DELETE 처럼 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시킨다. 하지만 버퍼 풀이 이러한 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업 횟수를 줄일 수 있다. (이는 버퍼링)

버퍼 풀의 크기 설정
일반적으로 전체 물리 메모리의 80% 정도를 InnoDB의 버퍼 풀로 설정하라는 말이 있는데, 이는 그렇게 단순하게 설정해서 되는 값이 아니다. 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정해야 한다. MySQL 서버 내에서 메모리를 필요로 하는 부분은 크게 없지만 아주 독특한 경우 레코드 버퍼가 상당한 메모리를 사용하기도 한다.

참고
레코드 버퍼 : 각 클레이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간

커넥션이 많고 사용하는 테이블이 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 많이 요구된다.

MySQL 서버가 사용하는 레코드 버퍼 공간은 별도로 설정할 수 없으며, 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블의 개수에 따라 결정된다. 또한, 이 버퍼 공간은 동적으로 해제되기도 하므로 정확히 필요한 체크 공간의 크기를 계산할 수 없다.

다행히 MySQL 5.7 버전부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 됬다. 그래서 가능하면 InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 봐가면서 증가시키는 방법이 최적이다.
일반적으로 회사에서 이미 MySQL 서버를 사용하고 있다면 그 서버의 메모리 설정을 기준으로 InnoDB 버퍼 풀의 크기를 조절하면 된다.

메모리 크기에 따른 대략적인 InnoDB 버퍼 풀의 크기는 다음과 같다.

• 8GB 미만
  50% 정도만 InnoDB 버퍼 풀로 할당
  나머지 메모리 공간은 MySQL 서버와 운영체제, 그리고 다른 프로그램이 사용할 수 있는 공간으로 사용
• 8GB 이상 50GB 미만
  50% 정도로 할당 후 점차 동적으로 늘려가면서 최적점을 찾는다.
• 50GB 이상
  대략 15GB ~ 30GB 정도를 제외한 나머지를 InnoDB 버퍼 풀로 할당
  InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 조절할 수 있으며, 동적으로 버퍼 풀의 크기를 확장할 수 있다. 하지만 버퍼 풀 크기 조절은 영향력이 큰 변경이므로 가능하면 서버가 한가한 시점에 변경을 하는 것이 좋다.

또한, InnoDB 버퍼풀을 더 크게 변경하는 작업은 시스템 영향도가 크지 않지만 크기를 줄이는 작업은 서비스 영향도가 매우 크므로 가능하면 버퍼 풀을 줄이는 작업을 하지 않도록 주의하자.

InnoDB 버퍼 풀은 여러 개로 쪼개어 관리할 수 있다. innodb_buffer_pool_instances 시스템 변수를 이용해 버퍼풀을 여러 개로 분리해서 관리할 수 있는데, 각 버퍼 풀을 버퍼 풀 인스턴스라 한다. 버퍼 풀을 여러 개의 페이지 조각으로 구성된다고 알아두자.

버퍼 풀의 구조
InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 자장한다.

참고
페이지 크기는 innodb_page_size 시스템 변수로 설정이 가능하다.

버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 크기 LRU(Least Recently Used) 리스트와 플러시(Flush) 리스트 그리고 프리(Free) 리스트라는 3개의 자료 구조를 사용한다. 하나씩 살펴보자.

참고
LRU(Least Recently Used) : 가장 오랫동안 사용되지 않은 페이지를 제거한다는 뜻으로, 가장 오래 전 사용된 것이 재사용될 가능성이 제일 적다는 것에 기반하여 페이지를 관리하는 방식을 의미

Free 리스트
Free라는 단어 그대로 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지의 목록이다. 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다.

LRU 리스트

LRU는 아래 그림과 같은 구조를 띄고 있는데, 엄밀하게 LRU와 MRU(Most Recently Used) 리스트가 결합된 형태라고 보면 된다. 아래 그림에서 'Old 서브 리스트' 영역은 LRU에 해당하며 'New 서브리스트' 영역은 MRU 정도로 이해하면 된다.

참고
MRU(Most Recently Used) : 가장 최근에 사용한 페이지를 저장해두는 기능

LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것이다. InnoDB 스토리지 엔진에서 데이터를 찾는 과정은 대략 다음과 같다.

• 필요한 레코드가 저장된 데이터 페이지가 버퍼풀에 있는지 검사
  A. InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색
  B. 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색
  C. 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
• 디스트에 필요한 데이터 페이지를 버퍼 풀에 적재하고 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
• 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동(Read Ahead와 같이 대량 읽기의 경우 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만 실제 쿼리에서 사용되지는 않을 수도 있으며, 이런 경우네는 MRU로 이동되지 않음)
• 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이(Age)가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지에 부여된 나이가 오래되고(Aging) 결국 해당 페이지는 버퍼 풀에서 제거된다. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨진다.
• 필요한 데이터가 자주 접근됬다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가
  즉, 처음 한 번 읽힌 데이터 페이지가 이후 자주 사용된다면 그 데이터 페이지는 InnoDB 버퍼 풀의 MRU 영역에서 계속 살아남게 되고, 반대로 거의 사용되지 않는다면 새롭게 디스크에서 읽히는 데이터 페이지들에 밀려서 LRU의 끝으로 밀려나 결국은 InnoDB 버퍼 풀에서 제거될 것이다.

플러시 리스트
동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리한다.

디스크에서 읽은 상태로 그대로 전혀 변경이 없다면 플러시 리스트에 관리되지 않지만, 일단 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록되어야 한다.

데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다. 그래서 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결된다. 하지만 리두 로그가 디스크로 기록되었다고 해서 데이터 페이지가 디스크로 기록되었다는 것을 항상 보장하진 않는다. 때로는 그 반대의 경우도 발생할 수 있다.

버퍼 풀과 리두 로그
InnoDB의 버퍼 풀과 리두 로그는 매우 밀접한 관계를 맺고 있다. 이미 디스크의 모든 데이터 파일이 버퍼 풀에 적재될 정도의 버퍼 풀 공간이 아니라면 InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 그게 설정하면 할수록 쿼리의 성능이 빨라진다.

InnoDB 버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데, 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐시 성능만 향상시키는 것이다. 그러면 쓰기 버퍼링 기능까지 향상시키려면 어떻게 해야할까?

InnoDB 버퍼풀의 쓰기 버퍼링 기능까지 향상시키려면 InnoDB 버퍼 풀과 리두 로그와의 관계를 먼저 이해해야 한다.

InnoDB의 버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지(Clean Page)와 INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 더티 페이지(Dirty Page)도 가지고 있다.

더티 페이지는 디스크와 메모리(버퍼 풀)의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록되야 하기에 버퍼 풀에 무한정으로 머물 수 없다. InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용한다.

즉, 데이터 변경이 발생하면 리두 로그 파일에 기록되있는 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 덮어 쓰인다. 그래서 InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능 공간과 당상 재사용 가능한 공간을 구분해서 관리해야 한다.
재사용 불가능한 공간을 활성 리두 로그(Active Redo Log)라고 하며 그림에서 화살표를 가진 엔트리들이 활성 리두 로그 공간이다.

버퍼 풀 플러시(Buffer Pool Flush)
예전에는 더티 페이지를 디스크에 동기화 하는 과정(Dirty Page Flush)에서 디스크 쓰기 폭증 현상이 발생했으나 지금은 그러지 않는다. 여기서 InnoDB 스토리지 엔진의 더티 페이지의 디스크 쓰기 동기화와 관련된 시스템 설정을 살펴보자.

특별히 서비스를 운영할 때 성능 이슈가 발생하지 않는다면 굳이 지금부터 설명하는 시스템 변수를 건드릴 필요가 없다.

InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지를들 성능상의 악영향 없이 디스크에 동기화 하기 위해 다음과 같이 2개의 플러시 기능을 백그라운드로 실행한다.

• 플러시 리스트(Flush_list) 플러시
• LRU 리스트(LRU_list) 플러시
  하나씩 살펴보자.

플러시 리스트 플러시
InnoDB 스토리지 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 한다. 이 때 오래된 리두 로그 공간이 지워지려면 반드시 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화되어야 한다.

이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트(Flush_list) 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행한다. 이를 위해 InnoDB 스토리지 엔진은 여러 시스템 변수들을 제공하는데 이는 다음과 같다.

• innodb_page_cleaners
  - InnoDB 스토리지 엔진에서 더티 페이지를 디스크로 동기화하는 스레드인 클리너 스레드(Cleaner Thread)의 개수를 조정
  - 버퍼 풀 인스턴스 개수(innodb_buffer_pool_instances)를 넘을 수 없다. 만약, 이 값보다 크다면 자동으로 이 값으로 값이 설정된다.
• innodb_max_dirty_pages_pct
  - 더티 페이지의 비율을 조정
  - 더티 페이지를 많이 가지고 있을수록 디스크 쓰기 작업을 버퍼링함으로써 여러 번의 디스크 쓰기를 한 번으로 줄이는 효과를 극대화할 수 있기에 디폴트값으로 유지하는 것을 권장
• innodb_max_dirty_pages_pct_lwm
  - 더티 페이지가 innodb_max_dirty_pages_pct 비율에 도달하지 않도록 미리미리 플러시 하도록 하는 최소한의 더티 페이지 비율을 의미
  - 특정 상황에서 이 값을 조금 더 높은 값으로 저장하면 디스크 쓰기 횟수를 줄이는 효과를 얻을 수 있다.
• innodb_io_capacity
  - InnoDB 스토리지 엔진은 이 값을 기준으로 더티 페이지 쓰기를 실행한다.
  - 일반적인 상황에서 사용되거나 사용할 수 있는 I/O의 한계를 의미한다.
• innodb_io_capacity_max
  - 비상 상황에서 사용되거나 사용할 수 있는 I/O의 한계를 의미한다
  - 이로써 비상 상황에서도 I/O를 모두 소비하지 않도록 제한을 둘 수 있다.
• innodb_adaptive_flushing
  - 디폴트는 어댑티브 플러시를 사용
  - 어댑티브 플러시 기능이 활성화되면 innodb_io_capacity, innodb_io_capacity_max를 사용하지 않는 새로운 알고리즘을 사용한다.
  - 이를 활성화하면 일일히 서버의 트래픽을 봐가면서 innodb_io_capacity, innodb_io_capacity_max를 설정하는 번거로운 일을 해결해준다.
• innodb_adaptive_flushing_lwm
  - 전체 리두 로그 공간에서 활성 리두 로그의 공간이 이 값 미만이면 어댑티브 플러시가 작동하지 않고 이 값을 넘어서만 어댑티브 플러시가 작동한다.
  - 디폴트는 10%이다.
• innodb_flush_negihbors
  - 활성화 시 더티 페이지를 디스크에 기록할 때 디스크에서 근접한 페이지들 중 더티 페이지가 있으면 InnoDB 스토리지 엔진이 함께 묶어서 디스크로 기록하게 해주는 기능
  - 디폴트인 비활성으로 유지하는 것을 권장

LRU 리스트 플러시
InnoDB 스토리지 엔진은 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 때 LRU 리스트(LRU_list) 플러시 함수를 사용한다.

이 때, InnoDB 스토리지 엔진은 LRU 리스트의 끝 부분부터 innodb_lru_scan_depth 에 설정된 값 만큼의 페이지들을 스캔한다.

스캔하면 InnoDB 스토리지 엔진은 더티 페이지는 디스크에 동기화하며 클린 페이지는 즉시 프리(Free) 리스트로 옮긴다.

버퍼 풀 상태 백업 및 복구
InnoDB 서버의 버퍼 풀은 쿼리의 성능에 매우 밀접하게 연결되있다. 쿼리 요청이 매우 빈번한 서버를 셧다운 후 다시 시작하여 서비스를 시작하면 쿼리 처리 성능이 평상시보다 1/10도 안되는 경우가 대부분이다. 버퍼 풀에 아무것도 없으면 데이터를 읽거나 쓸 때 디스크에 접근해야 하기 때문이다.

디스크의 데이터가 버퍼 풀에 적재되있는 상태를 워밍업(Warming Up)이라 하는데, 버퍼 풀이 잘 워밍업 된 상태에서는 워밍업이 안 된 상태에 비해 성능이 몇 십배 우수하다.

여러가지 불가피한 이유들 때문에 MySQL 서버를 재시작해야 하는 경우 셧다운 전에 다음과 같이 innodb_buffer_pool_dump_now 시스템 변수를 이용해 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있다.
그리고 서버를 재시작시 innodb_buffer_pool_load_now 시스템 변수를 이용해 백업된 버퍼 풀의 상태를 다시 복구할 수 있다.

<MySQL 서버 셧다운 전에 버퍼 풀의 상태 백업>
mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
<MySQL 서버 재시작 후, 백업된 버퍼 풀의 상태 복구>
mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;

실제 백업 데이터는 데이터 디렉토리에 ib_buffer_pool라는 이름으로 저장이 되는데, 파일의 크기를 살펴보면 버퍼 풀의 크기에 비해 상대적으로 매우 작은 것을 확인할 수 있다. 그 이유는 InnoDB 스토리지 엔진이 버퍼 풀의 LRU 리스트에서 적재된 데이터 페이지의 메타 정보만을 저장하기 때문이다.

이 때문에, 백업을 하는데는 시간이 상대적으로 적게 소모되지만 백업된 데이터를 불러오는데는 오랜 시간이 걸릴 수 있다.

만약, 버퍼 풀 적재 작업이 너무 오랜 시간걸려 중간에 멈추고자 한다면 어떻게 해야할까? innodb_buffer_pool_loat_abort 시스템 변수를 이용하면 된다. 버퍼 풀을 다시 복구하는 작업은 상당히 많은 디스크 읽기를 필요로 하기 때문에 복구가 진행 중인 상황에서 서비스를 재개하는 것은 좋지 않다. 따라서, 복구 중 불가피하게 서비스를 재개해야 하는 경우 다음과 같이 이를 활용할 수 있다.

SET GLOBAL innodb_buffer_pool_loat_abort`=ON;

지금까지 수동으로 InnoDB 버퍼 풀의 백업과 복구를 살펴봤는데 사실 이 작업은 InnoDB 스토리지 엔진이 자동으로 해주기 때문에 수동으로 할 필요는 없다.
만약, 자동화를 하려면 innodb_buffer_pool_dump_at_shutdown과 innodb_buffer_pool_load_at_startup 설정을 MySQL 서버의 설정 파일에 넣어두면 된다.

버퍼 풀의 적재 내용 확인
information_schema 데이터베이스의 innodb_cached_index 테이블을 조회하면 테이블의 인덱스별 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재되있는지 확인할 수 있다.

하지만, 아직 MySQL 서버는 개별 인덱스별 전체 페이지 개수가 몇 개인지는 사용자에게 알려주지 않기에 information_schema 테이블을 이용해도 인덱스별 페이지가 InnoDB 버퍼 풀에 적재된 비율을 확인할 수는 없다.