Real My SQL 8.0 읽고 공부하기 - ④-② InnoDB 스토리지 엔진 아키텍처

InnoDB 스토리지 엔진의 구조

 

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

 

InnoDB엔진의 모든 테이블은 프라이머리 키값 순서대로 디스크에 저장되며, 모든 세컨더리의 인덱스는 레코드의 주소 대신

프라이머리 키의 값을 논리적 주소로 사용한다. 

프라이머리 키가 클러스터링 인덱스이기 때문에 해당 키를 이용한 레인지스캔은 상당히 빠르다.

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

 

MyISAM엔진에서는 클러스터링 키를 지원하지 않아서 프라이머리 키와 세컨더리 인덱스와의 구조적 차이가 없다.

단지 유니크키를 가진 세컨더리 인덱스일 뿐이다.

 

 

---

 

2) 외래키 지원

 

MyISAM이나 MEMORY 엔진은 외래 키를 지원하지 않는다.

InnoDB에서 외래 키는 부모 테이블과 자식테이블 모두 해당 컬럼에 인덱스 생성이 필요하고 변경시에는 반드시 데이터가 있는지

체크하는 작업이 필요해서 잠금이 전파 될 수 있어 데드락이 발생할 확률이 높아진다.

 

foreign_key_checks 변수로 외래키에 대한 체크작업을 일시적으로 멈출 수 있다. (ON DELETE CASCADE, ON UPDATE CASCADE 무시)

반드시 연관관계의 데이터 정합성을 반드시 맞추고 활성화 시켜야한다. (고아가 발생)

 

 

---

 

3) MVCC(Multi Version Concurrency Control)

 

InnoDB는 언두로그를 이용해 잠금을 사용하지 않는 일관된 읽기를 제공한다.

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

 

예를들어..

INSERT 문이 실행되면 InnoDB 버퍼풀과 데이터파일(디스크) 에 데이터가 저장된다.

이후, 해당 레코드에 대해 UPDATE를 실행하면 

InnoDB의 버퍼 풀 - 새로운 값으로 수정

언두 로그 - 수정전 데이터를 언두 로그에 저장

데이터파일 (디스크) - 체크포인트 혹은 write 스레드에 의해 새로운값으로 수정되거나 아직 수정전 상태

로 된다.

만약 Commit이나 Rollback이 되지 않은 상태에서 다른 사용자가 해당 데이터에 대해 검색하면

트랜잭션 격리 수준에 따라

READ_UNCOMMITTED -> 버퍼풀의 변경된 데이터를 읽어서 반환

READ_COMMITTED 혹은 그이상(REPEATABLE_READ, SERIALIZEABLE) -> 언두 영역의 데이터를 읽어서 반환 한다.

이러한 과정을 MVCC라고 표현한다.

 

트랜잭션이 길어질 수록 예전버전의 데이터는 무한히 많아 질 수 있다.

커밋과 롤백에 따라 데이터도 다르게 될 수 있는데,

커밋 - 지금의 데이터상태를 영구적인 데이터로 변경한다,
언두영역의 백업데이터는 바로 사라지지 않으며 해당 데이터를 접근하는 트랜잭션이 존재하지않으면 삭제된다.

롤백 - 언두영역의 데이터를 버퍼 풀로 다시 복구 하고 언두 영역 데이터를 삭제한다.

 

 

---

 

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

 

MVCC를 통해 잠금을 걸지 않고 읽기작업을 수행한다.

격리 수준이 SERIALIZABLE이 아닌 경우 INSERT와 연결되지 않은 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이

잠금을 대기하지 않고 바로 실행된다.

단, 오랜시간 활성 상태인 트랜잭션으로 인해 언두로그가 삭제되지 못하고 유지되었기 때문에 서버가 느려지거나 문제가 발생할 수 있다.

이를 방지하기 위해 가능한 한 빠른 롤백이나 커밋을 해야한다.

 

 

---

 

5) 자동 데드락 감지

 

