MySQL 아키텍처 (MySQL 엔진 구조, 스토리지 엔진 구조, 스레드 구조, 메모리 구조, 쿼리 실행 구조, Replication 구조)

MySQL 서버 = MySQL 엔진 + 스토리지 엔진

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 나눠볼 수 있다.

MySQL 엔진은 클라이언트로부터 오는 요청 처리(요청된 SQL 문장을 분석, 최적화, ...)를 담당하고, 스토리지 엔진은 실제 데이터를 디스크 스토리지에 저장하거나 조회하는 부분을 담당한다.

• MySQL 엔진
  • Connection Handler : 커넥션 및 쿼리 요청을 처리 담당
  • SQL 인터페이스 : DML, DDL, Procedure, View 등 SQL 인터페이스 제공 담당
  • SQL 파서(parser) : SQL문법 오류 탐지 및 SQL 쿼리 문장을 MySQL이 처리하기 좋은 토큰 단위로 나눠서 트리 형태로 파싱 하는 작업 담당
  • SQL 옵티마이저(optimizer) : 쿼리의 최적화된 실행 담당
  • 캐시와 버퍼 : 성능 향상을 위한 보조 저장소 기능 담당
• 스토리지 엔진
  • 스토리지 엔진은 MySQL 엔진과 플러그인 형태로 연동/분리 가능하고 핸들러 API(핸들러 요청)를 통해 스토리지 엔진에 읽기/쓰기 요청이 가능

MySQL 스레드 구조

MySQL 스레드 = foreground thread + background thread

• 포그라운드 스레드
  • 클라이언트/사용자가 요청한 쿼리 문장을 처리하는 스레드(고객 창구 역할)
  • 데이터 조회 스레드(데이터 버퍼나 캐시 또는 직접 디스크나 인덱스 파일로 접근하여 데이터를 가져오는 스레드)
• 백그라운드 스레드
  • Insert buffer를 병합하는 스레드
  • 로그 기록 스레드
  • InnoDB 버퍼 풀의 데이터를 디스크로 기록(Write)하는 스레드
  • 데이터를 버퍼로 읽어들이는 스레드
  • 잠금, 데드락을 모니터링하는 스레드
  • 모든 백그라운드 스레드를 관리하는 메인 스레드

포그라운드 스레드는 'thread_cache_size' 설정 값에 따라 일정 스레드 개수로 유지된다.

메모리 구조와 할당

MySQL의 메모리 구조는 크게 글로벌 메모리 영역과 로컬(세션) 메모리 영역으로 나눠볼 수 있다.

글로벌 영역은 많은 스레드에 의해 공유되는 영역이고 로컬 영역은 많은 스레드에 의해 공유되지 않는 독립적인 영역이다.

• 글로벌 메모리
  • MySQL 서버를 실행할 때 운영체제로부터 설정한 만큼 할당받는 영역
  • 모든 스레드로부터 공유되는 영역
• 로컬(세션) 메모리
  • 클라이언트 스레드가 쿼리를 처리하는데 쓰이는 영역
  • 스레드 별로 독립적이라 절대 공유되지 않는 영역
  • 쿼리의 용도 별로 할당이 될 때도 있고 아예 할당되지 않을 때도 있음

쿼리 실행 구조

1. 파서(Parser)가 들어온 쿼리 문장에 대해서 문법(Syntax) 확인을 하고 쿼리 문장을 적절한 단위로 분할하여 파서 트리(parser tree)를 만든다.
2. 전처리기(pre-processor)가 앞에서 만들어진 파서 트리를 보고 구조적으로 문제가 있는지 확인한다. (예를 들면 없는 테이블, 컬럼 등에 접근한다거나 테이블에 접근하려고 보니 권한이 없다거나 하는 등...)
3. 옵티마이저(Optimizer)가 요청받은 쿼리를 어떻게하면 가장 적은 비용으로 빠르게 즉, 최적으로 처리할지를 결정한다. 앞으로 개발자가 해야 할 일은 이 옵티마이저가 더 나은 결정(실행 계획 생성)을 할 수 있도록 쿼리를 작성하는 것이다.
4. 실행 엔진은 옵티마이저에 의해 결정된 실행 계획대로 핸들러에게 지시한다. (임시테이블 만들어라 → where 조건대로 레코드 읽어와라 → 읽은 레코드를 임시 테이블에 써라 → ...)
5. 핸들러(스토리지 엔진)는 실행 엔진이 내려준 지시대로 작업을 수행한다.

