MySQL 아케텍처

개발자와 DBA를 위한 Real MySQL 책을 보고 정리한 내용이다.

MySQL 전체 아키텍처

MySQL 서버는 MySQL 엔진, 스토리지 엔진으로 구분된다.

MySQL 엔진

• 클라이언트로부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 그리고 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.
• 성능 향상을 위해 MyISAM의 키 캐시나 InnoDB의 버퍼 풀과 같은 보조 저장소 기능이 포함돼 있다.
• 표준 SQL(ANSI SQL-92) 문법을 지원하므로 표준 문법에 따른 다른 DBMS와 호환되어 실행될 수 있다.

스토리지 엔진

• 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행
• 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분

MySQL 스레딩 구조

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

포그라운드 스레드

• 포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재
• 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리
• 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread pool)로 돌아간다. 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다.
• 포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와 작업을 처리한다.
• MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리
• InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.

백그라운드 스레드

• MyISAM의 경우 별로 해당 사항이 없는 부분
• InnoDB는 여러 가지 작업이 백그라운드로 처리된다.
  • 인서트 버퍼(insert buffer)를 병합하는 스레드
  • 로그를 디스크로 기록하는 스레드
  • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
  • 데이터를 버퍼로 읽어들이는 스레드
  • 기타 여러 가지 잠금이나 데드락을 모니터링하는 스레드
  • 이러한 모든 스레드를 총괄하는 메인 스레드도 있다.
• 모두 중요한 역할을 하지만 그중 가장 중요한 것은 로그 스레드(Log thread)와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드(Write thread)일 것이다.
  • 쓰기 스레드는 윈도우용 MySQL 5.0에서부터 1개 이상을 설정할 수 있었지만 리눅스나 유닉스 계열 MySQL에서는 5.1버전부터 쓰기 스레드의 개수를 1개 이상으로 지정할 수 있게 되었다.

InnoDB는 쓰기 작업을 버퍼링 해서 일괄 처리하는 기능이 있지만, MyISAM은 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계돼 있다.

메모리 할당 및 사용 구조

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.

글로벌 메모리 영역

글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 무조건 운영체제로부터 할당 된다. 운영체제에 따라 다르지만 요청된 메모리 공간을 100%할당해 줄 수도 있고, 그 공간만큼 예약해두고 필요할 때 조금씩 할당해주는 경우도 있다.

일반적으로 클라이언트 스레드의 수와 무관하게 일반적으로는 하나의 메모리 공간만 할당된다. 단, 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있지만 클라이언트의 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유된다.

로컬 메모리 영역

세션 메모리 영역이라고도 표현하며, MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다. 대표적으로 커넥션 버퍼와 정렬 버퍼 등이 있다.

클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터의 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다. 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 로컬 메모리 영역을 세션 메모리 영역이라고도 한다.

로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다. 일반적으로 글로벌 메모리 영역의 크기는 주의해서 설정하지만 소트 버퍼와 같은 로컬 메모리 영역은 크게 신경 쓰지 않고 설정하는데, 최악의 경우에는 MySQL 서버가 메모리 부족으로 멈춰 버릴 수도 있으므로 적절한 메모리 공간을 설정하는 것이 중요하다. 로컬 메모리 공간의 또 한가지 중요한 특징은 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지않을 수도 있다는 점이다.

로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고(커넥션 버퍼, 결과 버퍼) 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼, 조인 버퍼)도 있다.

글로벌 메모리 영역과 로컬 메모리 영역의 차이는 MySQL 서버 내에 존재하는 많은 스레드가 공유해 사용하는 공간인지 아닌지에 따라 구분된다.

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

MySQL 에서 쿼리가 실행되는 과정

순서	처리 영역
1. SQL 파서	MySQL 엔진
2. SQL 옵티마이저	MySQL 엔진
3. SQL 실행기	MySQL 엔진
4. 데이터 읽기/쓰기	스토리지 엔진
5. 디스크 스토리지	디스크 스토리지

MySQL을 사용하다보면 핸들러(Handler)라는 단어를 접하게되는데 이는 MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 객체를 사용한다.

