Chapter 04.02 InnoDB 스토리지 엔진 아키텍처(architecture)

4.2  InnoDB 스토리지 엔진 아키텍처(architecture)

• InnoDB는 MySQL의 스토리지 엔진 가운데 가장 많이 사용된다.
• MySQL 스토리지 엔진 중 유일하게 레코드 기반의 잠금을 제공한다.
  
  • 동시성 처리 가능하고 안정적, 성능 뛰어나다.

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」

 

• cf) 클러스터링(Clustering)이란?
  • 하나의 데이터베이스를 여러 개의 서버로 구축하는 것을 말한다.
  • 1개의 서버에 데이터베이스를 이용할 경우, 서버가 다운될 경우 서비스가 다운되기 때문에 클러스터링을 이용한다.

 

4.2.1 프라이머리 키(Primary Key)에 의해 클러스터링

• InnoDB의 모든 테이블의 PK를 기준으로 클러스터링(Clustering)되어 저장된다.(PK = 클러스터링 인덱스)
• PK 순서대로 디스크에 저장된다. 세컨더리 인덱스는 레코드 주소 대신에 pk 값을 논리적인 주소로 사용한다. 
• ??

 

 

4.2.2 외래 키 지원 

•  외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능이다.
  • MyISAM이나 MEMORY 테이블에서는 사용 불가능
• 외래 키는 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 있지만 개발 환경의 데이터베이스에서는 가이드 역할을 할 수도 있다.
• SET session foreign_key_checks = OFF;
  • 외래 키 체크를 해제한다고 해서 부모/자식 테이블 간의 관계가 깨진 상태로 유지된다는 것을 의미하지는 않는다.

 

  Ex) 외래 키 관계에 대한 체크 작업을 일시적으로 멈추기

SET foreign_key_checks=OFF;

 

 

4.2.3 MVCC(Multi Version Concurrency Control, MVCC, 다중 버전 동시성 제어)

• 일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 지원하는 동시성 제어 방식이다.
• 여러 사용자가 데이터베이스로 동시에 접근 가능하도록 하고 프로그래밍 언어에서 트랜잭셔널 메모리를 구현한다.
  • 만약 동시성 제어가 없다면 데이터는 일관성이 없어질 것이다.
• MVCC의 목적: 잠금을 사용하지 않는 일관된 읽기를 제공한다.
• 멀티 버전이란?
  • 하나의 레코드에 대해 여러 개의 버전이 동시에 관리되는 것을 말한다.
• InnoDB는 언두 로그(Undo log)를 이용해서 MVCC를 구현한다.
  • cf) 언두(Undo) 영역 (언두 로그): UPDATE, DELETE 문장으로 데이터를 변경하기 전의 데이터를 보관하는 곳을 말한다.
  • 용도 1) 실행 취소 로그 레코드의 집합으로 Transaction 실행 후, 롤백 시 언두 로그를 참조해 이전 데이터로 복구할 수 있도록 로깅해놓은 영역이다.
  • 용도 2) 트랜잭션의 격리수준을 유지하면서 높은 동시성을 제공하기 위해 사용된다.

 

 

  Ex) 격리 수준이 READ_COMMITTED 일 때, MySQL 서버에서 InnoDB 스토리지 엔진을 사용하는 테이블의 데이터 변경을 어떻게 처리할까?

<hide/>

CREATE TABLE member(
	m_id INT NOT NULL,
    m_name VARCHAR(20) NOT NULL, 
    m_area VARCHAR(100) NOT NULL,
    PRIMARY KEY (m_id),
    INDEX ix_area (m_area)
);

INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울');
COMMIT;

• INSERT 문이 실행된 후의 상태

Inno

 

UPDATE member SET m_area='경기' WHERE m_id = 12;