InnoDB엔진은 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리한다. 

데드락 감지 스레드를 통해 주기적으로 교착 상태에 빠진 트랜잭션들을 찾아 그중 하나를 강제 종료한다.

강제 종료 우선 순위는 트랜잭션 언두 로그의 양이며, 일반적으로 적게 가진 트랜잭션이 롤백의 대상이 된다.

 

innodb_table_locks 시스템 변수를 활성화 하면 엔진 내부의 레코드 잠금 만이 아닌 테이블레벨의 잠금을 감지할 수 있다.

 

데드락 감지 스레드 또한 innodb_deadlock_detect 변수를 통해 on/off 할 수 있는데, 데드락 상황이 발생하였을때

무한정 대기할 수 있다.

 

대신 innodb_lock_wait_timeout 변수를 활성화하여 일정 초이상 잠금을 획득하지 못하면 쿼리 실패와 에러를 반환 할 수 있다.

(기본값이 50초인데, 훨씬 낮은 값을 하는 것이 권장된다.) 

구글에서는 데드락 감지 스레드를 off하는 방식으로 변경하여 성능을 이끌어 냈다.

(만약 PK 또는 세컨더리 인덱스를 기반으로 매우 높은 동시성 처리를 요구하는 서비스가 있으면 데드락 감지 스레드를 비활성화해보자.)

 

 

 

---

 

6) 자동화된 장애 복구

 

InnoDB는 많은 복구 메커니즘이 탑재되어있고, 매우 견고해서 데이터 파일이 손상되거나 MySQL서버가 시작되지 못하는 경우가 거의 발생하지 않는데 , 디스크나 하드웨어 이슈로 스토리지 엔진이 자동 복구를 못 하는 경우가 발생할 수 있다.

InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작 될 때 항상 자동복구를 실행하는데, 이 단계에서 복구될수 없는 손상이 존재하면

자동복구를 멈추고 서버가 종료된다.

 

이러한 경우에 서버 설정파일에 존재하는 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 한다.

● InnoDB 로그파일 손상시 6으로 설정후 서버기동

● InnoDB 테이블의 데이터 파일 손상시 1로 설정하고 서버 기동

● 원인 파악이 어려울 시  1 -> -> 6으로 천천히 변경하면서 서버 기동한다. 

해당 값이 커질 수록 심각한 상황이어서 데이터 손실 가능성이 커지며 복구 가능성은 적어진다.

innodb_force_recovery가 0이 아닌 복구모드에서는 SELECT이외의 DML 쿼리는 실행할 수 없다.

 

만약 서버가 재기동 되고 InnoDB 테이블이 인식되면 mysqldump를 통해 백업하고 그 데이터로 MySQL서버의 DB와 테이블을 

다시 생성하는것이 좋다.

 

각 값에 대한 간략은 설명은 다음과 같다.

 

◆ 1 - SRV_FORCE_IGNORE_CORRUPT

테이블 스페이스 데이터 혹은 인덱스 페이지 손상을 무시하고 서버 기동한다.
mysqldump 혹은 SELETE INTO OUTFILE을 이용해 덤프해서 데이터베이스 다시 구축하는것이 좋다.

 

◆ 2 - SRV_FORCE_NO_BACKGROUND

메인 스레드를 시작하지 않고 (언두 로그 삭제 비활성화  Undo purge 중지) 서버 기동한다. 

 

◆ 3 - SRV_FORCE_NO_TRX_UNDO

커밋되지 않고 종료된 트랜잭션은 계속 그 상태로 남아 있게 하고 서버를 기동한다. 
mysqldump을 이용해 덤프해서 데이터베이스 다시 구축하는것이 좋다.

 

◆ 4 - SRV_FORCE_NO_IBUF_MERGE

인서트 버퍼(인덱스변경 작업을 저장)의 내용을 무시하고 서버를 기동한다.
실데이터와 무관한 인덱스 관련 부분이므로 테이블을 ㄷ머프하고 다시 데이터베이를 구축하면 손실없이 복구 된다.

 

