RealMySQL 4장 MySQL 아키텍쳐(1)

[seokhwan-an]

4장 아키텍처

MySQL의 전체 구조

MySQL 엔진

MySQL 엔진은 요청된 SQL 문장을 분석하거나 최적화와 같이 DBMS의 두뇌에 해당하는 처리를 수행한다.

• 클라이언트로의 접속을 관리하는 커넥션 핸들러
• 쿼리 요청을 처리하는 SQL 파서 및 전처리기
• 쿼리의 최적화된 실행을 위한 옵티마이저

스토리지 엔진

• 스토리지 엔진은 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 것을 담당한다.
• MySQL 엔진의 경우 하나지만 스토리지 엔진의 경우는 여러 개를 동시에 사용이 가능하다.
• 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM)나 버퍼 풀(InnoDB)과 같은 기능을 내장하고 있다.

핸들러 API

• MySQL에서 쿼리를 실행하는 과정에서 데이터를 읽거나 쓰는 요청이 스토리지 엔진으로 보내지는데 이 요청을 핸들러 요청이라고 한다.
• InnoDB 스토리지 엔진 역시 이 핸들러 요청을 통해 MySQL 엔진과 데이터를 주고받는다.

MySQL 스레딩 구조

• MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 동작한다.
• foreground 스레드와 background 스레드로 구성이 되어 있다.

foreground thread(client thread)

• 최소 MySQL 서버에 접속된 클라이언트 수만큼 존재한다.(사용자 connection 수만큼 존재한다.)
• 주로 사용자의 요청 쿼리문을 처리한다.
• connection이 종료되면 thread cache로 돌아가게 되는데 일정 수 이상의 thread가 존재하면 thread 자체를 종료시킨다.
• 데이터를 MySQL의 데이터 버퍼나 캐시로 부터 가져오며 이에 존재하지 않는 경우 파일로부터 읽어온다.
• innoDB테이블은 데이터버퍼나 캐시까지만 foreground thread가 처리하며 나머지 버퍼로부터의 disk 기록은 백그라운드 스레드가 처리한다.

background thread(InnoDB)

• 인서트 버퍼를 병합하는 thread
• 로그를 디스크로 기록하는 스레드
• innoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
• 데이터를 버퍼로 읽어 오는 스레드
• 잠금이나 데드락을 모니터링하는 스레드

이와 같이 다양한 thread 중에서도 데이터를 디스크에 쓰고 읽는 thread가 가장 중요하다. 데이터를 읽은 작업은 주로 client Thread에서 처리되며 쓰기 thread의 경우에는 background thread로 처리하기 때문에 여러개의 thread 개수를 가지는 것이 좋다.

InnoDB에서 쓰기 작업은 지연되어 처리가 가능하여 버퍼에서 디스크로의 변화가 적용되는 과정에서 다음 요청이 이를 기다릴 필요가 없지만 MyISAM의 경우 쓰기 작업까지 모두 client Thread에서 처리하기 때문에 쓰기 요청이 모두 마무리 되어야 다음 요청이 처리가 가능하다.

메모리 할당 및 사용구조

• 메모리 공간은 글로벌 메모리 영역과 로컬 메모리 영역으로 구분된다.

글로벌 메모리 영역

• MySQL 서버가 시작되면서 운영체제로부터 할당되는 메모리로 주로 1개의 공간이 할당되며 필요에 따라 2개 이상의 공간이 할당될 수 있다.
• 모든 쓰레드에 의해 공유되는 자원이다.
• 테이블 캐시
• InnoDB 버퍼 풀
• InnoDB 어댑티브 해시 인덱스
• InnoDB 리두 로그 버퍼

💡innoDB 어댑티브 해시 인덱스
innoDB 어뎁티브 해시 인덱스는 주로 메모리 중심의 작업에 효과적이며, 자주 조회되는 열을 더 빠르게 검색할 수 있게 설계가 되어 있다. 이 인덱스 역시 테이블마다 관리가 된다.
주로 데이터를 조회할 때 가장 먼저 이용되는 인덱스이다. 여기는 모든 데이터가 존재하는 것이 아닌 자주 조회하는 데이터가 담겨져 있다.

로컬 메모리 영역

• 세션 메모리 영역이라고도 하며 client thread가 쿼리를 처리하는데 사용하는 메모리 영역이다.
• client thread가 사용하며 client thread간에 독립적으로 할당되며 공유되어 사용되지 않는다.
• 커넥션이 열려있는 동안 유지되는 공간(커넥션 버퍼나 결과 버퍼)이 있는 반면 쿼리를 실행하는 도중에만 공간이 할당되고 없어지는 메모리 영역(조인 버퍼 및 정렬 버퍼)이 있다.
• 정렬 버퍼
• 조인 버퍼
• 바이너리 로그 캐시
• 네트워크 버퍼

쿼리 실행 구조

1. 쿼리 파서

사용자의 요청으로 들어온 쿼리를 토큰으로 분리하여 트리 구조로 만들어 내는 작업을 의미한다. 이과정에서 쿼리문의 기본 문법 오류를 잡아내고 이를 사용자에게 오류메시지로 전달한다.

예시

SELECT id, name, age FROM member WHERE id = 1;

위의 쿼리문에서 SELECT, id, name, age, FROM, member, WHERE, id, = ,1의 토큰이 결정되며 아래와 같이 트리구조로 만든다.

1. 전처리기

쿼리 파서에서 만들어진 토큰 기반의 트리를 통해 구조적인 문제를 확인한다.(테이블이 존재하는지 혹은 테이블 내에 column이 존재하는지, 객체에 접근이 가능한지) 즉, 실제로 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰을 걸러내는 작업을 하는 것이다.

1. 옵티마이저

옵티마이저는 사용자의 요청 쿼리를 가장 저렴한 비용으로 처리하는 방법을 결정하는 역할이다. MySQL 엔진의 두뇌라고 볼 수 있다.

1. 실행 엔진

옵티마이저에서 생성한 실행 계획을 실행하는 역할로 실행 계획에 맞게 핸들러에게 요청을 보내는 중간 다리 역할을 하는 엔진이다.

쿼리 캐시

• SQL의 실행 결과를 메모리에 캐시하고, 동일한 요청에 대해서 빠른 응답을 하는데 도움을 준다.
• 그러나 테이블의 값이 변화하게 되면 이와 연관된 캐시를 모두 지워야 했고 이는 오히려 성능저하를 유발한다.
• 현재는 이 기능은 MySQL 8.0부터는 완전하게 삭제가 되었다.
• 지하철 최단 노선을 보여주는 서비스에서는 적합할 수 있는 기능이라고 생각한다.(읽기 비율이 쓰기 비율에 비해서 압도적으로 많은 서비스)

스레드 풀

스레드 풀의 목적은 사용자의 요청을 처리하는 스레드 개수를 줄여서 MySQL 서버의 cpu가 제한된 개수의 스레드 처리에만 집중하게 하여 자원 소모를 줄이는 것이다.

• MySQL 서버가 처리해야 요청이 발생하면 이를 스레드 풀로 이관한다. (thread_pool_oversubscribe에 정의된 개수만큼 추가로 작업을 받는다.)
• 스레드 그룹에 모든 스레드가 일을 처리하고 있다면 새로운 작업이 들어왔을 때 새로운 스레드를 생성해야할 지 아니면 기존의 스레드가 작업이 끝나면 그 후에 작업을 할당해야할 지 결정을 해야한다.
  • thread_pool_stall_limit 에 정의된 밀리초만큼 작업을 쓰레드가 처리하지 못하면 새로운 스레드를 생성한다.
  • 스레드 개수는 thread_pool_max_thread 를 넘어설 수 없다.