• 아래 그림은 update 문이 실행될  때의 처리 절차를 나타낸다.
  • UPDATE 문장이 실행되면 커밋 실행 여부와 관계없이 InnoDB 버퍼풀은 새로운 값이 "경기"로 업데이트 된다.
  • 디스크의 데이터 파일에는 체크포인트나 InnoDB 의 write 스레드에 의해 새로운 값으로 업데이트돼 있을 수돌 있고 아닐 수도 있다.(그렇지만, ACID를 보장하기 때문에 데이터 파일은 InnoDB 버퍼 풀의 상태와 동일하다고 할 수 있다.)

 

  Ex) Commit이나 RollBack이 되지 않은 상태에서 다른 사용자가 작업중인 레코드를 조회("SELECT * FROM member WHERE m_id = 12;")하면 어디에 있는 데이터를 조회할까?

• MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준(Isolation level)에 따라 다르다.
• (1) 격리 수준이 READ_UNCOMMITTED 일 때:  InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다. (데이터가 커밋되지 않았어도 변경된 데이터를 읽어서 반환한다.)
• (2) 격리 수준 READ_COMMITTED 또는 그 이상의 격리 수준(REPEATABLE_READ, SERIALIZABLE)인 경우에는 아직 커밋되지 않았기 때문에 InnoDB 버퍼 풀이나 데이터 파일에 있는 내용 대신 변경되기 이전의 내용을 보관하고  있는 Undo 영역의 데이터를 반환한다.
• 이러한 과정을 MVCC라고 한다.

??????????????????????????????????

 

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

• InnoDB 스토리지 엔진은 MVCC 기술을 이용해서 잠금을 걸지 않고 읽기 작업을 수행한다. 
• 잠금을 걸지 않아서 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 읽기 작업이 가능하다. 
• 특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다. => "잠금 없는 일관된 읽기"
• InnoDB에서는 변경되기 전의 데이터를 읽을 때 언두 로그를 이용한다.
• 오랜 시간동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 수 있는데 이러한 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야하기 때문에 발생하는 문제이다.
  • 따라서 트랜잭션이 시작됐다면 가능한 한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.

 

변경중인 (COMMIT 전) 레코드 읽기

 

 

4.2.5 자동 데드락 감지

• InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지  않았는지 체크하기 위해 잠금 대기 목록을 그래프(wait for list)형태로 관리한다.
  • InnoDB 스토리지 엔진이 가지고 있는 데드락 감지 스레드가 주기적으로  그래프를 검사해서 교착 상태에 빠진 트랜잭션을  찾아서 그 중 하나를 강제 종료 시킨다.
  • 트랜잭션의 언두 로그 양을 기준으로 강제 종료 여부를 판단한다.  
  • 언두 로그 레코드가 적은 트랜잭션은 롤백의 대상이 된다.
• innodb_table_locks: 활성화하면 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 MySQL 엔진에서 관리되는  테이블 레벨의 잠금까지 감지할 수 있다.  활성화하는 걸 권장.
• innodb_deadlock_detect: "데드락 감지 스레드"의 작동 여부를 선택할 수 있는 시스템 변수이다.
  • ON(활성화 시킨 경우): 데드락 상황에서 일정 시간 지난 다음 자동으로 요청 실패하고 에러 메시지를 반환한다.
  • OFF로 설정할 경우: 데드락 감지 스레드 작동이 꺼짐 => InnoDB 스토리지 엔진 내부에서 2개 이상의 트랜잭션이 상대방이 가진 잠금을 요구하는 상황(데드락)이 발생해도 아무도 중재하지 않아 무한정 대기 상태에 빠진다.
• innodb_lock_wait_timeout: 잠금을 설정한 시간동안 획득하지 못하면 쿼리는 실패하고 에러를 반환한다.
  • innodb_deadlock_detect가 OFF 인 경우에는 innodb_lock_wait_timeout을 기본값(50)보다 낮게 변경해서 쓰도록 권장한다.

 

 

4.2.6 자동화된 장애 복구

