Real MysQL 8.0 1권을 읽고 정리한 내용입니다.
잘못된 내용이 있을 경우 댓글로 남겨주시면 감사하겠습니다🙏
MySQL 엔진 아키텍처
MySQL 의 내부에서 사용되는 엔진은 사람의 머리에 해당하는 MySQL 엔진 과 손발에 해당하는 스토리지 엔진 이 있습니다.
MySQL 엔진
- 커넥션 핸들러 : 클라이언트로부터의 접속 요청 처리
- SQL 파서 & 전처리기 : 쿼리 요청 처리
- 옵티마이저 : 쿼리 최적화
스토리지 엔진
| MyISAM | InnoDB | |
|---|---|---|
| 특징 | 지원하는 기능이 많지 않아, 구조가 단순함 | 지원하는 기능이 많아 구조가 복잡하고, 자원을 많이 사용함 |
| 기능 지원 | Full-text 인덱싱 가능 | 인덱싱(Full-text 인덱싱 포함), 외래키, 제약조건, 동시성제어, 트랜잭션 지원 |
| Lock 단위 | Table level-lock | Row level-lock |
| 복구 능력 | 나쁨 | 좋음 |
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;위 처럼 InnoDB 엔진을 사용하도록 명시할 수 있는데, 이 테이블에 대해 INSERT, UPDATE, DELETE, SELECT 등의 작업이 발생하면 InnoDB 가 이를 처리한다.
핸들러 API
MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야할 때, 각 스토리지 엔진 에 요청합니다.
이러한 요청을 핸들러 요청 이라고 하고, 여기서 사용되는 API 를 핸들러 API 라고 합니다.
아래는 핸들러 API 를 통해 얼마나 많은 작업들이 있었는지 확인할 수 있다.
SHOW GLOBAL STATUS LIKE `Handler%`;
MySQL 스레딩 구조
MySQL 서버는 스레드 기반으로 동작하며, 크게 포그라운드(Foreground) 스레드 와 백그라운드(Background) 스레드 로 구분할 수 있습니다.
실행중인 스레드의 목록은 아래 명령어를 통해 확인할 수 있습니다.
간혹 동일한 이름의 스레드가 2개 이상 보일 수도 있는데, 이는 MySQL 서버 설정에 따라 동일 작업을 병렬로 처리하는 경우입니다.
SELECT thread_id, name, type, processlist_user, processlist_host FROM performance_schema ORDER BY type, thread_id;이 명령을 통해 확인할 수 있는 스레드들 중에서 thread/sql/one_connection 스레드만 사용자의 요청을 처리하는 포그라운드 스레드 입니다.
포그라운드 스레드
포그라운드 스레드 는 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 각 클라이언트가 요청하는 쿼리를 처리합니다.
클라이인트가 작업을 마치고 커넥션을 종료하면 스레드 캐시(Thread cache)로 되돌아 갑니다.
이때 스레드 캐시 에 대기중인 스레드가 일정 개수 이상이라면, 포그라운드 스레드는 스레드 캐시 에 넣지 않고 종료됩니다.
포그라운드 스레드 는 데이터를 데이터 버퍼나 캐시에서 가져오는데, 여기에 없는 경우 디스크 데이터나 인덱스 파일을 읽어 작업을 처리합니다.
MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드 가 처리하지만, InnoDB 테이블은 데이터 버퍼와 캐시까지만 처리하고, 그 이외에는 백그라운드 스레드 가 처리합니다.
백그라운드 스레드
MyISAM 의 경우는 해당사항이 없고, InnoDB 는 다음과 같이 여러가지 작업이 백그라운드로 처리됩니다.
- Insert Buffer 를 병합하는 스레드
- 로그를 디스크로 기록하는 스레드
- InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
- 데이터를 버퍼로 부터 읽어오는 스레드
- 잠금이나 데드락을 모니터링하는 스레드
이 중에서 더욱 중요한 작업은 2번, 3번 입니다.
MySQL 5.5 버전부터 쓰기 스레드와 읽기 스레드의 개수를 2개 이상 지정할 수 있게 되었습니다.
innodb_write_io_threads, innodb_read_id_threads 시스템 변수로 스레드의 개수를 설정함
InnoDB 에서도 읽기 작업은 포그라운드 스레드 에서 처리되기 때문에 많이 설정할 필요가 없습니다.
하지만 쓰기 작업은 아주 많은 작업을 백그라운드 로 처리하기 때문에, 일반적인 디스크를 사용할 때는 2~4 정도, DAS, SAN 같은 스토리지의 경우는 최적값을 찾아 설정하는것이 좋습니다.
메모리 할당 및 사용 구조
MySQL 에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역 과 로컬 메모리 영역 으로 구분할 수 있습니다.글로벌 메모리 영역 은 MySQL 서버가 시작되면서 운영체제로 부터 할당됩니다.
운영체제의 종류에 따라 메모리 관리 방법이 다를 수 있어, 사용되는 정확한 메모리 양을 측정하는것은 쉽지 않습니다.
글로벌 메모리 영역
일반적으로, 클라이언트 스레드(포그라운드 스레드) 의 수와 무관하게 하나만 할당됩니다.
필요에 따라 2개 이상이 할당될 수도 있지만, 생성된 글로벌 영역은 모든 스레드에게 공유됩니다.
대표적인 글로벌 메모리 영역은 아래와 같습니다.
- 테이블 캐시
- InnoDB 버퍼 풀
- InnoDB 어댑티브 해시 인덱스
- InnoDB 리두 로그 버퍼
로컬 메모리 영역
세션 메모리 영역 이라고도 부르며, 클라이언트 스레드 가 쿼리를 처리하는데 사용하는 영역입니다.
대표적으로 위 그림의 커넥션 버퍼와 정렬 버퍼등이 있습니다.
로컬 메모리 영역 은 각 클라이언트 스레드 별로 독립적으로 할당됩니다.
중요한 특징은 각 쿼리의 용도로 필요할 때만 공간이 할당되고, 필요하지 않을때는 아예 할당되지 않을 수도 있습니다.
위에서 말한 커넥션 버퍼와 정렬 버퍼등이 이에 해당합니다.
그리고 세션이 살아있는 동안 계속 할당된 채로 유지되는 공간도 있고, 쿼리를 실행하는 순간에만 할당되는 공간도 있습니다.
대표적인 로컬 메모리 영역 은 아래와 같습니다.
- 정렬 버퍼
- 조인 버퍼
- 바이너리 로그 캐시
- 네트워크 버퍼
플러그인 스토리지 엔진 모델
MySQL 의 독특한 구조 중 하나인 플러그인 모델 입니다.
스토리지 엔진, 검색어 파서, 인증 등등의 기능이 모두 플러그인 형태로 존재합니다.
이 중에서 스토리지 엔진을 살펴보면, 이미 MySQL 에서는 기본적으로 여러 스토리지 엔진을 제공합니다.
그러나 필요에 따라 직접 개발한 엔진을 사용할 수도 있습니다.
여기서 플러그인 모델 의 장점이 드러납니다.
쿼리가 실행되는 과정에서 거의 대부분의 작업은 MySQL 엔진이 수행하고, 데이터 읽기/쓰기 작업만 스토리지 엔진이 수행합니다.
여기서 새로운 스토리지 엔진을 사용하더라도, 전체 기능에는 영향을 주지 않고 데이터 읽기/쓰기 작업에만 영향을 주게 됩니다.
객체지향에서 Interface 를 만들고 그 구현체를 바꿔끼우는것과 유사합니다.
컴포넌트
MySQL 8.0 부터는 기존의 플러그인 모델(플러그인 아키텍처)를 대체하기 위해서, 컴포넌트 아키텍처가 지원됩니다.컴포넌트 아키텍처는 다음과 같은 플러그인 모델 의 단점을 개선하여 구현되었습니다.
- MySQL 서버와만 상호작용하고, 플러그인간 상호작용은 안됨
- MySQL 서버의 변수나 함수를 직접 호출(캡슐화 안됨)
- 플러그인간 상호 의존 관계 설정이 안되어, 초기화가 어려움
쿼리 실행 구조
쿼리를 실행하는 과점에서 표현한 MySQL의 구조 입니다.
쿼리 파서
쿼리 문장을 MySQL 이 인식할 수 있는 최소 단위인 토큰 으로 만들고, 이를 바탕으로 파스 트리(신텍스 트리, Syntax Tree)를 생성합니다.
이 과정에서 쿼리 문장의 기본 문법 오류르 검출해 내고, 사용자에게 오류르 전달합니다.
전처리기
파스 트리를 기반으로 쿼리 문장의 구조적인 문제점을 확인합니다.
테이블 이름, 칼럼 이름, 내장함수 이름 및 매개변수, 접근 권한 등을 확인하여 존재하지 않거나 권한 밖인지를 확인합니다.
옵티마이저
어떻게 하면 쿼리 문장을 리소스를 적게 사용하면서 가장 빠르게 처리할지를 결정합니다.
보통 옵티마이저가 알아서 방법을 선택하지만, 사용자가 더 나은 선택을 하도록 유도할수도 있습니다.
(MySQL Hint)
실행 엔진
옵티마이저가 사람의 두뇌에 해당한다면, 실행 엔진과 핸들러는 손발에 해당합니다.
아래는 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다는 가정 하의 시나리오 입니다.
복제(Replication)
2대 이상의 DBMS 를 나눠서 데이터를 저장하는 방식입니다.
복제를 사용하기 위해서는 최소한 Master, Slave 를 구성해야합니다.
- Master : 데이터 변경 요청시 바이너리 로그(Binarylog)를 생성하여 Slave 서버로 전달합니다.
- 데이터 변경 요청(Insert, Update, Delete)을 주로 처리합니다.
- Slave : 전달받은 바이너리 로그를 데이터로 반영합니다.
- 데이터 읽기 요청을 주로 처리합니다,
복제를 사용하는 목적은 다음과 같습니다.
- 요청 부하 분산 : 변경 요청, 읽기 요청 분리
- 백업 데이터 : 서버에 장애가 발생했을 경우, 백업 데이터로 활용
참고 : MySQL Replication - 단방향 이중화
쿼리 캐시
빠른 응답속도를 위해 쿼리 실행결과를 캐싱하는 기능입니다.
이전에 실행되었던 쿼리가 실행되면, 테이블을 읽지 않고 캐시를 읽어서 응답하여 매우 빠른 응답속도를 제공합니다.
그러나 캐시된 데이터의 실데이터에 변경이 발생할 경우, 관련 데이터를 모두 삭제(Invalidate)처리 해야합니다.
이 작업이 심각한 동시 처리 성능 저하를 일으키며, 또한 많은 버그의 원인이 되었다고 합니다.
그래서 MySQL 8.0부터는 쿼리 캐시 기능은 MySQL 서버에서 완전히 제거되었다고 합니다.
스레드 풀
스레드 풀이란 일정량을 스레드를 미리 생성해놓고, 스레드가 필요할때 미리 생성된 스레드를 제공하는 방식입니다.
이러한 방식을 사용하는 이유는 요청마다 스레드를 생성하는 경우 오버헤드가 많이 발생하기 때문에 이를 줄이기 위함입니다.
- 발생하는 오버헤드
- 스레드 생성, 제거 비용
- 컨텍스트 스위칭
- 스레드 스케줄링 비용
MySQL Server Enterprise 에서는 스레드 풀을 제공하지만, Community 에서는 제공하지 않습니다.
그래서 Percona Server 에서 제공하는 스레드 풀 기능을 기준으로 기술하였습니다.
Percona Server 란
Percona 에서 만든 오픈 소스 MySQL 배포판입니다.
여기는 MySQL Enterprise 에서만 사용할 수 있는 다양한 확장성, 가용성, 보안 및 백업 기능이 포함되어있습니다.
MySQL Enterprise 에는 스레드 풀이 내장되어있지만, Percona Server는 플러그인 형태로 작동하게 구현되어있습니다.
Percona Server 의 스레드 풀은 기본적으로 CPU 코어 개수만큼 스레드를 생성하여 사용합니다.
(thread_pool_size 시스템 변수를 변경하여 조정할 수 있음)
만약 모든 스레드가 작업중이라면, 스레드 풀은 새로운 스레드를 생성할지 기존 스레드의 종료를 기다릴지 판단해야합니다.
이때 스레드 풀의 타이머는 주기적으로 스레드들의 상태를 체크해서 일정 시간 이상 종료되는 스레드가 없으면 새로운 스레드를 생성합니다.
(이 시간은 thread_pool_stall_limit 시스템 변수로 설정할 수 있음)
하지만 최대로 설정된 수(thread_pool_max_threads) 이상으로 스레드를 생성할수는 없습니다.
Percona Server 의 스레드 풀은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능을 제공합니다.
먼저 시작된 트랜잭션 내의 쿼리를 우선적으로 처리하여, 잠금이 빨리 해제되고 경합을 낮춰서 전체 성능 향상을 도모합니다.
다음 그림은 선순위 큐와 후순위 큐를 이용해 재배치한 작업의 순서입니다.
트랜잭션 지원 메타데이터
DB 서버에서 테이블의 구조 정보, 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터 라고 합니다.
MySQL 5.7 까지는 테이블 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램도 파일 기반으로 관리했습니다.
그러나 파일 기반 관리 방법은 트랜잭션이 지원되지 않기 때문에 테이블을 생성, 변경 도중 장애가 발생하면 테이블이 깨지는 현상이 발생했다고 합니다.
그래서 MySQL 8.0 부터는 이런 문제를 해결하기 위해 메타데이터들을 InnoDB 테이블에 저장하도록 개선되었습니다.
MySQL 서버가 동작하기 위해 필요한 테이블들을 시스템 테이블이라고 하는데요.
대표적으로는 인증과 권한에 관련된 테이블이 있습니다.