복제(Replication)

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

보통 읽기, 쓰기 모두 가능한 마스터 역할의 마스터 서버(Primary)와 읽기만 가능한 슬레이브 역할의 슬레이브 서버(Secondary)로 나뉜다.

마스터 서버에서만 쓰기가 가능하기에 마스터에 쓰인 데이터를 슬레이브에 "복제"하여 데이터를 동기화한다.

마스터 역할의 서버는 복제를 위해서 MySQL 바이너리 로그를 활성화해서 남겨야 한다.

바이너리 로그에는 DML(데이터를 조작하는 문장)과 DDL(스키마를 변경하는 문장)이 기록된다.

슬레이브 역할의 서버에서 마스터 역할의 서버로 동기화 요청이 온다면 "Binlog dump"라는 스레드가 동기화를 수행한다.

슬레이브 역할의 서버에서는 마스터 역할의 서버에 접속할 정보(IP, PORT, 계정)를 가지고 있으면 된다.

그리고 슬레이브 역할의 서버는 마스터 역할의 서버로 데이터 동기화 요청을 하고 받아온 내역을 릴레이 로그에 기록한다. (참고로 바이너리 로그와 릴레이 로그는 내용이 동일하다.)

🔒 복제 시 주의 사항

• 하나의 슬레이브는 하나의 마스터만 설정 가능
• 마스터와 슬레이브의 데이터 동기화를 위해 슬레이브는 읽기 전용으로 설정
  • 읽기 전용으로 설정하지 않으면 개발자나 사용자 실수로 슬레이브에 쓰기 요청을 해서 데이터가 기록될 위험이 있음
• 슬레이브 서버 장비와 마스터 서버의 장비 사양은 동일한 것으로 사용
• 복제가 불필요한 경우 바이너리 로그 중지 (바이너리 로그를 작성하는 일은 비용이 굉장히 큰 작업임)
• 바이너리 로그와 트랜잭션 격리 수준
  • 바이너리 로그 기록 방식 2가지
    • STATEMENT 방식 : 마스터에서 실행된 쿼리 자체를 기록
      • 쿼리 자체를 기록하기 때문에 복제할 때 네트워크로 전달되는 용량이 적어서 복제 성능 좋긴 하나, 트랜잭션 격리 수준을 REPEATABLE READ 수준 이상으로 해야 해서 레코드 간의 잠금 등 락에 영향을 끼친다.
    • ROW 방식 : 마스터에서 실행된 쿼리로 인해 변경된 레코드를 기록
      • 변경 사항이 많은 경우, 네트워크로 전달되어야 할 레코드 양이 많아져서 복제 성능이 안 좋긴 하나, 트랜잭션 격리 수준을 READ COMMITED 수준에서도 동작 가능하여 락에서 race-condition은 줄어든다.

쿼리 캐시

쿼리(SQL)를 캐시 하지 않고 쿼리 결과를 메모리에 캐시 한다.

쿼리 캐시는 간단하게 key, value 쌍의 맵(Map) 같은 데이터 구조로 되어있다.

MySQL에만 있는 기능이며 생각보다 효율적이지 않은 경우가 많기에 트레이드오프를 잘 기억해놔야 한다.

🏹 쿼리 캐시 확인 절차