• InnoDB 에는 손실이나 장애로부터 데이터를 보호하기 위해 여러 메커니즘이 탑재되어 있다. 
• 그런 메커니즘을 이용해서 MySQL 서버가 시작될 때, 완료되지 않은 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 복구 작업을 자동 진행한다.
• InnoDB 스토리지 엔진은 견고해서 데이터 파일이 손상되거나 MySQL 서버가 시작되지 못하는 경우는 거의 발생하지 않는다.
  • 하지만 서버와 무관하게 디스크나 서버 하드웨어 이슈로  자동 복구하지 못하는 경우가 있다.
• InnoDB  데이터 파일은 MySQL 서버가 시작될 때, 항상 자동 복구를 수행한다.
  • 여기에서 자동 복구 불가능한 손상이 있다면 멈추고 MySQL 서버는 종료된다.
  • 이 때, MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정해서 서버를 시작할 수 있다.
    • 1) InnoDB 의 로그 파일 손상된 경우: innodb_force_recovery를 6으로 설정 (숫자가 클수록 심각해서 복구 가능성 떨어진다.)
    • 2) InnoDB  테이블의 데이터 파일이 손상된 경우: 1로 설정
    • 3) 어떤 문제인지 모르는 경우: 1 ~ 6까지 변경하면서 MySQL를 재시작해본다. 
• 서버가 켜지고 InnoDB 테이블이 인식되는 경우, mysqldump를 이용해서 데이터를 가능한 만큼 백업하고 그 데이터로  DB와 테이블을 다시 생성하는 게 좋다. 
• innodb_force_recovery 설정을 바꿔도 서버가 시작 되지 않는다면 백업을 이용해서 다시 구축하는 방법밖에 없다.

 

 

4.2.7 InnoDB 버퍼 풀

• InnoDB 버퍼 풀은 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간이며  InnoDB 스토리지 엔진에서 가장 핵심 부분이다.
• 버퍼 역할(쓰기 작업을 지연시켜서 일괄 작업으로 처리)을 한다.

 

4.2.7.1  버퍼 풀의 크기 설정

• 레코드 버퍼: 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간
  • 커넥션이 많고 사용하는 테이블이 많으면  레코드 버퍼 용도로 사용되는 메모리 공간이 많이 필요해진다.
• MySQL 5.7 버전 부터는 InnoDB 버퍼 풀의 크기를 innodb_buffer_pool_size 시스템 변수를 이용해서 동적으로 조절할 수 있다. 작은 값으로 설정해서 상황을 보면서 증가시키는 게 좋다.
• innodb_buffer_pool_indtances: 버퍼 풀을 여러 개로 분리해서 관리할 수 있다.

 

4.2.7.2 버퍼 풀의 구조

• InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개서 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다.
  • 페이지 크기 조각을 관리하기 위해 InnoDB 엔진은 크게 LRU(Least Recently Used) 리스트와 플러시(flush) 리스트, 그리고 프리(free) 리스트는 3개의 자료 구조를 관리한다.
    • LRU(Least Recently Used) 리스트: LRU와 MRU(Most Recently Used) 리스트가 결합된 형태이다.
      • 아래 그림에서 old 서브리스트 영역:  LRU
      • new 서브리스트 영역: MRU
      • LRU의 리스트 관리 목적: 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화한다.
    • 플러(Flush)시 리스트
    • 프리(free) 리스트:  InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어있는 페이지들의 목록을 말한다. 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야하는 경우에 사용한다.

 

 

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

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가한다.
3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했는지에 따라서 나이(age)가 부여되며 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지가 부여된 나이가 오래되고 결국 해당 페이지는 버퍼 풀에서 제거된다. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화 되서 다시 젊어지고 MRU의 헤더 부분으로 옮겨진다.
5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가한다.

버퍼 풀 관리를 위한 LRU 리스트 구조

 

 

• 어떤 데이터 페이지가 자주 읽히는 경우 그 페이지는 InnoDB 버퍼 풀의 MRU 영역에서 계속 살아남게 된다.
• 거의 사용되지 않는 경우에는 새롭게 디스크에서 읽히는 데이터 페이지들에 밀려서 LRU의 끝으로 밀려나서 InnoDB 버퍼 풀에서 제거된다.
• 플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리한다.
• ??

 

