MYSQL 아키텍처

MYSQL 엔진 

MySQL 엔진은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리의 최적화된 실행된다.

MySQL 표준 SQL문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS 호환되어 실행 가능하다.

 

스토리지 엔진 

MYSQL 엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS 두뇌에 해당하는 처리를 수행하고, 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분을 스토리지 엔진이 전담한다. 
MYSQL 엔진은 하나지만 스토리지 엔진은 여러개를 사용할 수 있다. 

 

create table test_table (fd1 INT, fd2 INT)ENGINE=INNODB;

 

test_table은 InnoDB 스토리지 엔진을 사용하도록 정의했다. 

각 스토리지엔진은 성능을 향상하기 위해 키 캐시(MyISAM 스토리지 엔진)나 InnoDB 버퍼 풀(InnoDB 스토리지 엔진)과 같은 기능을 내장하고 있다. 

 

핸들러 API

MYSQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이러한 요청을 핸들러(Handler) 요청이라 하고, 여기서 사용되는 API 핸들러 API라고 한다. 

 

핸들러 API를 통해서 얼마나 많은 데이터(레코드) 작업이 있었는지 확인이 가능하다.

SHOW GLOBAL STATUS LIKE 'Handler%'

 

 

 

MySQL 스레딩 구조 

MYSQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드 (Foreground) 스레드와 백그라운드(Background)스레드로 구분할 수 있다. 

 

MYSQL 서버에서 실행 중인 스레드 목록 명령어 

 

SELECT tread_id, name, type, processlist_user, processlist_host 
FROM performance_schema.thread.threads ORDER BY type, thread_id;

 

동일한 이름의 스레드가 2개 이상씩 보이는 것은 MySQL 서버의 생성 내용에 의해 여러 스레[드가 동일 작업을 병렬로 처리 

 

MYSQL 엔터프라이즈 에디션과 Percona MySQL 서버에서는 전통적인 스레드 모델뿐만아니라 스레드 폴(Thread Pool)모델을 사용할 수도 있다. 스레드 폴과 전통적인 스레드 모델의 가장 큰 차이점은 포그라운드 스레드와 커넥션의 관계다. 

전통적인 스레드 모델에서는 커넥션별로 포그라운드 스레드가 하나씩 생성되고 할당된다. 

하지만 스레드 폴에서는 커넥션과 포그라운드 스레드는 1:1 관계가 아니라 하나의 스레드가 여러 개의 커넥션 요청을 전담한다. 

 

포그라운드 스레드(클라이언트 스레드)

포그라운드 스레드는 접속한 클라이언트 수만큼 존재하며, 클라이언트가 요청한 쿼리문장을 처리한다. 

클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread cache)캐시로 되돌아간다. 

이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다.

이때 스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정한다.

 

포그라운드 스레드는 MYSQL 데이터 버퍼나 캐시로부터 데이터를 가져온다. 

버퍼나 캐시에 없으면 직접 디스크의 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다. 

InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다. 

 

백그라운드 스레드 

- 인서트 버퍼(Insert Buffer) 병합하는 스레드 

- 로그를 디스크로 기록하는 스레드 

- InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드

- 데이터를 버퍼로 읽어 오는 스레드 

- 잠금이나 데드락을 모니터링하는 스레드 

 

모두 중요한 역할을 하지만 그중에서도 가장 중요한 것은 로그 스레드(Log thread), 쓰기 스레드(Write thread) 이다. 

 

쓰기 스레드- 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리 

 

MYSQL 5.5 버전에서 부터 데이터 쓰기 스레드, 데이터 일기 스레드 개수 2개 이상 지정할 수 있게 변경되었다. 

 

innodb_write_io_threads  와 innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정한다. 

 

주로 InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 일반적인 내장 디스크 사용할 때는 2~4, DAS나 SAN과 같은 스토리지를 사용할 때는 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정 하는 것이 좋다. 

 

1. DAS (Direct Attached Storage) - 직접 연결 스토리지

DAS는 서버와 직접 연결된 스토리지를 의미해. 가장 기본적인 스토리지 방식으로, 서버 내부에 장착하거나 외부에서 직접 연결해서 사용하는 방식이야.

 

2. SAN (Storage Area Network) - 네트워크 기반 스토리지

