[Database] Storage Engine의 꽃 InnoDB

InnoDB는 MySQL의 기본 storage engine입니다. 우리가 사용하는 데이터베이스의 기능(transaction, isolation level 등)은 InnoDB에서 제공하고 있는 만큼 InnoDB를 이해하면 데이터베이스의 기능을 더 효율적으로 활용할 수 있습니다. 이번 포스팅을 통해 InnoDB가 어떤 기능을 어떻게 제공하는지 살펴보겠습니다.  이번 포스팅은 MySQL 경험이 있고 MySQL 기능을 어느 정도 이해한 분들이 대상입니다. 

 

InnoDB 란? 

InnoDB는 MySQL과 MariaDB에서 사용하는 storage engine입니다. InnoDB는 트랜잭션 기능, row-level locking, clustered index를 활용한 효율적인 데이터 저장과 검색 그리고 데이터 정합성을 위한 foreign key 기능을 제공합니다. 애플리케이션 개발자인 제 입장에서 위 기능들은 자주 활용하기 때문에 더 중요한 것 같습니다. 

InnoDB가 제공하는 기능
- B-tree Index
- Backup / point-in-time recovery 
- Clustered indexes
- Compressed data 
- Data caches 
- Encrypted Data 
- Foreign key 
- Full text search 
- Geospatial data type support 
- Index caches 
- Locking granularity (row level) 
- MVCC 
- Replication 
- Storage limit up to 64TB 
- Transactinos 
- Update statistics for data dictionary 

장점 

InnoDB의 대표적인 장점에 대해 살펴보겠습니다.

• Crash Recovery: InnoDB는 데이터베이스 서버가 알 수 없는 이유로 실패하더라도 데이터베이스 서버 재시작 과정에서 관리자가 별도의 처리를 수행할 필요가 없습니다. 운영 관점에서 살펴보면 이게 얼마나 편리한 것인지.... InnoDB는 이러한 기능을 제공하기 위해 다양한 로그를 활용합니다.
• Buffer Pool: InnoDB는 메모리에 Buffer Pool 공간을 두어 테이블이나 인덱스를 캐싱합니다. 이를 통해 쿼리를 처리하는 속도를 향상할 수 있습니다. 
• Insert, update, delete 최적화: Change buffering 기능을 활용해서 insert, update, delete 등의 쓰기 작업을 최적화할 수 있습니다. Change buffer는 뒤에서 살펴보겠습니다. 

InnoDB를 효과적으로 사용하는 방법 

InnoDB를 자세히 살펴보기 전에 우선 개발자로서 어떻게 InnoDB를 잘 활용하는지 살펴보겠습니다. 

• Primary key를 지정해서 사용합니다. Primary key를 지정하지 않더라도 InnoDB가 내부적으로 활용할 primary key를 생성하기 때문에 가능하다면 primary key를 생성해서 사용하는 게 권고됩니다. 모든 secondary index는 primary key를 함께 저장하기 때문에 primary key의 크기에 대한 고려가 필요합니다. 
• Join 성능을 높이기 위해서는 foreign key를 활용해 join column을 연결하고(foreign key 지정 시 index는 자동적으로 생성됩니다) 가능하면 join column 간 데이터 타입을 동일하게 설정합니다. 
• Autocommit을 해제합니다(default로 ON입니다). Autocommit이 ON인 경우 명시적으로 트랜잭션 내부에서 실행되지 않는 DML을 트랜잭션으로 감싸서 실행합니다. 서로 연관된 DML을 하나의 트랜잭션으로 묶어서 사용하는 것이 autocommit에 의해 각각의 DML이 각각의 트랜잭션에서 실행되는 것보다 성능이 좋습니다. 
• "LOCK TABLE"을 사용하지 말고 "SELECT... FOR UPDATE"처럼 row-level locking을 제공하는 기능을 활용합니다. 

 

InnoDB ACID 

데이터베이스 하면 가장 중요한 단어를 떠올리라 하면 ACID일 확률이 높습니다. InnoDB도 ACID 특성을 보장하기 위해 다양한 방법을 활용하는데요, 하나하나 살펴보겠습니다. 

Atomicity 

트랜잭션의 원자성을 의미합니다. 하나의 트랜잭션에서 실행되는 모든 작업은 모두 성공하거나 모두 실패해야 합니다. InnoDB의 원자성을 활용하기 위해서는 autocommit을 활용하거나 COMMIT 또는 ROLLBACK 명령을 활용할 수 있습니다. 

