MySQL 엔진 아키텍처

MySQL 서버는 크게 머리 역할을 담당하는 MySQL 엔진과 손발 역할을 담당하는 스토리지 엔진으로 구성된다. 기본으로 제공되는 스토리지 엔진에는 InnoDB와 MyISAM 등이 있다.

 

MySQL의 구조 및 MySQL 엔진

MySQL 엔진은 다음과 같은 구성요소가 중심을 이룬다.

• 접속 및 쿼리 요청을 처리하는 커넥션 핸들러
• SQL 파서
• 전처리기
• 옵티마이저
  - 옵티마이저가 중심

 

스토리지 엔진

 

실제 데이터의 저장과 조회는 스토리지 엔진이 담당하고 있다.

• 특징
  • MySQL 서버는 하나의 MySQL 엔진을 사용하지만, 여러 스토리지 엔진을 동시에 사용할 수 있다.
  • 테이블별로 사용할 스토리지 엔진을 지정이 가능하다.
  • INSERT, UPDATE, DELETE, SELECT 등의 작업은 InnoDB 스토리지 엔진이 처리

CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;

• test_table 을 스토리지 엔진을 사용하도록 정의
• 성능 향상 기능이 있다.
  1. MyISAM 의 경우
     1. 키 캐시 기능
        • 인덱스 정보를 메모리에 캐싱하는 기능이다.
        • DB는 인덱스를 디스크에서 직접 읽는 대신, 메모리에서 빠르게 접근하여 쿼리 실행 속도를 향상시킬 수 있다.
  2. InnoDB 의 경우
     1. InnoDB 버퍼풀 기능
        • 데이터와 인덱스를 메모리에 캐싱하는 기능
        • DB 읽기와 쓰기 작업의 성능을 크게 향상시킨다.
        • Disk I/O 를 줄일 수 있고 전반 DB 성능 향상을 기대할 수 있다.

 

위와 같은 이유로 DB 의 성능 향상을 이룰수 있다는 점이 장점인것 같다.

 

핸들러 API

• MySQL 엔진이 데이터를 쓰거나 읽는 경우, 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는 것을 핸들러 요청 이라고 한다.
• 핸들러 요청에 사용되는 API 를 핸들러 API 라고 한다

핸들러 API 의 역할

• InnoDB 스토리지 엔진을 포함한 다양한 스토리지 엔진들이 MySQL 엔진과 데이터를 주고 받는데에 사용된다.
• 핸들러 API 를 통해 DB 엔진은 스토리지 엔진에 data 를 read 혹은 write 작업을 요청

실제로 호출되는 많은 데이터 작업이 있는지 확인 해봤다.

 

MySQL 스레딩 구조

 

MySQL 은 프로세스 기반이 아니라, 스레드 기반으로 작동한다.

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

- 스레드 확인 하는 쿼리문

 

실제 사용자의 요청을 처리하는 포그라운드 스레드는 thread/sql/one_connection

 

이런식으로 동일한 스레드가 여러개 있는 경우, 여러 스레드가 동일 작업을 병렬로 처리하는 설정 때문이다.

 

스레드 종류

크게 2가지 유형의 스레드로 구분한다.

1. 포그라운드 스레드 (Foreground Threads) - 사용자 스레드라고도 한다.
   1. 사용자의 요청을 직접 처리한다
   2. 커넥션 연결 및 스레드 캐시 관리 등을 담당한다
2. 백그라운드 스레드
   1. 데이터 파일, 로그 파일, 캐시 및 버퍼 관리 등의 백엔드 작업 수행 한다.
   2. 로그 버퍼 - 디스크 기록, 다양한 시스템 유지 관리 작업 수행한다

포그라운드 스레드

• MySQL 서버에 접속된 각 클라이언트의 요청을 처리하는 스레드
• 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
• 작업 완료 후 커넥션을 종료하면 스레드 캐시로 반환된다.

스레드 캐시 관리

• 스레드 캐시는 재사용을 위하여 대기 중인 스레드를 관리함
• thread_cache_size 시스템 변수를 통해 스레드 캐시에 유지할 수 있는 최대 스레드 지정 가능
• 스레드 캐시에 일정 개수 이상의 스레드 있는 경우, 추가 스레드 종료시켜서 자원을 관리함.

데이터 처리

• 데이터는 MySQL 의 데이터 버퍼나 캐시 에서 가져온다.
• 버퍼나 캐시에 없는 경우, 디스크의 데이터나 인덱스 파일에서 직접 읽어옴
• MyISAM 테이블
  • 포그라운드가 디스크 쓰기 작업까지 처리
• InnoDB 테이블
  • 포그라운드 스레드는 데이터 버퍼나 캐시까지만 처리함.
  • 디스크까지 기록은 백그라운드 스레드가 처리함.

백그라운드 스레드

 

역할

- 인서트 버퍼 병합 ( Insert Buffer Merging ) 스레드

1. 비클러스터 인덱스에 대한 INSERT 작업을 더 효율적으로 만들기 위하여 인서트 버퍼를 사용한다.
2. 해당 스레드는 인서트 버퍼에 저장된 변경사항을 실제 인덱스와 병합한다.

DB 에는 인덱스 라는 정렬 규칙

인덱스
책의 목차 와 같은 역할을 하고 우리가 필요한 데이터를 빠르게 탐색 가능하다

비클러스터 인덱스와 인서트 버퍼
비 클러스터 인덱스데이터가 저장된 순서와 별개이다.
특정 규칙에 따라 별도로 관리되는 인덱스
예시
- 책의 색인과 같이 본문의 순서와 상관없이 특정 단어가 어디에 있는지 빠르게 찾을 수 있게 해준다

인서트 버퍼
비클러스터 인덱스에 데이터를 추가하는 경우
바로 인덱스를 업데이트하는 대신 임시로 저장해주는 공간
DB 성능을 향상시키기 위한 기술

실생활로 따지면
도서관에 책이 있고, 해당 책들을 바로 책장에 꽂는 대신, 일단 책을 도서관 입구에 임시로 쌓아둔다.
인서트 버퍼
그리고 나중에 도서관 직원이 해당 책을 적절한 책장에 꽂아 정리하는 형태
인서트 버퍼 병합

 

1. 로그 기록 스레드
   1. 트랜잭션 내구성을 보장하기 위해 변경 사항을 로그 파일에 저장
   2. 시스템 장애가 발생하여도 데이터를 복구할 수 있게 해준다
2. 버퍼 풀 데이터 기록 스레드
   1. 메모리에 있는 버퍼 풀의 데이터를 주기적으로 디스크에 기록하여, 데이터의 영속성을 보장함
3. 데이터 읽기 스레드
   1. 디스크에서 데이터를 읽어와, 버퍼 풀에 적재
   2. 데이터 접근 시간을 단축시키는데 도움
4. 잠금 및 데드락 모니터링 스레드
   1. DB 잠금 상태를 모니터링
   2. 데드락 감지하여 해결함