Section4-2.md

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

• InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 유일하게 레코드 기반 잠금을 제공한다.
• 이를 통해 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

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

InnoDB의 모든 테이블은 프라이머리 키를 기준으로 클러스터링되어 저장된다.

• 즉, 프라이머리 키 값의 순서대로 디스크에 저장된다.
• 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
• 프라이머리 키가 클러스터링 인덱스이기 때문에, 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.
• 결과적으로 쿼리 실행 계획에서 다른 보조 인덱스에 비해 비중이 높게 설정된다. (실행 계획에서 선택될 확률이 높다) ➡️ 8.8장에서 자세히 다룸

MyISAM 스토리지 엔진은 클러스터링 키를 지원하지않는다.

• MyISAM 테이블에서 프러이머리 키와 세컨더리 인덱스는 구조적으로 차이가 없다.
• 프라이머리 키는 유니크 제약을 가진 세컨더리 인덱스일 뿐이다.
• MyISAM 테이블의 프라이머리 키를 포함하는 모든 인덱스는 물리적인 주소값(ROWID)를 갖는다. ➡️ 4.3.3에서 자세히 다룸

4.2.2 외래 키 지원

외래 키는 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM, MEMORY 테이블에서는 사용할 수 없다.

• 외래 키는 서버 운영의 불편함으로 생성하지 않는 경우도 있지만 개발 환경에서는 좋은 가이드 역할을 한다.
• InnoDB에서 외래키는 부모와 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고
• 변경 시 반드시 부모나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하다.
• 그로 인해 데드락이 발생할 때가 많으므로 개발 시 외래키 존재에 주의하자.

외래키가 복잡하게 얽힌경우 수동 데이터 생성/스키마 변경 등의 작업이 실패할 수 있다.

• 긴급하게 뭔가 조치를 해야하는데 문제가 발생한 경우 foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크작업을 일시적으로 멈출 수 있다.
• 멈추면 레코드 적재나 삭제 등의 작업도 부가적인 체크가 필요없기 때문에 빠르게 처리할 수 있다.

mysql> SET foreign_key_checks=OFF;
// 작업실행
mysql> SET foreign_key_checks=ON;

이는 GLOBAL, SESSION 모두 설정가능한 변수이다. 이런 작업을 수행할 땐 현재 세션에만 적용해야하므로 SESSION키워드를 명시하던지 아니면 아무것도 명시하지 않아야한다. 작업이 완료되면 반드시 현재 세션을 종료하거나, 외래키 체크를 활성화하자.

하지만 일시적으로 해제했다고 해서 부모-자식 관계가 깨진 상태로 유지해도되는 건 아니다.

• 만약 외래키 체크를 중지한 상태에서 외래키 관계를 가진 부모 레코드를 삭제했다면, 자식 레코드도 삭제해야한다.
• 그리고 일관성을 맞춰준 후 다시 외래키 체크 기능을 활성화해야한다.

만약 외래키 체크가 중지되었다면 ON DELETE CASCADE, ON UPDATE CASCADE 옵션은 무시된다. 이는 자동으로 자식 테이블에 매치되는 row에도 똑같이 DELETE, UPDATE를 반영하는 것을 의미한다.

4.2.3 MVCC(Multi Version Concurrency Control)

레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다.

• InnoDB는 Undo log를 이용해 이 기능을 구현한다.
• 여기서 멀티 버전은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미이다.

Isolation level에 따른 데이터 조회 예시

1. 테이블을 생성한다.

mysql> 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)
    );

2. 레코드 한건을 INSERT한다.

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

3. 해당 레코드를 업데이트 한다.

mysql> UPDATE member SET m_area = '경기' where m_id = 12;

update 쿼리가 실행되면 커밋 실행 여부와 관계 없이 InnoDB 버퍼풀은 새로운 값으로 업데이트 된다.

그리고 데이터 파일(디스크)는 체크포인트나 InnoDB의 Write 스레드에 의해 업데이트돼있을 수 도 있고 아닐 수 도 있다.

• InnoDB가 ACID를 보장하기 때문에 일반적으로 InnoDB의 버퍼풀과 데이터파일은 동일한 상태라고 가정해도 무방하다.

아직 COMMIT이나 ROLLBACK을 하지 않은 상태에서 다른 사용자가 조회하면 어디 데이터를 조회할까?

SELECT * FROM member where m_id = 12;

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

그래서 MVCC란

이러한 과정을 DBMS에서는 MVCC라고 표현한다. 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 상황에 따라 달라지는 구조다.

• 여기서는 한개의 데이터만가지고 설명했지만, 예전 버전의 데이터는 무한히 많아질 수 있다.
• 만약 트랜잭션이 길어지면 언두에서 관리하는 예전 데이터는 삭제되지 못하고 오래 관리돼야한다.
• 자연히 언두 영역이 시스템 테이블스페이스의 공간을 많이 차지하게된다.

COMMIT & ROLLBACK

위 상태에서 커밋을 실행하면, InnoDB는 더이상의 변경 작업 없이 지금의 상태를 영구적인 데이터로 만들어 버린다.

하지만 롤백을 실행하면 InnoDB는 언두 영역에 있는 데이터를 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제한다.

커밋을 한다고해서 언두 영역의 데이터가 바로 삭제되는 것은 아니며, 언두 영역을 필요로 하는 트랜잭션이 없을 때 삭제된다.

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

MVCC 기술을 통해, 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 작업을 수행한다.

• SERIALIZABLE를 제외한 READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ격리 수준에서 순수한 읽기(SELECT)는 다른 트랜잭션의 변경 작업과 관계없이 잠금을 대기하지 않고 바로 실행한다.

변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다. ➡️ 잠금 없는 일관된 읽기

• 오랜 시간 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 때가 있는데
• 이런 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 발생하는 문제다.
• 따라서 트랜잭션이 시작됐다면 가능한 빨리 롤백/커밋으로 트랜잭션을 완료하는 것이 좋다.

4.2.5 자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List)형태로 관리한다.

• 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태인 트랜잭션들을 찾아 하나를 강제 종료한다.
• 강제 종료 기준은 트랜잭션의 언두 로그 양이며, 언두 로그를 적게 가진 트랜잭션이 롤백의 대상이 된다.
• 적게 가졌다는 이야기는? 롤백 해도 언두 처리해야할 내용이 적다는 것, 강제 롤백으로 인한 서버 부하도 적다는 것.

참고로 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금(Lock Tables 명령으로 잠긴 테이블)은 볼 수 없어서 데드락 감지가 불확실할 수 도 있다.
이때 innodb_table_locks 시스템 변수를 활성화하면 레코드 잠금뿐 아니라 테이블 잠금까지 감지할 수 있다.

