MySQL 서버는 커넥션 관리를 위해 Connection manager thread를 사용한다.
Connection manager thread는 클라이언트 요청에 스레드를 1대1로 연결한다.
연결에 필요한 스레드가 필요하다면 생성하지만,
먼저Thread Cache를 참조하여 사용 가능한 스레드가 있는지 확인한다.
클라이언트가 커넥션을 종료하면 이를 담당했던 클라이언트 스레드는Thread Cache로 반환된다.
이미 일정 개수의 스레드가 Thread Cache에 존재하는 경우 스레드를 반환하지 않고 제거하게 된다. 이때 스레드 캐시에 보관할 스레드의 최대 개수는thread_cache_size를 통해설정한다.
초당 발생하는 커넥션의 수가 많을수록 해당 값을 크게 설정하면 성능을 향상시킬 수 있다.
show variables like 'thread_cache_size';
✔️ Background Thread
대부분의 작업을 포그라운드 스레드로 진행하는 MyISAM과 달리,
InnoDB는 백그라운드 스레드를 통해 아래와 같은 작업을 처리한다.
체인지 버퍼(Change Buffer) 병합(merge)
(5.5 버전 이전에는 Change Buffer 를 Insert Buffer라 불렀다.)
디스크에 로그 기록
디스크에 버퍼 풀의 데이터 기록
데이터 버퍼로 가져오기
잠금, 데드락 모니터링
InnoDB에서 데이터를 읽는 작업은 클라이언트 스레드가 처리하지만,
쓰기 작업은 주로 백그라운드 스레드를 통해 이루어진다.
따라서 읽기 스레드는 많이 설정할 필요가 없지만,
쓰기 스레드는 충분한 값으로 설정해야 한다.
스레드 개수는 innodb_write_io_threads와 innodb_read_io_threads를 통해 설정할 수 있다.
쓰기 스레드는 일반적인 내장 디스크를 사용하는 경우 2 ~ 4 정도, DNS, SAN 등의 스토리지를 사용하는 경우 디스크를 최적으로 사용할 수 있을 만큼 설정하는 것이 좋다.
클라이언트의 요청 처리 중 데이터의쓰기작업은버퍼링하여 처리할 수 있지만,
데이터의읽기작업은 버퍼링(지연)할 수 없다.
위 특징을 바탕으로 일반적인 DBMS(InnoDB 포함)에서는 쓰기 작업을 버퍼링하여 처리하는 기능을 제공한다.
버퍼링을 사용하면 INSERT, UPDATE, DELETE를 통해 데이터가 변경되는 경우,
디스크에 완전히 저장될 때까지 기다리지 않아도 된다.
반면 MyISAM 같은 경우 사용자 스레드가 쓰기 작업까지 함께 처리하므로 쓰기 작업 시 버퍼링 기능을 사용하지 못한다.
메모리 할당 구조
MySQL의 메모리 공간은 스레드간의 공유 여부에 따라 크게글로벌 영역과로컬 영역으로 구분된다.
✔️ 글로벌 메모리 영역
일반적으로 단 하나의 메모리 공간만 운영체제로부터 할당된다.
필요에 따라 여러개를 할당받는 것 또한 가능하지만 이는 클라이언트 스레드 수와는 무관하며,
생성된 N개의 글로벌 메모리 영역은 모든 스레드의 의해 공유된다.
글로벌 영역에는 다음과 같은 것들이 있다.
테이블 캐시
InnoDB 버퍼 풀
InnoDB 어댑티브 해시 인덱스
InnoDB 리두 로그 버퍼
✔️ 로컬 메모리 영역 (세션 / 클라이언트 메모리 영역)
클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다.
로컬 메모리 영억은 클라이언트가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다. 또한 클라이언트와 MySQL서버와의 커넥션을세션이라고 하므로, 세션 메모리 영역이라고도 표현한다.
로컬 메모리는 클라이언트 스레드별로 할당되며 공유되지 않는다.
로컬 메모리 영역을 제대로 설정하지 않은 경우,
MySQL 서버가 메모리 부족으로 멈출 수도 있으므로 적절히 설정해야 한다.
로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아있는 공간도 있고(커넥션 버퍼나 결과 버퍼),
쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼나 조인 버퍼)도 있다.
대표적으로 다음과 같은 것들이 있다.
정렬 버퍼(Sort Buffer)
조인 버퍼(Join Buffer)
바이너리 로그 캐시
네트워크 버퍼
쿼리 실행 구조
✔️ 쿼리 파서
쿼리 파서는 사용자의 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)로 분리해,
트리 형태의 구조를 만들어 내는 작업을 의미한다.
쿼리의 기본 문법 오류는 이 과정에서 발견된다.
✔️ 전처리기
파서를 통해 만들어진 파서 트리를 기반으로, 쿼리의 구조적인 문제점을 찾는다.
각 토큰을 테이블 이름이나 컬럼 이름, 내장 함수와 매핑하여 존재 여부, 접근 권한 등을 확인한다.
✔️ 옵티마이저
쿼리를 저렴한 비용으로 빠르게 처리하기 위한 최적화를 수행한다.
✔️ 실행 엔진
옵티마이저의 계획대로 각 핸들러(스토리지 엔진)에게 요청하고,
결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
이해를 위해 GROUP BY를 처리하는 예시를 보자.
1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청 2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청 3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 핸들러에게 요청 4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청 5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
✔️ 핸들러 (스토리지 엔진)
실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당한다.
트랜잭션 지원 메타데이터
테이블의 구조 정보, 스토어드 프로그램 등의 정보를data dictionary(데이터딕셔너리)또는메타데이터라고 한다.
5.7 버전까지는 테이블의 구조와 일부 스토어드 프로그램들을 파일 기반으로 관리했다.
파일 기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않는데,
이로 인해 테이블의 생성 또는 변경 도중에 MySQL 서버가 비정상적으로 종료되면 일관성이 깨지는 문제가 발생했다.
이를 해결하기 위해 MySQL 8.0 버전부터는 테이블의 구조, 스토어드 프로그램등을 모두 InnoDB 의 테이블에 저장되도록 개선됐다.
MySQL 서버가 작동하는 데 기본적으로 필요한 테이블들을 묶어서시스템 테이블이라 한다.
대표적으로 사용자 인증과 권한에 관련된 테이블들이 있다.
시스템 테이블 또한8.0 버전부터InnoDB 스토리지 엔진을 사용하도록 개선되었고,
시스템 테이블과데이터 딕셔너리정보를 모두 모아서mysql DB에 저장한다.
mysql DB는 통째로mysql.ibd라는 이름의 InnoDB 테이블스페이스에 저장된다.
이러한 개선 덕분에 스키마 변경 작업 도중 MySQL 서버가 비정상 종료되더라도, 완전한 성공 혹은 완전한 실패로 처리된다.
(즉 원자성을 갖는다.)
그러나 InnoDB가 아닌 MyISAM, CSV 와 같은 스토리지 엔진의 메타 정보는 파일 기반으로 저장된다.
위에서 mysql DB에 데이터 딕셔너리 정보를 저장한 테이블이 저장된다고 하였다. 그러나 mysql DB 내의 테이블을 조회하면 테이블의 구조를 저장한 테이블은 보이지 않는다. 이는 사용자가 임의로 데이터 딕셔너리 테이블의 데이터를 수정하지 못하게 하기 위해 화면에서 감춘 것이며, 실제로는 존재한다. 만약 조회를 원한다면 information_schema DB를 사용하면 된다.
