개발자와 DBA를 위한 real mysql(work in progress)

Architecture

Path Overview

  1. Mysql 엔진 클라이언트로부터 접속 및 sql 쿼리 요청을 받아 문장을 분석, 최적화한다. 데이터를 스토리지로부터 읽거나 써야 할 떄는 스토리지 엔진에게 요청한다. 이러한 요청을 Handler Request라고 한다. 여기에 사용되는 API는 Handler API라고 함.

  2. 스토리지 엔진 실제 데이터를 디바이스에 저장하거나 읽어오는 부분을 담당. MySQL서버에서 Mysql엔진은 하나이지만 스토리지 엔진은 여러 개 일 수 있다.

Threading

  1. 포그라운드 스레드(클라이언트 스레드) - mysql 서버에 접속한 클라이언트 만큼 존재하며, 클라이언트가 요청하는 쿼리 문장을 처리한다. MySQL에서 사용자 스레드와 포그라운드 스레드는 같은의미로 사용됨. 클라이언트가 서버에 접속하게 되면 그 서버는 클라이언트의 요청을 처리해 줄 스레드를 클라이언트에게 할당 해 준다.

  2. 백그라운더 스레드 - InnoDB의 경우 여러가지 작업이 백그라운드로 처리된다. 예를 들어, insert Buffer를 병합, 로그를 디스크로 기록, InnoDB의 버퍼 풀의 데이터를 디스크에 기록, 데이터를 버퍼로 읽기, 모니터링 등이 있다. 읽기의 경우 클라이언트 스레드에서 처리되기 때문에, 읽기 스레드가 많을 필요가 없지만, 쓰기 스레드는 많은 작업들을 백그라운드로 처리하기 때문에, 충분하게 설정 해 주는 것이 좋다.

sql 처리에서 읽기는 지연이 없지만, 쓰기는 배치 프로세싱을 할 수 있음. InnoDB에서는 INSERT와 UPDATE 그리고 DELETE 쿼리로 데이터가 변경되는 경우, 데이터가 디스크의 데이터 파일로 완정히 저장될 때 까지 기다리지 않아도 된다. 하지만, MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.

메모리 할당 및 사용 구조

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분된다. 글로벌 메모리 영역의 모든 메모리 공간은 MySQL서버가 시작되면서 OS로부터 할당된다.

  1. 글로벌 메모리 영역 - 클라이언트 스레드의 수와 무관하게 하나의(일반적으로) 메모리 공간으로 할당 된다. 모든 스레드에 의해 공유된다.

  2. 로컬 메모리 영역 - 세션 메모리 영역이라고도 표현하며, MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역, 클라이언트 메모리 영역, 또는 세션 메모리 영역이라고도 한다. 로컬 메모리 영역은 각 클라이언트 스레드 별로 독립적으로 할당되며, 절대 공유되어 사용되지 않는다.

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

대부분의 MySQL기능은 MySQL엔진에서 처리된다. I/O 작업만 스토리지 엔진에서 처리됨, 따라서 플러그인 처럼 서로다른 스토리지 엔진을 사용할 수 있다.

MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용하게 된다. MySQL엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 핸들러를 반드시 통해야 한다.

쿼리 실행 구조

Drawing

http://rrhh234cm.tistory.com/140

  1. 파서 - 문법 체크, 토큰분리
  2. 전처리기 - 객체의 존재 유무, 접근 권한 확인 등.
  3. 옵티마이저 - 두뇌, 가장 중요
  4. 실행 엔진 - 손과 발,
  5. 핸들러(스토리지 엔진) - I/O

replication

데이터베이스의 데이터가 대용량화 됨에 따라, 확장성을 위한 여러 기술 중 하나. 두 개 이상의 MySQL서버가 동일한 데이터를 담도록 실시간으로 동기화 하는 기술.

쿼리 캐시

