InnoDB 아키텍쳐를 알아보자

요즘 APM을 만드려고 하다보니 오픈소스의 아키텍쳐에 대한 관심이 높아져서

Real MySQL을 보고 공부한 InnoDB의 아키텍쳐를 정리해보고자 합니다.

책에서 언급하는 8.0이하에서 발생한 이슈들이나 정보들은 정리하지 않았습니다.

 

InnoDB 아키텍쳐

레코드 기반 Lock을 제공하기에 높은 동시성 처리가 가능하며 안정적인 엔진입니다.

MySQL에서 기본적으로 사용하는 스토리지 엔진입니다.

 

InnoDB의 특징

primary key에 의한 클러스터링

InnoDB에 저장되는 테이블 레코드들은 기본적으로 pk에 의해 클러스터링 되어 pk값 순서대로 저장됩니다.

그렇기에 pk를 이용한 레인지 스캔은 빠른 속도로 처리 될 수 있습니다.

또한 세컨더리 인덱스는 레코드의 실제 주소가 아닌 pk의 값을 논리적인 주소로 사용해서 InnoDB의 데이터를 가져옵니다.

foreign key 지원

InnoDB스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY테이블에서는 사용할 수 없습니다.

InnoDB에서 fk를 사용하기 위해서는 부모, 자식 테이블 모두 해당 컬럼에 인덱스 생성이 필요하고, 변경시 부모,자식 테이블 모두에게서 데이터가 있는지, 데드락이 걸릴 수 있는 상황 등 고려할 여러 상황들이 많기에 주의해서 사용할 필요가 있습니다.

 

foreign_key_checks 시스템 변수를 OFF로 설정하면 fk체크 작업을 일시적으로 멈출 수도 있습니다.

SET foreign_key_checks=OFF;
SET SESSION foreign_key_checks=OFF;

 

MVCC (Multi Version Concurrency Control)

트랙잭션간 다중 동시성 제어를 위한 기능입니다.

즉 트랜잭션 간의 읽기와 쓰기간 작업이 충돌하지 않도록 합니다.

MVCC의 MV(Multi Version)는 하나의 레코드에 대해 여러개의 버전이 동시에 관리된다는 의미입니다.

 

InnoDB는 Undo Log를 이용하여 MVCC를 구현합니다.

 

위와 같은 쿼리들이 실행되면 InnoDB에서는 아래 그림과 같은 상황이 됩니다.

버퍼 풀에 작업한 쿼리 내용이 존재하고 디스크에도 동일하게 존재합니다.

이후 아래와 같은 UPDATE문을 실행하면 아래 그림과 같은 상태가 됩니다.

UPDATE 내용을 COMMIT여부와 관계없이 버퍼풀에 반영합니다.

이후 변경되기 전 내용을 언두로그에 보관합니다.

디스크에 변경을 기록하는건 InnoDB의 백그라운드 스레드 (Write Thread)가 작업하기에 UPDATE명령 후 바로 디스크에 업데이트 될 수도 있고 아닐 수도 있습니다.

 

UPDATE문 이후 COMMIT을 하면 버퍼풀의 내용을 디스크에 영구적으로 저장하고,

ROLLBACK을 하면 언두영역의 데이터를 버퍼풀로 복구하고 언두영역의 데이터를 삭제합니다.

언두 영역의 데이터는 COMMIT이 되었을때 사라지는건 아니고 언두영역을 필요로하는 트랜잭션이 없을때 사라집니다.

 

위와 같은 구조로 InnoDB가 동작하기에 두가지 버전 (Multi Version)을 관리한다고 하는것이며 어디에 있는 데이터를 읽어올지는 MySQL의 격리수준(Isolation level)에 따라 다를 수 있습니다.

 

READ_UNCOMMITTED의 경우에는 버퍼풀의 데이터를,

READ_COMMITTED나 그 이상의 격리수준(REPEATABLE_READ, SERIALIZABLE)의 경우에는 아직 COMMIT되지 않았기에 버퍼풀이나, 언두영역의 데이터를 반환합니다.

 

이러한 작업과 과정을 MVCC라고 표현합니다.

 

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

