MySQL 엔진 아키텍처
MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분해서 볼 수 있다.
MySQL 엔진
클라이언트로부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.
스토리지 엔진
실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다. MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러개를 동시에 사용할 수 있다. 아래의 예제와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.
mysql > CREATE TABLE test_table (fd1 IT, fd2 INT) ENGINE = INNODB;핸들러
MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에게 쓰기 또는 읽기를 요청하는데,이러한 요청을 핸들러(Handler) 요청이라고 하고, 여기서 사용되는 API를 핸들러 API라고 한다.
아래의 쿼리문으로 이 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있엇는지 확인가능하다.
SHOW GLOBAl STATUS LIKE 'Handler%';MySQL 스레딩 구조
Mysql 에서 실행 중인 스레드의 목록은 다음과 ㅏㅌ이 pefomance_schema 데이터베이스의 therads 테이블을 통해 화인 할 수 있다.
SELECT thead_id, name, type, processlist_user, pocesslist_host
FROM perfomace_schema.threads ORDER BY type, thread_id;목록 중에 동일한 스레드가 2개 이상씩 보이는 것은 여러 스레드가 동일 작업을 병렬로 처리하는 경우이다.
포그라운드 스레드(Client Thread)
포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무다. 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread pool)로 되돌아간다. 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드 만 스레드 캐시에 존재하게 한다.
스레드 캐시에 유지 할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정한다. InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.
백그라운드 스레드
InnoDB 는 다음과 같이 여러가지 작업이 백그라운드로 실행된다.
1. 인서트 버퍼(Insert Buffer)를 병합하는 스레드
2. 로그를 디스크로 기록하는 스레드
3. InnoDB 버퍼 풀의 데이터를 디스크에 기로하는 스레드
4. 데이터를 버퍼로 일어 오는 스레드
5. 잠금이나 데드라을 모니터링하는 스레드
여기서 가장 중요한 것은 로그 스레드(Log thread)와 버퍼의 데이터를 디스크로 내려쓰는 작업을처리하는 쓰기 쓰레드(Write thread) 이다.
MySql 5.5 버전부터 데이터 쓰기 스레드와 데이터 읽기 스레드의 개수를 innodb_write_io_threads 와 innodb_read_io_therads 시스템 변수로 2개 이상 지정할 수 있다.
사용자의 요청을 처리하는 도중 데이터의 쓰기 자업은 지연(버퍼링) 되어 처리 될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다. 대부분의 DBMS 는 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있다.
메모리 할당 및 사용 구조
글로벌 메모리 영역
하나의 메모리 공간만 할당된다. 모든 스레드에 의해 공유된다. 필요에 따라 2개 이상이 될 수 있지만, 클라이언트의 스레드 수와는 무관하며, 생성된 글로벌 영역은 모든 스레드에게 공유됩니다.
대표적인 글로벌 메모리 영역
1. 테이블 캐시
2. InnoDB 버퍼 풀
3. InnoDB 어댑티브 해시 인덱스
4. InnoDB 리두 로그 버퍼
로컬 메모리 영역
MySql 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다.
대표적으로 커넥션 버퍼와 정렬(소트) 버퍼 등이 있다.
로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다.
또 한 가지 중요한 특징이 있는데 가 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우 에는 Mysql이 메모리 공간을 할당조차도 하지 않을 수 도 있다는 점이다.
대표적인 로컬 메모리 영역
1. 정렬 버퍼(Sort Buffer)
2. 조인 버퍼
3. 바이너리 로그 캐시
4. 네트워크 버퍼
플러그인 스토리지 엔진 모델
Mysl 의 독특한 구조 중 한가지는 바로 플러그인 모델이다. 스토리지엔진, 전문 검색 엔진을 위한 검색어 파서, 인증 등이 플러그인 형태로 구현되어 제공된다.
컴포넌트
MySql 8.0 부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다.
- 플러그인은 오직 MySql 서버와 인터페이스를 할 수있고 플러그인끼리는 통신 할 수 없다.
- 플러그인은 MySql 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음
- 플러그인은 상호 의존 관계를 설정할 수 없어서 최화가 어려움
쿼리 실행 구조
파서
사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의구조로 만들어 내는 작업 한다. 쿼리문장의 기본 문법 오류는 이 과정에서 발견되며 사용자에게 오류 메시지를 전달하게 된다.
전처리기
파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 이름이나 칼럼 이름 또는 내장 함수와 같은 개체를 매핑에 해당 객체의 존재 여부와 객체의 접근권한 등을 확인하는 과정을 이 단계에서 수행한다. 존재 하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.
옵티마이저
사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 한다.
실행 엔진
실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 가정했을때,
ex) 실행엔진이 핸들러에게 테이블을 만들어, WHERE 절에 일치하는 레코드 읽어와, 읽어온 레코드들 임시 테이블로 저장해등을 요청하고 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈에게 넘긴다.
핸들러
MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당한다. 핸들러는 결국 스토리지엔진을 의미한다.
복제
MySQL의 복제는 레플리케이션이라고도 하는데, 복제는 2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간으로 동기화 하는 기술이다.
복제에는 INSERT나 UPDATE와 같은 쿼리를 이용해 데이터를 변경할 수 있는 MySQL서버와 SELECT 쿼리로 데이터를 읽기만 할 수있는 MySQL서버로 나뉜다.
MySQL에서는 쓰기와 읽기 역할로 구분해, 전자를 마스터라고 하고 후자를 슬레이브라고 한다. 서버의 복제에서는 머스터는 반드시 1개이며 슬레이브는 1개 이상으로 구성될 수 있다.
마스터
MySQL의 바이너리 로그가 활성화되면 어떤 MySQL 서버든 마스터가 될 수 있다. 애플리케이션의 입장에서 본다면 마스터 장비는
주로 데이터가 생성 및 변경, 삭제되는 주체(시작점)이라고 볼 수 있다. 일반적으로 복제에 참여하는 여러 서버 가운데 변경이 허용되는 서버는 마스터로 한정할 때가 많다.
그렇지 않은 경우 복제되는 데이터의 일관성을 보장하기 어렵기 때문이다. 슬레이브 서버에서 변경 내역을 요청하면 마스터 장비 는 그 바이너리 로그를 읽어 슬레이브로 넘긴다. 마스터 장비의 프로세스 가운데 "Bimlog dump"라는 스레드가 이 일을 전담하는 스레드다.
슬레이브
데이터(바이너리 로그)를 받아 올 마스터 장비의 정보(IP주소와 포트 정보 및 접속 계정)을 가지고 있는 경우 슬레이브가 된다. (마스터나 슬레이브라고 해서 별도의 빌드 옵션이 필요하거나 프로그램을 별도로 설치해야 하는 것은 아니다.)마스터 서버가 바이너리 로그를 가지고 있다면 슬레이브 서버는 릴레이 로그를 가지고 있다.
슬레이브 서버의 I/O 스레드는 마스터 서버에 접속해 변경 내역을 요청하고 받아 온 변경 내역을 릴레이 로그에 기록한다.그리고 슬레이브 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행함으로써 슬레이브의 데이터를 마스터와 동일한 상태로 유지한다. I/O 스레드와 SQL 스레드는 마스터 MySQL에서는 가동되 않으며, 복제가 설정된 슬레이브 MySQL 서버에서 자동적으로 가동하는 스레드다.
쿼리 캐시
쿼리 캐시는 MySql 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보인다. 하지만, 변경된 테이블과 관련된 것들을 삭제(Invalidate) 해야 하는 이슈가 있어, 동시 처리 성능 저하를 유발했다.
결국 MySql 8.0 버전으로 올라오면서 쿼리 캐시 기능은 제거 되었다.
스레드풀
커뮤니티 에디션에서는 지원하지 않지만 엔터프라이즈 에디션에서는 스레드 풀(Thread pool) 기능을 제공한다. Percona Server 스레드 풀을 기반으로 설명한다.
스레드 풀의 목적은 사용자의 요청마다 생성되는 스레드 개수를 줄여 동시 요청수가 많아도 제한된 개수의 스레드 처리만 집중할 수 있도록 하는 것이다. 매우 성능이 향상될 것 같지만 실제로 눈에 띄는 성능 향상을 보여주는 경우는 많이 없다. 제한된 수의 스레드를 CPU가 잘 처리할 수 있도록 하는 기능인데 스케줄링 과정에서 CPU 자원이 잘 확보되지 못하면 쿼리 처리가 더 느려진다. 이것이 잘 처리되도록 유도한다면 CPU 프로세서 친화도도 높이고 운영체제 입장에서 컨텍스트 스위치도 줄여서 오버헤드를 줄일 수 있다.
Percona Server와 같은 경우 기본적으로 CPU 코어 개수만큼 스레드를 생성하여 사용한다. (그래야 CPU 프로세서 친화도를 높일 수 있다) 요청이 들어왔을 때 스레드 풀이 처리중인 작업이 있다면 thread_pool_oversubscribe 변수 만큼 추가로 스레드를 받아드려서 처리한다. 만약에 이 값이 너무 크면 스레드가 많아지고 스케줄링을 해야해서 스레드 풀이 비효율적으로 작동할 수 있다.
만약에 모든 스레드가 일을 처리하고 있다면 새로운 worker thread를 추가할지 기다릴지 결정할 수 있다. 이때는 thread_pool_stall_limit 만큼 기다렸다가 그래도 작업이 끝나지 않으면 새로운 스레드를 추가하여 작업을 처리한다. 하지만 여전히 thread_pool_max_threads 를 넘을수는 없다.
Perconam Server는 선순위 큐와 후순위 큐를 사용하여 먼저 처리할 수 있는 트랜잭션이나 쿼리를 우선적으로 처리하여 잠금 경합을 낮추고 전체적인 성능을 향상시키도록 하는 기능을 제공한다.
트랜잭션 지원 메타데이터
선 데이터베이스 서버에서 테이블 구조 정보나 스토어드 프로그램 등의 정보를 데이터 딕셔너리 혹은 메타데이터라고 한다. 이전에는 이것을 파일 기반의 메타데이터로 저장했다. 이 데이터 생성 및 변경 작업을 트랜잭션을 지원하지 않기 때문에 중간에 에러가 나거나 종료가 되면일관되지 않은 상태로 남아있어 문제가 생기게 된다.
따라서 MySQL8.0 부터는 테이블의 구조 정보나 스토어드 프로그램의 코드는 모두 InnoDB의 테이블에 저장하도록 했다. MySQL 서버가 실행하는데 기본적으로 필요한 테이블들을 시스템 테이블이라고 하며 사용자 인증 및 권한 등과 관련된 테이블이 있다. 이런 시스템 테이블과 데이터 딕셔너리 정보를 mysql DB에 저장하고 그것을 mysql.ibd 라는 테이블스페이스에 저장하므로 이것은 각별히 주의하여 관리해야 한다.
이제 트랜잭션 기반의 InnoDB에 저장이 되므로 중간에 실패를 하면 트랜잭션의 원자성 특성에 따라서 완전히 성공하거나 실패한 것은 완전히 정리된다.
InnoDB 스토리지 엔진 아키텍처
프라이머리 키에 의한 클러스터링
외래키 지원
MVCC(Multi Version Concurrency Control)
잠금 없는 일괄된 읽기(Non-Locking Consistent Read)
자동 데드락 감지
자동화된 장애복구
InnoDB 버퍼 풀
버퍼 풀의 크기 설정
버퍼 풀의 구조
버퍼 풀과 리두 로그
버퍼 풀 플러시(Buffer Pool Flush)
플러시 리스트 플러시
LRU 리스트 플러시
버퍼 풀 상태 백업 및 복구
버퍼 풀의 적재 내용 확인
Double Write Buffer
언두 로그
언두 테이블스페이스 관리
체인지 버퍼
리두 로그 및 로그 버퍼
리두 로그 아카이빙
리두 로그 활성화 및 비활성화
어댑티브 해시 인덱스
IPnnoDB 와 MyLSAM, MEMORY 스토리지 엔진 비교
MylSAM 스토리지 엔진 아키텍처
키 캐시
운영체제의 캐시 및 버퍼
데이터 파일과 프라이머리 키(인덱스) 구조
MySQL 로그 파일
에러 로그 파일
MySQL 이 시작하는 과정과 관련된정보성 및 에러 메시지
마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지
쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
비정상적으로 종료된 커넥션 메시지(Aborted connection)
InnoDB의 모니터링 또는 상태 조회 명령(SHOW ENGINE INNODB STATUS 같은) 결과 메시지
MySQL의 종료 메시지
제너럴 쿼리 로그 파일(제너럴 로그파일, General log)
슬로우 쿼리 로그
슬로우 쿼리 통계
실행 빈도 및 누적 실행 시간순 랭킹
쿼리별 실행 횟수 및 누적 실행 시간 상세 정보