Consistency 

데이터베이스는 일관된(consistent) 상태이어야 하며 일관된 상태에서 특정 작업이 수행된 이후에도 일관된 상태이어야 합니다. 데이터베이스의 일관성을 보장하기 위해 InnoDB는 double write buffer과 crash recovery 기능을 제공합니다. 

Isolation 

트랜잭션끼리 어떻게 격리시킬 것인지와 관련된 특성입니다. InnoDB의 경우 "SET TRANSACTION" 구문을 통해 isolation level을 지정할 수 있습니다. Isolation level은 READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE이 있는데요, 각각의 isolation level은 뒤에서 살펴보겠습니다. 

Durability 

커밋된 데이터를 영구저장소에 어떻게 저장할 것인지와 관련된 특성입니다. 다양한 형태의 영구저장소가 존재하기 때문에(SSD, HDD, RAID) MySQL 문서에서도 가이드라인을 제공하는 게 복잡하다고 명시돼있습니다. 

 

InnoDB Multi-Versioning 

InnoDB는 MVCC(Multi-Version Concurrency Control)을 지원합니다. Row 데이터의 여러 버전을 저장함으로써(undo log에 저장합니다) MVCC를 지원합니다. MVCC의 장점은 하나의 row 데이터에 대한 쓰기 작업이 진행 중이라도 해당 row에 대한 읽기 작업을 blocking 하지 않는다는 점입니다(동일한 row이지만 서로 다른 버전의 데이터를 읽으면 되기 때문입니다). 

특정 데이터에 대한 쓰기 작업(insert, update)이 발생하면 InnoDB는 기존 버전 데이터를 undo log에 기록하고 버퍼 풀에 해당 데이터를 업데이트합니다. Undo log에 기록된 데이터는 rollback 또는 consistent read를 위해 사용됩니다. MVCC에 대해 더 자세한 설명을 위해서는 하단의 포스팅을 확인해주세요. 

2022.11.14 - [Database/DBA급 개발자로] - [Database] DBA급 개발자로 - #18 Mutli-Version Concurrency Control

[Database] DBA급 개발자로 - #18 Mutli-Version Concurrency Control

 

InnoDB Architecture 

다음으로는 InnoDB가 어떻게 구성됐는지 살펴보겠습니다. 하단의 사진은 InnoDB의 내부 구조입니다. 

InnoDB architecture(https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html)

In-Memory Structures 

우선 메모리에 저장된 InnoDB의 각 구조에 대해 살펴보겠습니다. 

Buffer Pool 

Buffer Pool's LRU (https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html)

Buffer Pool은 InnoDB가 테이블과 인덱스를 캐싱하기 위해 사용하는 공간입니다. Buffer Pool은 페이지의 단위로 구성돼있고 하나의 페이지는 한 개 이상의 row를 저장할 수 있습니다. Buffer Pool은 캐시이기 때문에 오래 사용되지 않는 페이지는 제거되어야 합니다. 오래된 페이지를 제거하기 위해 LRU 알고리즘을 활용합니다. 

Buffer Pool을 효율적으로 사용하는 대표적인 방법에 대해 소개해드리겠습니다. 
- Buffer Pool 사이즈를 최대한 크게 설정합니다. Buffer Pool 사이즈가 클수록 디스크에서 페이지를 찾기 위한 page fault의 빈도수가 감소하기 때문입니다. 
- Buffer Pool을 여러 구역으로 나눠서 활용합니다. 여러 구역으로 나뉜 만큼 buffer pool에 대한 경합의 빈도가 감소합니다. 
- 나중에 읽을 확률이 높은 페이지를 미리 buffer pool에 읽어오는 기능을 활용합니다. 

Change Buffer 

Change Buffer(https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html)

InnoDB는 secondary index에 변경사항이 발생했을 때 이를 disk에 바로 반영하는 게 아니라 change buffer에 기록합니다. Secondary index를 포함한 페이지가 buffer pool에 로드됐을 때 change buffer에 저장된 secondary index의 변경사항이 buffer pool에 로드된 secondary index 데이터와 병합(merge)됩니다(Change Buffer에 저장된 데이터는 주기적으로 disk에 flush되기도 합니다). 이러한 방식을 사용하는 이유는 secondary index를 buffer pool에 로드하기 위한 비용과 관련이 있습니다. Primary key와 다르게 secondary index의 경우 디스크의 연속된 공간에 저장되지 않습니다. 따라서 secondary index를 buffer pool에 로드하기 위해서는 random I/O가 필요하기 때문에 비용이 큽니다. 