• 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있다.
  • 먼저 실행된 트랜잭션을 먼저 처리함으로써 그 트랜잭션이 가지고 있던 잠금을 빨리 해제하여 잠금 경합을 낮출 수 있다. 이를 통해 전체적인 처리 성능을 향상 시킬 수 있다.

InnoDB 스토리지 엔진 아키텍처

• innoDB 스토리지 엔진은 레코드 기반의 잠금을 제공하여 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다. (MyISAM의 경우는 테이블 기반의 잠금)

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

• innoDB의 모든 테이블은 기본적으로 pk를 기준으로 클러스터링되어 저장된다.(pk 순서대로 디스크에 저장된다)
• 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
• innoDB의 경우 pk가 클러스터링 인덱스이기 때문에 데이터 접근이 매우 빠르게 처리 된다.

💡클러스터링 인덱스
데이터베이스 관리 시스템에서 사용되는 데이터 구조 중 하나로 테이블 내의 데이터를 물리적으로 정렬하는 방식이다.

클러스터링 인덱스를 사용하면 테이블의 데이터를 디스크 상에서 연속적인 블록에 저장하여 특정 범위 내의 데이터에 대한 검색 성능이 향상될 수 있습니다. 하지만 테이블 하나당 하나의 클러스터링 인덱스가 존재할 수 있으며 정렬 작업 때문에 삽입, 갱신, 삭제 연산의 성능이 떨어진다.

특징

• 한 테이블당 하나의 클러스터링 인덱스만 가능하다.
• 테이블의 데이터는 클러스터링 키(인덱스의 정렬 기준)에 따라 물리적으로 정렬되어 저장된다.
• 클러스터링 인덱스는 테이블의 물리적 구조에 직접적으로 영향을 미치므로, 클러스터링 인덱스의 변경은 테이블의 데이터 재정렬을 필요로 한다.
• 주로 범위 검색이나 정렬된 결과를 필요로 하는 쿼리에 적합하다.

외래 키 지원

• MyISAM과 Memory 테이블에서는 지원하지 않지만 InnoDB에서 외래 키를 지원한다.
• foreign_key_checks 시스템 변수를 off로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다. 단, 다시 외래 키 체크를 키려면 부모 자식간의 관계를 데이터가 잘 유지하고 있어야 한다.

MVCC

• MVCC의 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 것이다.(undo 로그를 이용해서 목표달성)

예시: 데이터가 생성되고 변경되는 경우

• 먼저 쓰기 데이터가 들어오면 innoD 버퍼풀과 디스크에 데이터가 들어간다.
• 이 데이터를 수정하는 요청이 들어오면 현재 정보는 undo로그로 옮겨지고 버퍼풀에는 변경사항이 적용된다.
  • 이 때 read uncommit 레벨에서 조회요청이 들어오면 버퍼풀의 내용을 전달하며 그 상위 레벨에서 요청이 들어오면 undo 로그에 기록된 정보를 반환한다.

잠금 없는 일관된 읽기

두명의 사용자가 하나의 자원에 대해 접근을 할 때 한 사용자는 데이터를 수정하고 나머지 한 사용자는 데이터를 조회한다고 하자

이 때 데이터가 수정중이 있더라도 undo를 통해서 조회하는 사용자도 데이터에 접근이 가능하다. 즉, 잠금없이 데이터를 조회가 가능하다.

자동 데드락 감지

• innoDB는 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리하고 있다.
• 교착상태에 빠진 트랜잭션들이 있다면 undo로그 양이 가장 적은 트랜잭션을 롤백한다.
• innodb_deadlock_detect 시스템 변수로 데드락 감지를 할지 말지를 결정하고 innodb_lock_wait_timeout을 활성해 데드락 상황에서 일정 시간이 지나면 자동으로 요청을 실패하고 에러 메세지를 반환한다.

자동화된 장애 복구

• innoDB 데이터 파일은 기본적으로 MySQL서버가 시작될 때 항상 자동 복구를 수행한다.
• innodb_force_recovery 시스템 변수(1~6)를 설정해 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 할 수 있다.
  • 1(SRV_FORCE_IGNORE_CORRUPT): 테이블 스페이스의 데이터나 인덱스 페이지에 손상된 문제이며 주로 에로 로그 파일에 ‘Database page corruption on disk or a failed’ 메시지가 출력된다.
  • 2(SRV_FORCE_NO_BACKGROUND): undo 로그를 삭제하는 과정에서 발새하는 문제이다.
  • 3(SRV_FORCE_NO_TRX_UNDO)
    : undo 로그의 내용으로 트랜잭션 롤백이 실패한 문제이다.
  • 4(SRV_FORCE_NO_IBUF_MERGE): Insert, Update, Delete으로 인한 인덱스 변경 작업 상황에서 즉시 변경사항이 반영될 수도 있지만 인서트 버퍼에 저장해두고 나중에 처리가 될 수 있다. 이 때 인서트 버퍼가 손상된 문제이다.
  • 5(SRV_FORCE_NO_UNDO_LOG_SCAN): 트랜잭션 중 서버가 종료되면 서버 재시작시 undo 로그를 통해 롤백처리가 되어야 하는데 이때 undo로그를 사용하지 못하는 문제이다.
  • 6(SRV_FORCE_NO_LOG_REDO): redo 로그 파일이 손상된 문제이다.
• 바이너리 로그가 있다면 InnoDB의 복구 방법을 이용하는 것보다 풀 백업과 바이러니 로그로 복구하는 것이 데이터 손실이 더 적을 수 있다.

InnoDB 버퍼 풀

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

버퍼 풀의 크기 설정

• 기본적으로 전체 커넥션 수와 커넥션이 읽고 쓰는 테이블의 개수에 따라서 결정이 된다.
• MySQL 5.7 버전 부터 버퍼 풀의 크기를 동적으로 처리가 가능하다
• 크기는 가능하면 작게 설정을 해서 점점 키워나가는 것이 최적의 방법이다. (innodb_buffer_pool_size에서 킄기를 설정히 가능하다.)
  • 128MB 단위로 크키를 늘렸다 줄였다가 가능하다.
• 버퍼 풀 전체를 관리하는 잠금으로 인해 내부 잠금 경합이 많이 유발했는데 이를 줄이기 위해 버퍼 풀을 여러개로 나누어서 관리한다.(일반적으로 5GB당 하나의 버퍼 풀 인스턴스로 관리하는 것이 좋다.)

버퍼 풀의 구조

• 버퍼 풀은 페이지 크키의 조각으로 나누어서 관리한다. (LRU 리스트, 플러시 리스트, 프리 리스트 3개의 자료 구조를 관리한다)
  • 프리 리스트: 실제 사용자 데이터로 채워지지 않고 비어있는 페이지 목록으로 사용자의 쿼리가 디스크 데이터 페이지를 읽어와야 하는 경우 사용된다.
  • LRU 리스트: 두개의 공간으로 분류가 가능한데 Old 서브리스트와 New 서브리스트로 분류된다. LRU 리스트의 목적은 디스크로 부터 읽어온 데이터를 최대한 오랫동안 보관하여 디스크 읽기를 최소화하는 것이다.
  • 플러시 리스트: 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)를 관리하는 리스트이다. InnoDB는 redo 로그와 데이터 페이지에 데이터의 변경사항을 모두 정리하지만 이는 항상 보장되지 못하여 체크포인트를 발생시켜 동기화를 해준다.

버퍼 풀과 리두 로그

• 버퍼 풀은 데이터 캐시와 쓰기 버퍼링이라는 두가지 용도가 있다.
• 서버 메모리에서 허용하는 만큼 크기를 설정하면 데이터 캐시에 대한 성능은 기본적으로 향상된다.
• 버퍼 풀은 기본적으로 디스크에서 읽은 상태로 데이터가 변화되지 않은 페이지인 클린페이지와 데이터 변경작업이 발생한 더티 페이지를 가지고 있다.
• 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용된다. 이 때 로그가 쌓일 때 마다 LSN(Log Sequence Number)가 증가하게 된다.
• 체크포인트의 LSN을 통해 그 보다 작은 LSN을 가진 리두 로그와 그 리두 로그와 연관된 더티 페이지는 디스크에 반영한다.