◆ 5 - SRV_FORCE_NO_UNDO_LOG_SCAN

언두로그를 모두 무시하고 서버를 시작한다. 단, 이모드로 복구하면 서버가 종료되던 시점의 커밋되지 않았던 작업들도

모두 커밋된 것으로 처리된다. (잘못된 데이터가 남는다.)
mysqldump을 이용해 백업하고 데이터베이스를 다시 구착한다.

 

◆ 6 - SRV_FORCE_NO_LOG_REDO

리두 로그를 모두 무시한채로 서버가 시작되며, 커밋된 데이터가 있어도 리두로그에 기록되고 파일에 기록되지 않은 데이터는

모두 무시된다. (마지막 체크포인트 데이터) 이 때는 기존 InnoDB 리두 로그를 모두 삭제 혹은 백업하고 서버를 시작하자. (새로 생성해준다.)

mysqldump를 이용해 데이터를 모두 백업하고 서버를 재구축하자.

 

위의 수동 복구방법을 이용해도 서버가 시작되지 않으면 백업을 이용해 다시 구축하는 방법밖에 없다.

백업을 이용해 데이터베이스를 새로 구축하고 바이너리 로그를 이용해 최대한 장애 시점의 데이터를 복구 할 수 있다.

(만약 없다면 백업시점까지만..)

 

 

---

 

7) 버퍼 풀

 

디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이며, 쓰기 작업을 지연시켜 일괄작업으로 처리할 수 있게 하는

버퍼 역할도 같이 한다.

 

(1) 버퍼 풀 크기 설정

 

innodb_buffer_pool_size로 변경할 수 있으며 메모리가 8GB미만의 운영체제라면  50%정도만 버퍼 풀로 설정하고 나머지 메모리공간은

MySQL서버와 운영체제 , 다른 프로그램 용으로 확보한다. 만약 그 이상의 메모리크기가 된다면 50%시작해서 조금씩 올려가면서 

최적점을 찾고, 50GB 이상이면 15~30Gb 정도를 남겨두는 것으로 하자.

버퍼 풀 크기 변경은 크리티컬한 작업이므로 한가한 시점에 그리고 늘리는 것은 영향이 작지만 줄이는 것은 영향이 크므로 주의해야한다.

 

버퍼 풀을 동적으로 변경해야한다면 MySQL :: MySQL 8.0 Reference Manual :: 15.8.3.1 Configuring InnoDB Buffer Pool Size을

꼭 숙지하도록 하자.

 

버퍼 풀 또한 여러 인스턴스로 분리해서 관리할 수 있는데 innodb_buffer_pool_instances 변수를 이용하면 된다.

이는 버퍼 풀 전체를 관리하는 잠금으로 인해 내부 잠금 경합을 줄이기 위한 것이다.

 

기본값은 8개의 인스턴스로 초기화 되지만 1GB미만의 버퍼 풀 메모리 공간이면 1개만 생성된다. 40GB 이하 수준이면 8개를 유지하고

이보다 크다면 5GB당 1개꼴로 하는 것이 좋다.

 

(2) 버퍼 풀 구조

 

innoDB는 버퍼 풀 공간은 페이지 크기의 조각으로 쪼개는데 이 페이지 크기 조각을 관리하고 위해 다음과 같은 자료구조로 관리한다.

LRU(Least Recently Used) - LRU는 MRU LRU과 결합된 상태로 관리되는데 Old 서브리스트 영역은 LRU,

New 서브리스트는 MRU로 이해하면된다.

Flush List - 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티페이지)의 변경 시점 기준의 페이지 목록, 데이터 변경이 가해진

데이터는 이 플러시 리스트로 관리되다가 특정 시점에 디스크에 기록되어야 한다.

Free List - 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어있는 페이지 목록 , 사용자 쿼리로 새로운 데이터 조회시 사용된다.

 

---

