[MySQL] MySQL 서버 구조와 특징

 

 

MySQL 서버는 MySQL 엔진 + 스토리지 엔진으로 구분할 수 있다. 
스토리지 엔진은 핸들러 API를 만족하기만 하면 MySQL 서버에 추가해서 사용할 수 있다.

해당 글에선 MySQL 서버의 전체적인 구조와 특징을 알아본다.

 

---

 

1. MySQL 전체 구조

 

(1) MySQL 엔진 -> (2) 핸들러 API -> (3) 스토리지 엔진

 

---

 

(1) MySQL 엔진 : 요청된 SQL 문장 분석, 최적화

 

• 커넥션 핸들러, SQL 파서, 전처리기, 옵티마이저로 이루어 짐
• 쿼리 실행은 대부분 MySQL 엔진에서 이루어짐

 

 

 

< 쿼리 실행 순서 >

1) 쿼리 파서

2) 전처리기

3) 옵티마이저

4) 쿼리 실행기

5) 핸들러

 

 

 

 

1) 쿼리 파서
SQL 쿼리 문장을 토큰으로 잘게 쪼개는 순서 ->  파스 트리를 만들어 냄
문법 확인 과정

 

2) 전처리기 
만들어진 파스트리를 통해 문제점 확인 - 객체의 존재 여부, 접근 권한 등 확인

 

3) 옵티마이저
비용을 아낄 수 있는 더 나은 실행 계획을 도출

4) 쿼리 실행기
실행 계획에 따라 핸들러에게 요청하고 다시 받은 결과를 다른 핸들러에게 요청하며 연결하는 역할 수행

 

5) 핸들러 = 스토리지 엔진

MySQL 서버의 가장 아랫단에서 쿼리 실행기(실행 엔진) 요청에 따라 데이터를 디스크에 저장, 읽기 역할

 

 

---

 

(2) 핸들러 API : MySQL엔진의 쿼리 실행기에서 스토리지엔진에 쓰기와 읽기를 요청 ( Handler 핸들러 요청)

 

SHOW GLOBAL STATUS LIKE 'Handler%';

 

위의 명령으로 얼마나 많은 데이터 작업이 핸들러 API 로 일어났는지 확인 가능

 

• 운전대를 잡은 사람 역할을 MySQL 엔진이라고 한다면 자동차는 각 스토리지 엔진
• MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러를 사용함
• MySQL 서버의 상태 변수에서 'Handler_'로 시작하는 것이 많은 이유는 MySQL 엔진이 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수이기 때문

---

 

(3) 스토리지 엔진 : 디스크 스토리지에 데이터 저장, 읽기

 

• 여러개 사용 가능하며 InnoDB, MyISAM 등이 존재함 (주로 InnoDB로 대체되고 있다)
• 성능 향상을 위해 내장한 기능으로는 MyISAM의 경우엔 키 캐시 / InnoDB의 경우엔 버퍼풀이 있다.

 

* InnoDB 로 사용법

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

 

 

*스토리지 엔진 확인 방법

SHOW ENGIENS;

 

Support 칼럼에 표시될 수 있는 값으론 

- YES(사용 가능)

- DEFAULT(사용가능 - 필수)

- NO (MySQL 서버에 포함되지 않음 - 서버 다시 빌드(컴파일)해야함)

- DISABLED (비활성화)

 

 

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

 

다양한 스토리지 엔진 뿐만 아니라 검색어 파서 등도 플러그인 형태로 개발해 사용 가능

SHOW PLUGINS;

 

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

=> 이러한 문제를 보완하고자 컴포넌트라는 아키텍처가 지원됨

 

---

 

2. MySQL 스레딩 구조

 

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동함

(1) 포그라운드 스레드  (2) 백그라운드 스레드로 구분

 

(1) 포그라운드 스레드 = 사용자 스레드 : 각 클라이언트가 요청하는 쿼리 문장을 처리함

 