여기서 중요한 내용은 "하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다." 는 점이다.

스토리지 엔진에서 설명하고자 하는 점은 스토리지 엔진의 개념을 설명하기 위한 것도 있지만 각 단위 작업을 누가 처리하고 "MySQL 엔진 영역"과 "스토리지 엔진 영역"의 차이를 설명하는 데 목적이 있다.

MySQL 서버에서 지원되는 스토리지 엔진 확인 명령어

mysql> SHOW ENGINES;

플그러인 확인 명령어

mysql> SHOW PLUGINS;

쿼리 실행 구조

순서	역할
1. 파서	사용자 요청으로 들어온 쿼리문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업. 쿼리문장의 기본 문법 오류는 이 과정에서 발견
2. 전처리기	파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인. 각 토큰을 테이블 이름, 칼럼 이름, 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근권한 등을 확인. 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.
3. 옵티마이저	사용자의 요청으로 들어온 쿼리 문장을 어떻게(CBO, RBO) 가장 빠르게 처리할지 결정하는 역할을 담당
4. 실행 엔진	실행 엔진은 옵티마이저에서 만들어진 계획대로 각 핸들러에게 요청해 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행
5. 핸들러(스토리지 엔진)	데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당

복제 (Replication)

복제는 2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간으로 동기화하는 기술이다.

마스터

• MySQL의 바이너리 로그가 활성화되면 마스터가 될 수 있다.
• 마스터 서버에서 실행되는 DML, DDL 가운데 데이터의 구조나 내용을 변경하는 모든 쿼리 문장은 바이너리 로그에 기록한다.
• 슬레이브 서버에서 변경 내역을 요청하면 마스터 장비는 그 바이너리 로그를 읽어 슬레이브로 넘긴다.

슬레이브

• 데이터(바이너리 로그)를 받아 올 마스터 장비의 정보를 가지고 있는 경우 슬레이브가 된다.
• 마스터 서버가 바이너리 로그를 가지고 있다면 슬레이브 서버는 릴레이 로그를 가지고 있다.
• 데이터의 일관성을 위해 슬레이브 서버는 읽기 전용(read_only)으로 설정한다.
• 슬레이브 서버의 I/O 스레드는 마스터 서버에 접속해 변경 내역을 요청하고, 받아 온 변경 내역을 릴레이 로그에 기록한 후 슬레이브 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행해 동일한 데이터 상태로 유지한다.

주의점

• 슬레이브는 하나의 마스터만 설정 가능
• 마스터와 슬레이브의 데이터 동기화를 위해 슬레이브는 읽기 전용으로 설정
• 슬레이브 서버용 장비는 마스터와 동일한 사양이 적합
• 복제가 필요한 경우에는 바이너리 로그 중지
• 바이너리 로그와 트랜잭션 격리 수준
  • 바이너리 로그 파일은 어떤 내용이 기록되느냐에 따라 STATEMENT 포맷 방식과 ROW 포맷 방식
  • STATEMENT 포맷 방식
    • 바이너리 로그 파일에 마스터에서 실행되는 쿼리 문장을 기록
  • ROW 포맷 방식
    • 마스터에서 실행된 쿼리에 의해 변경된 레코드 값을 기록

쿼리 캐시

쿼리 캐시는 타 DBMS에는 없는 독특한 기능 중 하나로 적절한 설정으로 성능 향상 효과를 얻을 수 있다. 여러 가지 복잡한 처리 절차와 꽤 큰 비용을 들여 실행된 결과를 쿼리 캐시에 담아두고, 동일한 쿼리 요청이 왔을 때 간단하게 쿼리 캐시에서 찾아서 바로 결과를 내려 줄 수 있기 때문에 기대 이상의 효과를 거둘 수 있다.

데이터베이스에서 쿼리를 처리할 때는 상당히 많은 처리 절차가 있다. 쿼리 캐시의 결과를 내려 보내주기 전 다음과 같은 확인 절차를 거쳐야 한다.