버퍼 풀 플러시

• 버퍼 풀의 더티 페이지의 내용을 디스크에 동기화하기 위해서는 플러시 리스트 플러시 하는 것과 LRU리스트 플러시하는 것이 백그라운드로 실행한다.

플러시 리스트 플러시

• 리두 로그의 공간을 확보하기 위해서는 리두 로그와 연관된 버퍼 풀의 더티 페이지가 먼저 디스크에 동기화가 되어야 한다.
• innodb_page_cleaners : 버퍼 풀 인스턴스 개수를 넘어갈 수 없으며 더티 페이지를 디스크로 동기화하는 스레드이다.
• innodb_max_dirty_pages_pct: 버퍼 풀 내 데이터 페이지가 차지하는 비율을 설정할 수 있는 시스템 변수로 기본값으로 유지하는 것이 좋다.
• innodb_max_dirty_pages_pct_lwm: 디스크 쓰기 폭발을 막기위해 설정하는 시스템 변수로 기본값은 10% 수준인데, 상황에 알맞게 디스크 동기화를 적절하게 설정하는 것이 중요하다.
• 기본적으로는 어댑티브 플러시를 이용하는 것이고 리두 로그의 증가 속도를 분석해서 적절한 수준의 더티페이지가 유지 될 수 있도록 디스크 쓰기를 시행한다.

LRU 리스트 플러시

• 사용빈도가 낮은 페이지들을 제거하여 새로운 페이지들을 읽어올 수 있는 공간을 만들기 위해 LRU 리스트 플러시 함수를 사용한다.
• 스캔을 하면서 더티페이지는 디스크에 동기화가 되고, 클린페이지는 즉시 프리 리스트로 페이지를 옮긴다.

버퍼 풀 상태 백업 및 복구

• 버퍼 풀은 쿼리 성능이 큰 영향을 미친다.
  • 자주 요청 되는 쿼리를 셧다운 후 시도하면 처음에는 성능이 좋지 않게 나타난다. 그 이유는 모든 정보를 디스크에서 가져와야 하기 때문이다.
• 워밍업이 중요해 예전에는 주요 테이블과 인덱스에 풀 스캔을 한 번씩 실행을 했다면 최근에는 버퍼 풀을 백업하고 그 정보를 덤프 및 적재하는 방안이 도입되었다.
  • 실제 정보를 가진다기 보다는 메타정보를 가지고 백업을 해두어 파일의 크기가 작고 백업의 속도가 빠르다.
  • 백업에 비해 복구의 경우 시간이 오래걸리며 급히 서비스를 시작해야한다면 버퍼 풀 복구를 멈추는 것을 권장한다.
  • 이는 자동으로 서버가 셧다운 되기 직전에 버퍼 풀의 백업을 실행하고 MySQL 서버가 시작하면 자동으로 백업된 버퍼 풀의 상태를 복구할 수 있는 기능을 제공하며 이를 활성화 하려면 MySQL 설정 파일에 innodb_buffer_pool_dump_at_shutdown과 innodb_buffer_pool_load_at_startup 설정을 추가 하면된다.

[JoungMinJu]

1. MySQL 엔진 아키텍처

(1) 전체 구조

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분할 수 있다.

<1> MySQL 엔진

DBMS의 두뇌에 해당하는 처리를 수행한다.

클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.

<2> 스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나 읽어오는 부분을 전담한다.

MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다. (테이블이 사용할 스토리지 엔진을 지정할 수도 있다.)

<3> 핸들러 API

MySQL 엔진에서 스토리지 엔진에 요청을 할 때, 이 요청을 핸들러 요청이라 한다. 그리고 이때 사용되는 API가 바로 핸들러API이다.

(2) MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동한다. 이때 스레드는 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.

<1> 포그라운드 스레드(클라이언트 스레드)

이는 최소한 MySQL 서버에 접속한 클라이언트의 수만큼 존재한다.
주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 되돌아간다. 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있다면 스레드 캐시에 넣지 않고! 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다.

포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.

MySQL에서 "사용자 스레드"와 "포그라운드 스레드"는 똑같은 의미로 사용된다.

<2> 백그라운드 스레드

InnoDB에서 백그라운드 스레드가 하는 일 중 가장 중요한 것이 바로 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드이다.

(3) 메모리 할당 및 사용 구조

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.
글로벌 메모리 영역과 로컬 메모리 영역은 MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 여부에 따라 구분된다.

<1> 글로벌 메모리 영역

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

<2> 로컬 메모리 영역

세션 메모리 영역이라고도 한다.
MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다. 클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터의 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다.
로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다. 또한 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우엔 MySQL이 메모리 공간을 할당조차도 하지 않을 수도 있다는 특징이 있다.

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

MySQL의 독특한 구조 중 대표적인 것이 플러그인 모델이다.
스토리지 엔진, 검색어 파서.. 등이 플로그인으로 구현되어 제공된다.
실제로 수많은 사용자의 다양한 요건을 기초로 MySQL엔 다양한 스토리지 엔진이 존재한다.

(5) 컴포넌트

MySQL 8.0부터는 기존의 플러그인 아키텍처를 대체하기 위해서 컴포넌트 아키텍처가 지원된다.

왜냐하면 플러그인은 아래와 같은 단점이 존재하기 때문이다.

• 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없다.
• 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않다(캡슐화 되지 않는다)
• 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어렵다.

(6) 쿼리의 실행 구조

<1> 쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다.
이 과정에서 쿼리 문장의 기본 문법 오류가 발견된다.

<2> 전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.

<3> 옵티마이저

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당한다. (중요하고 영향 범위도 무지 넓다.)

<4> 실행 엔진

옵티마이저가 두뇌라면, 실행 엔진과 핸들러는 손과 발에 비유할 수 있다.

옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 해보자.

1. 실행 엔진이 핸들러에게 임시 테이블 만들라고 요청함
2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청함.
3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청.
4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김

즉, 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.

<5> 핸들러(스토리지 엔진)

핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다. 핸들러는 결국 스토리지 엔진을 의미하는 것

(7) 복제

다른 장에서 설명 예정 ~..

(8) 쿼리 캐시

빠른 응답을 필요로하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당했다.
SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 반환하면 되기 때문에 매우 빠른 성능을 보였다.
하지만, 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제해야 했다. 이는 심각한 동시 처리 성능 저하를 유발한다. 또한 MySQL 서버가 발전하면서 성능이 개선되는 과정에서 쿼리 캐시는 계속된 동시 처리 성능 저하와 많은 버그의 원인이 되기도 했다.
결국 MySQL 8.0에서 쿼리 캐시는 완전히 제거됐다.

(9) 스레드 풀

스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다.
근데 실제 서비스에서 스레드 풀이 눈에 띄는 성능 향상을 보여준 경우는 드물었다. 또한 스레드 풀은 동시에 실행 중인 스레드들을 CPU가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능이기 때문에 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우엔 쿼리 처리가 더 느려지는 사례도 발생할 수 있다는 점에 주의하자.

일반적으로는 CPU 코어의 개수와 스레드 풀 사이즈를 맞추는 것이 CPU 프로세서 친화도를 높이는데 좋다.

(10) 트랜잭션 지원 메타데이터

MySQL 서버는 5.7 버전까지 테이블의 구조를 FRM 파일에 저장하고, 일부 스토어드 프로그램 또한 파일 기반으로 관리했다. 그러니까 테이블의 생성 또는 변경 도중 서버가 비정상적으로 종료되면 일관되지 않는 상태로 남는 문제가 발생했다.

