• mysql
  • book

Real MySQL 8.0 Ch.4 — MySQL 엔진과 스레드 구조

MySQL 엔진과 스토리지 엔진의 역할, 스레드와 메모리 구조, 스레드 풀과 ProxySQL, 쿼리 실행 구조와 로그 파일을 정리한다.

Manx8분 읽기
시리즈 · Computer Science 서적1 / 8
  1. Real MySQL 8.0 Ch.4 — MySQL 엔진과 스레드 구조
  2. Real MySQL 8.0 Ch.4 — InnoDB 스토리지 엔진 아키텍처
  3. Real MySQL 8.0 Ch.5 — 트랜잭션과 잠금
  4. Real MySQL 8.0 Ch.6~7 — 데이터 압축과 암호화
  5. Real MySQL 8.0 Ch.8 — B-Tree 인덱스
  6. Real MySQL 8.0 Ch.8 — R-Tree, 전문 검색, 함수 기반, 멀티 밸류 인덱스
  7. Real MySQL 8.0 Ch.8 — 클러스터링 인덱스, 유니크 인덱스, 외래키
  8. Real MySQL 8.0 Ch.9 — 옵티마이저와 기본 데이터 처리

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 나뉜다. 사람에 비유하면 MySQL 엔진은 머리, 스토리지 엔진은 손발이다.

MySQL 엔진과 스토리지 엔진

MySQL 엔진은 요청된 SQL을 분석하고 최적화하는 두뇌 역할이다. 커넥션 핸들러, SQL 파서, 전처리기, 옵티마이저로 구성된다.

스토리지 엔진은 데이터를 디스크 스토리지에 저장하거나 읽어 오는 부분을 전담한다.

  • MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다. 테이블마다 엔진을 지정한다.
  • 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM)나 버퍼 풀(InnoDB) 같은 기능을 내장한다.
  • MySQL 엔진은 읽기·쓰기가 필요할 때 핸들러(Handler) API를 통해 스토리지 엔진에 요청한다.
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;

스레딩 구조

MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 동작하며, 스레드는 포그라운드와 백그라운드로 나뉜다.

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

  • 서버에 접속한 클라이언트 수만큼 존재하며, 사용자가 요청한 쿼리 문장을 처리한다.
  • 커넥션이 종료되면 스레드는 Thread Cache로 돌아간다.
  • Thread Cache에 이미 thread_cache_size만큼 스레드가 대기 중이면 캐시에 넣지 않고 종료시킨다.

백그라운드 스레드

InnoDB는 다음 작업을 백그라운드 스레드로 처리한다.

  • Insert Buffer 병합
  • 로그를 디스크로 기록
  • 버퍼 풀의 데이터를 디스크에 기록
  • 데이터를 버퍼로 읽어 오기
  • 잠금, 데드락 모니터링

5.5 버전부터는 쓰기 스레드와 읽기 스레드를 2개 이상 지정할 수 있다(innodb_write_io_threads, innodb_read_io_threads).

쓰기 지연

포그라운드 스레드는 데이터 버퍼나 캐시까지만 처리하고, 버퍼에서 디스크까지 기록하는 작업은 백그라운드 스레드가 맡는다. 덕분에 InnoDB는 쓰기 작업을 버퍼링해 일괄 처리할 수 있고, 쿼리는 데이터가 데이터 파일에 완전히 저장될 때까지 기다리지 않아도 된다.

반면 MyISAM은 디스크 쓰기까지 포그라운드 스레드가 처리하므로 이런 쓰기 지연을 쓸 수 없다.

메모리 구조

글로벌 메모리 영역

클라이언트 스레드 수와 무관하게 하나만 할당되어 모든 스레드가 공유한다.

  • 테이블 캐시
  • InnoDB 버퍼 풀
  • InnoDB 어댑티브 해시 인덱스
  • InnoDB 리두 로그 버퍼

로컬 메모리 영역(세션 메모리 영역)

클라이언트 스레드가 쿼리를 처리하는 데 사용하는 영역으로, 스레드 간에 공유되지 않는다.

  • 커넥션 버퍼, 네트워크 버퍼
  • 정렬 버퍼, 조인 버퍼
  • 바이너리 로그 캐시

정렬 버퍼나 조인 버퍼처럼 필요할 때만 할당되는 공간도 있다. 글로벌 영역에 비해 크기를 신경 쓰지 않고 설정하기 쉬운데, 커넥션마다 할당되므로 메모리 부족으로 서버가 멈출 수 있다. 적절한 크기로 설정하는 것이 중요하다.

스레드 캐시와 스레드 풀

Thread Cache

커넥션이 끊길 때마다 스레드를 없애고 새로 만드는 비용을 줄이기 위한 캐시다. Threads_created / Connections 값이 1% 이상이면 thread_cache_size를 늘려야 한다고 한다.

Thread Pool

전통적인 스레드 모델에서는 커넥션마다 포그라운드 스레드가 하나씩 할당된다. 스레드 풀에서는 커넥션과 스레드가 1:1이 아니고, 하나의 스레드가 여러 커넥션의 요청을 처리한다.

MySQL 커뮤니티 버전은 스레드 풀 없이 커넥션당 스레드 하나로 처리하고(Thread Cache 사용), 스레드 풀은 Enterprise 버전에서 제공한다. 책은 Percona Server의 스레드 풀 플러그인을 기준으로 설명한다.

