MySQL8.0 chapter4
4장. 아키텍쳐
개요 (MySQL 서버의 구성)
- 머리 : MySQL 엔진
- 손발 : 스토리지 엔진 (핸들러 API의 구현으로 InnoDB, MyISAM이 대표적)
- 쿼리 실행 과정
- [SQL 파서 ↔ SQL 옵티마이저 ↔ SQL 실행기]{MySQL 엔진} ←→ [데이터 읽기/쓰기]{스토리지 엔진} ←→ [디스크]
4.1 MySQL 엔진 아키텍쳐
구성
- 커넥션 핸들러: 클라이언트의 접속/쿼리 요청 처리
- SQL 파서 및 전처리기
- 쿼리 옵티마이저
스레딩 구조
- MySQL은 스레드 기반의 동작
- 포그라운드 스레드 (클라이언트 스레드)
- 클라이언트 수 만큼, 클라이언트 사용자가 요청하는 쿼리 처리
- 백그라운드 스레드
- 데이터 디스크 기록, 버퍼로 가져오기
- 데드락 모니터링
- 대다수가 백그라운드 스레드
메모리 구조
- 글로벌 메모리 영역 - (모든 스레드에 의해 공유)
- InnoDB 버퍼 풀
- MyISAM 키 캐시
- 바이너리 로그 버퍼
- 리두 로그 버퍼
- 테이블 캐시
- 세션(커넥션) 메모리 영역 - (클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역, 독립적)
- 조인 버퍼
- 정렬 버퍼
- 네트워크 버퍼
- 리드 버퍼
플러그인 & 컴포넌트
- 요구사항에 맞게 직접 만들어 쓸 수 있음 (ex. 스토리지 엔진)
- 컴포넌트는 플러그인 보다 발전된 아키텍쳐
쿼리 실행 구조
- 쿼리 파서 : 쿼리 → 토큰 트리
- 전처리기 : 트리 구조와 권한 검증
- 옵티마이저 : 비용 낮고 빠르게 처리할 방법 고안
- 실행엔진 : 만들어진 계획대로 각 핸들러에게 요청 + 받은 결과를 또 다른 핸들러로 요청
- 핸들러(스토리지 엔진) : 디스크 접근
스레드 풀
- MySQL 커뮤니티 스레드 풀 지원 X
- Percona는 지원 O
- CPU 코어 갯수 만큼 스레드 그룹 생성
- 시스템 변수 thread_pool_stall_limit 밀리초 안에 못끝내면 새로운 스레드 생성해서 스레드 그룹에 추가
- 추가해도 시스템 변수 thread_pool_max_threads 갯수는 못 넘음
- 생각보다 쓰레드 풀 도입한다고 성능 향상 눈에 띄진 않을수도
4.2 InnoDB 스토리지 엔진 아키텍쳐
개요
- MySQL 스토리지 엔진 중 가장 많이 사용되는 InnoDB
- 스토리지 엔진 중 거의 유일하게 레코드 기반 락 => 높은 동시성 처리 가능
PK에 의한 클러스터링
- PK 값의 순서대로 디스크에 저장 (클러스터링 인덱스)
- 모든 세컨더리 인덱스는 레코드의 주소 대신, PK의 값을 논리적인 주소로 사용
FK 지원
- 데드락 발생할 가능성이 높은데 이유는...
- InnoDB에서 FK는 부모/자식 테이블의 해당 칼럼에 모두 인덱스 생성이 필요
- 변경 시에는 부모/자식 테이블에 데이터 있는지 검사하니 락이 여러 테이블로 전파... → 데드락
- foreign_key_checks 시스템 변수 off 하는 것도 방법
MVCC (Multi Version Concurrency Control)
- Undo Log의 도움을 받아 잠금을 사용하지 않는 일관된 읽기 기능 제공하고자 함
- 데이터 변경 시...
- 새로운 값 → InnoDB 버퍼풀
- 옛날 값 → Undo Log
- 이 상태에서 SELECT 시, 격리 수준에 따라 다른 영역에서 데이터 읽어서 반환
- Multi Version (2개의 버전으로 데이터를 유지하고) Concurrency (동시성 처리를 이를 통해) Control (해냄)
- 트랜잭션에서...
- COMMIT → 지금 InnoDB 버퍼 풀 데이터 영구적으로
- ROLLBACK → Undo Log 데이터 다시 복구
- Undo Log의 백업 데이터는 이를 필요로 하는 트랜잭션이 더는 없을 때 삭제됨
잠금없는 낙관된 읽기 (Non-Locking Consistent Read)
- InnoDB에서 SELECT는 락 없이 뚝딱 가능
자동 데드락 감지
- 데드락 감지 스레드 : 주기적으로 잠금 대기 그래프 검사해 교착 상태 트랜잭션 중 하나 강제 종료
- 언두 로그 레코드를 더 적게 가진 트랜잭션이 롤백 대상
- 감지 스레드가 부담스럽다면 innodb_deadlock_detect 를 끄거나, innodb_lock_wait_timeout을 짧게 가져가자
자동화된 장애 복구
- InnoDB 데이터 파일은 기본적으로 MySQL 서버 시작 시 항상 자동 복구 수행
- 자동 복구될 수 없다면, MySQL 서버 종료됨
- innodb_force_recovery설정을 통해 강제 시작 가능
- 설정값 1 ~ 6. 각기 다른 방법
- 풀 백업 + 바이너리 로그로 복구하는 것도 방법
InnoDB 버퍼 풀
- 가장 핵심적인 부분 → 위의 4.1 에서 메모리 구조 전체 스레드가 공유하는 영역에서도 언급됨
- 역할
- 디스크의 데이터 파일, 인덱스 정보를 메모리에 캐시해두는 공간
- 쓰기 지연 + 일괄 처리
- 랜덤 디스크 작업 횟수 줄임
- 크기 설정
- OS, 클라이언트 스레드가 사용할 메모리 고려해서 설정
- 크게 변경은 OK, 작게 변경은 큰일 날 수 있으니 주의
- 128MB 단위로 크기 변경 가능
- 기본적으로 버퍼 풀은 8개로 초기화, 버퍼 풀은 인스턴스당 5GB 정도로 추천
- 버퍼 풀 구조
- 메모리를 페이지 조각으로 쪼개서 관리하며, 필요로 할 때 디스크로 부터 읽어서 저장
- 3개의 자료 구조로 관리 (LRU 리스트, 플러시 리스트, 프리 리스트)
- 프리 리스트 : 빈 공간. 새로 읽어올 때 사용
- LRU 리스트 : 캐싱 용도로 디스크 읽기 최소화
- 플러시 리스트 : 디스크로 동기화 되지 않은 더티 페이지의 목록 관리
- 버퍼 풀과 리두 로그
- InnoDB에서 페이지 변경이 일어나면...
- 변경 내용을 리두 로그에 기록 + 버퍼 풀의 데이터 페이지에도 변경 내용 반영 (더티 페이지)
- 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결됨
- 하지만 리두 로그와 더티 페이지는 항상 디스크로 기록이 보장된 것은 아님 (복구 기준점 역할)
- 다시 역할로...
- 데이터 캐시 기능 향상 → 버퍼 풀 사이즈 키우면 향상됨
- 쓰기 기능에 대한 버퍼링 기능 향상 → 얘는 꼭 버퍼 풀 사이즈 키운다고 늘지 않음. 리두 로그와 연관 있음
- 리두 로그
- 리두 로그는 1개 이상의 더티 페이지를 참조 중인 듯 (확실치 않음)
- 리두 로그 파일 공간에는 LSN(Log Sequence Number)가 존재
- 특정 시점에 체크 포인트 발생하면, 이전 반영된 LSN + 1 ~ 현재 체크포인트 LSN 까지 디스크 반영
- 그렇다보니, 역할 2가지를 모두 잘 이득보려면 버퍼 풀/리두 로그 영역의 적당한 밸런스가 중요
- 버퍼 풀 플러시
- 앞서 살펴본 더티 페이지를 디스크에 쓰는 작업
- 플러시 리스트 플러시
- 얼마나 많은 더티 페이지를 한번에 디스크에 쓸래?
- innodb_max_dirty_pages_pct : 더티 페이지 / 버퍼 풀 비율
- innodb_max_dirty_pages_pct_lwm : 일정 수준 더티 페이지 발생 시 디스크로 기록
- 이 방식은 약간 고전 방식이고... 발전된 방식으로는
- 어댑티브 플러시 기능 (default ON)
- 리두 로그 증가 속도 분석 → 적절한 수준의 더티 페이지가 버퍼 풀에 유지되도록 디스크 쓰기
- 버퍼 풀 백업 및 복구
- 쌩으로 시작하면 성능 많이 떨어져
- 5.6 부터 버퍼 풀 덤프/적재 가능
- 시스템 변수 innodb_buffer_pool_dump_at_shutdown , innodb_buffer_pool_load_at_startup 설정해서 자동으로 백업/복구 하도록 세팅 권장
- Double Write Buffer
- 리두 로그는 공간 아끼려고 더티 페이지의 변경된 내용만 기록
- 근데 일부만 기록되면 속상함 (Partial-page/Torn-page)
- 그래서 더티 페이지 기록할 친구들 모아서 Double Write Buffer에 기록하여 안정성 확보
언두 로그
- 트랜잭션 + 격리 수준 보장 위해 언두 로그로 데이터 백업
- 트랜잭션 : 롤백 시 언두로그에 있는 친구 가져옴
- 격리 수준: MVCC에서 본 것 처럼 격리 수준에 따라 알맞은 데이터 반환
- 원래는 한번 사이즈 늘면 안 줄었는데, 8.0 부터는 디스크 공간 줄일 수 있음
- 그래도 언두 로그 용량 급격히 느는지 모니터링 대상
- 언두 로그는 테이블 스페이스 외부의 별도 로그 파일에 기록 됨
- 언두 로그 슬롯(공간) 이 부족하면 트랜잭션 시작도 못할 수 있으니, 적절히 필요한 동시 트랜잭션 갯수에 맞춰 설정해주세요
체인지 버퍼
- 인덱스도 변경될 일이 있는데, 많은 자원 소모 대상
- 인덱스에 따라 다른데
- 인덱스 페이지가 버퍼풀에 있음 → 즉시 업데이트
- 디스크로 부터 읽어와야 함 → 임시 공간 저장 (이게 체인지 버퍼)
- 유니크 인덱스의 경우, 중복 여부를 체크해야 하기에 체인지 버퍼 사용 불가
리두 로그 및 로그 버퍼
- 리두 로그 : MySQL 갑자기 꺼지더라도 데이터 잃지 않게 해주기 위함
- 리두 로그는 트랜잭션 커밋 시, 즉시 디스크로 기록되게 해주세요
- 8.0 부터는 리두 로그 아카이빙도 가능하답니다
어댑티브 해시 인덱스
- InnoDB에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스!
- B-Tree도 결국 루트 - 리프 까지 가긴해야하는데 이걸 줄여줌
- 자주 읽히는 데이터 페이지의 키 값으로 해시 인덱스
- 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리됨
- 버퍼 풀에서 사라지면 (flush), 해당 정보 사라짐
- 어댑티브 해시 인덱스 사용해도 데이터 페이지를 디스크에서 하도 많이 읽어오면 도움 안 됨
- 어댑티브 해시 인덱스 도움을 많이 받을수록 SELECT 는 좋지만, 변경은 치명적인 작업
4.3 MyISAM 스토리지 엔진 아키텍처
키 캐시
- InnoDB의 버퍼 풀과 비슷한 역할
- 키 캐시는 인덱스만을 대상으로 작동
- 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링
운영체제의 캐시 및 버퍼
- 디스크 I/O 해결해줄 캐시 기능 자체적으로 X
- 데이터 읽기/쓰기는 항상 OS의 디스크 읽기/쓰기로 이어짐
- OS 캐싱이 중요하다는 뜻으로도 해석 됨. 메모리 OS에 양보하세요
데이터 파일과 PK(인덱스 구조)
- InnoDB: PK 클러스터링
- MyISAM: PK 클러스터링 없이 데이터 파일 Heap 공간 처럼 활용
- Insert 순서대로 데이터 파일에 저장
- ROWID라는 물리적인 주솟값 가짐
- PK, IDX 모두 ROWID 참조
4.4 MySQL 로그 파일
- MySQL 문제 생기면 로그 파일에 잘 적어줌
- 시작 과정 정보성 로그
- 비정상적 종료 트랜잭션 복구 로그
- 쿼리 처리 도중 문제 로그
- 비정상적 종료 커넥션 로그
- InnoDB 모니터링 / 상태 조회 명령 로그
- MySQL 종료 로그
슬로우 쿼리 로그
- 쿼리 튜닝
- 서비스 적용 전 전체적으로 튜닝 (우리 아지트 DDL 요청)
- 서비스 운영 중에 발생하는 슬로우 쿼리 (와치타워 알림 가끔 오는거)
- long_query_time 시스템 변수에 설정한 시간 넘어가면 슬로우 쿼리로 기록
