Real My SQL 8.0 읽고 공부하기 - ③ 아키텍처

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

1) MySQL 엔진 아키텍처

 

MySQL은 C API, JDBC, ODBC, .NET 표준 드라이버를 제공하며 각종 언어를 지원한다.

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

 

● MySQL 엔진

 

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

옵티마이저가 주로 이룬다.

MySQL은 표준 SQL 문법 (ANSI SQL)을 지원하기 때문에 타 DBMS와 호환된다.

 

 

● 스토리지 엔진

 

MySQL엔진은 요청된 SQL을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하고

실제 데이터를 디스크 스토리지에 저장 혹은 읽어오는 부분은 스토리지엔진이 전담한다.

MySQL서버에서 MySQL엔진은 하나지만 스토리지 엔진은 여러개를 동시에 사용할 수 있다.

 

스토리지 엔진은 다음과 같이 테이블 생성시 지정할 수 있다.

CREATE DATABASE study; --데이터베이스 생성
USE study; -- 데이터베이스 선택
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB; -- INNODB엔진을 사용한 테이블 생성

 

● 핸들러 API

 

MySQL엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데

이것을 핸들러 요청이라 하고 이곳에 쓰이는 API를 핸들러 API라고한다.

SHOW GLOBAL STATUS LIKE 'Handler%';

명령으로 핸들러 작업이 얼마나 있었는지 알 수 있다.

 

 

---

 

2) MySQL 스레딩 구조

 

MySQL서버는 스레드 기반으로 작동하며 크게 포그라운드와 백그라운드 스레드로 구분할 수 있다.

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

으로 현재 스레드들을 알수 있으며

맨 밑에 보이는 thread/sql/one_connection 스레드만 사용자의 요청을 처리하는 포그라운드 스레드이다.

 

백그라운드 스레드의 개수는 MySQL서버 설정에따라 늘어날 수도 작아질 수도 있다.  (여러 스레드가 병렬로 처리 가능)

MySQL 커뮤니터 에디션에서는 전통적으로 사용하고 있는 스레드 모델을 사용하며

엔터프라이즈나 Percona MySQL서버에서는 스레드 풀 모델을 사용할 수 있다.

 

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

 

최소 MySQL서버에 접속된 클라이언트 수만큼 존재하며 주로 각 클라이언트가 요청하는 쿼리 문장을 처리한다.

클라이언트가 작업종료 후 커넥션을 종료하면 스레드는 다시 스레드 캐시로 되돌아가거나 일정 개수의 대기의 스레드가 존재하면

스레드를 종료시킨다. (설정으로 이 개수를 변동가능하다.)

 

해당 스레드는 데이터를 MySQL 데이터 버퍼나 캐시로부터 가져오며 없는 경우 직접 디스크의 데이터 혹은 인덱스 파일로부터

데이터를 읽어와서 작업을 처리한다. (MyISAM테이블의 경우 쓰기 작업도 처리한다.)

 

 

● 백그라운드 스레드

 

MyISAM의 경우 별로 해당이 되지 않지만 InnoDB는 다음과 같은 작업을 백그라운드에서 처리한다.

 

■ 인서트 버퍼를 병합하는 스레드

■ 로그를 디스크로 기록하는 스레드

■ InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드

■ 데이터를 버퍼로 읽어오는 스레드

■ 잠금이나 데드락을 모니터링하는 스레드

 

innodb_write_io_threads , innodb_read_io_threads를 통해 쓰기, 읽기 쓰레드 수를 2개이상 지정할 수 있는데,

읽기 쓰레드는 클라이언트 스레드가 처리하기 때문에 많이 지정할 필요는 없지만 

쓰기 스레드의 경우 백그라운드에서 처리하기 때문에 일반적인 내장 디스크 사용시에는 2~4 정도

DAS, SAN과 같은 스토리지 이용하는 경우 디스크를 최적으로 사용할 수 있을만큼 충분히 설정한다.

 

데이터 읽기작업은 절대 지연되지 않지만, 쓰기작업의 경우 지연(버퍼링) 되어 처리 될 수 있다.

InnoDB는 쓰기작업을 버퍼링해서 일괄처리하는 기능이 탑재되어 있다.

그러나 MyISAM은 그렇지 않고 사용자 스레드가 쓰기 작업까지 처리하도록 설계되어잇다.

 

그래서 InnoDB에서는 INSERT, DELETE, UPDATE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히

