InnoDB Engine

많은 엔진 중 innoDB가 8.0 이후 메인이 된 이유는

  1. 거의 유일하게 레코드 기반 잠금을 제공한다.
  2. 테이블 잠금이 아니라 높은 동시성 처리가 가능하다. 가 있다.

특징

  1. PK에 의해서 클러스터링이 된다.(물리적 저장 공간도 가른다.)
  2. 외래키를 지원한다.(무결성을 체크하는 과정이 들어간다.)
  3. MVCC(MultiVersionConcurrencyControl)으로 잠금을 사용하지 않고 일관된 읽기를 제공한다. (레코드에 대해 버전 관리를 진행한다.)
  4. 자동 데드락 감지를 지원한다. 잠금이 교착 상태에 빠지지 않았는지 백그라운드에서 확인하고 조치한다.

1. PK에 의한 클러스터링

InnoDB에서는 Pk가 꼭 필요하다. 기본적으로 PK를 기준으로 클러스터링 되어 저장된다. 혹시나 PK가 없다면 사용자는 인지하지 못하는 내부에서만 사용하는 PK를 만들어서라도 사용한다.

이러한 특징 때문에 PK를 이용한 레인지 스캔은 빠르게 처리될 수 있다.

2. 외래키 지원

InnoDB는 부모-자식의 인덱스 설정, 무결성 체크와 이에 따른 잠금 전파가 되는 외래키 제약 조건을 지원한다.

3. MVCC(Multi Version Concurrency Control)

레코드 레벨 트랙잭션을 지원하는 DBMS가 제공하는 기능이며 MVCC의 궁극적 목적은 잠금 없이 일관된 읽기를 제공하는 것에 있다. InnoDB는 undo log를 이용해서 여러 버전의 레코드를 관리하고 이를 통해서 일관된 읽기를 제공한다.

예를 들어 insert를 하면 바로 디스크에 쓰는 것이 아니라. InnoDB bufferPool에 올려 둔다. backgroundThread에 의해서 지연된 쓰기가 되면 비로소 Disk에 쓰기 작업이 진행된다. 그 전까지는 select을 하면 버퍼풀에서 읽어들인다.

update의 경우에는 버퍼풀은 변경된 대로 바꾸고 undoLog에 이전 내역을 쌓아서 커밋이 안된 작업 중이라면 undo를 읽어서, 커밋이 됐다면 버퍼풀을 읽어서 결과를 전달한다.

이 항목은 격리 수준과도 연관이 있다.

4. 자동 데드락 감지

잠금이 교착 상태에 빠지지 않았는지 체크하기 위해서 잠금 대기 목록을 그래프 형태로 관리한다. 만약 데드락 감지 쓰레드가 주기적으로 확인 도중 교착 상태에 빠진 트랜잭션을 찾으면 그 중 하나를 강제 종료하는 식으로 데드락을 해제한다. 둘 중 하나를 선택하는 판단 근거는 undoLog의 양이다.

이 과정은 보통은 문제가 되지 않지만 동시 처리가 격해지거나 잠금 개수가 많아지면 문제가 생긴다. 당시 상황을 보존하기 위해서 리스트에 저장된 목록에 새로운 잠금을 걸고 데드락 쓰레드를 찾기 때문이다.

innodb_deadlock_detect로 조절할 수 있지만 off로 두면 단순하게 failover 할 수 있는 경우에도 시스템이 다운될 수 있으니 문제가 생길 수 있다. innodb_lock_wait_time으로 일정 시간이 지나면 요청이 실패하고 에러 메시지를 반환하게 할 수도 있다.

구조

  • BufferPool
  • DoubleWriteBuffer
  • UndoLog
  • ChangeBuffer
  • RedoLog
  • AdaptiveHashIndex

BufferPool

innoDB 핵심 부분으로 디스크 데이터, 파일, 인덱스 정보를 메모리에 올려두는 공간이다. 쓰기 작업에도 이 메모리에 올려두고 한 번에 디스크에 쓰기하는 지연 일괄 처리도 가능케 하는 부분이다.

innodb_buffer_pool_size로 설정할 수 있다. 동적으로 버퍼풀 크기를 정할 수 있다. 꽤 크리티컬한 부분이기 때문에 시스템에 맞춰서 조금씩 변경하면서 최적점을 찾아가야 하는 튜닝 포인트다. 메모리가 8GB 미만이면 50%로, 그 이상이면 50%에서 점진적으로 늘려가는 식으로 잡으라고 가이드한다.

과거에는 버퍼 풀 전체를 관리하는 세마포어로 인해 내부 경합이 있었는데, 버퍼풀을 쪼개면서 개선됐다. innodb_buffer_pool_instances로 수를 조정할 수 있다.

