4.1 MySQL 엔진 아키텍처

4.1 MySQL 전체 구조

1) MySql 엔진 스토리지 엔진 핸들러 API

MySQL 서버는 크게 두 부분으로 나눌 수 있습니다: MySQL 엔진과 스토리지 엔진입니다.

1. MySQL 엔진: 클라이언트로 부터의 요청을 처리하고, SQL 문장을 분석하고 전처리하며, 최적의 실행 계획을 세우는 등의 역할을 합니다. 이러한 작업은 커넥션 핸들러, SQL 파서 및 전처리기, 옵티마이저 등에 의해 수행됩니다.

   1. 1. 커넥션 핸들러: 클라이언트와 MySQL 서버 간의 연결을 관리합니다. 클라이언트가 MySQL 서버에 접속 요청을 보내면, 커넥션 핸들러는 새로운 스레드를 생성하여 해당 클라이언트의 요청을 처리합니다. 클라이언트의 연결이 종료되면, 해당 스레드는 종료되거나 스레드 캐시로 반환됩니다.
   2. SQL 파서 및 전처리기: 클라이언트로부터 받은 SQL 쿼리를 분석하고 검증하는 역할을 합니다. SQL 파서는 쿼리를 여러 개의 토큰으로 분리하고, 이 토큰들의 문법적인 구조를 검사하여 쿼리가 유효한지 확인합니다. 전처리기는 파서가 생성한 토큰들을 바탕으로 쿼리의 의미를 검사하고, 필요한 테이블이나 컬럼이 실제로 존재하는지, 클라이언트가 해당 작업을 수행할 권한이 있는지 등을 확인합니다.
   3. 옵티마이저: SQL 쿼리를 어떻게 실행할지 결정하는 역할을 합니다. 여러 가지 실행 계획을 고려하고, 각 계획의 비용을 추정하여 가장 효율적인 실행 계획을 선택합니다. 이 과정에서 테이블의 통계 정보, 인덱스의 유무, 데이터의 분포 등 다양한 요소를 고려합니다.

2. 스토리지 엔진: 실제 데이터를 디스크 스토리지에 저장하거나 읽어오는 역할을 담당합니다. MySQL 엔진은 하나만 있지만, 스토리지 엔진은 여러 개를 동시에 사용할 수 있습니다. 예를 들어, CREATE TABLE test_table (…) ENGINE=INNODB; 명령을 사용하면, test_table 테이블에 대한 데이터 조작 요청은 InnoDB 스토리지 엔진이 처리하게 됩니다.

또한, 핸들러 API라는 것이 있습니다. 이는 MySQL 엔진이 쿼리를 실행하기 위해 스토리지 엔진에게 요청을 보내는 방법입니다. 이러한 요청을 '핸들러 요청'이라고 하며, 이를 위해 사용되는 API를 '핸들러 API'라고 합니다. 핸들러 API를 통해 얼마나 많은 데이터 작업이 이루어졌는지 확인할 수 있습니다. 예를 들어, SHOW GLOBAL STATUS LIKE 'Handler%'; 명령을 사용하면 됩니다.

이렇게 MySQL은 사용자의 요청을 처리하고, 데이터를 저장하고, 읽어오는 등의 작업을 수행하며, 이를 위해 위에서 설명한 다양한 컴포넌트와 API를 사용합니다. 이는 MySQL이 다양한 프로그래밍 언어로부터 접근이 가능하게 하며, 높은 성능과 효율성을 제공합니다.

2) 스레딩 구조

MySQL의 작동 구조는 크게 프로세스 기반이 아닌 스레드 기반으로 이루어져 있습니다. 이 스레드들은 크게 포그라운드 스레드와 백그라운드 스레드로 구분됩니다.

1. 포그라운드 스레드: 이 스레드는 사용자의 요청을 처리하는 역할을 합니다. 예를 들어, 사용자가 MySQL 서버에 접속하면, 그 사용자를 위한 포그라운드 스레드가 생성됩니다. 이 스레드는 사용자가 요청하는 쿼리를 처리하며, 사용자의 연결이 종료되면 스레드 캐시로 돌아가 재사용을 위해 대기합니다. 이때, thread_cache_size 설정 값보다 많은 스레드가 생성되면, 추가로 생성된 스레드는 캐시에 돌려놓지 않고 종료됩니다.