저장될 때 까지 기다리지 않아도 되지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.

 

 

 

---

 

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

 

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

 

● 글로벌 메모리 영역

 

글로벌 메모리 영역의 메모리공간은 MySQL 서버가 시작되면서 운영체제로부터 할당된다.

클라이언트 수와 무관하게 하나의 메모리 공간만 할당되며, 2개 이상의 메모리 공간을 할당 받을 수 있다.

이는 모든 스레드에 의해 공유 된다. 대표적인 글로벌 영역은 다음과 같다.

 

■ 테이블 캐시

■ InnoDB 버퍼 풀

■ InnoDB 어댑티브 해시 인덱스

■ InnoDB 리두 로그 버퍼

 

 

● 로컬 메모리 영역

 

세션 메모리 영역이라고 표현하며 MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다.

이는 다른 클라이언트 스레드와 절대 공유되지 않는다.

 

글로벌 영역의 크기는 주의해서  설정하지만 로컬 메모리 영역은 신경쓰지않고 설정하는데,

최악의 경우(희박) MySQL서버가 멈춰 버릴 수 있으므로 적절한 메모리공간을 설정해야한다.

 

로컬 메모리 공간의 또 다른 특징은 각 쿼리의 용도별로

필요할 때만 공간을 할당되고 필요하지 않은 경우 메모리 공간을 할당하지 않는 경우 - 소트 버퍼 혹은 조인 버퍼

커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 경우 - 커넥션 버퍼나 결과 버퍼

쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 경우 - 소트 버퍼나 조인 버퍼

 

대표적인 로컬 메모리 영역은 다음과 같다.

■ 정렬 버퍼

■ 조인 버퍼

■ 바이너리 로그 캐시

■ 네트워크 버퍼

 

 

---

 

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

 

MySQL에서 기본적으로 제공되는 스토리지 엔진 이외에 부가적인 기능을 제공하는 스토리지 엔진이 필요할 수 있는데,

사용자가 직접 스토리지 엔진을 개발하여 플러그인 형태로 사용할 수 있다.

 

MySQL 대부분의 작업은 MySQL 엔진에서 처리되고 데이터 읽기/쓰기 에 대해서는 스토리지 엔진에 의해 처리된다.

MySQL에서는 핸들러라는 단어를 자주 사용하는데, MySQL엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용하게 된다.

SHOW ENGINES;

를 통해 MySQL 서버(mysqld)에서 지원되는 스토리지 엔진을 확인할 수 있다.

 

Support 컬럼에 표시될 수 있는 값은 다음 4가지다.

■ YES:  MySQL 서버에 해당 스토리지 엔진이 포함되어 있고, 사용 가능으로 활성화된 상태

■ DEFAULT: 'YES'와 동일한 상태, 필수 스토리지엔진임을 의미

■ NO: 현재 MySQL 서버에 포함되지 않음을 의미

■ DISABLED: 현재 MySQL서버에 포함되어있지만 파라미터에 의해 비활성화된 상태

 

MySQL에서 플러그인은 준비만 되어있다면 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워넣기만

하면 사용할 수 있다.

 

이외에도 인증 및 전문 검색용 파서와 같은 플러그인도 확인 할 수 있다.

SHOW PLUGINS;

 

 

---

 

5) 컴포넌트

 

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

아래에는 플러그인 단점을 나열해놓았는데 컴포넌트는 이 단점들을 보완하였다.

■ 플러그인은 오직 MySQL서버와 인터페이스 할 수 있고, 플러그인 끼리는 통신할 수 없음

■ 플러그인은 MySQL서버의 변수나 함수를 직접 호출하기때문에 안전하지 않음 (캡슐화 X)

■ 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움

 

 

 

---

 

6) 쿼리 실행 구조

 

SQL 요청 -> 쿼리 파서 -> 전처리기 -> 옵티마이저 -> 쿼리 실행기 -> 스토리지 엔진 -> SQL 결과

 

● 쿼리 파서

 

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조 만들어 내는 작업

기본 문법 오류는 이 과정에서 발견된다.

 

● 전처리기

 

파서 과정에서 만들어진 파서 트리를 기반으로쿼리 문장에 구조적인 문제점 있는지 확인

각 토큰을 테이블 이름, 칼럼 이름, 내장함수와같은 개체를 매핑해 해당 객체의 존재여부와 객체의 접근 권한 등을 확인한다.

실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.

 

● 옵티마이저

 

