아키텍처

MySQL 서버의 구조는 다음과 같이 나누어 생각할 수 있습니다.
- MySQL 엔진: 머리 역할
- 스토리지 엔진: 손발 역할

 

MySQL 엔진 아키텍처

 

 

MySQL 서버 전체 구조도

 

My SQL 전체 구조

MySQL 서버는 다른 DBMS에 비해 구조가 상당히 독특합니다. 이 구조로 인해 엄청난 혜택이 있기도 하지만, 이상한 곳에서 문제가 되기도 합니다.
MySQL은 다른 DBMS와 마찬가지로 대부분의 프로그래밍 언어에서 접근 방법을 모두 지원하고 있습니다 ( C/C++, PHP, JAVA, Python, Ruby 등 ).

a) MySQL 엔진

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

b) 스토리지 엔진

MySQL 엔진인 요청된 쿼리를 분석하거나 최적화하는 등의 처리를 한다면, 스토리지 엔진은 실제 데이터를 디스크 스토리지로부터 데이터를 읽어오는 부분은 역할을 전담합니다. MySQL 서버에서 MySQL엔진은 하나이지만, 스토리지 엔진은 여러 개일 수 있습니다.

 

* 테이블이 사용할 스토리지 엔진을 지정할 수도 있습니다.

CREATE TABEL test_table (x INT, y INT) ENGINE=INNODB;

 

각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM 스토리지 엔진)나 InnoDB 버퍼 풀(InnoDB 스토리지 엔진)과 같은 기능을 내장하고 있습니다.

* MyISAM vs InnoDB
InnoDB와 MyISAM은 Mysql에서 가장 많이 활용되는 스토리지 엔진입니다.
가장 큰 차이점은 Locking 방식의 차이에 있습니다. Innodb는 특정한 로우를 locking 하는 반면, MyISAM은 Mysql의 테이블 그전체를 locking 합니다. 또한 InnoDB는 트랜젝잭을 제공하지만, MyISAM은 트랜잭션을 제공하지 않습니다.

c) 핸들러 API

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

 

* 핸들러 API를 통해 얼마나 많은 데이터 작업이 있었는지 확인하는 방법

SHOW GLOBAL STATUS LIKE 'Handler%';

 

MySQL 스레딩 구조

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

 

* 실행 중인 스레드 확인 방법

SELECT * FROM performance_schema.threads;

백그라운드 스레드의 개수는 MySQL 서버의 설정에 따라 달라질 수 있습니다. 동일한 이름의 스레드가 2개 이상씩 보이는 것은 MySQL 서버의 설정 내용에 의해 여러 스레드가 동일 작업을 병렬로 처리하는 경우입니다.

a) 포그라운드 스레드 (Client Thread)

포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트 수만큼 존재하며,  각 클라이언트 사용자가 요청하는 쿼리 문장을 처리합니다. 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread cache)로 되돌아갑니다. 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고, 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 합니다. 이 때, 이 스레드 캐시에 유지할 수 있는 최대 스레드 개수는 시스템 변수 thread_cache_size로 설정합니다.

포그라운드 스레드는 데이터를 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리합니다. 스토리지 엔진의 종류에 따라, 포그라운드 스레드 역할 범위가 달라집니다.
- MyISAM: 디스크 쓰기 작업까지 포그라운드 스레드가 처리
- InnoDB: 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리.

b) 백그라운드 스레드

MyISAM의 경우 해당 사항이 없는 부분이지만, InnoDB는 여러가지 작업이 백그라운드로 처리됩니다.
- 인서트 버퍼(Insert Buffer)를 병합하는 스레드
- 로그를 디스크로 기록하는 스레드
- InnoDB 버퍼풀의 데이터를 디스크에 기록하는 스레드
- 데이터를 버퍼로 읽어 오는 스레드
- 잠금이나 데드락을 모니터링하는 스레드

이 중 가장 중요한 것은 로그 스레드(Log thread)와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드(Write thread) 입니다. MySQL 5.5부터는 데이터 데이터 쓰기 스레드와 읽기 쓰레드 개수를 2개 이상 지정할 수 있습니다.

( 시스템 변수 innodb_write_io_threads와 innodb_read_io_threads 조절 )

 

