MySQL 엔진 아키텍처

4.1.1 MySQL의 전체 구조

4.1.1.1 MySQL 엔진

• 클라이언트로부터 접속 및 쿼리 요청을 하는 커넥션 핸들러
• SQL 파서 및 전처리기
• 쿼리의 최적화된 실행을 위한 옵티마이저

 

4.1.1.2 스토리지 엔진

• 데이터를 디스크 스토리지에 저장하거나 이로부터 읽어오는 부분
• 여러개 동시에 사용 가능
• 핸들러 API를 만족하면 스토리지 엔진 구현해 MySQL 서버에 추가해서 사용 가능
• 테이블이 사용할 스토리지 엔진 지정하여 이후 해당 테이블의 읽기/변경 작업 처리 가능
• mysql> CREATE TABLE test_table(fd1 INT, fd2 INT) ENGINE = INNODB;
• 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM) 나 InnoDB 버퍼 풀(InnoDB) 가짐

4.1.1.3 핸들러 API

핸들러 요청

• MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할때 각 스토리지 엔진에 쓰기/읽기 요청
• InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고 받음

mysql> SHOW GLOBAL STATUS LIKE 'Handler%';

 

4.1.2 MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아닌 스레드 기반

백그라운드 스레드의 개수는 MySQL 서버의 설정 변경에 따라 가변적

 

4.1.2.1 포그라운드 스레드(클라이언트 스레드, 사용자 스레드)

• 최소 MySQL 서버에 접속한 클라이언트의 수 만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 담당
  • 클라이언트가 MySQL 서버에 접속하게 되면 MySQL 서버는 그 클라이언트의 요청을 처리해 줄 스레드를 생성해 그 클라이언트에게 할당
• 스레드 캐시(Thread cache)
  • 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 스레드 캐시로 돌아감
• 스레드 캐시에 일정 대수 이상(최대 스레드 개수 : thread_cache_size)의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재
• 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져옴
  • 없으면 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와 작업 처리
  • MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리
  • InnoDB 테이블은 데이터 버퍼 / 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리

4.1.2.2 백그라운드 스레드

InnoDB의 백그라운드 처리

• 종류
  • 인서트 버퍼(Insert Buffer)를 병합하는 스레드
  • ✳️ 로그 스레드
    • 로그를 디스크로 기록하는 스레드
  • ✳️ 쓰기 스레드
    • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
  • 데이터를 버퍼로 읽어 오는 스레드
  • 잠금 / 데드락을 모니터링하는 스레드
• 5.5~ 데이터 쓰기 스레드와 읽기 스레드의 개수를 2개 이상 지정 가능
  • innodb_write_io_threads와 innodb_read_io_threads 시스템 변수로 스레드 개수 지정
  • 읽기 스레드는 클라이언트 스레드에서 처리되어 많이 설정할 필요 없지만 읽기 스레드는 일반적인 내장 디스크 사용시 2~4 / DAS나 SAN 같은 스토리지는 충분히 사용
• 사용자 요청 처리시 쓰기 작업은 지연 즉, 버퍼링 되어 처리 가능하지만 읽기는 지연 불가
  • INSERT / UPDATE / DELETE 쿼리로 데이터 변경시 데어터가 디스크의 데이터 파일로 완전히 저장될 때 까지 기다리지 않아도 됨
  • ↔ MyISAM은 사용자 스레드가 쓰기 작업까지 함께 처리, 버퍼링 불가

4.1.3 mysql 메모리 할당 및 사용 구조

메모리 영역은 MySQL 서버 내 존재하는 많은 스레드가 공유해서 사용하는 공간이지 여부에 따라 구분

4.1.3.1 글로벌 메모리 영역

• 모든 메모리 공간은 MySQL 서버가 시작되며 OS로부터 할당됨
  • OS 따라 메모리 공간을 100% 할당 / 100% 예약 후 필요시 조금씩 할당
• 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당
  • N개 할당도 가능하지만 클라이언트의 스레드 수와 무관하게 모든 스레드에 공유
• 종류
  • 테이블 캐시
  • InnoDB 버퍼 풀
  • InnoDB 어댑티브 해시 인덱스
  • InnoDB 리두 로그 버퍼

 

4.1.3.2 로컬 메모리 영역(세션 메모리 영역)

• MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역
• 각 클라이언트 스레드 별 독립 할당으로 절대 공유되지 않음
• MySQL 서버가 메모리 부족으로 멈출 수 있으므로 적절한 메모리 공간 설정 중요
• ✳️ 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우 MySQL이 메모리 공간 할당조차도 하지 않을 수 있음
  • 소트 버퍼, 조인 버퍼
• 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고(커넥션 버퍼 / 결과 버퍼) 그렇지 않고 쿼리를 실행한느 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼 / 조인 버퍼)
• 종류
  • 정렬 버퍼(Sort buffer)
  • 조인 버퍼
  • 바이너리 로그 캐시
  • 네트워크 버퍼

 

4.1.4 플러그인 스토리지 엔진 모델

전문 검색 엔진을 위한 검색어 파서, 인증 등 플러그인으로 구현되어 제공

(SHOW PLUGINS 명령으로 확인 가능)

 

단점

• MySQL 서버와 인터페이스, 플러그인끼리 통신 불가
• 캡슐화 안됨
  • MySQL 서버의 변수나 함수 직접 호출
