들어가기 전

슬로우 쿼리를 고쳐보고 싶은데 고치려면 어떻게 해야할지에 대한 지식이 전혀없다. 근데 슬로우 쿼리는 차치하고, 쿼리가 어떻게 동작하고 DB 내부는 어떻게 생긴지에 대해서도 공부해보지 않았다. 그래서 먼저 MySQL을 기준으로 내부 구조가 어떤지 세부적인 내용을 제외하고 간단하게 정리해보려고 한다.

MySQL의 전체적인 구조

MySQL 고유의 C API부터 JDBC, ODBC, .NET의 표준 드라이버 등을 제공한다. 이러한 드라이버는 다양한 언어(C/C++, 자바 등)가 MySQL 서버에서 쿼리를 사용할 수 있게 지원한다. MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분할 수 있다.

MySQL 엔진

요청된 SQL 문장을 분석, 최적화하는 등 DBMS의 두뇌 역할을 수행한다.

• 커넥션 핸들러
  • 클라이언트로부터의 접속 및 쿼리 요청을 처리
• SQL 파서
  • 쿼리를 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘, 기호)으로 분리해 트리 형태의 구조(파서 트리)로 만든다.
  • 쿼리의 문법 오류를 이 과정에서 발견된다.
• 전처리기
  • 파서 트리를 기반으로 쿼리 문장에 구조적인 문제가 있는지 확인한다.
  • 토큰을 테이블 이름, 칼럼 등에 매핑해 해당 객체의 존재 여부와 접근 권한 등을 확인한다.
• 옵티마이저
  • 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할 수 있도록 결정한다.
• 캐시, 버퍼
  • 성능 향상을 위해 MyISAM의 키 캐시나 InnoDB의 버퍼 풀과 같은 보조 저장소 기능이 포함된다.
• 실행 엔진
  • 쿼리에 기반하여 만들어진 실행 계획대로 각 핸들러(스토리지 엔진)에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
  • 예를 들어, GROUP BY를 처리하는 과정을 살펴보자.
    1. 실행 엔진은 핸들러에게 임시 테이블을 만들라고 요청
    2. 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
    3. 읽어온 레코드를 1번에서 준비한 임시 테이블로 저장하라고 핸들러에게 요청
    4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 요청
    5. 실행 결과를 사용자나 다른 모듈로 넘김

스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나, 디스크 스토리지로부터 데이터를 읽어온다.

• InnoDB, MyISAM 등
• MySQL 엔진은 하나이지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.
• 아래와 같이 TEST 테이블을 생성한다면, 해당 테이블에 CRUD 작업 수행시 InnoDB 스토리지 엔진이 처리하게 된다. CREATE TABLE TEST ~~ ENGINE = INNODB

핸들러 API

• 핸들러 요청
  • MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때, 각 스토리지 엔진에게 쓰기 또는 읽기를 요청하는 것.
• 핸들러 요청시 사용되는 API를 핸들러 API라고 한다.
  • InnoDB 스토리지 엔진 또한 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고받는다.
• SHOW GLOBAL STATUS LIKE 'Handler%'
  • 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지 확인할 수 있다.

스레딩 구조

MySQL 서버는 스레드 기반으로 작동하며, 크게 Foreground 스레드와 Background 스레드로 구분된다.

Foreground 스레드(클라이언트 스레드)

클라이언트가 요청하는 쿼리 문장을 처리하는 것이 주 역할이다.

• 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재한다.
• 클라이언트가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 되돌아간다.
• 일정 개수의 스레드를 스레드 캐시에 유지하려면 thread_cache_size 파라미터를 사용한다.
• 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져온다.
  • 버퍼나 캐시에 데이터가 없는 경우, 디스크나 인덱스 파일로부터 데이터를 읽어온다.
  • MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리한다.
  • InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 디스크까지 쓰는 작업은 백그라운드 스레드가 처리한다.

Background 스레드

특히 InnoDB는 인서트 버퍼를 병합하는 스레드, 로그를 디스크로 기록하는 스레드, 버퍼 풀의 데이터를 디스크에 기록하는 스레드 등을 통해 여러 가지 작업이 백그라운드로 처리된다.

• 쓰기 스레드
  • MySQL 5.0(윈도우 기준, 유닉스는 5.1)부터 innodb_write_io_threads 파라미터를 사용하여 쓰기 스레드 개수를 1개 이상 지정할 수 있다.
  • 일반적인 내장 디스크를 사용할 때는 2~4개, DAS나 SAN과 같은 스토리지를 사용할 때는 4개 이상으로 충분히 설정해 해당 스토리지 장비가 충분히 활용될 수 있게 하는 것이 좋다.

메모리 할당 및 사용 구조

크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분된다. 각 영역은 여러 스레드가 공유해서 사용하는지 아닌지에 따라 구분된다.

글로벌 메모리 영역

글로벌 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당된다. 일반적으로 하나의 메모리 공간만 할당되지만, 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있다. 클라이언트 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유된다.

• 구성 요소
  • 키 캐시(MyISAM) / 버퍼 풀(InnoDB) / 쿼리 캐시 / 바이너리 로그 버퍼 / 로그 버퍼 / 테이블 캐시
• 운영체제의 메모리 할당 방식은 매우 복잡하기 때문에 MySQL 서버가 사용하는 정확한 메모리 양을 측정하는 것은 쉽지 않다.
• 일단은 MySQL 파라미터로 설정해 둔 만큼 운영체제로부터 메모리를 할당받는다고 생각하자.

로컬(세션) 메모리 영역

클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다. 각 클라이언트 스레드별로 독립적으로 할당되기 때문에 절대 공유되지 않는다.

• 구성 요소
  • 커넥션 버퍼 / Result 버퍼 / Read 버퍼 / 조인 버퍼 / 랜덤 Read 버퍼 / Sort 버퍼
• 로컬 메모리 영역의 경우, 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차 하지 않을 수도 있다.
• 커넥션 버퍼, Result 버퍼는 커넥션이 열려 있는 동안 계속 할당된 상태로 남는다.
• Sort 버퍼, 조인 버퍼는 쿼리를 실행하는 순간에만 할당했다가 해제한다.

참고 자료

• 이성욱, 『개발자와 DBA를 위한 Real MySQL』, 위키북스(2012), 3장