데이터를 읽는 작업은 주로 포그라운드 스레드에서 진행되어 읽기 스레드가 많이 필요하지 않지만, 쓰기 스레드의 경우 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크의 경우 2~4, DAS나 SAN과 같은 스토리지는 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하는 것이 좋습니다.

사용자의 요청을 처리하는 도중, 데이터 쓰기 작업은 지연되어 처리될 수 있지만, 데이터 읽기 작업은 절대 지연될 수 없습니다. 그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 있으며, InnoDB 또한 이러한 방식으로 처리합니다. ( MyISAM은 포그라운드 스레드가 쓰기 작업까지 함께 처리 ) 따라서 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경될 경우, 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 됩니다.

 

메모리 할당과 사용 구조

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분됩니다. 두 가지의 구분 기준은 MySQL 서버 내에 존재하는 많은 스레드가 공유 가능한지 여부입니다.
글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 부여 받습니다. ( 운영체제마다 할당해주는 방식이 차이가 있음. 요청된 메모리 공간을 100% 할당해주는 체제도 있고, 요청된 메모리 공간을 예약해두고 필요할 때 조금씩 할당해주는 체제도 있음 )

a) 글로벌 메모리 영역

일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당됩니다. 필요에 따라 2개 이상의 메모리 공간을 할당 받을 수 있지만, 클라이언트 수와는 무관하고, 모든 스레드에 의해 무조건 공유됩니다.

 

대표적인 글로벌 메모리 영역
- 테이블 캐시
- InnoDB 버퍼 풀
- InnoDB 어댑티브 해시 인덱스
- InnoDB 리두 로그 버퍼

b) 로컬 메모리 영역

로컬 메모리 영역은 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역입니다. 클라이언트가 MySQL 서버에 접속하면 서버에서 클라이언트 커넥션으로부터 요청을 처리하기 위해 스레드를 하나씩 할당하는데, 이 때 클라이언트 스레드가 사용하는 메모리 공간이 로컬 메모리 영역입니다. ( 다른 말로 클라이언트 메모리 영역 ) 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 로컬 메모리 영역을 세션 메모리 영역이라고도 합니다.

로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되어, 절대 공유되어 사용할 수 없습니다. 보통 글로벌 메모리 영역은 주의해서 설정하지만, 로컬 메모리 영역은 크게 신경 쓰지 않고 설정하는데, 최악의 경우에는 MySQL 서버가 메모리 부족으로 멈춰 버릴 수도 있으니 적절한 메모리 공간을 할당하는 것이 중요합니다. ( 물론 가능성은 희박 )

로컬 메모리 영역의 특징 중 하나는 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 안할 수 있습니다. ( 대표적으로 SORT 버퍼나 JOIN 버퍼 ) 물론 커넥션이 열려 있는 동안 계속 할당된 상태로 남아있는 공간도 있습니다. ( 대표적으로 커넥션 버퍼나 결과 버퍼 )

 

대표적인 로컬 메모리 영역
- 정렬 버퍼 (Sort buffer)
- 조인 버퍼
- 바이너리 로그 캐시
- 네트워크 버퍼

 

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

MySQL의 독특한 구조 중 대표적인 것이 바로 플러그인 모델입니다. 여러 가지 스토리지 엔진들이나 검색어 파서 등이 MySQL서버에 플러그인 되어서 사용됩니다.

* 플러그인이란? 

플러그인(plugin)은 호스트 응용 프로그램과 서로 응답하는 컴퓨터 프로그램이며, 특정한 "주문식" 기능을 제공한다.

일반적인 쿼리의 처리 과정을 보면, 쿼리 요청 ↔ MySQL 엔진 처리 ↔ 스토리지 엔진 처리 ↔ 디스크 와 같습니다. 이 때, MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러(Handler)를 통해야 합니다.

* 핸들러란? 

프로그래밍 언어에서는 어떤 기능을 호출하기 위해 사용하는 운전대와 같은 역할을 하는 객체를 핸들러라고 표현한다.
'Handler_'로 시작하는 MySQL서버의 상태 변수는 'MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수'라고 이해하면 된다.

MySQL 엔진에서 MyISAM이나 InnoDB와 같이 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도 MySQL 처리 내용은 대부분 동일하며, 데이터의 읽기/쓰기 영역에서만 차이가 있습니다.

 