BufferPool 구조

innoDB는 버퍼풀을 페이지 크기(innodb_page_size)로 조각내서 InnoDB 엔진이 필요할 때 데이터 페이지를 읽을 수 있도록 한다. 페이지 관리는 LRU(Least Recently Used), Flush 리스트 Free 리스트라는 3가지 종류의 자료 구조로 구성된다.

  • Free : 할당 X
  • LRU : 최근 사용한 순서로 정렬되어 있다. 쓰지 않을 수록 old로 빠지며 새로 읽으면 new로 올라온다. 읽은 페이지를 최대한 오랫동안 써서 DISK I/O를 줄이려는 목적이다.
  • Flush : 동기화 되지 않은 데이터를 가진 데이터 페이지(더티 페이지). 등록 후 수정되면 플러시 리스트에서 관리되고 특정 시점이 되면 플러시 된다. InnoDB는 변경되면 리두로그에 기록하고 리두에 데이터 페이지와 연결하는 식으로 구성된다.

BufferPool과 리두

버퍼풀과 리두는 연관 관계가 깊다. 버퍼 풀의 양은 쿼리 성능과 직결된다.

  1. 데이터 캐시 (읽은 데이터를 메모리에 보관)
  2. 쓰기 버퍼링 (쓴 데이터를 메모리에 올려놓고 한 번에 디스크에 기록해서 DISK I/O를 줄인다.)

2번에 한해서 리두와 연관이 깊은데 InnoDB는 Redo를 1개 이상의 고정 크기 파일을 연결해서 환형으로 사용한다. 즉, 데이터 변경이 반복되면 일전의 RedoEntry를 덮어쓰는 식이다. 앞서 말한 바와 같이 Redo는 Flush리스트와 매핑된다. 이렇게 물려 있는 공간을 ActiveRedoLog라고 한다.

환형이지만 로그 포지션(LogSequenceNumber: LSN)은 증가한다. InnoDB는 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 더티 페이지를 Disk에 flush 한다. 그래서 마지막 LSN - 최근 LSN을 CheckPointAge라고 한다. 체크 포인트가 발생하면 체크포인트보다 작은 LSN을 가진 리두, 더티 페이지는 모두 Disk에 동기화돼어야만 한다.

자, 버퍼풀은 큰데 리두가 작으면 리두가 금방 돌게 되므로 버퍼링 효과를 못 본다. 반대의 경우 리두가 크고 버퍼풀이 작으면 더티 페이지가 작아지고 이것도 문제가 된다. 급작스럽게 Disk I/O를 유발할 수 있기 때문이다.

비율은 버퍼풀이 리두보다 크게 잡히는게 맞다. 그러나 비대칭은 성능을 제대로 사용하지 못하는 결과를 낳을 수도 있다.

BufferPool Flush

더티 페이지를 디스크로 작성하는 과정을 의미한다. 성능 저하 없이 이 작업을 수행하기 위해서 아래의 두 가지 플러시 기능을 백그라운드 로 진행한다.

  • Flush_list flush
  • LRU flush
FlushList Flush

리두 로그 재활용을 위해서 CheckPointAge가 많은 요소들을 Disk에 동기화한다. 선행되어야 하는 것은 DirtyPage 동기화다. 주기적으로 이를 위해서 Flush_list 함수를 콜해서 Disk에 쓰는 작업을 한다.

  • innodb_page_cleaners
  • innodb_max_dirty_pages_pct_lwm
  • innodb_max_dirty_pages_pct
  • innodb_io_capacity
  • innodb_io_capacity_max
  • innodb_flush_neighbors
  • innodb_adaptive_flushing
  • innodb_adaptive_flushing_lwm

클리너 쓰레드의 수, 버퍼풀에서 더티 페이지의 비율 등을 조정할 수 있다. 일반적으로 더티페이지가 많을 수록 쓰기 버퍼링을 극대화할 수 있다. 너무 많이도 문제가 된다. 많은 더티 페이지가 한 번에 기록되면 DiskBurst가 될 수 있다.

읽기/쓰기에 굉장히 민감하기에 adaptive_flush로 여타 설정 없이(innodb_io_capacity, innodb_io_capacity_max 의존 없이) 스토리지 엔진 판단 하에 동작하도록 할 수 있다. (리두 로그 생성 속도를 분석한다.)

마지막으로 innodb_flush_neighbors로 쓰기 작업 때 근접한 경우 같이 들고 들어가는 식으로 I/O를 줄일 수 있다.

LRU Flush

사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지를 읽을 공간을 만들어야 하는데, 이 때 LRU_list 플러시 함수를 사용한다. 이 과정에서 더티페이지는 동기화, 클린 페이지는 Free로 옮긴다.

버퍼풀 웜업