1. 요청으로 들어온 문장이 쿼리 캐시에 존재하는가? (공백, 대소문자 등 완벽하게 일치하는지)
2. 사용자의 권한은 충분한가?
3. 트랜잭션 내에서 실행된 쿼리면 결과도 같은 가시 범위 내의 트랜잭션에서 만들어진 결과인가? (가시 범위 : 자신의 트랜잭션 아이디보다 작은 것(자신의 트랜잭션보다 이전에 실행된 트랜잭션)만 확인 가능한 조건)
4. 쿼리에 사용된 기능이 캐시 돼도 동일한 결과를 보장할 수 있는가? (CURRENT_DATE, SYSDATE, RAND 같이 호출 시점에 따라 달라지는 요소가 있는지?, 변수가 결과에 영향을 미치는지?)
5. 캐시가 생성된 이후 해당 데이터에 변경이 있었는지? (변경된 데이터는 캐시 의미가 없어진다. 그리고 지워진다. 캐시 용량을 넉넉히 잡으면 지우는데도 오버헤드가 있다.)
6. 쿼리에 의해 만들어진 결과가 너무 큰 데이터는 아닌지? (결과가 너무 크면 쿼리 결과가 캐시 공간을 혼자 다 차지하여 낭비를 일으킬 수 있다.)
7. 쿼리 캐시를 사용하지 못하게 하는 요소가 있는지?
   • 캐시 할 수 없는 요소
     • 임시 테이블에 대한 쿼리
     • 사용자 변수 사용한 쿼리
     • 컬럼(column)기반 권한 설정
     • LOCK IN SHARE MODE 힌트
     • FOR UPDATE 힌트
     • User Defined Function 사용
     • 독립적인 SELECT 문이 아닌 일부분의 서브 쿼리
     • Stored Procedure, Function, Trigger에서 사용한 쿼리
     • SQL_NO_CACHE 힌트

이 만큼 복잡한 쿼리 캐시 확인 절차(이 밖에도 더 있음)가 있기에 캐시 적중률이 떨어질 수 있고, 쿼리 결과가 너무 크면 캐시를 비우는데도 오버헤드가 걸리므로 전체적으로 보았을 때 과연 효율적인가에 대해서는 사용 환경을 잘 따져봐야 할 필요가 있다.

InnoDB 스토리지 엔진

MySQL이 제공하는 스토리지 엔진이 다양하지만 그중 유일하게 레코드 기반의 락(Lock)을 제공하며 이로 인해 높은 동시성 처리가 가능한 특징이 있고 안정적이다. 그래서 주로 쓰이는 스토리지 엔진이기 때문에 구조를 파악해야 한다.

구조는 크게 메모리 영역과 디스크(스토리지) 영역으로 나눌 수 있다.

• 메모리 영역
  • InnoDB 버퍼 풀 : 실제 데이터 블록(페이지)을 메모리에 적재하는 영역 + 인서트 버퍼 + 언두 레코드
  • 로그 버퍼 : 로그 스레드에 의해 로그 파일로 기록되기 전 버퍼링 하는 영역
• 디스크 영역
  • 시스템 테이블 스페이스
  • 사용자 테이블 스페이스
  • 리두(Redo) 로그

🥥 InnoDB 스토리지 엔진의 특징

• 테이블은 Primary Key 순서대로 디스크에 저장되며 그렇기 때문에 Range scan이 굉장히 빠르다.
• 실행계획 결정에서 다른 보조 인덱스에 비해 Primary Key가 선택될 확률이 높다.
• MVCC를 이용하여 락을 걸지 않고 읽기 수행
  • MVCC가 뭔지는 아래에서 참조!
• 외래 키 지원
  • 외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 대한 인덱스 생성이 필요하고 변경 시에는 반드시 부모 테이블과 자식 테이블의 데이터가 있는지 체크하는 작업이 필요하므로 잠금(Lock)이 전파되는 특징이 있다. Lock이 여러 곳에 전파되면서 데드락 발생을 시킬 수 있기 때문에 실무에서 제약을 걸지 않는 경우가 많다.