4.2.7.3 버퍼 풀과 리두 로그

• InnoDB의 버퍼 풀과 리두 로그의 관계
• InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정할수록 쿼리 성능이 빨라진다.
• InnoDB의 버퍼 풀의 용도: 데이터 캐시와 쓰기 버퍼링
• ?
• InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리하는데 재사용 불가능한 공간을 "활성 리두 로그(Active  Redo Log)" 라고 한다. 
• LSN(Log Sequence Number): 리두 로그 파일의 공간은 계속 순환되어 재사용되지만 매번 기록할 때마다 로그 포지션은 증가된 값을 갖는 것을 말한다. 
• InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜서 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화하는데 발생한 체크포인트 중에서 가장 최근의 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 된다.
• 가장 최근의 체크포인트의 LSN(Log Sequence Number)과 마지막 리두 로그 엔트리의 LSN의 차이를 "체크포인트 에이지(checkpoint age)"라고 한다. 즉, 활성 리두 로그 공간의 크기를 의미한다.

 

InnoDB의 버퍼 풀

• InnoDB의 버퍼 풀은 클린 페이지와 더티 페이지를 가지고 있다.
  • 클린 페이지(Clean Page): 디스크에서 읽은 상태로 전혀 변경되지 않은 페이지
  • 더티 페이지(Dirty Page): INSERT, UPDATE, DELETE 명령으로 인해 변경된 데이터를 가진 페이지를 말한다. 더티페이지는 디스크와 메모리(버퍼 풀)의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록되어야한다. 더티 페이지는 버퍼 풀에 무한정 머무를 수 있는게 아니다. 
• ex 1) InnoDB 버퍼 풀은 100GB이며 리두 로그 파일의 전체 크기는 100MB인 경우
  • 리두 로그 파일의 크기가 100MB이므로 체크포인트 에이지도 최대 100MB까지만 허용한다.
  • 따라서, 체크포인트 에이지가 100MB까지만 허용한다.
  • 예를 들어 평균 리두 로그 엔트리가 4KB라면 25600개 ( = 100MB/4KB) 정도의 더티 페이지만 버퍼 풀에 보관 가능하다. 
  • 데이터 페이지가 16KB 라고 하면 허용 가능한 더티페이지는 400MB 정도 수준 밖에 안된다.
  • 결과: 버퍼 풀의 크기는 매우 크지만 실제 쓰기 버퍼링 위한 효과는 거의 못 보는 상황이다.
• ex 2) InnoDB 버퍼 풀은 100MB이며 리두 로그 파일의 전체 크기는 100GB인 경우
  • 더티페이지를 400GB 정도 가질 수 있다. ??
    • 그런데 버퍼 풀의 크기가 100MB 이므로 최대 허용 가능한 더티 페이지는 100MB 가 된다.
• 결과: 둘 다 좋은 설정은 아니다.
  • ex 1) 잘못된 설정이다.
  • ex 2) 이론적으로는 문제가 없어 보여도 실제 서비스를 운영하면 급작스러운 디스크 쓰기가 발생할 가능성이 높다.
  • 처음 부터 리두 로그 파일의 크기를 선택하기 어렵다면 버퍼 풀의 크기가 100GB 이하의 MySQL 서버에서는 리두 로그 파일의 전체 크기를 대략 5 ~ 10GB 수준으로 선택하고 조금씩 늘려가면서 최적값을 선택하는 게 좋다.

 

4.2.7.4 버퍼 풀 플러시(Buffer Pool Flush)

• MySQL 8.0 버전 부터는 대부분의 서비스에서 더티 페이지를 디스크에 동기화하는 부분(더티 페이지 플러시)에서 디스크 쓰기 폭증 현상은 발생하지 않는다.
• InnoDB 스토리지 엔진은 버퍼 풀에서 더티 페이지들을 디스크에 동기화하기 위해 다음과 같은 2개의 플러시 기능을 백그라운드로 실행한다.
  • 플러시 리스트 플러시
  • LRU 리스트 플러시

 