• 상호 의존 관계 설정할 수 없어 초기화 어려움

핸들러

• 어떤 기능을 호출하기 위해 사용하는 운전대와 같은 역할
• MySQL 엔진이 스토리지 엔진을 조정하기 위해(데이터 읽기/저장 하도록 명령) 사용
• Handler_* 상태 변수
  • MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미

✳️ 하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다

• 스토리지 엔진은 단순히 데이터 읽기/쓰기 영역만 처리하고 실질적인 GROUP BY나 ORDER BY 와 같은 복잡한 처리는 MySQL 엔진 > 쿼리 실행기 에서 처리

MySQL 서버(mysqld)에서 지원하는 스토리지 엔진

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

• YES : 사용가능으로 활성화
• DEFAULT : 사용가능으로 활성화, 필수 스토리지 엔진(이거 없으면 MySQL 시작 못할수 있음)
• NO : 현재 MySQL 서버에 포함 안됨
  • 사용시 MySQL 다시 빌드(컴파일)
• DISABLE : 포함되었는데 파라미터에 의해 비활성화

 

4.1.5 컴포넌트

8.0~ 플러그인 아키텍처 대체

 

4.1.6 쿼리 실행 구조

4.1.6.1 쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)로 분리해 트리 형태의 구조로 만들어냄

기본 문법 오류는 여기서 발견

4.1.6.2 전처리기

파서 과정에서 만들어진 쿼리 문장에 구조적인 문제점 있는지 확인

• 각 토큰을 테이블 이름 / 칼럼 이름 / 내장 함수와 같은 매체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한등을 확인
• 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러짐

4.1.6.3 옵티마이저

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정

4.1.6.4 실행 엔진

실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 다른 핸들러 요청의 입력으로 연결

• 예 : 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다면?
  1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
  2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
  3. 읽어온 레코드들을 1번에 준비한 임시 테이블로 저장하라고 핸들러에게 요청
  4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
  5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김

 

4.1.6.5 핸들러(스토리지 엔진)

MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 읽어옴

 

4.1.7 복제

16장 참고

 

4.1.8 쿼리 캐시

SQL 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리 실행시 테이블을 읽지 않고 즉시 반환

• 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 중요 역할
• 8.0~ MySQL 서버의 기능에서 제거됨
  • 테이블의 데이터가 변경되면 이와 관련된 캐시는 모두 삭제 해야 했기 때문에 버그

 

4.1.9 스레드 풀

내부적으로 사용자의 요청을 처리하는 스레드의 개수를 줄여 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 하여 서버의 자원 소모 줄임

• ✅ 실행 중인 스레드들을 CPU가 잘 처리 가능한 수준으로 줄여 빠르게 처리하는 기능임으로 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우 쿼리 처리가 더 느려짐
• 제한된 스레드 만으로 CPU가 처리 가능하다면 CPU의 프로세서 친화도(Processor affinity) 도 높이고 OS 입장에선느 불필요한 Context switch를 줄여 오버헤드를 낮출 수 있음
• 기본적으로 CPU 코어 개수 만큼 스레드 그룹을 생성하는데 thread_pool_size 시스템 변수를 사용해 변경 가능
  • 동일하게 맞추면 프로세서 친화도를 높이는 것이 좋음
  • MySQL 서버가 처리해야 할 요청이 생기면 스레드 풀로 처리를 이관하는데, 스레드 풀이 처리 중인 작업이 있으면 thread_pool_oversubscribe(default:3)에 설정된 개수 만큼 추가로 더 받아 처리
    • ✅ 이 값이 너무 크면 스케줄링해야 할 스레드가 많아져 스레드 풀이 비효율적 작동
• 스레드 그룹의 모든 스레드가 일 처리시 스레드 풀은 새로운 작업 스레드(Worker thread)를 추가할지, 아니면 기존 작업 스레드가 일을 처리할 때까지 기다릴지 여부를 판단
  • 스레드 풀의 타이머 스레드 는 주기적으로 스레드 그룹의 상태를 체크해서 thread_pool_stall_limit 에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 작업 못 끝내면 새로운 스레드를 생성해 스레드 그룹에 추가함
    • 모든 스레드가 작업 처리시 새로운 쿼리 요청 들어오면 스레드 풀은 thread_pool_stall_limit 만큼 기다려야 새로 들어온 요청 처리 가능
    • 응답 시간에 민감시 이를 줄여야 하지만 0에 가까운 값이라면 스레드 풀 사용하지 말아라
  • 전체 스레드 풀에 있는 스레드의 개수는 thread_pool_max_threads 개수 못넘음
• 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능 제공
  • 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해 주면 해당 트랜잭션이 가지고 있던 잠금이 빨리 해제되고 잠금 경합을 낮춰 전체적인 처리 성능 향상 가능

 

4.1.10 트랜잭션 지원 메타데이터

데이터 딕셔너리(=메타데이터)

• 테이블의 구조 정보와 스토어드 프로그램 등의 정보
• ~5.7 까지 테이블 구조를 파일 저장
  • 파일 기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않아 테이블 생성/변경 도중 MySQL 서버가 비정상적으로 종료 되면 일관되지 않은 상태로 남음
  • 데이터베이스나 테이블이 깨짐