• 자동 데드락 감지 : 감지 시 변경된 레코드가 가장 작은 트랜잭션을 롤백해버려서 데드락을 풀어준다.
• 자동 장애 복구 : 완료하지 못한 트랜잭션이나 일부만 기록되어 손상된 데이터 페이지 등을 자동 복구한다.

InnoDB 버퍼 풀

디스크의 데이터 파일이나 인덱스를 메모리에 캐시 해 두는 공간이다. (실질적인 데이터 영역이므로 메모리의 대부분을 자치한다.)

쿼리로 인해 변경되어야 할 레코드들이 디스크 이곳저곳에 있는데 메모리에 모아서 한 번에 디스크 작업을 발생시키는 "쓰기 지연" 역할을 하며 더티 페이지(디스크에 아직 기록되지 않은 데이터를 가진 페이지)를 특정 조건에 주기적으로 체크하여 디스크에 쓰는 작업을 한다.

언두(Undo) 레코드(로그?)

UPDATE, DELETE 문과 같이 데이터를 변경하는 쿼리로 데이터를 변경했을 때 변경되기 이전 데이터를 보관하는 공간이다.

롤백 용도나 트랜잭션 격리 수준에 따라 대응하기 용도로 사용된다.

인서트 버퍼(Insert buffer)

INSERT, UPDATE 등 데이터 파일을 변경할 때는 데이터만이 아니라 연관된 인덱스를 변경하는 작업이 필요하다.

인덱스를 변경하는 작업은 랜덤 디스크를 읽는 작업이 필요하므로 자원 소모가 크다.

이를 최적화하기 위해 InnoDB가 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트하지만 변경해야 할 인덱스 페이지가 버퍼 풀에 없는 경우는 디스크로부터 읽어와서 버퍼 풀에 적재하고 인덱스를 업데이트를 해야 하는데 이때, 즉시 업데이트하지 않고 임시 공간에 저장해둔다. 이 임시 공간이 인서트 버퍼다.

리두(Redo) 로그

데이터 파일을 변경하는 것은 랜덤하게 디스크에 기록해야 하기 때문에 디스크를 상당히 바쁘게 하는 비용이 비싼 작업이다.

이 작업을 모아서 처리하여 성능을 향상하기 위해 InnoDB 버퍼 풀 같은 장치가 있다.

그러나 InnoDB 버퍼 풀만으로는 데이터베이스의 ACID를 보장할 수 없는데 이를 위해서 순차적으로 디스크에 기록하는 로그 파일을 가지고 있는데 이곳이 리두(Redo) 로그다.

너무 많은 양의 변경이 일어나게 되면 리두(Redo) 로그에 기록하는 것이 큰 문제가 되는데 이 부분을 보완하기 위해서 로그 버퍼도 있다.

🥥 데이터 베이스의 ACID

A : Atomic = 트랜잭션은 원자성 작업이어야 한다.(원자성 작업 = 트랜잭션으로 레코드가 변경된다면 전체가 성공 또는 실패여야 하는 작업)

C : Consistent = 일관성

I : Isolated = 격리성

D : Durable : 한번 저장된 데이터는 지속적으로 유지되어야 함

MVCC(Multi Version Concurrency Control)

목적은 Lock(잠금)을 사용하지 않는 일관된 읽기를 하는 데 있다.

하나의 레코드에 대해 여러 버전으로 관리하는 방식을 MVCC라 한다.

MySQL은 레코드를 여러 버전으로 관리하는 방법으로 트랜잭션의 커밋/롤백 여부와 상관없이 변경 이전 레코드의 데이터를 Undo 로그에 남겨두는 방법을 사용한다.

Undo 로그를 남겨서 여러 버전으로 관리하면 격리 수준에 따라 Lock(잠금)을 사용하지 않고 일관된 조회가 가능하다.