• 전통적인 스레드 모델에서는 커넥션별로(접속한 클라이언트 수만큼)  포그라운드 스레드가 하나씩 생성되어 할당됨
• 사용하지 않으면 스레드 캐시로 되돌아 감
• 대기중에 있던 스레드가 있다면 스레드 캐시에 넣지는 않고 종료함
• 스레드 캐시에 유지할 수 있는 갯수가 한정적 (thread_cache_size로 설정 가능)
• MySQL의 데이터 버퍼나 캐시로부터 데이터를 가져오고, 데이터가 없을 경우에는 직접 디스크나 인덱스 파일에서 데이터를 읽어옴

 

(+) 스토리지 엔진에 따라 다른 스레드 수행 방법

 

• MyISAM 스토리지 엔진을 사용할 경우 테이블은 읽기, 디스크 쓰기까지 포그라운드 스레드가 수행 처리
• InnoDB 스토리지 엔진을 사용할 경우 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 수행처리, 디스크에 쓰기는 백그라운드 스레드의 역할

 

 

(2) 백그라운드 스레드 : 주로 InnoDB에서 사용되는 스레드

 

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

 

(+) 데이터 읽기 작업은 절대 지연될 수 없다

(사용자가 SELECT 쿼리를 실행했는데 요청된 SELECT 는 10분 뒤에 결과를 돌려주겠다라고 응답을 보내는 DBMS는 없다!! 😆)

 

• 그래서 대부분의 DBMS와 InnoDB는 그래서 쓰기 작업을 버퍼링해 일괄 처리해 데이터가 변경될 디스크의 데이터 파일로 저장되는 것까지 기다리지 않아도 된다!
• 하지만! MyISAM은 포그라운드 스레드가 읽기, 쓰기 둘 다 하기 때문에 쓰기 버퍼링 기능 사용할 수 없음

 

---

 

 

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

 

(1) 글로벌 메모리 영역  (2) 로컬 메모리 영역 

 

(1) 글로벌 메모리 영역

 

• MySQL 서버가 시작되면서 OS로부터 할당되는 공간
• 정확한 메모리의 양을 측정하는 건 어려움
• 클라이언드 스레드 수와 무관하게 하나의 메모리 공간 할당됨 : 2개 이상의 공간을 할당받아도 모든 스레드에게 공유됨
• 테이블 캐시 / InnoDB 버퍼 풀 / InnoDB 어댑티드 해시 인덱스 / InnoDB 리두 로그 버퍼

 

(2) 로컬 메모리 영역 (= 클라이언트 메모리 영역 = 세션 메모리 영역)

 

• MySQL  서버상 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 독립적인 메모리 영역
• 절대 공유되어 사용되지 않음
• 조인버퍼나 소트 버퍼의 경우 필요할 때만 공간이 할당됨
• 커넥션 버퍼 / 소트버퍼 / 조인버퍼 / 바이너리 로그 캐시 / 네트워크 버퍼
• 커넥션 버퍼나 결과 버퍼는 커넥션이 열려있는 동안 계속 할당된 상태로 공간이 남아있음

 

 

 

---

 

 

(+) 쿼리 캐시

 

SQL의 실행 결과를 메모리에 캐시하고 동일한 쿼리가 요청 실행되면 테이블을 읽지 않고 반환하는데 계속된 동시 처리 성능 저하와 많은 버그의 원인이 되어 MySQL 서버의 기능에서 완전히 제거됨

 

(+) 스레드 풀

 

내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있도록 서버 자원 소모를 줄이는 역할 

그러나 스레드 그룹 개수와 CPU 코어 개수를 맞추는 것이 CPU 프로세서 친화도를 높이는 데 좋다

 

(+) 트랜잭션 지원 메타데이터 = 데이터 딕셔너리

 

- 테이블의 구조 정보와 스토어드 프로그램 등의 정보

- 기존에는 파일 기반으로 테이블 구조와 스토어드 프로그램을 저장함

- 파일기반 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않아 테이블의 생성 또는 변경 중에 서버가 종료되면 일관되지 않은 상태로 남는 문제 = 데이터베이스/테이블이 깨졌다. 

=> InnoDB 스토리지 엔진 테이블에 저장하도록 개선됨. mysql.ibd 테이블 스페이스에 mysqlDB가 통째로 저장됨 

 

 

 

---

 

 

 

• 출처

Real MySQL 8.0 - 백은빈, 이성욱  < 4.1 MySQL 엔진 아키텍처 >