4. 아키텍처 (1) - MySQL 엔진

Yany Choi·2023년 4월 1일
mysql
0

DB

목록 보기
2/4

4.1.1 MySQL의 전체 구조

MySQL은 다른 DBMS와 비교해서 그 구조가 상이하다.


(출처 : https://github.com/bestdevhyo1225/dev-log/blob/master/MySQL/MySQL-Architecture.md)

MySQL 서버는 크게 MySQL 엔진, 스토리지 엔진으로 구별한다.
MySQL의 쿼리 파서나 옵티마이저 등과 같은 기능들을 MySQL 엔진에 포함한다.

MySQL 엔진

  • MySQL 엔진은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 Connection Handler와 SQL Parser, Preprocessor, Optimizer를 중심으로 이루어져 있다.
  • ANSI SQL 문법을 지원한다.

스토리지 엔진

MySQL 엔진은 SQL문장을 분석, 최적화 한다면 스토리지 서버는 실제 데이터를 저장하고 읽어오는 부분을 전담한다.

  • MySQL 서버에 MySQL 엔진은 하나지만 스토리지 엔진은 여러개가 존재할 수 있다.
  • 테이블 생성 시에 ENGINE={ENGINE_NAME} (e.g. INNODB) 를 작성하면 해당 테이블의 모든 읽기, 쓰기 작업들을 정의된 스토리지 엔진이 처리, 예시에서는 InnoDB 엔진을 사용하도록 정의한다.
  • 각 스토리지 엔진은 성능 향상을 위해 키 캐시 (MyISAM)나 InnoDB 버퍼 풀(InnoDB)과 같은 기능을 내장하고 있다.

핸들러 API

MySQL 엔진에서 데이터를 쓰거나 읽어야 할 때 각 스토리지 엔진에 I/O 요청을 하는데, 이것을 핸들러 요청이라 하며, 여기서 사용되는 API가 핸들러 API이다.

핸들러 API를 통한 데이터 작업 횟수를 SHOW GLOBAL STATUS LIKE 'Handler%'; 로 확인할 수 있다.

4.1.2 MySQL 스레딩 구조

MySQL 서버는 스레드 기반으로 작동하며, Foreground, Background Thread로 구분한다.

다음 쿼리로 performance_schema 데이터베이스의 threads 테이블에 있는 실행 중인 스레드의 목록을 확인할 수 있다;

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

  • 39개의 스레드 중 3개만 Foreground이고, 나머지는 Background Thread이다.
  • 이중 thread/sql/one_connection 만 실제 사용자의 요청을 처리한다.
  • Background Thread의 갯수는 MySQL 서버의 설정에 따라 가변적이다.
  • 동일한 이름의 Thread가 여러 개 보이는 것은 여러 Thread에서 병렬로 동일 작업을 처리하는 경우이다.

Foreground Thread (Client Thread)

  • Foreground Thread는 최소 MySQL에 접속한 client의 수만큼 존재한다.
  • 주로 각 client 사용자가 요청하는 쿼리 문장을 처리한다.
  • client에서 작업을 마치고 커넥션을 종료하면 해당 커넥션의 Thread는 Thread cache로 돌아간다. 이때 Thread cache에 일정 개수의 Thread가 이미 존재하면 Thread를 종료시켜서 최대 Thread 개수를 유지한다. 이 최대 Thread 개수는 thread_cache_size 시스템 변수로 설정한다.
  • 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없으면 직접 디스크의 데이터나 인덱스 데이터로부터 읽어와서 처리한다.
  • MyISAM 테이블은 디스크 쓰기 작업까지 Foreground Thread에서 맡지만, InnoDB 테이블의 경우 버퍼와 캐시까지만 Foreground Thread가 처리하고, 나머지는 Background Thread가 처리한다.

Background Thread

  • 그래서 Background Thread가 버퍼에서 디스크 쓰기까지 담당해야 하는 InnoDB의 경우 이러한 작업들이 Background에서 일어난다:

    • Insert Buffer를 병합하는 Thread
    • 로그를 디스크로 기록하는 Thread (Log Thread)
    • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 Thread (Write Thread)
    • 데이터를 버퍼로 읽어 오는 Thread
    • Lock이나 Deadlock을 모니터링하는 Thread

  • MySQL 5.5부터 io_read_thread 와 io_write_thread 를 2개 이상 지정할 수 있으며, 시스템 변수에 innodb_write_io_threads 와 innodb_read_io_threads 로 설정한다.

  • InnoDB에서 읽는 작업은 Client Thread에서 처리하기 때문에 많이 필요없지만, Write Thread는 Background에서 많은 작업을 처리하기 때문에 Write Thread를 디스크에 적합하게 조율한다.

  • 쓰는 작업은 지연될 수 있지만, 읽는 작업은 지연될 수 없다. → 대부분의 DBMS는 쓰기 작업을 버퍼링해서 일괄 처리함. InnoDB 또한 그러나, MyISAM에서는 지원하지 않는다.

4.1.3 메모리 할당 및 사용 구조

메모리 공간은 크게 글로벌 메모리, 로컬 메모리로 구분한다.

글로벌 메모리 영역

글로벌 메모리는 MySQL 서버가 시작되면서 운영체제로부터 할당된다.

Client Thread 수와 무관하게 하나의 메모리 공간만 할당받는다.
필요하면 복수의 메모리 공간을 할당받을 수 있지만, Client Thread와는 무관하며, 개수와 상관없이 모든 Thread에 공유된다.

종류

  • 테이블 캐시
  • InnoDB 버퍼 풀
  • InnoDB Adaptive Hash Index
  • InnoDB Redo Log Buffer

로컬 메모리 영역 (=세션 메모리 영역, 클라이언트 메모리 영역)

MySQL 서버의 Client Thread가 쿼리를 처리하는 데 사용하는 메모리.

  • 각 Client Thread 별로 독립적으로 할당, 절대 공유되지 않는다.
  • Client Thread의 커넥션이 유지되는 동안 계속 할당되는 영역 (Connection Buffer, Result Buffer), 그리고 쿼리가 실행될 때만 할당됐다가 해제되는 공간 (Sort Buffer, Join Buffer)도 있다.
  • 각 쿼리의 용도 별로 필요할 때만 공간이 할당되고 필요하지 않을 시 아예 공간을 할당조차 하지 않는다. (e.g. Sort Buffer, Join Buffer)

종류

  • Sort Buffer
  • Join Buffer
  • Binary Log Cache
  • Network Buffer

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

MySQL에는 스토리지 엔진 뿐만 아닌 다양한 플러그인을 구현할 수 있으며, 또한 직접 커스터마이징한 스토리지 엔진도 구현할 수 있다.

위의 그림과 같이 스토리지 엔진은 데이터 읽기/쓰기에 전담하는데, 여기서 이 작업은 대부분 1건의 레코드 단위로 처리된다.

결국 스토리지 엔진의 교체는 뒤의 데이터 읽기/쓰기 부분만 변경되며, 나머지 부분은 대부분은 동일하다.

Docker mysql:latest (2023-04-01) 기준 지원되는 스토리지 엔진들은 다음과 같다;

Support에 YES, DEFAULT이면 사용 가능한 스토리지 엔진이며, NO인 스토리지 엔진의 경우 사용하려면 MySQL를 다시 빌드해야 한다.

이외에도 SHOW PLUGINS; 를 통해 스토리지 엔진을 포함한 다양 기능의 플러그인들을 볼 수 있다.

또한 몇가지 단점이 있는데, 이를 보완한것이 컴포넌트이다.

  • MySQL 서버와만 통신할 수 있음
  • MySQL 서버의 변수나 함수를 직접 호출해서 안전하지 않음 (캡슐화 X)
  • 플러그인 간 상호 의존 관계를 설정할 수 없어 초기화가 어려움

4.1.6 쿼리 실행 구조

쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다.

이 과정에서 쿼리 문법 오류를 발견하고 오류 메시지를 전달하게 된다.

토큰 : MySQL이 인식할 수 있는 최소 단위의 어휘나 기호

전처리기

만들어진 파서 트리를 기반으로 쿼리 문장의 구조적 문제점을 확인한다. 토큰을 테이블명, 칼럼명, 내장 함수 등으로 매핑해서 객체들의 존재 여부와 접근 가능 여부를 확인해서 걸러낸다.

옵티마이저

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리하도록 수행하는 역할을 담당하며, DBMS에서 옵티마이저의 역할은 매우 중요하고 영향력 또한 크다.

실행 엔진

옵티마이저의 계획대로 각 핸들러에게 요청하고 받은 결과들을 종합해서 다시 다른 핸들러들의 요청에 입력으로 넣어준다.

핸들러 (스토리지 엔진)

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

4.1.8 쿼리 캐시

쿼리 캐시는 SQL 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환한다.

장점

  • 캐싱을 한 데이터를 부를땐 성능이 비약적으로 향상됨.

단점

  • 변경된 테이블이 있으면 그 테이블의 캐시를 다 삭제함으로 인해 동시 처리에 심각한 성능 저하가 발생함.

그리고 MySQL 서버의 성능 개선 과정에서 많은 버그의 원인이 되어
결국 MySQL 8.0 이후로 쿼리 캐시는 MySQL에서 제거됐다.

4.1.9 스레드 풀

  • Thread 생성을 무제한적으로 할 수 있으면 Context Switching으로 인한 심한 오버헤드가 발생할 것이기에, 생성할 수 있는 Thread의 수를 제한하여 서버의 자원 소모를 최적화하는 것이 목표이다.
  • 그러나 스레드 풀로 눈에 띄는 성능 향상은 보기 힘들어 정말 필요한 경우가 아니면 손을 대지 않는다.
  • 스레드 풀은 MySQL Enterprise Edition에서 지원하나, Community Edition에서는 지원되지 않는다.
  • thread_pool_size 시스템 변수로 스레드 그룹의 개수를 조정할 수 있다.
  • 스레드 그룹의 모든 스레드가 일하고 있다면, thread_pool_stall_limit 시스템 변수에 정의된 시간 안에 작업을 끝내지 못하면 새로운 스레드를 스레드 그룹에 추가한다. 그러나 이는 풀의 최대 스레드 수를 넘지 않는다
  • 또한 Percona에서는 선순위, 후순위 큐를 이용해서 특정 트랜잭션/쿼리를 우선적으로 처리할 수 있도록 해준다. 이를 통해 트랜잭션의 Lock을 빨리 해제하고 Lock 경합을 낮춰서 성능을 높일 수 있다.

4.1.10 트랜잭션 지원 메타데이터

메타데이터 (데이터 딕셔너리) : 데이터베이스의 테이블 구조 정보, 스토어드 프로그램 등의 정보

~ 5.7버전

  • 테이블 구조를 파일 기반으로 관리한다.
  • 파일 기반 메타데이터는 생성 변경 작업이 트랜잭션 지원X, 비정상적 종료시 테이블에 문제가 발생한다.

MySQL 8.0

  • 시스템 테이블 (MySQL 서버가 작동하는 데 기본적으로 필요한 테이블의 집합)을 InnoDB에 저장한다.
    시스템 테이블, 데이터 딕셔너리 전부 mysql DB에 저장함. 이는 mysql.ibd라는 테이블 스페이스에 저장된다.
  • InnoDB 스토리지 엔진은 트랜잭션 기반으로 작동되기에 비정상적 종료에도 스키마가 완전한 변경, 아예 변경되지 않는 경우 두가지로만 귀결된다.
  • 만약 테이블이 InnoDB를 사용하지 않을 경우, 타 스토리지 엔진을 위해 MySQL에서는 SDI (Serialized Dictionary Information) 파일을 사용하여 보관한다.
profile
Yany Choi
생각하자
이전 포스트

Real MySQL 정독하기 (0) - 시작

다음 포스트

4. 아키텍처 (2) - 스토리지 엔진 아키텍처 (InnoDB)

0개의 댓글