4.2.7.4.1 플러시 리스트(Flush_list) 플러시

• InnoDB 스토리지 엔진은 리두 로그 공간을 재활용하기 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야한다.
  • 이 때, 오래된 리두 로그 공간이 지워지려면 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화돼야 한다.
  • 이를 위해 InnoDB 스토리지 엔진은 플러시 리스트 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스트에 동기화하는 작업을 수행한다. 

 

InnoDB 스토리지 엔진의 시스템 변수와  기능

• innodb_page_cleaners: 클리너 스레드의 개수를 조정할 수 있도록 한다.
  • 만약, innodb_page_cleaners가 버퍼 풀 인스턴스 개수보다 많은 경우에는 innodb_buffer_pool_instances 설정값으로 자동으로 변경한다.
  • innodb_page_cleaners < 버퍼 풀 인스턴스 개수: 하나의 클리너 스레드가 여러 개의 버퍼 풀 인스턴스를 처리한다. 
  • 권장: innodb_page_cleaners는 innodb_buffer_pool_instances 와 동일하게 설정한다.
• innodb_max_dirty_pages_pct_lwm
  • 더티 페이지가 많을 수록 디스크 쓰기 폭발(Disk IO Burst)현상 발생 가능성이 높다.
  • 디스크 쓰기 폭발 문제를 완화시키는 변수
  •  일정 수준 이상의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록하게 한다. 
  • 기본값: 10%
• innodb_max_dirty_pages_pct: 더티 페이지의 비율을 조정할 수 있다. 기본값은 90% 
  • InnoDB 버퍼 풀은 한계가 있기 때문에 전체 버퍼 풀이 가진 페이지의 몇 프로까지 더티 페이지를 가질 수 있는지 정하는 변수이다.
  • 권장: 기본값(90%)을 유지한다.
• innodb_io_capacity: 각 데이터베이스 서버에서 어느 정도의 디스크 읽고 쓰기가 가능한지 설정한다.
  • 일반적인 상황에서 디스크가 적절히 처리할 수 있는 수준의 값을 설정한다. 
  • cf) 디스크 읽고 쓰기: InnpDB 스토리지 엔진의 백그라운드 스레드가 수행하는 디스크 작업을 의미한다. 대부분의 InnoDB 버퍼 풀의 더티 페이지 쓰기가 이에 해당한다.
• innodb_io_capacity_max: 각 데이터베이스 서버에서 어느 정도의 디스크 읽고 쓰기가 가능한지 설정한다.,
  • 디스크가 최대의 성능을 발휘할 때 어느 정도의 디스크 읽고 쓰기가 가능한지 설정한다.
• innodb_adaptive_flushing
  • 어댑티브 플러시 기능을 켜거나 끌 수 있다.
  • 기본값: ON
  • 어댑티브 플러시(adaptive_flush): InnoDB 스토리지 엔진은 버퍼 풀의 더티 페이지 비율이나  innodb_io_capacity, innodb_io_capacity_max 설정값에 의존하지 않고 새로운 알고리즘을 이용한다.
• innodb_adaptive_flushing_lwm
  • 전체 리두 로그 공간에서 활성 리두 로그의 공간이 얼마 미만이면 어댑티브 플러시가 작동하지 않고 얼마 이상이면 어댑티브 플러시 알고리즘이 작동한다. 
  • 기본값: 10%
• innodb_flush_neighbors(이웃 페이지동시 쓰기): 더티 페이지를 디스크에 기록할 때 디스크에서 근접한 페이지 중에서 더티 페이지가 있다면 InnoDB 스토리지 엔진이 함께 묶어서 디스크로 기록하게 해주는 기능을 활성화할지 결정한다.
  • 데이터 저장을 하드디스크로 하고 있다면 innodb_flush_neighbors를 1 또는 2로 설정해서 활성화하는 게 좋다. 
  • 요즘에는 보통 SSD(solid state drive)를 쓰기 때문에 기본값인 비활성 모드로 유지하는 게 좋다.

 