Adaptive Hash Index 

MySQL의 인덱스를 읽기 위해서는 B-Tree를 탐색해야 합니다. 자주 사용되는 index의 경우 해당 인덱스를 Hash Table에 저장하면 더 빠르게 접근할 수 있습니다. MySQL의 innodb_adaptive_hash_index 설정을 활용하면 adaptive hash index 기능을 사용할 수 있습니다. 

Log Buffer 

디스크의 로그에 기록하기 전 데이터를 보관하는 메모리 공간입니다. 로그 버퍼의 데이터는 주기적으로 디스크에 저장됩니다.

Log Buffer 설정
- innodb_flush_log_at_trx_commit: commit 시점에 log buffer을 flush 할지 여부를 결정 
- innodb_flush_log_at_timeout: flush 빈도수를 조절 

On-Disk Structures 

Tables && Indexs

InnoDB의 테이블과 인덱스는 디스크에 저장됩니다. Index의 경우 B-tree의 형태로 저장됩니다. 

Tablespace 

MySQL에 존재하는 다양한 종류의 tablespace에 대해 알아보겠습니다. 

• System tablespace: change buffer을 저장하기 위한 공간입니다. 
• File-Per-Table tablespaces: 하나의 테이블의 data와 index를 저장하기 위한 공간입니다. File system 상의 하나의 file에 저장됩니다. File-Per-Table tablespace를 사용했을 때 장단점은 링크를 참조해주세요. 
• General tablespaces: 공유된 tablespace입니다. 공통으로 사용하는 데이터를 저장하기 위해 사용됩니다. 
• Undo tablespaces: undo log를 저장하기 위한 공간입니다. 
• Temporary tablespaces: 임시로 생성되는 tablespace로 session temporary tablespaces와 global temporary tablespaces가 존재합니다. 

Double Write Buffer

Double write buffer는 buffer pool의 데이터가 디스크에 flush 되기 전에 별도로 데이터를 먼저 저장하기 위해 사용되는 공간입니다. 데이터가 디스크에 플러시 될 때 각 데이터는 제 위치에 저장이 돼야 하기 때문에 시간이 오래 걸립니다. 만약 중간에 데이터베이스 서버가 다운되면 변경사항이 부분적으로만 반영될 위험이 있습니다. Double Write Buffer을 활용하면 중간에 데이터베이스 서버가 다운되더라도 double write buffer의 데이터를 참고해서 실패한 지점부터 복원을 할 수 있습니다. 

Redo Log 

트랜잭션 완료 후 변경사항이 디스크에 반영되기 전에 데이터베이스 서버가 다운된 경우 redo log를 활용해서 복원할 수 있습니다. InnoDB는 트랜잭션이 commit되기 전 redo log에 해당 트랜잭션과 관련된 정보를 저장합니다. Redo log에 저장된 변경사항은 묶어서 한 번에 디스크에 flush 됩니다. 

Undo Logs

Rollback을 위해 사용되는 데이터가 저장되는 공간입니다. 해당 로그는 consistent read 작업을 위해 참조되기도 합니다(MVCC). 

 

InnoDB Locking 

InnoDB는 다양한 잠금 방법을 활용하는데요, 각각의 잠금에 대해 살펴보겠습니다. 

Shared and Exclusive Locks

트랜잭션이 특정 row를 읽을 때 shared lock을 획득하고 쓰기 작업을 할 때 exclusive lock을 획득합니다. 특정 row에 shared lock이 걸려있는 경우 다른 트랜잭션에서 읽기를 위한 shared lock을 획득할 수 있습니다. 만약 row에 exclusive lock이 걸려있는 경우 다른 트랜잭션은 어떠한 잠금도 획득할 수 없습니다. 

Intention Locks 

Intention lock은 어떤 형태의 잠금을 획득할 것인지, 그리고 그 의도(읽기 또는 쓰기)가 무엇인지 표현할 수 있는 일종의 잠금입니다. 동작원리는 intention lock의 동작원리가 깔끔하게 잘 정리된 하단의 포스팅을 참고해주세요.

https://bako94.tistory.com/157

[Concurrency Control]Multiple Granularity Locking

Record Locks

