InnoDB

InnoDB 스토리지 엔진 아키텍처

• 레코드 기반 잠금 제공

• 높은 동시성 처리, 안정적, 성능 뛰어남

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

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

• 프라이머리 키 값의 순서대로 디스크에 저장된다.

• 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.

• 쿼리 실행 계획에서 다른 보조 인덱스보다 프라이머리 키가 선택될 확률이 높다.

외래 키 지원

• InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하다.

• 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로
  잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 주의해야 한다.

MVCC(Multi Version Concurrency Control)

• MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 일기를 제공하는 것.

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

• 멀티 버전은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미다.

• update쿼리 후 커밋 혹은 롤백이 되기 전(트랜잭션이 종료되기 전)에 다른 사용자가 작업 중인 레코드를 조회하게 되면, 트랜잭션 격리 수준이 READ_COMMITTED이상의 수준이라면, 트랜잭션이 종료되지 않았기 때문에, InnoDB 버퍼 풀이나 데이터 파일에 있는 내용 대신 변경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다.

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

• 커밋을 하게되면 InnoDB는 더 이상의 변경 작업 없이 지금의 상태를 영구적인 데이터로 만든다.

• 롤백을 하게되면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제한다.

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

잠금 없는 일관된 읽기

• InnoDB는 MVCC기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.

• 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가는ㅇ하다.

• 다른 트랜잭션이 가지고 있는 잠금을 가지고 있을 때 InnoDB는 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용한다.

데드락 감지 스레드

• 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료한다.

• 종료 기준은 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다.

• 언두 레코드를 적게 가졌다는 것은 롤백을 해도 언두 처리를 해야 할 내용이 적다는 것이며, 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문이다.

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

• 데드락 감지 스레드는 잠금 목록을 검사해야 하기 때문에 잠금 상태가 변경되지 않도록 새로운 잠금을 걸고 데드락 스레드를 찾게 된다. 따라서 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 작업을 진행하지 못하고 대기하면서 서비스에 악영향을 미치게 된다.

• 시스템 변수로 끌 수 있다.(innodb_deadlock_detect (ON, OFF) 시스템 변수를 OFF로 설정)

• 자동 데드락 감지 기능을 끄게되면 잠금 타임아웃 시간을 줄인다. (innodb_lock_wait_timeout 시스템 변수)

자동화된 장애 복구

• MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다.

• InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다.

• 자동으로 복구할 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 서버는 종료된다.

• innodb_force_revocery 시스템 변수를 설정해서 MySQL 서버가 시작될 때 InnoDB의 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있다.

InnoDB의 로그 파일이 손상됐다면 6으로 설정하고 MySQL 서버를 기동한다.

InnoDB 테이블의 데이터 파일이 손상됐다면 1로 설정하고 서버를 기동한다.

어떤 부분이 문제인지 알 수 없다면 innodb_force_revocery값을 1부터 6까지 변경하면서 서버를 재시작 해본다. 
innodb_force_revocery값이 커질수록 그만큼 심각한 상황이며, 그만큼 복구 가능성이 적어진다.

1. SRV_FORCE_IGNORE_CORRUPT

   • InnoDB의 테이블스페이스의 데이터나 인덱스 페이지에서 손상된 부분이 발견돼도 무시하고 서버를 시작한다.

   • 이때는 덤프해서 데이터베이스를 다시 구축하는 것이 좋다. (mysqldump)

2. SRC_FORCE_NO_BACKGROUND

   • InnoDB는 쿼리 처리를 위해 여러 종류의 백그라운드 스레드를 동시에 사용한다.

   • 이러한 백그라운드 스레드 가운데 메인 스레드를 시작하지않고 서버를 시작한다.

   • InnoDB는 트랜잭션 롤백을 위해 언두 데이터를 관리하는데, 트랜잭션이 커밋되어 불필요한 언두 데이터는 InnoDB의 메인 스레드에 의해 주기적으로 삭제더ㅣㄴ다.

   • InnoDB의 메인 스레드가 언두 데이터를 삭제하는 과정에서 장애가 발생한다면 이 모드로 복구하면 된다.

