MySQL 서버는 사람의 머리 역할을 담당하는 MySQL 엔진과
손발 역할을 담당하는 스토리지 엔진으로 구분됨
손과 발의 역할을 담당하는 스토리지 엔진은 핸들러 API를 만족하면 누구든지 스토리지 엔진을 구현해서 MySQL 서버에 추가해서 사용할 수 있다.
MySQL 엔진과 MySQL 서버에서 기본으로 제공되는 InnoDB 스토리지 엔진, 그리고 MyISAM 스토리지 엔진을 구분해서 살펴보려한다.
1. MySQL 서버의 전체 구조
1-1. MySQL 엔진
- MySQL 엔진은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는
커넥션 핸들러와SQL 파서 및 전처리기, 쿼리의 최적화된 실행을 위한옵티마이저가 중심을 이룬다. - MySQL은 표준 SQL(ANSI SQL) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있다.
1-2. 스토리지 엔진
MySQL 엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행- 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은
스토리지 엔진이 전담함 - MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.
1-3. 핸들러 API
- MySQL 엔진의
쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청함 - 이러한 요청을
핸들러 요청이라 하고, 여기서 사용되는 API를핸들러 API라고 한다. - InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고 받는다.
2. MySQL 스레딩 구조
MySQL 서버는 프로세스 기반이 아니라 스레드 기반(MySQL 커뮤니티 기준)으로 작동하며, 크게 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.
- MySQL 엔터프라이즈나 Percona MySQL 서버에서는 전통적인 스레드 모델뿐 아니라 스레드 풀 모델을 사용할 수 있다
- 스레드 풀과 전통적인 스레드 모델의 가장 큰 차이점은 포그라운드 스레드와 커넥션의 관계다
- 전통적인 스레드 모델에서는 커넥션별로 포그라운드 스레드가 하나씩 생성되고 할당된다.
- 하지만, 스레드 풀에서는 커넥션과 포그라운드 스레드는 1:1관계가 아니라 하나의 스레드가 여러 개의 커넥션 요청을 전담한다.
2-1. 포그라운드 스레드(클라이언트 스레드)
- 포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장 처리
- 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 되돌아감
- 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면, 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 함
- 이때, 스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정
- 포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져온다
- 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리함
- MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만(MyISAM도 지연된 쓰기가 있지만 일반적인 방식은 아님)
- InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리함
2-2. 백그라운드 스레드
- MyISAM의 경우는 해당 사항이 없지만, InnoDB는 다음과 같이 여러 가지 작업이 백그라운드로 처리됨
- 인서트 버퍼를 병합하는 스레드
- 로그를 디스크로 기록하는 스레드
- InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
- 데이터를 버퍼로 읽어 오는 스레드
- 잠금이나 데드락을 모니터링하는 스레드
- 모두 중요하지만, 그중 가장 중요한 것은 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드이다.
- MySQL 5.5 버전부터 데이터 쓰기 스레드와 데이터 읽기 스레드의 개수를 2개 이상 지정할 수 있게 됐으며, innodb_write_io_threads와 innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정함
- InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에, 읽기 스레드는 많이 설정할 필요가 없지만
- 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크를 사용할 때는 2~4정도
- DAS나 SAN같은 스토리지를 사용할 때는 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하는 것이 좋다
- 사용자 요청을 처리하는 도중 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 읽기 작업은 절대 지연될 수 없다
- 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있으며, InnoDB 또한 이러한 방식으로 처리한다.
- 하지만, MyISAM은 그렇지 않고, 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계돼 있다
- 이러한 이유로 InnoDB에서는
INSERT,UPDATE,DELETE쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때 까지 기다리지 않아도 된다. - 하지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.
출처 : Real MySQL 8.0 (1권)개발자와 DBA를 위한 MySQL 실전 가이드
매일 발전