특정 record에 대한 잠금을 획득하는 것입니다. 중요한 것은 잠금의 대상이 인덱스라는 점입니다. 만약 참조 가능한 인덱스가 없으면 clustered index를 사용합니다. 

SELECT * FROM t WHERE t.idx = 10 FOR UPDATE

Gap Locks

Gap locks

Gap locks는 인덱스 사이의 잠금입니다. 몇몇 transaction isolation level에서 일관성을 보장하기 위해서 사용합니다. Gap lock의 목적은 다른 트랜잭션이 특정 데이터를 인덱스 중간에 insert 하는 것을 방지하기 위해서 사용합니다. 

Next Key Locks 

Next key lock은 record lock과 잠금 대상 인덱스 이전 범위에 대한 gap lock 조합입니다. 

인덱스 파일에 10, 20, 30 값을 가진 인덱스가 존재한다고 가정하겠습니다. Next key lock은 다음과 같이 설정될 수 있습니다. 
( negative infinity, 10 ]
( 10, 20 ]
( 20, 30 ]
( 30, positive infinity )

InnoDB는 REPEATABLE_READ(default) isolation level에서 검색 또는 index scan 작업을 수행할 때 phantom rows 현상을 방지하기 위해서 next key lock을 활용합니다.

Insert Intention Locks 

Insert intention lock은 일종의 gap lock으로 데이터를 insert 하기 전에 획득하는 잠금입니다. Insert intention lock을 활용하면 다수의 트랜잭션이 동일한 index gap에 insert를 수행할 때 서로 같은 인덱스 위치에 데이터를 삽입하지 않는다면 트랜잭션끼리 서로 기다리지 않도록 할 수 있습니다. 

Auto-Inc Locks 

AUTO_INCREMENT가 설정된 칼럼을 가진 테이블에서 사용되는 잠금으로 테이블 수준의 잠금입니다. 하나의 트랜잭션에서 데이터를 삽입하고자 할 때 이전에 실행된 트랜잭션들이 해당 테이블에 데이터 삽입이 마무리됐는지 확인해야 합니다. innodb_autoinc_lock_mode 변수를 통해 Auto-Inc lock과 관련된 알고리즘을 설정할 수 있습니다. 

InnoDB Transaction Model 

InnoDB의 transaction model은 multi-versioning 데이터베이스와 two phase locking의 장점을 최대한 적용하고자 합니다. InnoDB는 row 단위로 잠금을 수행하며 consistent read를 위해서 별도의 잠금이 필요하지 않습니다. 

Transaction Isolation Levels 

InnoDB에서 각각의 transaction isolation level을 어떻게 지원하는지 살펴보겠습니다. 

REPEATABLE_READ 

InnoDB의 기본 isolation level입니다. REPEATABLE_READ가 설정된 트랜잭션에서 여러 번 SELECT를 하더라도 결과는 항상 동일합니다. 이는 InnoDB MVCC를 위해서 여러 버전의 데이터를 저장하기 때문에 가능합니다. 하단의 문서는 InnoDB가 어떻게 consistent nonlocking read가 가능한지 설명합니다. 

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.3 Consistent Nonlocking Reads

READ_COMMITTED 

트랜잭션은 commit이 완료된 데이터만 읽을 수 있습니다. REPEATABLE_READ와 다르게 동일한 트랜잭션에서 여러 번 SELECT를 하는 경우 결과가 달라질 수 있습니다. 이는 해당 트랜잭션이 다수의 SELECT를 수행하는 와중에 다른 트랜잭션이 데이터를 삽입하고 commit 하는 경우 발생할 수 있습니다. 

READ_UNCOMMITTED 

트랜잭션은 commit 되지 않은 데이터를 읽을 수 있습니다. 아무런 제약 없이 데이터의 변경사항을 즉각 확인하기 때문에 consistent read가 불가능합니다(dirty read). 

SERIALIZABLE

REPEATABLE_READ와 유사하지만 InnoDB에서 모든 SELECT 문장을 SELECT... FOR SHARE 구문으로 변경합니다(Autocommit이 OFF인 경우). 만약 Autocommit이 ON인 경우에 자동적으로 모든 SELECT 문장은 transaction 내부에서 실행됩니다. 

 

마무리 

이번 포스팅을 통해 MySQL의 기본 storage engine인 InnoDB에 대해 살펴봤습니다. 다양한 MySQL 기능이 InnoDB의 storage API를 활용해서 구현하는 것이기 때문에 InnoDB에 대한 이해는 중요합니다.