• 8.0~ 데이터 딕셔너리, 시스템 테이블은 모두 InnoDB 테이블에 저장
  • 시스템 테이블
    • MySQL 서버 작동시 필요한 인증, 권한 관련 테이블
  • mysql DB는 통째로 mysql.ibd라는 이름의 테이블스페이스에 저장되어 주의
    • 실제 테이블은 보여주지 않고 INFORMATION_SCHEMA.TABLES/COLUMS 뷰로 조회 가능하게 함
  • 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료되더라도 스키마 변경이 완전한 성공
  • InnoDB가 아닌 스토리지 엔진은 SDI(직렬화를 위한 포맷) 파일 사용
    • *.sdi
    • InnoDB 테이블의 구조도 SDI 파일로 변환 가능

 

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

스토리지 엔진 중 거의 유일하게 레코드 기반 잠금 을 제공하여 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어남

 

4.2.1 PK에 의한 클러스터링

• InnoDB의 모든 테이블은 기본적으로 PK를 기준으로 클러스터링 되어 저장됨
  • 모든 세컨더리 인덱스는 레코드의 주소 대신 PK 값을 논리적인 주소로 사용됨
• PK가 클러스터링 인덱스 임으로 PK를 이용한 레인지 스캔은 빠름~ ⇒ 쿼리의 실행 계획에서 PK는 다른 보조 인덱스에 비해 비중이 높게 설정 (다른 보조 인덱스보다 PK 선택 확률 높음)
• ↔ MyISAM에서는 클러스터링 키를 지원하지 않아 PK와 세컨더리 인덱스는 구조 차이 없음 (오직 PK는 유니크 제약을 가진 세컨더리 인덱스) PK를 포함한 모든 인덱스는 물리적인 레코드의 주소 값(ROWID)만 가짐

 

4.2.2 외래 키 지원

• InnoDB 스토리지 엔진 레벨에서 지원(MyISAM, MEMORY 테이블에서 불가)
• ✅ 주의
  • 외래 키는 부모, 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모, 자식 테이블에 데이터가 있는지 체크 → 잠금이 여러 테이블 전파 → 데드락 발생
  • 수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업 실패 가능성
    • foreign_key_checks 시스템 변수 off시 체크 작업 일시적으로 멈출 수 있음
    • 급할때 사용하고 이후 외래키 관계를 가지는 부모, 자식의 레코드를 삭제해 일관성 맞춘 후 다시 on 시켜야 함
    • ON DELETE CASCADE 와 ON UPDATE CASCADE 옵션도 무시

 

4.2.3 MVCC(Multi Version Concurrency Control)

• 잠금을 사용하지 않는 일관된 읽기를 제공하기 위한 기능
• 언두 로그(Undo log)를 이용해 구현
• 멀티 비전
  • 하나의 레코드에 대해 여러 개의 버전이 동시에 관리 된다

 

UPDATE 문장 실행시 영역별 업데이트

• 버퍼풀
  • 커밋 실행 여부와 관계 없이 새로운 값으로 업데이트
• 디스크의 데이터 파일
  • 체크포인트나 InnoDB의 Write 스레드에 의해 새로운 값 업데이트 / 혹은 유지
    • InnoDB가 ACID를 보장함으로 일반적으로 버퍼풀과 상태 동일

 

COMMIT / ROLLBACK 안된 상태에서 id=12로 레코드 조회시 어디를 조회?

→ MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준(Isolation level)에 따라 다름

• READ_UNCOMMITED
  • 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어 반환
    • 변경된 상태의 데이터를 반환
• READ_COMMITED나 그 이상 격리 수준(REPEATABLE_READ, SERIALIZABLE)인 경우 아직 커밋되지 않아 변경되기 전 영역인 언두 영역 반환

⇒ DBMS에서는 이 상황을 MVCC라고 표현

• 하나의 레코드(id=12)에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 상황에 따라 달라짐
  • 트랜잭션이 길어지면 언두에서 관리하는 예전 데이터가 삭제되지 않아 많아지고 언두 영역이 저장되는 시스템 테이블스페이스의 공간이 늘어갈 수 있음

이때 COMMIT시 InnoDB는 변경 작업 없이 지금 상태를 영구 데이터로 만들고 (언두 영역은 필요로 하는 트랜잭션이 없을때 삭제) ROLLBACK 실행시 언두 영역에 있는 백업 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역 내용 삭제

 

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

• MVCC 기술을 이용 잠금을 걸지 않아 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 읽기 가능
• 격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMITTED, REPEATABLE_READ 수준인 경우 INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행
  • InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두 로그 를 사용한다.
• 오랜 시간 동안 활성 상태인 트랜잭션으로 MySQL 서버가 느려지거나 문제가 발생하는 것은 바로 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 발생 → 트랜잭션이 시작 되었다면 가능한 빨리 롤백 / 커밋을 통해 트랜잭션을 완료

 

4.2.5 자동 데드락 감지

• 내부적으로 잠금이 교착 상태에 빠지진 않았는지 확인하기 위해 잠금 대기 목록 을 그래프(Wait-for List) 형태로 관리
• 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사, 교착 상태에 빠진 트랜잭션들을 찾아서 강제 종료
  • 강제 종료 기준 : 언두 로그 양
    • 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상(부하 적음)
