MySQL 아키텍처

1. MySQL 전체 구조

 

1-1. MySQL 엔진

a. 커넥션 핸들러 : 클라이언트로부터의 접속 및 쿼리 요청 처리

b. SQL 파서 및 전처리기

c. SQL 옵티마이저 : 쿼리의 최적화된 실행에 도움

d. 성능 향상을 위한 MyISAM의 키 캐시나 InnoDB 같은 보조 저장소 기능

 

1-2. 스토리지 엔진

실제 데이터를 디스크에 저장하거나 디스크로부터 데이터를 읽어오는 부분

 

1-3. 핸들러 요청

MySQL 엔진 → 스토리지 엔진에 보내는 요청

 

1-4. 핸들러 API

핸들러 요청에서 사용되는 API, ‘SHOW GLOBAL STATUS LIKE 'Handler%’로 확인 가능

 

2. 스레드 구조

 

MySQL 서버는 Thread 기반으로 작동

2-1. Foreground Thread

• 캐시 된 Foreground Thread는 Thread Pool에 존재하는 Thread이다.
• MySQL 서버에 접속된 클라이언트 수만큼 존재한다. 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무이다. 작업이 종료되면 Thread Pool로 돌아가며 thread_cache_size 옵션에 의해 수가 조절된다.
• Foreground Thread는 데이터는 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 데이터가 없으면 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어온다. MyISAM 테이블의 경우는 쓰기 작업도 Foreground Thread가 처리하지만, InnoDB 테이블은 데이터 버퍼나 캐시까지만 Foreground thread가 처리하고 나머지 버퍼로부터 디스크까지 기록하는 작업은 Background thread가 처리한다.

 

2-2. Background Thread

• InnoDB는 많은 Background thread가 존재한다.
• Insert Buffer를 병합하는 Thread, 로그를 디스크로 기록하는 Thread, InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 Thread, 데이터를 버퍼로 읽어 들이는 Thread, 여러 가지 잠금이나 데드락을 모니터링하는 Thread 등이 있다. 이러한 모든 Thread를 총괄하는 메인 Thread도 존재한다.

일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링 해서 일괄 처리하는 기능이 탑재되어 있으며 InnoDB 또한 이러한 방식으로 처리한다.

 

3. 메모리 할당 및 사용 구조

 

3-1. 글로벌 메모리 영역

• MySQL 서버가 시작되면서 무조건 운영체제로부터 할당된다.
• Thread 수와 무관하게 일반적으로 하나의 메모리 공간이 할당되며 모든 Thread에 의해 공유된다.

 

3-2. 로컬 메모리 영역

• 클라이언트 Thread가 쿼리를 처리하는 데 사용하는 메모리 영역
• Thread별로 독립적으로 할당되며 절대 공유되어 사용하지 않는다.
• 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수 있다.
• 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간(커넥션 버퍼, Result 버퍼)도 있고, 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(정렬 버퍼, 조인 버퍼)도 있다.

 

4. 쿼리 실행 구조

 

4-1. 파서

• 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조를 만들어낸다. 문법적 오류는 이때 발견된다.

 

4-2. 전처리기

• 파서 트리를 바탕으로 쿼리 문장에 구조적인 문제점을 확인한다. 테이블 이름, 칼럼 이름 등의 객체 존재 여부나 접근권한을 체크한다.

 

4-3. 옵티마이저

• 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리하는 결정을 내린다.

 

4-4. 실행 엔진 (쿼리 실행기)

• 옵티마이저에서 내려온 계획대로 각 핸들러(스토리지 엔진)에게 요청해서 받은 결과를 또 다른 핸들러 요청으로 연결하는 역할을 수행한다.

 

4-5. 스토리지 엔진

• 실행엔진 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다.

 

5. 복제

 

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

 

5-1. 마스터

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

 

바이너리 로그란? : MySQL 서버에서 Create, Drop, Alter 같은 DDL과 Insert, Update, Delete 같은 DML을 통해 데이터베이스, 오브젝트, 데이터에 생성, 수정, 업데이트를 했을 시 그 변화된 이벤트를 기록하는 이진 파일이 있는데 이것을 바이너리 로그라고 한다.

 

5-2. 슬레이브