스토리지 엔진에서 데이터를 찾는 과정

 

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사

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

  b. 해당 테이블의 인덱스(B-Tree) 를 이용해 버퍼 풀에서 페이지 검색

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

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

3. 버퍼 풀의 LRU 헤더 부분의 적재된 데이터가 읽히면 MRU 헤더 부분으로 이동한다.

(Read Ahead와 같이 대량 읽기의 경우 적재는 되지만 실제 사용되지 않으므로 MRU로 이동하지 않는다.)

4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했는지에 따라 Age를 부여해서 오래된 것을 제거하고

사용되면 나이를 초기화 시킨다.

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

 

---

 

(3) 버퍼 풀과 리두 로그

 

버퍼 풀의 크기를 키우는 것은 단순 캐시 기능만 향상 시키는 것인데, 버퍼링 기능을 향상시키려면 리두로그와의 관계를

먼저 이해해야한다.

InnoDB의 버퍼풀은 디스크에서 읽은 상태로 변경되지 않은 클린페이지와 INSERT,UPDATE,DELETE 명령으로 변경된 데이터를 가진

더티페이지가 있다. 더티 페이지는 언젠가는 디스크로 기록되긴하지만, 버퍼풀에 무한정 머무를 수 는 없다. 

 

데이터 변경이 발생하면 리두 로그파일에 기록되는데, 데이터변경이 계속 발생하게 되면 이 리두 로그파일에 기록된 로그 엔트리가

새로운 로그 엔트리로 덮어 씌인다. 따라서 전체 리두 파일에서 재사용가능한 공간과 재사용 불가 공간을 구분해야하는데,

재사용 불가능한 공간을 활성 리두 로그라고 한다.

 

리두 로그 파일의 공간은 계속 순환되어 재사용되지만 기록될때마다 로그 포지션이 계속 증가하는데,

이것을 LSN(Log Sequence Number)라한다.

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

그리고 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 된다.

(물론 공간은 계속 증가하기 때문에 체크포인트와 무관)

 

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

(활성 리두 로그 공간의 크기를 말한다.)

 

이러한 관계가 있어, 체크포인트 LSN보다 작은 LSN을 가진 리두 로그 엔트리는 디스크로 동기화 되어야한다.

버퍼풀의 크기가  100GB이하의 서버에서는 리두 로그 파일 크기를 5~10GB로 선택하고 조금씩 늘려가면서 최적값을 선택한다.

 

(4) 버퍼 풀 플러시

 

InnoDB는 버퍼 풀에서 디스크로 기록되지 않은 더티 페이지들을 성능상 악영향 없이 디스크에 동기화하기 위해 다음과 같이 2개의

플러시 기능을 백그라운드로 실행한다.

 

● 플러시 리스트 플러시

오래된 리두 로그 공간을 지우기 전에 버퍼 풀의 더티페이지가 먼저 디스크로 동기화되어야 하는데, 이때 엔진은 플러시리스트 플러시함수를

호출해서 오래전에 변경된 데이터페이지를 순서대로 디스크에 동기화 한다. 이 때 얼마나 많은 더티페이지를 한 번에 디스크에 기록하냐에

따라서 사용자에게 악영향을 받지 않으면서 부드럽게 처리된다.

 

더티페이지를 디스크로 동기화하는 스레드를 클리너 스레드라고하는데, 이 클리너 스레드 수와 버퍼 풀 인스턴스를 동일하게 맞추는 것을

권장한다.

 

버퍼 풀에 더티페이지가 많으면 많을 수록 디스크 쓰기 폭발(Disk IO Burst) 현상이 발생할 가능성이 높아진다.

일반적으로 더티페이지 비율이 90%넘어가면 디스크 쓰기를 하는데, 이를 방지하기 위해 엔진에서는 일정 수준 더티페이지가 발생하면

조금씩 디스크로 기록할 수 있게 한다. 만약 너무 많은 디스크쓰기 횟수가 발생하면 innodb_max_dirty_pages_lwm값을 조금씩 올리는 것이