3. SRV_FORCE_NO_TRX_UNDO

   • 일반적으로 MySQL 서버는 다시 시작하몀ㄴ서 언두 영역의 데이터를 먼저 데이터 파일에 적용하고 그다음 리두 로그의 내용을 다시 덮어 써서 장애 시점의 데이터 상태를 만들어낸다.

   • 정상적인 서버 시작에서는 커밋되지 않은 트랜잭션은 롤백을 수행하지만, 복구모드가 3으로 설정되면 롤백하지않고 그대로 놔둔다.

   • dump를 이용해 데이터베이스를 다시 구축하는 것이 좋다.

4. SRV_FORCE_NO_IBUF_MERGE

   • InnoDB는 insert, update, delete 등의 데이터 변경으로 인한 인덱스 변경 작업을 상황에 따라 즉시 처리할 수도 있고 인서트 버퍼에 저장해두고 나중에 처리할 수도 있다. 이렇게 인서트 버퍼에 기록된 내용은 언제 데이터 파일에 병합될지 알 수 없다. 서버를 종료해도 병합되지 않을 수 있다.

   • 인서트 버퍼에 손상이 생기면 서버는 시작하지 못한다. 이 때 이 모드로 설정하면 InnoDB가 인서트 버퍼의 내용을 무시하고 강제로 서버가 시작된다.

   • 인서트 버퍼는 실제 데이터가 아니라 인덱스와 관련된 부분이므로 테이블을 덤프한 후 다시 데이터베이스를 구축하면 데이터의 손실 없이 복구할 수 있다.

5. SRV_FORCE_NO_UNDO_LOG_SCAN

   • 서버가 정상적으로 종료되는 시점에 진행 중인 트랜잭션이 있다면 MySQL은 그냥 그 커넥션을 끊어버리고 종료한다.

   • 서버가 다시 시작하면 InnoDB 엔진은 언두 레코드를 이용해 데이터 페이지를 복구하고 리두 로그를 적용해 종료 시점이나 장애 발생 시점의 상태를 재현 해낸다.

   • 언두 로그에 문제 생겼을때 이 모드로 설정하면 InnoDB가 언두 로그를 모두 무시하고 서버를 시작한다.

   • 이 모드로 복구되면 서버가 종료될때 커밋되지 않았던 작업도 모두 커밋된 것처럼 처리되므로 잘못된 데이터가 데이터베이스에 남을 수 있다.

   • 덤프로 데이터베이스 재구축

6. SRV_FORCE_NO_LOG_REDO

   • 리두 로그가 손상되면 서버가 시작되지 못한다.

   • 이 모드로 시작하면 InnoDB엔진은 리두 로그를 무시하고 서버가 시작된다.

   • 커밋됐다 하더라도 리두 로그에만 기록되고 데이터 파일에 기록되지 않은 데이터는 모두 무시된다. 이 때는 기존 리두로그는 모두 삭제 또는 백업 후 서버를 시작하는 것이 좋다.

   • 덤프로 데이터베이스 재구축

InnoDB 버퍼 풀

• InnoDB 핵심

• 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.

• 쓰기 작업을 지연시켜 일괄작업으로 처리할 수 있게 해주는 버퍼 역할도 한다.

• insert, update, delete처럼 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시킨다.

• 버퍼 풀이 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다.

• innodb_buffer_pool_size 시스템 변수로 동적으로 버퍼풀의 크기를 설정할 수 있다.(버퍼 풀 크기 변경은 크리티컬한 변경이므로 가능하면 서버가 한가한 시점을 골라서 진행하는 것이 좋다.)

• 버퍼 풀은 버퍼 풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합을 많이 유발 해왔다.

• 이런 경합을 줄이기 위해 버퍼 풀을 여러개로 쪼개어 관리할 수 있다.

