[MySQL] MySQL 아키텍처

성능 테스트를 진행하면서 MySQL의 내부 구조를 자세히 모르고 있다는 생각이 들었다. 특히 InnoDB 버퍼 풀이라는 공간은 버퍼와 캐시의 역할을 하는 것으로 알고있는데, 그렇다면 이 부분이 성능 측정에 영향을 줄 뿐더러, 쿼리 특성에 따라 적절히 조절한다면 성능 향상에 도움이 될 수도 있겠다라는 생각에서 공부를 시작했고 그 과정에서 알게된 것들을 앞으로 2~3개의 글에서 다뤄보고자 한다.  

 

---

 

☑️ MySQL 아키텍처

 

MySQL 아키텍처는 크게 MySQL엔진과 스토리지 엔진으로 이루어져 있다. 이 글에서 스토리지 엔진은 현재 MySQL의 기본 스토리지 엔진인 InnoDB를 기준으로 다룬다. MySQL 엔진은 클라이언트로부터의 요청, 즉 쿼리를 분석하고 최적화하는 역할을 하며, 스토리지 엔진은 직접 디스크에 접근하여 데이터를 읽고 쓰는 작업을 한다.

MySQL 아키텍처는 다음과 같은 구성으로 되어있다.

 

 

 

 

• 쿼리 파서: 클라이언트로부터의 쿼리의 기본 문법을 체크하고 토큰 단위로 분리해 파서 트리를 만들어낸다.
• 전처리기: 파서 트리를 기반으로 구조적인 문제점이 있는지 확인한다. 이 단계에서 테이블 이름이나 칼럼 이름과 같은 것들을 매핑해 해당 객체의 존재 여부와 접근 권한을 확인한다.
• 옵티마이저: 쿼리의 최적화된 실행계획을 세운다.
• 실행 엔진: 실행 계획대로 핸들러에게 요청하며, 요청에 대한 응답을 또 다른 핸들러의 입력으로 전달해주는 역할을 한다.
• 핸들러(스토리지 엔진): MySQL 엔진의 요청에 따라 데이터를 디스크에 저장하거나 불러오는 역할을 한다.

 

 

☑️ MySQL 스레드 구조

 

MySQL 커뮤니티 버전은 쓰레드 캐시로 동작하고, 엔터프라이즈 버전은 쓰레드 풀을 지원한다.

그렇다면 쓰레드 캐시와 쓰레드 풀은 무슨 차이가 있을까? 

 

💠 쓰레드 캐시 

• 클라이언트의 요청을 처리한 쓰레드는 쓰레드 캐시에 반납된다. 
• 쓰레드 캐시에 쓰레드 캐시 사이즈만큼의 쓰레드가 보관 중일 때 쓰레드가 반납되면, 해당 쓰레드는 삭제된다. 
• 쓰레드 캐시 사이즈가 10일 때, 쓰레드 캐시에 10개의 쓰레드가 보관중이면서 다른 10개의 쓰레드가 클라이언트의 요청을 처리하고 있을 수 있다. 

💠 쓰레드 풀

• 쓰레드 풀 사이즈는 MySQL 서버에서 사용할 수 있는 쓰레드 개수를 의미한다. (병렬 처리할 수 있는 쿼리의 개수)
• 모든 쓰레드가 일하고 있을 때, 새로운 클라이언트 요청이 들어오면 해당 요청은 이미 처리중인 요청이 끝날 때까지 기다려야한다.

쓰레드 풀을 사용할 때, 너무 많은 쓰레드를 사용하는 것은 좋지 않다. 한정된 CPU를 스케쥴링 해야하기 때문에 쓰레드가 많아지면, 컨텍스트 스위칭이 많이 발생하여 오히려 비용이 더 커질 수 있다. 

 

---

 

 

MySQL 스레드는 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.

 

💠 포그라운드 스레드

포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재한다.

InnoDB 스토리지 엔진의 포그라운드 스레드는 데이터를 데이터 버퍼와 캐시에서 읽어오며, 이 곳에 데이터가 존재하지 않을 시 직접 디스크의 데이터나 인덱스 파일에 접근하여 데이터를 읽어온다. 이 외에 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드에서 처리한다.

 

 

💠 백그라운드 스레드

• 로그를 디스크로 기록하는 스레드
• InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
• 데이터를 버퍼로 읽어오는 스레드 (클라이언트 쿼리에 의한 읽기가 아닌, Read Ahead와 같은 성능 향상을 위한 읽기)
• 인서트 버퍼를 병합하는 스레드
• 잠금이나 데드락을 모니터링하는 스레드

 

여기서 중요한 것은 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 하는 쓰기 스레드이다. 데이터를 읽는 작업은 주로 포그라운드 스레드에서 처리되기 때문에 백그라운드의 읽기 스레드는 많이 설정할 필요가 없지만 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크를 사용할 경우 일반적으로 2~4로 설정한다.

 

 

 

☑️ MySQL 메모리 구조

 

MySQL의 메모리 구조는 글로벌 메모리 영역과 로컬 메모리 영역으로 나눠진다.

 

💠 글로벌 메모리

MySQL 서버를 실행할 때 운영체제로부터 설정한 만큼 할당받 영역이며 모든 스레드로부터 공유된다.

 

💠 로컬(세션) 메모리

클라이언트 스레드가 쿼리를 처리하는데 쓰이는 영역이며 스레드 별로 독립적이기 때문에 공유되지 않는다.

버퍼 중 커넥션 버퍼와 결과 버퍼는 커넥션이 열려있는 동안 계속 할당된 상태로 남아있지만 조인 버퍼와 소트 버퍼는 쿼리를 실행하는 순간에만 할당했다가 해제된다.

 

 

 

☑️ InnoDB 스토리지 엔진 아키텍처

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

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

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

 

☑️ InnoDB 스토리지 엔진의 특성

 

 

1️⃣ 프라이머리 키에 의한 클러스터링

InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다. 즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며, 이로 인해 프라이머리 키에 의한 Range Scan은 상당히 빨리 처리 될 수 있다. 결과적으로 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정된다.

 

2️⃣ FK(외래 키) 지원

InnoDB에서 FK는 부모 테이블 / 자식 테이블 모두 해당 Column에 인덱스 생성이 필요하다. 

부모 테이블의 PK 변경 시 부모 테이블 / 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하는 경우가 있을 수 있기 때문에 여러 테이블로 Lock이 전파될 수 있고, 이에 따라 DB에서 데드락이 많이 발생할 수 있다. 

이를 위해 foreign_key_checks 옵션이 설정 가능하다.

 

3️⃣ 잠금이 필요없는 일관된 읽기(Non-locking consistent read)

InnoDB 스토리지 엔진은 MVCC를 이용해 락을 걸지 않고 읽기 작업을 수행한다. 락을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 락을 기다리지 않고 읽기 작업이 가능하다(SERIALIZABLE 격리 수준 제외)

 

4️⃣ 자동 데드락 감지

InnoDB는 락 대기 목록을 그래프 형태로 가지고 있다. 만약 데드락이 발생한다면 자동적으로 감지되고 감지된 데드락은 관련 트랜잭션 중에서 ROLLBACK이 가장 용이한 트랜잭션(가장 적은 UNDO 로그를 가진 트랜잭션)을 자동적으로 강제 종료한다.

데드락 감지 스레드는 잠금 목록을 검사할 때 상태가 변하지 않도록 잠금 대기 목록에 락을 걸어두고 검사한다.

 

 

☑️ InnoDB 버퍼 풀과 리두 로그

InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이한다. 버퍼 풀에 대해서는 다음 글에서 자세히 다룰 예정이다.