좋다.

 

시스템 변수로 디스크 읽고 쓰기의 성능을 설정할 수 있는데, 이 기능을 사용하기 보다. 어댑티브 플러시 기능을 활용한다.

이 기능을 사용하면 리두 로그가 어느 속도로 증가하는지 분석해서 적절한 수준의 더티페이지가 버퍼 풀에 유지 될 수 있도록

디스크 쓰기를 실행한다.

 

마지막으로 더티페이지를 디스크에 기록할 때 디스크에 근접한 페이지 중에 더티페이지가 있다면 함께 묶어서 디스크에 기록할 수

있게 하는 기능이 있었는데 SSD 방식에서는 비활성모드로 유지하는 것이 좋다.

 

 

● LRU 리스트 플러시

 

LRU리스트에서 사용빈도가 낮은 데이터페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 하는데

이때 LRU 리스트 플러시 함수가 사용된다.

InnoDB엔진은 innodb_lru_scan_depth 변수에 의해 설정된 개수만큼 페이즈들을 스캔하면서 더티페이지를 디스크에 동기화한다.

클린 페이지는 Free 리스트로 페이지를 옮긴다.

 

 

(5) 버퍼 풀 상태 백업 및 복구

 

디스크의 데이터가 버퍼풀에 적재돼 있는 상태를 워밍업이라고 표현하는데, 이 상태에서는 다른 상황보다 몇십배의 쿼리 속도를

보일 수 있다.

버퍼 풀 덤프 및 적재 기능을 활용하면 셧다운하기전에 버퍼 풀의 상태를 백업할 수 있다.

이 작업은 수동으로 하기 보다 자동화하여서 서버를 셧다운 & 재가동 하는것이 좋다.

 

(6) 버퍼 풀의 적재 내용 확인

 

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

 

 

---

 

8) Double Write Buffer

 

더티페이지를 디스크에 기록할 때 일부만 기록되는 현상을 파셜 페이지 혹은 톤 페이지 라고 하는데, 이런 현상은 하드웨어 오작동 이나

시스템 비정상 종료등으로 발생할 수 있다.

이를 막기 위해 Double-Write 기법을 이용하는데, 디스크에 기록하기전에 해당 버퍼에 기록하고 이 후 스토리지엔진은

더티페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기 시작한다.

정상으로 기록이 완료되면 해당 버퍼의 내용은 필요없어지며, 만약 비정상 종료되었을 경우 DoubleWrite 버퍼와 데이터파일의 페이지를

비교해서 다른 내용을 가지고 있으면 버퍼의 내용을 데이터 파일로 복사한다.

 

데이터 무결성이 중요한 곳은 활성화를 하는데, 만약 성능을 위해 리두 로그 동기화 설정을 1이아닌 값(비동기화)으로 설정하였다면

비활성화 하는것이 좋다.

 

 

---

 

9) 언두 로그

 

스토리지엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 전 데이터를 별도로 백업하는데, 이렇게 백업하는 데이터를

언두 로그라고 한다. 다음 두 가지를 보장해준다.

 

◆ 트랜잭션 보장 - 트랜잭션 롤백시 변경전 데이터로 복구

◆ 격리 수준 보장 - 데이터 변경도중 다른 커넥션에서 데이터 조회시 변경전 데이터를 조회해야할 때

 

기존에는 한번 증가한 언두 로그가 다시 줄어들지 않아서 문제가 있었는데, 단지 디스크 사용량 뿐 아니라 백업할 때에도 그만큼 

복사를 해야한다는 사실이 존재했다.

현재 8.0 버전에서는 언두 로그를 돌아가면서 순차적으로 사용해 디스크 공간을 줄이기도 하며, 서버 자체적으로 사용공간을 

자동으로 줄여 줄 수도있다.

 

하지만 트랜잭션이 오래지속됨에 따라 언두로그가 계속 증가할 수 있는데 SHOW ENGINE INNODB STATUS \G 를 통해 언두로그 건수를