• innodb_beffer_pool_instance 시스템 변수를 이용해 버퍼 풀을 여러개로 분리할 수 있다. 각 버퍼 풀을 버퍼 풀 인스턴스라고 한다. (기본 값 8개 버퍼 풀 크기가 1GB 미만이면 1개, 버퍼 풀 크기가 40GB 이하 수준이라면 기본 값(8개)유지, 메모리가 크다면 5GB당 1개 정도로 설정하는것이 좋다.)

• 버퍼 풀이 여러개의 작은 버퍼 풀로 쪼개지면서 개별 버퍼 풀 전체를 관리하는 잠금(세마포어)자체도 경합이 분산됐다.

버퍼 풀의 구조

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

• 버퍼 풀의 페이지 크기 조각을 관리하기 위해 크게 LRU 리스트, 플러시 리스트, 프리 리스트라는 3가지 자료구조를 사용한다.

• 프리 리스트: 실제 사용자 데이터로 채워지지 않은, 비어 있는 페이지들의 목록 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다.

• LRU 리스트: 이 리스트의 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화 하는 것이다.

InnoDB 엔진에서 데이터를 찾는 과정

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
	- InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색
    - 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색
    - 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU로 승급
    
2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더에 추가

3. 버퍼 풀의 LRU 헤더에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더로 이동
	- Read Ahead와 같이 대량 읽기의 경우 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만 실제 쿼리에서 
    사용되지는 않을 수도 있는데 이때는 MRU로 이동되지 않는다.
    
4. 버퍼 풀에 있는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이(Age)가 부여된다.
오래 사용되지 않으면 늙게되고, 해당 페이지를 버퍼 풀에서 삭제된다.
쿼리에 의해 다시 사용되면 젊어지고 MRU로 승급한다.

5. 필요한 데이터가 자주 접근된다면 해당 페이지의 인덱스 키를 어댑티드 해시 인덱스에 추가한다.

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

• 디스크에서 읽은 후 변경이 일어난 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록된다.

• 데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고, 버퍼 풀의 데이터 페이지에도 반영한다.

버퍼 풀과 리두 로그

• 재사용 불가능한 공간: 활성 리두 로그

• 재사용 가능한 공간: 비활성 리두 로그

• 리두 로그에 기록될 때마다 로그 포지션이 증가한다. 증가한 값을 LSN이라고 한다.

• InnoDB는 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 더티 페이지를 디스크로 동기화 한다.

• 이때 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그의 시작점이 된다.

• 최근 체크포인트의 LSN과 가장 마지막 리두 로그의 LSN의 차이를 '체크포인트 에이지'라고 한다.

• 체크 포인트 에이지는 활성 리두 로그 공간의 크기

• 체크 포인트가 발생하면 체크 포인트 LSN보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 동기화된다.

버퍼 풀 플러시

• 더티 페이지들을 성능에 영향 없이 동기화 하기 위해 2개의 플러시 기능을 백그라운드로 실행한다.

플러시 리스트 플러시

• 주기적으로 플러시 리스트 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화 하는 작업을 수행한다.

• 이때 얼마나 많은 더티 페이지를 한번에 기록하느냐에 따라 성능에 영향을 받지 않을 수 있다.

• 더티 페이지를 동기화하는 스레드를 클리너 스레드라고 한다.(innodb_page_cleaners)

• 기본적으로 전체 버퍼 풀이 가진 페이지의 90%까지 더티 페이지를 가질 수 있다.
  innodb_max_dirty_pages_pct라는 시스템 변수를 이용해 더티 페이지의 비율을 조정할 수 있다.

• 디스크로 기록되는 더티 페이지 개수보다 더 많은 더티 페이지가 발생하면 버퍼 풀에 더티 페이지가 계속 증가하게 되고, 어느 순간 더티 페이지의 비율이 90%를 넘어가면 급작스럽게 더티 페이지를 디스크로 기록해야 한다고 판단한다. 디스크 폭발 현상이 발생할 수 있다.

• 위 문제를 완화하기 위해 innodb_max_dirty_pages_pct_lwm시스템 변수에 설정된 값 이상의 수준의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록하게 하고 있다. 기본값은 10%정도이다.