일반적으로 데드락 감지 스레드의 작업은 크게 부담되지않지만, 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 느려진다.

• 데드락 감지 스레드는 잠금 목록을 검사해야 하기 때문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 잠금 테이블에 새로은 잠금을 걸고 데드락 스레드를 찾는다.
• 데드락 감지 스레드가 느려지면, 서비스 요청을 수행중인 스레드는 더 이상 작업을 진행하지 못하고 대기하면서 서비스에 악영향을 준다.
• 따라서 동시 처리 스레드가 매우 많은 경우 데드락 감지 스레드는 더 많은 CPU 자원을 소모할 수 있다.

이런 상황에선 innodb_deadlock_detect를 OFF로 설정하여 데드락 감지 스레드의 작동을 멈춘다.

• 데드락 상황이 발생하게 된다면 무한정 대기하게 되겠지만, innodb_lock_wait_timeout를 활성화하면 일정시간이 지난 후 자동으로 요청이 실패하고 에러를 반환한다.
• innodb_lock_wait_timeout는 초단위로 설정 가능하며, 잠금을 설정 시간동안 획득하지 못하면 쿼리는 실패한다.
• innodb_deadlock_detect를 비활성화하는 경우라면 innodb_lock_wait_timeout을 default 값인 50초보다 낮은 시간으로 변경해서 사용할 것을 권장한다.

만약 PK 또는 세컨더리 인덱스를 기반으로 매우 높은 동시성 처리를 요구하는 서비스가 있다면 innodb_deadlock_detect를 비활성화해 성능을 비교해보자.

4.2.6 자동화된 장애 복구

• InnoDB 스토리지 엔진은 매우 견고해서 데이터 파일이 손상되거나 MySQL 서버가 시작되지 못하는 경우는 거의없다.
• 하지만 디스크나 서버 하드웨어 이슈로 자동 복구가 불가한 경우가 발생할 수 있다.
• InnoDB 데이터 파일은 기본적으로 MySQL 서버 시작 시 항상 자동 복구를 수행한다.
• 이 단계에서 자동으로 복구될 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 서버는 종료된다.

손상 여부 검사 과정

• InnoDB의 로그파일이 손상됐다면 innodb_force_recovery를 6으로 설정하고 MySQL서버를 기동한다.
• InnoDB 테이블의 데이터 파일이 손상됐다면 innodb_force_recovery를 1로 설정하고 MySQL 서버를 기동한다
• 어떤 부분이 문제인지 알 수 없다면, 1~6까지 변경하면서 MySQL을 재시작해본다.
  • 값이 커질수록 그만큼 심각한 상황이기때문에 손실 가능성이 커지고 복구 가능성은 작아진다.

일단 MySQL 서버가 기동되고 InnoDB 테이블이 인식된다면, mysqldump를 이용해 데이터를 가능한 만큼 백업하고 그 데이터로 MySQL 서버의 DB와 테이블을 다시 생성하는 것이 좋다.

innodb_force_recovery가 0이 아닌 복구모드에서는 select 이외의 insert, update, delete 같은 쿼리는 수행할 수 없다.

그래도 서버가 시작되지 않으면 마지막 백업으로 DB를 새로 구축하고 바이너리 로그를 사용해 최대한 장애 시점까지 데이터를 복구할 수 도 있다.

4.2.7 InnoDB 버퍼풀

InnoDB 스토리지 엔진의 가장 핵심적인 부분, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.

쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼역할도 같이한다. 버퍼를 통해 변경된 데이터를 모아 한번에 처리하기 때문에 랜덤한 디스크 작업의 횟수를 줄일 수 있다.

4.2.7.1 버퍼풀의 크기 설정

일반적으로 전체 물리 메모리의 80%정도를 버퍼풀로 설정하라는 게시물도 있지만, OS와 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정해야한다.

• MySQL 서버내에서 메모리를 필요로 하는 부분은 크게 없지만 레코드 버퍼가 상당한 메모리를 사용하기도 한다.
• 레코드 버퍼: 각 클라이언트 세션에서 테이블 레코드를 읽고 쓸 때 버퍼로 사용하는 공간. 커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 공간이 꽤 많이 필요할 수도 있다.
• 이 버퍼 공간은 직접 설정이 불가능하며, 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블 개수에 따라 결정된다.
• 또한 동적으로 해제되기도 하므로 정확히 필요한 공간 크기를 계산할 수 없다.

5.7버전부터는 InnoDB 버퍼풀의 크기를 동적으로 조절할 수 있도록 개선됬다.

• 따라서 크기를 작은 값으로 설정해 조금씩 증가시키는 방법이 최적이다.
• 처음 MySQL 서버를 준비한다면, OS 전체 메모리 50% 정도만 버퍼풀로 설정하고 나머지 공간은 MySQL 서버와 OS, 그리고 다른 프로그램이 사용할 수 있는 공간으로 확보해주는 것이 좋다.
  • 그리고 조금씩 올려가션서 최적점을 찾는다.

InnoDB 버퍼풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있으며, 동적으로 버퍼풀 크기를 확장할 수 있다.

• 하지만 이는 크리티컬한 변경이므로 한가한 시점에 진행하는 것이 좋다. (크기를 늘리는건 영향도가 크지 않지만, 줄이는 작업은 영향도가 매우 크다.)
• InnoDB는 내부적으로 128MB 청크단위로 쪼개어 관리되며, 버퍼풀을 줄이거나 늘리는 단위로 사용된다.

InnoDB 버퍼풀은 전통적으로 버퍼 풀 전체를 관리하는 잠금(세마포어)로 인해 내부 잠금 경합을 많이 유발해왔다. 이런 경합을 줄이기 위해 버퍼풀을 여러개로 쪼개어 관리할 수 있게 개선됐다.

• 버퍼풀이 작은 버퍼풀로 쪼개지면서, 잠금(세마포어)자체도 경합이 분산되는 효과를 낸다.
• innodb_buffer_pool_instance 시스템 변수를 이용해 여러개로 분리해서 관리할 수 있으며, 각 버퍼풀을 버퍼 풀 인스턴스라고 표현한다.
• 기본은 8개로 초기화되지만, 버퍼풀을 위한 메모리 공간이 1GB 미만이라면 1개만 생성된다.
• 40GB 이하라면 8개를 유지하고, 더 크다면 버퍼 풀 인스턴스 당 5GB정도가 되게 개수를 설정하는 것이 좋다.

4.2.7.2 버퍼 풀의 구조

