DB/MySQL

[Real MySQL 8.0] #4 아키텍처

Tomining 2024. 9. 8. 21:08
Real MySQL 8.0 (1권)을 읽고 요약한 자료입니다.

 

4.1 MySQL 엔진 아키텍처

4.1.1 MySQL의 전체 구조

Real MySQL 8.0 1권 P.77

  • MySQL 엔진
    • 커넥션 핸들러, SQL 파서, 전처리기, 옵티마이저 등
    • 요청된 SQL 문장을 분석 최적화
  • 스토리지 엔진
    • (동시에) 여러 스토리지 엔진을 사용할 수 있다.
    • MyISAM, InnoDB, Memory

 

4.1.2 MySQL 스레딩 구조

Real MySQL 8.0 1권 P.80

SELECT
	thread_id,name
    , type,processlist_user
    , processlist_host 
FROM
	performance_schema.threads
ORDER BY
	type,thread_id
;

Real MySQL 8.0 1권 P.81

마지막 'thread/ sql/ one_connection' 스레드만 실제 사용자의 요청을 처리하는 Foreground Thread다.

  • Foreground Thread
    • SQL 요청 -> Foreground Thread 생성 -> SQL 수행 -> Foreground Thread는 Thread Cache로 복귀
    • MySQL 서버에 접속된 갯수만큼 생성되며, 무제한은 아니다. (thread_cache_size로 설정)
  • Background Thread
    • 인서트 버퍼(Insert Beffer)를 병합하는 스레드
    • 로그를 디스크로 기록하는 스레드
    • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
      • READ는 지연처리 없음
      • WRITE는 지연(버퍼링) 처리 => 보통 처리량이 많기에 Disk 최적화를 위해 설정
    • 데이터를 버퍼로 읽어 오는 스레드
    • 잠금이나 데드락을 모니터링 하는 스레드

4.1.3 메모리 할당 및 사용 구조

Real MySQL 8.0 1권 P.84

  • 세션 메모리 영역 - 사용자 요청이 있을 때 커넥션 별 할당되는 영역

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

Real MySQL 8.0 1권 P.86

 

  • 스토리지 엔진 외에도 여러 기능들(인증 등)이 플러그인 형태로 제공된다.
  • 핸들러(Handler) - MySQL엔진과 스토리지엔진 사이에 데이터가 오가려면 핸들러(명령)를 통해야 한다.
  • SHOW ENGINES; 를 통해 상태를 확인할 수 있다.
    • Support 컬럼: YES, DEFAULT(필수엔진), NO(서버에 없음), DISABLED

4.1.5 컴포넌트

플러그인의 단점을 보완하기 위해 만들어졌다.

  • 플러그인은 오직 MySQL 서버와 인터페이스 할 수 있고, 플러그인끼리는 통신할 수 없다.
  • 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않다.
  • 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어렵다.

MySQL 8.0의 비밀번호 검증 기능은 컴포넌트로 개선되었다. (이전에는 플러그인 형태로 제공)

 

4.1.6 쿼리 실행 구조

Real MySQL 8.0 1권 P.91

  • 쿼리 파서 - SQL을 DB가 이해할 수 있도록 변환(트리구조)
  • 전처리기 - 테이블명이나 컬럼명 등 확인, 접근 권한 확인
  • 옵티마이저 - 쿼리 최적화
  • 실행엔진 - 옵티마이저(경영진), 실행엔진(중간관리자), 핸들러(실무자)
  • 핸들러
  • 쿼리 캐시 - 8.0에서 제거된 기능, DDL 실행시 관련된 캐시를 삭제하는 과정에서 성능 저하
  • 스레드 풀 - Enterprise Edition에서만 제공
  • 트랜잭션 지원 메타데이터
    • 파일(FRM파일, TRN, TRG, PAR 등)로 관리하던 5.7 이전 버전
    • 8.0부터는 (시스템)테이블 구조로 관리 -> InnoDB 스토리지 엔진 사용

 

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

Real MySQL 8.0 1권 P.98

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

  • 기본적으로 PrimaryKey 기준으로 클러스터링되어 저장된다. -> Clustered Index
  • Secondary Index는 레코드 주소 대신 PrimaryKey 논리 주소를 사용한다.
  • 오라클의 IOT와 유사하다.
  • MyISAM은 Clustered Index를 지원하지 않으며, Primary/Secondary Index 모두 동일하다.