• innodb_io_capacity, innodb_io_capacity_max 시스템 변수는 각 서버에서 어느 정도의 디스크 읽고 쓰기가 가능한지를 설정하는 값이다.

• 어댑티브 플러시: 리두 로그가 어느 정도 속도로 증가하는지 분석한다. (어느 정도 속도로 더티 페이지가 생성되는지 분석) 리두 로그의 증가 속도를 분석해서 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있록 디스크 쓰기를 실행한다.(innodb_adaptive_flushing (ON, OFF)) 기본값은 10%인데, 이는 활성 리두 공간이 10% 미만이면 작동하지 않다가 10%가 넘어서면 그때부터 어댑티브 플러시 알고리즘이 작동한다. (innodb_adaptive_flushing_lwm)

부드럽게 디스크로 기록하기 위해 제공하는 시스템 변수들
- innodb_page_cleaners: 클리너 스레드 개수 조정 (버퍼 인스턴스와 동일한 값으로 설정)

- innodb_max_dirty_pages_pct_lwm: 일정 수준 이상의 더티 페이지가 발생하면 조금씩 더티 페이지를 
디스크로 기록하게 된다. 기본 값은 10% 더티 페이지 비율이 얼마되지 않는 상태에서 디스크 쓰기가 많이 발생하고 
더티 페이지의 비율이 낮은 상태로 머물고 있는 경우 이 값을 올려주면 디스크 쓰기 횟수를 줄이는 효과를 얻을 수 있다.

- innodb_max_dirty_pages_pct: 더티 페이지 비율 조정 (기본 값을 유지하는게 좋다. 90%)

- innodb_io_capacity: 일반적인 상황에서 디스크가 적절히 처리할 수 있는 수준의 값을 설정한다.

- innodb_io_capacity_max: 디스크가 최대 성능을 발휘할 때 처리할 수 있는 값을 설정한다.

- innodb_flush_neighbors

- innodb_adaptive_flushing: On/Off 기본 값 On

- innodb_adaptive_flushing_lwm: 기본 값 10%, 리두 로그 공간이 해당 값 이상이 되면 
어댑티브 플러시 알고리즘이 작동하게 된다.

LRU 리스트 플러시

• LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만든다. (LRU 리스트 플러시 함수 사용)

• LRU 리스트의 끝부분부터 시작해서 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수 만큼의 페이지들을 스캔한다.

• InnoDB는 이때 스캔하면서 더티 페이지는 디스크에 동기화하고, 클린 페이지는 프리 리스트로 옮긴다.

버퍼 풀 상태 백업 및 복구

• InnoDB 버퍼 풀은 쿼리의 성능에 매우 밀접하게 연결돼 있다.

• 쿼리 요청이 빈번한 서버를 셧다운했다가 다시 시작하고 서비스를 시작하면 쿼리 처리 성능이 떨어진다.

• 버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비돼 있으므로 디스크에서 데이터를 읽지 않아도 쿼리가 처리될 수 있기 때문이다. 이러한 상태를 '워밍업'이라고 한다.

• 5.6버전부터 버퍼 풀 덤프 및 적재 기능이 도입됐다

• innodb_buffer_pool_dump_now (ON, OFF) 시스템 변수를 이용해 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있다. 이 후 서버를 다시 시작하면 innodb_buffer_pool_load_now (ON, OFF) 시스템 변수를 이용해 백업된 버퍼 풀의 상태를 다시 복구할 수 있다.

• 자동 가능

버퍼 풀의 적재 내용 확인

• 5.6버전부터 MySQL 서버의 information_sheme 데이터베이스의 innodb_buffer_page 테이블을 이용해 InnoDB 버퍼 풀의 메모리에 어떤 테이블의 페이지들이 적재돼 있는지 확인할 수 있다. -> 버퍼 풀이 큰 경우 부하 발생

• 8.0부터 innodb_cached_indexes 테이블이 추가됐다. 테이블의 인덱스별로 데이터 페이지가 얼마나 버퍼 풀에 적재돼 있는지 알 수 있다.

Double Write Buffer