InnoDB 스토리지 엔진은 버퍼 풀을 페이지 크기(innodb_page_size 시스템 변수)의 조각으로 쪼개어 데이터를 필요로 할 때 해당 데이터 페이지를 읽어 각 조각에 저장한다.

버퍼풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 LRU(Least Recently Used) 리스트와 플러시(Flush) 리스트, 프리(Free) 리스트 3개 자료구조로 관리한다.

프리(Free) 리스트

• 비어 있는 페이지들의 목록. 사용자의 쿼리가 새롭게 데이터 페이지를 읽어와야하는 경우 사용

LRU(Least Recently Used) 리스트

• LRU와 MRU(Most Recently Used) 리스트가 결합된 형태
• Old 서브리스트 영역 = LRU, New 서브리스트 영역 = MRU

LRU 리스트 목적

• 디스크로부터 한번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼풀의 메모리에 유지해 디스크 읽기를 최소화하는 것

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

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
   • InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색
   • 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색
   • 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
2. 디스크에서 필요한 데이터 페이지를 버퍼풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
   • Read Ahead와 같이 대량 읽기의 경우, 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만 실제 쿼리에서 사용되지 않을 수도 있으며, 이런 경우 MRU로 이동되지 않음
4. 버퍼풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했는지에 따라 나이(AGE)가 부여된다.
   • 버퍼풀에 상주하는 동안 오랫동안 쿼리에서 사용되지 않으면 나이가 오래되고 결국 버퍼풀에서 제거된다. ➡️ Eviction
   • 쿼리에 의해 사용되면 나이는 초기화되고 MRU의 헤더 부분으로 옮겨진다.
5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

즉, 버퍼 풀 내부에서 최근 접근 여부에 따라 데이터 페이지는 서로 경쟁하면서 MRU 또는 LRU로 이동하는 것이다.
그리고 InnoDB 스토리지 엔진은 LRU의 끝으로 밀려난 데이터 페이지들을 버퍼풀에서 제거하여 빈공간을 만든다.

그래서 처음 읽힌 데이터 페이지가 자주 사용된다면 MRU 영역에서 계속 살아남게되고, 반대로 거의 사용되지 않으면 LRU 끝으로 밀러나 InnoDB 버퍼풀에서 제거될 것이다.

플러시(Flush) 리스트

디스크로 동기화되지 않은 데이터를 가진 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리한다.

• 디스크에서 읽은 상태 그대로 변경이 없다면 플러시 리스트에 관리되지 않는다.
• 하지만 한번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록돼야 한다.
• 데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고, 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다.
• 그래서 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결된다.

하지만 리두 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는 것을 항상 보장하지 않는다. (반대도 마찬가지)

• InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화하게 된다.
• 체크 포인트는 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 리두 로그의 어느부분부터 복구를 실행해야할지 판단하는 기준점이다.

4.2.7.3 버퍼 풀과 리두 로그

버퍼풀은 서버의 메모리가 허용하는 만큼 설정할 수록 쿼리의 성능이 빨라진다.

• 물론 모든 데이터 파일이 버퍼풀에 적재될 정도의 공간이라면 늘려도 성능에 도움이 되지 않는다.
• 그렇지 않다면 디스크 데이터가 버퍼 풀 메모리로 적재되면 성능이 좋아진다.

버퍼풀은 DB 서버 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 2가지 용도가있다.

• 버퍼풀 메모리 공간 늘리기는 데이터 캐시 기능만 향상 시킨 것.
• 쓰기 버퍼링을 향상시키기 위해서는 버퍼풀과 리두로그의 관계를 알아야한다.

버퍼풀과 리두로그

버퍼풀 = 클린 페이지 + 더티 페이지

• 클린 페이지: 디스크에서 읽은 후 데이터가 변경되지 않은 페이지
• 더티 페이지: 디스크에서 읽은 후 데이터가 변경된 페이지
• 여기서 더티페이지는 데이터 상태가 디스크와 다르기때문에, 언젠가는 디스크로 기록되야한다.
• 하지만 버퍼풀에 무한정 머무를 수 있는 것은 아니다.

리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환고리 처럼 사용한다.

• 즉, 데이터 변경이 계속 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는 어느 순간 새로운 로그 엔트리로 덮어쓰인다.
• 그래서 전체 리두 로그 파일에서 재사용 가능한 공간과 재사용 불가능한 공간을 구분해서 관리해야한다.
• 재사용 불가능한 공간 = 활성 리두 로그(Active Redo Log) = 화살표를 가진 엔트리들

❓ 버퍼풀에 있는 네모 = 고정 크기 파일(리두 로그 파일), 리두 로그에 있는 네모 = 리두 로그 엔트리

LSN(Log Sequence Number): 리두 로그 파일의 공간은 계속 순환되어 재사용되지만, 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 갖는다.

• InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜, 리두로그와 버퍼풀의 더티페이지를 디스크로 동기화하는데
• 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 된다.
• 하지만 활성 리두 로그 공간의 마지막은 계속 증가하기 때문에 체크포인트와 무관한다.
• 그리고 가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지(Checkpoint Age)라고 한다.
• 즉, 체크포인트 에이지는 활성 리두 로그 공간의 크기를 일컫는다.

더티페이지는 특정 리두로그엔트리와 관계를 가지고, 체크포인트가 발생하면 체크포인트 LSN보다 작은 리두 로그 엔트리와 관련된 더티페이지는 모두 디스크로 동기화돼야한다.

• 물론 체크포인트 LSN보다 작은 LSN값을 가진 리두 로그 엔트리도 디스크로 동기화 돼야한다.

예제

1. InnoDB 버퍼풀은 100GB이며 리두 로그 파일의 전체 크기는 100MB인 경우
   • 리두 로그 파일 크기가 100MB밖에 안되기 때문에 체크포인트 에이지도 최대 100MB만 허용된다.
   • 평균 리두로그엔트리가 4kb라면 100MB/4KB = 25600개 정도의 더티 페이지만 버퍼풀에 보관할 수 있다.
   • 데이터 페이지가 16KB라면 허용가능한 전체 더티 페이지 크기는 25600 * 16KB = 400MB 수준밖에 안된다.
   • 이 경우 버퍼풀의 크기는 매우 크지만, 실제 쓰기 버퍼링을 위한 효과는 거의 못본다.
2. InnoDB 버퍼풀은 100MG이며 리두 로그 파일의 전체 크기는 100GB인 경우
   • 위 방식으로 계산하면 전체 더티 페이지 크기가 400GB가 된다.
   • 하지만 버퍼풀 크기가 100MB이기 대문에 최대 허용 가능한 더치 페이지는 100MB가 된다.