InnoDB 스토리지엔진은 MVCC를 이용하기에 읽기 작업 시 잠금을 걸지 않습니다.

그렇기에 격리 수준 SERIALIZABLE이 아니라면 INSERT와 관련없는 순수한 읽기 작업은 다른 트랙잭션의 변경 작업과 관계없이 없이 바로 실행됩니다.

 

즉 다른 사용자가 특정 레코드를 변경하고 COMMIT하지 않았다고 해도 이 트랜잭션이 다른 사용자들의 SELECT를 방해하지 않습니다. (다른 사용자들은 대부분 언두로그를 통해 데이터를 읽음, 격리수준에 따라 다를 수 있음)

이를 '잠금 없는 읽관된 읽기' Non-Locking Consistent Read라고 합니다.

 

종종 오랜시간 활성된 트랜잭션으로 인해 MySQL서버가 느려진다면 Consistent Read를 위해 InnoDB가 언두로그를 삭제하지 못하고 계속 유지하고 있어서 그런 것일 수 있으니, 트랜잭션은 시작되었다면 가능한 빨리 종료되어야 합니다. 

 

자동 데드락 감지

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

스토리지 엔진은 데드락 감지 스레드를 가지고 있어 주기적으로 잠금 대기 그래프를 검사해 교착상태의 트랜잭션을 찾아 그중 하나를 강제 종료합니다.

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

 

InnoDB엔진은 상위 레이어인 MySQL엔진에서 관리되는 테이블 잠금(LOCK TABLES 명령으로 잠긴 테이블)은 확인 할 수 없어

데드락 감지가 불확실하기에 innodb_table_locks 시스템 변수를 활성화하여 InnoDB 스토리지 엔진 내부 잠금뿐 아니라 테이블 레벨의 잠금까지 감지하도록 활성하하는게 좋습니다.

 

동시 처리되는 스레드나 각 트랜잭션의 잠금의 개수가 많아지면 데드락 감지 스레드가 느려집니다.

데드락 감지 스레드는 잠금 목록을 검사하기에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트(잠금 테이블)에 새로운 잠금을 걸고 데드락 스레드를 찾습니다

데드락 감지 스레드가 느려지면 서비스 쿼리를 처리중인 스레드는 더는 작업을 하지 못하고 대기하게 되므로 서비스에 악영항을 끼칠 수 있습니다.

 

위와 같은 상황에 대비하여 innddb_deadlock_detect 변수를 통해 데드락 감지 스레드를 OFF할 수 있으나

데드락 감지 스레드가 작동하지 않으면 InnoDB스토리지 엔진에서 2개 이상의 트랜잭션이 데드락이 걸리게 된다면 무한정 대기하게 될 것 입니다.

하지만 innodb_lock_wait_timeout 변수(기본값 50초)를 활성화하여 데드락 상황이 지정한 시간만큼 지나게 되면 에러를 반환하게 할 수 있습니다

 

InnoDB 버퍼풀

디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 InnoDB의 가장 핵심적인 기능입니다.

쓰기작업을 지연시켜 일괄적으로 처리할 수 있게 해주는 버퍼 역할도 같이하고있습니다.

INSERT, UPDATE, DELETE처럼 데이터를 변경하는 쿼리는 레코드 데이터에 직접 접근해야하기에 매번 랜덤I/O를 발생시킬 수 밖에 없습니다.

그렇기에 버퍼풀을 이용해서 변경된 데이터를 모아서 처리하면 랜덤I/O 작업을 줄일 수 있습니다.

 

버퍼풀 크기(Memory) 설정

운영체제와 각 클라이언트 스레드가 사용할 메모리를 충분히 고려해서 설정해야합니다.

MySQL서버에서 메모리를 필요로하는 부분은 크게 없지만 가끔 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용되는 '레코드 버퍼'가 상당한 메모리를 사용하는 경우도 있습니다.

그렇기에 커넥션과 테이블 모두 많은 서비스라면 메모리공간이 꽤 많이 필요할수도 있습니다

레코드 버퍼의 크기는 전체 커넥션수와 각 커넥션에서 읽고 쓰는 테이블 개수에 따라 달라집니다.

 