• InnoDB 스토리지 엔진의 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금(LOCK TABLES 명령으로 잠긴 테이블)은 볼 수 없어서 데드락 감지가 불확실할 수 있는데, innodb_table_locks 시스템 변수를 활성화하면 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금 까지 감지 가능
  • innodb_table_locks 활성화
• 일반적으로 작업이 크게 부담되지 않으나, 동시 처리 스레드 가 매우 많아지거나 각 트랜잭션이 가지는 잠금의 개수 가 많아지면 데드락 감지 스레드가 느려짐
  • 잠금 목록 검사시 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트(잠금 테이블)에 새로운 잠금을 걸고 데드락 스레드를 찾게 됨 → 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드 는 더 이상 작업을 진행하지 못하고 대기하면서 서비스에 악영향
  • → 동시 처리 스레드가 매우 많은 경우 데드락 감지 스레드는 더 많은 CPU 자원을 소모 가능
  • innodb_deadlock_detect를 off로 설정시 데드락 감지 스레드는 더 이상 동작하지 않음
    • 단, 이경우 InnoDB 스토리지 엔진 내부에서 2개 이상의 트랜잭션이 상대방이 가진 잠금을 요구 (데드락 상황)이 발생해도 누군가 중재하지 않음으로 무한정 대기
      • innodb_lock_wait_timeout를 50초보다 적게 설정하기
    • on 설정시 데드락 상황에서 일정 시간(innodb_lock_wait_timeout) 동안 획득하지 못하면 쿼리는 실패하여 자동으로 요청이 취소되고 에러는 반환

 

4.2.6 자동화된 장애 복구

MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된(Partial write) 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행

• InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작시 항상 자동 복구 를 수행하고 이 단계에서 자동으로 복구 될 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 서버는 종료됨
  • innodb_force_recovery 시스템 변수 설정해 MySQL 서버 시작하여 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일 손상여부 검사를 선별적으로 진행
    • 로그 파일 손상시 6 설정후 서버 기동
    • 데이터 파일 손상시 1 설정후 서버 기동
    • 모르면 1부터 +1 해가며 실행
• 일단 MySQL 서버가 기동되고 InnoDB 테이블이 인식되면 mysqldump를 이용해 데이터를 가능한 만큼 백업하고 그 데이터로 MySQL 서버의 DB와 테이블을 재 생성
  • 숫자에 따른 처리
    • https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
• MySQL 서버가 시작되지 않으면 백업을 이용해 다시 구축하는 방법뿐….
  • 백업이 있으면 마지막 백업으로 데이터베이스를 새로 구축, 바이너리 로그를 사용해 최대한 장애 시점 까지 데이터 복구
  • 마지막 풀 백업 시점부터 장애 시점까지 바이너리 로그가 있으면 InnoDB의 복구를 이용하는 것보다 풀 백업과 바이너리 로그로 복구를 하는 편이 데이터 손실 적음
  • 백업이 있지만 복제의 바이너리 로그가 없거나 손실시 마지막 백업 시점까지만 복구 가능

 

4.2.7 InnoDB 버퍼풀

• 디스크의 데이터 파일 이나 인덱스 정보 를 메모리에 캐시해 두는 공간
• 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 함
  • INSERT / UPDATE / DELETE 처럼 데이터 변경 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시킴
  • → 버퍼 풀이 변경된 데이터를 모아 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있음

4.2.7.1 버퍼 풀의 크기 설정

• 운영체제와 각 클라이언트 스레드가 사용할 메모리를 충분히 고려해 설정 필요
• ✅ 레코드 버퍼
  • 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 사용하는 버퍼로 사용하는 공간
  • 커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 용도로 사용되는 메모리 공간 많이 필요
  • 별도 설정 불가, 각 커넥션 개수와 커넥션이 읽고 쓰는 테이블 개수에 따라 결정
  • 동적으로 해제되기도 함으로 정확히 필요한 메모리 공간 계산 불가
• 5.7~ 부터 버퍼 풀의 크기 동적으로 조절 가능 → 작은 값 설정해서 조금씩 증가
  • OS의 전체 메모리 공간이 8GB 미만
    • 50% 정도만 InnoDB 버퍼 풀 설정, 나머지 메모리 공간은 MySQL 서버, OS, 다른 프로그램 사용
  • 8GB 이상
    • 50%에서 점점 늘리기
  • 50GB
    • 15GB ~ 30GB 정도 OS와 다른 응용 프로그램을 위해 남기자
  • innodb_buffer_pool_size 시스템 변수로 크기 설정
  • 크리티컬한 변경이므로 가능하면 MySQL 서버가 한가한 시점은 고르고 줄이는 작업은 영향도가 매우큼으로 하지 말자 (MySQL 매뉴얼 참조)
  • 내부적으로 128MB 청크 단위로 쪼개 관리
    • 버퍼 풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합을 많이 유발하는 이슈 개선을 위해
    • 버퍼풀이 작은 버퍼 풀로 쪼개지면서 개별 버퍼 풀 전체를 관리하는 잠금(세마포어) 자체도 경합 분산
  • innodb_buffer_pool_instances로 버퍼풀 여러 개로 분리 → 버퍼 풀 인스턴스
    • default : 8개
      • 전체 버퍼 풀 메모리가 1GB 미만이면 버퍼풀 인스턴스는 1개
    • 버퍼풀 할당 메모리 공간이 40GB 이상이면 버퍼 풀 인스턴스 당 5GB 되게 인스턴스 개수 설정