확인할 수 있다.

 

주의해야할 점은 A, B, C 트랜잭션이 있다고 했을때 A트랜잭션이 진행중이면 B와 C 트랜잭션 완료 여부와 상관없이 언두로그는 삭제되지

않으므로 주의해야한다.

 

언두로그는 innodb_undo_tablespaces 값에 따라 시스템 테이블스페이스에 저장하거나 로그파일로 저장(2인 경우)한다.

8.0부터는 항상 별도 로그 파일로 저장한다.

 

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

하나의 트랜잭션은 최대 4개까지 언두 슬롯을 사용한다.

만약 언두 로그 슬롯이 부족한 경우에는 트랜잭션이 시작할 수 없는 심각한 문제가 발생하기 때문에 언두로그 시스템 변수를 변경하거나

테이블을 동적으로 추가/삭제하여야 한다.

 

언두테이블스페이스 공간을 필요한 만큼만 남기고 운영체제로 반납하는 것을 Undo Tablespace truncate 라고한다.

이것에 대한 것은 자동 (퍼지 스레드로 Undo Purge)/ 수동(테이블 스페이스가 최소 3개이상은 되어야함) 모드 둘다 가능하다. (8.0 지원)

 

 

---

 

10) 체인지 버퍼

 

RDBMS에서 INSERT , UPDATE 될때는 데이터 파일을 변경하는 작업 뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트도 해야한다.

이 작업 또한 많은 자원을 소모하는데, 버퍼 풀에 있으면 바로 업데이트하고 그렇지 않으면 임시 공간에 저장 해두고 사용자에게

결과를 반환하는 형태로 성능을 향상 시킨다. 이 공간이 체인지 버퍼이다.

 

체인지 버퍼에 저장된 레코드 조각은 백그라운드 스레드에 의해 병합되는데 이 스레드를 체인지 버퍼 머지 스레드라고 한다.

innodb_change_buffering 변수를 통해 체인지 버퍼를 설정하고 insert만 할지, update만 할지, 전부를 할지 등을 설정할 수 있다.

 

체인지버퍼는 버퍼 풀의 25% 가 기본값이며 50%까지 사용할 수 있는데, 만약 INSERT, UPDATE등이 빈번하게 일어난다면 체인지 

버퍼를 늘리는 방안도 괜찮을 것이다.

 

 

---

 

11) 리두 로그 및 로그 버퍼

 

리두 로그는 트랜잭션의 ACID중에서 D(Durable)에 해당하는 영속성과 가장 밀접하게 관련되어있다.

서버는 데이터 변경내용을 로그로 먼저 기록한다. 그 이유는 데이터 파일에 랜덤 엑세스를 통해 읽기를 하려면 비용이 크기때문에

쓰기 비용이 낮은 리두로그를 활용하고, 비정상 종료가 발생하면 리두 로그 내용을 이용해 데이터 파일을 종료 전 상태로 복구한다.

이에 따라 InnoDB 버퍼 풀이나 로그 버퍼와 같은 자료구조를 가지고 있는 것이다.

 

비정상 종료시 데이터 파일은 두가지 상태를 가질 수 있는데

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

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

 

1번의 경우 리두로그에 저장된 데이터를 데이터 파일에 복사하면 되지만 2번의 경우 언두로그를 가지고 와서 복사한다.

물론 2번의 경우도 트랜잭션의 상태를 확인하기 위해 리두 로그를 읽어보아야 한다.

 

innodb_flush_log_at_trx_commit 변수를 통해 트랜잭션 커밋시의 디스크 기록 처리를 정할 수 있는데

처리 빈도수에 따라서 성능이 저하 될 수 있으므로 주의해서 설정하도록 한다. (단 DDL 사용시에는 바로 디스크로 동기화된다.)

 

리두 로그 파일의 크기를 적절히 설정해야 변경된 내용을 버퍼 풀에 모아두었다가 한번에 디스크에 기록 할 수 있다.