아래 쿼리를 통해 MySQL에 포함된 스토리지 엔진을 확인 가능합니다. 이 때, 포함되지 않은 스토리지 엔진은, 추후 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워넣기만 하면 사용할 수 있습니다. ( 플러그인 모델의 장점 )

SHOW ENGINES;

인증 및 전문 검색용 파서와 같은 플러그인도 확인 가능합니다.

SHOW PLUGINS;

 

컴포넌트

플러그인의 단점은 플러그인이 오직 MySQL 서버와만 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없다는 점입니다. 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않습니다. ( 캡슐화 X )

→ 그래서 MySQL v8.0부터는 컴포넌트 아키텍처가 지원됩니다.
( 간단한 사용 예시 )

INSTALL COMPONENT 'file://compnent_validation_password';

 

쿼리 실행 구조

 

MySQL 쿼리 실행 구조

a) 쿼리 파서 (Query parser)

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

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

쿼리 문장의 기본 문법 오류는 이 과정에서 발견됩니다.

 

b) 전처리기 (Preprocessor)

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인합니다. 각 토큰을 테이블 이름이나 칼럼 이름, 또는 내장 함수와 같은 개체에 매핑합니다. 이 때, 객체의 존재 여부나 객체로의 접근 권한 등을 확인합니다.

 

c) 옵티마이저 (Optimizer)

옵티마이저는 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정합니다. ( DBMS의 두뇌 ) 

 

d) 실행 엔진 (Query execution engine)

옵티마이저가 두뇌라면, 실행엔진과 핸들러는 손과 발입니다. 실행 엔진은 옵티마이저가 만든 계획대로 각 핸들러에게 요청을 보내고, 받은 결과를 또 다른 핸들러의 입력으로 연결하는 연결을 합니다.

 

e) 핸들러 (스토리지 엔진) (Handler, Storage engine)

핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고, 디스크로부터 읽어오는 역할을 담당합니다. MyISAM 테이블을 조작하는 경우에는 핸들러가 MyISAM 스토리지가 되고, InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지가 됩니다.

 

쿼리 캐시 (Query Cache)

 

쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보입니다. MySQL 서버에서 쿼리는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당합니다.
하지만 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제(Invalidation)해야 합니다. 이는 심각한 동시 처리 성능 저하를 유발합니다.
결국 MySQL v8.0으로 올라오면서 쿼리 캐시는 mySQL 서버의 기능에서 완전히 제거되고, 관련된 시스템 변수도 모두 제거되었습니다.

 

스레드 풀 (Thread pool)

 

스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적입니다.
( 많은 사람들이 스레드 풀만 설치하면 성능이 그냥 두 배쯤 올라갈 거라고 기대하는데, 스레드 풀이 실제 서비스에서 눈에 띄는 성능 향상을 보여준 경우는 드물다 )

 

InnoDB 스토리지 엔진 아키텍처

 

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

InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장됩니다. 즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻입니다. 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용합니다. 프라이머리 키가 클러스터링 인덱스이기 때문에, 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있습니다.
→ 결과적으로 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정됩니다. ( 다른 보조 인덱스보다 프라이머리키가 선택될 확률 높음 )

* MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다. ( 프라이머리 키가 큰 의미를 지니지 않음 )

 

외래 키 지원

외래 키는 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 자주 있습니다. 그렇다 해도 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있습니다. InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성을 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로, 개발할 때도 외래키의 존재를 유의해야 합니다.

외래키로 인해 데이터 수동 적재 작업이나 스키마 변경 등의 관리 작업에 실패할 수 있습니다. 물론 부모 테이블과 자식 테이블의 관계를 명확히 파악해서 순서대로 작업한다면 문제 없이 실행할 수 있지만, 외래키가 복잡하게 얽혀있는 경우 이 역시 간단하지 않습니다. 뭔가 서비스에 긴급한 조치를 해야할 때는 마음이 더 조급해질 수도 있습니다.
이런 경우, 시스템 변수 foreign_key_checks를 OFF로 설정하면, 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있습니다. 또한, 부가적인 체크들이 사라지기에, 대량의 데이터 적재도 빠르게 할 수 있습니다.
물론 외래 키 체크를 해제 했다고 해서, 부모 테이블과 자식 테이블의 관계가 깨진 상태를 유지해도 되는 건 절대 아닙니다. 외래키를 해제하고 부모 테이블의 데이터를 제거했다면, 자식 테이블에서도 적절하게 데이터를 삭제해주어야 합니다. ( 저는 이를 실무에서 많이 활용했습니다 )