4.2.7.2 버퍼 풀의 구조

• 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기(innodb_page_size) 조각으로 쪼개 InnoDB 스토리지 엔진이 데이터가 필요 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장
• 페이지 크기 조각을 관리하기 위해 LRU(Least Recently Used) 리스트, 플러시(Flush) 리스트,
  • 프리 리스트
    • 실제 사용자로 채워 지지 않은 빈 페이지 목록으로 사용자의 쿼리가 새롭게 디스크의 데이터 페이지 를 읽어야 할 때 사용
  • LRU(Least Recently Used) 리스트
    • LRU[OLD 서브리스트 영역]와 MRU(Most Recently Used)리스트[NEW 서브리스트 영역] 결합
    
    
    • 목적
      • 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해 디스크 읽기를 최소화
    • 데이터를 찾는 과정
      1. 필요한 레코드가 저장된 페이지가 버퍼 풀에 있는지 검사
         1. InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색
         2. 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지 검색
         3. 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
      2. 1번에서 언급된 InnoDB 어댑티브 해시 인덱스는 데이터의 위치를 빠르게 찾기 위해 사용되는 기술입니다. 어댑티브 해시 인덱스는 주요 키를 통해 데이터 페이지를 빠르게 찾을 수 있게 해주는 해시 테이블을 생성합니다. 따라서 InnoDB는 먼저 어댑티브 해시 인덱스를 사용하여 필요한 페이지가 버퍼 풀에 있는지 빠르게 확인합니다.
      3. 네, 1과 2에서 언급된 "페이지"는 동일한 영역을 가리킵니다. "페이지"라는 용어는 데이터베이스에서 디스크의 일정한 블록을 의미합니다.
      4. 디스크에 필요한 데이터 페이지를 버퍼풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
      5. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
         1. Read Ahead와 같이 대량 읽기의 경우 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만 실제 쿼리에서 사용되지 않을 수 있음으로 MRU로 이동 안됨
      6. 버퍼 풀에 상주하는 데이터 페이지 는 사용자가 얼마나 최근 에 접근했는지에 따라 나이(AGE)가 부여되고, 버퍼 풀에 상주하는 동안 오랫동안 사용되지 않으면 나이가 오래되어(Aging) 해당 페이지는 버퍼 풀에서 제거(Eviction) 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨짐
      7. 필요한 데이터가 자주 접근되었다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가
  • 플러시 디스크
    • 디스크로 동기화되지 않은 데이터를 가진 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록 관리
    • 한 번 데이터 변경이 가해진 데이터는 플러시 디스크에서 관리되고 특정 시점이 되면 디스크 기록
    • 데이터 변경시 변경 내용을 리두 로그 에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용 반영
      • 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결
      • 리두 로그가 디스크로 기록되었다고 해서 데이터 페이지가 디스크로 기록 된 것을 보장하지 않음
      • 체크포인트
        • MySQL 서버가 시작될 때 InnoDB 엔진이 리두 로그의 어느 부분부터 복구를 실행할지 판단하는 기준
        • InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화
• 프리(Free) 리스트라는 3개의 자료 구조를 관리

 

4.2.7.3 버퍼 풀과 리두 로그

InnoDB 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정시 쿼리의 성능이 빨라짐

• 하지만 이는 데이터 캐시와 쓰기 버퍼링 중 데이터 캐시 기능 만 향상 시키는 것
• 쓰기 버퍼링까지 향상하려면 리두 로그 관계 이해 필요

• 버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지(Clean Page)와
  • 더티페이지는 디스크와 메모리(버퍼풀)의 데이터 상태가 달라 언젠가 디스크로 기록(무한정 버퍼풀내 머물 수 없음)
• INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 더티 페이지(Dirty Page)를 가짐
• InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용
  • 데이터 변경이 계속 발생시 리두 로그 파일에 기록되었던 로그 엔트리는 새로운 로그 엔트리로 덮힘 → InnoDB 스토리지 엔진은 전체 리두 파일에서 재사용 가능한 공간과 재사용 불가능한 공간(활성 리두 로그)를 구분해 관리해야 함(화살표를 가진 엔트리)
• LSN(Log Sequence Number)
  • 리두 로그 파일의 공간은 계속 순환되어 재사용 하지만 매번 기록될 때 마다 로그 포지션 이 증가된 값
  • InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 디스크의 리두 로그와 버퍼풀의 더티 페이지를 동기화
• 체크포인트 에이지(Checkpoint Age)
  • 가장 최근 체크포인트의 LSN과 마지막 로그 엔트리의 LSN의 차이 = 활성 리두 로그 공간의 크기
• 디스크 동기화
  • 버퍼풀의 더티 페이지는 특정 리두 로그 엔트라와 관계를 가지고, 체크 포인트 발생시 체크 포인트 LSN 보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화
  • 체크포인트 LSN보다 작은 LSN 값을 가진 리두 로그 엔트리도 디스크로 동기화
  • 버퍼풀의 크기가 100GB 이하에선 리두 로그 파일의 전체 크기를 5~10GB로 선택하고 조금씩 늘리기

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