하지만 사용량이 많은 서버는 ACID 속성을 보장하는 수준에서 버퍼링하는데, 이 리두 로그 버퍼링에 사용되는 공간이 로그버퍼다.

 

 

1) 리두 로그 아카이빙

 

8.0부터 지원하는 기능으로 리두 로그를 아카이빙 할 수 있는 기능이다. 데이터 변경이 많은 경우 리두 로그 내용을 백업 공간에 

복사되기전에 덮어 씌일수 있는데, 이러한 것을 방지하게 해준다.

DO innodb_redo_log_archive_start('backup', '230820');

을 하면 아카이빙을 시작할 수 있다.

단, 아카이빙된 리두 로그를 쓰려면 커넥션을 그대로 유지하고 종료되면 명령어를 이용해서 정상적으로 종료해야한다.

 

 

2) 리두 로그 활성화 및 비활성화

 

리두 로그는 장애 발생했을 때 트랜잭션을 복구하기 위해 항상 활성화 되어있고, 데이터 파일과 달리 리두 로그는 디스크로  즉시 기록된다.

8.0부터 리두로그를 비활성화 할 수 있게 되었는데, 데이터 복구 혹은 대용량 데이터 적재시 비활성화해서 적재시간을 단축시킬 수 있다.

 

비활성화 및 작업 이후 꼭 다시 활성화하는 것을 잊으면 안된다. 그렇게 되면 비정상 종료시 복구할 데이터 시점이 체크포인트 시점으로만

될 수 있다. 

 

 

 

---

 

12) 어댑티브 해시 인덱스

 

InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다. (innodb_adaptive_hash_index)

해시 인덱스는 인덱스 키값 과 데이터 페이지 주소 쌍으로 관리된다.

어댑티브 해시 인덱스는 내부 잠금(세마포어) 경합을 줄이기 위해 파티션 기능을 제공하는데, 어댑티브 해시 인덱스가 도움이 된다면

이 수를 늘리는것도 도움이 된다.

 

마치 캐싱처럼 작동되어서 좋게만 느껴지지만 사용하는 곳에 따라 성능이 다르다. 
아래와 같은 경우 사용하기 좋다.

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

● 동등 조건 검색 (동등 비교 OR IN연산자) 많은 경우

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

 

아래 같은 경우는 사용하기에는 별로이다.

● 디스크 읽기가 많은 경우

● 특정 패턴의 쿼리가 많은 경우 (JOIN, LIKE 패턴 검색)

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

 

이는 사실 기존 캐시가 사라진 이유와 비슷하게 DDL에 의해 영향도 가고, 키값 서칭도 필요하고 등등 안쓰느니만 못한 경우가 생긴다.

SHOW ENGINE INNODB STATUS\G

를 통해 확인 할 수 있는데,

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

만약 0 hash searches/s 가 나오면 어댑티브 해시 인덱스를 사용하지 않고 있다는 것이며

1.03 / (1.03 + 2.64)  = 0.28 를 통해 히트율을 검사 해볼 수 있는데, 이 정도의 히트율이라면 비활성화하는 것이 좋다.

(메모리 사용량이 높다면)

 

 

 

 

---

 

13) InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교

 

기존 까지는 MyISAM이 기본 스토리지 엔진으로 사용되었지만, 8.0부터는 시스템 테이블이 InnoDB로 교체되면서 

대부분의 모든 기능이 InnoDB 스토리지 엔진으로 넘어가게되었다. 지금은 호환을 위해 남겨둔 정도이다.

 

MyISAM이 InnoDB 보다 나은 점이 없어져서 사라질 예정이고

MEMORY엔진 메모리에서 처리하기 때문에 단일 처리 성능은 뛰어나지만  동시 처리 성능이 InnoDB보다 떨어진다 (테이블 락)

 

심지어 임시테이블 생성용도로 사용했던 MEMORY엔진은 TempTable 스토리지 엔진이 대체되어 사용되고 있다. (가변 길이 타입 컬럼 지원)