SAN은 여러 서버가 네트워크를 통해 공유할 수 있는 고속 스토리지 네트워크야. 보통 광섬유(Fiber Channel, FC) 또는 iSCSI 같은 기술을 사용해서 빠르게 데이터를 주고받을 수 있어.

 

사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터 읽기 작업은 절대 지연될 수 없다. 

사용 DBMS 에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있다. -> InnoDB same 

 

InnoDB도 쿼리로 데이터 변경되는 경우 데이터가 디스크 데이터 파일로 완전히 저장되는 것을 기다리지 않아도 된다. 

 

메모리 할당 및 사용 구조 

MySQL 사용되는 메모리 공간은 글로벌 영역과 로컬 메모리 영역으로 구분할 수 있다. 

글로벌 메모리 공간은 운영체제에서 MYSQL 서버가 시작되면서 할당되는 공간이다. 

 

 

글로벌 메모리 영역

일반적으로 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당된다. 필요에 따라 2개 이상의 메모리 공간 할당을 받을 수 있지만 클라이언트 스레드 수와는 무관하다. 생성된 글로벌 영역이 N개여도 모든 스레드에 의해 공유된다. 

 

글로벌 영역의 종류 

1. 테이블 캐시

2. InnoDB 버퍼 풀

3. InnoDB 어댑티드 해시 인덱스 

4. InnoDB 리두 로그 버퍼 

 

로컬 메모리 영역 

세션 메모리 영역이라고도 표현하며, MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다. 

 

커넥션 버퍼와 정렬 버퍼 등이 있다. 클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다. 

 

클라이언트와 MYSQL 서버와의 커넥션을 세션이라고 하기 때문에 로컬 메모리 영역을 세션 메모리 영역이라고도 표현한다.

 

로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다. 

일반적으로 글로벌 메모리 영역의 크기는 주의해서 설정학지만 소트 버퍼와 같은 로컬 메모리 영역을 크게 신경쓰지 않고 설정하는데, 최악의 경우에는 MySQL 서버가 메모리 부족으로 멈춰 버릴 수도 있으므로 적절한 메모리 공간을 설정하는 것이 중요하다. 

 

대표적인 로컬 메모리 영역은 다음과 같다. 

- 정렬 버퍼(Sort buffer)

- 조인 버퍼

- 바이너리 로그 캐시

- 네트워크 버퍼 

 

플러그인 스토리지 엔진 모델 

플러그인해서 사용할 수 있는 것이 스토리지 엔진만 있는 것이 아니다. 전문 검색 엔진을 위한 파서(인덱싱할 키워드를 분리해내는 작업)도 플로그인 형태로 개발해서 사용할 수 있으며, 사용자의 인증을 위한 Native Authentication 과 Caching SHA-2 Authentication 등도 모두 플러그인으로 구현되어 제공된다.

 

사용자가 직접 스토리지 엔진을 개발 하는 것도 가능하다.

대부분의 작업이 MySQL 엔진에서 처리되고 데이터 읽기/쓰기 작업만 스토리지 엔진에 의해 처리된다. 

새로운 용도의 스토리지 엔진을 만든다 하더라도 DBMS 전체 기능이 아닌 일부분의 기능만 수행하는 엔진을 작성하게 된다. 

 

'데이터 읽기/쓰기' 작업은 대부분 1건의 레코드 단위 또는 마지막으로 읽은 레코드의 다음 또는 마지막으로 읽은 레코드의 다음 이전 레코드 읽기와 같이)로 처리된다. 

MySQL 사용하면 'Handler'라는 단어를 자주 접하게 된다. 핸들러라는 단어는 MySQL 서버의 소스코드로부터 넘어온 표현이다. 

프로그래밍에서 어떤 기능을 호출하기 위해 사용하는 객체를 핸들러라고 표현한다. 

 

MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용하게 된다. 

MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야 한다는 점만 기억하자. 

'Handler_'로 시작하는 상태 변수는 'MySQL 엔진이 각 스토리지 엔진에게 보내는 명령의 횟수를 의미하는 변수'

 

MySQL에서 MyISAM 이나 InnoDB 같은 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도 MySQL 처리 내용은 대부분 동일하며, '데이터 읽기/쓰기' 영역의 처리만 차이가 있을 뿐이다. 

 