• 8.0~ 더티 페이지를 디스크에 동기화하는 부분(더티 페이지 플러시)에서 예전과 같은 디스크 쓰기 폭증 현상은 발생하지 않음
• 성능 문제가 없으면 굳이 이 시스템 변수 조정할 필요 없음
• 성능상 악영향 없이 디스크 동기화 하는 플러시 기능을 백그라운드로 실행
  • 플러시 리스트(Flush_list) 플러시
  • LRU 리스트(LRU_list) 플러시

4.2.7.4.1 플러시 리스트 플러시

✳️ 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트티가 사용하는 공간을 비워야 함

→ InnoDB 버퍼풀의 더티 페이지가 먼저 디스크로 동기화 되어야함

• InnoDB 스토리지 엔진은 주기적으로 플러시 리스트(Flush_list) 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지를 순서대로 디스크에 동기화
  • 얼마나 많은 더티 페이지를 한 번에 디스크에 기록하느냐가 성능에 영향
  • 더티 페이지를 디스크로 동기화 하는 스레드 : 클리너 스레드
    • innodb_page_cleaners 로 스레드 개수 조절
    • innodb_buffer_pool_instances로 버퍼풀 인스턴스 조절
• InnoDB 버퍼풀은 클린 페이지 뿐만아니라 DML(INSERT, UPDATE, DELETE)에 의해 변경된 더티 페이지 함께 가짐
  • 무한정 유지 불가능함으로 전체 버퍼풀이 가진 페이지의 90% 만큼 더티 페이지 가짐 → ✳️ 너무 높아지면 변경 가능하지만 더티 페이지를 많이 가지고 있을 수록 디스크 쓰기 작업 을 버퍼링 함으로써 여러 번의 디스크 쓰기를 한 번으로 줄이는 효과 → 유지하자
  • 더티 페이지가 많을 수록 디스크 쓰기 폭발(Disk IO Burst) 현상 발생 가능
    • innodb_max_dirty_pages_pct_lwm
  • → 일정 수준 이상의 더티 페이자가 발생하면 이를 디스크로 기록하게 함
• 데이터베이스 서버에서 어느 정도의 디스크 읽기/쓰기가 가능한지 설정
  • 이는 백그라운드 스레드가 수행하는 디스크 작업으로 대부분 버퍼풀의 더티 페이지 쓰기
  • innodb_io_capacity : 디스크 적철 처리 수준
  • innodb_io_capacity_max : 디스크가 최대 성능 발휘시 어느정도 디스크 읽기/쓰기가 가능한지
  • 쿼리 처리를 위해 쓰기 뿐만 아니라 읽기도 해야 함으로 초당 1000 IOPS 처리 가능하다고 해서 그대로 시스템 변수 설정하면 안됨
• 어댑티브 플래시
  • innodb_adaptive_flushing로 버퍼 풀의 더티 페이지 비율이나 innodb_io_capacity, innodb_io_capacity_max에 의존하지 않고 새로운 알고리즘 사용
  • 리두 로그의 증가 속도(어느 정도로 기록해야 할지 분석하기 위해)를 분석해 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기
• 더티 페이지를 디스크에 기록시 디스크에 근접한 페이지 중 더티 페이지 존재시 함께 기록
  • innodb_flush_neighbors
    • 하드디스크라면 1~2 / SSD면 비 활성화

 

4.2.7.4.2 LRU 리스트 플러시

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

• LRU 리스트를 끝부분부터 시작해 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수 만큼 스캔 → 더티페이지는 디스크에 동기화하게 하며, 클린 페이지는 즉시 프리(Free) 리스트로 페이지를 옮김
• LRU 리스트 스캔은 (innodb_buffer_pool_instances * innodb_lru_scan_depth) 수만큼 수행

4.2.7.5 버퍼 풀 상태 백업 및 복구

✳️ 워밍업

• 버퍼 풀은 쿼리의 선능에 매우 밀접하게 연결
• 쿼리 요청이 매우 빈번한 서비스 셧다운이후 재시작하면 쿼리 처리 성능이 평상시보다 1/10 인 경우가 존재하는데 버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비되어 있어 디스크에서 데이터를 읽지 않아도 쿼리가 처리 될 수 있음으로 ⇒ 디스크가 버퍼 풀에 적재되어 있는 상태를 워밍업
• 버퍼 풀이 잘 워밍업된 상태에서는 그렇지 않은 경우보다 몇십 배의 쿼리 처리 속도를 보임
• ~5.5 서비스 오픈전 강제 워밍업을 위해 주요 테이블과 인덱스에 대해 풀 스캔
• 5.6~ 버퍼 풀 덤프 및 적재 기능 도입
  • 백업의 경우 ib_buffer_pool 이라는 파일로 생성하고 LRU 리스트에 적재된 데이터 페이지의 메타 정보만 가져와 저장함으로 크기가 작고 빠르게 백업
  • 이를 다시 버퍼 풀로 복수하는 작업은 각 테이블의 데이터 페이지를 다시 디스크에서 읽어 옴
  • 진행 상태
  • mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'\\G
  • 중간에 멈추고 다시 재시작
  • mysql > SET GLOBAL innodb_buffer_pool_load_abort=ON;