쿼리 문장을 저렴한 비용으로 빠르게 처리할지를 결정하는 역할, DBMS의 두뇌이다.

 

● 실행 엔진

 

실행 엔진(중간 관리자)과 핸들러(각 업무 실무자)는 손과 발에 비유된다. 

 

옵티마이저가 GROUP BY 를 처리하기 위해 임시 테이블을 사용하기로 결정한 경우

1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청

2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청

3. 읽어온 레코드들을 1번에서 준비한 임시테이블로 저장하라고 다시 핸들러에게 요청

4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어오라고 핸들러에게 다시 요청

5. 최종적으로 실행엔진은 결과를 사용자나 다른 모듈로 넘김

 

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

수행한다.

 

 

● 핸들러(스토리지 엔진)

 

MySQL 서버의  가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당

 

 

---

 

8) 쿼리 캐시

 

쿼리 캐시는 SQL 실행 결과를 메모리에 캐시하고 동일한 SQL쿼리가 실행되면 테이블을 읽지 않고 결과를 반환해주었다.

하지만, 쿼리 캐시는 테이블 데이터 변경시 캐시에 저장된 결과중에서 변경된 테이블과 관련된 것들은 모두 삭제 해야했다.

이에따라 8.0이 되면서 쿼리 캐시는 MySQL서버에서 기능이 완전 삭제되었다.

 

 

---

 

9) 스레드 풀

 

커뮤니티 에디션은 스레드풀 지원을 하지 않는다. 엔터프라이즈 혹은 Percona Server에서 제공하는 스레드 풀 기능을 살펴보자.

 

엔터프라이즈 스레드 풀 기능은 내장되어있지만, Percona Server 스레드 풀은 플러그인 형태로 작동하게 구현되어있다.

커뮤니티 버전에서 이를 사용하려는 경우 동일 버전의 Percona Server에서 스레풀 플러그인 라이브러리 (thread_pool.so파일)을

MySQL 커뮤니티 에디션 서버에 설치(INSTALL PLUGIN명령)한다.

 

스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도

MySQL서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게하여 자원의 소모를 줄이는것이 목적이다.

그러나 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우 쿼리처리가 더 느려질 수 있다.

그래도 제한된 수의 스레드만으로 CPU가 처리하도록 잘 유도한다면 CPU의 프로세스 친화도를 높이고 

운영체제에서는 불필요한 컨텍스트 스위치를 줄여 오버헤드를 낮출 수 있다.

 

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

만약 모든 스레드가 일을 처리하고 있으면 스레드 풀은 작업 스레드를 추가할지 기존스레드가 작업 완료하는 것을 기다릴지를

판단해야 한다.

 

스레드풀의 타이머 스레드는 주기적으로 스레드 그룹의 상태를 체크해서 기존 작업스레드가 정의된 밀리초만큼 처리하지 못하면

새로운 작업 스레드를 추가한다.

만약 응답시간이 민감한 서비스이면 이 정의된 밀리초를 많이 낮춰야하지만 0밀리초는 권장하지않는다. (스레드풀을 사용안하는게 나음)

 

Percona Server의 스레드 풀 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있다.

먼저 시작된 트랜잭션에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있던 잠금이 해제되면서 잠금 경합을 낮춘다.

 

 

---

 

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

 

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

5.7버전까지는 해당 테이블의 구조를 FRM파일 스토어드 프로그램 또한 (.TRN...)과 같은 파일 기반으로 관리하였다.

이러한 파일기반은 비정상 종료시 일관되지 않은 상태로 남지않을 수 있었다.

 

8.0 부터는 테이블 구조정보, 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB 테이블에 저장하도록 개선되었다.

MySQL 서버가 작동하는데 기본적으로 필요한 테이블들을 묶어서 시스템 테이블이라고 하는데

엔진은 InnoDB 스토리지 엔진을 사용하도록 개선되었으며, 시스템 테이블과 데이터 딕셔너리 정보를 모두 mysql DB에 저장하고 있다.

mysql.ibd라는 테이블 스페이스에 저장되므로 특별히 주의하자.

 

8.0부터는 스키마 변경이 완전한 성공 또는 실패로 정리되므로 비정상 종료때문에 발생하는 문제가 존재하지 않게 된다.

 

MyISAM이나 CSV 과 같은 스토리지 엔진의 메타 정보는 SDI 파일을 사용해서 저장한다. (FRM파일과 동일한 역할)