실질적인 GROUP BY 나 ORDER BY 등 복잡한 처리는 스토리지 엔진 영역이 아니라 MySQL 엔진의 처리 영역인 '쿼리 실행기'에서 처리된다. 

 

하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다.

 

SHOW ENGINES;

 

YES : MySQL 서버(mysqld) 해당 스토리지 엔진이 포함돼 있고, 사용 가능으로 활성화된 상태임

DEFAULT : 'YES' 동일한 상태이지만 필수 스토리지 엔진임을 의미함 

NO : 현재 MySQL 서버(mysqld) 포함되지 않았음을 의미함

DISABLED: 현재 MySQL 서버(mysqld) 포함됐지만 파라미터에 의해 비활성화된 상태임 

 

MySQL 서버(mysqld) 포함되지 않은 스토리지 엔진을 사용하려면 MySQL 서버를 다시 빌드해야 한다. 

MySQL 서버가 잘 준비되어있다면 플러그인 형태의 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워 넣기만 하면 사용할 수 있다. 

 

SHOW PLUGINS;

 

 

컴포넌트 

기존 플러그인 아키텍처 대체하기 위해 컴포넌트 아키텍처 지원한다. 

 

기존 플러그인 아키텍처 단점)

- 플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없음

- 플러그인은 MySQL 서버와 변수나 함수를 직접 호출하기 때문에 안전하지 않음 

- 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움 

 

 

validate_password 컴포넌트 설치 

INSTALL COMPONENT 'file://component_validate_password';

 

 

설치된 컴포넌트 확인

SELECT * FROM mysql.component;

 

 

쿼리 실행 구조 

 

SQL 요청 -> 쿼리 파서 -> 전처리기 -> 옵티마이저 -> 쿼리 실행기 -> 스토리지 엔진 (InnoDB, MyISAM, Memory)

 

쿼리파서 

쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다. 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달한다. 

 

전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 이름이나 칼럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한등을 확인하는 과정을 이 단계에서 수행한다. 

 

옵티마이저 

옵티마이저란 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당한다. 

 

실행엔진

옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다. 

 

옵티마이저가 GROUP BY 처리하기 위해 임시 테이블을 사용하기로 결정 예시)

1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청

2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청

3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청

4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청

5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김 

 

핸들러 

MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다. 

핸들러는 결국 스토리지 엔진을 의미하며, MyISAM 테이블을 조작하는 경우에는 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 된다. 

 

복제

MySQL 서버에서 복제는 매우 중요한 역할을 담당한다. 

 

쿼리 캐시 

MySQL 서버에서 쿼리 캐시는 빠른 응답을 필요로 하는 웹 기반의 응용프로그램에서 매우 중요한 역할을 담당했다. 

쿼리 캐시는 SQL 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보여준다. 하지만 캐시는 테이블 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들을 모두 삭제해야 한다. 

이는 심각한 처리 성능 저하를 유발한다. 

쿼리 캐시는 계속된 동시 처리 성능 저하와 많은 버그 원인이 되기도 한다. 

 

8.0 부터 쿼리 캐시는 완전히 제거되고, 관련된 시스템 변수도 모두 제거 됐다. 

 

스레드 풀 

MySQL 서버 엔터프라이즈 에디션은 스레트 풀 기능을 제공하지만 MySQL 커뮤니티 에디션은 스레드 풀 기능을 지원하지 않는다.

 

MySQL 엔터프라이즈 스레드 풀 기능은 MySQL 서버 프로그램에 내장돼 있지만 Percona Server의 스레드 풀은 플러그인 형태로 작동하는 차이점이 있다. 

만약 MySQL 커뮤니티 에디션에서도 스레드 풀 기능을 사용하고자 한다면 동일 버전의 Percona Server에서 스레드 풀 플러그인 라이브러리를 MySQL 에디션 서버에 설치하면 된다. (Install PLUGIN)

 

스레드 풀은 내부적으로 사용자 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많더라도 MySQL 서버의 CPI 제한된 개수와 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다. 

많은 사람들은 스레드 풀이 성능 향상을 가져다 줄 것을 기대하지만 but 서비스에서 성능 향상을 보여준 경우는 드물다. 

실행중인 스레드들을 CPU 가 최대한 미리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능이기 때문에 스케줄링 과정에서 CPU시간 제대로 확보 못하면 쿼리 처리가 더 느려질 수 있다. 

 