1번은 잘못된 설정이다. 2번은 이론적으로 문제가 없어보이지만, 이 상태로 서비스를 운영하다 보면 급작스러운 디스크 쓰기가 발생할 가능성이 높다.

• 버퍼풀에 더티 페이지의 비율이 너무 높은 상태에서 갑자기 버퍼풀이 필요해지는 상황이 오면 InnoDB 스토리지 엔진은 매우 많은 더티페이지를 한번에 기록해야하는 상황이 온다.
• 처음부터 리두로그파일의 크기를 적절히 선택하기 어렵다면 5~10GB 수준으로 선택하고 필요할 때마다 조금씩 늘려가며 최적값을 선택하는 것이 좋다.

버퍼풀 크기가 100GB라고 해서 리두 로그의 공간이 100GB가 돼야하는 것은 아니다. 리두 로그는 변경분만 가지고 버퍼풀은 데이터 페이지 통째로 가지기 때문에 데이터 변경이 발생해도 리두 로그는 훨씩 작은 공간만 있으면된다.

버퍼풀과 리두로그 다시 공부필요함..

• 버퍼풀
• 리두로그 파일, 리두로그 엔트리
• 체크포인트 에이지

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

InnoDB 스토리지 엔진은 버퍼풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화 하기 위해 아래 2개의 플래시 기능을 백그라운드로 처리한다.

• 플러시 리스트(Flush_list)플러시
• LRU 리스트(LRU_list)플러시

4.2.7.4.1 플러시 리스트 플러시

InnoDB 스토리지 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 한다.

• 이때 리두 로그 공간을 지우려면 반드시 버퍼풀의 더티 페이지가 먼저 디스크로 동기화돼야 한다.
• 이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트(Flush_list)플러시 함수를 호출하여
• 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행한다.
• 이때 언제부터 얼마나 많은 더티 페이지를 한번에 디스크로 기록하느냐에 따라 사용자 쿼리 처리에 영향이 결정된다.
• 이를 부드럽게 처리하기 위해 InnoDB는 아래와 같은 시스템 변수를 제공한다.

시스템 변수

• 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_daaptive_flushing_lwn

클리너 스레드(Cleaner Tread): 더티 페이지를 디스크로 동기화하는 스레드

• innodb_page_cleaners는 클리너 스레드 개수를 조정한다.
• innodb_page_cleaners이 버퍼풀 인스턴스 개수보다 많은 경우 innodb_buffer_pool_instances 설정값을 클리너 스레드 개수와 맞춰준다. (클리너스레드 1:1 전담)
• 하지만 innodb_page_cleaners가 버퍼풀 인스턴스 개수보다 적은 경우 하나의 클리너 스레드가 여러개 버퍼풀 인스턴스를 처리하기 때문에 가능한 동일한 값으로 맞춰주자.

InnoDB 버퍼풀은 버퍼풀이 가진 페이지 90%까지 더티페이지를 가질 수 있다.

• 하지만 이 값이 너무 높을 경우 innodb_max_dirty_pages_pct로 더티페이지 비율을 조정할 수 있다.
• 일반적으로 더티페이지를 많이 가지고 있을 수록 디스크 쓰기 작업을 버퍼링함으로써 여러번 디스크 쓰기를 한번으로 줄이는 효과를 극대화 할 수 있다.
• 가능한 기본값을 유지하자.

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

• innodb_io_capacity 설정값 기준으로 더티페이지 쓰기를 실행하지만, 디스크로 기록되는 개수보다 많이 발생하면 버퍼풀에 더티페이지가 계속 증가하게 되고
• 어느순간 90% 비율을 넘어가면 InnoDB 엔진은 급작스럽게 페이지를 디스크로 기록해야한다고 판단한다. ➡ 디스크 쓰기 폭발(Disk IO Burst)현상 발생
• 위 문제를 완화하기 위해 innodb_max_dirty_pages_pct_lwm 시스템 변수를 사용해 일정 수준 이상의 더티페이지가 발생하면 조금씩 디스크로 기록하게 한다.
• 기본값은 10% 수준인데, 만약 페이지 비율이 얼마 되지 않는 상태에서 디스크 쓰기가 많이 발생하고 있다면, 조금 더 높은 값으로 조정하여 디스크 쓰기 횟수를 줄일 수 있다.

innodb_io_capacity, innodb_io_capacity_max는 각 DB서버에서 어느정도의 디스크 읽고 쓰기가 가능한지를 설정하는 값이다.

• innodb_io_capacity: 디스크가 적절히 처리할 수 있는 수준의 값
• innodb_io_capacity_max: 디스크가 최대 성능을 발휘할 때 어느 정도의 읽고 쓰기가 가능한지 설정
• 여기서 언급하는 읽고 쓰기는 백그라운드 스레드가 수행하는 디스크 작업을 의미한다.
• 대부분 버퍼풀의 더티페이지 쓰기가 해당
• 하지만 InnoDB 스토리지 엔진은 사용자의 쿼리를 처리하기 위해서도 디스크 읽기를 해야한다.
• 따라서 현재 장착된 디스크의 최대처리량(ex 1000IOPS)을 그대로 시스템 변수에 설정해서는 안된다.

관리해야할 서버가 많다면 innodb_io_capacity, innodb_io_capacity_max 설정은 상당히 번거롭다. 그래서 어댑티브 플러시(Adaptive flush)기능을 제공한다.

• 이는 innodb_adaptive_flushing로 켜고 끌 수 있다.
• 어댑티브 플러시가 활성화 되면, 설정값에 의존하지 않고 새로운 알고리즘을 사용한다.
• 더티페이지를 어느정도 디스크로 기록해야할지는 사실 어느정도로 더티페이지가 생성되는지 분석하는것이다.
• 이는 결국 리두 로그가 어느정도로 증가하는지와 같다.
• 따라서 어댑티브 플러시 알고리즘은 리두 로그의 증가 속도를 분석해 적절한 수준의 더티 페이지가 버퍼풀에 유지될 수 있도록 디스크 쓰기를 실행한다.
• innodb_adaptive_flushing_lwn은 기본 10%이며, 전체 리두 로그 공간에서 활성 리두 로그 공간이 10% 미만이면 어댑티브 플러시가 작동하지 않다가 10%를 넘어서면 그때부터 알고리즘이 작동한다.

innodb_flush_neighbors는 더티 페이지를 디스크에 기록할 때 디스크에서 근접한 페이지 중 더티페이지가 있다면, 함께 묶어서 기록하게 해주는 기능을 활성화할지 결정한다.