다시 버퍼풀의 크기 이야기로 넘어와서, 운영체제의 공간이 8GB 미만이라면 50%정도만 InnoDB버퍼풀로 설정하는게 좋습니다

더 여유가 있는 운영체제를 사용한다면 전체 메모리의 50%에서 시작해서 조금씩 올려가며 최적점을 찾습니다.

전체 메모리 공간이 50G이상인 운영체제를 사용한다면 15GB ~ 30GB정도를 다른 프로그램을 위해 남겨두고 나머지를 InnoDB버퍼풀로 설정하는게 권장됩니다.

 

innodb_buffer_pool_size 시스템 변수로 크기를 설정 할 수 있으며 동적인 크기 확장도 가능합니다.

버퍼풀은 내부적으로 128MB 청크 단위로 쪼개어져 관리되는데, 이는 크리를 늘리는 단위 크기로 사용됩니다.

그래서 버퍼풀은 반드시 128MB단위로 처리됩니다.

 

버퍼풀은 innodb_buffer_pool_instances 변수를 통해 여러개로 분리하여 관리 할 수도 있습니다.

기본적으로 8개로 초기화됩니다. 

매모리가 크다면 버퍼 풀 인스턴스당 5GB정도가 되도록 개수를 설정하는게 좋습니다.

 

버퍼풀의 구조

InnoDB스토리지 엔진은 버퍼 풀이라는 메모리 공간을 페이지 크기(innodb_page_size 변수에 설정된)로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 페이지를 읽어서 각 조각에 저장합니다.

버퍼풀의 페이지를 관리하기 위해 LRU(Least Recently Used)리스트와 플러시(Flush)리스트, 그리고 프리(Free)리스트라는 3개의 자료구조를 관리합니다.

 

프리 리스트는 버퍼풀에서 데이터로 채워지지 않아 비어있는 페이지들의 목록이며, 클라이언트의 쿼리가 새롭게 디스크에서 데이터를 읽어와야하는 경우에 사용됩니다.

프리 리스트는 LRU(Least Recently Used)와 MRU(Most Recently Used)리스트의 결합입니다.

 

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

 

InnoDB 스토리지 엔진에서 디스크로부터 버퍼풀로 데이터를 가져오는 과정은 아래와 같습니다.

 

1. 필요한 레코드가 저장된 페이지가 버퍼풀에 있는지 확인

  - A. InnoDB 어댑티브 해시 인덱스를 이용해 페이지 검색

  - B. 해당 테이블의 인덱스를 이용해 버퍼풀에서 검색

  - C. 버퍼풀에 이미 페이지가 있다면 해당 페이지의 포인터를 MRU 방향으로 승급

 

2. 디스크에서 필요한 페이지를 버퍼풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가

 

3. 버퍼풀의 LRU 헤더 부분에 적재된 데이터 페이지가 읽히면 (Read되면) MRU헤더 부분으로 이동 

 

4. 버퍼풀에 상주하는 페이지는 클라이언트 쿼리가 얼마나 최근에 접근했었는지에 따라 Age가 부여되며, 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 결국 페이지는 버퍼풀에서 제거됩니다.

버퍼풀의 데이터 페이지가 쿼리에 의해 사용되면 Age가 초기화 되고 MRU의 헤더 부분으로 옮겨집니다.

 

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

 

그렇기에 한번 버퍼풀에 들어간 페이지가 꾸준하게 Read된다면 MRU영역에 계속 존재하게 되고, 사용되지 않는 페이지들은 LRU의 끝으로 밀려나며 결국 버퍼풀에서 제거됩니다.

 

플러시(Flush)리스트는 디스크와 동기화 되지 않은 페이지 (더티 페이지)의 변경 시점 기준의 페이지 목록을 관리합니다.

디스크에서 읽은 상태 그대로 존재한다면 플러시 리스트에 관리되지 않지만, 변경이 가해진 페이지는 플러시 리스트에 관리되고 특정 시점에 디스크로 기록됩니다.

 

데이터가 변경되면 InnoDB는 변경 내용을 리두로그, 버퍼풀의 데이터 페이지에 내용을 반영합니다.

