이 글은 Real MySQL 스터디에서 연철님이 발표한 발표자료입니다.
### **1) MySQL 전체 구조**
-
참고 사진
출처: MySQL 공식 문서
-
MySQL 엔진 (커넥션 핸들러 + SQL parser/optimizer)
- 클라이언트 접속 및 쿼리 요청 처리 (커넥션 핸들러)
-
스토리지 엔진 (SQL 문장 분석 및 최적화)
- 실제 저장 공간에 데이터 읽기/쓰기 담당하는 기본 소프트웨어 컴포넌트
- 각 엔진마다 성능 최적화를 위해서 별도의 전략 사용
- InnoDB, MyISAM 엔진 존재
- 각 테이블마다 필요한 엔진 지정 가능
CREATE TABLE table_name (columns) ENGINE=INNODB;
-
핸들러 API
- 핸들러 요청(스토리지 엔진에 읽기/쓰기) 시 사용되는 API
- 스토리지 엔진과 mysql 엔진 사이에서 동작
- MySQL 엔진 —> 스토리지 엔진으로 요청 시 사용
- 실제 CRUD는 스토리지 엔진에서 동작하기 때문에 핸들러 api를 통해서 요청이 되어야 함
2) MySQL 쓰레딩 구조
-
MySQL 서버는 스레드 기반 동작 (프로세스 기반 X)
-
Foreground (동기식, 요청 후 응답 대기)
- 클라이언트가 요청하는 쿼리 문장 처리
- 클라이언트 작업 종료 시 DB 커넥션을 담당하던 스레드는 thread cache로 복귀
- 만약 thread_cache_size 보다 더 많게 된다면 스레드를 종료시킴
- 캐시나 버퍼에 데이터가 없는 경우에는 백그라운드 동작
-
Background (비동기식, 요청 후 다른 요청 가능)
-
실제 데이터를 디스크에 기록 담당
-
읽기, 쓰기 쓰레드가 나뉘어져 있음
-
innodb_write_io_threads, innodb_read_io_threads로 개수 설정
-
쓰기 작업 시 log buffer에 담은 후 배치 처리
-
참고 사진
-
-
백그라운드 방식 덕분에 CUD 작업 시 완료 기다리지 않아도 됨
-
만약 이것을 다 기다려야 한다면 blocking이 매우 많이 발생할 것
-
-
3) 메모리 할당 및 사용 구조
- 글로벌 메모리 영역
- 서버 시작 시 운영체제로부터 할당
- MySQL 서버 내 여러 쓰레드가 공유하는 영역
- 아래의 내용들이 글로벌 메모리 할당
- 테이블 캐시 (table cache)
- 각 테이블의 metadata
- 테이블 읽기/쓰기 시 테이블 여는 역할
- 컬럼의 타입에 따라서도 용량 차이 큼 varchar(10) 보다 varchar(100)은 10배 더 소비
- 아래의 InnoDB buffer pool에 저장
- InnoDB buffer pool
- Table cache 및 index data를 caching 하는 역할
- 자주 접근되는 데이터를 빠르게 획득 가능
- LRU(Least Recently Used) 알고리즘 이용. 공간 부족 시 오래전에 사용된 데이터 제거
- InnoDB redo log buffer
- InnoDB buffer pool은 메모리에 저장되기 때문에 휘발성. 장애 발생 시 transaction 유실
- 이러한 유실 방지를 위해 redo log buffer 저장 후 file에 배치 처리
- redo log file을 남기기 위해서 저장되는 공간. 클라이언트를 위한것 X
- MySQL 장애 발생시 redo log file을 바탕으로 장애 transaction 전으로 회복
- 참고 사진
- 테이블 캐시 (table cache)
- 로컬 메모리 영역 (=세션 메모리)
- 스레드별로 독립적 할당. 공유 불가
- Sort / Join / Binary log / Network buffer
- Sort/Join buffer의 경우 데이터 정렬/Join 위해 별도의 메모리 공간을 할당
- 커넥션이 종료되지 않더라도 쿼리 실행 후 공간 해제
- 클라이언트가 요청한 쿼리 실행이 끝났다고 해서, 반드시 커넥션이 종료되는것은 아님
4) 플러그인 스토리지 엔진 모델
- 스토리지 엔진, 사용자 인증 등 모두 플러그인으로 제공
- 즉 필요한 플러그인으로 교체하여 사용 가능
5) 컴포넌트
- 플러그인의 단점
- 오직 MySQL 서버와 통신 가능. 플러그인끼리 불가
- MySQL 서버의 변수, 함수를 직접 호출하여 불안전 (캡슐화 X)
- 플러그인끼리 상호 의존 관계 설정 불가능
- 위의 단점을 컴포넌트가 보완
6) 쿼리 실행 구조 (컴파일러 구조와 흡사)
-
참고 사진
출처: https://12bme.tistory.com/73#:~:text=쿼리 실행구조는 기능별,같이 나눠질 수 있습니다.&text=파서는 사용자 요청으로,내는 작업을 의미합니다.
-
쿼리 파서 —> 전처리기 —> 옵티마이저 —> 쿼리 실행기
-
쿼리 파서 (parser)
- 요청 쿼리 문장을 토큰(token)으로 분리해 트리 형태 구조 (parse tree)
-
전처리기 (processor)
- parse tree를 기준으로 쿼리 문장 구조적 문제 확인
- 함수, 컬럼 이름, 권한 등 확인
-
옵티마이저 (optimizer)
- 사용자 요청 쿼리 문장의 빠른 실행 계획 세움
-
쿼리 실행기 (query execution engine)
- MySQL 스토리지 엔진 요청 (API)
8) 스레드 풀
- 사용자 요청당 할당되는 스레드
- 스레드 풀의 숫자가 부족할 경우 thread_pool_stall_limit을 활용하여 개수 설정
- 하지만 요청마다 스레드가 생생되버리면 스레드가 너무 많이 생김.
- 메모리 및 과한 CPU context switching 문제 발생 위험.
- 그래서 적당량의 스레드 풀을 설정 후 획득하지 못 하는것은 대기 상태로 두는것이 효율적
9) MyISAM 스토리지 엔진 아키텍처
- 키 캐시 (key cache)
- InnoDB의 buffer pool과 비슷한 역할
- buffer pool과 다른점은 인덱스만을 대상으로 동작
- 캐시 히트 99% 이상으로 유지하는것을 권장 (key 요청 대비 캐시 읽기)
- 미만이라면 cache 공간을 더 크게 할당하여 사용
10) 운영체제의 캐시 및 버퍼
- Key cache를 이용해서 인덱스 조회 성능은 우수하지만, 일반 테이블 데이터에 대한 캐시 혹은 버퍼링 기능 없음
- 그래서 인덱스 외의 데이터는 운영체제의 캐시 기능 사용
- 문제는 시스템 전체 메모리를 사용하기 때문에 다른 애플리케이션에서 메모리 모두 사용한다면 캐시용 메모리 공간 부족
11) MySQL 로그 파일
- my.cnf 파일의 log_error 변수에 정의된 경로에 로그 파일 생성
- 에러 로그 종류
- 시작하는 과정의 정보성 및 에러 메세지
- mysqld: ready for connections 메세지가 발생하면 정상 기동
- 비정상적 종료로 인한 InnoDB 트랜잭션 복구 메세지
- 비정상 종료 시 재시작 후 미완료 트랜잭션을 재처리
- 재시작이 불가능한 경우 오류 메세지만 출력하고 다시 종료됨
- 비정상 종료된 커넥션 메세지 (aborted connection)
- connection 획득한 클라이언트에서 정상적인 종료 및 해제 못한 경우
- 사용 클라이언트의 connection 종료 로직 검토 필요
- 슬로우 쿼리 로그 (slow query)
- long_query_time 시스템 변수에 설정한 시간 이상 소요된 쿼리 모두 기록
- 슬로우 쿼리라도 정상 수행된것만 저장. 실행 중에 임의로 kill한것은 저장되지 않음.
- log_output 옵션을 이용해서 파일 혹은 테이블로 기록 가능
- 슬로우 쿼리 로그 파일
- query_time: 쿼리 실행 전체 시간
- rows_examined: 쿼리 처리 위해 접근한 레코드 수
- rows_sent: 실제 처리된 레코드 수
- 접근한 레코드 수는 줄이기 위해서 더 작고 명확한 where절 등을 사용한다면 더 좋지 않을까 생각
- Percona Toolkit을 이용하면 슬로우 쿼리 통계, 실행 횟수 등 조회 가능
- 보통은 모니터링 툴을 사용하거나 Cloud SQL을 사용한다면 불필요하지 않을까?
- 하지만 로컬 시스템에서 모니터링 툴로 내용을 응답하다가 서버 부하를 주는 경우도 존재
- e.g. 레거시 시스템에서 동작하는지도 몰랐던 프로메테우스가 요청을 계속 보낸 경우 존재. 메모리를 다 잡아먹어서 VM 자체의 메모리가 부족했던 경우도 존재했음.
- 시작하는 과정의 정보성 및 에러 메세지
백엔드 프로그래머