4.2.7.4.2 LRU 리스트 플러시

• InnoDB 스토리지 엔진은 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들은 읽어올 공간을 만들어야하는데 이를 위해 LRU 리스트 플러시 함수가 사용된다.
  • LRU 리스트 끝부분부터 시작해서 최대 innidb_lru_scan_depth 시스템 변수에 설정되 개수만큼의 페이지를 스캔한다.
• LRU 리스트 스캔은 (innodb_buffer_pool_instances * innodb_lru_scan_depth) 수만큼 수행한다.

 

4.2.7.5 버퍼 풀 상태 백업 및 복구

• InnoDB 서버의 버퍼 풀은 쿼리 성능에 밀접하게 연결되어 있다. 
• 워밍 업(Warming Up): 디스크의 데이터가 버퍼 풀에 적재돼있는 상태를 말한다. 워밍업이 잘 돼있는 상태에서는 쿼리 처리 속도가 몇 십배가 빠르다.
• MySQL 5.6 버전 부터는 버퍼 풀 덤프 및 적재 기능이 도입됐다. 서버 점검이나 기타 작업을 위해 MySQL 서버를 재시작하는 경우, MySQL서버를 셧다운 하기 전에 innodb_buffer_pool_dump_now를 이용한다.
  • innodb_buffer_pool_dump_now: 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있다.
  • innodb_buffer_pool_load_now: 백업된 버퍼 풀의 상태를 다시 복구 가능하다.
• InnoDB 버퍼 풀의 백업은 데이터 디렉터리에 ib_buffer_pool이라는 이름의 파일로 생성된다.
• innodb_buffer_pool_load_abort: 버퍼 풀 적재 작업에 시간이 오래 걸리는 경우 멈추기 위해 쓴다.
  • ON으로 설정: 버퍼 풀 복구 도중에 급히 서비스를 시작하기 위해 버퍼풀 복구를 멈춘다.

 

  Ex) 버퍼 풀 상태 백업 및 복구

<hide/>
-- MySQL 서버 셧다운 전에 버퍼 풀의 상태를 백업
SET GLOBAL innodb_buffer_pool_dump_now = ON;

-- MySQL 서버 재시작 후, 백업된 버퍼 풀의 상태 복구
SET GLOBAL innodb_buffer_pool_load_now = ON;

 

 

4.2.7.6 버퍼 풀의 적재 내용 확인

• MySQL 8.0 버전에서는 information_schema 데이터베이스에 innodb_cached_indexes 테이블이 추가됐다. 
• innodb_cached_indexes 테이블을 이용하면 테이블의 인덱스 별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인 가능하다.
  • 테이블 전체 페이지 중에서 대략 어느 정도 비율이 InnoDB 버퍼 풀에 적재돼있는지 확인할 수 있다.

 

 

4.2.8 Double Write Buffer

• InnoDB 스토리지 엔진의 리두 로그는 리두 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다.
• 더티 페이지를 디스크 파일로 플러시할 때, 일부만 기록되는 문제가 발생하면 그 페이지 내용은 복구할 수 없을 수도 있다.
• 파셜 페이지(partial page) / 톤 페이지(Torn-page): 페이지가 일부만 기록되는 현상을 말한다. 하드웨어 오작동 또는 시스템의 비정상 종료로 발생한다.
  
  •  해결: Double-write 기법
    • 'A' ~ 'E' 까지의 더티 페이지를 디스크로 플러시한다고 할 때, InnoDB 스토리지 엔진은 실제 데이터 파일에 변경 내용을 기록하기 전에 'A' ~ 'E' 까지의 더티 페이지를 묶어서 한번의 디스크 쓰기로 시스템 테이블 스테이스의 DoubleWrite 버퍼에 기록한다. 그리고 InnoDB 스토리지 엔진은 각 터티 페ㅣ지는 파일의 적당한 위치에 하나씩 랜덤 쓰기를 실행한다. 

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」

 

 

4.2.9 언두 로그(Undo log)