그렇기에  리두로그의 각 엔트리는 특정 데이터 페이지와 연결됩니다.

하지만 리두로그가 디스크에 기록되었다고 해서 버퍼풀의 페이지가 디스크로 기록되었다는것을 보장하지는 않습니다.

InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두로그와 버퍼풀의 페이지의 상태를 동기화 시킵니다.

 

버퍼풀과 리두로그

InnoDB 버퍼풀의 더티 페이지는 특정 리두로그의 엔트리와 관계를 가지고,

체크포인트가 발생하면 체크포인트 LSN보다 낮은 리두로그 엔트리와 관련된 더티 페이지를 모두 디스크로 동기화 시킵니다.

(장애복구 및 데이터 무결성을 위한 디스크 동기화)

 

이제 리두로그, LSN, 체크포인트에 대해 알아보겠습니다.

 

리두로그는 서버가 비정상적으로 종료되었을 때 데이터파일에 기록되지 못한 데이터를 잃지 않게 도와줍니다.

리두로그는 1개 이상의 고정 크기 파일을 사용하며 '순환 버퍼 방식'으로 사용됩니다.

데이터 변경이 발생하면 로그 엔트리(리두 로그에 기록되는 단위)가 하나의 리두로그 파일에 계속 쓰여집니다

계속 쓰여지다가 어느 순간 새로운 파일에 쓰여지고 모든 파일에 다 쓰여지면 다시 첫번째 파일에 로그 엔트리가 덮어 쓰여집니다.

InnoDB엔진은 로그 버퍼를 사용하여 리두로그 파일에 버퍼링하여 데이터를 씁니다.

리두로그는 아카이빙해서 보관하는 방법 또한 존재합니다.

 

리두 로그 파일의 '공간'들에는 새로운 엔트리가 계속해서 덮혀쓰여지기에 로그의 고유ID값 LSN(Log Sequence Number)는 계속해서 증가하게 됩니다. 

InnoDB엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두로그와 버퍼풀의 더티페이지를 디스크로 동기화 시킵니다.

이때 발생한 체크포인트 지점의 로그 엔트리의 LSN 보다 낮은 LSN을 가지는 데이터들을 디스크로 동기화 시킵니다.

 

이 체크 포인트 지점의 LNS을 기점으로 더 큰 LSN을 가지는 데이터가 있는 공간을 활성 리두 로그 (Active Redo Log)공간,

체크포인트 지점의 LSN보다 낮은 LSN을 가지는 공간 즉 디스크로 동기화된 공간은 재사용 가능 공간(Reuseable Space)으로 구분합니다. 

# 체크포인트 발생 시 LSN : 2000
# 2000 이상 LSN을 가지고 있는 공간은 재사용 불가능한 Active Log
# 디스크로 동기화된 2000 이하 LSN을 가지고 있는 공간은 재사용 가능

|-- Reusable Space --|-------- Active Redo Log -------|
| LSN: 1500 ---------| LSN: 2000 ------------ 2500    |

 

활성 리두 공간 (Active Redo Log)공간의 데이터들은 아직 디스크에 동기화 되지 않았기에 덮어쓰여질 수 없습니다.

만약 체크포인트 이벤트가 적절하게 발생하지 않는다면 재사용 가능 공간을 확보할 수 없기에 문제가 발생 할 수 있습니다.

 

추가로 가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지 (CheckPoint Age)라고 하며 이는 활성 리두 공간의 크기를 의미합니다.

 

버퍼 풀 flush (Buffer Pool Flush) (플러시 리스트 flush, LRU 리스트 flush)

버퍼풀은 크게 데이터 캐시, 쓰기 버퍼링 두가지 기능이 있습니다.

버퍼풀에서 디스크로 기록되지 않은 더티페이지들을 효율적으로 디스크에 동기화 하기 위해 쓰기 버퍼링 기능이 존재하고

쓰기 버퍼링 기능을 구현하기 위해서 '플러시 리스트' flush, 'LRU 리스트' flush  2개의 기능을 백그라운드로 실행시킵니다.