만약 격리 수준을 READ_UNCOMMITED로 설정했으면 InnoDB 버퍼 풀에 있는 최신 버전의 레코드를 조회하면 되고, READ_COMMITED 이상의 격리 수준으로 설정했으면 메모리 영역인 Undo 로그에 저장된 레코드 값으로 레코드를 조회하면 굳이 레코드의 Lock을 걸 필요가 없이 일관된 읽기가 가능해진다.

단, 트랜잭션 범위가 길어져서 undo 로그 영역에 이전 버전의 레코드들이 많이 쌓여 시스템 테이블 스페이스 공간이 많이 필요로 할 수 있기 때문에 트랜잭션 범위 최소화할 필요가 있다.

정리

1. MySQL 아키텍처는 크게 MySQL 엔진과 스토리지 엔진으로 나뉘며 각각은 SQL 처리를 위한 두뇌역할과 실질적인 데이터 저장 및 조회 역할을 담당한다.
2. MySQL 스레드 구조는 크게 Foreground Thread와 Background Thread로 나뉘며 각각은 클라이언트 요청을 처리/조회하는 역할과 그 외 로그나 데이터 저장/운영 등과 관련된 역할을 담당한다.
3. MySQL의 메모리 구조는 크게 글로벌 영역과 로컬(세션) 영역으로 나뉘며 각각은 많은 스레드가 공유해서 사용하는 공간인지 아닌지에 따라 구분된다.
4. 쿼리 실행 순서는 파서가 문법 체크하고 파서 트리를 만들고 전처리기가 테이블 존재 여부나 권한 등을 구조적 문제를 체크하고 문제가 없다면 옵티마이저에 의해 최적의 실행계획이 생성되고 이것을 핸들러 API를 요청하여 스토리지 엔진이 작업을 수행하도록 하는 순서다.
5. MySQL의 복제(Replication)는 데이터 이중화와 확장성을 갖게 해주는 기술로 데이터 읽기/쓰기가 모두 가능한 마스터 MySQL 서버와 읽기만 가능한 슬레이브 MySQL 서버로 구분할 수 있다. 마스터로 지정하는 방법은 바이너리 로그를 활성화하여 바이너리 로그가 기록되게 하는 것이고, 슬레이브로 지정하는 방법은 마스터 서버의 IP, PORT, 계정을 설정하는 것이다. 슬레이브에서 오는 동기화 요청에 대해 바이너리 로그를 통해 마스터는 처리가 가능하고, 슬레이브는 동기화한 내역을 릴레이 로그에 기록하여 데이터를 동기화한다.
6. MySQL만의 특징으로 쿼리 캐시라는 기능이 있고 쿼리 캐시는 쿼리 자체를 캐시 하는 것이 아닌 쿼리 결과를 캐시 한다. 쿼리 캐시 확인 절차가 까다로워 효율적인지 고민해야 한다.
7. 가장 많이 쓰이는 MySQL 스토리지 엔진인 InnoDB의 구조는 크게 메모리 영역과 디스크 영역으로 나뉜다. 메모리 영역에는 데이터와 인덱스를 적재하는 InnoDB 버퍼 풀, 변경 이전 레코드 값을 기록하여 롤백 및 트랜잭션 격리 수준을 보장하는 언두(Undo) 로그, 레코드 변경에 의해 인덱스를 변경해야 할 때, 인덱스 페이지가 없을 경우 바로 인덱스 페이지를 로드해서 인덱스를 업데이트하지 않고 자연스럽게 인덱스가 있는 페이지가 사용될 때까지 버퍼링하는 인서트 버퍼가 있다. 디스크 영역에는 시스템/사용자 테이블 스페이스와 데이터베이스의 ACID를 만족시키기 위한 리두(Redo) 로그가 있다.
8. MVCC(Multi Version Concurrency Control)는 잠금을 사용하지 않는 일관된 읽기를 하기 위한 기능으로 언두(Undo) 로그를 통해 변경되기 이전의 레코드 값을 여러 버전으로 나눠 관리하여 기능을 구현했다.

🐋 참고 도서 : 개발자와 DBA를 위한 Real MySQL . 이성욱 지음