• 옛날 하드디스크의 경우 IO는 고비용 작업이기때문에 횟수를 줄이기위해 많은 노력을 기울였는데 위 기능이 그러한 노력의 결과로 볼 수 있다.
• 만약 하드디스크를 사용하고있다면 활성화하는것이 좋지만 SSD를 사용하면 기본값인 비활성화 모드로 유지하는 것이 좋다.

4.2.7.4.2 LRU 리스트 플러시

LRU리스트(LRU_list) 플러시 함수 : LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들때 사용

• LRU리스트의 끝부분부터 시작해 최대 innodb_lru_scan_depth에 설정된 만큼 페이지들을 스캔한다.
• 이 때 스캔하면서 더티 페이지는 디스크에 동기화하게 하며, 클린 페이지는 즉시 프리(Free)리스트로 옮긴다.
• 버퍼풀 인스턴스별로 최대 innodb_lru_scan_depth개수만큼 스캔하기 때문에 실질적으로 LRU 리스트의 스캔은 innodb_buffer_pool_instance * innodb_lru_scan_depth 만큼 수행한다.

4.2.7.5 버퍼 풀 상태 백업 및 복구

버퍼 풀에 쿼리들이 사용할 데이터가 준비되어 디스크에서 데이터를 읽지 않아도 되는 상태를 워밍업(Warming Up)이라고 표현한다.

• 버퍼풀 워밍업이된 상태에서는 그렇지 않은 상태보다 몇십 배 속도가 빠르다.
• MySQL5.5: ySQL 서버를 재시작하는 경우 서비스 오픈전 강제 워밍업을 위해 주요 테이블과 인덱스에 대해 풀스캔을 한번씩 실행하고 서비스를 오픈했었다.
• MySQL5.6 이후: 버퍼 풀 덤프 및 적재기능 도입되어 innodb_buffer_pool_dump_now 시스템 변수를 이용해 현재 버퍼풀 상태를 백업할 수 있다.
• 그리고 다시 시작하면 innodb_buffer_pool_load_now를 이용하여 백업된 버퍼풀 상태를 다시 복구할 수 있다.

// 서버 셧다운 전 버퍼 풀의 상태 백업
set GLOBAL innodb_buffer_pool_dump_now = ON;

// 서버 재시작 후 백업된 버퍼 풀의 상태 복구
set GLOBAL innodb_buffer_pool_load_now = ON;

버퍼 풀 백업 데이터는 데이터 디렉터리에 ib_buffer_pool 이름으로 파일이 생성된다.

• 실제 크기를 보면 아무리 버퍼풀이 크더라도 몇십 MB 이하다.
• 이는 버퍼풀의 LRU 리스트에서 적재된 데이터 페이지의 메타정보만 가져와서 저장하기 때문이다.

하지만 이를 다시 버퍼풀로 복구하는 과정은 버퍼풀 크기에 따라 상당한 시간이 걸릴 수 있다.

• 이는 백업된 내용을 기반으로 디스크에서 각 테이블의 데이터 페이지로 읽어와야하기 때문이다.
• 그래서 복구하는 과정의 진행 정도를 확인할 수 있는 상태값을 지원한다.

show STATUS LIKE `innodb_buffer_pool_dump_status`

만약 적재작업이 너무 오래 걸려 중간에 멈추고자 한다면 innodb_buffer_pool_load_abort를 이용하면된다.

• 버퍼풀 복구는 상당히 많은 디스크 읽기를 필요로 하기 때문에, 복구중인 상태에서 서비스를 재개하는 것은 좋지 않은 선택이다.
• 따라서 복구 도중에 급히 서비스를 재시작해야한다면 복구를 멈출것을 권장한다.

set GLOBAL innodb_buffer_pool_load_abort = ON;

위 작업을 수동으로 하는것은 사실 쉽지않다. 따라서 MySQL 서버가 셧다운 되기 직전에 버퍼풀 백업을 실행하고 서버가 시작되면 버퍼풀 복구하는 기능을 제공한다.

• 설정 방법은 innodb_buffer_pool_dump_at_shutdown, innodb_buffer_pool_load_at_startup설정을 MySQL 서버 설정 파일에 넣어두면된다.

버퍼풀 백업 파일이 반드시 셧다운하기 직전의 파일일 필요는없다. ib_buffer_pool파일에서 데이터 페이지의 목록을 가져와 실제 존재하는 데이터 페이지만 버퍼풀로 적재하기때문에 괜찮다.

4.2.7.6 버퍼풀의 적재 내용 확인

MySQL 5.6 이후

• MySQL 5.6부터 information_schema데이터베이스의 innodb_buffer_page테이블을 이용해 버퍼풀 메모리에 어떤 테이블의 페이지들이 적재되어있는지 확인할 수 있었다.
• 하지만 버퍼풀이 큰 경우 상당한 부하를 일으켜 서비스 쿼리가 느려지는 문제가 있었고, 실 서비스용 MySQL서버는 버퍼풀 상태 확인이 거의 불가능했다.

MySQL 8.0 버전

• 위 문제를 해결하기위해 information_schema데이터베이스에 innodb_cached_indexes테이블이 새로 추가됐다.
• 이를 이용하면 테이블의 인덱스별 데이터 페이지가 얼마나 버퍼풀에 적재되어있는지 확인할 수 있다.

• 응용하여 테이블 전체(인덱스 포함)페이지 중 대략 어느 정도 비율이 InnoDB 버퍼 풀에 적재돼 있는지 다음과 같이 확인해볼 수 있다.

4.2.8 Double Write Buffer

리두로그는 리두로그 공간의 낭비를 막기위해 페이지의 변경된 내용만 기록한다.

• 따라서 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제가 발생하면, 그 페이지의 내용은 복구할 수 없을 수도 있다.
• 이렇게 페이지 일부만 기독되는 현상을 파셜 페이지(Partial page), 톤 페이지(Torn page)라고 한다.
• 이런 현상은 하드웨어의 오작동이나 시스템의 비정상 종료 등으로 발생할 수 있다.

Double-Write

위 문제를 막기위해 Double-Write 기법을 이용한다.

A ~ E까지 더티 페이지를 디스크로 플러시한다고 가정해보자.

• 스토리지 엔진은 실제 데이터 파일에 변경 내용을 기록하기 전에, A ~ E까지 더티 페이지를 우선 묶어 한번의 디스크 쓰기로 Double-Wirte버퍼에 기록한다.
• 그리고 스토리지 엔진은 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기를 시작한다.

이렇게 Double Write버퍼 공간에 기록된 변경 내용은 실제 데이터 파일에 정상적으로 기록되면 더이상 필요가 없어진다.