2. 백그라운드 스레드: 이 스레드는 데이터베이스의 내부 작업을 처리하는 역할을 합니다. 예를 들어, InnoDB 스토리지 엔진에서는 데이터를 디스크에 기록하거나, 데이터를 버퍼로 읽어오는 등의 작업을 백그라운드 스레드가 처리합니다. 이러한 백그라운드 스레드의 개수는 innodb_read_io_threads, innodb_write_io_threads 등의 설정으로 조절할 수 있습니다. Mysql 5.5 버전부터는 쓰기 스레드를 2개 이상으로 설정할 수 있으며, 쓰기 스레드는 많은 작업을 처리하기 때문에, 일반적인 재장 디스크를 사용할 때는 2~4개 정도로 설정하는 것이 좋습니다.

이렇게 MySQL은 여러 스레드를 활용하여 효율적으로 작업을 처리합니다. 사용자의 요청을 처리하는 포그라운드 스레드와 데이터베이스의 내부 작업을 처리하는 백그라운드 스레드가 협력하여 빠른 응답 시간과 높은 처리 성능을 제공합니다. 이러한 스레드 기반의 구조는 MySQL이 높은 동시성을 지원하고, 다양한 작업을 효율적으로 처리할 수 있게 합니다.

3) 메모리 할당 및 사용 구조

1. 글로벌 메모리 영역

MySQL 서버가 시작될 때 운영체제로부터 할당되는 메모리 영역입니다. 이 메모리는 정확히 할당된 양을 확인하기 어려우며, 시스템 변수로 설정한 만큼 할당받는다고 생각할 수 있습니다. 이와 관련된 시스템 변수는 다음과 같습니다:

• innodb_buffer_pool_size: InnoDB 버퍼 풀의 크기를 설정합니다.
• innodb_log_buffer_size: InnoDB 로그 버퍼의 크기를 설정합니다.

이러한 글로벌 메모리 영역에는 테이블 캐시, InnoDB 버퍼 풀, InnoDB 어댑티브 해시 인덱스, InnoDB 리두 로그 버퍼 등이 포함됩니다.

1. 테이블 캐시: 테이블 캐시는 자주 사용되는 테이블의 데이터와 인덱스를 메모리에 보관하여 데이터베이스의 성능을 향상시킵니다. 이를 통해 데이터에 대한 빠른 액세스가 가능해지며, 디스크에서 데이터를 읽어오는 횟수를 줄여줍니다.

2. InnoDB 버퍼 풀: InnoDB 버퍼 풀은 InnoDB 테이블의 데이터와 인덱스를 메모리에 캐싱하여 빠른 읽기 작업을 지원합니다. 이를 통해 자주 액세스되는 데이터에 대한 디스크 I/O를 줄여주고, 데이터베이스의 성능을 향상시킵니다.

3. InnoDB 어댑티브 해시 인덱스: InnoDB 어댑티브 해시 인덱스는 쿼리 실행 중에 자동으로 생성되는 인덱스로, 자주 액세스되는 쿼리의 성능을 향상시킵니다. 이를 통해 데이터베이스의 성능을 최적화하고, 쿼리 실행 속도를 향상시킵니다.

4. InnoDB 리두 로그 버퍼: InnoDB 리두 로그 버퍼는 트랜잭션의 변경 내역을 기록하여 데이터베이스의 내구성을 보장합니다. 이를 통해 데이터베이스의 안정성을 유지하고, 장애 발생 시 데이터의 일관성을 보장합니다.

2. 로컬 메모리 영역

로컬 메모리 영역은 세션 메모리 영역이라고도 불리며, 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역입니다. 각 클라이언트 스레드별로 독립적으로 할당되며, 공유되지 않습니다. 이러한 로컬 메모리 영역에는 정렬 버퍼, 조인 버퍼, 바이너리 로그 캐시, 네트워크 버퍼 등이 포함됩니다.

1. 정렬 버퍼: 정렬 버퍼는 정렬 작업을 수행할 때 사용되는 메모리 공간으로, ORDER BY 및 GROUP BY와 같은 연산에서 데이터를 일시적으로 저장하고 처리합니다. 이를 통해 정렬 작업의 성능을 향상시키고, 데이터 처리 속도를 높일 수 있습니다.

2. 조인 버퍼: 조인 버퍼는 조인 작업을 수행할 때 사용되는 메모리 공간으로, 여러 테이블 간의 조인 연산에서 중간 결과를 저장하고 처리합니다. 이를 통해 조인 작업의 성능을 최적화하고, 데이터베이스의 처리 속도를 향상시킵니다.

