Real MySQL 8.0 (신판)을 4장 아키텍처 InnoDB 스토리지 엔진 아키텍처를 정리한다.
자세한 내용이 궁금하면 책을 읽어보는 걸 권장한다.
Real MySQL 8.0 (1권) - YES24
『Real MySQL 8.0』은 『Real MySQL』을 정제해서 꼭 필요한 내용으로 압축하고, MySQL 8.0의 GTID와 InnoDB 클러스터 기능들과 소프트웨어 업계 트렌드를 반영한 GIS 및 전문 검색 등의 확장 기능들을 추가로
www.yes24.com
InnoDB 스토리지 엔진 아키텍처
InnoDB는 MySQL 스토리지 엔진 가운데 가장 많이 사용되며, 엔진 중에서 거의 유일하게 레코드 기반 잠금을 제공한다. 그 덕분에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.
프라이머리 키(PK)에 의한 클러스터링
InnoDB의 모든 테이블은 PK를 기준으로 클러스터링되어 저장된다. 이는 PK 값의 순서대로 디스크에 저장되며, 모든 세컨더리 인덱스는 레코드의 주소 대신 PK의 값을 논리적인 주소로 사용한다.
다른 스토리지 엔진인 MyISAM은 클러스터링 키를 지원하지 않으며, PK를 포함한 모든 인덱스는 물리적인 레코드의 주소 값(ROWID)을 가진다.
외래 키 지원
InnoDB 에서 지원하기 때문에 MyISAM 혹은 MEMORY 테이블에서는 사용할 수 없다. 외래 키는 운영의 불편함(전파로 인한 데드락 발생) 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우가 있는데, 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다.
외래 키가 생성되어 있을 경우 foreign_key_checks 변수를 OFF로 설정하면 외래 키 체크 작업을 멈출 수 있다.
다만, 해당 변수 범위는 GLOBAL, SESSION 둘 다 사용되기 때문에 현재 세션에서만 멈추게 해야 한다.
SET foreign_key_checks=OFF;
SET SESSION foreign_key_checks=OFF;
만약에 SESSION을 명시하지 않으면, 자동적으로 현재 세션의 설정만 변경하도록 되어있기 때문에 위의 명령어 2가지는 같은 효과를 낸다.
MVCC(Multi Version Concurrecy Control)
MVCC는 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다.
MEMBER라는 테이블을 생성하고 id, name, area 값으로 12, 홍길동, 서울을 넣으면 데이터베이스 상태는 아래와 같다.
해당 레코드의 값을 UPDATE 진행하면 데이터베이스 상태는 다음과 처럼 변경된다.
UPDATE member SET area='경기' WHERE id=12;
UPDATE가 실행되면 커밋 실행 여부에 상관없이 InnoDB의 버퍼풀은 입력된 '경기'로 업데이트되고, 이전 값은 언두 로그로 복사되어 존재한다.
그럼 해당 레코드가 COMMIT, ROLLBACK 되지 않은 상태에서 다른 사용자가 해당 레코드를 조회하면 어디서 데이터가 조회될까?
이는 MySQL 시스템 변수에 설정된 격리 레벨에 따라 달라진다.
-- 격리 레벨을 확인하는 방법
SELECT @@GLOBAL.transaction_isolation;
SELECT @@SESSION.transaction_isolation;-- 격리 레벨 변경하는 방법
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;격리 수준이 READ UNCOMMITTED 경우에 다른 사용자는 InnoDB 버퍼풀에 존재하는 변경된 값인 '경기'를 조회하고
나머지는 COMMIT이 되기 전의 값인 '서울'을 조회하게 된다.
이렇게 MVCC는 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보이는지 여러 가지 상황에 달라지는 구조다. (만약 COMMIT이 되지 않고 변경되는 데이터가 많아지면 언두 로그에 보관되는 데이터는 점점 늘어나게 된다.)
잠금 없는 일관된 읽기(Non-Locking Consistent Read)
InnoDB 스토리지 엔진의 MVCC 덕분에 잠금 없이 읽기 작업이 가능하다. 격리 수준이 SERIALIZABLE이 아니라면 순수 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업에 관계없이 잠금을 대기하지 않고 바로 실행된다. 이를 '잠금 없는 일관된 읽기'라고 하며, InnoDB에서는 언두 로그를 사용한다.
자동 데드락 감지
InnoDB는 내부적으로 잠금 교착 상태를 체크하는 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다.
아래는 잠금 대기 상태를 볼 수 있는 쿼리다.
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
information_schema.innodb_trx를 따로 구분한 이유는 information_schema.innodb_lock_waits 에서 제공하는 대기 상태와 잠금 상태의 트랜잭션 id를 구분해서 사용하기 때문이다.
간단하게 sys 스키마에서 제공하는 뷰를 이용하는 방법도 있다.
SELECT
waiting_trx_id,
waiting_pid,
waiting_query,
blocking_trx_id,
blocking_pid,
blocking_query
FROM sys.innodb_lock_waits;InnoDB는 이를 활용해서 교착 상태에 빠진 트랜잭션 중에서 하나를 강제로 종료하는데, 트랜잭션마다 가진 언두 로그양이 적은 걸 기준으로 롤백한다. 언두 로그가 적다는 것은 부하가 적다는 의미다.
다만 동시 처리 스레드가 많으면 데드락 감지 서비스가 느려지는데, 이는 데드락 감지 스레드가 목록 검사를 위해 잠금 테이블에 새로운 잠금을 걸기 때문이다. 이 경우 많은 CPU 자원을 소모할 수 있다.
아래는 데드락 관련된 시스템 변수다.
- innodb_deadlock_defect : 데드락 감지를 제어한다. 기본 값 ON이면 감지하고 OFF 하면 멈춘다.
- innodb_lock_wait_timeout : 기본 값 50초로 데드락 상황이 해당 시간을 넘기면 자동으로 요청 실패를 반환하게 한다.
- innodb_print_all_deadlocks : 데드락이 발생할 InnoDB가 모든 데드락 정보를 로그로 출력할지 정하는 변수로 기본 값은 OFF다.
자동화된 장애 복구
InnoDB에는 여러 가지 장애 복구 기능이 존재하고 서버가 실행될 때 자동으로 수행한다. 만약에 복구할 수 없는 손상이 존재한다면 MySQL은 종료가 된다. 종료가 되면 손상된 파일에 따라 설정 파일의 innodb_force_recovery 시스템 변수를 설정해서 시작해야 한다.
- 로그 파일이 손상되면 6으로 설정하고 기동
- 데이터 파일이 손상되면 1로 설정하고 기동
- 알 수 없다면 1 ~ 6까지 차례대로 설정하고 기동 한다.
innodb_force_recovery의 값이 커질수록 복구 가능성은 낮아진다.
숫자는 0 ~ 6까지 존재하며, 0을 제외한 숫자는 SELECT 구문을 제외하고 INSERT, UPDATE, DELETE를 사용할 수 없다.
다음은 숫자의 의미다.
- 0 : 기본값으로 복구 방법이 아닌 일반적인 방법으로 실행한다.
- 1(SRV_FORCE_IGNORE_CORRUPT) : 테이블 스페이스의 데이터나 인덱스 페이지의 오류를 무시하고 서버를 실행한다.
- 2(SRV_FORCE_NO_BACKGROUD) : 백그라운드 메인 스레드를 실행하지 않고 서버를 실행한다. 메인 스레드가 언두 데이터를 삭제하는 과정에 발생한 에러라면 이 모드를 이용한다.
- 3(SRV_FORCE_NO_TRX_UNDO) : 커밋되지 않고 종료된 트랜잭션을 그대로 유지하면서 서버를 실행한다.
- 4(SRV_FORCE_NO_IBUF_MERGE) : InnoDB가 인서트 버퍼를 무시하고 강제로 서버를 실행한다. 인서트 버퍼는 실제 데이터가 아닌 인덱스 관련된 부분이다.
- 5(SRV_FORCE_NO_UNDO_LOG_SCAN) : 서버가 종료된 시점의 커밋 안된 작업도 모두 커밋처리해서 실행하므로 잘못된 데이터를 남긴다.
- 6(SRV_FORCE_NO_LOG_REDO) : 마지막 체크포인트 시점의 데이터로 실행한다.
InnoDB 버퍼풀
버퍼풀은 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간으로, I/O 작업을 줄여주는 역할을 한다.
버퍼 풀의 크기 설정
버퍼 풀의 크기는 메모리 크기에 따라 운영체제와 각 클라이언트 스레드를 고려해서 설정이 필요하다.
- 8GB 미만 : 50%만 버퍼풀로 설정하고 나머지는 운영체제 등에 사용하게 한다.
- 8GB 이상 : 50%로 시작해서 천천히 비율을 올리면서 최적점을 찾는다.
- 50GB 이상 : 대략 15~30GB를 제외하고 버퍼풀로 할당하면서 찾는다.
다음은 버퍼풀에 관련된 변수다.
- innodb_buffer_pool_size : 버퍼 풀의 크기를 지정한다. 기존보다 작하면 서비스에 영향도가 높다. 변경은 크리티컬 한 영향을 끼치기 때문에 한가할 때 해야 한다.
- innodb_buffer_pool_instances : 버퍼 풀 인스턴스 개수를 지정한다. 기본 값은 8이지만 메모리 크기가 1GB 미만이면 1개이고, 40GB 이면 8개 유지한다. 인스턴스당 5GB 설정이 좋다.
- innodb_old_blocks_time : 기본값은 1000(초)이며, 버퍼 풀의 데이터가 이 시간을 넘어가면 제거된다.
그 외에도 여러 가지 변수가 존재한다.
버퍼 풀의 구조
버퍼 풀은 메모리 공간을 페이지 크기(innodb_page_size 설정값)에 따라 쪼개어 데이터를 저장한다. 이 버퍼 풀을 3가지 자료 구조로 관리하는데 다음과 같다
- LRU(Least Recently Used) List : 한번 읽어 온 데이터를 최대한 유지해서 디스크 읽기를 최소화한다.
- Flush List : 디스크로 동기화되지 않은 더티 페이지의 변경 시점 기준으로 목록을 관리한다.
- Free List : 비어있는 목록으로 디스크의 페이지를 읽어올 경우 사용된다.
버퍼 풀과 리두 로그
버퍼 풀은 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링 두 가지 용도가 있는데, 버퍼 풀의 메모리 공간은 데이터 캐시 기능만 연관되어 있다. 쓰기 버퍼링 성능 향상을 이해하려면 버퍼 풀과 리두 로그 관계를 알아야 한다.
버퍼 풀에는 변경된 데이터 공간인 더티 페이지가 존재하는데 디스크 상태와 다르기 때문에 언젠가는 디스크로 기록되어야 할 데이터라 버퍼 풀에 계속 머무를 순 없다.
InnoDB에서 리두 로그는 고정 크기 파일을 순환 고리처럼 사용한다. 즉, 기존의 엔트리를 새로운 엔트리로 덮어 쓰인다. 순환되는 리두 로그를 구분하기 위해서 로그 포지션이 존재하는데, 매번 기록될 때마다 계속 증가된 값을 갖아서 LSN(Log Sequence Number)라고 한다. 그리고 InnoDB는 주기적인 체크포인트 이벤트로 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화한다.
*언두(Undo) 로그와 리두(Redo) 로그의 차이점.
언두 로그는 변경 전 데이터를 보관하는 장소로 롤백이 될 경우 이전 데이터로 복구한다.
반대로 리두 로그는 데이터 변경 이력 을 로그 파일로 보관하고 문제가 생길 경우 해당 파일을 통해 데이터를 복구한다.
버퍼 풀 상태 백업 및 복구
InnoDB의 버퍼 풀은 쿼리 성능에 밀접되어 있다. 버퍼 풀의 데이터가 적재되어 있으면 디스크를 읽지 않아도 처리되는데, 서버를 재시작하면 처리 성능이 1/10도 안되는 걸 볼 수 있다. 버퍼 풀에 데이터가 적재되어 있는 걸 워밍업이라 한다. MySQL 5.6 버전부터는 버퍼 풀 덤프 및 적재 기능이 추가되었다.
-- 서버 다운전에 버퍼 풀 백업 여부
SHOW VARIABLES LIKE 'innodb_buffer_pool_dump_now';
-- 서버 시작 후 백업된 버퍼 풀 상태 복구 여부
SHOW VARIABLES LIKE 'innodb_buffer_pool_load_now';그 외에 관련된 변수가 몇가지 더 존재한다.
버퍼 풀의 백업은 데이터 디렉터리에 ib_buffer_pool 파일로 생성되는데, 파일 크기는 버퍼 풀의 크기에 비해 상당히 작은데 메타 데이터의 정보만 가져와서 저장하기 때문이다.
만약 복구 중에 너무 오래걸려서 중단하고 서비스를 시작하려면 innodb_buffer_pool_load_abort 변수를 이용하면 된다.
SET GLOBAL innodb_buffer_pool_load_abort = ON;
버퍼 풀의 적재 내용 확인
MySQL 8.0 부터 추가된 innodb_cached_indexes 테이블을 통해 테이블의 인덱스별로 버퍼 풀의 적재상황을 볼 수 있게 되었다.
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;
Double Write Buffer
리두 로그는 변경된 내용만 기록하는데 더티 페이지에서 디스크 파일로 플러시 될 때 하드웨어 오작동 등 문제가 생기면 일부분만 기록되는 문제가 생긴다. 데이터 무결성을 위해 Double Write(이중 쓰기)가 사용되는데, Double Write는 데이터를 디스크의 두 곳에 기록한다는 의미다.
Double Write Buffer는 InnoDB가 디스크에 데이터를 기록할 때 사용되는 버퍼로 데이터 페이지가 디스크에 기록되기전에 버퍼에 복사하는 곳이다.
*Double Write와 리두 로그 차이점.
Double Write Buffer는 InnoDB가 데이터를 디스크에 기록하기 전에, 데이터 먼저 기록하는 곳으로 디스크에 데이터를 안전하게 기록하게 보호한다.
리두 로그는 변경 사항(트랜잭션)을 로그에 기록하는 곳으로 문제가 생기면 해당 파일로 복구한다.
언두 로그
트랜잭션과 격리 수준을 보장하기 위해 DML(insert, update, delete)로 변경되기 이전 버전의 데이터를 별도로 백업하는 곳을 언두 로그라고 한다. 주로 2가지 용도로 사용된다.
- 트랜잭션 보장 : 트랜잭션이 롤백되면 언두 로그에 백업해둔 이전 데이터를 이용한다.
- 격리 수준 보장 : 데이터 변경 중에도 다른 커넥션이 접근하면 트랜잭션 격리 수준에 맞게 언두 로그 데이터를 반환한다.
언두 테이블스페이스(Undo Tablespace) 관리
언두 로그가 저장되는 공간을 언두 테이블스페이스라고 한다.
하나의 언두 테이블스페이스는 1개 ~ 128개의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯을 가진다. 언두 슬롯이 남는건 문제가 없지만 언두 슬롯이 부족해지면 트랜잭션을 시작할 수 없는 문제가 생긴다.
하나의 롤백 세그먼트는 InnoDB 페이지 크기를 16바이트로 나눈 값의 개수만큼 언두 슬롯을 가진다.
ex) 16KB 페이지라면 롤백 세그먼트는 1024개 언두슬롯을 가지며, 하나의 트랜잭션에 사용되는 언두 슬롯은 INSERT, UPDATE, DELETE에 따라 최대 4개의 언두 슬롯을 사용한다.
하나의 트랜잭션이 대략 2개 정도의 언두 슬롯을 사용한다는 가정하에 트랜잭션의 개수는 아래 수식으로 예측할 수 있다
최대 동시 트랜잭션 = (InnoDB 페이지 크기) / 16 * innodb_rollback_segments(롤백 세그먼트 개수) * innodb_undo_tablespaces(언두 테이블 스페이스 개수) / 하나의 트랜잭션이 사용할 언두 슬롯 개수
-> 131072 = 16 * 1024 / 16 * 128 * 2 / 2
대략 131072개 정도의 트랜잭션이 동시 처리된다.
체인지 버퍼(Change Buffer)
상태가 변경되면 데이터 파일뿐만 아니라 인덱스 업데이트를 위해 디스크를 읽는 작업이 필요해서 자원소모가 크다.
InnoDB는 버퍼 풀에 있다면 바로 업데이트를 수행하지만 디스크를 읽어야한다면 임시 저장 공간에 저장해두고 사용자에게 결과를 반환하는 형식으로 성능을 향상하는데 이 공간을 체인지 버퍼라 한다.
innodb_change_buffering 변수를 사용하면 INSERT, DELETE, UPDATE 작업별로 체인지 버퍼를 적용할 수 있다.
- all : 모든 인덱스 관련 작업을 버퍼링
- none : 버퍼링 안함
- inserts : 인덱스에 새로운 아이템을 추가하는 작업만 버퍼링
- deletes : 인덱스에서 기존 아이템을 삭제하는 작업만 버퍼링
- changes : 인덱스에서 추가하고 삭제하는 작업만(inserts + deletes) 버퍼링
- purges : 인덱스 아이템을 영구적으로 삭제하는 작업만 버퍼링(백그라운드 작업)
리두 로그 및 리두 버퍼
리두 로그는 MySQL 서버가 비정상적인 종료를 했을 때 데이터를 잃지 않게 해주는 안전장치다.
비정상적으로 종료되면 InnoDB 엔진은 두 가지 종류의 일관되지 않은 데이터를 가진다.
- 1. 커밋됐지만 데이터 파일에 기록되지 않은 데이터
- 2. 롤백됐지만 데이터 파일에 이미 기록된 데이터
1번의 경우 리두 로그를 다시 복사하면 끝난다.
2번의 경우 언두 로그를 통해 복구하지만 리두 로그를 사용한다. 리두 로그 통해 변경, 롤백, 트랜잭션 실행 중간이였는지 판단한다.
innoDB는 리두 로그를 디스크에 기록하는 걸 제어하는 innodb_flush_log_at_trx_commit 변수를 제공하는데 다음과 같은 값을 가진다.
- 0 : 1초에 한번씩 리두 로그를 디스크로 기록하고 동기화한다.
- 1 : 매번 트랜잭션이 커밋될 때 마다 디스크로 기록하고 동기화한다.
- 2 : 매번 트랜잭션이 커밋될 때 마다 디스크로 기록하지만 동기화는 1초마다 실행한다.
리두 로그 아카이빙
MySQL 8.0부터 리두 로그 아카이빙 기능이 추가되었다. innodb_redo_log_archive_dirs 변수를 통해 저장할 디렉터리를 설정하는데 설정 방식은 아래와 같다.
SET GLOBAL innodb_redo_log_archive_dirs='label1:directory_path1[;label2:directory_path2;…]';
설정이 되면 UDF(사용자 정의 함수)를 통해 아카이빙을 실행한다.
DO innodb_redo_log_archive_start('backup', '20230322');첫번째 식별자는 디렉터리에 대한 레이블이고, 두번째 식별자는 서브디렉터리의 이름이다.
만약에 권한이 없는 등 여러 에러가 발생하면 가이드를 참고하자
아카이빙 중지는 단순하게 innodb_redo_log_archive_stop을 사용하면 된다.
DO innodb_redo_log_archive_stop();아카이빙이 중단되면 아카이빙 파일은 사용자가 수동으로 삭제한다. 만약 비정상적으로 세션이 종료되면 아키이빙 파일은 자동으로 삭제된다.
어댑티브 해시 인덱스
어댑티브 해시 인덱스는 B-Tree 검색 시간을 줄여주는 기능이다.
자주 읽히는 데이터 페이지의 키 값으로 해시값을 만들어서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다.
해시 인덱스는 '인덱스 키 값'과 키 값이 저장된 '데이터 페이지 주소'의 쌍으로 관리되는데, 인덱스 키 값은 'B-Tree 인덱스의 고유 번호(id)와 B-Tree 인덱스의 실제 키 값' 조합으로 생성된다.
어댑티브 해시 인덱스를 이용해서 성능 향상을 볼 수 있는 경우는 다음과 같다.
- 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 적을 경우)
- 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
- 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
다음과 같은 경우는 성능향상에 도움이 되지 않는다.
- 디스크 읽기가 많은 경우
- 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
- 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
또한 테이블 삭제작업의 경우 해당 테이블의 데이터 페이지가 저장된 어댑티브 해시 테이블의 인덱스도 모두 삭제되는 등 자원 소모가 크다.
'서적 > Real MySQL 8.0' 카테고리의 다른 글
| MySQL - 엔진 아키텍처 (0) | 2023.03.15 |
|---|