[RealMySQL 8.0] 4장 아키텍처

4.1 MySQL 엔진 아키텍처

• 쿼리의 처리는 MySQL 엔진 -> 스토리지 엔진 순서로 처리된다.
• MySQL 엔진
  • MySQL의 핵심적인 기능이 담겨있는 부분이다. 다음 내용은 처리 순서대로 아키텍처를 설명한 것이다.
  • 클라이언트의 연결을 생성한다. : 커넥션 핸들러가 담당한다.
  • 문자열인 SQL을 MySQL이 처리할 수 있도록 구조화한다. : SQL 파서가 담당한다.
    • 기본 문법 오류는 여기서 발견된다.
  • 구조화된 SQL이 유효한 지 검사한다 : 전처리기가 담당한다.
    • 테이블 이름, 칼럼 이름, 내장 함수들이 존재하는지 확인한다.
    • 권한을 확인한다.
  • 쿼리를 효과적으로 실행하기 위해 쿼리 실행 계획을 작성한다. : 옵티마이저가 담당한다.
    • 가장 저렴한 비용으로 쿼리를 수행하기 위한 전략을 세운다.
    • 데이터의 통계 정보, 인덱스 정보 등을 활용하여 쿼리 실행 계획을 생성한다.
    • 옵티마이저가 어떻게 더 나은 선택을 할 수 있을지 유도해야한다.
  • 스토리지 엔진에 명령을 내린다. : 실행 엔진이 담당한다.
    • 옵티마이저가 작성한 쿼리 실행 계획을 실행한다.
    • 핸들러 API 요청 결과를 다른 핸들러 API 입력으로 연결한다.
• 핸들러 API는 데이터 I/O를 담당하는 스토리지 엔진의 인터페이스이다.
  • SHOW GLOBAL STATUS LIKE 'Handler%' 명령으로 핸들러 API의 실행 데이터를 확인할 수 있다.
• MySQL 은 스레딩 기반으로 작동한다.
  • 포그라운드 스레드는 지연될 수 없는 작업을 처리한다.
    • 접속된 클라이언트 수만큼 존재한다.
    • 쿼리를 처리하는 스레드이다.
    • 스레드는 캐시되며, 캐시된 최대 스레드 개수는 thread_cache_size 이다.
    • 데이터를 디스크로부터 메모리에 올려서 읽거나 메모리에 쓰는데까지만 관여하고, 직접적인 데이터 쓰기는 백그라운드 스레드가 담당한다.
  • 백그라운드 스레드는 지연될 수 있는 작업을 처리한다.
    • 인서트 버퍼를 병합하고, 로그와 버퍼풀의 데이터를 디스크에 기록하거나, 데이터를 버퍼로 읽어오고, 잠금이나 데드락을 모니터링한다.
    • 쓰기 스레드 수는 innodb_write_io_threads 시스템 변수로, 읽기 스레드 수는 innodb_read_io_threads 시스템 변수로 설정한다.
  • SELECT * FROM performance_schema.threads; 쿼리로 스레드의 상태를 확인할 수 있다.
• MySQL 서버의 메모리는 글로벌 메모리 영역과 로컬(세션 or 커넥션) 메모리 영역으로 구분된다.
  • 글로벌 메모리 영역은 MySQL 서버가 쿼리를 수행하기 위해 필요한 메모리 영역이다.
    • MySQL 서버 시작 시 운영체제로부터 할당받는다.
    • 모든 스레드에 의해 공유된다.
    • 테이블 캐시, 버퍼 풀, 어댑티브 해시 인덱스, 리두 로그 등 쿼리를 처리하기 위한 메모리 공간이다.
  • 로컬 메모리 영역은 세션이 쿼리를 수행하기 위해 필요한 영역이다.
    • 쿼리 결과를 정렬하기 위해 사용되는 정럴 버펴, 커넥션의 정보를 저장하는 커넥션 버퍼 등이 있다.
    • 스레드 별로 할당되며, 공유되지 않는다.
    • 커넥션이 열려있는 동안 계속 사용되는 메모리 영역과(커넥션, 결과) 쿼리를 실행하는 순간에만 사용되는 메모리 영역이 있다. (정렬, 조인 등)