MySQL 셧다운 시 innodb_buffer_pool_dump_now로 꺼지기 직전 버퍼풀 상태를 백업하고 다시 켜질 때 복원할 수 있다.

버퍼풀 적재 내용 확인

select * from information_schema.innodb_buffer_page;

POOL_ID BLOCK_ID SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE PAGE_STATE IO_FIX IS_OLD FREE_PAGE_CLOCK
0 0 3223 329 INDEX 2 0 YES 12569361696 0 3687115098 `UnionGlobal`.`tblProduct` PRIMARY 22 15045 0 FILE_PAGE IO_NONE NO 59217
0 1 2969 3 INDEX 1 0 NO 12842443110 0 3823986346 `Baro`.`tblDevice` PRIMARY 4 56 0 FILE_PAGE IO_NONE NO 59224
0 2 0 0 UNKNOWN 0 0 NO 0 0 0 null null 0 0 0 NOT_USED IO_NONE NO 0
0 3 0 5535 UNDO_LOG 1 0 NO 12822049215 0 1103049611 null null 0 0 0 FILE_PAGE IO_NONE NO 58694
0 4 2981 258 INDEX 1 0 NO 12842442406 0 3709288763 `SYS_TABLES` CLUST_IND 115 15132 0 FILE_PAGE IO_NONE NO 58544
0 5 2213 7 INDEX 0 0 YES 0 0 4048910798 `Homepage`.`tblPortfolio` PRIMARY 49 15533 0 FILE_PAGE IO_NONE YES 57814
0 6 2981 666 INDEX 1 0 NO 12842440799 0 3708388881 `SYS_TABLES` CLUST_IND 110 15081 0 FILE_PAGE IO_NONE YES 58194
0 7 2981 609 INDEX 1 0 NO 12842443203 0 3708253346 `SYS_TABLES` CLUST_IND 89 15042 0 FILE_PAGE IO_NONE NO 58544
0 8 2920 173 INDEX 0 0 YES 0 0 3731384877 `UnionGlobal`.`tblCouponBox` PRIMARY 255 15045 0 FILE_PAGE IO_NONE NO 58559
0 9 2981 69 INDEX 1 0 NO 12842443203 0 3705727334 `SYS_TABLES` CLUST_IND 89 15047 0 FILE_PAGE IO_NONE NO 58507

DoubleWriteBuffer

리두 공간 낭비를 막기 위해서 페이지의 변경된 내용만 기록한다. 그런데 이러면 더티 페이지 플러시 때 일부만 기록될 수 있고 그러면 유사시 해당 페이지는 복구가 불가능할 수도 있다. 이 경우를 Partial-page, Torn-page라고 한다.

이를 막기 위해서 Double-Write를 사용한다. 플러시 전 한 번의 디스크 쓰기로 System-TableSpace의 DoulbeWrite 버퍼에 기록한다. 그리고 이를 기반으로 각 더티 페이지를 기록한다.

DoubleWriteBuffer는 중간에 문제가 생길 때를 대비한 것이라 다른 목적으로는 사용되지 않는다. 또한 재시작 시 이 영역과 데이터 페이지를 대조해서 복원하기도 한다.

Undo log

InnoDB는 MVCC를 위해서 이전 데이터를 보관한다.

  1. 트랜잭션 보관 : 트랜잭션 롤백 시 변경 전 데이터로 복구해야 하는데 언두로그를 참조한다.
  2. 격리수준 보장 : 격리 수준에 맞게 언두 로그에 백업한 데이터를 읽어서 반환하기도 한다.

굉장히 중요하지만 관리 비용도 만만치 않다.

show engine innodb status; 의

=============

TRANSACTIONS

=============

에서 확인할 수 있다.

언두 테이블 스페이스

UndoTableSpace라고 한다. undo가 저장되는 공간을 5.6 전까지는 서버가 초기화될 때 생성되서 확자엥 문제가 있었다. (ibdata.ibd에 저장됐다.) 그러나 innodb_undo_tablespaces 변수를 2보다 크게 주면 시스템 테이블 스페이스가 아닌 별도 로그 파일을 사용하는 것으로 바뀌었다. 8.0.14부터 항상 외부에 저장되는 것으로 바뀌었다.

하나의 롤백 세그먼트는 InnoDB 페이지 크기를 16바이트로 나눈 값의 개수 만큼 언두 슬롯을 갖는다. 언두가 남는건 문제가 되지 않는데 언두가 부족하면 트랜잭션을 시작할 수 없을 수 있다. 따라서 언두 관련 시스템 변수를 변경한다면 동시 트랜잭션 수에 맞게 설정해야 한다.

ChangeBuffer