* foreign_key_checks는 글로벌 범위와 세션 범위 모두에 존재하기에, 꼭 세션 범위의 시스템 변수를 조절해줘야 합니다.

SET SESSION foreign_key_checks=OFF;

* MyISAM이나 MEMORY 테이블에서는 외래 키를 지원하지 않는다.

 

MVCC (Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능입니다. MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기 제공에 있습니다. MV(Mulit Version)은 하나의 레코드에 대해 여러 개의 버전이 관리된다는 의미다.

 

(예) 데이터를 INSERT 후, UPDATE하는 상황
→ 데이터를 INSERT하면, 값이 InnoDB 버퍼 풀과 디스크에 모두 저장된다.
→ 데이터를 UPDATE하면, 변경 후 값이 InnoDB 풀에 저장되고, 변경 전 값은 undo 로그에 남게 된다.
→ 디스크에 있던 데이터 값은 업데이트가 되었을 수도 있고 아닐 수도 있다. (COMMIT 이나 Rollback을 아직 안했기 때문)
→ 이 때, 이 데이터를 SELECT 하면, 업데이트된 값을 불러올까 업데이트 되지 않은 값을 불러올까?

 

정답은 MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준에 따라 다르다는 것입니다.

 

READ_UNCOMMITED의 경우, 아직 커밋되지 않은 데이터(변경 후의 값)를 읽어오는 반면, READ_COMMITED의 경우, 커밋이 된 데이터만 읽기에 변경 전의 값을 읽어 옵니다.

 

즉, 이런 식으로 한 테이블에 대해 여러가지 버전이 관리되는 것이 MVCC입니다. 데이터에 따라 관리해햐하는 언두 로그가 무한히 많아질 수 있습니다. ( 이 경우 성능이 급격히 하락 ) 그래서 언두 영역의 데이터는 이를 필요로 하는 트랜잭션이 더는 없을 때 삭제됩니다. 따라서 개발자는 트랜잭션이 시작되었다면 가능한 한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋습니다.

 

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

 

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

격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMITED나 READ_COMMITED, REPEATABLE_READ 수준인 경우, INSERT와 연결되지 않는 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업와 관계 없이 잠금을 대기하지 않고 바로 실행됩니다. 이를 '잠금 없는 일관된 읽기'라고 표현합니다.
(예) UPDATE를 하는 도중 SELECT : 언두 로그에 있는 파일 읽어서 반환

 

자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 작므이 교착 상태에 빠지지 않았는지 체크 하기 위해 잠금 대기 목록을 그래프 형태(Wait-for-List)로 관리합니다. 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사하여, 교착 상태에 빠진 트랜잭션들을 찾으면 그 트랜잭션 중 하나를 강제 종료합니다. 어떤 트랜잭션을 종료할 것인지를 결정하는 건 언두 로그의 양이다. ( 언두 로그가 적다는 건 롤백할 데이터가 더 적다는 의미 → 부하가 적다 )

 

* 참고

InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금은 볼 수가 없어서 데드락 감지가 불확실할 수도 있는데, innodb_lock_table 시스템 변수를 활성화하면 InnoDB 스토리지 엔진 내부에서도 테이블 잠금까지 감지할 수 있게 된다. ( 특별한 이유가 없다면 innodb_lock_table 시스템 변수를 활성화하자 )

일반적인 서비스에서 데드락 감지는 큰 부담이 되지 않습니다. 다만, 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금 개수가 많아지면, 데드락 감지 스레드가 느려집니다. 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업하지 못하고, 서비스에 악 영향을 끼치게 됩니다.

이런 문제점을 해결하기 위해 MySQL 서버는 innodb_deadlock_detect 시스템 변수를 제공하며, 이를 OFF로 설정할 경우 더 이상 데드락 감지 스레드는 작동하지 않게 됩니다. 다만, 이 경우 데드락이 발생했을 때 누군가의 중재가 없이 무한정 대기 상태에 들어갈 수 있습니다. innodb_lock_wait_timeout 시스템 변수를 활성화하면, 이런 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환할 수 있게 됩니다.

 

자동화된 장애 복구

 

InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러 가지 메커니즘이 탑재돼 있습니다. 사실 InnoDB 스토리지 엔진은 매우 견고해서 데이터 파일이 손상되거나 MySQL 서버가 시작되지 못하는 경우는 거의 발생하지 않습니다. 하지만 MySQL 서버와 무관하게 디스크난 서버 하드웨어 이슈로 InnoDB 스토리지 엔진이 자동으로 복구를 못하는 경우가 있습니다. 일단 기본적으로 InnoDB 데이터 파일은 MySQL 서버가 시작될 때 항상 자동으로 복구를 수행합니다. 이 단계에서 자동으로 복구될 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 서버를 종료시킵니다. ( 이 경우, 시스템 변수 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 한다 )

 

InnoDB 버퍼 풀

 

InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간입니다. 또한, 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다. 일반적인 어플리케이션에서는 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업이 발생하게 되는데, 버퍼 풀이 이런 작업들을 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있습니다.

 

MyISAM 스토리지 엔진 아키텍처

 

키 캐시

InnoDB의 버퍼 풀과 비슷한 역할을 하는 것이 MyISAM의 키 캐시( Key Cache )입니다. 하지만 이름 그대로 ISAM 키 캐시는 인덱스만을 대상으로 작동하며, 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 합니다. 키 캐시가 얼마나 효율적으로 작동하는지는 '키 캐시 히트율'을 통해 확인할 수 있습니다.  일반적으로 키 캐시를 이용한 쿼리의 비율( 히트율 )을 99% 이상으로 유지하라고 권장합니다. 만약 99% 미만이면 키 캐시( key_buffer_size )를 조금 더 크게 설정하는 것이 좋습니다.

 

운영체제의 캐시 및 버퍼

MyISAM 스토리지 엔진은 테이블의 데이터에 대해서는 디스크로부터 I/O를 해결해 줄 만한 어떠한 캐시나 버퍼링 기능도 가지고 있지 않습니다. 물론 대부분의 운영체제에는 디스크로부터 읽고 쓰는 파일에 대한 캐시나 버퍼링 메커니즘을 탑재하고 있기 때문에 MySQL 서버가 요청하는 디스크 읽기 작업을 위해 매번 디스크이 파일을 읽지 않습니다. 다만, 운영체제의 캐시 공간은 남는 메모리를 사용하는 것이 기본 원칙이기 때문에 만약 다른 어플리케이션이 메모리를 모두 사용 중이라면 쿼리 처리 속도를 급격히 낮아집니다.

 

데이터 파일과 프라이머리 키 구조

MyISAM 스토리지 테이블은 프라이머리 키에 의해 클러스터링 없이 데이터 파일이 힙 공간처럼 활용됩니다. 즉, MyISAM 테이블 레코드는 프라이머리 키 값과 무관하게 INSERT 되는 순서대로 데이터 파일에 저장됩니다. MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주솟값을 가지는데, 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가집니다.

 

MySQL 로그 파일

 

에러 로그 파일

- MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
- 마지막으로 종료할 때, 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지
- 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
- 비정상적으로 종료된 커넥션 메시지
- InnoDB의 모니터링 또는 상태 조회 명령의 결과 메시지
- MySQL의 종료 메시지

 

제너럴 쿼리 로그 파일 (General query log)

- 시간 단위로 실행했던 쿼리의 내용이 모두 기록

 

슬로우 쿼리 로그 파일 (Slow query log)

- 시스템 변수 long_query_time 에 설정한 시간 이상이 소요된 쿼리를 기록.

- 일반적으로 슬로우 쿼리 또는 제너럴 로그 파일의 내용이 상당히 많아서 직접 쿼리를 하나씩 검토하기에는 시간이 너무 많이 걸리거나 어느 쿼리를 집중적으로 튜닝해야 할지 식별하기가 어려울 수 있습니다. 이런 경우, Percona에서 개발한 Percona Toolkit의 pt-query-digest 스크립트를 이용하면 쉽게 빈도난 처리 성능별로 쿼리를 정렬해서 볼 수 있습니다.

pt-query-digest --type='slowlog' mysql-slow.log > parsed_mysql-slow.log

pt-qeury-digest 사용 예시