• 컴포넌트
  • 플러그인의 설계 단점을 보완하기 위해 8.0부터 도입된 기능
  • 비밀번호 검증 컴포넌트 등이 있다.
  • SELECT * FROM mysql.component; 로 확인할 수 있다.
  • 컴포넌트 설치 시 새로운 시스템 변수를 설정해야 할 수도 있으므로, 설치 전 관련 매뉴얼을 읽어보자.

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

• MVCC를 이용한 레코드 기반의 잠금은 InnoDB 엔진만 제공한다.
• 모든 테이블은 프라이머리키를 기준으로 클러스터링 되어 저장된다.
  • 클러스터링 인덱스는 물리적으로 정렬되어 있는 인덱스이다.
  • 물리적으로 정렬되어 있기 때문에 레인지 스캔에 유리하다.
  • 세컨더리 인덱스는 물리적으로 정렬되어 참조가 빠른 프라이머리키를 논리 주소로 사용한다.
• 외래키 지원도 InnoDB 엔진 수준에서 지원한다.
  • 운영환경에서는 여러 이유로 FK를 설정하지 않으나, 개발환경에서는 FK 설정을 해주자.
  • forein_key_check 변수를 설정할 때는 언제나 적절히 키고 끔을 유의하자.
• MVCC는 언두 로그를 이용해 구현된다.
  • 언두 로그는 변경 전의 값이다.
  • 언두 로그로 여러 버전의 데이터가 존재할 수 있다.
  • 언두 로그는 언두 로그 값을 변경한 순차적으로 증하가는 TX ID 값을 가지고 있기 때문에 다른 TX가 어떤 언두를 봐야할 지 알 수 있다.
  • 다른 TX 가 상호간섭하지 않는 일관된 읽기는 언두로그를 이용해 구현된다.
• 자동 데드락 잠지 스레드도 돌고 있으나 성능상 이슈가 있다면 끌 수 있다.
• InnoDB 버퍼 풀
  • 디스크의 데이터 파일이나 인덱스를 메모리에 캐시하는 공간이다. -> 책에서는 캐시라는 표현을 썼으나, 매핑이라는 표현이 더 적절할 것 같다.
  • 쓰기 작업을 지연시킨다. -> mmap 원리랑 같다.
  • 버퍼풀의 크기는 실험적으로 동적으로 조정하자.
  • innodb_buffer_pool_size라는 변수로 조정할 수 있으나, 버퍼풀의 크기를 줄이는 작업은 영향도가 크므로 신중하게 작업하자.
  • 버퍼풀의 데이터는 캐시의 Eviction 과정과 비슷하게 관리된다. 덜 참조되는 데이터는 삭제된다.
  • 데이터를 찾는 순서는 어댑티브 해시 인덱스를 먼저 찾고 그 다음 버퍼풀이다. 즉 버퍼풀의 캐시가 어댑티브 해시 인덱스이다.
  • 체크포인트를 발생 시켜서 리두로그와 버퍼풀의 상태를 동기화한다.
    • 리두로그는 변경 후의 데이터이다. 디스크에 기록되지 않은 리두로그가 많을 수도, 더티 페이지가 많을 수도 있다.
    • 이를 맞추고, 복구 지점 생성하는 것이 체크포인트이다.
    • 메모리의 더티페이지가 디스크에 기록된 리두로그보다 시간상 앞설 경우, 데이터 손실이 생긴다.
      • innodb_flush_log_at_trx_commit 변수로 리두로그를 언제 기록할 지 설정할 수 있다.
      • 0: 1초에 한번씩, 1: 커밋될때마다 바로 fsync 까지, 2: mmap만 수행
      • 중요한 데이터는 1로 설정하자.
    • 메모리의 더티페이지가 디스크에 기록된 리두로그보다 시간상 뒤쳐질 경우, 리두로그를 이용해 복구하면 된다.