• 언두로그: InnoDB 스토리지 엔진은 트랜잭션 수준과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업한다. 이 때, 백업된 데이터를 언두로그(updo log)fkrh gksek. 
  • 트랜잭션 보장: 트랜잭션이 롤백되면 오
  • 격리 수준 보장: 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경 중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.

 

4.2.9.1 언두 로그(Undo log) 모니터링

• 언두 영역: INSERT, UPDATE, DELETE 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터를 보관하는 곳이다. 
  • ex) UPDATE member SET name = "홍길동" WHERE member_id = 1;
  • 문장이 실행되면 트랜잰션을 커밋하지 않아도 실제 데이터 파일 내용은 "홍길동"으로 변경된다.
  • 변경 전의 값이 "벽계수"였다면 언두 영역에는 "벽계수" 라는 값이 백업되어 있는 것이다. 
  • 이 상태에서 커밋하면 현재 상태가 그대로 유지되고 롤백하면 언두 영역의 백업된 데이터를 다시 데이터 파일로 복구한다.
• 언두 로그의 데이터 용도는?
  • 1) 트랜잭션의 롤백 대비 용도
  • 2) 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공한다.
  • cf)트랜잭션의 격리 수준: 동시에 여러 트랜잭션이 데이터를 변경하거나 조회할 때 한 트랜잭션의 작업 내용이 다른 트랜잭션의 작업 내용이 다른 트랜잭션에 어떻게 보일지 결정하는 기준이다.

 

4.2.9.2 언두 테이블스페이스 관리

• 언두 테이블 스페이스(Undo Tablespace): 언두 로그가 저장되는 공간 
• 그림 4.17) 하나의 언두 테이블 스페이스는 1개 이상 128개 이하의 롤백 세그먼크를 가지며 롤백 세그먼크는 1개 이상의 언두 슬롯(Undo slot)을 가진다. 
• 최대 공시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 * (롤백 세그먼트 개수) * (언두 테이블스페이스 개수)
• MySQL 8.0 전까지는 한 번 생성된 언두 로그는변경이 되지 않고 정적으로 사용됐으나 MySQL 8.0 부터는 CREATE UNDO TABLESPACE 나 DROP TABLESPACE 같은 명령으로 새로운 언두테이블스페이스를 동적으로 추가하고 삭제할 수 있도록 바뀌었다. 

 

Undo tablespace truncate

• 언두 테이블스페이스 공간을 필요한 공간만 남기고 불필요하거나 과도라게 할당된 공간을 운영체제로 반납하는 것을 "Undo tablespace truncate"라고 한다.
  • 자동 모드
  • 수동 모드

 

 

4.2.10 체인지 버퍼(Change Buffer)

• InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키게 되는데  이 때 사용하는 임시 메모리 공간을 체인지 버퍼라고 한다.
• 체인지 버퍼는 기본적으로 InnoDB버퍼 풀로 설정된 메모리 공간의 25%까지 사용하도록 설정되어 있다. 50%까지 설정 가능하다.

 

 

innodb_change_buffering 시스템 변수에 설정할 수 있는 값

• all: 모든 인덱스 관련 작업
• none: 버퍼링 안 함
• inserts: 인덱스에 새로운 아이템을 추가하는 작업만 버퍼링
• deletes: 인덱스에서 기존 아이템을 삭제하는 작업(삭제됐다는 마킹 작업)만 버퍼링
• changes: 인덱스에 추가하고 삭제하는 작업만(inserts + deletes) 버퍼링
• purges: 인덱스 아이템을 영구적으로 삭제하는 작업만 버퍼링(백그라운드 작업)

 

 

4.2.11 리두 로그(Redo Log) 및 로그 버퍼

• 리두 로그는 HW, SW등 여러가지 문제점으로 인해 MySQL 서버가 비정상 종료됐을 때, 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전 장치이다. 
• MySQL 서버가 비정상 종료되는 경우 InnoDB 스토리지 엔진의 데이터 파일은 두 종류의 일관되지 않은 데이터를 가질 수 있다.
  • 1) 커밋됐지만 데이터 파일에 기록되지 않은 데이터: 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 된다.
  • 2) 롤백됐지만 데이터 파일에 이미 기록된 데이터: 변경 되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면 된다. 