• //MySQL 서버 셧다운 전에 버퍼 풀의 상태 백업 mysql > SET GLOBAL innodb_buffer_pool_dump_now=ON; //MySQL 서버 재시작 후, 백업된 버퍼 풀의 상태 복구 mysql > SET GLOBAL innodb_buffer_pool_load_now=ON;
• MySQL 서버 시작시 자동으로 백업 복구 자동화
  • innodb_buffer_pool_dump_at_shutdown,innodb_buffer_pool_load_at_startup활성화

 

4.2.7.6 버퍼 풀의 적재 내용 확인

infomation_schma.innodb_cached_indexes 확인

 

4.2.8 Double Write Buffer

파셜 페이지(Partial-page), 톤 페이지(Torn-page)

• 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록
• 더티 페이지를 디스크 파일로 플러시시 일부만 기록되는 문제 발생시 해당 페이지 내용 복구 불가

→ 해당 문제 막기위해 Double-Write 기법 이용

1. 여러 더티 페이지를 우선 묶어 한 번의 디스크 쓰기로 DoubleWrite 버퍼에 기록
2. 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기
3. 2번으로 정상적으로 기록시 DoubleWrite 버퍼 필요 없어짐
   1. 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래의 목적으로 사용
   2. ✳️ 비 정상적으로 종료시 InnoDB 스토리지 엔진의 재 시작시 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사

• innodb_doublewrite 시스템 변수로 제어
• HDD 처럼 자기 원판(Platter)이 회전하는 저장 시스템에서는 어차피 한 번의 순차 디스크 쓰기 를 함으로 별로 부담되지 않지만 SSD 처럼 랜덤 IO나 순차 IO의 비용이 비슷한 저장 시스템에서 는 부담스러움
  • 데이터 무결성이 매우 중요한 시스템에서는 DoubleWrite 활성화
  • 성능을 위해 리두 동기화 설정을 1이 아닌 값으로 설정시 DoubleWrite 비활성화

 

4.2.9 언두 로그

트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업

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

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

~5.5 한 번 증가한 언두 로그 공간을 줄어들지 않음

• 대용량 데이터 처리(100GB 테이블 삭제)
• 오랜 시간 동안 트랜잭션 실행시
  • A, B, C 트랜잭션 순서대로 실행 후 B, C가 완료되었어도 A가 활성 상태이면 B, C 트랜잭션이 만들어낸 언두 로그는 삭제되지 않음
• InnoDB 스토리지 엔진은 언두 로그의 이력을 필요한 만큼 스캔해야만 필요한 레코드를 찾을 수 있어 쿼리의 성능이 떨어짐
• 디스크 용량뿐만 아니라 백업시 그만큼 더 복사 해야 하는 문제점

8.0~ 언두 로그를 돌아가면서 순차적으로 사용해 디스크 공간을 줄이고, MySQL 서버가 필요한 시점에 사용 공간을 자동으로 줄여줌

• 여전히 서비스 중인 MySQL 서버에서 활성 상태의 트랜잭션이 장시간 유지되는 것은 성능상 안좋음

 

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

언두 테이블스페이스

• 언두 로그가 저장되는 공간
• 하나의 언두 테이블스페이스는 1~128개의 롤백 세그먼트를 가지고 롤백 세그먼트는 1개 이상의 언두 슬롯을 가짐
• 최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 * (롤백 세그먼트 개수) * (언두 테이블스페이스 개수)
  • 일반적인 16KB InnoDB에서 기본 설정시 131072(=161024 / 16128*2 /2)
  • 언두 로그 공간이 남는건 문제 되지 않지만 부족한 경우 트랜잭션 시작 불가

8.0~

• 시스템 테이블스페이스 외부의 별도 로그 파일에 기록

Undo tablespace truncate

• 언두 테이블스페이스 공간을 필요한 만큼 남기고 불필요하게 할당된 공간을 OS로 반납
• 8.0~ 자동/수동 모두 가능

 

4.2.10 체인지 버퍼

RDBMS에서 레코드가 INSERT/UPDATE 될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트 하는 작업도 필요

• 인덱스 업데이트는 랜덤하게 디스크를 읽음으로 인덱스가 많으면 상당히 많은 자원 소모
• 변경해야할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 디스크로부터 읽어와 업데이트하면 임시 메모리 공간(=체인지 버퍼)에 저장해 두고 사용자에게 반환
  • 사용자에게 결과를 전달하기 전 중복 체크를 해야하는 유니크 인덱스는 체인지 버퍼 사용 불가
• 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 백그라운드 스레드(=체인지 버퍼 머지 스레드)에 의해 병합(8.0~ 부터 모든 DML 문에서 가능)
• 5.5~ 부터 innodb_change_buffering을 통해 작업 종류별 체인지 버퍼 활성화

 

4.2.11 리두 로그 및 로그 버퍼

• 트랜잭션의 4가지 요소인 ACID 중 D(Durable)에 해당하는 영속성과 가장 밀접
  • 서버가 비정상적으로 종료시 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전 장치
• 대부분의 데이터베이스 서버는 데이터 변경 내용을 로그에 먼저 기록
  • 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료 구조를 가지고 있어 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요하고 상대적으로 큰 비용 필요 → 성능 저하를 막기 위해 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있고, 비정상 종료시 리두 로그 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전으로 복구
  • 성능을 위해 데이터 파일 뿐만아니라 리두 로그를 버퍼링 할 수 있는 InnoDB 버퍼풀이나 로그 버퍼 같은 자료 구조를 가짐