이떄 동기화에 사용하는 스레드를 클리너 스레드(Cleaner Thread) 라고 합니다.

(성능 및 클라이언트 데이터 무결성을 위한 디스크 동기화)

 

1. 플러시 리스트 플러시

InnoDB엔진은 버퍼풀에 더티페이지가 생성되면 리두로그에도 기록되지만 '플러시 리스트'에도 추가됩니다.

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

이 더티페이지 일괄 처리 작업(flush)이 효율적일수록 클라이언트의 쿼리 처리가 악영향을 받지 않고 부드럽게 처리 될 수 있습니다.

 

버퍼풀에 더티페이지의 양이 많을 수록 Disk IO Burst 현상이 발생할 가능성이 높아지는데,

버퍼풀의 더티페이지 비중이 90%가 넘어가게 되면 InnoDB엔진은 즉시 디스크 기록을 시작합니다.

이때 Disk IO Burst현상이 발생할 가능성이 매우 높기에 innodb_max_dirty_pages_pct_lwn 변수를 이용해 일정 수준이상의 더티페이지가 발생하면 조금씩 디스크로 기록하도록 하는 설정값을 조정해서 Dist IO Burst현상을 예방 할 수도 있습니다 (기본값10%)

 

2. LRU 리스트 플러시

스토리지 엔진은 LRU리스트에서 사용빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어들일 공간을 만드는데,

이를 위해 LRU리스트에서 flush함수가 사용됩니다.

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

스캔하면 디스크에 동기화 작업을 진행하고 클린 페이지는 즉시 Free 리스트로 페이지를 옮깁니다.

LRU 리스트를 flush 함으로서 공간을 확보해 클라이언트의 요청 처리 속도를 개선합니다.

 

버퍼풀 상태 백업 및 조회방법

버퍼풀은 쿼리 성능에 매우 밀접한 관계가 있습니다.

버퍼풀에 사용할 데이터들이 미리 적재되어 있으면 디스크 읽기 없이 데이터를 클라이언트에 반환 할 수 있기 때문입니다.

이런 상태를 Warning Up상태라고 합니다.

 

버퍼풀에 대한 백업은 데이터 디렉토리에 ib_buffer_pool이라는 이름의 파일로 생성되는데,

실제 버퍼풀의 크기에 비해 매우 작습니다. (몇십MB 이하)

그 이유는 LRU리스트의 데이터 페이지의 메타 정보만 가져와 저장하기 때문입니다.

 

버퍼풀에 있는 데이터를 조회하기 위해서는 information_schema 데이터베이스에 innddb_cached_indexs 테이블에서 

테이블의 인덱스별로 데이터 페이지가 얼마나 버퍼풀에 적재되어 있는지 확인 할 수 있습니다.

SELECT it.name table_name
      ,ii.name index_name
      ,ici.n_cached_pages n_cached_pages
FROM information_schema.INNODB_TABLES it
INNER JOIN information_schema.INNODB_INDEXES ii on  ii.TABLE_ID = it.TABLE_ID
INNER JOIN information_schema.INNODB_CACHED_INDEXES ici on ici.INDEX_ID = ii.INDEX_ID

 

테이블 전체 페이지중 어느정도 비율이 InnoDB 버퍼풀에 있는지 확인할수도 있습니다.

SELECT
    t.TABLE_NAME
    ,(SELECT SUM(ici.N_CACHED_PAGES) AS n_cached_pages
     FROM information_schema.INNODB_TABLES it
              INNER JOIN information_schema.INNODB_INDEXES ii ON ii.TABLE_ID = it.TABLE_ID
              INNER JOIN information_schema.INNODB_CACHED_INDEXES ici ON ici.INDEX_ID = ii.INDEX_ID
     WHERE it.name = CONCAT(t.table_schema, '/', t.table_name)
    ) AS total_cached_pages,
    ((t.data_length + t.index_length - t.data_free) / @@innodb_page_size) AS total_pages
FROM
    information_schema.tables t
WHERE
    t.table_schema = 'EMP' and t.TABLE_NAME = 'TB_USER'

 

 

Double Write Buffer

파셜 페이지(Partial-page), 톤 페이지(Torn-page)라는 용어가 있습니다.