3. 바이너리 로그 캐시: 바이너리 로그 캐시는 바이너리 로그 파일에 대한 쓰기 작업을 버퍼링하여 디스크에 효율적으로 기록합니다. 이를 통해 바이너리 로그의 처리 속도를 향상시키고, 데이터의 내구성을 보장합니다.

4. 네트워크 버퍼: 네트워크 버퍼는 클라이언트와 서버 간의 통신에 사용되는 메모리 공간으로, 네트워크 상에서 데이터 전송을 위해 사용됩니다. 이를 통해 클라이언트와 서버 간의 효율적인 통신을 지원하고, 데이터 전송 속도를 향상시킵니다.

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

MySQL의 플러그인 모델은 스토리지 엔진, 검색어 파서 등과 같은 다양한 기능을 플러그인으로 추가할 수 있는 독특한 특징을 가지고 있습니다. 또한, 사용자의 인증을 위한 Native Authentication과 같은 기능도 플러그인으로 제공됩니다. 이는 사용자가 직접 스토리지 엔진을 개발하거나 쿼리 실행에 필요한 작업을 스토리지 엔진에서 처리할 수 있게 합니다.

실제로 MySQL 엔진은 대부분의 작업을 처리하고, 마지막 "읽기/쓰기" 작업만 스토리지 엔진에서 처리합니다. 이는 스토리지 엔진을 개발할 때 전체 기능을 개발하는 것이 아니라 일부 기능만을 개발한다는 의미입니다. MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽고/쓰기 위해서는 반드시 핸들러를 통해야 합니다.

또한, GROUP BY나 ORDER BY와 같은 복잡한 처리는 MySQL 엔진의 '쿼리 실행기'에서 처리됩니다. 이러한 처리를 위해서는 많은 양의 데이터를 처리하기 위한 메모리가 필요합니다.

MySQL에서 지원되는 스토리지 엔진은 다음과 같이 확인할 수 있습니다:

SHOW ENGINES;

만약 포함되지 않은 스토리지 엔진을 사용하려면, 서버를 다시 빌드(컴파일)해야 합니다. 그러나 적절한 준비만 되어 있다면 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 쉽게 사용할 수 있습니다. 또한, 서버의 플러그인은 다음과 같이 확인할 수 있습니다:

SHOW PLUGINS;

적절한 준비는 MySQL 서버의 환경과 운영에 따라 다를 수 있습니다. 일반적으로는 새로운 플러그인을 추가하기 위해서는 해당 플러그인에 필요한 라이브러리 및 의존성을 설치하고, MySQL 서버의 설정을 업데이트하여 플러그인을 활성화하는 등의 작업이 필요할 수 있습니다.

5) 컴포넌트

MySQL 8.0에서는 기존 플러그인 아키텍처를 대체하기 위해 새로운 컴포넌트 아키텍처를 도입했습니다. 이는 기존 플러그인 아키텍처의 몇 가지 단점을 해결하기 위한 것이었습니다.

기존 플러그인 아키텍처의 단점은 다음과 같습니다:

1. 플러그인은 오직 MySQL 서버와만 인터페이스할 수 있었고, 플러그인끼리는 통신할 수 없었습니다. 이로 인해 플러그인 간의 상호 작용이 제한적이었습니다.

2. 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않았습니다. 이는 플러그인이 서버의 내부 상태를 변경할 수 있음을 의미하며, 이로 인해 데이터 무결성이 손상될 수 있었습니다.

3. 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려웠습니다. 이는 플러그인이 서로에게 의존하는 경우, 어떤 플러그인이 먼저 초기화되어야 하는지 결정하기 어려웠음을 의미합니다.

이러한 문제를 해결하기 위해 MySQL 8.0에서는 컴포넌트 아키텍처를 도입하였습니다. 이는 플러그인 간의 상호 작용을 가능하게 하고, 안전한 인터페이스를 제공하며, 상호 의존성을 관리할 수 있게 해줍니다.

예를 들어, 비밀번호 검증에 대해 MySQL 5.7은 플러그인을 사용했지만, MySQL 8.0에서는 이를 컴포넌트로 개선하였습니다. 이로 인해 비밀번호 검증 과정이 더 안전해졌으며, 비밀번호 검증 컴포넌트가 다른 컴포넌트와 상호 작용할 수 있게 되었습니다.