• Double Write버퍼의 내용은 실제 데이터 파일의 쓰기가 중간에 실패할 때 원래의 목적으로 사용된다.
• 만약 페이지 기록중 OS가 비정상적으로 종료되면, 스토리지 엔진은 재시작될때 Double Write버퍼의 내용과 데이터 파일의 페이지들을 비교한다.
• 그리고 다른내용을 담고있는 페이지가 있으면, Double Write 버퍼에서 데이터 파일의 페이지로 복사한다.
• 이 기능의 사용여부는 innodb_doublewrite로 제어할 수 있다.

Double Wirte는 HDD처럼 자기 원판이 회전하는 저장시스템에서는 한번의 순차 디스크 쓰기를 하기때문에 부담되지 않지만, SDD같이 랜덤IO나 순차IO의 비용이 비슷한 저장시스템에서는 부담스럽다.

• 하지만 데이터의 무결성이 매우 중요한 서비스에서는 Double Wirte 활성화를 고려하는 것이 좋다.
• 만약 DB 서버성능을 위해 InnoDB 리두 로그 동기화 설정(innodb_flush_log_at_trx_commit)을 꺼놨다면 Double Write도 비활성화하는 것이 좋다.

일반적으로 MySQL 서버는 복제를 이용해 여러개 사본을 유지하기때문에 비정상 종료가 되면 백업과 바이너리 로그를 이용해 동기화하는 경우가 많다.
만약 무결성이 민감한 서비스라면 리두로그와 바이너리 로그 등 트랜잭션을 Commit하는 시점에 동기화할것들이 많다는 점을 주의하자.
즉, 리두로그는 동기화하지 않으면서(innodb_flush_log_at_trx_commit 1이 아닌 경우) DoubleWrite만 활성화한다는것은 잘못된 선택이다.

4.2.9 언두로그

InnoDB 스토리지 엔진은 트랜잭션과 격리수준을 보장하기위해 DML로 변경되기 이전 버전의 데이터를 별도로 백업한다. ➡ 언두로그(Undo Log)

• 트랜잭션 보장
  • 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 이전으로 변경해야하는데, 이때 언두로그에 백업해둔 이전 버전의 데이터를 이용해 복구한다.
• 격리 수준 보장
  • 특정 커넥션에서 데이터를 변경하는 도중 다른 커넥션에서 데이터를 조회하면, 트랜잭션 격리 수준에 맞게 변경중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.

언두로그는 중요한 역할을 담당하지만, 관리 비용도 많이 필요하다.

4.2.9.1 언두 로그 레코드 모니터링

DML 쿼리가 실행되면, 트랜잭션을 커밋하지 않아도 실데 데이터 파일(데이터/인덱스 버퍼)내용은 변경된다.

• 그리고 변경되기 전 데이터는 언두영역에 백업된다.
• 이 상태에서 사용자가 커밋하면, 현재 상태가 유지되고 롤백하면 언우 영윽의 데이터가 복구된다.

언두로그의 데이터의 용도

1. 트랜잭션 롤백 대비용
2. 트랜잭션 격리 수준을 유지하면서, 높은 동시성을 제공

트랜잭션 격리 수준: 동시에 여러 트랜잭션이 데이터 변경/조회 시 한 트랜잭션의 작업 내용이 다른 트랜잭션에 어떻게 보일지 결정하는 기준 5.4.3절

MySQL 5.5 이전 버전

1. 한번 증가한 언두로그 공간은 다시 줄어들지 않는다.

• 100GB 테이블을 DELETE로 삭제하면 언두로그는 테이블 크기만큼 언두로그에 복사하며 공간 사용량이 늘어난다.

2. 트랜잭션이 오랜 시간 실행될 경우 언두로그양은 급격히 증가한다.

• 트랜잭션이 완료됐다해서 해당 트랜잭션이 생성한 언두로그를 즉시 삭제할 수 있는것은 아니다.
• 만약 A->B->C 순서대로 트랜잭션이 시작되었고 B와 C의 트랜잭션은 완료되었지만 A는 아직 활성항태일 경우
• B, C 트랜잭션 완료 여부와 상관없이 B, C 트랜잭션이 만든 언두로그는 삭제되지 않는다.

만약 사용자가 트랜잭션을 시작한 상태에서 완료하지 않고 하루정도 방치했다면, 트랜잭션 시작 시점부터 계속 언두로그를 보존할것이다.
디스크 사용량이 증가하는것은 큰문제가 아닐 수 있지만, 그동안 빈번하게 변경된 레코드를 조회하는 쿼리가 실행되면 언두 로그의 이력을 필요한 만큼 스캔해야 레코드를 찾을 수 있기 때문에 쿼리 성능이 떨어지게된다.

MySQL 5.7, 8.0

언두로그를 돌아가면서 순차적으로 사용해 디스크 공간을 줄이는게 가능해졌다. 때로는 MySQL 서버가 필요한 시점에 사용 공간을 자동으로 줄여 주기도 한다.

하지만 여전히 트랜잭션이 장시간 활성화 되는것은 성능상 좋지 않다. 그래서 언두로그 레코드가 얼마나 되는지 항상 모니터링하는 것이 좋다.

// MySQL 서버의 모든 버전에서 사용 가능
show ENGINE INNODB STAUS \G
                         
// 8.0 버전에서만 사용가능한 명령
select count
from information_schema.innodb_metrics
where subsystem='transaction' and name='trx_rseg_history_len';

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

언두 테이블스페이스(Undo Tablespace) = 언두로그가 저장되는 공간

• MySQL 5.6 이전: 언두로그가 모두 시스템 테이블 스페이스(ibdata.ibd)에 저장됨. 하지만 이는 서버가 초기화될때 생성되기 때문에 확장에 한계가 있었다.
• MySQL 5.6 이후: innodb_undo_tablespaces 시스템 변수가 등장하고 이를 2보다 큰값을 설정하면 더이상 언두로그를 시스템 테이블 스페이스에 저장하지 않고 별도의 언두로그 파일을 사용한다.
  • 하지만 0으로 설정하면 여전히 시스템 테이블 스페이스에 저장함.
• MySQL 8.0: innodb_undo_tablespaces 시스템 변수는 Deprecated되었으며, 언두로그는 시스템 테이블스페이스 외부 별도 로그 파일에 기록되도록 개선되었다.

언두 테이블스페이스 구조

• 하나의 언두 테이블스페이스는 1~128개의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯(Undo Slot)을 가진다.
• 하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16byte로 나눈 값의 개수만큼 언두 슬롯을 가진다.
• 하나의 트랜잭션이 필요로하는 언두 슬롯의 개수는 insert, update, delete 문장 특성에 따라 최대 4개까지 언두슬롯을 사용한다.
• 일반적으로는 임시테이블을 사용하지 않으므로 하나의 트랜잭션은 대락 2개를 필요로한다고 가정하면된다.
• 그래서 최대 동시 처리 가능한 트랜잭션 개수는 아래 식으로 예측해볼 수 있다.