하드웨어의 오작동이나 시스템의 비정상종료 시 페이지중 일부만 기록되는 현상인데 이는 InnoDB 스토리지 엔진에서 디터 페이지를 디스크 파일로 플러시 할때 일부만 기록되기 떄문에 발생합니다.

 

이러한 현상을 막기 위해 InnoDB엔진에서는 Double-Write 기법을 사용하니다.

버퍼풀애서 더티페이지를 디스크에 쓰기 전에 데이터페이지들을 적당한 위치에 랜덤I/O 방식으로 먼저 DoubleWrite 버퍼에 기록합니다.

이후 버퍼풀에서 디스크에 쓰기가 성공하면 DoubleWrite 버퍼에 저장해두었던 내용은 사라집니다.

 

하지만 디스크 쓰기 중 비종상 종료가 되어 재시작을 한 상황이라면

재시작 시 DoubleWrite 버퍼에 있는 내용과 데이터 파일의 페이지 내용을 비교해서 다른 내용이 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사합니다.

 

하지만 SSD와 같이 랜덤I/O가 일어나는 저장시스템에서는 해당 기능의 비용이 다소 들 수도 있습니다.

 

언두로그 (Undo Log)

InnoDB 스토리지 엔진은 트래잭션과 격리수준을 보장하기 위해 DML로 변경되기 이전 데이터를 별도로 백업해둡니다.

이 백업 데이터를 '언두로그'라고 합니다.

리두로그는 데이터 백업과 장애 복구 시 사용하고, 언두로그는 트랜잭션 보장과 서비스의 격리수준을 보장하기 위해 사용됩니다.

 

위와 같은 DML이 실행되면 트랜잭션을 커밋하지 않아도 실제 데이터와 인덱스 버퍼에는 UPDATE-MySQL-USER라는 이름으로 변경됩니다.

그리고 변경되기 전 데이터(MySQL-USER)는 언두영역에 백업됩니다.

이 상태로 COMMIT이 일어나면 상태가 유지되고,  (언두로그가 바로 삭제되지는 않고 삭제예정 처리(Prune) 됨, 백그라운드 작업으로 삭제)

ROLLBACK되면 언두영역의 백업 데이터를 원래대로 되돌립니다. 

 

DML 쿼리를 통해 트랜잭션에 적용되는 레코드들이 많으면 언두로그에 그만큼의 레코드들이 쌓이게 되어 사용량이 늘어나게됩니다.

그리고 트랜잭션이 오래 실행되면 될수록 언두로그의 사용량에 영향을 미쳐 전반적인 쿼리 성능이 느려지게 됩니다.

 

MySQL에서 활성상태의 트랜잭션이 장기간 유지되는건 좋지 않습니다.

그래서 MySQL 서버의 언두레코드를 모니터링하는것은 반드시 필요합니다.

 

언두 레코드 수 확인

SELECT count
FROM information_schema.innodb_metrics
WHERE SUBSYSTEM='transaction' AND NAME = 'trx_rseg_history_len';

 

언두 테이블스페이스 관리

언두로그는 '언두 테이블스페이스'에서 관리됩니다.

하나의 언두 테이블 스페이스는 1 ~ 128개의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두슬롯을 가집니다.

 

하나의 롤백 세그먼트는 InnoDB 페이지 크기를 16바이트로 나눈 값의 개수만큼 언두 슬롯을 가집니다.

InnoDB 페이지 크기가 16KB라면 롤백 세그먼트는 1024개의 언두슬롯을 가집니다.

하나의 트랜잭션이 필요로하는 언두슬롯 개수는 최대 4개입니다.

하지만 대략적으로 2개정도로 계산해서 최대 통시 처리 가능 트랜잭션 수를 예측 할 수 있습니다.

최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 * (롤백 세그먼트 개수) * (언두 테이블스페이스 개수)

 

기본 세팅에서는 대략 131072(16*1024/16*128/2)개의 트랜잭션이 동시에 처리 될 수 있습니다.

아래와 같은 명령어로 언두 테이블 스페이스를 동적으로 생성 및 조절할수도 있습니다.