MySQL8.0부턴 이를 해결하기 위해 테이블 구조 정보나 스토어드 프로그램의 코드 관련 정보들을 모두 InnoDB의 테이블에 저장하도록 했다. MySQL 서버가 동작하는데 기본적으로 필요한 테이블을 묶어서 시스템 테이블이라고 하는데, 이런 애들이 mysql DB에 저장하고 있다. mysql DB는 통째로 mysql.ibd라는 이름의 테이블스페이스에 저장된다(얘는 다른 .ibd 파일과 함께 특별히 주의해야 한다.)

MySQL 8.0 버전부터 데이터 딕셔너리와 시스템 테이블이 모두 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장되도록 개선되면서 이제 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료된다고 하더라도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리된다.

InnoDB 스토리지 엔진 아키텍처

위에서는 MySQL 엔진의 전체적인 구조를 살펴봤다면, 이번 절에서는 MySQL의 스토리지 엔진 가운데 가장 많이 사용되는 InnoDB 스토리지 엔진을 간단히 살펴보자.

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

(1) 프라이머리 키에 의한 클러스터링

InnoDB의 모든 테이블은 프라이머리 키를 기준으로 클러스터링되어 저장된다. 즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이다. 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리의 키 값을 논리적인 주소로 사용한다.

프라이머리 키가 클러스터링 인덱스이기 땜누에 프라이머리 키를 이요한 "레인지 스캔"은 상당히 빨리 처리될 수 있다.

InnoDB 스토리지 엔진과는 달리 MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다. 그래서 MyISAM 테이블에서는 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.(걍 프라이머리 키는 unique 제약을 가진 세컨더리 인덱스일 뿐) 또한 MyISAM 테이블의 프라이머리 키를 포함한 모든 인덱스는 물리적인 레코드의 주소값(ROWID)을 가진다.

(2) 외래 키 지원

InnoDB 스토리지 엔진 레벨에서 지원하는 기능이다.
InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래 키의 존재에 주의하는 것이 좋다.

(3) MVCC(Multi Version Concurrency Control)

레코드 레베르이 트랜잭션을 지원하는 DBMS가 제공하는 기능.
잠금을 사용하지 않는 일관된 읽기 제공에 목적이 있다.

InnoDB는 Undo log를 이용해 이 기능을 구현한다.
여기서 multi version은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미이다.

(4) 잠금 없는 일관된 읽기(Non-Locking Consistent Read)

InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다. 잠금을 걸지 않기 때문에 읽기 작언은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 읽기 작업이 가능하다.

(5) 자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다.
InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션 들을 찾아서 그 중 하나를 강제 종료한다. 이때 어느 트랜잭션을 먼저 강제 종료할 것인지를 판단하는 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다. (서버의 부하가 덜할 것이기 때문에)

일반적인 작업에서는 데드락 감지 스레드가 트랜잭션의 잠금 목록을 검사해서 데드락을 찾아내는 작업은 크게 부담되지 않는데, 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려진다.
이러한 문제를 해결하기 위해 시스템 변수를 활용하여 제어할 수 있다. innodb_deadlock_detect를 OFF로 설정하면 데드락 감지 스레드는 작동하지 않게 된다. 이게 작동하지 않으면 InnoDB 스토리지 엔진 내부에서 두 개 이상의 트랜잭션이 상대방이 가진 잠금을 요구하는 상황이 발생해도 누가 중재를 하지 않기 때문에 무한 대기 현상이 발생할 것이다.
하지만 innodb_lock_wait_timeout 시스템 변수를 활성화하면 이러한 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메세지를 반환하게 된다.

(6) 자동화된 장애 복구

InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러 가지 메커니즘이 탑재되어 있다. 이를 활용해서 MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다.
InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다. 이 단계에서 자동으로 복구될 수 없는 손상이 있다면 MySQL 서버는 종료돼 버린다. (innodb_force_recovery 시스템 변수로 설정 가능)
MySQL 서버가 기동되고 InnoDB 테이블이 인식된다면 mysqldump 를 이용해 데이터를 가능한만큼 백업하고 그 데이터로 MySQL 서버의 DB와 테이블을 다시 생성하는 것이 좋다.

(7) InnoDB 버퍼 풀

스토리지 엔진에서 가장 핵심적인 부분. 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간. 버퍼 풀이 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다.

<1> 버퍼 풀의 크기 설정

OS와 각 클라이언트 스레드가 사용할 메모리를 충분히 고려해서 설정해야 한다.
가능하면 InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 봐 가면서 증가시키는 방법이 최적이다.
버퍼 풀의 크기 변경은 크리티컬한 변경이므로 가능하면 MySQL 서버가 한가한 시점을 골라서 진행하는 것이 좋다.
버퍼 풀을 여러 개로 분리해서 관리할 수 있다. 이때 각 버퍼 풀을 버퍼 풀 인스턴스라고 표현한다.

<2> 버퍼 풀의 구조

InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다.
버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 크기 LRU 리스트와 플러시 리스트, 그리고 프리 리스트라는 세 개의 자료 구조를 관리한다.

프리 리스트는 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록.

LRU 리스트는 엄밀하게 보면 LRU와 MRU 리스트가 결합된 형태이다. Old 서브 리스트 영역은 LRU에 해당하고 New 서브리스트 영역은 MRU 정도로 이해하면 된다. LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화 하는 것이다.
플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지의 변경 시점 기준의 ㅔ이지 목록을 관리한다. 데이터가 변경되면 InnoDB는 변경 내용을 "리두 로그"에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다. 그래서 리두 로그의 각 엔트리는특정 데이터 페이지와 연결된다. 하지만 리두 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는 것을 항상 보장하지는 않는다. 때로는 그 반대의 경우도 발생할 수 있는데, InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화 하게 된다.

<3> 버퍼 풀과 리두 로그