RDBMS는 Insert, Update 시 데이터도 데이터지만 인덱스도 관리해야 한다. 인덱스 업데이트 작업은 random disk i/o이므로 부담스럽다. 만일 버퍼 풀에 있으면 바로 업데이트 하지만 그게 아니라면 (읽어서 수정해야 한다면) 임시공간에 저장하고 사용자에게 반환하는 형태로 성능을 향상 시킨다. 이 때 사용하는게 체인지 버퍼다.

유니크 인덱스에는 체인지 버퍼를 사용할 수 없다. (유니크임을 증명해야하니까) 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 백그라운드 쓰레드에 의해서 병합되는데 이 쓰레드를 체인지 버퍼 머지 쓰레드라고 한다.

체인지 버퍼는 기본적으로 버퍼풀로 설정된 메모리의 25%까지 사용할 수 있게 설정돼 있다.

리두 로그, 리두 버퍼

리두는 ACID의 Durable과 연관이 있다. 서버가 기록하지 못한 데이터를 비정상 종료시 보전할 수 있게 해주는 안전 장치다. 대부분 데이터베이스는 데이터 변경 내용을 로그로 먼저 기록한다. 읽기 성능을 고려한 자료 구조를 갖고 있기 때문에 파일 쓰기는 랜덤 액세스를 필요로 한다. 이는 비용이 크다. 따라서 리두 로그를 두고 비정상 종료가 되면 리두 로그를 바탕으로 복원한다.

  1. 커밋됐지만 Disk에 쓰지 않은 것 : 리두를 보고 기록한다.
  2. 롤백 됐지만 Disk에 쓴 것 : 리두를 확인하고 언두로 돌려놓는다.

리두를 어느 주기로 디스크에 동기화할지를 결정할 수 있다. innodb_flush_log_at_trx_commit으로 말이다.

  • 0 이면 1초에 한 번씩 기록, 동기화한다. 1초 내의 기록은 누락될 수 있다.
  • 1이면 트랜잭션 커밋과 생을 같이 한다. 커밋되면 트랜잭션에서 변경한 데이터는 사라진다.
  • 2 트랜잭션 커밋 시 기록은 되지만 실질 동기화는 1초에 한 번씩 된다. 커밋되면 OS 메모리 버퍼로 기록되는 것이 보장된다.

리두 로그 사이즈 전체 크기는 버퍼풀을 효율성을 결정한다. innodb_log_file_size, innodb_log_files_in_group으로 사이즈, 수를 결정할 수 있다. 리두 로그 파일 크기는 두 변수의 곱으로 산정된다.

리두 활성/ 비활성

리두는 비정상 종료 시를 대비할 수 있게 해준다. 커밋돼도 데이터 파일은 디스크로 동기화되지 않는 반면 리두 로그는 항상 디스크로 기록된다. 8.0부터는 수동으로 리두 로그를 활성/ 비활성화할 수 있게 됐다. bulk 작업을 하는 경우 유용하다.

Alter instance disable innodb redo_log; Alter instance enable innodb redo_log;

Adaptive Hash Index

어댑티브 해시 인덱스는 사용자가 수동으로 생성하는 것이 아니라 InnoDB Storage Engine에서 사용자가 자주 요청하는 데이터에 대해서 자동으로 생성하는 인덱스다. innodb_adaptive_hash_index로 활성/비활성 할 수 있다. 어댑티브 해시 인덱스는 B-Tree 검색 시간을 줄이기 위해서 도입된 기능이다. 자주 읽는 데이터 페이지의 키 값을 이용해서 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 위치를 찾아간다. B-Tree 순회 과정이 없어지고 쿼리 성능을 향상된다.

해시 인덱스는 인덱스 키 - 데이터 페이지 주소로 구성된다. (인덱스 키 - 버퍼풀 주소)

어댑티브 해시 인덱스를 의도적으로 비활성화 하는 경우도 있다.

  1. 디스크 읽기가 많은 경우
  2. 특정 패턴의 쿼리가 많은 경우(join, like)
  3. 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

아래의 경우는 도움이 되는 경우다.

  1. 디스크 데이터가 innoDB 버퍼풀 크기과 비슷한 경우( 디스크 읽기가 많지 않은 경우 )
  2. 동등 조건 검색( 동등 비교, IN )이 많은 경우
  3. 쿼리가 데이터 중 일부에 집중되는 경우

어댑티브 해시 인덱스는 버퍼 풀에 접근하기 빠르게 하는 기능이기에 디스크에서 자주 읽어오는 경우는 의미가 퇴색된다. 또한 인덱스 저장 공간을 따로 사용해야 하며, 때로는 큰 메모리 공간이 들 수 있다. 테이블 삭제 때도 어댑티브 해시 인덱스를 비워야 하기에 비용이 크다.