Percona Server 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성하는데, 스레드 그룹의 개수는 thread_pool_size 시스템 변경해서 조정할 수 있다. 하지만 일반적으로는 CPU 코어의 개수와 맞추는 것이 CPU 프로세서 친화도를 높이는데 좋다. 

MySQL 서버가 처리해야 할 요청이 생기면 스레드 풀로 처리를 이관하는데, 만약 이미 스레드 풀이 처리 중인 작업이 있는 경우에 thread_pool_oversubscribe 시스템 변수(기본값 3) 설정된 개수만큼 추가로 더 받아들여서 처리한다. 

 

해당 값이 커지면 비효율적이게 변함 

 

트랜잭션 지원 메타 데이터 

데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 하는데, MySQL 서버는 5.7버전까지 테이블의 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일 (*.TRN, *.TRG, *.PAR) 기반으로 관리 했다.

하지만 이러한 파일 기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 떄문에 테이블의 생성 또는 변경 도중에 MySQL 서버가 일관되지 않은 상태로 남는 문제가 있었다.  해당 현상을 '데이터베이스 테이블이 깨졌다'라는 표현을 사용했다.

 

8.0버전 부터 문제를 해결하기 위해서 테이블 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB 테이블에 저장하도록 개선됐다. MySQL 서버가 작동하는 데 기본적으로 필요한 테이블을 묶어서 시스템 테이블이라고 한다. 

대표적으로 사용자의 인증과 권한에 관련된 테이블들이 있다. MySQL 서버 8.0버전 부터는 이런 시스템 테이블을 모두 InnoDB 스토리지 엔진을 사용하도록 개선했으며, 시스템 테이블과 데이터 딕셔너리 정보를 모두 모아서 mysql DB 에 저장하고 있다. 

 

MySQL 서버는 InnoDB 스토리지 엔진을 사용하는 테이블은 메타 정보가 InnoDB 테이블 딕셔너리에 저장되지만 MyISAM , CSV 등과 같은 스토리지 엔진의 메타 정보는 아직도 여전히 저장공간이 필요하다. 그래서 MySQL 서버는 InnoDB 이외에 스토리지 엔진을 사용하는데에 SDI(Serialized Dictionary Information) 파일을 사용한다. 

InnoDB 이외에 테이블에 대해서는 *.sdi 파일이 존재해야함. 

 

 

mysql.tables 딕셔너리 데이터를 위한 테이블 구조 

ibd2sdi mysql_data_dir/mysql.ibd > mysql_schema.json
cat mysql_schema_json

 

InnoDB 스토리지 엔진 아키텍처 

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하며, 그 때문에 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다. 

 

 

프라이머리 키에 의한 클러스터링 

InnoDB 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다. 즉, 프라이머리 키 값의 순서대로 디스크에 저장된다.

모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다. 

프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키의 값을 논리적인 주소로 사용한다. 프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정(쿼리의 실행 계획에서 다른 보조 인덱스보다 프라이머리 키가 선택될 확률이 높음)된다. 

 

InnoDB 스토리지 엔진과는 달리 MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다. 

 

 

외래 키 지원 

외래 키에 대한 자원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM 그리고 MEMORY 테이블에서는 사용할 수 없다. 

외래 키는 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 자주 있는데, 그렇게 하더라도 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다. InnoDB 외래키는 부모 테이블과 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래 키의 존재에 주의하는 것이 좋다. 

 

수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업이 실패할 수 있다. 물론 부모 테이블과 자식 테이블의 관계를 명확히 파악해서 순서대로 작업한다면 문제없이 실행할 수 있지만 외래키가 복잡하게 얽힌 경우에는 간단하지 않다. 또한 서비스에 문제가 있어 긴급하게 조치해야하는데 이런 문제가 발생하면 조급해질 수 있다. 

foreign_key_checks 시스템 변수를 OFF 로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈추고 작업할 수 있다. 

 

SET foreign_key_checks=OFF;

 

외래키 체크를 일시적으로 해제했다고 해서, 부모와 자식 테이블간의 관계가 깨진 상태로 유지되어야 한다는 말은 아니다. 부모 테이블의 레코드를 삭제했다면, 자식 테이블의 관련된 레코드도 반드시 삭제해주어야 한다.