그러나 컴포넌트도 플러그인과 마찬가지로 설치하면 새로운 시스템 변수를 설정해야 할 수 있습니다. 따라서 컴포넌트를 사용할 때는 해당 컴포넌트의 메뉴얼을 확인해야 합니다.

6) 쿼리 실행 구조

MySQL에서 쿼리가 처리되는 과정은 크게 4단계로 나눌 수 있습니다: 쿼리 파서, 전처리기, 옵티마이저, 실행엔진.

1. 쿼리 파서(Query Parser): 쿼리 파서는 사용자가 입력한 SQL 쿼리를 알맞은 토큰으로 분리하고, 이를 트리 형태로 변환하는 역할을 합니다. 예를 들어, 사용자가 SELECT * FROM users WHERE age > 20;라는 쿼리를 입력하면, 쿼리 파서는 이를 SELECT, *, FROM, users, WHERE, age, >, 20 등의 토큰으로 분리하고, 이를 트리 형태로 변환합니다. 또한, 쿼리 파서는 쿼리 문장의 기본 문법 오류를 인식합니다. 즉, 문법적으로 잘못된 쿼리가 입력되면 쿼리 파서에서 오류를 발생시킵니다.

2. 전처리기(Preprocessor): 전처리기는 쿼리 파서에서 생성된 토큰 트리를 문법적으로 인식하고, 문장에 구조적인 문제가 있는지 확인합니다. 예를 들어, SELECT * FORM users;와 같이 FROM이 FORM으로 잘못 입력된 경우, 전처리기에서 이를 인식하고 오류를 발생시킵니다. 또한, 전처리기는 토큰을 테이블 이름이나, 내장 함수 등과 같은 개체로 매핑하고, 해당 객체의 존재 여부와 접근 권한을 파악합니다. 존재하지 않거나 접근 불가능한 토큰은 이 단계에서 걸러집니다.

3. 옵티마이저(Optimizer): 옵티마이저는 SQL 문의 최적화를 담당합니다. 이는 DBMS의 두뇌에 해당하는 부분으로, 어떤 인덱스를 사용할 것인지, 어떤 순서로 테이블을 조인할 것인지 등의 결정을 내립니다. 이 과정에서 옵티마이저는 통계 정보를 활용하여 가장 효율적인 쿼리 실행 계획을 세우게 됩니다.

4. 실행엔진(Execution Engine): 실행엔진은 옵티마이저가 결정한 계획을 실제로 수행하는 역할을 합니다. 이는 DBMS의 손과 발에 비유할 수 있습니다. 예를 들어, 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다면, 실행 엔진은 핸들러에게 임시 테이블을 만들라고 요청하고, WHERE 절에 일치하는 레코드를 읽어오라고 요청하며, 읽어온 레코드들을 임시 테이블로 저장하라고 요청합니다. 그리고 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 요청합니다. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘깁니다.

이렇게 MySQL에서는 쿼리 파서, 전처리기, 옵티마이저, 실행엔진이라는 4단계를 거쳐 사용자의 쿼리를 처리하게 됩니다. 이 각 단계는 서로 밀접하게 연관되어 있으며, 각각의 역할을 통해 효율적이고 안정적인 쿼리 처리를 가능하게 합니다.

7) 쿼리 캐시

쿼리 캐시는 데이터베이스 관리 시스템(DBMS)에서 매우 중요한 역할을 합니다. 이는 SQL 쿼리의 실행 결과를 메모리에 저장하고, 동일한 SQL 쿼리가 다시 요청될 경우에는 테이블을 읽지 않고 즉시 결과를 반환하는 기능을 가지고 있습니다 이로 인해 웹 기반의 응용 프로그램에서 빠른 응답을 제공할 수 있게 됩니다.

그러나 쿼리 캐시는 몇 가지 단점이 있습니다. 테이블의 데이터가 변경될 경우, 관련된 캐시를 삭제해야 하며, 이로 인해 동시 처리 성능이 저하될 수 있습니다 또한, 쿼리 캐시는 많은 버그의 원인이 되기도 했습니다. 이러한 이유로 MySQL 8.0 버전부터는 쿼리 캐시 기능 및 관련 시스템 변수가 모두 제거되었습니다.

8) 스레드 풀

