MySQL 서버는 사람의 머리 역할을 담당하는 MySQL 엔진과 손발 역할을 담당하는 스토리지 엔진으로 구분할 수 있다.
그리고 손과 발의 역할을 담당하는 스토리지 엔진은 핸들러 API를 만족하면 누구든지 스토리지 엔진을 구현해서 MySQL 서버에 추가해서 사용할 수 있다.
4.1 MySQL 엔진 아키텍처
4.1.1.1 MySQL 엔진
MySQL 엔진은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다. 또한 MySQL은 표준 SQL문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있다.
4.1.1.2 스토리지 엔진
실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다.
MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.
다음 예제와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후에 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스트리지 엔진이 처리한다.
mysql > CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM 스토리지 엔진)이나 InnoDB 버퍼 풀(Inno DB 스토리지 엔진)과 같은 기능을 내장하고 있다.
4.1.1.3 핸들러 API
MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이러한 요청을 핸들러 요청이라고 하고, 여기서 사용되는 API를 핸들러 API라고 한다.
핸들러 API를 통해 얼마나 많은 데이터 작업이 있었는지 아래와 같이 확인 가능하다
SHOW GLOBAL STATUS LIKE 'Handler%';4.1.2 MySQL 스레딩 구조
MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분할 수 있다. MySQL 서버에서 실행 중인 스레드의 목록은 다음과 같이 perfeormance_schema 데이터베이스의 threads 테이블을 통해 확인할 수 있다.
mysql > SELECT thread_id, type, processlist_user, processlist_host
FROM performance_schema.threads ORDER BY type, thread_id;4.1.2.1 포 그라운드 스레드(클라이언트 스레드)
포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다. 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 되돌아간다. 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 종료시켜 일정 개수의 스레드만 캐시에 존재하도록 한다. 최대 스레드 개수는 thread_cache_size 시스템 변수로 정한다.
4.1.2.2 백 그라운드 스레드
MyISAM의 경우에는 별로 해당 사항이 없는 부분이지만 InnoDB는 다음과 같이 여러 가지 작업이 백그라운드로 처리된다.
- 인서트 버퍼를 병합하는 스레드
- 로그를 디스크로 기록하는 스레드
- InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
- 데이터를 버퍼로 읽어 오는 스레드
- 잠금이나 데드락을 모니터링하는 스레드
모두 중요한 역할을 하지만 그중에서 가장 중요한 것은 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드일 것이다.
4.1.3 메모리 할당 및 사용 구조
MySQL에서 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.
4.1.3.1 글로벌 메모리 영역
일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다. 단, 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있지만 클라이언트의 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개라고 하더라도 모든 스레드에 의해 공유된다.
- 테이블 캐시
- InnoDB 버퍼풀
- InnoDB 어댑티브 해시 인덱스
- InnoDB 리두 로그 버퍼
4.1.3.2 로컬 메모리 영역
세션 메모리 영역이라고도 하며, MySQL서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다.
대표적으로 커넥션 버퍼와 정렬 버퍼 등이 있다.
로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다. 일반적으로 글로벌 메모리 영역의 크기는 주의해서 설정하지만 소트 버퍼와 같은 로컬 메모리 영역은 크게 신경 쓰지 않고 설정하는데, 최악의 경우에는 MySQL 서버가 메모리 부족으로 멈춰 버릴 수도 있으므로 적절한 메모리 공간을 설정하는 것이 중요하다. 로컬 메모리 공간의 또 한 가지 중요한 특징은 각 쿼리의 용도별로 필요한 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수 있다는 점이다. 대표적으로 소트 버퍼나 조인 버퍼와 같은 공간이 그러하다. 그리고 로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공가도 있고 그렇지 않고 쿼리를 순간에만 할당하다가 다시 해제하는 공간도 있다.
- 정렬 버퍼
- 조인 버퍼
- 바이너리 로그 캐시
- 네트워크 버퍼
4.1.4 플러그인 스토리지 엔진 모델
MySQL 서버에서는 스토리지 엔진뿐만 아니라 다양한 기능을 플러그인 형태로 지원한다. 인증이나 전문 검색 파서 또는 쿼리 재작성과 같은 플러그인이 있으며, 비밀번호 검증과 커넥션 제어 등에 관련된 다양한 플러그인이 제공된다.
4.1.5 컴포넌트
MySQL 8.0부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다.
MySQL 서버 플러그인은 다음과 같은 몇 가지 단점이 있는데, 컴포넌트는 이러한 단점을 보완해서 구현됐다.
- 플러그인은 오직 MySQL 서버와 인터페이스 할 수 있고, 플러그인끼리는 통신할 수 없음
- 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음
- 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움
4.1.6 쿼리 실행 구조
4.1.6.1 쿼리 파서
쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다.
쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달하게 된다.
4.1.6.2 전처리기
파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 이름이나 칼럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 이 단계에서 수행한다.
4.1.6.3 옵티마이저
옵티마이저란 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당하며, DBMS의 두뇌에 해당한다고 볼 수 있다.
4.1.6.4 실행 엔진
옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다. 실행 엔진이 하는 일에 대한 예시를 들어보자. 옵티마이저가 GROUP BY를 처리하기 위해 임해 임시 테이블을 사용하기로 결정했다고 해보자.
- 실행엔진이 핸들러에게 임시 테이블을 만들라고 요청
- 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
- 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
- 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
- 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
4.1.6.5 핸들러(스토리지 엔진)
핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다. 핸들러는 결국 스토리지 엔진을 의미한다.
4.1.7 복제
4.1.8 쿼리 캐시
MySQL 서버에서 쿼리 캐시는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당한다. 쿼리 캐시는 SQL의 실행결과를 메모리에 캐시 하고, 동일한 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보였다. 하지만 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제해야 했다. 이는 심각한 성능 저하를 유발한다. 결국 MySQL 8.0으로 올라오면서 쿼리 캐시는 MySQL 서버의 기능에서 완전히 제거되고, 관련된 시스템 변수도 모두 제거 됐다.
4.1.9 스레드 풀
스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다. 많은 사람들이 MySQL 서버에서 스레드 풀만 설치하면 성능이 그냥 두 배쯤 올라갈 거라고 기대하는데, 실제 서비스에서 그렇게 눈에 띄는 성능 향상을 보여준 경우는 드물다. 스레드를 제대로 확보하지 못한 경우에는 쿼리처리가 더 느려질 수도 있다는 점에 주의하자.
4.1.10 트랜잭션 지원 메타데이터
MySQL 8.0부터는 이러한 문제점을 해결하기 위해 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB의 테이블에 저장하도록 개선됐다. MySQL 8.0부터 데이터 딕셔너리와 시스템 테이블이 모두 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장되도록 개선되면서 이제 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료된다고 하더라도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리된다. 기존의 파일 기반 메타데이터를 사용할 때와 같이 작업 진행 중인 상태로 남으면서 문제를 유발하지 않게 개선된 것이다.
4.2 InnoDB 스토리지 엔진 아키텍처
InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공한다 그렇기 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.
4.2.1 프라이머리 키에 의한 클러스터링
InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장된다. 즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며, 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다. 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조인덱스보다 프라이머리 키가 선택될 확률이 높다. InnoDB스토리지 엔진과는 달리 MyISAM스토리지 엔진에서는 클러스터링 키를 지원하지 않는다.
4.2.2 왜래 키 지원
외래키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY테이블에서는 사용할 수 없다. InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고 그로 인해 데드락이 발생할 때가 많다.
4.2.3 MVCC(Multi Version Concurrency Control)
일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다. InnoDB는 언두 로그를 통해 이 기능을 구현한다. 여기서 멀티 버전이라 함은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미이다.
4.2.4 잠금 없는 일관된 읽기(Non-Locking Consistent Read)
InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다. 잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다.
4.2.5 자동 데드락 감지
InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기목록을 그래프 형태로 관리한다. InnoDB스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠김 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료한다.
4.2.6 자동화 장애 복구
InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러 가지 메커니즘이 탑재돼 있다. 그러한 메커니즘을 이용해 MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다.
4.2.7 InnoDB 버퍼 풀
InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.
쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼역할도 같이 한다.
4.2.8 Double Write Buffer
InnoDB 스토리지 엔진의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다. 이로 인해 InnoDB의 스토리지 엔진에서 더티 페이지를 디스크 파일로 플러시 할 때 일부만 기록되는 문제가 발생하면 그 페이지의 내용은 복구할 수 없을 수도 있다. 이렇게 페이지가 일부만 기록되는 현상을 파셜 페이지 또는 톤 페이지라고 하는데 이런 현상은 하드웨어의 오작동이나 시스템의 비정상 종료등으로 발생할 수 있다. InnoDB 스토리지 엔진에서는 이 같은 문제를 막기 위해 Double-Write 기법을 이용한다.
4.2.9 언두 로그
InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전 버전의 데이터를 별도로 백업한다. 이렇게 백업된 데이터를 언두 로그라고 한다.
- 트랜잭션 보장 : 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데, 이때 언두 로그에 백업해 둔 이전 버전의 데이터를 이용해 복구한다.
- 격리 수준 보장 : 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경 중인 레코드를 읽지 않고 언두 로그에 백업해 둔 데이터를 읽어서 반환하기도 한다.
4.2.10 체인지 버퍼
RDBMS에서 레코드가 Insert 되거나 Update 될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요하다. 그런데 인덱스를 업데이트하는 작업은 랜덤 하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모하게 된다. 그래서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상하게 되는데, 이때 사용하는 임시 메모리 공간을 체인지 버퍼 라고 한다.
4.2.11 리두 로그 및 로그 버퍼
리두 로그는 트랜잭션의 4가지 요소인 ACID 중에서 D에 해당하는 영속성과 가장 밀접하게 연관돼 있다. 리두 로그는 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.
4.2.12 어댑티브 해시 인덱스
어댑티브 해시 인덱스는 사용자가 수동으로 생성하는 인덱스이며, 사용자는 innodb_adaptive_hash_index 시스템변수를 이용해서 어댑티브 해시 인덱스 기능을 활성화하거나 비활성화할 수 있다.
4.2.13 InnoDB와 MyISAM
MySQL 8.0으로 업그레이드되면서 MySQL 서버의 모든 시스템이 기존과 다르게(MyISAM) InnoDB 스토리지 엔진으로 교체 됐고, 공간 좌표 검색이나 전문 검색 기능이 모두 InnoDB 스토리지 엔진을 지원하도록 개선됐다. 이후 버전에서는 MyISAM 스토리지 엔진은 없어질 것으로 예상된다. MyISAM 스토리지 엔진만이 가지는 장점이 없는 상태이다. 때로는 MEMORY 스토리지 엔진이 이름 때문에 과대평가를 받는 경우가 있지만 처리 성능에 있어서 InnoDB를 따라갈 수 없다.
4.3 MyISAM 스토리지 엔진 아키텍처
'DB > Real Mysql' 카테고리의 다른 글
| [Real Mysql] 03. 사용자 및 권한 (0) | 2023.02.06 |
|---|