•  inno_flush_log_at_trx_commit
  •  inno_flush_log_at_trx_commit = 0: 1초에 한 번씩 리두 로그를 디스크로 기록하고 동기화를 실행
  •  inno_flush_log_at_trx_commit = 1:매번 트랜잭션이 커밋될 때마다 디스크로 기록되고 동기화(sync)까지 수행된다.
  •  inno_flush_log_at_trx_commit = 2: 매번 트랜잭션이 커밋될 때마다 디스크로 기록은 되지만 실질적인 동기화는 1초에 한 번씩 실행된다. 
• 로그 버퍼:  사용량이 많은 DBMS 서버의 경우에  리두 로그의 기록 작업이 문제가 되는데 이 부분을 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링한다. 이러한  리두 로그 버퍼링에 사용되는 공간을 "로그 버퍼"라고 한다. 

 

 

ACID

• 데이터베이스에서 트랜잭션의 무결성을 보장하기 위해 꼭 필요한 4가지 기능을 말한다.
• A(Atomic)L 트랜잭션은 원자성 작업이어야 한다.
• C(Consistent): 일관성
• I(Isolated): 격리성
• D(Durable): 한번 저장된 데이터는 지속적으로 유지돼야한다.

 

 

4.2.11.1 리두 로그(Redo Log) 아카이빙

• 리두 로그 아카이빙: InnoDB 스토리지 엔진의 리두 로그를 아카이빙할 수 있는 기능을 말한다.
• 리두 로그 아카이빙은 데이터 변경이 많아서 리두 로그가 덮어쓰인다고 하더라고 백업이 실패하지 않게 해준다. 
• innodb_redo_log_archive_dirs: MySQL 서버에서 아카이빙된 리두 로그가 저장된 디렉터리를 innodb_redo_log_archive_dirs 변수에 설정해야하고 이 디렉터리는 운영체제의 MySQL 서버를 실행하는 유저만 접근이 가능해야한다.
•  

 

 

 

 

 

4.2.11.2 리두 로그(Redo Log) 활성화 및 비활성화

• MySQL 8.0 부터는 수동으로 리두 로그를 활성화하거니 비활성화 할 수 있다.
  • 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우 리두 로그를 비활성화해서 데이터의 적재 시간을 단축시킬 수 있다.

 

 

4.2.12 어댑티브 해시 인덱스

• 인덱스: 테이블에 사용자가 생성해둔 B-Tree 인덱스를 의미한다.
  • cf)  B-Tree 인덱스: 
• 어댑티브 해시 인덱스(Adaptive hach index): 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다.  
  • B-Tree 검색 시간을  줄여주기 위해 도입된 기능이다. 

 

어댑티브 해시 인덱스가 성능 향상에 크게 도움이 되지 않는 경우

• 디스크 읽기가 많은 경우
• 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
• 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

 

어댑티브 해시 인덱스가 성능 향상에 도움이 되는 경우

• 디스의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
• 동등 조건 검색(동등 비교와 IN 연산자)가 많은 경우
• 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

 

 

 

 

4.2.13 InnoDB와 MyISAM, Memory 스토리지 엔진 비교

• MyISAM이 기본 스토리지 엔진으로 사용되는 경우가 많았다.
• MySQL 5.5  부터는 InnoDB 스토리지 엔진이 기본 스토리지 엔진으로 채책됐지만 MySQL 서버의 시스템 테이블은 여전히 MyISAM 테이블을 사용했다. 
•  

 

 

 

 

 

 

 

 

 

MySQL

• CONTENT
• CONTENT

 

MySQL

• CONTENT
• CONTENT

 

MySQL

• CONTENT
• CONTENT

 

 

ISSUES

• 이슈
• 궁금한 점

 

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」 위키북스