• 더티 페이지를 디스크로 플러시할 때 일부만 기록되는 문제가 발생할 수 있다. 이럴 경우 DoubleWriteBuffer의 내용을 데이터 페이지로 복사한다.

• 실제 데이터 파일에 변경 내용을 기록하기 전에 더티 페이지들을 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWriteBuffer에 기록한다. 이 후 더티 페이지를 적당한 위치에 하나씩 랜덤으로 쓰기를 실행한다.

• innodb_doublewrite (ON, OFF)

언두 로그

• 트랜잭션과 격리 수준을 보장하기위해 DML로 변경되기 이전 버전의 데이터를 별도로 백업한다. 이 백업된 데이터를 언두 로그라고 한다.

1. 트랜잭션 보장
   • 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 한다. 이때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구한다.
2. 격리 수준 보장
   • 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.

언두 로그 저장 위치

• 8.0버전부터 언두 로그는 항상 외부의 별도 로그 파일에 기록된다.

체인지 버퍼

• 레코드가 insert되거나 update될 때는 데이터 파일의 변경 뿐만 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요하다.

• 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 많은 자원을 소모하게 된다.

• InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크에서 읽어와 업데이트를 해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상 시키는데 이때 사용하는 임시 메모리 공간을 체인지 버퍼라고 한다.

• 유니크 인덱스는 체인지 버퍼 사용 불가능 ( 사용자에게 결과를 전달 하기전에중복 여부를 체크
  해야해서 )

• 체인지 버퍼에 저장된 인덱스는 백그라운드 스레드에 의해 병합된다 (체인지 버퍼 머지 스레드)

• innodb_change_buffering 작업의 종류별로 활성화 가능

- all: 모든 인덱스 작업(inserts, deletes, purges)을 버퍼링

- none: 비활성화

- inserts: 추가 작업만 버퍼링

- deletes: 삭제 작업만 버퍼링

_ changes: 추가, 삭제 작업만 버퍼링

- purges: 영구적으로 삭제하는 작업만 버퍼링

리두 로그

• 리두 로그는 하드웨어, 소프트웨어 등 여러 문제로MySQL서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.

• 대부분의 데이터 베이스는 읽기 성능을 고려한 자료 구조를 가지고 있다.

• 리두 로그는 쓰기 비용이 낮은 자료구조를 가지고 있다.

• 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 서버가 종료되기 직전의 상태로 복구한다.

• ACID뿐만 아니라 성능도 중요하다.

• MySQL서버가 비정상 종료되는 경우 InnoDB의 데이터 파일은 다음 두 가지 종류의 일관되지 않는 데이터를 가질 수 있다.

1. 커밋됐지만 데이터 파일에 기록되지 않은 데이터

   • 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 된다.

2. 롤백됐지만 데이터 파일에 이미 기록 데이터

   • 언두 로그의 내용을 데이터 파일에 복사한다. 하지만 해당 변경이 커밋인지, 롤백인지, 확인하기 위해 리두 로그가 필요하다.

• 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정하는 것을 권장한다.

• 그렇게 돼야만 서버가 비정상적으로 종료 됐을 때 직전까지의 트랜잭션 커밋 내용을 리두 로그에 기록할 수 있고, 리두 로그를 이용해 복구가 가능하다.

• 하지만 성능 부하가 일어날 수 있음 따라서 리두 로그의 동기화 주기를 설정하는 innodb_flush_log_at_trx_commit 시스템 변수 제공

innodb_flush_log_at_trx_commit = 0 -> 1초에 한번씩 리두 로그를 디스크로 기록하고 동기화 한다.

innodb_flush_log_at_trx_commit = 1 -> 트랜잭션이 커밋될 때마다 디스크로 기록하고 동기화 한다.

innodb_flush_log_at_trx_commit = 2 -> 트랜잭션이 커밋될 때마다 디스크로 기록되지만
동기화는 1초에 한번씩 한다.
MySQL서버가 비정상적으로 종료 됐지만, 운영체제만 정상적으로 작동한다면 해당 트랜잭션의 데이터를 사라지지 않는다.

