Real MySQL - 엔진 1

 

 

개요

최근 Database에 대해 잘 모른다는 생각이 들어 Real MySQL 책으로 MySQL에 대해 공부하기로 했습니다.
현업에서 많이 쓰이며, 제가 다니는 회사에서도 많이 쓰는 RDBMS 이지만, 사실 아무것도 모르고 썼던걸 책을 보면서 많이 느꼈습니다.

이번에 정리한 내용을 포스팅하면서 공부해보는 걸로..

 

 

MySQL Engine

먼저 MySQL 의 엔진에 대해 알아보겠습니다.
엔진에는 MySQL 엔진, 스토리지 엔진이 있으며 두가지를 합쳐 MySQL, MySQL 서버라고 표현합니다.

 

MySQL 엔진

사람의 머리 역할을 담당하며, 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 처리를 담당하며, 클라이언트의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL Parser 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룹니다.

 

• 커넥션 핸들러 : 커넥션 핸들러는 클라이언트와 MySQL 서버 간의 연결을 관리하는 역할을 합니다. 즉, 클라이언트가 MySQL 서버에 접속하거나 연결을 종료할 때, 그리고 동시에 여러 클라이언트가 접속할 때 이를 조정합니다. 커넥션 핸들러는 사용자 인증, 보안, 세션 관리 등의 작업을 처리합니다.
• 옵티마이저 Optimizer : 옵티마이저는 최적화를 위한 장치입니다. 쿼리의 실행계획을 수립하는 옵티마이저는 가능한 다양한 실행 계획을 고려하여 가장 효율적인 방법을 선택하기 위해 사용되며 옵티마이저는 쿼리의 조건, 테이블의 통계 정보, 인덱스 등 등을 분석하여 실행 계획을 결정하고 이를 통해 쿼리의 실행 속도와 성능을 최적화합니다. 
• SQL 인터페이스 : SQL 인터페이스는 클라이언트가 MySQL 서버에 SQL 쿼리를 전송하고, 그 결과를 반환받는 역할을 합니다. 이 인터페이스는 클라이언트와 MySQL 엔진 간의 통신을 중개하며, 클라이언트가 요청한 작업을 수행합니다. SQL 인터페이스는 사용자의 요구에 따라 데이터의 검색, 수정, 삭제, 삽입 등의 작업을 처리합니다.
• SQL 파서 : SQL 파서는 클라이언트로부터 전달받은 SQL 쿼리를 분석하고, 문법적인 오류나 부정확한 쿼리를 감지합니다. 파서는 SQL 문장을 토큰으로 분리하고, 구문 트리로 변환하여 엔진이 이해할 수 있는 형태로 만듭니다. 이러한 파싱 작업을 통해 MySQL 엔진은 쿼리의 의미를 파악하고 처리할 수 있게 됩니다.
• 캐시 & 버퍼 : MySQL 엔진은 캐시와 버퍼를 사용하여 데이터 액세스의 성능을 향상시킵니다. 캐시는 자주 액세스되는 데이터나 쿼리 결과를 메모리에 보관하여 반복적인 액세스 시 디스크 I/O를 줄입니다. 버퍼는 디스크에 대한 액세스를 최소화하기 위해 변경된 데이터를 메모리에 임시로 저장합니다. 이러한 캐시와 버퍼를 통해 데이터베이스의 성능을 향상시키고, 응답 시간을 단축시킬 수 있습니다.

 

MySQL engine

 

 

 

핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이런 요청을 핸들러(Handler) 요청이라 하고, 이 때 사용되는 API를 핸들러 API라고 한다. InnoDB 스토리지 엔진 또한 이 핸들러 API를 통해 MySQL 엔진과 데이터를 주고받는다.

 

 

스토리지 엔진

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

MySQL Threading 구조

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

select * from performance_schema.threads # 실행중인 threads 조회

 

 

Foreground Thread (Client Thread)

MySQL 서버에 접속된 클라이언트의 수 만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다. 클라이언트가 종료하면 해당 커넥션을 담당하던 스레드는 다시 Thread Cache 로 반환된다. 만약 대기중인 스레드가 설정한 최대 스레드의 수 이상이라면 스레드 캐시에 넣지 않고 스레드를 종료시킨다. Foreground thread는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와 처리한다. MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드가 처리하지만 InnoDB 테이블은 버퍼와 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다. 

 

Background Thread

InnoDB일 경우 MyISAM에서는 해당하지 않는 작업이 백그라운드에서 처리된다.

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

가장 중요한 것은 로그 스레드(Log thread)와 버퍼의 데이터를 디스크로 내려쓰는 쓰기 스레드(Write thread)일 것이다. InnoDB에서도 데이터를 읽는 작업을 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요가 없지만 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 디스크를 최적으로 사용할 수 있을 만큼 충분한 설정이 필요하다.

요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다. 보통 DBMS에서 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 있으며, InnoDB 또한 동일한 방식으로 처리한다. 하지만 MyISAM은 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계되어 있다. 그러한 이유로 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때 까지 기다리지 않아도 된다. (쓰기 버퍼링때문)