스레드 풀의 목적은 요청을 처리하는 스레드 개수를 줄여, 동시 요청이 많더라도 CPU가 제한된 개수의 스레드 처리에만 집중하게 해 자원 소모를 줄이는 것이다.

  • 기본적으로 CPU 코어 개수만큼 스레드 그룹을 만든다.
  • 스레드 그룹이 이미 작업을 처리 중이면 thread_pool_oversubscribe에 설정된 개수만큼 추가로 받아들인다. 이 값이 너무 크면 스케줄링할 스레드가 많아져 비효율적이다.
  • 모든 스레드가 작업 중이면 새 스레드를 추가할지, 기존 작업의 완료를 기다릴지 판단한다. thread_pool_stall_limit(ms)만큼 기다려도 끝나지 않으면 스레드를 추가한다.
  • 전체 스레드 개수는 thread_pool_max_threads를 넘을 수 없다.

선순위 큐와 후순위 큐를 이용해 먼저 시작된 트랜잭션의 SQL을 우선 처리해 주기도 한다. 트랜잭션이 빨리 끝나면 잠금이 빨리 해제되고 경합이 줄어 전체 성능이 좋아진다.

요청이 유입된 순서와, 이미 시작된 트랜잭션의 쿼리를 앞으로 당겨 재배치한 처리 순서 비교

Thread Pool과 Thread Cache의 차이

  • Thread Pool의 스레드는 없어지지 않고 계속 재사용된다.
  • Thread Cache의 스레드는 개수가 넘치면 제거되고, 필요하면 새로 생성된다. 즉 언제든 없어질 수 있다.

ProxySQL

커뮤니티 버전에서는 결국 커넥션 수만큼 스레드가 만들어진다. 레플리케이션이나 샤딩으로 MySQL 서버가 여러 대가 되고 애플리케이션 서버까지 늘어나면 커넥션이 과도하게 많아진다.

MySQL 30대 × 애플리케이션 200대 × 서버별 커넥션 10개 = 60,000 커넥션

이런 상황에서 클라이언트와 MySQL 서버 사이에 ProxySQL을 두기도 한다.

  • 스레드 풀을 사용하고, 커넥션을 다중화(multiplexing)해 관리한다.
  • 쿼리 결과를 캐시해 빠르게 제공할 수 있다.
  • 장애 복구나 Slave를 Master로 승격하는 기능은 제공하지 않는다. 그래서 Orchestrator를 함께 사용한다.

플러그인 스토리지 엔진 모델과 컴포넌트

스토리지 엔진은 플러그인 형태여서, Handler API 규칙만 따르면 용도에 맞는 엔진을 직접 개발해 끼워 넣을 수 있다. 다만 플러그인 아키텍처에는 단점이 있다.

  • 플러그인은 MySQL 서버와만 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없다.
  • MySQL 서버의 변수나 함수를 직접 호출하므로 캡슐화가 되지 않는다.
  • 플러그인 간 상호 의존 관계를 설정할 수 없어 초기화가 어렵다.

MySQL 8.0부터는 이를 대체하기 위한 컴포넌트 아키텍처를 지원한다.

쿼리 실행 구조

구성 요소 역할
쿼리 파서 쿼리를 토큰으로 분리해 트리 구조를 만든다. 문법 오류는 이 단계에서 발견된다.
전처리기 파서 트리를 기반으로 구조적인 문제를 확인한다. 테이블·컬럼 이름을 매핑해 객체의 존재 여부와 접근 권한을 확인한다.
옵티마이저 쿼리를 가장 저렴한 비용으로 가장 빠르게 처리할 방법을 결정한다.
실행 엔진 만들어진 계획대로 핸들러에게 요청하고, 받은 결과를 또 다른 핸들러 요청의 입력으로 연결한다.
핸들러(스토리지 엔진) 실행 엔진의 요청에 따라 데이터를 디스크에 저장하고 읽어 온다.

회사에 비유하면 옵티마이저는 경영진, 실행 엔진은 중간 관리자, 핸들러는 실무자다. 쿼리 튜닝의 핵심은 옵티마이저가 더 나은 선택을 하도록 유도하는 것이다.

쿼리 캐시

SQL 실행 결과를 메모리에 캐시해 두고, 동일한 쿼리가 오면 즉시 결과를 반환하던 기능이다. 데이터 변경이 거의 없고 읽기만 하는 시스템에서는 매우 빨랐지만, 동시 처리 성능을 떨어뜨리는 문제 때문에 MySQL 8.0에서 완전히 제거됐다.

MySQL 로그 파일

에러 로그

설정 파일(my.cnf)의 log_error 파라미터에 정의된 경로에 생성된다. 따로 정의하지 않으면 데이터 디렉터리에 .err 확장자가 붙은 파일로 생성된다.

슬로우 쿼리 로그

long_query_time 시스템 변수에 설정한 시간 이상 걸린 쿼리를 모두 기록한다. 쿼리가 정상적으로 실행을 완료해야 기록된다.

  • Time: 쿼리가 종료된 시각
  • Query_time: 쿼리가 실행되는 데 걸린 전체 시간
  • Lock_time: 잠금 대기 시간

Lock_time은 MySQL 엔진 레벨에서 설정한 테이블 잠금의 대기 시간일 가능성이 높다. MySQL 엔진의 잠금과 스토리지 엔진의 잠금이 따로 관리되기 때문이다. 따라서 InnoDB 테이블에만 접근하는 쿼리라면 슬로우 쿼리 로그의 Lock_time 값은 튜닝에 도움이 되지 않는다.

참고