엔터프라이즈 에디션은 스레드 풀 기능을 제공하며, 커뮤니티 에디션에서는 Percona Server에 플러그인 형태의 스레드 풀 라이브러리를 설치하여 사용할 수 있습니다 스레드 풀의 주요 목적은 사용자의 요청을 처리하는 스레드 개수를 제한하고, CPU가 제한된 스레드 처리에만 집중하여 서버의 자원 소모를 줄이는 것입니다. 이를 통해 CPU 프로세서 친화도를 높이고 불필요한 컨텍스트 스위치를 줄여 오버헤드를 감소시킵니다.

Percona Server 스레드 풀

Percona Server의 스레드 풀은 CPU 코어 개수만큼 스레드 그룹을 생성하며, thread_pool_size 값을 변경하여 조정할 수 있습니다. CPU 코어 개수와 일치하는 것이 CPU 프로세서 친화도를 증가시킵니다 thread_pool_oversubscribe 시스템 변수를 사용하여 스레드 풀이 부족할 경우 추가할 스레드를 지정할 수 있으며, 기본 값은 3입니다.

thread_pool_stall_limit 시스템 변수에 설정하는 밀리초를 통해 새로운 스레드를 추가할지 기다릴지를 결정할 수 있습니다. 응답 시간에 민감한 서비스라면 이 값을 적절하게 낮춰야 합니다. 0에 너무 가까운 값이면 스레드 풀을 사용하지 않는 편이 나을 것입니다 thread_pool_max_threads 시스템 변수로 전체 스레드 풀에 있는 스레드 개수를 제한할 수 있습니다.

스레드 풀에서는 선순위 큐와 후순위 큐를 사용하여 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주는 방식으로 순서를 재배치합니다.

예를 들어, 웹 서버에서 동시에 수천 개의 요청을 처리해야 하는 상황을 생각해봅시다. 이 경우, 각 요청을 처리하기 위해 스레드를 생성하면 서버의 자원이 과도하게 소모될 수 있습니다. 이럴 때 스레드 풀을 사용하면, 요청을 처리하는 스레드의 개수를 제한하고, CPU가 이 제한된 스레드 처리에만 집중할 수 있게 됩니다. 이로 인해 CPU의 프로세서 친화도가 높아지고, 불필요한 컨텍스트 스위치가 줄어들어 오버헤드가 감소하게 됩니다.

또한, 스레드 풀에서는 선순위 큐와 후순위 큐를 사용하여 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해줍니다. 이는 사용자의 요청에 빠르게 응답할 수 있게 해주며, 서버의 성능을 향상시키는 데 도움이 됩니다.

9) 트랜젝션 지원 메타데이터

메타 데이터는 데이터에 대한 데이터로, 테이블 구조 정보, 스토어드 프로그램 등의 정보를 포함합니다. 이러한 정보는 데이터 사전이라고도 부르며, 데이터베이스의 구조와 동작 방식을 정의하는 중요한 요소입니다.

MySQL 5.7까지의 메타 데이터 관리

MySQL 5.7 버전까지는 테이블 구조를 FRM 파일에 저장하였습니다. 또한, 스토어드 프로그램도 파일 기반으로 관리되었습니다. 이러한 파일 관리 방식은 트랜잭션을 지원하지 않았기 때문에, 해당 파일 수정 도중에 서버가 종료되면 일관되지 않은 파일로 남게 되어 "데이터베이스나 테이블이 깨졌다"라는 문제가 발생하였습니다.

MySQL 8.0부터의 메타 데이터 관리

MySQL 8.0 버전부터는 테이블 구조와 스토어드 프로그램 관련 정보를 모두 InnoDB 테이블에 저장하게 되었습니다. 이에는 시스템 테이블도 포함되며, 시스템 테이블은 서버 작동을 위한 기본적인 필요 테이블의 묶음을 의미합니다. 시스템 테이블과 데이터 사전 정보를 모두 모아서 mysql DB에 저장하며, mysql DB는 통째로 mysql.ibd라는 테이블 스페이스에 저장됩니다.

이러한 변경으로 인해, 데이터 사전에 대한 수정은 트랜잭션 기반이 되어 완전 성공 또는 완전한 실패로 정리되게 되었습니다. 또한, 데이터 사전을 저장한 테이블은 테이블 목록에 보이지 않아 사용자가 임의로 수정하지 못하게 되었습니다.

InnoDB 스토리지 엔진 이외에는 메타 정보를 *.sdi 파일로 보관하며, 이는 FRM 파일과 동일한 역할을 합니다. ibd2sdi 유틸을 통해 InnoDB 테이블스페이스에서 스키마 정보를 추출할 수 있습니다.