쿼리의 결과를 쿼리 캐시에 담아 두고, 동일한 쿼리 요청이 왔을때 캐시에서 그 결과를 돌려준다.

  • 절차
    1. 요청된 쿼리 문장이 쿼리 캐시에 존재하는가(공백이나 개행문자 하나라도 같아야 한다)?
    2. 해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?
    3. 트랜잭션 내에서 실행된 쿼리 인 경우, 그 결과가 가시 범위 내의 트랜잭션 내에서 만들어진 결과 인가(InnoDB의 경우)
    4. 쿼리에 사용된 기능(내장 함수나 저장 함수, 예를 들어 랜덤이나 현재 날짜 관련 )이 캐시 되어도 동일한 결과를 보장 할 수 있는가?
    5. 캐시가 만들어지고 난 이후ㅠ 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?
    6. 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?
    7. 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용됐는가? 등등등

InnoDB 스토리지 엔진 아키텍쳐

  • InnoDB 특징
    1. 프라이머리 키에 의한 클러스터링
    2. 잠금이 필요없는 읽기
    3. foreign key 지원
    4. 자동 데드락 감지
    5. 자동화 된 장애 복구
    6. 오라클 아키텍쳐 적용

InnoDB 버퍼 풀

MySQL의 쓰기 작업(INSERT, DELETE등)은 파일의 이곳 저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시킴. 버퍼풀이 이러한 변경된 데이터를 모아서 처리하게 되면 랜덤한 디스크 잡업의 횟수를 줄일 수 있다.

InnoDB의 버퍼 풀은 데이터와 인덱스 모두 캐시하고 쓰기 버퍼링의 역할까지 모두 처리한다. 또한, 여러 백그라운드 작업의 기반이 되는 메모리 공간이다. 따라서 InnoDB의 버퍼 풀 크기를 충분하게 설정해야 한다.

InnoDB 버퍼 풀은 아직 디스크에 기록되지 않은 변경된 데이터를 가지고 있다. 이런 데이터를 가지고 있는 페이지를 더티 페이지라고 하는데, InnoDB에서는 이런 더티 페이지를 주기적으로 체크포인트 이벤트를 통해 Write Thread 가 필요한 만큼의 더티페이지를 디스크에 기록한다. 체크포인트가 발생한다고 해서 모든 더티페이지를 디스크에 기록하는 것은 아니다.

Insert Buffer

INSERT나 UPDATE시 데이터 파일을 변경하는 작업 뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트 해야하는 작업이 필요함. 이 작업은 랜덤한 디스크 읽기 이므로 오버헤드가 있음. 따라서, InnoDB에서는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하고, 디스크로부터 읽어와서 업데이트를 해야 한다면 임시 공간에 저장 해 두고 결과를 사용자에게 반환한다. 이때 이 임시 공간을 Insert Buffer라고 한다

Redo 로그, 로그 버퍼

MySQL에서 변경된 데이터를 버퍼링 해 두기 위해 InnoDB 버퍼 풀 과 같은 장치가 마련되어 있음, 이것만으로 ACID를 보장하기 어려움. 변경된 내용을 순차적으로 디스크에 기록하는 로그 파일을 가지고 있음. 이 로그를 Redo log라고 함.

데이터의 변경 작업이 매우 많은 DBMS서버의 경우에는 이 리두 로그의 기록 작업이 문제가 됨. 따라서 ACID를 보장하는 수준에서 버핑을 하고 이 버퍼링에 사용되는 공간이 로그버퍼임.

실행 계획

쿼리 실행 절차

  1. 사용자로부터 요청된 SQL 문장을 잘개 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
  2. SQL Parse tree를 만들어, 어떤 테이블로부터 읽고, 어떤 인덱스를 사용하여 테이블을 읽을지 정한다.
  3. 2.로부터 결정된 방법을 통해 스토리지 엔진으로부터 데이터를 가져온다.

1~2 단계는 MySQL 엔진에서 처리하며, 3단계는 MySQL엔진과 스토리지엔진 모두가 관여한다.