4.2.2 외래 키 지원

  • InnoDB 스토리지 엔진 레벨에서 지원하는 기능이다. (MyISAM or MEMORY 엔진에서는 지원 불가)
  • 변경사항 발생시 여러 테이블로 전파(CASCADE) 될 때 주의가 필요하다.

4.2.3 MVCC(Multi Version Concurrency Control)

  • 잠금을 사용하지 않는 일관된 읽기를 제공하기 위함이다.
  • InnoDB는 이를 위해 Undo Log를 이용해 구현하고 있다.
  • 격리수준에 따라 차이가 있을 수 있다. (아래 기본값 참고)
    • (Oracle) READ_COMMITED
    • (MySQL) REPEATABLE READ

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

Real MySQL 8.0 1권 P.103

  • Undo Log를 이용하여 Commit되지 않은 데이터(경기)는 제외하고 읽을 수 있다.
  • 트랜잭션이 오랫동안 유지되는 경우 MySQL 서버가 느려지는 등의 문제가 있을 수 있으니, 트랜잭션은 가능한 빠르게 처리해야 한다.

4.2.5 자동 데드락 감지

  • DeadLock 상태를 체크하기 위해 Wait-for List 형태로 관리한다.
  • (InnoDB에서) DeadLock 감지 Thread가 주기적으로 트랜잭션을 체크하여 DeadLock에 빠진 트랜잭션 중 하나를 강제 종료한다.
    • Undo Log 양을 기준으로 더 적은 트랜잭션을 강제 종료
  • 동시 처리 스레드가 많은 경우 CPU 자원을 더 많이 소모할 수 있다.
  • DeadLock 감지 Thread를 중지하기 보다 Timeout(innodb_lock_wait_timeout)을 설정하자.

4.2.6 자동화된 장애 복구

MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 복구 작업이 자동으로 진행된다. 만약 자동 복구를 할 수 없다면 MySQL 서버는 종료되는데, innodb_force_recovery 설정을 통해 시작해야 한다.

  • 1(SRV_FORCE_IGNORE_CORRUPT) - 테이블 스페이스나 인덱스 페이지 손상 무시
  • 2(SRV_FORCE_NO_BACKGROUND) - Main Thread가 Undo Purge 때 문제가 발생한 경우 background thread 중지
  • 3(SRV_FORCE_NO_TRX_UNDO) - Commit되지 않은 작업을 그대로 둠
  • 4(SRV_FORCE_NO_IBUF_MERGE) - Insert Buffer 실행 않함
  • 5(SRV_FORCE_NO_UNDO_LOG_SCAN) - UndoLog 무시
  • 6(SRV_FORCE_NO_LOG_REDO) - RedoLog 모두 무시

숫자가 커 질수록 손상 범위가 크다는 의미이며, 복구 가능성은 그 만큼 적어진다.

4.2.7 InnoDB Buffer Pool

  • 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.
  • 쓰기 작업을 지연시켜 일괄 작업 때 활용한다. => Random Access 를 줄일 수 있다.

Real MySQL 8.0 1권 P.110

 

4.2.7.4 Buffer Pool Flush

Dirty Page(Commit이 되었으나 아직 Disk에 기록되지 않은 데이터)를 디스크에 동기화 하기 위해 2가지 Flush 기능을 Background로 실행한다.

  • Flush_list flush
    • RedoLog 엔트리가 사용하는 공간을 주기적으로 비워야 한다.
    • 이전에 Dirty Page를 Disk로 동기화가 필수인데, 이 때 Flush_list flush를 한다.
    • Dirty Page를 오래된 순서대로 동기화를 진행한다.
  • LRU_list flush
    • 사용 빈도가 낮은 Dirty Page들을 제거하여 읽어올 공간을 확보한다.

4.2.8 Double Write Buffer

Real MySQL 8.0 1권 P.121

  • A~E까지 Dirty Page를 우선 Double Write Buffer에 묶어서 기록
  • 데이터 파일에 A~E를 Write
    • 이 과정을 (하드웨어 이슈 등으로) 실패한 경우 Buffer와 비교하여 복구

4.2.9 Udo Log

DML로 변경되기 이전 버전의 데이터를 별도로 백업하는데, 이 때 백업된 데이터를 Undo Log라고 한다.

  • 트랜잭션 보장
  • 격리 수준 보장 => Non-Locking Consistent Read 가 가능해 진다.

