[4장] 인덱스 #7
Description
04. 아키텍처
4.1 MySQL 엔진 아키텍처
- MySQL 서버 = MySQL 엔진 + 스토리지 엔진
- MySQL 엔진: 프론트, 기획자, 옵티마이제이션 및 실행계획을 세움
- 스토리지 엔진: 백엔드, 실무자, 세워진 계획을 실제로 실행
- 스토리지 엔진에 따라 트랜잭션 처리 방법이 달라진다.
- 스토리지 엔진에서 보는 트랜잭션과, MySQL 엔진에서 보는 테이블락은 다르다.
4.1.1 MySQL 전체 구조
- MySQL 독특한 구조
- 보통은 모놀리식 같은 느낌
- MySQL 은 plugin 형태 (MSA 같은 느낌?) 으로 잘 나뉘어짐
- 장점
- 유지보수 좋음
- 단점
- 예상과 다른 동작을 할 수 있는 복잡함
- 장점
- MySQL 엔진
- SQL 인터페이스
- SQL 파서
- SQL 옵티마이저
- 캐시 & 버퍼
- 스토리지 엔진
- InnoDB
- MyISAM
- Memory
- 성능 향상을 위해서 키 캐시(MyISAM) 버퍼풀 (InnoDB)
4.1.1.1 MySQL 엔진
- 커넥션 핸들러
- SQL 파서
- 전처리기
- 옵티마이저
- ANSI SQL (American Standard SQL 지원)
4.1.1.2 스토리지 엔진
- InnoDB 스토리지 엔진
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB
- 캐싱하는 법
- MyISAM 키캐시
- InnoDB 버퍼 풀
4.1.1.3 핸들러 API
- 핸들러(Handler) 요청: 스토리지 엔진에 쓰기 또는 읽기 요청
- 핸들러 API: 핸들러 요청에 사용되는 API
SHOW GLOBAL STATUS LIKE 'Handler%'- MySQL 엔진과 스토리지 엔진 사이에 주고받는 API
4.1.2 MySQL 스레딩 구조
- Foreground 스레드 타입
- Background 스레드 타입
SELECT thread_id, name, type, processlist_user, processlist_host
FROM performance_schema, threads ORDER BY type, thread_id4.1.2.1 포그라운드 스레드(클라이언트 스레드)
클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 스레드
- 기본적으론 클라이언트 당 thread
- 클라이언트 요청시 스레드가 한개씩 물리는데, 최소 접속한 클라이언트 갯수만큼 존재
- 근데 쓰레드를 만드는 비용이 커서 쓰레드 캐시라는 걸로 미리 만들어놓음
- 따라서 실제 접속한 클라이언트보다 살짝 더 많은 쓰레드가 존재할 수 있음
- 이를 조절할 수 있는 thread_cache_size 라는 설정 값이 존재
- 쓰레드 풀과 쓰레드 캐시는 다르다!
- 쓰레드 캐시는 그냥 쓰레드를 미리 생성해 놓고, 연결해줌
- 요청과 쓰레드가 1 대 1 매핑, 처리 종료 시에 쓰레드 캐시에 쓰레드가 max 개수를 넘어가면 해당 쓰레드를 종료
- 쓰레드 캐시에 max 보다 작으면 반환
- 쓰레드 풀은 만들어 놓은 쓰레드를 재사용할 수 있음
- 요청과 쓰레드가 1 대 1 매핑이 아니라, 요청이 큐 자료구조로 들어감
- 쓰레드 캐시는 그냥 쓰레드를 미리 생성해 놓고, 연결해줌
4.1.2.2 백그라운드 스레드
- Insert Buffer 를 병합하는 스레드
- 로그를 디스크로 기록하는 스레드
- InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
- 데이터를 버퍼로 읽어 오는 스레드
- 잠금이나 데드락을 모니터링하는 스레드
4.1.3 메모리 할당 및 사용 구조
- 글로벌 메모리 영역
- InnoDB 버퍼 풀
- MyISAM 키 캐시
- Binary 로그 버퍼
- Redo 로그 버퍼
- 테이블 캐시
- 모든 스레드에서 공유하는 영역
- 세션(커넥션) 로컬 메모리 영역
- Join 버퍼
- 정렬(Sort) 버퍼
- 네트워크 버퍼
- Read 버퍼
- 스레드 별로 절대 공유하지 않는 영역
4.1.4 플러그인 스토리지 엔진 모델
- 스토리지 엔진 플러그인
- 인증, 전문 검색 파서, 쿼리 재작성 플러그인
- 비밀번호 검증, 커넥션 제어 플러그인
4.1.5 컴포넌트
기존의 플러그인 아키텍처를 컴포넌트 아키텍처로 대체 중
4.1.6 쿼리 실행 구조
SQL 요청
-> MySQL 엔진
-> 쿼리파서
-> 전처리기
-> 옵티마이저 (DBA 핵심역량)
-> 쿼리 실행기
-> 스토리지 엔진(핸들러)
- InnoDB, MyISAM, Memory
-> SQL 결과4.1.7 복제(Replication)
4.1.8 쿼리 캐시(Query Cache)
버그 투성이라서 지금은 완전히 제거됨
4.1.9 스레드 풀
돈을 내야하는 엔터프라이즈 에디션에서만 지원하는 기능
일반적인 쓰레드 풀 구조
- 요청이 여러개 들어올 시, 요청을 큐에 저장
- 쓰레드 풀에 있는 임의의 쓰레드가 요청을 가져와서 처리
- 쓰레드 그룹의 갯수는 실행 환경의 코어 갯수 나 그 이상 만큼 설정
- 모든 쓰레드가 동작 중일 때, 새 요청에 대해서 새로운 쓰레드를 만들게 되면 스레드 풀의 의미가 없다.
- thread_pool_stail_limit 을 0 에 가까운 값으로 설정하지 말자.
- 특정 트렌잭션이나 쿼리를 우선적으로 처리하는 선순위 큐와 후순위 큐
4.1.10 트랜잭션 지원 메타 데이터
- 데이터 딕셔너리 = 메타데이터
- 테이블의 구조 정보, 스토어드 프로그램 정보
- 5.6 까지는 FRM 파일에, 8.0 부터는 InnoDB 테이블에 저장
4.2 InnoDB 스토리지 엔진 아키텍처
InnoDB 가 스토리지 엔진 중 유일하게 레코드 기반의 잠금을 제공
4.2.1 프라이머리 키에 의한 클러스터링
- PK 순서대로 디스크에 저장
4.2.2 외래 키 지원
- InnoDB 스토리지 엔진에서 지원
- 다른 엔진에서는 지원하지 않음
- 실무에서 솔직히 외래키 안 쓴다!
4.2.3 MVCC(Multi Version Concurrency Control)
- 잠금을 사용하지 않는 일관된 읽기를 지원하기 위해 필요
- InnoDB 처럼 레코드 레벨의 트랜잭션이 가능해야 지원 가능
4.2.4 잠금 없는 일관된 읽기(Non-Locking Consistent Read)
- MVCC 를 통해 지원가능
4.2.5 자동 데드락 감지
- 잠금 대기 목록을 Wait-for List 로 그려서 관리
- 데드락 발생시, 잠금을 일으킨 트랜잭션을 강제 종료하고 롤백함
4.2.6 자동화된 장애 복구
- 복구모드: innodb_force_recovery 시스템 변수 설정 후 MySQL 서버를 재시작한다.
- 복구모드 중에는 SELECT 외의 쿼리는 불가능하다.
4.2.7 InnoDB 버퍼 풀
- 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간
- INSET, UPDATE, DELETE 를 모아서 한꺼번에 처리
4.2.8 Double Write Buffer
- Partial-page == Torn-page 문제
- 페이지가 일부만 기록되는 현상
- Redo 로그가 페이지 전체가 아니라 변경된 내용만 기록하기 때문에 발생
- Double-Write 를 통해 문제를 해결
- HDD 에서는 좋지만, SSD 에서는 Double-Write 를 비활성화하는게 좋음
4.2.9 언두 로그(Undo Log)
- DML(INSERT, UPDATE, DELETE) 를 롤백할 수 있게 해줌
- Transaction 과 Isolation Level 에 필요
4.2.10 체인지 버퍼
- INSERT 와 UPDATE 변화를 체인지 버퍼 메모리에 올려두고 나중에 디스크에 반영
- 쿼리 속도를 빠르게 해줌
4.2.11 Redo 로그 및 로그 버퍼
- ACID 중 Durable
- 서버가 비정상적으로 종료시, 기록되지 못한 데이터를 잃지 않게 해줌
- 데이터 변경 내용을 Redo Log 로 기록해서, 변경실패시 재 요청
4.2.12 어댑티브 해시 인덱스
- 인덱스 == B-Tree 인덱스
- 어댑티브 해시 인덱스
- 사용자가 만든 인덱스가 아님
- 자주 요청되는 데이터에 대해서 InnoDB 스토리지 엔진이 자동으로 생성한 인덱스
4.2.13 InnoDB 와 MyISAM, MEMORY 스토리지 엔진 비교
결론: InnoDB 가 짱이다!
4.3 MyISAM 스토리지 엔진 아키텍처
InnoDB 가 짱이므로 자세한 설명은 생략한다.
4.3.1 키 캐시
InnoDB 의 버퍼 풀과 비슷한 녀석
4.3.2 운영체제의 캐시 및 버퍼
InnoDB 보다 후져서 운영체제의 캐시 및 버퍼를 직접 사용한다.
4.3.3 데이터 파일과 프라이머리 키(인덱스) 구조
- InnoDB 가 짱좋은 PK 클러스터링을 사용한다면,
- MyISAM 은 후져서 클러스터링 없이, Heap 을 활용한다.
4.4 MySQL 로그 파일
MySQL 서버가 맛탱이가 갔을 때, 다음 로그들을 보면 좋다.
4.4.1 에러 로그 파일
- 실행 도중 발생하는 에러나 경고 메시지의 로그 파일
my.cnf에서log_error에 정의된 경로에.err확장자 파일로 생성됨
4.4.2 제너럴 쿼리 로그 파일(제너럴 로그 파일, General log)
- 실행된 쿼리가 무엇이 있었는지 확인가능한 로그
my.cnf의general_log_file에 정의된 경로에 생성- 로그를 파일이 아닌 테이블에 저장하도록 할 수도 있음
4.4.3 슬로우 쿼리 로그
long_query_time으로 설정한 실행 시간을 초과한 쿼리들을 기록- 쿼리 실행시간을 통계낼 때 편리한 로그