• innodb_log_file_size 시스템 변수로 리두 로그 파일의 크기를 결정한다.

• innodb_log_files_in_group 시스템 변수로 리두 로그 파일의 갯수를 결정하낟.

• 8.0버전부터 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우 리두 로그를 비활성화 하여 데이터의 적재 시간을 단축시킬 수 있다.

• MySQL 서버는 새롭게 시작될 때 자신이 가진 리두 로그에서 데이터 파일에 기록되지 못한 데이터가 있는지 검사를 한다.

어댑티브 해시 인덱스

• 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다.

• 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티드 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다.

• innodb_adaptive_hash_index (ON, OFF)

• B-Tree를 루트 노드부터 리프 노드까지 찾아가는 비용이 없어진다. CPU가 하는 일은 적고, 쿼리의 성능은 빨라진다. 동시에 더 많은 쿼리 처리 가능

• 해시 인덱스는 '인덱스 키 값'과 인덱스 키 값이 저장된 '데이터 페이지 주소'의 쌍으로 관리된다. 인덱스 키 값은 B-Tree인덱스의 고유 번호와 실제 키 값 조합으로 생성된다.

• 고유 번호가 포함되는 이유는 해시 인덱스는 하나만 존재하기 때문이다.

• 데이터 페이지 주소는 InnoDB 버퍼 풀에 로딩된 페이지의 주소이다.

• 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 버퍼 풀에서 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 삭제된다.

• 예전 버전에서는 어댑티브 해시 인덱스가 하나의 메모리 객체인 이유로 경합이 심했다. 그래서 8.0버전 부터 innodb_adaptive_hash_inddex_parts시스템 변수를 이용하여 파티션 기능을 제공한다. 기본값은 8이다.

어댑티브 해시 인덱스가 성능에 크게 도움이 되지 못하는 경우

• 디스크 읽기가 많은 경우

• 특정 패턴의 쿼리가 많은 경우(join, like)

• 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

어댑티브 해시 인덱스가 성능에 크게 도움이 되는 경우

• 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)

• 동등 조건 검색(동등 비교 in절)이 많은 경우

• 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

계속

• 어댑티브 해시 인덱스는 버퍼 풀 내에서 접근하는 것을 빠르게 만드는 인덱스

• 디스크 읽기가 빈번한 DB에서는 도움이 되지 못한다.

• 어댑티브 해시 인덱스 또한 메모리 사용한다.

• 해시 인덱스가 활성화되면 InnoDB는 그 키 값이 해시 인덱스에 있든 없든 검색해봐야 한다.

• 즉 해시 인덱스 효율이 없는 경우에도 InnoDB는 계속해서 해시 인덱스를 사용해야 한다.

• 데이털을 삭제, 변경(drop, alter)하려고 하면 InnoDB는 테이블이 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거해야 한다. 이로 인해 더 많은 자원을 사용하고, 성능이 저하될 수 있다. (어댑티브 해시 인덱스의 도움을 많이 받을수록 테이블 삭제, 변경은 더 치명적인 작업이 된다.)

1.03 hash searches/s, 2.64 non-hash searches/s

초당 3.67번 검색이 실행됐는데, 1.03번은 어댑티브 해시 인덱스 사용, 2.64번은 사용하지 못했다는 것을 알 수 있다.

(searches는 쿼리의 실행 횟수가 아니라 쿼리가 처리되기 위해 내부적으로 키 값의 검색이 몇번 실행됐느냐를 의미한다.)

28%정도 어댑티브 해시 인덱스를 이용했다. 이 서버의 CPU 사용량이 100%에 근접하다면
어댑티브 해시 인덱스는 효율적이라고 볼 수 있다.

하지만 CPU는 높지 않은데 28%정도의 히트율이라면 어댑티브 해시 인덱스를 비활성화 하는 편이 나을수도 있다.
위의 경우에는 어댑티브 해시 인덱스가 사용중인 메모리 사용량이 높다면 비활성화해서,
InnoDB 버퍼 풀이 더 많은 메모리를 사용할 수 있게 유도하는 것도 좋은 방법이다.