본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.
4.2.3 Double Write Buffer
InnoDB 스토리지 엔진의 리두 로그는 공간 낭비를 막기 위해 페이지의 변경된 내용만 기록한다. 이로 인해 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록하는 문제가 발생하면 그 페이지 내용은 복구할 수 없을 수도 있는데, 이러한 현상을 파셜 페이지(Partial-page) 또는 톤 페이지(Torn-page)라고 하며, 하드웨어 오작동이나 시스템의 비정상적인 종료 등으로 발생할 수 있다.
위 같은 문제를 막기 위해 Double-Write 기법을 이용한다. 위 그림처럼 A ~ E까지의 더티 페이지를 디스크로 플러시할 때, InnoDB 스토리지 엔진은 실제 데이터 파일에 변경 내용을 기록하기 전 A~E 까지의 더티 페이지를 우선 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록한 후, 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기를 실행한다.
시스템 테이블스페이스의 DoubleWrite 버퍼 공간에 기록된 변경 내용은 실제 데이터 파일에 A~E 더티 페이지가 정상적으로 기록되는 경우 필요가 없어지며, 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래의 목적으로 사용된다. 쓰기 작업 중 서버가 비정상적으로 종료되었을 때 재시작하게 된다면 InnoDB 스토리지 엔진은 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해 다른 내용을 담고 있는 페이지가 있다면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다. 이는 innodb_doublewrite 시스템 변수로 제어할 수 있다.
4.2.9 언두 로그
InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업하고, 백업된 데이터를 언두 로그(Undo Log)라고 한다.
언두 로그 모니터링
언두 영역은 INSERT, UPDATE, DELETE 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터를 보관하는 곳이다. 만약 사용자의 UPDATE문장으로 데이터를 변경할 때, 트랜잭션이 커밋되기 전에도 실제 데이터 파일의 내용은 변경된다. 이때 트랜잭션이 커밋된다면 현재 상태가 유지되고, 롤백하면 언두 영역의 백업된 데이터를 다시 데이터 파일로 복구한다.
언두 로그는 아래와 같이 사용된다.
- 트랜잭션 보장
트랜잭션이 롤백되는 경우 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구한다. - 격리 수준 보장
특정 커넥션에서 데이터 변경 도중 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경정운 레코드를 읽지 않고 백업해둔 데이터를 읽어서 반환하기도 한다.
대용량의 데이터를 처리하는 트랜잭션 뿐만 아니라 트랜잭션이 오랜 시간 동안 실행될 때도 언두 로그의 양은 급격히 증가할 수 있다. 트랜잭션이 완료됐다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것은 아니다. 위 그림은 B 트랜잭션과 C 트랜잭션은 완료됐지만 가장 먼저 시작된 트랜잭션 A는 완료되지 않은 상태를 나타낸다. 이때 트랜잭션 B와 C는 각각 UPDATE와 DELETE를 실행했으므로 변경 이전의 데이터를 언두 로그에 백업했을 것이다. 하지만 먼저 시작된 A 트랜잭션이 아직 활성 상태이기 때문에 B와 C 트랜잭션 완료 여부와 관계없이 B와 C 트랜잭션이 만들어낸 언두 로그는 삭제되지 않는다.
만약 MySQL 서버에서 사용자가 트랜잭션을 시작한 상태에서 완료하지 않고 하루정도 방치한 경우, InnoDB 스토리지 엔진은 이 트랜잭션이 시작된 시점부터 생성된 언두 로그를 계속 보존하게 되어 하루치 데이터 변경을 모두 저장하고 언두 로그 저장 공간은 계속 증가하게 된다. 이로 인해 빈번하게 변경된 레코드 조회 쿼리가 실행되면 언두 로그의 이력을 필요한 만큼 스캔해야만 필요한 레코드를 찾을 수 있기 때문에 쿼리 성능이 전반적으로 떨어지게 된다.
MySQL 5.7, MySQL 8.0 으로 업그레이드 되면서 언두 로그 공간의 문제는 완전히 해결됐다. MySQL 8.0에서는 언두 로그를 돌아가면서 순차적으로 사용해 디스크 공간을 줄이는 것도 가능하며, MySQL 서버가 필요한 시점에 사용 공간을 자동으로 줄여주기도 한다. 그럼에도 MySQL 서버에서 활성 상태의 트랜잭션 장시간 유지는 성능상 좋지 않으므로 MySQL 서버의 언두 로그 건수를 모니터링하는 것이 좋다.
언두 테이블스페이스 관리
언두 로그가 저장되는 공간을 언두 테이블스페이스(Undo Tablespace)라고 한다. 위 그림은 언두 테이블스페이스가 어떤 형태로 구성되는지 보여준다. 하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯(Undo Slot)을 가진다.
하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수만큼의 언두 슬록스 가진다. 하나의 트랜잭션이 필요로 하는 언두 슬롯의 개수는 트랜잭션이 실행하는 INSERT, UPDATE, DELETE 문장의 특성에 따라 최대 4개까지 언두 슬롯을 사용하게 된다. 일반적으로는 트랜잭션이 임시 테이블을 사용하지 않으므로 하나의 트랜잭션은 대략 2개 정도의 언두 슬롯을 필요로 한다고 가정하면 된다. 그래서 최대 동시 처리 가능한 트랜잭션 개수는 다음 수식으로 예측해볼 수 있다.
최대 동시 트랜잭션 수 =
(InnoDB 페이지 크기 / 16* (롤백 세그먼트 개수) * (언두 테이블스페이스 개수)
MySQL 8.0 버전부터 CREATE UNDO TABLESPACE나 DROP TABLESPACE 같은 명령으로 새로운 언두 테이블스페이스를 동적으로 추가하고 삭제할 수 있게 개선되었다.
언두 테이블스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것을 **Undo tablespace truncate`라고 한다. 언두 테이블스페이스의 불필요한 공간을 잘라내는 방법은 자동과 수동으로, 모두 MySQL 8.0부터 지원된다.
- 자동 모드
트랜잭션이 데이터를 변경하면 이전 버전의 데이터를 언두 로그로 기록하는데, 트랜잭션이 커밋되면 더이상 언두 로그의 복사된 이전 값은 불필요해진다. InnoDB 스토리지 엔진의 퍼지 스레드는 주기적으로 깨어나서 언두 로그 공간에서 불필요해진 언두 로그를 삭제하는 언두 퍼지(Undo Purge)를 실행한다.innodb_undo_log_truncate시스템 변수가 ON으로 설정되면 퍼지 스레드는 주기적으로 언두 로그 파일에서 사용되지 않는 공간을 잘라내고 운영체제로 반납하게 된다. - 수동 모드
innodb_undo_log_truncate시스템 변수가 OFF로 설정하여 언두 로그 파일의 잘라내기가 자동으로 실행되지 않거나 예상보다 자동 모드로 언두 테이블스페이스의 공간 반납이 부진한 경우 언두 테이블스페이스를 비활성화해서 언두 테이블스페이스가 더이상 사용되지 않도록 설정하면 퍼지 스레드는 비활성 상태의 언두 테이블스페이스를 찾아서 공간을 잘라내고 해동 공간을 반납한 뒤 다시 활성화한다. 수동 모드는 언두 테이블스페이스가 최소 3개 이상은 돼야 작동한다.
4.2.10 체인지 버퍼
RDBMS에서 레코드가 INSERT되거나 UPDATE될 때는 데이터 파일을 변경하는 작업뿐 아니라 인덱스를 업데이트 하는 작업이 필요한데, 업데이트 작업은 랜덤하게 디스크를 읽는 작업이 필요해 테이블에 인덱스가 많다면 상당히 많은 자원을 소모하게 된다. 따라서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 디스크로부터 읽어와서 업데이트 해야 하는 경우에는 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키는데, 이때 사용하는 임시 메모리 공간을 체인지 버퍼(Change Buffer)라고 한다.
유니크 인덱스는 체인지 버퍼를 사용할 수 없다, 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드의 체인지 버퍼 머지 스레드(Merge thread)에 의해 병합된다. MySQL 8.0 에서는 INSERT, DELETE, UPDATE로 인해 키를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 될 수 있게 개선되었으며, MySQL 5.5부터 innodb_change_buffering 시스템 변수를 통해 작엽의 종류별로 체인지 버퍼를 활성화하거나 사용하지 않게 설정할 수 있다.
4.2.11 리두 로그 및 로그 버퍼
리두 로그(Redo Log)는 트랜잭션의 4가지 요소인 ACID중에서 D(Durable)에 해당하는 영속성과 가장 밀접하게 연관되어 있다. 리두 로그는 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.
대부분의 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록한다. 거의 모든 DBMS에서 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료 구조를 가지고 있기 때문에 쓰기는 디스크의 랜덤 액세스가 필요하고, 따라서 변경된 데이터를 데이터 파일에 기록하려면 상대적으로 큰 비용이 필요하다. 이로 인한 성능 저하를 막기 위해 데이터베이스 서버는 쓰기 비용이 낮은 리두 로그를 가지고 있으며, 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버 종료 직전 상태로 복구한다. 데이터베이스 서버는 ACID도 중요하지만 성능도 중요하기 때문에 데이터 파일뿐만 아니라 리두 로그를 버퍼링할 수 있는 InnoDB 버퍼 풀이나 리두 로그를 버퍼링 할 수 있는 로그 버퍼와 같은 자료 구조도 가지고 있다.
MySQL 서버가 비정상 종료되는 경우 InnoDB 스토리지 엔진의 데이터 파일은 두 종료의 일관되지 않은 데이터를 가질 수 있다. 첫번째로 커밋됐지만 데이터 파일에 기록되지 않은 데이터로, 이는 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 된다. 두번째는 롤백됐지만 데이터 파일에 이미 기록된 데이터로 이는 리두 로그로는 해결할 수 없고, 변경 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면 된다. 이때 리두로그는 그 변경이 커밋, 롤백, 혹은 트랜잭션 실행 중간 상태를 확인하기 위해 필요하다.
리두 로그 아카이빙
MySQL 8.0 버전부터 InnoDB 스토리지 엔진의 리두 로그를 아카이빙할 수 있는 기능이 추가됐다. MySQL 엔터프라이즈 백업이나 Xtrabackup 툴은 데이터 파일을 복사하는 동안 일관된 상태 유지를 위해 InnoDB 스토리지 엔진의 리두 로그에 쌓인 내용을 계속 추적하면서 새로 추가된 리두 로그 엔드리를 복사한다. 그런데 데이터 변경이 너무 많으면 리두 로그가 매우 빠르게 증가하며 백업 툴이 새로 추가되는 리두 로그 내용을 복사하기도 전에 덮어쓰여 리두 로그 엔트리를 복사할 수 없어 백업은 실패하게 된다. MySQL 8.0의 리두 로그 아카이빙 기능은 데이터 변경이 많아서 리두 로그가 덮어쓰인다고 하더라도 백업이 실패하지 않게 해준다.
리두 로그 활성화 및 비활성화
MySQL 8.0 이전 버전까지는 수동으로 리두 로그를 비활성화할 수 있는 방법이 없었지만 MySQL 8.0 버전부터는 수동으로 리두 로그를 활성화 혹은 비활성화할 수 있게 되었다. 이로 인해 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우 다음과 같이 리두 로그를 비활성화해서 데이터의 적재 시간을 단축시킬 수 있다. 비활성화한 뒤 데이터 적재 작업이 완료되었다면 리두 로그를 다시 활성화하여 데이터의 일관성을 유지해야 한다.
4.2.12 어댑티브 해시 인덱스
일반적인 인덱스는 테이블에 사용자가 생성해둔 B-Tree 등의 알고리즘을 사용하는 인덱스를 의미하지만 어댑티브 해시 인덱스(Adaptive Hash Index)는 사용자가 수동으로 생성하는 인덱스로, innodb_adaptive_hash_index 시스템 변수를 이용해 기능을 활성화 혹은 비활성화할 수 있다.
B-Tree 인덱스에서 값을 찾는 과정은 데이터베이스 서버가 얼마나 많은 일을 하느냐에 따라 속도가 달라질 수 있는데, 어댑티브 해시 인덱스는 이러한 B-Tree 검색시간을 줄여주기 위해 도입된 기능이다. InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고 필요할 때마다 어댑티브 해시 인덱스를 검색해 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있어 B-Tree를 루트 노드부터 리프 노드까지 찾아가는 비용이 없어져 쿼리의 성능은 빠랄지고 컴퓨터는 더 많은 쿼리를 동시에 처리할 수 있게 된다.
해시 인덱스는 B-Tree 인덱스 고유번호(Id)와 B-Tree 인덱스의 실제 키 값의 조합으로 인덱스 키 값과 해당 인덱스 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리된다. B-Tree 인덱스 고유번호가 포함되는 이유는 InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스는 하나만 존재하기 때문이다. 데이터 페이지 주소는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가지는데, 이는 InnoDB 버퍼 풀에 로딩된 페이지 주소를 의미한다. 그래서 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되며 버퍼 풀에서 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지 정보는 사라진다.
어댑티브 해시 인덱스가 성능적인 이점만 가지고 있는 것 같이 보이나,실제 어댑티브 해시 인덱스를 의도적으로 비활성화하는 경우도 많다. 어댑티브 해시 인덱스 사용으로 성능이 향상되는 경우와 도움이 되지 않는 경우는 아래와 같다.
성능 향상에 도움이 되는 경우
- 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
- 동등 조건 검색(동등 비교와 연산자)이 많은 경우
- 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
성능 향상에 도움이 되지 않는 경우
- 디스크 읽기가 많은 경우
- 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
- 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
어댑티브 해시 인덱스는 테이블의 삭제 작업에도 많은 영향을 미친다. 어떤 테이블의 인덱스가 어댑티브 해시 인덱스에 적재되어있을 때 테이블을 삭제 혹은 변경하려고 하면 InnoDB 스토리지 엔진은 테이블이 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거해야 한다. 이로 인해 테이블 삭제 및 스키마 변경 시 상당히 많은 CPU 자원을 사용하고, 데이터베이스 서버의 처리 성능이 느려진다.
4.2.13 InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교
MySQL 5.5 버전부터 InnoDB 스토리지 엔진이 기본 스토리지 엔진으로 채택됐으며 MySQL 8.0부터는 MySQL 서버의 모든 시스템 테이블이 InnoDB 스토리지 엔진으로 교체됐고, 공간 좌표 검색 혹은 전문 검색 기능이 모두 InnoDB 스토리지 엔진을 지원하도록 개선됐다. 이로 인해 MyISAM 스토리지 엔진의 기능은 도태되어 이후 버전에서는 없어질 것으로 예상된다.