-- 언두 테이블 스페이스 생성
CREATE UNDO TABLESAPCE
-- 언두 테이블 스페이스 삭제
DROP UNDO TABLESPACE

 

체인지 버퍼

테이블의 레코드가 변경되면 인덱스 또한 변경이 일어나야합니다.

하지만 인덱스를 읽어 변경하는 작업은 결국 랜덤 I/O작업이 일어나게 되고 이는 상당한 자원소모가 발생합니다.

그렇기에 InnoDB엔진은 변경해야할 인덱스 페이지가 버퍼풀에 있으면 즉시 업데이트를 수행하지만

그렇지 않고 디스크에서부터 읽어서 변경해야한다면 즉시 실행하지 않고 임시공간에 저장해둔뒤 먼저 사용자에게 결과를 반환하고 추후 작업을 하게됩니다.

이때 사용하는 임시공간을 체인지버퍼라고 합니다.

기본적으로 체인지 버퍼는 InnoDB 버퍼풀로 설정된 공간에 25%까지 사용 가능합니다. (innodb_change_buffer_max_size 변수를 통해 50%까지 조정 가능)

SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME = 'memory/innodb/ibuf0ibuf'

 

 

 

어뎁티브 해시 인덱스 (Adaptive Hash Index)

사용자가 직접 생성하는 B-Tree 인덱스가 아닌 InnoDB스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해서 자동으로 생성하는 메모리 기반 해시 인덱스입니다.

 

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

어뎁티브 해시 인덱스가 생성된 후 어뎁티브 해시 인덱스를 사용할 수 있는 요청이 발생하면 B-Tree인덱스 대신 어뎁티브 해시 인덱스를 사용합니다.

그렇기에 B-Tree의 루트 노드에서 리프노드까지 찾아가는 비용이 줄어들고 CPU의 역할이 줄어들어 쿼리의 성능을 향상시킵니다.

 

어뎁티브 해시 인덱스는 InnoDB엔진에서 하나만 존재하며 (파티션은 가능)

'인덱스 키 값'과 해당 인덱스 키 값이 저장된 '데이터 페이지 주소'의 쌍으로 관리되는데,

인덱스 키 값은 'B-Tree 인덱스의 고유번호(Id)'와 'B-Tree 인덱스의 실제 키 값'의 조합으로 생성됩니다.

인덱스 키에 B-Tree 인덱스의 고유번호(Id)를 가지고 있는 이유는 'B-Tree 인덱스의 실제 키 값'이 어떤 B-Tree인덱스에 있는지 구분하기 위함입니다.

 

어뎁티브 해시 인덱스의 value값인 해당 인덱스 키 값이 저장된 '데이터 페이지 주소는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가지는데, 이는 InnoDB 버퍼풀에 로딩된 페이지의 주소를 의미합니다.

그렇기에 어뎁티브 해시 인덱스는 InnoDB 버퍼풀에 로딩된 페이지에 대하서만 관리됩니다.

 

하지만 어뎁티브 해시 인덱스가 성능에 도움이 되지 않는 경우도 존재합니다.

어뎁티브 해시 인덱스는 innodb_adaptive_hash_index 변수를 통해 비활성화가 가능합니다.

 

어뎁티브 해시 인덱스가 도움이 되는 경우

 

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

  - 동등 조건 검색이 많은 경우 (동등비교나 IN)

  - 쿼리가 데이터 중 일부 데이터에 집중되는 경우

 

어뎁티브 해시 인덱스가 도움이 되지 않는 경우 

 

  - 디스크 읽기가 많은 경우

  - 특정 패턴의 쿼리가 많은 경우 (join이나 like)

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

 

아래 명령어를 통해 InnoDB 상태를 확인해보면

INSERT BUFFER AND ADAPTIVE HASH INDEX 항목을 볼 수 있는데

마지막 hash searches/s + non-hash searches/s 초당 번의 검색이 있었는데 hash searches/s 만큼 어뎁티브 해시 인덱스가 실행되었다는 내용입니다.

show engine INNODB status\G

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
5.03 hash searches/s, 15.24 non-hash searches/s