둘은 매우 밀접한 관계를 맺고 있다.
InnoDB의 버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지와 변경된 데이터를 가진 더티 페이지를 가지고 있다.
InnoDB 스토리지 엔진에서 리두 로그는 한 개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용한다. 즉, 데이터 변경이 계속 발생하면 리두 로그 파일에 기록됐던 "로그 엔트리"는 어느 순간 새로운 로그 엔트리로 덮어 쓰인다. 그래서 InnoDB 스토리지 엔진은 "전체 리두 로그 파일"에서 "재사용 가능한 공간"과 "당장 재사용 불가능한 공간"을 구분해서 관리해야한다. 이때 재사용 불가능한 공간을 활성 리두 로그라고 한다.
리두 로그 파일의 공간은 계속 순환되어 재사용되지만 기록될때마다 로그 포지션은 증가한 값을 갖게 되는데 이를 LSN이라 한다. InnoDB 스토리지 엔진은 주기적으로 "체크포인트 이벤트"를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화하는데, 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 된다. 하지만 활성 리두 공간의 마지막은 계속해서 증가하기 때문에 체크ㅗ인트와 무관하다. 그리고 가장 최근 체크포인트의 LSN와 마지막 리두 로그 엔트리의 LSN 차이를 체크 포인트 에이지(=활성 리두 로그 공간의 크기(라고 한다.

InnoDB 버퍼 풀의 더티 페이지는 특정 리두 로그 엔트리와 관계를 가지고, 체크포인트가 발생하면 체크포인트 LSN보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화 되어야 한다.

<4> 버퍼 풀 플러시

InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화 하기 위해 두 가지의 플래시 기능을 백그라운드로 실행한다.

플러시 리스트 플러시
InnoDB 스토리지 엔진은 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야한다. 그런데 이게 지워지려면 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화되어야 한다. 이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행한다.

LRU 리스트 플러시
InnoDB 스토리지 엔진은 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 하는데, 이를 위해 LRU 리스트 플러시 함수가 사용된다.

<5> 버퍼 풀 상태 백업 및 복구

InnoDB 스토리지 엔진은 MySQL 서버가 셧다운 되기 직전에 버퍼 풀의 백업을 실행하고, MySQL 서버가 시작되면 자동으로 백업된 버퍼 풀의 상태를 복구하 ㄹ수 있는 기능을 제공한다.

<6> 버퍼 풀의 적재 내용 확인

innodb_cached_indexes 테이블을 활용하면 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재되어 있는지 확인할 수 있다.

[somefood]

아키텍처

4.1 MySQL 엔진 아키텍처

MySQL 엔진

• 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룸
• 표준 SQL(ANSI SQL) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있음

스토리지 엔진

• 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 역할
• MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있음 (MyISAM - 캐시, InnoDB - 버퍼 풀)

핸들러 API

• MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때 각 스토리지 엔진에 쓰기 또는 읽기 요청함. 이런 요청을 핸들러(Handler)라 한다.

4.1.2 MySQL 스레딩 구조

• MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분할 수 있음

SELECT thread_id, name, type, processlist_user, processlist_host FROM performance_schema.threads ORDER BY type, thread_id;

1. 스프링 thread pool로 인한 연결
   2. 스프링 종료 후 connection 종료 후
   

포그라운드 스레드 (클라이언트 스레드)

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

백그라운드 스레드

• InnoDB에서 다음과 같은 작업이 백그라운드로 처리됨
  • 인서트 버퍼(Insert Buffer)를 병합하는 스레드
  • 로그를 디스크로 기록하는 스레드
  • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
  • 데이터를 버퍼로 읽어 오는 스레드
  • 잠금이나 데드락을 모니터링하는 스레드

메모리 할당 및 사용 구조

• MySQL에 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분 됨
• 글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당 됨
• 글로벌 메모리 영역과 메모리 영역은 MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 여부에 따라 구분됨
• 글로벌 메모리 영역
  • 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당
  • 필요에 따라 2개 이상 받을 수 있으나 클라이언트 스레드 수완 무관하며, 글로벌 영역이 N개라 해도 모든 스레드에 의해 공유됨
  • 테이블 캐시, InnoDB 버퍼 풀, InnoDB 어댑티브 해시 인덱스, InnoDB 리두 로그 버퍼
• 로컬 메모리 영역
  • 세션 메모리 영역이라고도 하며 MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역
  • 클라이언트가 서버에 접속하면 서버에서 커넥션으로부터의 요청을 처리하기 위해 스레드 하나씩 할당하게 되는데, 이때 사용되는 영역이라 보면 됨
  • 로컬 메모리기에 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않음
  • 정렬 버퍼(Sort buffer), 조인 버퍼, 바이너리 로그 캐시, 네트워크 버퍼

쿼리 파서

• 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업
• 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지 전달

토큰: MySQL이 인식할 수 있는 최소 단위의 어휘나 기호

전처리기

• 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점 있는지 확인
• 토큰을 테이블 이름, 칼럼 이름, 또는 내장 함수와 같은 개체를 매핑해 존재 여부, 접근 권한 등을 확인
• 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 여기서 걸러짐

옵티마이저

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

4.2 InnoDB 스토리지 엔진 아키텍처

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

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

외래 키 지원

• MyISAM이나 MEMORY테이블에선 외래 키를 지원하지 않음

MVCC(Multi Version Concurrency Control)

• 하나의 레코드에 대해 여러 개의 버전이 동시에 관리됨
• 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, 잠금을 사용하지 않는 일관된 읽기를 제공
• InnoDB는 언두 로그(Undo log)를 이용해서 구현

잠금 없는 일관된 읽기(Non-Locking Consistent Read)

• MVCC 기술을 통해 잠금을 걸지 않고 읽기 작업 수행
• 잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업 가능
• 격리 수준이 SERIALIZABLE이 아닌 레벨들은 다른 트랜잭션 변경 작업과 관계없이 바로 실행 가능하다
• 변경되기 전의 데이터를 읽기 위해선 언두 로그를 사용한다.

트랜잭션이 시작됐다면 가능한 한 빨리 롤백이나 커심을 통해 트랜잭션을 완료하라

자동 데드락 감지

자동화된 장애 복구

InnoDB 버퍼 풀

• InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간
• 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 함
• 일반적인 APP에서 INSERT, UPDATE, DELETE 같은 데이터 변경 쿼리는 데이터 파일 이곳저곳에 위치한 레코드를 변경하기에 랜덤한 디스크 작업이 발생함.
• 이때 버퍼 풀에 변경된 데이터를 모아 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있게 됨

[rawfishthelgh]

구조

mysql의 구조는 위와 같다. mysql 서버는 mysql 엔진과 스토리지 엔진으로 크게 구분할 수 있다.

MySql 엔진

클라이언트의 접속과 쿼리 요청을 처리하는 커넥션 핸들러, sql 파서 및 전처리, 옵티마이저가 존재한다. Mysql은 ansi 표준 sql 문법을 지원하므로 타 dbms와 호환되어 실행될 수 있다. 즉 문장분석 및 최적화와 같은 두뇌에 해당하는 처리를 한다.

스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나 스토리지로부터 데이터를 읽어들인다. InnoDb 등의 여러 가지 스토리지 엔진이 존재한다.

핸들러 API

Mysql 엔진에서 스토리지 엔진과 읽기/쓰기 요청을 주고 받을 때 사용하는 api다.

show global status like 'Handler%' 쿼리를 사용해 얼마나 많은 데이터 작업이 있었는지 확인 가능하다.

MySql 스레딩 구조

mysql 서버는 프로세스 기반이 아닌 스레드 기반으로 작동하며, 크게 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.

포그라운드 스레드

각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다. 최소 MYsql 서버에 접속된 클라이언트 수만큼 존재한다. 사용자가 작업 후 커넥션이 끝나면 해당 커넥션 스레드는 다시 스레드 캐시로 돌아간다. 그러나 스레드 캐시에 일정 수 이상의 스레드가 있으면 스레드 캐시에 돌아가는게 아닌 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다.

포그라운드 스레드는 데이터를 먼저 Mysql의 데이터 버퍼나 캐시로부터 가져오며, 여기에 없으면 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와 처리한다. MyISAM은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만, INNODB는 데이터 버퍼나 캐시에 쓰는 작업까지만 포그라운드가 처리하고, 버퍼로부터 디스크로 쓰는 작업은 백그라운드 스레드가 처리한다.

MYSQL에서 사용자 스레드와 포그라운드 스레드는 똑같은 의미로 사용된다.

백그라운드 스레드

MYISAM에는 해당사항이 없는데, INNODB에서는 아래의 작업을 백그라운드에서 처리한다

• 로그를 디스크로 기록하는 스레드 (제일 중요)
• 인서트 버퍼를 병합하는 스레드
• InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
• 데이터를 버퍼로 읽어 오는 스레드
• 잠금이나 데드락을 모니터링하는 스레드

가장 중요한 스레드는 로그 스레드의 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드이다.

Mysql 5.5 버전부터 데이터 쓰기 스레드와 읽기 스레드의 개수를 2개 이상 지정할 수 있다. 데이터를 읽는 작업은 주로 포그라운드 스레드에서 처리되므로 많이 설정할 필요가 없으나, 쓰기 스레드는 대부분 백그라운드 스레드로 처리하므로 충분히 설정하는 것이 좋다.

사용자의 요청을 처리하는 도중 쓰기 작업은 지연되어 처리할 수 있으나, 읽기 작업은 절대 지연될 수 없다.(생각해보면 당연함, select를 요청했는데 좀따 알려줄게요 하면 안되지) 따라서 일반적인 dbms는 쓰기 작업을 버퍼링으로 일괄 처리하는 기능이 탑재되어 있다(innodb 역시). 그러나 myisam은 그렇지 않고 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계되어 있다.

(이해안가는곳)따라서 innodb는 쓰기 쿼리가 일어나는 경우, 데이터가 디스크의 데이터 파일로 완전히 저장될 때 까지 기다리지 않아도 된다(이게 쓰기 지연이랑 무슨 관련??). 하지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다(사용자 스레드에 인서트 버퍼를 병합하는 처리 기능이 없으므로, 이건 이해함).

메모리 할당 및 사용 구조

Mysql의 메모리 공간은 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.

글로벌 메모리 영역

일반적으로 클라이언트 수와 무관하게 하나의 메모리 공간만 할당된다. 2개 이상 할당되어도 사용자 스레드 수와는 무관하다. 또 모든 스레드에 의해 공유된다. 아래와 같은 것이 해당한다.

• 테이블 캐시
• InnoDB 버퍼 풀
• InnoDB 어댑티브 해시 인덱스
• InnoDB 리두 로그 버퍼

로컬 메모리 영역

세션 메모리 영역이라고도 한다. 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다. 각 클라이언트 스레드별로 독립적으로 할당되며 공유되어 사용되지 않는다.

필요할 때만 공간이 할당되고 필요하지 않으면 Mysql이 공간을 할당조차 하지 않는 경우가 있다.(소트와 조인 버퍼와 같은 공간이 그러함)

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

아래와 같은 것이 해당한다

• 정렬 버퍼
• 조인 버퍼
• 바이너리 로그 캐시
• 네트워크 버퍼

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

MYsql에는 핸들러라는 개념이 존재한다. Mysql 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용하게 된다. 즉 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하기 위한 장치이다.

Mysql의 상태 변수 중 Handler_ 로 시작하는 것은 Mysql이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수다.

따라서 Mysql의 처리 내용은 핸들러를 사용하므로, 어떤 스토리지 엔진(innodb,myisam)을 사용해도 처리 내용은 대부분 동일하다.

그러나 어떤 스토리지 엔진을 사용하느냐에 따라 데이터 읽기/쓰기 작업이 크게 다르므로, 가장 중요한 것은

“하나의 쿼리 작업은 여러 하위 작업으로 나눠지는데, 각 하위 작업이 Mysql 엔진 영역(파서, 옵티마이저, 실행기)에서 처리되는지 아니면 스토리지 엔진(innoDB, myIsam)에서 처리되는지 구분할 수 있어야 한다”

라는 점이다. 예를 들어 group by나 order by는 쿼리 실행기에서 처리되는 것 처럼 말이다.

show engines;

위 쿼리를 사용하면 mysql 서버의 스토리지 엔진을 확인할 수 있다.

support 칼럼에는 서버에 해당 스토리지 엔진이 포함됐는지, 사용 가능으로 활성화됐는지를 표시한다.

show plugin 쿼리를 사용하면 스토리지 엔진 뿐 아니라 설치된 다양한 플러그인을 확인할 수 있다. 인증이나 전문 검색용, 혹은 쿼리 재작성을 위한 플러그인을 mysql에서 제공한다.

그러나 플러그인은 아래와 같은 단점을 보유한다

• 플러그인은 오직 Mysql 서버와 인터페이스할 수 있고 플러그인끼리 통신 불가
• 플러그인은 Mysql 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음(캡슐화 안 됨)
• 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움

컴포넌트

Mysql 8.0 부터 기존의 플러그인 아키텍쳐를 대체하기 위해 컴포넌트 아키텍처가 지원된다.

→ 여기는 뭔 소리인지 모르겠음

쿼리 실행 구조

위는 쿼리가 실행되는 구조이다

쿼리 파서

요청에서 들어온 쿼리 문장은 토큰(Mysql이 인식하는 최소 단위의 어휘나 기호)로 분리해 트리 형태의 구조로 만드는 작업을 한다. 문법 오류를 여기서 발견함

전처리기

파서가 만들어준 파서 트리를 기반으로 쿼리 문장의 구조적 문제점을 파악. 각 토큰을 테이블 이름, 칼럼 이름, 내장 함수와 같은 개체와 매핑해 해당 객체의 존재 여부와 객체 접근 권한을 확인한다. 실제 존재하지 않거나, 권한상 사용할 수 없는 개체의 토큰을 이 단계에서 걸러낸다.

옵티마이저

쿼리 문장을 가장 저렴한 비용으로 빠르게 처리하기 위한 최적화 실행계획을 세운다.

실행 엔진

옵티마이저(경영진)가 두뇌라면 실행 엔진(중간 관리자)과 핸들러(실무자)는 손과 발이다.

옵티마이저가 group by를 처리하기위해 임시 테이블을 사용하기로 결정했다고 해보자

1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
2. 실행 엔진은 where 절에 해당하는 레코드를 읽어오라고 핸들러에 요청
3. 읽어온 레코드를 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에 요청
4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에 다시 요청
5. 최종적으로 실행 엔진이 결과를 사용자나 다른 모듈에 넘김

핸들러

Mysql 서버의 가장 밑단에서 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크에서 읽어 옴. 띠라서 핸들러는 결국 스토리지 엔진을 의미한다. 따라서 InnoDB 테이블을 조작한다면 InnoDB가 핸들러가 된다.

복제

16장에서 다룬다함

쿼리 캐시

sql 실행 결과를 메모리에 캐시하고 동일 쿼리가 실행되면 테이블을 읽지 않고 메모리에서 즉시 반환

그러나 테이블 데이터가 변경되면 캐시 중 변경된 것들은 모두 삭제해야 함. 이 경우 동시 처리 성능 저하를 유발하는 경우가 많음.

따라서 Mysql 8.0으로 올라오면서 쿼리 캐시는 mysql 서버 기능에서 제외됐음

스레드 풀

Mysql 커뮤니티 에디션에서는 지원하지 않음.

동시 처리되는 요청이 많더라도, MYsql서버의 cpu가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는게 목적.

그러나 실제 서비스에서 큰 성능 향상을 보여주기는 힘들며, 동시 실행 스레드들을 cpu가 최대한 잘 처리할 수 있는 만큼 줄여서 빨리 처리하게 하는 기능이므로, 스케줄링 과정에서 cpu 시간을 제대로 확보하지 못하는 경우 쿼리 처리가 느려질 수도 있음

물론 제한된 수만의 스레드만으로 cpu가 처리할 수 있도록 적절히 유도되면 cpu의 프로세서 친화도(특정 프로세스가 어느 프로세서에서 실행되는지 결정→ 이게 높으면 왜 좋음?)도 높이고 운영체제 입장에서는 불필요한 컨텍스트 스위치를 줄여서 오버헤드를 낮춘다.

일반적으로 스레드 풀 사이즈는 cpu 코어 개수와 맞추는 것이 프로세서 친화도를 높이는데 좋음. 값이 너무 크면 스케줄링해야 할 스레드가 많아져서 비효율적으로 작동할 수 있음.

풀의 모든 스레드가 일을 하고 있다면 스레드 풀은 새 스레드를 추가할지, 기다릴지 여부를 판단. 풀의 타이머 스레드가 주기적으로 상태를 체크해 thread_pool_stall_limit 시스템 변수에 정의된 밀리초만큼 작업 스레드가 처리 중인 작업을 끝내지 못하면, 새로운 스레드를 생성해서 스레드 그룹에 추가함. 그러나 이는 최대로 설정한 스레드 개수를 넘어설 수 없음. 응답 시간에 민감한 서비스라면 thread_pool_stall_limit 을 낮추는게 좋은데, 이것이 0에 수렴하면 굳이 스레드 풀을 둘 필요가 없다.

Percona Server의 스레드 풀은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리하도록 재배치할 수 있음. 이를 통해 전체적인 처리 성능을 향상시킴.

트랜잭션 지원 메타데이터

데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라 함.

Mysql서버는 5.7버전까지 메타데이터를 파일에 저장했는데, 이는 생성 및 변경 작업이 트랜잭션을 지원하지 않아 생성/변경 도중 서버가 종료되면 일관성이 깨지는 문제가 생김.

Mysql은 8.0 버전부터 이를 해결하기 위해 메타데이터를 모두 InnoDB의 테이블에 저장하도록 개선함. Mysql 서버가 작동하는데 기본적으로 필요한 테이블을 시스템 테이블이라 하는데, 이 시스템 테이블을 모두 innoDB 스토리지 엔진을 사용해 이 정보를 모두 모다 Mysql DB에 저장함. 이는 통째로 mysql.ibd 라는 이름의 테이블스페이스에 저장됨.→ 중요한 파일이니 주의하라는 얘기임.

따라서 트랜잭션 기반의 innoDB 스토리지 엔진에 저장되므로 변경 작업 중간에 서버가 종료되어도 all or nothing을 보장함.

그러나 InnoDB 이외의 스토리지 엔진은 여전히 저장공간이 필요한데, 이 때는 SDI(Serialized Dictionary Information) 파일을 사용함. 이름 그대로 직렬화를 위한 포맷이므로, InnoDB 테이블의 구조도 SDI 파일로 변환 가능.

InnoDB 스토리지 엔진 아키텍처

InnoDb는 Mysql에서 사용하는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공한다. 따라서 높은 동시성 처리 성능을 보유하고 있다.

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

innodb의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다. 즉 pk의 순서대로 디스크에 저장된다는 뜻이다. 모든 세컨더리인덱스는 레코드의 주소 대신, 프라이머리키의 값을 논리적인 주소로 사용한다. 따라서 pk를 이용한 레인지 스캔은 빨리 처리된다. 즉 실행 계획에서 pk가 다른인덱스에 비해 비중이 높다.

Myisam에는 클러스터링 키를 지원하지 않아 pk와 세컨더리 인덱스가 차이가 없다.

외래 키 지원

외래 키에 대한 지원은 innoDb에서만 지원한다. innoDb의 외래 키는 부모와자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시 연관 테이블에 데이터가 있는지 체크 작업이 필요하므로, 잠금이 전파되고 데드락이발생할 수 있다. 따라서 외래키는 서버 운영의 불편함 때문에 쓰지 않는 경우도 있다.

foreign_key_checks 시스템 변수를 off로 설정하면 외래키 체크 작업을 멈출 수 있다. 그러나 이 경우 부모 자식 테이블의 관계가 깨질 수 있으므로 일관성을 유지하도록 삭제 작업을 한 후 다시 체크기능을 활성화해야 한다.

MVCC(Multi Version Concurrency Control)

레코드 레벨의 트랜잭션을 지원하는 dbms가 지원하는 기능이다. mvcc의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다. InnoDB는 언두 로그를 이용해 이 기능을 구현한다. 멀티버전은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 뜻이다.

어떤 테이블에 위 데이터가 저장되어 있다고 생각해 보자

UPDATE member SET m_area='경기' WHERE m_id=12; 쿼리문을 사용하면 커밋 여부와 상관없이 innodb 버퍼 풀은 업데이트 되고, 기존 데이터는 undo 로그로 복사된다. 아직 커밋 혹은 롤백되지 않은 상태에서

SELECT * FROM member WHERE m_id=12; 를 사용하면 어떤 결과가 일어날까?

이는 격리 수준에 따라 다르다. 격리 수준이 최소 read_commited 이상 인 경우 undo 로그의 데이터를 반환하고, read_uncommited 인 경우에는 커밋과 상관없이 버퍼 풀의 데이터를 반환한다.

이러한 과정을 dbms에서는 mvcc라고 표현한다. 즉, 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 상황에 따라 다르다.

여기서는 한 개의 데이터만 있지만, 상황에 따라 관리해야 하는 예전 버전의 데이터가 무한히 많아질 수 있다. 트랜잭션이 길어지만 언두에서 관리하는 예전 데이터가 삭제되지 못하고 오랫동안 관리돼야 하며, 따라서 테이블 저장공간이 늘어나는 상황이 발생 가능하다.

잠금 없는 일관된 읽기(Non-Locking Consistent Read)

InnoDB 스토리지 엔진은 MVCC 엔진을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다. 잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다. 격리 수준이 Serializable 보다 낮은 수준이기만 하면 insert와 연결되지 않은 순수 읽기 작업은 다른 트랜잭션의 변경 작업과 상관없이 잠금 대기 없이 바로 실행된다. 이를 “잠금 없는 일관된 읽기”라 한다.

innodb에서는 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용한다.

자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았느지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리한다. 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아 그 중 하나를 강제 종료한다.

트랜잭션의 언두 로그를 적게 가진 트랜잭션이 롤백 대상 중 더 먼저 처리된다. 이는 롤백을 해도 언두 처리를 덜 하기 때문이고, 서버의 부하도 덜 유발해서이다.

Innodb 테이블 엔진은 상위 레이어진 mysql엔진에서 관리되는 테이블 잠금은 볼수 없어서 데드락 감지가 불확실할 수 있다. 이 때 innodb_table_locks 변수를 활성화하면 mysql 테이블 레벨 잠금까지 감지 가능하므로, 웬만하면 이 변수를 활성화하는게 좋다.

동시 처리 스레드가 많아지거나 잠금 개수가 많아지면 데드락 감지 스레드가 느려진다. 데드락 스레드가 잠금 목록을 검사하기 위해 잠금 상태가 변경되지 않도록 잠금 테이블에 새 잠금을 걸고 데드락을 찾기 때문이다. 따라서 서비스를 처리하는 스레드가 작업을 하지 못하고 대기하면서 cpu 자원을 더 많이 소모하고 서비스에 악영향이 생길 수 있다.

이 문제를 해결하기 위해 mysql 서버는 innodb_deadlock_detect 시스템 변수를 제공하고, 이를 off로 설정하면 데드락 감지 스레드가 작동하지 않는다. 하지만 이 경우 데드락 발생시 무한정 대기하게 되므로, innodb_lock_wait_timeout 변수를 활용해 일정 시간이 지나면 요청이 타임아웃으로 실패하도록 한다.

자동화된 장애 복구

Mysql 서버와 무관하게 디스크나 서버 하드웨어 이슈로 innodb 스토리지 엔진이 자동으로 복구를 못하는 상황이 생길 수 있는데, innodb 데이터파일은 서버가 시작될 때 항상 자동 복구를 시행하며, 자동 복구가 안되는 손상이 있다면 자동 복구를 멈추고 서버를 종료한다.

이 때는 MYsql 서버의 설정 파일에 innodb_force_recovery 변수를 사용해 mysql 서버를 시작해야 한다. 이 설정값은 mysql 서버 시작 시 innodb 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 한다. 이 옵션에는 1부터 6까지의 값을 설정 가능하다. 옵션은 찾아보면 나온다.

그러나 옵션을 걸어 진행해도 서버가 시작되지 않으면 백업을 이용해 다시 구축하는 방법밖에 없다.

백업이 있다면 마지막 백업으로 db를 새로 구축하고, 바이너리 로그를 사용해 장애 시점까지의 데이터를 복구할 수도 있다. 마지막 풀 백업 시점부터 장애 시점까지의 바이너리 로그가 있다면 innodb를 사용하는 것보다 풀 백업과 바이너리 로그로 백업하는게 데이터 손실이 더 적을 수도 있다.

InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분이다. 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다. 쓰기 작업을 지연해 일괄 처리하는 버퍼 역할도 한다. 버퍼 풀은 데이터 변경 쿼리를 모아서 처리하므로 랜덤한 디스크 작업의 횟수를 줄일 수 있다.

버퍼 풀 크기 설정

버퍼 풀의 크기는 운영체제와 각 클라이언트가 사용할 공간까지 고려해 걸정해야 한다.

만약 커넥션이 많다면 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 사용하는 레코드 버퍼의 메모리 공간이 많이 필요해진다. 이 공간은 별도로 설정할 수 없고, 동적으로 해제되기도 해서 정확히 필요한 메모리 크기를 계산할 수 없다.

Mysql 5.7 버전부터는 innodb 버퍼 풀의 크기를 동적으로 조절할 수 있다. 따라서 버퍼 풀의 크기를 적절히 작게 설정하고 조금씩 증가하는 방식이 최적이다. 보통 운영체제의 메모리 공간이 8gb 미만이면 50프로를 버퍼 풀로 설정 후 올리고, 50gb 이상이면 15~30gb 정도를 운영체제와 응용 프로그램에 할당하고 나머지를 버퍼 풀로 쓴다.

버퍼 풀의 크기 변경은 크리티컬한 변경이므로 서버가 한가한 시점에 진행한다. 그리고 버퍼 크기를 늘리는 것보다 줄이는 것이 크리티컬하다.

innodb 버퍼 풀은 버퍼 풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합(이게뭐지)을 많이 유발해왔는데, 이런 경합을 줄이기 위해 버퍼 풀을 여러 개로 쪼개 관리할 수 있게 개선됐다. 버퍼 풀이 쪼개지면서 잠금 자체도 경합이 분산되는 효과를 내는 것이다.

버퍼 풀의 구조

innodb 스토리지 엔진은 버퍼 풀을 페이지 크기의 조각으로 쪼개어 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다.

버퍼 풀의 페이지 크기 조각을 관리하기 위해 innodb는 LRU(Least Recently Used)리스트와, 플러시 리스트, 프리 리스트라는 3개의 자료 구조를 관리한다. 프리 리스트는 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록이다. 사용게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다.

LRU 리스트는 LRU와 MRU(Most Recently Used)가 결합된 형태인데, old 서브리스트가 LRU, new 서브리스트가 MRU이다.

LRU 리스트를 관리하는 목적은, 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것이다. InnoDB 스토리지 엔진에서 데이터를 찾아내는 과정은 다음과 같다

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
   1. 페이지 검색 시 버퍼 풀에 이미 데이터 페이지가 있다면 해당 페이지의 포인터를 MRU 방향으로 승급
2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이가 부여되며, 쿼리가 오랫동안 사용되지 않으면 나이가 먹고 해당 페이지가 버퍼 풀에서 제거됨. 반대로 사용되면 나이가 초기화 됨
5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

즉, 페이지가 읽히면 new(mru)로, 읽히지 않으면 old(lru)로 이동하는 구조이다.

플러시 리스트는 디스크로 동기화되지 않은, 더티 페이지의 변경 시점 기준의 페이지 목록을 관리한다. 일단 한 번 데이터 변경이가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록돼야 한다.

데이터가 변경되면 innodb는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다. 따라서 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결된다.

그러나 리두 로그가 디스크로 기록됐다고 해도 데이터 페이지가 디스크로 기록됨을 항상 보장하지 않는다. 따라서 innodb 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화하게 된다. 체크포인트는 리두 로그의 어느 부분부터 복구를 시작할지 판단하는 기준점임

버퍼 풀과 리두 로그(뒤지게 어려움)

버퍼 풀은 디스크 메모리가 모든 버퍼 풀에 적재되기 전까지 늘리면 늘릴수록 쿼리가 빨라짐. 그러나 버퍼 풀은 데이터 캐시와 쓰기 버퍼링(지연), 두 가지 용도가 있는데, 늘리는 것은 캐시만 이득은 보는 것임. 따라서 쓰기 버퍼링을 향샹시키려면 버퍼 풀과 리두 로그의 관계 파악이 필수.

버퍼 풀은 데이터의 변경이 없는 클린 페이지와 변경이 생긴 더티 페이지로 나뉨. 더티 페이지는 버퍼 풀에 무한정 머무를 수 없음.

데이터 수정이 계속 발생하면 리두 로그의 로그 엔트리는 새로운 로그 엔트리로 덮임. 따라서 재사용 가능한 공간과 재사용 불가능한 공간을 구분해야 한다. 이 때 재사용 불가능한 공간을 활성 리두 로그라 함.

리두 로그는 재사용되지만 기록될 때마다 로그 포지면은 계속 증가된 값을 가짐. 이를 LSM(log sequence number)라 함. 가장 최근 체크포인트 지점의 lsn이 활성 리두 로그 공간의 시작점이 됨. 그러나 활성 리두 로그 공간의 마지막은 계속해서 증가하기 때문에 체크포인트와 무관함. 가장 최근 체크포인트의 lsn과 마지막 리두 로그 엔트리의 lsn의 차이를 체크포인트 에이지라고 함. 즉 체크포인트 에이지는 활성 리두 로그 공간의 크기임.

체크포인트 lsn보다 작은 lsn값을 가진 더티 페이지와 리두 로그 엔트리는 모두 디스크로 동기화돼야 함.

버퍼 풀 플러시

innodb는 버퍼 풀에서의 더티 페이지를 디스크로 동기화하기 위해 2개의 플러시 기능을 백그라운드로 실행한다.

1. 플러시 리스트 플러시

   플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행한다. 언제부터 얼마나 많은 더티 페이지를 한 번에 디스크로 기록하느냐에 따라 성능 차이가 결정된다.

   클리너 스레드를 통해 더티 페이지를 디스크로 동기화하며, 클리너 스레드의 개수를 조정할 수 있다. 보통 버퍼 풀 인스턴스 개수와 동일하게 맞춘다.

   가진 페이지의 몇 퍼센트까지 더티 페이지를 허용할 것인지 결정할 수 있다. 보통 기본값을 쓴다.

   더티 페이지가 많을수록 디스크 쓰기 폭발(Disk IO Burst)이 일어날 가능성이 높아진다. 따라서 일정 수준 이상의 더티 페이지가 발생하면 그때부터 조금씩 더티 페이지를 디스크로 기록하도록 한다.

   각 데이터베이스 서버에서 어느 정도의 디스크 읽고 쓰기를 가능하게 할 것인지 설정할 수 있다. 이 설정을 동적으로 조정하기는 힘드므로, 어댑티브 플러시라는 기능을 사용하면, 어댑티브 플러시가 리두 로그의 증가 속도를 분석해서 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 한다.

2. LRU 리스트 플러시

   사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지를 읽어올 공간을 만들 때 사용한다. 설정된 만큼의 페이지를 스캔 후, 더티 페이지는 동기화하고, 클린 페이지는 프리 페이지로 옮긴다.

버퍼 풀 상태 백업 및 복구

디스크의 데이터가 버퍼 풀에 적재된 상황을 워밍업이라 한다. mysql 5.5에서는 서버를 셧다운하다가 다시 시작하는 경우, 강제 워밍업을 해 주요 테이블과 인덱스에 풀 스캔을 한번씩 실행한다.

5.6 버전부터는 버퍼 풀 덤프 및 적재 기능이 도입된다. 따라서 ìb_buffer_pool 시스템 변수에 innoDB 버퍼 풀에 상태를 백업해준다. 셧다운 후 재시작 시 이 변수를 이용해 상태를 다시 복구한다.

이 백업 파일은 제한적 크기를 두어 데이터 페이지의 메타 정보만 가져와 백업하도록 한다. 따라서 백업이 빠르게 완료된다.

버퍼 풀의 적재 내용 확인

Mysql 8.0 버전부터는 information_schema 테이블에 innodb_cached_indexes 테이블을 새로 추가해, 테이블의 인덱스별로 데이터 페이지가 얼마나 innoDb 버퍼 풀에 적재돼 있는지 확인할 수 있다.