• Double Write Buffer
  • 버퍼풀의 내용을 디스크로 기록할 때, IO는 실패할 수 있다. 더 중요한건, 실패했다는 것을 모를 수 있다.
  • 따라서 시퀀셜 접근으로 Double Write 버퍼에 한번에 쓰고, 데이터파일에 기록한다.
  • 안전장치인 셈이다.
• 언두로그
  • 언두 로그는 데이터가 변경되기 전의 값을 보관한다.
  • 변경되기 전의 값을 보관한다는 것은, 내가 아닌 다른 트랜잭션이 사용할 데이터라는 것이다.
  • 롤백이 되거나 잠금없는 읽기를 위해 사용된다.
  • A, B, C 순서로 트랜잭션이 시작됐을 때, A만 트랜잭션이 끝나지 않았을 때, B,C 의 언두 로그는 보존되어야한다.
    • A가 B,C 가 변경한 데이터를 읽을려고 할 때, 언두로그의 값을 줘야하기 때문이다.
    • 언두 로그가 많아지면 성능 저하가 있으므로 언두 로그 양을 모니터링하고, TX를 가능한 짧게 유지하자.
  • 8.0 부터 언두 로그는 외부의 별도 로그파일에 기록되도록 개선되었다.
• 체인지 버퍼
  • 체인지 버퍼는 인덱스 업데이트를 위한 메모리 상의 버퍼이다.
  • PK를 포함하여 유니크 인덱스는 체인지 버퍼를 사용하지 못한다.
  • 체인지 버퍼 머지 스레드가 디스크로 병합한다.
  • 5.5 부터 체인지버퍼가 비효율적일 때는 끌 수 있도록 되었다.
• 리두 로그 및 로그 버퍼
  • 리두 로그는 변경된 데이터 값을 기록한다.
  • 리두 로그는 쓰기 비용이 낮은 로그 형식 (Append Only 를 의미)을 가지고 있으므로 데이터 변경은 리두 로그에 먼저 쓰고, 변경 내용을 실제 물리적인 주소에 반영하는 것은 비동기적으로 수행한다.
  • 데이터 정합성보다 성능이 중요한 경우, 리두 로그도 버퍼링할 수 있다.
    • innodb_flush_log_at_trx_commit
      • 0: 1초에 한번씩 기록하고 동기화한다.
      • 1: 커밋 시마다 디스크로 기록되고 동기화까지 된다. (fsync)
      • 2: 커밋 시마다 OS의 메모리 버퍼로 기록된다. (mmap)
      • 가급적 1로 설정하자.
    • innodb_flush_log_at_timeout
      • 디스크 동기화 간격을 변경할 수 있다. (innodb_flush_log_at_trx_commit이 0이나 2일 경우)
      • 특별히 변경할 만한 이유는 없다.
  • 리두 로그도 아카이빙할 수 있다.
    • 백업 시, 실시간 데이터 변경이 많아서 리두 로그 엔트리를 복사하지 못하고, 리두 로그가 덮어씌여질 경우
  • 리두 로그도 비활성화 할 수 있다.
    • 대용량 데이터를 한번에 적재하는 경우
    • ALTER INSTANCE (DISABLE or ENABLE) INNODB REDO_LOG
    • 리두 로그 활성화하는 것을 잊지 말자.
• 어댑티브 해시 인덱스
  • B-Tree 에 대한 캐시이다.
  • 버퍼 풀에 있는 데이터만 어댑티브 해시 인덱스에 적재될 수 있다.
  • B-Tree ID 와 B-Tree 인덱스의 실제 키 값으로 해시 키가 생성된다. (스토리지 엔진 당 어댑티브 해시 인덱스는 하나만 존재하기 때문)
  • 파티션될 수 있다.
  • 버퍼풀 사용이 적거나 넓은 범위의 레코드를 조회한다면 비효율적일 수 있다.
  • SHOW ENGINE INNODB STATUS 명령의 내용으로 확인할 수 있다.
  • 사용 여부는 hit ratio, 메모리 공간, CPU 사용량을 종합해서 판단하자.