InnoDB 스토리지 엔진의 특성에 대해 자세히 살펴보자.
InnoDB의 모든 테이블은 PK를 기준으로 클러스터링되어 저장된다. 클러스터링 된다는 것은 물리적으로 비슷한 PK의 값들을 묶어 함께 저장한다는 의미이다. 따라서, PK를 통한 레인지 스캔은 빠르게 처리된다.
세컨더리 인덱스들은 직접 주소 값을 사용하지 않고 PK값을 논리적인 주소로 사용하여 동작한다.
쿼리를 수행할 때 다른 인덱스보다 PK 클러스터링 인덱스의 비중이 높아 선택될 확률이 높다. (세컨더리 인덱스보다 더 빠르게 조회가 가능하므로)
외래 키는 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM 이나 MEMORY 엔진 등에서는 사용할 수 없다.
외래 키는 부모 테이블과 자식 테이블에 모두 해당하는 칼럼에 대한 인덱스를 생성해야 하며, 변경 시 데이터를 체크해야하므로 lock 이 여러 테이블에 전파된다. 따라서 데드락에 주의해야 한다. 운영 환경에서는 이 이유로 FK 를 설정하지 않기도 한다.
외래 키가 복잡하게 설정되어 급하게 데이터 변경이나 스키마 변경이 필요할 때 변경 작업이 어려워 질 수 있다. 이런 경우에는 foreign_key_checks 시스템 변수를 OFF로 설정하면 일시적으로 외래 키에 대한 검사를 하지 않도록 할 수 있다. (시스템 변수를 SESSION 범위로 설정해서 변경 작업에 대해서만 외래 키 검사를 멈추도록 하는 것이 좋다.)
일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS에서 제공하는 기능이다. 이 기능의 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다.
MVCC의 의미는 여러 버전을 통해 동시성을 관리한다는 것인데, 하나의 레코드에 대해 언두 로그를 이용해 여러 개의 버전을 관리한다.
한 트랜잭션에서 데이터를 변경할 경우, 데이터 변경 사항은 버퍼 풀에 먼저 반영되며 디스크 쓰기는 백그라운드 쓰기 쓰레드에 의해 쓰여진다. 변경 사항에 대해서는 언두 로그에 기록한다. 이후 트랜잭션이 커밋되기 전에 READ_COMMITTED 격리 수준을 사용하는 다른 클라이언트가 해당 데이터를 조회하게 되면, 언두 로그를 보고 변경 이전의 데이터를 가져오게 된다. READ_UNCOMMITTED 격리 수준을 사용하는 클라이언트는 버퍼 풀에 있는 데이터가 dirty 상태이든 상관없이 가져와 사용하게 된다. 언두 로그와 버퍼 풀 사이에 하나의 레코드에 대해 다른 버전의 값이 존재하므로 MVCC 기능이라고 불린다.
MVCC 기능에 의해 트랜잭션에서 변경된 레코드여도 lock 없이 언두 로그를 통해 읽기 작업을 수행할 수 있었다.
격리 수준이 SERIALIZABLE 이 아닌 이상 언두 로그를 통해 잠금 없이 읽기 작업을 수행하여 변경 트랜잭션이 수행중이여도 빠른 조회가 가능하다.
오랜 시간 트랜잭션이 활성화되어 있을 경우 언두 로그를 오래 유지하여 MySQL 서버 성능이 안좋아 질 수 있다. 따라서 트랜잭션은 가능한 한 빨리 롤백이나 커밋 시켜주는 것이 좋다.
InnoDB는 데드락을 체크하기 위해 잠금 대기 목록 그래프(Wait-for List)를 관리하며, 주기적으로 데드락 감지 쓰레드가 이 그래프를 검사하여 데드락이 발생할 경우 트랜잭션을 강제 종료시킨다. 여러 트랜잭션 중 강제종료할 트랜잭션을 선택하는 기준은 언두 로그가 가장 작은 트랜잭션을 롤백시켜 언두 처리를 적게 하는 것이다.
데드락 감지 기능은 InnoDB에서 제공하는 것이므로 MySQL 엔진에서 LOCK TABLES 명령으로 테이블에 락을 거는 경우에는 제대로 동작하지 않을 수 있다. InnoDB가 테이블 락도 감지할 수 있도록 하려면 innodb_table_locks 시스템 변수를 활성화하면 된다.
동시에 많은 쓰레드를 처리하면 잠금 테이블로 존재하는 Wait-for List가 많아져 데드락 감지를 수행하는 데 시간이 오래걸리고, 이 작업은 잠금 테이블을 확인하는 동안 데이터 변경이 없도록 잠금 테이블 전체에 락을 잡게 되어 다른 쿼리도 이 작업 수행동안 작업을 수행하지 못하게 되는 문제가 발생한다. 이런 문제를 해결하기 위해 innodb_deadlock_detect 시스템 변수를 OFF로 설정해서 데드락 감지 기능을 사용하지 않을 수 있다. 사용하지 않을 경우 innodb_lock_wait_timout 시스템 변수 값을 낮게 잡아 데드락이 발생하면 더 빨리 종료할 수 있도록 설정할 수 있다.
InnoDB 엔진은 데이터보호를 위한 여러 매커니즘이 존재한다. 이러한 매커니즘들은 MySQL 서버가 시작될 때 자동으로 적용되어 완료되지 못한 트랜잭션이나, 디스크에 일부만 기록된 데이터 등에 대한 복구 작업이 실행된다.
서버 하드웨어 문제가 발생한 경우 자동 복구로는 해결할 수 없는 경우도 존재한다. 이런 경우에는 innodb_force_recovery 시스템 변수를 1에서 6까지 값으로 실행시켜 InnoDB 엔진이 데이터 파일이나 로그 파일에 대한 검사를 선별적으로 수행할 수 있게 해야한다. 1에서 6으로 갈 수록 데이터 손실 위험이 크고 복구 가능성이 낮다.
복구 모드로 MySQL 서버가 동작한다면 mysqldump 를 이용해 데이터를 백업하고 다시 DB와 테이블을 생성하는 것이 좋다.
InnoDB의 버퍼 풀은 가장 핵심적인 부분으로 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간이다.
버퍼 풀의 크기를 정확히 정할 수는 없다. 다른 프로그램, 운영체제, 레코드 버퍼(레코드를 읽고 쓸 때 버퍼로 사용하는 공간) 등을 고려해 50% 에서 시작해 서서히 올리면서 성능을 테스트해보고 최적점을 찾아야 한다.
innodb_buffer_pool_size 시스템 변수를 설정하여 버퍼 풀의 크기를 조정할 수 있다.
InnoDB 버퍼 풀은 전통적으로 버퍼 풀 전체를 관리하는 lock 으로 동작하여 잠금 경합을 많이 일으켰는데, 여러 개의 버퍼 풀로 쪼개어 사용할 수 있도록 개선되었다. innodb_buffer_pool_instances 시스템 변수로 버퍼 풀의 개수를 관리할 수 있으며, 사용할 수 있는 메모리가 40GB 이하라면 기본값인 8을 유지하고 그 이상이라면 버퍼 풀 한 개당 5GB 정도가 되게 버퍼 풀의 개수를 설정해줄 수 있다.
버퍼 풀은 메모리 공간을 페이지 크기(innodb_page_size 시스템 변수)로 쪼개어 특정 데이터가 필요할 때 페이지 크기만큼 읽어와 메모리에 저장하는 식으로 동작한다.
버퍼 풀에 있는 페이지들을 관리하기 위해 LRU 리스트, Flush 리스트, Free 리스트 라는 자료 구조들을 사용한다.
Free 리스트는 버퍼 풀에서 실사용자 데이터로 채워지지 않은 부분을 가리키며 새롭게 디스크에서 페이지를 읽어올 때 사용된다.
LRU(Least Recently Used) 리스트는 디스크에서 읽어온 페이지를 메모리 상에서 최대한 오래 유지해서 디스크 I/O를 줄이는 데 목적이 있다. 엄밀하게는 MRU 리스트와 LRU 리스트가 합쳐진 형태이며, Old 영역은 LRU, New 영역은 MRU 리스트로 관리한다. MRU 헤더 - MRU 테일 - LRU 헤더 - LRU 테일 형태로 관리하며, 새로 들어온 페이지는 LRU 헤더에 들어오고, 나갈 페이지는 LRU 테일에 있다. 들어온 페이지를 읽을 경우 MRU 헤더 방향으로 페이지 포인터가 이동한다.
Flush 리스트는 버퍼 풀에서 데이터 변경이 일어난 더티 페이지들을 관리하는 데 사용된다.
데이터 읽기 동작 과정
필요한 레코드가 있는 페이지가 버퍼 풀에 있는지 검사
a. InnoDB 어댑티브 해시 인덱스로 탐색
b. 해당 테이블의 B-Tree 인덱스를 통해 버퍼 풀에서 페이지 탐색
c. 해당하는 데이터 페이지가 버퍼 풀 내에 있었다면 해당 페이지 포인터를 MRU 헤더 방향으로 이동
디스크에서 가져온 페이지를 버퍼 풀에 적재하고 적재된 페이지의 포인터는 LRU 헤더에 추가
가져온 페이지에서 실제로 레코드가 읽히면 MRU 헤더로 이동 (페이지만 가져오고 레코드를 읽지 않는 경우도 많기 때문에)
시간이 지남에 따라 데이터 페이지의 Age 를 갱신하는 Aging 작업이 수행되며 오래된 페이지는 LRU 테일에서 제거됨.
같은 데이터가 여러 번 접근됐다면, 어댑티브 해시 인덱스에 추가
버퍼 풀에는 데이터 변경이 일어난 데이터를 갖고 있는 더티 페이지와 디스크에서 읽어온 상태를 유지하는 클린 페이지가 존재한다.
더티 페이지는 버퍼 풀에서 데이터 변경이 일어남과 동시에 리두 로그에 변경사항이 기록된다.
리두 로그는 하나 이상의 파일을 순환 고리처럼 연결하여 기록한다. 따라서 예전에 기록했던 리두 로그 파일이 로그 파일 고리를 한바퀴 돌고 덮어씌워질 수 있다. 이런 이유로 리두 로그는 일정시간만 보존되며 따라서 일정시간마다 리두 로그와 버퍼 풀의 더티 페이지를 디스크에 동기화시키는 작업을 수행해야 한다. InnoDB 는 일정시간마다 체크포인트 이벤트를 발생시켜 디스크 동기화가 일어날 수 있도록 한다.
디스크에 동기화되지 않고 버퍼 풀에서 데이터 변경이 관리되고 있는 변경 사항에 대한 리두 로그들을 활성 리두 로그라고 한다.
로그 파일 공간은 순환되어 재사용되지만 로그들은 지속적으로 증가하는 LSN이라는 시퀀스 넘버를 갖고 있다. 마지막으로 체크포인트 이벤트가 발생한 LSN과 마지막으로 쓰려진 리두 로그의 LSN 간의 차이를 체크포인트 에이지라 하며 활성 리두 로그 공간을 의미한다.
버퍼 풀에 비해 리두 로그 공간이 클 경우 평소에는 잘 동작하다가 어느 순간 활성 리두 로그 공간이 커져 한 번에 디스크 쓰기가 대량으로 일어날 수 있다. 리두 로그 공간에 비해 버퍼 풀이 클 경우 더티 페이지가 얼마 발생하지 않아도 동기화 작업이 자주 발생하여 쓰기 버퍼링을 통한 성능 최적화가 거의 되지 않을 수 있다. 추천하는 설정은 100GB 이하의 MySQL 서버는 리두 로그 공간을 5~10GB 수준으로 설정하고 필요할 때마다 조금씩 늘려가는 것이 좋다.
버퍼 풀의 더티 페이지들을 디스크로 플러시 하는 작업은 한 번에 일어날 경우 갑자기 디스크 I/O가 폭증하여 사용자 쿼리 수행 속도에 영향이 갈 수 있다. 이러한 작업을 성능 영향 없이 처리하기 위해 InnoDB 엔진은 플러시 리스트 플러시와 LRU 리스트 플러시 작업 기능 두 가지를 백그라운드로 실행한다.
플러시 리스트 플러시
InnoDB 엔진은 주기적으로 플러시 리스트에 있는 더티 페이지들을 플러시하는데, 이러한 작업을 수행하는 쓰레드를 클리너 쓰레드라고 부른다. 클리너 쓰레드의 개수는 innodb_page_cleaners 설정값으로 변경할 수 있는데, 하나의 버퍼 풀에 하나의 클리너 쓰레드를 할당하는 것이 좋다.
InnoDB 버퍼 풀은 기본적으로 90%까지 더티 페이지로 채워질 수 있고, 이 값을 넘어가면 강제로 디스크와 동기화가 발생한다. innodb_max_dirty_pages_pct 설정값을 통해 변경할 수 있지만 내려 잡을 경우 쓰기 버퍼링의 효과를 많이 볼 수 없어 기본값을 유지하는 것이 좋다.
기본적으로 InnoDB는 innodb_io_capacity 값에 따라 더티 페이지 쓰기를 수행한다. 하지만 더티 페이지 쓰기 작업보다 더티 페이지가 생성되는 양이 많다면, 더티 페이지가 90%가 넘어가 한 번에 디스크 동기화 작업이 발생한다. 이 경우 디스크 I/O가 폭증해 성능이 일시적으로 안좋아지게 된다. 이런 현상을 방지하기 위해서 일정 수준의 더티 페이지가 모이면 조금씩 디스크와 동기화하도록 하는 innodb_max_dirty_pages_lwm 설정값을 잡아줄 수 있다. 기본값은 10% 수준인데, 너무 자주 디스크 쓰기가 발생한다면 조금 올려 잡아주는 것이 좋다.
innodb_io_capacity, innodb_io_capacity_max 값은 데이터베이스 서버에서 어느 정도의 디스크 I/O 를 처리할 수 있는지에 대한 값이다. max 는 최대 성능 기준이다. 이 값을 설정하면 InnoDB 엔진이 처리 성능을 보고 적절한 양의 쓰기 작업을 처리한다. 그러나 이 값은 버퍼 풀의 디스크 쓰기 작업에만 해당하는 것이기에 디스크가 1000 IOPS 를 처리한다고 해서 1000 으로 설정하면 안된다.
innodb_io_capacity, innodb_io_capacity_max 값은 디스크 I/O 를 모니터링하며 적절한 값을 설정해주어야 하는데, 일일이 모니터링하는 것은 어렵다. 그래서 InnoDB 에서는 어댑티브 플러시 기능을 제공하며, innodb_adaptive_flushing 설정값을 활성화해 사용할 수 있다. 어댑티브 플러시 기능은 버퍼 풀에서의 더티 페이지 비율을 보는 것이 아니라 활성 리두 공간이 전체 리두 공간에서 차지하는 비율을 보고 플러시를 수행하며, 이 비율은 innodb_adaptive_flushing_lwm 값으로 설정할 수 있다.
innodb_flush_neighbors 설정값은 더티 페이지 쓰기 수행 시 인접한 더티 페이지를 한 번에 써서 디스크 I/O 를 줄이기 위한 방식으로 HDD 를 사용하는 경우 1이나 2값을 설정해주는 것이 좋다. 하지만 SSD 환경에서는 기본값인 비활성 모드를 유지하는 것이 좋다.
LRU 리스트 플러시
LRU 리스트에서 가장 오래 접근하지 않은 페이지들을 플러시한다.
클린 페이지를 플러시할 경우 바로 Free 리스트에 추가되며, 더티 페이지는 디스크에 동기화하고 Free 리스트에 추가된다.
버퍼 풀 인스턴스 별로 innodb_lru_scan_depth 설정값 만큼 스캔하며 LRU 리스트에 빈공간을 만들어준다.
쿼리에서 사용하는 데이터가 버퍼 풀에 있는 경우와 디스크를 스캔해야 하는 경우 수십, 수백 배의 처리 성능에 차이가 발생한다. 그래서 운영중인 MySQL 서버를 셧다운했다가 다시 킬 경우 버퍼 풀이 비워져 있어 성능이 잘 나오지 않는다. 이런 경우에는 MySQL 5.5 버전에서는 서비스를 운영하기 전에 강제 워밍업을 시켜 테이블과 인덱스를 한 번 풀스캔 하고 서비스를 시작하는 방식을 사용했었다.
MySQL 5.6 버전부터는 버퍼 풀 덤프 및 적재 기능이 도입되었다. 서버를 끄기 전에 innodb_buffer_pool_dump_now 시스템 변수를 ON으로 설정해서 버퍼 풀의 백업 데이터를 담은 ib_buffer_pool 이름의 파일을 생성하고, 서버 재시작 후 innodb_buffer_pool_load_now 시스템 변수를 ON으로 설정해서 백업 파일로부터 버퍼 풀을 복구할 수 있도록 하는 기능이다.
ib_buffer_pool 파일은 버퍼 풀의 LRU 리스트에서 적재된 페이지의 메타 정보만 담고 있어 몇십 MB로 크기가 작다. 하지만 실제 버퍼 풀은 GB단위로 실제 디스크 페이지를 읽어와 적재해야 하기 때문에 시간이 오래 걸리게 된다. 복구 작업 중에 복구를 멈추고 싶다거나, 복구 작업 상태를 확인하고 싶을 경우에도 시스템 변수들을 통해서 가능하다.
자동으로 서버가 꺼지기 전에 버퍼 풀 덤프 파일을 생성하고, 복구 작업을 하도록 하고 싶다면, innodb_buffer_pool_dump_at_shutdown, innodb_buffer_pool_load_at_startup 시스템 변수를 사용할 수 있다.
MySQL 5.6 버전부터 information_schema 데이터베이스의 innodb_buffer_page 테이블을 이용해 버퍼 풀의 메모리에 어떤 페이지가 적재되어 있는지 확인할 수 있었다. 하지만, 버퍼 풀이 클 경우 테이블 조회에 큰 부하가 걸려 서비스 쿼리 성능에 영향을 미쳤다.
MySQL 8.0 버전부터는 이러한 문제점을 해결하기 위해 information_schema에 innodb_cached_indexes 테이블이 추가되었다. 이를 통해 특정 인덱스가 얼마나 버퍼 풀에 페이지를 적재하고 있는지 큰 부하 없이 확인할 수 있다.
리두 로그는 데이터 변경 시에 같이 생성된다고 했는데, 공간 낭비를 막기 위해 전체 데이터 변경을 각각 기록하는 것이 아닌 페이지 단위의 변경 내용만 기록하고 있다. 따라서, 더티 페이지를 플러시하는 중에 페이지의 일부만 write 된다면(이런 상황을 Partial-page, Toen-page 라고 함) 그 페이지의 변경 사항은 복구가 불가능 할 수 있다. (리두 로그만으로는 더티 페이지의 어디까지 디스크에 반영되었는지 알 수가 없으므로)
이 문제를 방지하기 위해 InnoDB 는 double write 기능을 지원한다. 더티 페이지를 플러시하기 전에 플러시할 더티 페이지들을 먼저 묶어서 DoubleWrite 버퍼에 저장하고, 이후에 더티 페이지들을 하나씩 쓰기 시작하는 방식으로 총 2번의 디스크 쓰기를 통해 안전성을 강화하지만, 그만큼 추가적인 디스크 I/O가 발생해 성능은 낮아질 수 있다.
언두 로그는 앞에서 살펴봤듯 데이터 변경이 일어날 경우 변경되기 이전 버전의 데이터를 저장하여 트랜잭션 롤백 작업이나 격리 수준 읽기를 가능하도록 한다. 그러나, 언두 로그는 관리 비용도 많이 들고 문제점도 가지고 있다. 이에 대해 살펴보자.
언두 로그 모니터링
트랜잭션 내에서 DML 을 수행할 경우 실제로 버퍼와 데이터 파일에는 새로운 데이터로 변경된다. 변경되기 이전 데이터는 언두 로그에 기록한다. 커밋할 경우 현상태가 그대로 유지되고, 롤백할 경우 언두 로그의 백업된 데이터를 다시 데이터 파일로 복구한다.
트랜잭션이 하나라도 실행중일 경우 언두 로그는 삭제할 수 없다. 스냅샷 방식을 사용하는 트랜잭션이 어느 데이터를 조회할 지 알 수 없기 때문이다. 따라서 장기간 트랜잭션을 시작하고 닫지 않을 경우, 디스크 언두 로그 저장 공간이 많이 사용되게 되며, 다른 트랜잭션이 변경된 데이터를 읽어올 때 언두 로그의 많은 변경이력들을 탐색해야하기 때문에 쿼리 성능도 떨어지게 된다.
MySQL 8.0 버전부터는 레코드 단위로 트랜잭션이 어느 레코드를 참조하는지 효율적으로 검사할 수 있는 기능이 추가되었고, 따라서 참조하지 않는 레코드의 언두 로그까지 모두 갖고 있는 문제를 해결했다.
하지만 참조하고 있는 레코드의 언두 로그는 트랜잭션 종료까지 여전히 보관하므로 장시간 트랜잭션을 닫지 않는 것은 여전히 좋지 않다.
언두 테이블스페이스 관리
MySQL 5.6 버전 이전에는 언두 로그는 모두 시스템 테이블스페이스에 저장되었다. 시스템 테이블스페이스는 서버를 초기화할때 생성되기 때문에 확장이 어려웠다.
MySQL 5.6 버전부터는 시스템 변수에 따라 언두 로그를 별도의 언두 로그 파일에 저장할 수 있도록 개선되었다. MySQL 8.0.14 버전부터는 시스템 변수는 더이상 사용되지 않고, 항상 시스템 스페이스 외부의 별도 로그 파일에 저장되도록 변경되었다.
언두 테이블스페이스는 1~128 개의 롤백 세그먼트로 구성되며, 롤백 세그먼트는 다시 여러 개의 언두 슬롯으로 구성된다. 기본값을 사용할 경우 언두 로그 공간이 부족해서 테이블스페이스를 변경하는 상황은 트랜잭션 131072개를 동시에 실행해야 하는데 거의 발생하지 않는다.
기존에는 한 번 생성된 언두 로그는 변경이 불가능했지만, MySQL 8.0 버전부터는 언두 테이블스페이스를 CREATE UNDO TABLESPACE 나 DROP UNDO TABLESPACE 같은 명령으로 동적으로 변경할 수 있게 개선되었다.
자동으로 불필요한 언두 로그 공간을 삭제하는 것을 언두 퍼지(Undo Purge)라고 하는데, 주기적으로 퍼지 쓰레드가 불필요한 언두 로그 공간을 검사해 언두 로그들을 삭제하는 역할을 한다. innodb_undo_log_truncate 시스템 변수를 ON으로 설정하면 자동으로 퍼지 쓰레드가 동작하고, OFF 일 경우 퍼지 쓰레드는 동작하지 않는다.
수동으로 불필요한 언두 로그 공간을 삭제하는 방법은 특정 언두 테이블스페이스를 비활성화하는 것이다. 비활성화된 테이블스페이스는 퍼지 쓰레드가 필요없는 부분을 삭제한다. 이후에 다시 활성화하는 방식으로 수동으로 관리가 가능하다. 이 방식은 언두 테이블스페이스가 최소 3개 이상인 경우에만 사용될 수 있다.
데이터가 INSERT, UPDATE 되는 경우에는 해당 테이블에 포함된 인덱스 파일도 변경이 필요한데, 인덱스 파일은 디스크에 랜덤하게 저장되어 있으므로 데이터 변경마다 관련된 모든 인덱스 파일을 변경하는 것은 비용이 크다. 따라서 버퍼 풀에 적재되어 있는 인덱스 파일은 바로 업데이트하고, 버퍼 풀에 없는 인덱스 변경사항은 바로 디스크에 쓰는 것이 아니라 임시 공간에 저장해두고 사용자에게 결과를 반환하는데, 이 임시 공간을 체인지 버퍼라 한다.
유니크 인덱스에 대해서는 같은 값이 없는지 검사를 해야하기 때문에 체인지 버퍼를 사용할 수 없다.
체인지 버퍼에 저장된 인덱스 변경사항들은 백그라운드 쓰레드인 머지 쓰레드에 의해 디스크에 쓰여진다. MySQL 5.5 이전 버전까지는 INSERT 에 의한 인덱스 변경만 버퍼링이 가능했으나, MySQL 8.0 부터는 UPDATE, DELETE 까지 모두 버퍼링이 가능하도록 개선되었다. 체인지 버퍼를 특정 DML에만 사용하거나 사용하고 싶지 않을 경우, innodb_change_buffering 시스템 변수로 변경 가능하다.
체인지 버퍼는 기본적으로 버퍼 풀 메모리의 25% 공간까지 사용할 수 있으며, 이 값은 innodb_change_buffer_max_size 변수로 설정할 수 있다.
리두 로그는 DBMS 의 영속성을 위한 기능이며, 대부분의 DBMS 가 데이터 변경 내용을 로그로 먼저 기록한다.
DBMS 들은 파일 쓰기보다는 읽기에 최적화된 자료구조를 사용하기 때문에 랜덤하게 쓰기가 발생하는 데이터 변경을 바로 디스크에 반영하는 것은 성능상 좋지 않다. 따라서 데이터 쓰기 비용이 낮은 로그를 사용한다.
리두 로그는 트랜잭션 커밋 시에 바로 디스크와 동기화하는 것이 영속성을 위해서 가장 좋은 방법이지만, 성능을 고려해 1초 간격으로 디스크 동기화를 수행한다거나 운영체제의 메모리 버퍼와만 동기화 하는 등의 설정을 할 수 있다.
데이터 변경이 매우 많은 DBMS 서버의 경우에는 리두 로그 기록 작업이 큰 병목이 되는데, 이 부분을 보완하기 위해 ACID 를 지키는 선에서 로그를 버퍼링한다. 리두 로그를 버퍼링 하기 위한 공간을 로그 버퍼라고 한다.
리두 로그 아카이빙
MySQL 엔터프라이즈 백업이나 Xtrabackup 툴은 백업을 수행할 때, 데이터 파일을 복사하는 동안 리두 로그를 계속 추적하면서 새로 추가된 리두 로그 엔트리를 복사하여 일관된 상태를 유지한다. 하지만 데이터 변경이 너무 많아 백업 툴이 리두 로그를 못따라가면 리두 로그는 앞서 순환구조로 파일을 사용한다고 했으므로 백업 툴이 복사하기 전에 덮어 씌워질 수 있다. 이런 문제점을 보완하기 위해 MySQL 8.0 버전부터는 리두 로그 아카이빙 기능을 제공한다.
리두 로그 아카이빙 기능은 User Defined Function 형태로 제공되어 서버에서 실행시킬 수 있으며, 리두 로그를 아카이빙할 별도의 디렉터리를 설정하여 리두 로그를 복제하고 덮어 씌워지지 않고 계속 증가하도록 한다. 백업을 마치면 innodb_redo_log_archive_stop UDF 를 실행하여 아카이빙을 정상적으로 종료 해야한다.
리두 로그 활성화 및 비활성화
MySQL 8.0 버전부터는 리두 로그를 수동으로 활성화, 비활성화 하는 기능이 추가됐다. MySQL 서버를 복구하거나, 대용량 데이터를 한 번에 적재하는 경우 빠른 처리를 위해 일시적으로 리두 로그를 비활성화하는 등의 작업이 가능하다.
작업을 마치면 리두 로그는 항상 활성화 상태를 유지하고 운영되어야 한다. 비활성화 상태로 운영 중 크래시가 발생할 경우 마지막 체크포인트 이후의 데이터 변경은 일관성이 보장되지 않는다.
일반적인 데이터 인덱스는 B-Tree 인덱스를 사용하여 특정 데이터를 읽어올 때, 루트 노드부터 브랜치 노드, 리프 노드를 거쳐 데이터를 가져오게 된다. 하지만 이 작업도 몇 천개의 쓰레드가 동시에 실행된다면 느리게 동작할 수 있다.
어댑티브 해시 인덱스는 자주 요청하는 데이터에 대해 InnoDB 엔진이 내부에서 자동으로 생성하는 인덱스이며, B-Tree 인덱스 탐색 시간을 줄이기 위해서 도입되었다. 해시 인덱스를 사용하기 때문에 O(1) 으로 원하는 페이지에 접근하여 B-Tree 인덱스보다 훨씬 빠르게 동작한다.
어댑티브 해시 인덱스의 키값은 B-Tree 인덱스의 id와 해당 인덱스가 관리하는 키 값의 쌍으로 구성된다. 인덱스별, 테이블별로 존재하지 않고 서버에 하나만 존재하기 때문이다. 키값에 대응되는 값은 버퍼 풀에서의 데이터 페이지 주소로, 디스크에 있는 페이지까지는 관리하지 않고 해당 페이지가 플러시되면 어댑티브 해시 인덱스에서도 같이 사라진다.
기존에는 어댑티브 해시 인덱스가 하나의 메모리 객체인 이유로 세마포어 경합이 심했다. 이를 보완하기 위해 여러 개의 파티션으로 동작하는 기능이 추가되었다. 기본값은 파티션 8개이며, 어댑티브 해시 인덱스가 성능에 도움이 많이 된다면 파티션 수를 늘려 잡아주는 것도 성능에 도움이 된다.
어댑티브 해시 인덱스가 장점만 있는 것은 아니다. 당연히 해시 테이블을 사용하므로 그만큼의 메모리 공간을 사용해야 하며, 버퍼 풀에서 데이터 읽기보다 디스크에서 페이지 읽기가 자주 일어나는 환경에서는 사용도 못하는 어댑티브 해시 인덱스 테이블을 추가로 변경만 해주어야 해서 오히려 성능이 나오지 않는다. SHOW ENGINE INNODB STATUS 명령어를 통해 hash search와 non-hash search 의 비율을 모니터링해서 적절한 설정을 잡아줄 수 있다.