1. 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?
2. 해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?
3. 트랜잭션 내에서 실행된 쿼리인 경우, 그 결과가 가시 범위 내의 트랜잭션에서 만들어진 결과인가?
4. 쿼리에 사용된 기능이 캐시돼도 동일한 결과를 보장할 수 있는가?
   1. CURRENT_DATE, SYSDATE, RAND 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?
   2. 프리페어 스테이트먼트의 경우 변수가 결과에 영향을 미치지 않는가?
5. 캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않는가?
6. 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?
7. 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용됐는가?

InnoDB 스토리지 엔진 아키텍처

InnoDB는 MySQL에서 사용할수 있는 스토리지 엔진 중에서 거의 유일하게 레코드 기반의 잠금을 제공하고 있어, 높은 동시성 처리, 안정적, 높은 성능을 가지고 있다.

InnoDB 스토리지 엔진 특성

• 프라이머리 키에 의한 클러스터링
  • 프라이머리 키 값의 순서대로 디스크에 저장
• 잠금이 필요 없는 일관된 읽기(Non-locking consistent read)
  • MVCC(Multi Version Concurrency Control)라는 기술을 이용해 락을 걸지 않고 읽기 작업을 수행
• 외래 키 지원
• 자동 데드락 감지
  • 그래프 기반의 데드락 체크 방식을 사용하기 때문에 데드락이 발생함과 동시에 바로 감지되고, 감지된 데드락은 관련 트랜잭션 중 ROLLBACK이 가장 용이한 트랜잭션(레코드를 가장 적게 변경한 트랜잭션)을 자동적으로 강제 종료한다.
• 자동화된 장애 복구
  • InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러가지 매커니즘이 탑재돼 있다.
• 오라클의 아키텍처 사용

 

InnoDB 버퍼 풀

디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할

MyISAM 키 캐시가 인덱스의 캐시만을 주로 처리하는 데 비해 InnoDB의 버퍼 풀은 데이터와 인덱스 모두 캐시하고 쓰기 버퍼링의 역할까지 모두 처리

물리 메모리의 50~80% 수준에서 버퍼 풀의 메모리 크기 결정

언두(Undo) 로그

UPDATE, DELETE로 데이터 변경 시 변경되기 전 데이터를 보관하는 곳이다.

용도

• 트랜잭션의 롤백 대비용
• 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는데 사용

인서트 버퍼(Insert Buffer)

인서트 버퍼는 INSERT, UPDATE시 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 그렇지 않고 디스크로부터 읽어와 업데이트해야한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 사용자에게 결과를 반환하는 형태

사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 인서트 버퍼를 사용할 수 없다.

리두(Redo) 로그 및 로그 버퍼

쿼리 문장으로 데이터를 변경하고 커밋하면 DBMS는 데이터의 ACID를 보장하기 위해 즉시 변경된 데이터의 내용을 데이터 파일로 기록해야 한다. 이러한 데이터 파일의 변경 작업은 순차적으로 많은 데이터를 한꺼번에 변경하는 것이 아니고 랜덤하게 디스크에 기록해야 하기 때문에 디스크를 상당히 바쁘게 만드는 작업이다. 이러한 부하를 줄이기 위해 대부분의 DBMS에는 변경된 데이터를 버퍼링해 두기 위해 InnoDB 버퍼 풀과 같은 장치가 포함돼 있다. 하지만 이 장치만으로는 ACID를 보장할 수 없는데 이르 위해 변경된 내용을 수나적으로 디스크에 기록하는 로그 파일(리두 로그)을 가지고 있다.

리두 로그로 인해 DBMS 데이터는 버퍼링을 통해 한꺼번에 디스크에 변경된 내요을 처리할 수 있고 그로 인해 상당한 성능 향상을 기대할 수 있게 됐다. 하지만 변경 작업이 많은 DBMS 서버의 경우에는 이 리두 로그의 기록 작업이 큰 문제가 되는데, 이런 문제를 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링하게 된다. 이러한 리두 로그 버퍼링에 사용되는 공간이 로그 버퍼다.