• 마스터 서버가 바이너리 로그를 가지고 있으면 슬레이브 서버는 릴레이 로그를 가지고 있다.
• 슬레이브 서버의 I/O Thread는 마스터 서버에 접속해 변경 내역을 요청하고, 받아 온 변경 내역을 릴레이 로그에 기록한다. 슬레이브 서버의 SQL Thread가 릴레이 로그에 기록된 변경 내역을 재실행함으로써 슬레이브의 데이터를 마스터와 동일한 상태로 유지한다.

 

5-3. 복제에 대한 특징

• 슬레이브는 읽기 전용으로 설정하는 것이 일반적이다.
• 슬레이브 서버는 마스터 서버가 다운된 경우 그에 대한 복구 대안으로 사용될 때도 많기 때문에 사양을 동일하게 맞추는 경우가 대부분이다.
• 바이너리 로그를 기록하는 작업은 AutoCommit이 활성화된 MySQL 서버에서 더 심각한 부하로 나타날 때가 많다. 특히 트랜잭션을 지원하지 않는 MyISAM 테이블은 항상 AutoCommit 모드로 작동하기 때문에 InnoDB 테이블보다 바이너리 로그를 기록하는 데 더 많은 자원을 사용하게 된다.
• 바이너리 로그는 어떤 내용이 기록되느냐에 따라 두 가지 방식이 있다.
  • STATEMENT 포맷 방식 : 마스터에서 실행되는 쿼리 방식을 저장하는 방식
    • 네트워크 트래픽을 많이 유발하지 않는다.
    • 정상적으로 작동하려면 REPEATABLE-READ 이상의 트랜잭션 격리 수준을 사용해야 하며, 그로 인해 InnoDB 테이블에서는 레코드 간의 간격을 잠그는 갭 락이나 넥스트 키 락이 필요해진다.
  • ROW 포맷 방식 : 마스터에서 실행된 쿼리에 의해 변경된 레코드 값을 기록하는 방식
    • 네트워크 트래픽을 많이 발생시킬 수 있지만 READ-COMMITTED 트랜잭션 격리 수준에서도 작동할 수 있으며 InnoDB 테이블에서 잠금의 경합은 줄어든다.

 

6. 스토리지 엔진 종류 및 특징

 

6-1. InnoDB

InnoDB는 트랜잭션을 처리하기 위해 고안됐는데 대부분의 경우 롤백되지 않고 완료되는 짧은 트랜잭션이 많은 경우를 처리하기 좋게 되어 있다.

InnoDB는 가장 많이 사용되는 스토리지 엔진으로 ACID 트랜잭션을 지원하는 대표적인 MySQL의 뛰어난 성능과 장애 복구 기능을 가진 엔진이다. 결제 정보와 같이 정보의 무결성을 가져야 하고 손실되면 안 되는 중요한 데이터를 필요로 할 때 사용한다.

InnoDB 테이블은 클러스터 인덱스 위에 구성되어 있으며 InnoDB의 인덱스 구조는 대부분의 MySQL 스토리지 엔진의 인덱스 구조와는 매우 상이하다. 그 결과 매우 신속한 기본키 조회가 가능하다.

그러나 보조 인덱스(기본키가 아닌 인덱스)는 만약 기본키가 크다면 다른 인덱스 또한 클 것이다. 테이블이 여러 인덱스를 가진다면 기본키가 작은 값을 갖게 해야 한다.

a. 프라이머리 키에 의한 클러스터링

모든 테이블은 기본적으로 기본 키를 기준으로 클러스터링 되어 저장된다. 즉 키값 순서대로 디스크에 저장이 된다는 뜻이며 이로 인해 프라이머리 키에 의한 Range 스캔은 상당히 빨리 처리될 수 있다.

b. 잠금이 필요 없는 일관된 읽기

MVCC(Multi Version Concurrency Control) 기술을 이용해 락을 걸지 않고 읽기 작업을 수행하여 락을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 락을 기다리지 않아도 된다.

c. 외래 키 지원

InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다. 외래 키는 여러 가지 제약사항 탓으로 인해 실무에서는 잘 사용하지 않기 때문에 그렇게 필수적이지 않지만 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다.

d. 자동 데드락 감지