• 비 정상적 종료시 InnoDB 스토리지 엔진의 데이터파일은 두 종류의 일관되지 않은 데이터 가짐
  • 커밋되었지만 데이터 파일에 기록되지 않은 데이터
    • 리두 로드 저장 데이터를 데이터 파일에 복사만 하면 됨
  • 롤백되었지만 데이터 파일에 이미 기록된 데이터
    • 언두 로그를 데이터 파일에 복사 하면 됨
    • 변경이 커밋, 롤백, 트랜잭션 실행 중간인지 확인 용으로 리두 로그 필요
• 리두 로그는 트랜잭션 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정 권장
  • 리두 로그를 디스크에 기록하는 작업은 많은 부하 유발
  • innodb_flush_log_timeout 시스템 변수로 변경
• 전체 리두 로그 파일 크기 = 리두 로그 파일 크기(innodb_log_file_size) * 리두 로그 파일 개수 (innodb_log_files_in_group)
  • 리두 로그 파일의 전체 크기가 InnoDB 버퍼 풀의 크기에 맞게 적절히 선택되어야 변경된 내용을 버퍼 풀에 모았다가 한 번에 모아서 디스크에 기록
  • 로그 버퍼의 크기는 기본값은 16MB로 설정, BLOB / TEXT와 같이 큰 데이터를 자주 변경하는 경우 더 크게 설정

4.2.11.1 리두 로그 아카이빙

8.0~ 데이터 변경이 많아서 리두 로그가 덮어쓰인다고 하더라도 백업이 실패하지 않게 함

• 리두 로그 아카이빙은 리두 로그 파일에 로그 엔트리가 추가시 함께 기록되는 방식
• 아카이빙 시작 세션이 끊어지지 않아야 함을 주의

4.2.11.2 리두 로그 활성화 및 비활성화

• 트랜잭션이 커밋되어도 데이터 파일은 즉시 디스크로 동기화되지 않지만, 리두 로그(트랜잭션 로그)는 항상 디스크로 기록됨
• 비활성화를 통해 데이터를 복구하거나 대용량 데이터를 한번에 적재시 시간 단축 가능

 

4.2.12 어댑티브 해시 인덱스

• InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스
  • innodb_adaptive_hash_index 시스템 변수를 이용해 활성화/비활성화
• B-Tree 인덱스에서 특정 값을 찾는 과정은 정말 빠른가?
  • 성능은 상대적
  • 데이터베이스 서버가 얼마나 많은 일을 하느냐에 따라 달라짐
  • 루트 → 브랜치 → 리프 노드까지 찾아가는 작업을 몇천개의 스레드로 실행시 CPU는 엄청난 프로세스 스케줄링을 하게 되고 쿼리 성능 떨어짐
• 어댑티브 해스 인덱스는 B-Tree 검색 시간을 줄여주기 위해 사용
  • 검색시 노드 탐색 비용이 적어지고 InnoDB 내부 잠금(세마포어)의 횟수도 줄어들게 되며 CPU가 적은 일을 함으로 쿼리 성능이 빨라지고 더 많은 쿼리 처리 가능
• 인덱스 키 값(B-Tree 인덱스의 고유번호(Id)와 B-Tree 인덱스의 실제 키 값)과 키 값이 저장된 데이터 페이지의 메모리 주소 로 이루어짐
  • InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스는 하나만 존재하는 메모리 객체로 B-Tree 인덱스의 고유번호가 포함됨
    • 내부 잠금을 줄이기 위해 어댑티브 해시 인덱스의 파티션 기능 제공
      • innodb_adaptive_hash_index_parts
  • 데이터 페이지의 주소는 InnoDB 버퍼 풀에 로딩된 페이지의 주소 로 버퍼 풀에 올려진 데이터 페이지만 관리되어 버퍼 풀에 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지 정보 사라짐
• 성능 향상에 도움이 되지 않는 경우
  • 디스크 읽기가 많은 경우
    • ✅ 어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀)내에서 접근하는 것을 더 빠르게 만드는 기능임으로 데이터 페이지를 디스크에서 읽오는건 도움 안됨
    • 상당히 큰 메모리를 사용 가능함으로 공짜 점심이 아니다
    • 해시 인덱스의 효율이 없어도 계속 해시 인덱스 사용
      • 데이터 페이지의 인덱스 키가 해시 인덱스로 만들어져야 하고 불필요한 경우 제거되어야 하며 그 키 값이 인덱스에 있든 없든 검색해봐야 한다
  • 특정 쿼리의 패턴이 많은 경우(조인이나 LIKE 패턴 검색)
  • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
• 성능 향상에 도움이 됨
  • 디스크의 데이터가 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
  • 동등 조건 검색(동등 비교과 IN 연산자)
  • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
• ⭐⭐⭐ 삭제하거나 변경시 이 테이블이 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거해야 함 → 많은 CPU 작업을 사용하고 처리 성능이 느려짐
• default가 사용중이며 비 활성화시 hash searches/s 값이 0
  • 어댑티브 해시 인덱스를 효율은 해시 인덱스 히트율과 메모리 공간 그리고 CPU 사용량 종합해 확인

 

출저 : Real MySQL 8.0 (1권) 개발자와 DBA를 위한 MySQL 실전 가이드 [ 전면개정판 ] 백은빈, 이성욱저 , 위키북스