Real MySQL 8.0 1권 P.126

  • Undo Tablespace -> 1~128개 Rollback Segment -> 1개 이상의 Undo Slot
  • DML은 최대 4개의 Undo Slot을 사용
    • (트랜잭션이 2개의 Undo Slot을 사용한다고 가정하면?)
    • 최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16*(롤백 세그먼트 개수)*(언두 테이블 스페이스 개수)
    • (기본설정) 131072개 = (16*1024/16*128*2/2)

4.2.10 Change Buffer

데이터 변경시 Index 변경도 필요한데, 이는 Random Acces가 발생한다. 그래서 Buffer Pool에 있으면 즉시 수행하지만 그렇지 않다면 임시 공간에 담아두고 사용자에게 결과를 반환하는데, 이 때 활용하는 임시 공간이 Change Buffer라고 한다. Unique Index는 Change Buffer를 사용할 수 없다. Change Buffer 내용은 Change Buffer Merge Thread(Background Thread)를 통해 병합 되는데, 5.5 이전 버전에서는 Insert Merge Thread만 있었다.

4.2.11 Redo Log & Log Buffer

Redo Log는 트랜잭션의 4가지 요소인 ACID 중 D(Duration)과 가장 밀접하게 연관돼 있다. MySQL서버가 비정상적으로 종료됐을 때 데이터를 잃지 않게 해 주는 안전장치다.

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

innodb_flush_log_at_trx_commit(0~2)를 통해 디스크 동기화 방식을 설정할 수 있다. Redo Log 파일 크기(innodb_log_file_size) 와 개수(innodb_log_files_in_group)로 파일 전체 크기가 결정되며, InnoDB 버퍼 풀 크기에 맞춰 적절히 설정 되어야 한다. (로그 버퍼 크기는 16MB가 기본값)

 

(8.0부터 제공되는 기능)

  • Redo Log Archive
  • Redo Log 비활성화 - 데이터 복구나 대용량 Insert 시 비활성화하여 시간을 절약할 수 있다.

 

4.2.12 Adaptive Hash Index

사용자가 생성하는 일반적인 B-Tree 인덱스와 다르게 InnoDB 스토리지 엔진에서 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이며, innodb_adaptive_hash_index로 ON/OFF 할 수 있다. B-Tree 인덱스는 검색이 O(logN)인 반면 Hash는 O(1)의 시간복잡도를 가지며, 검색시간을 줄여준다.

Real MySQL 8.0 1권 P.138

 

Adaptive Hash Index를 활성화 함으로서 QPS 성능이 올라간 것을 확인할 수 있다. B-Tree 루트 노드부터 검색이 줄면서 InnoDB 내부 잠금 횟수도 획기적으로 줄어든다. (이전 버전까지는) 인덱스가 하나의 메모리 객체인 이유로 경합이 심했지만 8.0부터는 파티션 기능을 제공하면서 경합이 줄어들었다.

 

팔방미인 같은 Adaptive Hash Index지만 성능 향상에 크게 도움이 안 되는 경우도 있다.

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

(당연하겠지만) 위 케이스와 반대인 경우는 성능 향상에 도움이 될 것이다.

 

MyISAM 스토리지 엔진에 대해서는 따로 정리하지 않았다.

 

4.4 MySQL 로그 파일

  • Error Log - my.cnf 파일에 log_error 경로에 생성
    • MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
    • 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지
    • 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
    • 비정상적으로 종료된 커넥션 메시지(Aborted Connection)
    • InnoDB의 모니터링 또는 상태 조회 명령의 결과 메시지
    • MySQL의 종료 메시지
  • General Log - my.cnf 파일에 general_log_file 경로에 생성
  • Slow Query Log - long_query_time(초단위, 소숫점으로 설정시 마이크로초 단위 설정 가능) 이상 소요된 쿼리 기록
    • 쿼리 통계 - 쿼리 실행 시간(Exec time), 잠금 대기시간(Lock time) 등 평균 및 최소/최대 값 표시
    • 실행 빈도 및 누적 실행 시간순 랭킹 - 쿼리별 응답 시간과 실행 횟수 표시
    • 쿼리별 실행 횟수 및 누적 실행 시간 상세 정보 표시

 

저작자표시 비영리