그래프 기반의 데드락 체크 방식을 사용하기 때문에 데드락이 발생함과 동시에 바로 감지되고, 감지된 데드락은 관련 트랜잭션 중에서 ROLLBACK이 가장 용이한 트랜잭션을 자동적으로 강제 종료한다. 그래서 데드락 때문에 쿼리가 타임아웃 또는 슬로우 쿼리로 기록되는 경우는 많지 않다.

e. 자동화된 장애 복구

손실이나 장애로부터 데이터를 보호하기 위해 여러 메커니즘이 탑재되어 있으며 MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 트랜잭션 등에 대한 일련의 복구 작업이 자동으로 진행된다.

 

6-2. MyISAM 엔진

MySQL의 기본 스토리지 엔진인 MyISAM은 압축, 공간 함수(GIS) 등 여러 유용한 기능을 제공하지만 Table lock을 사용하여 트랜잭션이나 Row 수준의 잠금을 지원하지 않는다. MyISAM은 트랜잭션을 지원하지 않기 때문에 InnoDB보다 심플하고 기본적으로 빠르지만 동시성 제어가 어렵다는 단점이 있다. 그래서 Read 쿼리가 많은 DW(Data Warehouse) 환경에서 많이 사용된다.

MyISAM은 각 테이블을 데이터 파일과 인덱스 파일 두 곳에 주로 저장하는데, 이 두 파일의 확장자는 각각. MYD와. MYI다. MyISAM 테이블은 동적인 행과 정적인(고정된 길이)행을 모두 가질 수 있는데 MySQL이 테이블 정의를 토대로 하여 사용할 포맷을 결정한다.

a. Key Cache

InnoDB의 버퍼 풀과 비슷한 역할을 하는 것이 MyISAM의 Key Cache이다. 하지만 이름 그대로 Key Cache는 인덱스만 대상으로 작동하며 또한 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할만 한다.

b. 잠금과 동시성

MyISAM은 행 단위가 아니라 테이블 전체를 잠근다. 읽기 동작은 읽어야 할 모든 테이블에 대한 공유된 읽기 권한을, 쓰기 동작은 배타적 쓰기 잠금 권한을 얻는다. select 쿼리 실행 중에도 이 테이블에 새 행을 삽입할 수 있는데, 이 기능은 매우 중요하며 유용하게 쓰인다.

c. 수동 복구

테이블 오류를 조사하고 복구할 수 있다. 서버가 오프라인일 때에도 테이블을 조사하고 복구할 수 있다.

d. 지연된 키 쓰기

MyISAM테이블은 버퍼를 정리하거나 테이블을 닫을 때 인덱스 블록을 디스크로 플러시(flush)한다. 이러한 작업은 이용 빈도수가 높고 데이터 변경이 잦은 테이블의 성능을 높여 준다. 그러나 서버나 시스템에 충돌이 나면 인덱스가 손상되므로 이를 복구해야 한다. 서버를 다시 시작하기 전 myisamchk을 실행하는 스크립트나 자동 복구 옵션을 이용해 이 상황을 처리한다.

 

6-3. MEMORY 엔진

메모리에 데이터를 저장하는 엔진이며 트랜잭션을 지원하지 않고 table-level locking을 사용한다.

메모리를 사용하기 때문에 기본적으로 속도가 아주 빠른 편이지만 데이터를 잃어버릴 위험이 있다. 그렇기 때문에 중요하지 않지만 빠른 처리가 필요한 임시 테이블로 많이 사용하는 편이다. 메모리 테이블의 모든 데이터는 메모리 안에 저장되므로 쿼리가 디스크 입출력을 기다릴 필요가 없다.

 

6-4. Archive 엔진

아카이브 엔진은 아주 빠르게 INSERT 쿼리를 처리할 수 있는 엔진이다. MyISAM보다 디스크 입출력을 훨씬 적게 일으키는데, 데이터 쓰기를 버퍼링 하고 각 행이 삽입될 때마다 zlib으로 압축하기 때문이다.

인덱스를 지원하지 않으며 INSERT/REPLACE/SELECT 쿼리와 같이 데이터를 삽입하거나 읽는 것은 가능하지만 DELETE/UPDATE와 같이 데이터를 삭제하거나 수정할 수 없다. 트랜잭션을 지원하지 않고 row-level locking을 사용하며 주로 많은 양의 로그성 데이터를 저장하고 읽는데 주로 사용된다.