최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 * (롤백 세그먼트 개수) * (언두 테이블스페이스 개수)

16KB InnoDB의 기본 설정(innodb_undo_tablespaces=2, innodb_rollback_segent=128), 을 사용한다고 가정하면 대략 131072개 정도의 트랜잭션이 동시에 처리 가능해진다.

• 16 * 1024 / 16 * 128 * 2 / 2 = 131072
• 물론 일반 서비스가 이정도까지 동시 트랜잭션이 필요하진 않지만, 기본값으로 해서 크게 문제될 건 없으므로 가능하면 기본값을 유지하자.

언두로그공간이 남는건 문제되지 않지만, 언두 로그 슬롯이 부족한 경우 트랜잭션을 시작할 수 없는 심각한 문제가 발생한다.

• 언두로그 관련 시스템 변수를 변경해야 한다면 적절히 필요한 동시 트랜잭션 개수에 맞게 언두 테이블 스페이스와 롤백 세그먼트의 개수를 설정해야한다.

MySQL 8.0이전까지는 한번 생성된 언두로그 변경이 허용되지 않았지만 8.0부터는 CREATE UNDO TABLESPACE, DROP TABLESPACE 명령어로 언두테이블 추가/삭제가 가능하다.

과도한 언두 테이블스페이스 공간 할당

언두 테이블스페이스 공간이 과도하게 할당되어 이를 반납하는것을 Undo tablespace truncate라고 한다.
언두 테이블스페이스의 불필요한 공간을 잘라내는 방법이 있으며, 두가지 모두 8.0부터 지원된다.

자동 모드: InnoDB 스토리지 엔진의 퍼지 스레드(Purge Thread)는 주기적으로 깨어나 언두 로그에서 불필요해진 언두 로그를 삭제하는 작업을 실행한다.

• innodb_undo_log_truncate가 on으로 설정되면 퍼시 스레드는 주기적으로 동작하고 사용하지 않는 공간을 OS로 반납한다.
• 더 빈번하게 실행하려면 innodb_purge_rseg_truncate_frequency변수값을 조정하면된다.

수동모드: innodb_undo_log_truncate가 off로 설정되어 언두로그파일 잘라내기가 자동으로 실행되지 않거나

• 예상보다 자동모드여도 공간 반납이 부진한경우, 언두테이블스페이스를 비활성화하면 퍼지스레드는 비활성 상태의 언두테이블 스페이스를 찾아 불필요한 공간을 잘라내도 OS로 해당 곤간을 반납한다.
• 반납이 완료되면 언두테이블스페이스를 다시 활성화한다.
• 수동모드는 언두테이블스페이스가 최소 3개 이상은돼야 작동한다.

// 언두 테이블스페이스 비활성화
alter undo tablespace tablespace_name set inactive;

// 퍼지 스레드에 의해 언두 테이블스페이스 공간이 반납되면 다시 활성화
alter undo tablespace tablespace_name set active;

4.2.10 체인지 버퍼

RDBMS에서는 데이터를 변경하는 작업뿐 아니라, 테이블에 포함된 인덱스를 업데이트 하는 작업도 필요하다.

• 그런데 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로, 인덱스가 많다면 많은 자원을 소모한다.
• 따라서 변경해야할 인덱스 페이지가 버퍼풀에 있으면 바로 업데이트를 수행하지만, 디스크로 부터 읽어와야한다면 즉시 실행하지 않고 임시 공간에 저장해둔 후 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다.
• 이때 사용하는 임시 메모리 공간을 체인지 버퍼(Change Buffer)라고 한다.

사용자에게 결과를 전달하기 전, 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다.

체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합된다. ➡ 머지 스레드

• MySQL5.5 이전: Insert작업에 대해서만 버퍼링 가능. 시스템 변수 설정 x
• MySQL5.5 이후: innodb_change_buffering 시스템 변수로 체인지 버퍼 활성/비활성화 가능
• MySQL8.0: Insert, Delete, Update작업도 버퍼링 가능.

innodb_change_buffering

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

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

• 필요하다면 50%까지 사용하게 설정할 수 있다.
• 체인지 버퍼를 줄이거나 늘리고 싶다면 innodb_change_buffer_max_size 시스템 변수에 비율을 설정하면 된다.

체인지 버퍼가 메모리를 얼마나 사용중인지, 얼마나 많은 변경사항을 버퍼링하고있는지는 다음과 같이 확인할 수 있다.

// 체인지 버퍼가 사용중인 메모리 공간 크기
select event_name, current_number_of_btyes_used
from performance_schema.memory_summary_global_by_event_name
where event_name='memory/innodb/ibuf01buf';

// 체인지 버퍼 관련 오퍼레이션 처리 횟수
show engine innodb status \G

4.2.11 리두로그 및 로그버퍼

리두 로그는 트랜잭션의 4가지 요소인 ACID의 D(Durable)에 해당하는 영속성과 가장 밀접하게 연관되어있다.

• 리두 로그는 하드웨어나 소프트웨어 등 문제로 인해 MySQL서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는게 안전장치다.

MySQL서버를 포함한 대부분 DB서버는 데이터 변경 내용을 로그로 먼저 기록한다.

• 거의 모든 DBMS에서 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료구조를 가지고 있기 때문에 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요하다. (상대적으로 큰 비용이 필요)
• 이로 인한 성능 저하를 막기 위해 DB 서버는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있으며, 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전의 상태로 북구한다.
• 성능을 위해 리두 로그를 버퍼링할 수 있는 로그버퍼와 같은 자료구조도 가지고 있다.

MySQL서버가 비정상 종료되는 경우 InnoDB 스토리지 엔진의 데이터 파일은 일관성이 깨진 데이터를 가질 수 있다.

1. 커밋됐지만 데이터 파일에 기록되지 않은 데이터
2. 롤백됐지만 데이터 파일에 이미 기록된 데이터

1번의 경우 리두 로그에 저장된 데이터를 복구하면된다. 하지만 2번은 리두 로그로 해결할 수 없다.

• 이때 변경되지 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면된다.
• 그렇다고 리두로그가 전혀 필요하지 않은 것은 아니다. 최소한 그 변경이 커밋/롤백/트랜잭션중간 상태였는지를 확인하기 위해서라도 필요하다.

리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 설정하는 것을 권장한다.

• 그래야 서버가 비정상적으로 종료됐을 때 직전까지의 트랜잭션 커밋 내용이 리두 로그에 기록될 수 있고, 리두 로그를 이용해 장애 직전 시점까지 복구가 가능해진다.
• 하지만 커밋될 때마다 리두로그를 디스크에 기록하는 작업은 많은 부하를 유발한다.
• 그래서 리두로그를 어느 주기로 동기화할지 결정하는 innodb_flush_log_at_trx_commit시스템 변수를 제공한다.

innodb_flush_log_at_trx_commit

• 0: 1초에 한번씩 디스크로 기록하고 동기화. 만약 서버가 비정상 종료되면, 최대 1초 동안의 트랜잭션은 커밋됐다고 하더라도 해당 트랜잭션에서 변경한 데이터는 사라질 수 있다.
• 1: 매번 트랜잭션 커밋 시 디스크로 기록하고 동기화. 커밋되면 해당 트랜잭션에서 변경한 데이터는 사라진다.
• 2: 매번 트랜잭션 커밋 시 디스크로 기록되지만, 1초에 한번씩 동기화. 트랜잭션이 커밋되면 변경 내용이 메모리 버퍼로 기록되는 것을 보장한다.
  • 그래서 서버가 비정상 종료되더라도 OS가 정상 작동한다면 해당 트랜잭션 데이터는 사라지지 않는다.
  • 서버와 OS모두 비정상적으로 종료되면 최근 1초동안의 트랜잭션 데이터는 사라질 수 있다.

0과 2로 설정해놓아도, 만약 DDL이 실행되면 리두로그가 디스크로 동기화되기 때문에 1초보다 짧을 수는있다. 하지만 스키마 변경 작업은 자주 실행되는 작업은 아니므로, 리두 로그는 최대 1초 정도 손실이 발생할 수 있다.

innodb_flush_log_at_timeout

• 0과 2일 경우 디스크 동시화 시간간격을 위 시스템변수로 변경할 수 있다.
• 기본 1초이며 일반적인 서비스에서 이 간격을 변경할 이유는 없을 것이다.

리두 로그 파일들의 전체크기가 버퍼풀의 효율성을 결정하기 때문에 신중하게 결정해야한다.

• innodb_log_files_size: 리두 로그 파일 크기 결정
• innodb_log_files_in_group: 리두 로그 파일 개수 결정
• 전체크기 = 파일크기 * 파일개수
• 전체크기는 버퍼풀의 크기에 맞게 선택돼야 변경된 내용을 버퍼풀에 모았다가 한번에 모아 디스크로 기록할 수 있다.
• 하지만 사용량이 많은 DBMS 서버의 경우 리두 로그의 기록작업이 큰 문제가 되는데 이러한 부분을 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링한다.
• 이러한 리두 로그 버퍼링에 사용되는 공간이 로그 버퍼다.

로그버퍼

• 로그 버퍼는 기본값이 16MB 수준에서 설정하는 것이 적합하다.
• 하지마 BLOB이나 TEXT와 같이 큰 데이터를 자주 변경하는 경우 더 크게 설정하는 것이 좋다.

ACID 데이터베이스에서 트랜잭션의 무결성을 보장하기 위해 꼭 필요한 4가지 요소

• A(Atomic): 트랜잭션은 원자성 작업이어야 함을 의미한다.
• C(Consistent): 일관성을 의미한다.
• I(Isolated): 격리성을 의미한다.
• D(Durable): 한번 저장된 데이터는 지속적으로 유지돼야 한다.

4.2.11.1 리두 로그 아카이빙

...

4.2.12 어댑티브 해시 인덱스

어댑티브 해시 인덱스: 사용자가 수동으로 생성하는 인덱스가 아닌, InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스

• B-Tree 인덱스에서 특정 값을 찾는 과정이 매우 빠를 거같지만, DB 서버가 얼마나 많은 일을 하느냐에 따라 B-Tree 인덱스에서 값을 찾는 과정이 느려질 수도 빨라질 수 도있음.
• 특정 값을 찾기 위해 B-Tree의 루트 노드 -> 브랜치 노드 -> 최종 리프노트
• 이런 작업을 동시에 몇천개의 스레드로 실행하면? 자연히 쿼리 성능이 떨어진다.

어댑티브 해시 인덱스는 B-Tree 검색시간을 줄여주기 위해 도입된 기능

• InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고
• 필요할 때마다 어댑티브 해시 인덱스를 검색하여 저장된 페이지를 즉시 찾아감
• B-Tree 루트~리프까지 찾는 비용이 없어지고 CPU는 적은일을 하지만 쿼리 성능은 빨라짐.
• 동시에 빠른 쿼리 처리 가능.

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

• 인덱스 키 값은 B-Tree 인덱스 고유 번호(Id)와 B-Tree 인덱스 실제 키 값 조합으로 생성.
  • 모든 B-Tree 인덱스에 대한 어댑티브 해시 인덱스가 하나의 해시 인덱스에 저장되며, 특정 키 값이 어느 인덱스에 속한 것인지 구분해야한다.
• 데이터 페이지 주소는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가지며, 이는 InnoDB버퍼풀에 로딩된 페이지의 주소를 의미
  • 그래서 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 버퍼 풀에 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지는 사라진다.

어댑티브 해시 인덱스가 활성화되면 처리량이 늘어난다. 하지만 이를 의도적으로 비활성화하는 경우도 있다.

• 디스크 읽기가 많은 경우
• 특정 패턴 쿼리가 많은 경우
• 매우 큰 데이터를 가진 테이블의 레코드를 폭 넑게 읽는 경우

아래는 성능 향상에 도움되는 경우

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

하지만 단순히 도움의 유무를 판단하는건 쉽지 않음.

확실한건 어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼풀)내에서 접근하는 것을 빠르게 만드는 기능이기 때문에, 데이터 페이지를 디스크에서 읽어오는 경우가 빈번하면 아무런 도움이 되지 않는다.

또한 이는 테이블 삭제 작업에도 많은 영향을 미친다.

• 만약 어떤 테이블 인덱스가 어댑티브 해시 인덱스에 적재돼 있다고 가정해보자.
• 이때 테이블을 삭제/변경하게 되면 이 테이블이 가진 모든 데이터 페이지 내용을 어댑티브 해시 인덱스에서 제거해야한다.
• 상당히 많은 CPU 사원을 사용하게 됨. ➡️ 치명적인 작업

따라서 어댑티브 해시 인덱스가 서비스에 도움이 되는지 아닌지 잘 판단해야한다.

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

그냥. InnoDB 엔진 쓰라는 내용.

• MEMORY라는 이름으로 과대평가 받지만 InnoDB 못따라감.
• MyISAM 스토리지 엔진만이 가지는 장점 없음.