4.1 - MySQL 엔진 아키텍처
MySQL 서버는 다른 DBMS에 비해 구조가 상당히 독특하다.
•
독특한 구조 덕에 다른 DBMS에서는 가질 수 없는 엄청난 혜택을 누릴 수 있다.
•
반대로 다른 DBMS에서는 문제되지 않는 것들이 가끔 문제가 되기도 한다.
4.1.1 MySQL의 전체 구조
MySQL 서버 전체 구조
MySQL은 일반 사용 RDBMS와 같이 대부분의 프로그래밍 언어로부터 접근 방법을 모두 지원한다.
4.1.1.1 MySQL 엔진
MySQL엔진은 아래 두 가지 요소가 중심을 이룬다.
•
클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기
•
쿼리의 최적화된 실행을 위한 옵티마이저
또한 MySQL은 표준 SQL문법을 지원하기 때문에 타 DBMS와 호환되어 실행될 수 있다.
4.1.1.12 스토리지 엔진
실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다.
•
MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.
•
테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진에서 처리된다.
mysql> CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;
SQL
복사
◦
해당 테이블에서 CURD 등의 작업이 발생하면 InnoDB 스토리지 엔진이 처리를 담당한다.
•
각 스토리지 엔진은 키 캐시(MyISAM)나 InnoDB버퍼 풀(InnoDB)과 같은 기능을 내장하고 있다.
4.1.1.3 핸들러 API
•
MySQL 엔진의 쿼리 실행기서 데이터를 쓰고/읽어야 할 때 각 스토리지 엔진에 쓰기/읽기를 요청
◦
이러한 요청을 핸들러(Handler) 요청이라 하고, 여기서 사용되는 API를 핸들러 API라고 한다.
•
InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고받는다.
•
핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지는 아래 명령어로 확인 가능
mysql> SHOW GLOBAL STATUS LIKE 'Hnadler%';
SQL
복사
4.1.2 MySQL 스레딩 구조
MySQL의 스레딩 모델
MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 작동된다.
•
크게 포그라운드(Foreground)와 백그라운드(Background) 스레드로 구분된다.
•
아래 명령어를 통해 실행 중인 스레드의 목록을 확인할 수 있다.
mysql> SELECT thread_id, name, type, processlist_user, processlist_host
FROM performance_schema, threads, ORDER BY type, thread_id;
SQL
복사
•
thread/sql/one_connection스레드가 실제 사용자 요청을 처리하는 포그라운드 스레드
•
백그다운드 스레드의 개수는 MySQL 서버의 설정내용에 따라 가변적일 수 있다.
◦
동일한 이름의 스레드가 2개 이상인 이유는 설정에 따라 여러 스레드가 동일 작업을 병렬적으로 처리하는 경우다.
•
스레드 풀과 전통적인 스레드 모델의 가장 큰 차이점은 포그라운드 스레드와 커넥션의 관계이다.
◦
전통적 스레드 모델은 커넥션별 포그라운드 스레드가 하나씩 생성/할당 됨
◦
스레드 풀에서는 커넥션과 포그라운드 스레드는 1:1 관계가 아닌 하나의 스레드가 여러 커넥션 요청을 전담.
4.1.2.1 포그라운드 스레드(클라이언트 스레드)
•
포그라운드 스레드는 최소 MySQL 서버에 접속된 클라이언트 수만큼 존재한다.
◦
주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
◦
사용자가 작업을 마치면 스레드 캐시로 되돌아 간다.
◦
스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 종료시켜 일정 개수만을 유지한다.
▪
최대 스레드 개수는 thread_cache_size시스템 변수로 설정한다.
•
포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져온다.
◦
버퍼나 캐시에 없는 경우 직접 디스크의 데이터나 인덱스 파일로 부터 데잍를 읽어와 작업을 처리한다.
◦
MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리한다.
▪
MyISAM도 지연된 쓰기가 있지만 일반적인 방식은 아니다.
◦
InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.
•
MySQL에서 사용자 스레드와 포그라운드 스레드는 똑같은 의미로 사용된다.
4.1.2.2 백그라운드 스레드
•
InnoDB는 다음과 같이 여러 가지 작입이 백 그라운드로 처리된다.
> MyISAM의 경우 별로 해당 사항이 없는 부분이다.
◦
인서트 버퍼(Insert Buffer)를 병합하는 스레드
◦
로그를 디스크로 기록하는 스레드
◦
InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
◦
데이터를 버퍼로 읽어 오는 스레드
◦
잠금이나 데드락을 모니터링하는 스레드
•
이 중 가장 중요한 것은 로그 스레드와 버퍼 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드일 것이다.
•
MySQL 5.5 버전부터 데이터 쓰기/읽기 스레드의 개수를 2개 이상 지정할 수 있게 되었다.
◦
innodb_write_io_threads, innodb_read_io_threads를 통해서 스레드 개수를 설정한다.
•
InnoDB에서도 데이터를 읽는 작업은 주로 클리이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요가 없다.
•
반면 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크를 사용할 때는 2~4 정도가 적절하다.
◦
DAS/SAN과 같은 스토리지를 사용할 때는 디스크를 최적으로 사용할 수 있을만큼 충분히 설정하는것이 좋다.
•
사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다.
•
일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있다.
◦
InnoDB또한 이러한 방식으로 처리한다.
◦
MyISAM의 경우 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계되어있다.
•
때문에 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 저장될 때까지 기다리지 않아도 된다.
◦
하지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.
4.1.3 메모리 할당 및 사용 구조
MySQL의 메모리 사용 및 할당 구조
MySQL에서 사용되는 메모리 영역은 크게 글로벌 메모리 / 로컬 메모리 영역으로 구분할 수 있다.
•
글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되며 운영체제로 부터 할당받는다
◦
운영체제 종류에 따라 모두 할당 받거나, 미리 예약해두고 필요할 때마다 조금씩 할당하는 경우가 있다.
◦
운영체제의 메모리 할당 방식은 복잡하기 때문에 MySQL 서버가 정확한 메모리 양을 측정하기는 쉽지 않다.
◦
그냥 단순히 MySQL의 시스템 변수로 설정해 둔 만큼 운영체제로 부터 메모리를 할당받는다고 생각해도 된다.
4.1.3.1 글로벌 메모리 영역
일반적으로 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당된다.
단, 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있지만 클라이언트 스레드 수와는 무관하고, 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유된다.
•
대표적인 글로벌 메모리 영역
◦
테이블 캐시
◦
InnoDB 버퍼 풀
◦
InnoDB 어댑티브 해시 인덱스
◦
InnoDB 리두 로그 버퍼
4.1.3.2 로컬 메모리 영역
•
세션 메모리 영역이라고도 표현한다.
◦
MySQL 서버와 커넥션을 세션이라고 하기 때문에 로컬 메모리 영역을 세션 영역이라고도 표현한다.
•
MySQL 서바상 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역
•
대표적으로 커넥션 버퍼와 정렬(소트) 버퍼 등이 있다.
•
클라리언트가 MySQL 서버에 접속하면 클라이언트 커넥션으로부터의 요청을 처리하기 위해 스레드 하나씩 할당한다.
◦
이때 사용하는 메모리 공간을 클라이언트 메모리 영역이라 한다.
•
로컬 메모리는 각 클라리언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다.
•
일반적으로 소트 버퍼와 같은 로컬 메모리 영역은 크게 신경쓰지 않고 설정한다.
◦
최악의 경우(가능성은 희박)에는 MySQL 서버가 메모리 부족으로 멈춰버릴 수 있다.
◦
때문에 적절한 메모리 공간을 설정하는 것이 중요하다.
•
로컬 메모리 공간의 또 한 가지 중요한 특징은 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차 하지 않을 수도 있다는 점이다.
◦
대표적으로 소트 버퍼나 조인 버퍼와 같은 공간이 그러하다.
◦
커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간 = 커넥션 버퍼나 결과 버퍼
◦
쿼리를 실행하는 순간에만 할당 했다가 다시 해제하는 공간 = 소트 버퍼나 조인 버퍼
•
대표적인 로컬 메모리 영역
◦
정렬 버퍼
◦
조인 버퍼
◦
바이너리 로그 캐시
◦
네트워크 버퍼
4.1.4 플러그인 스토리지 엔진 모델
MySQL 플러그인 모델
MySQL의 독특한 구조 중 대표적인 것이 바로 플러그인 모델
•
플러그인 해서 사용할 수 있는 것이 스토리지 엔진만 있는 것은 아님
•
전문 검색 엔진을 위한 검색어 파서(인덱싱할 키워드를 분리해내는 작업)도 플러그인 형태로 개발해서 사용 가능
•
사용자의 인증을 위한 Native Authentication/Caching SHA-2 Authentication 등 모두 플러그인으로 구현되어 제공 됨
•
사용자의 요구 조건을 만족시키기 위해 기본적으로 제공되는 스토리지 엔진 이외에 부가적인 기능을 더 제공하는 스토리지 엔진이 필요할 수 있다.
◦
이러한 요건을 기초로 다른 전문 개발 회사 또는 사용자가 직접 스토리지 엔진을 개발하는 것도 가능하다.
MySQL 엔진과 스토리지 엔진의 처리 영역
•
MySQL에서 쿼리가 실행되는 과정을 크게 나눈다면 대부분의 작업이 MySQL 엔진에서 처리되고 데이터 읽기/쓰기 작업만이 스토리지 엔진에 의해 처리된다.
◦
사용자가 스토리지 엔진을 만든다고 하더라도 DBMS 전체가 아닌 일부분만 스행하는 엔진을 만든다는 의미
•
각 처리 영역에서 데이터 읽기/쓰기 작업은 대부분 1건의 레코드 단위로 처리된다.
•
핸들러(Handler)
◦
프로그래밍 언어에서는 어떤 기능을 호출하기 위해 사용하는 운전대와 같은 역할을 하는 객체를 핸들러라고 표현한다.
◦
MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용하게 된다.
▪
데이터를 읽기/쓰기 할때 반드시 핸드러를 통해야 한다.
•
MyISAM/InnoDB와 같이 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도 처리 내용은 대부분 동일하며, 단순히 데이터 읽기/쓰기 영역의 처리만 차이가 있을 뿐이다.
•
실질적인 GROUP BY나 ORDER BY 등 복잡한 처리는 스토리지 엔진이 아닌 MySQL 엔진의 처리 영역인 쿼리 실행기에서 처리된다.
•
하나의 쿼리 작업은 여러 하위 작업으로 나뉘고, MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다.
•
MySQL 서버에서 지원되는 스토리지 엔진의 종류는 아래 명령어를 통해 확인할 수 있다.
mysql> SHOW ENGINES;
+--------------------+---------+-------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+-------------------------+--------------+------+------------+
SQL
복사
◦
Support 칼럼에 표시될 수 있는 4가지 항목
▪
YES = 서버에 해당 스토리지 엔진이 포함돼 있고, 사용 가능으로 활성화된 상태
▪
DEFAULT = YES와 동일한 상태지만 필수 스토리지 엔진임을 의미
▪
NO = 현재 서버에 포함되지 않았음을 의미함
▪
DISABLED = 현재 서버에는 포함됐지만 파라미터에 의해 비활성화된 상태
•
MySQL 서버에서 설치되어있는 플러그인은 아래 명령어로 확인할 수 있다.
mysql> SHOW PLUGINS;
SQL
복사
4.1.5 컴포넌트
MySQL 8.0부터는 기존의 플러그인 아키텍처를 대체하기 위한 컴포넌트 아키텍처가 지원된다.
•
플러그인은 몇 가지 단점이 있는데 컴포넌트는 이러한 단점들을 보완해서 구현되었다.
◦
플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없음
◦
플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음 (캡슐화 X)
◦
플로그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움
•
플러그인과 마찬가지로 컴포넌트도 설치하면서 새로운 시스템 변수를 설정해야 할 수도 있으니 컴포넌트를 사용하기 전 매뉴얼을 살펴보도록 하자!
4.1.6 쿼리 실행 구조
쿼리 실행 구조
4.1.6.1 쿼리 파서
사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어내는 작업을 의미
쿼리 문장의 기본 문법 오류는 이 과정에서 발견되어 사용자에게 오류메시지를 전달
•
토큰 = MySQL이 인식할 수 있는 최소한의 어휘나 기호
4.1.6.2 전처리기
파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다.
토큰을 테이블 이름/칼럼 이름/내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부/접근 권한 등을 확인하는 과정
실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.
4.1.6.3 옵티마이저
옵티마이저란 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하 는 역할을 담당
DBMS의 두뇌에 해당한다고 볼 수 있음
4.1.6.4 실행 엔진
옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다.
다음은 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하는 과정
1.
실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
2.
다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
3.
읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
4.
데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
5.
최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할 수행
4.1.6.5 핸들러(스토리지 엔진)
MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당
4.1.7 복제
복제에 관해서는 16장에서 자세히 다뤄진다.
4.1.8 쿼리 캐시
서버에서 쿼리 캐시는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당했다.
•
쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시, 동일 SQL 요청시 즉시 반환하기에 성능이 좋아보였다.
◦
테이블 데이터 변경시캐시에 저장된 결과 중 변경된 테이블과 관련된 것을은 모두 삭제해야 했다.
◦
이는 심각한 성능저하를 유발하고, 동시 처리 성능 저하와 버그의 원인이 되기도 하였다.
결국 MySQL 8.0에서는 해당 쿼리 캐시 기능은 완전히 제거되었다.
4.1.9 스레드 풀
•
MySQL 서버 엔터프라이즈 에디션은 스레드 풀 기능을 제공하지만 커뮤니티에디션은 스레드 풀 기능을 지원하지 않는다.
•
우선 MySQL 엔터프라이즈 스레드 풀 기능은 MySQL 서버 프로그램에 내장돼 있지만 Percona Server의 스레드 풀은 플러그인 형태로 작동하게 구현돼 있다는 차이점이 있다.
•
스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여 동시 처리되는 요청이 많다 하더라도 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게해 자원 소모를 줄이는게 목적
◦
실제 서비스에서 눈에 띄는 성능 향상을 보여준 경우는 드물다
◦
스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우 쿼리 처리가 더 느려지는 사례도 발생할 수 있다.
◦
물론 제한된 수의 스레드만으로 CPU가 처리하도록 적절히 유도한다면 프로세서 친화도를 높히고, 불필요한 컨텍스트 스위치를 줄여 오버헤드를 낮출 수 있다.
•
Percona Server의 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성한다.
◦
thread_pool_size시스템 변수를 통해 조정 가능
◦
하지만 일반적으로 CPU 코러의 개수와 맞추는것이 프로세서 친화도를 높이는 데 좋다.
◦
이미 스레드 풀이 처리 중인 작업이 있는 경우에는 thread_pool_oversubscribe 시스템 변수(기본값은 3)에 설정된 개수만큼 추가로 더 받아들여서 처리한다.
▪
이 값이 너무 크면 스케줄링해야 할 스레드가 많아져서 스레드 풀이 비효율적으로 작동할 수도 있다.
•
스레드 그룹의 모든 스레드가 일을 처리하고 있다면 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드를 추가할지, 아니면 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부를 판단해야 한다.
◦
타이머 스레드는 주기적으로 스레드 구룹의 상태를 체크해서 thread_pool_stall_limit시스템 변수에 설정된 개수를 넘어설 수 없다.
→ 즉, 모든 스레드 그룹의 스레드가 각자 작업을 처리하고 있는 상태에서 새로운 쿼리 요청이 들어오더라도 스레드 풀은 thread_pool_stall_limit시간 동안 기다려야만 새로 들어온 요청을 처리할 수 있다는 뜻!
◦
응답시간에 민감한 서비스라면 값을 적절히 낮춰서 설정
◦
0에 가까이 사용한다면 스레드 풀을 사용하지 않는 편이 나을 것!
•
선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다.
◦
먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있던 잠금이 빨리 해재되고 잠금 경합을 낮춰 전체적인 처리 성능이 향상됨
4.1.10 트랜잭션 지원 메타데이터
데이터베이스 서버에서 테이블 구조 정보와 스토어드 프로그램 등 정보를 데이터 딕셔너리/메타데이터 라고 한다.
5.7 이전 버전까지는 일부 스토어드 프로그램 또한 파일 기반으로 관리 했었다.
•
파일 기반의 메타데이터 생성/변경 작업이 트랜잭션을 지원하지 않아, 작업 도중 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남는 문제가 발생
•
많은 사용자들이 이런 형상을 가르켜 DB나 테이블이 깨졌다라 표현
8.0 부터는 이러한 문제점 해결을 위해 테이블 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB의 테이블에 저장하도록 개선됐다.
•
MySQL 서버가 작동하는데 필요한 테이블을 묶어 시스템 테이블이라 한다.
•
대표적으로 사용자의 인증과 권한에 관련된 테이블이 존재한다.
•
시스템 테이블과 데이터 딕셔너리 정보를 모두 모아 mysql DB에 저장한다.
•
때문에 mysql.ibd 파일은 다른 ibd 파일과 함께 특별히 주의하도록 한다.
InnoDB 스토리지 엔진을 사용하는 테이블은 메타 정보가 InnoDB 테이블 기반의 딕셔너리에 저장되지만 MyISAM이나 CSV등과 같은 스토리지 엔진의 메타 정보는 여전히 저장할 공간이 필요하다.
4.2 InnoDB 스토리지 엔진 아키텍처
InnoDB의 구조
InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔징 중 거의 유일하게 레코드 기반의 잠금을 제공한다.
•
때문에 높은 동시성 처리가 가능하고 안정적이고 성능이 뛰어나다.
4.2.1 프라이머리 키에 의한 클러스터링
InnoDB의 모든 테이블은 기봊거으로 프라이머리 키를 기준으로 클러스터링되어 저장된다.
•
프라이머리 키 값의 순서대로 디스크에 저장된다.
•
모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
•
클러스터링 인덱스이기 때문에 이를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.
•
결과적으로 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정된다.
◦
쿼리의 실행 계획에서 다른 보조 인덱스보다 프라이머리 키가 선택될 확률이 높다.
오라클의 DBMS의 IOT와 동일한 구조가 InnoDB에서는 일반적인 테이블 구조가 된다.
MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다.
•
MyISAM 테이블의 프라이머리 키를 포함한 모든 인덱스는 물리적인 레코드의 주소 값을 가진다.
4.2.2 외래 키 지원
외래 키에 대한 지원은 InnoDB스토리지 엔진 레벨에서 지원하는 기능
•
MyISAM이나 MEMORY 테이블에서는 사용할 수 없다.
InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하다.
•
변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크가 필요하다.
◦
잠금이 여러 테이블로 전파됨
◦
그로 인해 데드락이 발생할 때가 많음
◦
때문에 개발할 때도 외래 키의 존재에 주의하는 것이 좋다.
수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업이 실패할 수 있다.
•
서비스에 문제가 있어서 긴급하게 뭔가 조치를 해야 하는데 문제가 발생한다면?
◦
foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.
▪
해당 변수는 GLOBAL과 SESSION 모두로 설정 가능한 변수다. 그래서 이 런 작업을 할 때는 다음과 같이 반드시 현재 작업을 실행하는 세션에서만 외래 키 체크 기능을 멈추게 해야 한다.
mysql> SET foreign_key_checks=OFF;
--// 작업 실행
mysql> SET foreign_key_checks=ON;
Plain Text
복사
◦
외래 키 체크를 일시적으로 해제했다고 해서 부모와 자식 테이블 간의 관계가 깨진 상태로 그대로 유지 해도 된다는 것을 의미하지는 않는다.
4.2.3 MVCC(Multi Version Concurrency Control)
일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능
•
MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 것
•
InnoDB는 언두 로그를 이용해 이 기능을 구현함
•
여기서 멀티 버전이라 함은 하나의 레코드에 여러 버전이 동시에 관리된다는 의미
mysql> INSERT INTO member(m_id, m_name, m_area) VALUES(12, '홍길동', '서울');
mysql> COMMIT;
Plain Text
복사
해당 INSERT문이 실행된다면 아래 그림과 같은 상태로 바뀔 것이다.
mysql> UPDATE member SET m_area='경기';
Plain Text
복사
해당 UPDATE문을 싱행한다면 커밋 여부과 관계없이 InnoDB의 버퍼 풀은 새로운 값인 ‘경기’로 업데이트된다.
그리고 디스크의 데이터 파일에는 체크포인트나 InnoDB의 Write 스레드에 의해 새로운 값으로 업데이트돼 있을 수도 아닐 수도 있다.
•
InnoDB가 ACID를 보장하기 때문에 일반적으로는 InnoDB의 버퍼 풀과 데이터 파일은 동일한 상태라고 가정해도 무방하다
그렇다면 해당 상태에서 member 테이블을 SELECT 한다면 어떤 데이터를 검색하게 될까?
답은 MySQL 서버의 격리 수준에 따라 다르다!
•
READ_UNCOMMITTED인 경우 InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환
•
READ_COMMITTED 이상의 격리 수준인 경우 언두 영역의 내용을 가져온다.
이러한 과정을 DBMS에서는 MVCC라고 표 현한다. 즉, 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조다.
•
트랜잭션이 길어지면 언두 영역이 저장되는 테이블스페이스의 공간이 많이 늘어나는 상황이 발생할 수 있다!
•
커밋이 된다고 언두 데이터가 삭제되는 것이 아닌 해당 데이터를 필요로 하는 트랜잭션이 더는 없을 때 비로소 삭제된다!
4.2.4 잠금 없는 일관된 읽기(Non-Locking Consistent Read)
InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.
•
잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다!
•
격리 수준이 SERIALIZABLE이 아닌 경우 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다.
•
특정 사용자가 레코드 변경 후 커밋하지 않아도 언두 로그를 통해 검색을 수행한다.
•
이를 잠금 없는 일관된 읽기 라고 한다.
•
트랜잭션이 시작됐다면 가능한 한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.
4.2.5 자동 데드락 감지
InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다.
•
데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아 그중 하나를 강제 종료한다.
•
트랜잭션 강제 종료 기준은 언두 로그를 적게가진 트랜잭션이 기준
•
InnoDB의 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금은 볼 수가 없어 데드락 감지가 불확실
◦
innodb_table_locks 시스템 변수를 할성화 하면 InnoDB 스토리지 엔진 내부의 레코드 잠금 뿐 아니레 테이블 레벨의 잠금 까지 감지 가능
▪
특별한 이유가 없다면 활성화
•
동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려짐
◦
처리가 느려지면 다른 작업을 처리중인 스레드도 대기해야 하기 때문에 서브스에 악영향
◦
더 많은 양의 CPU 자원을 소모할 수 있음
◦
innodb_deadlock_detech를 OFF로 설정하면 데드락 감지 스레드가 작동하지 않음
▪
다만 이 경우 데드락 감지가 이뤄지지 않기 때문에 무한정 대기가 발생
▪
때문에 innodb_lock_wait_timeout을 통해 초단위로 잠금을 획득하지 못하면 쿼리가 자동으로 실패하게 설정한다.
4.2.6 자동화된 장애 복구
InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러 가지 메커니즘이 탑재돼 있다.
InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다.
•
이 단계에서 자동으로 복구될 수 없는 손상이 있다면 자동복구를 멈추고 서버는 종료된다.
이때 innodb_force_recovery 설정값을 1~6까지 변경하며 복구를 수행할 수 있고, 값이 커질 수록 심각한 상황이여서 데이터의 손실 가능성이 커진다.
1.
InnoDB의 테이블스페이스의 데이터나 인덱스 페이지에 손상된 부분이 발견돼도 무시하고 MySQL 서버를 시작한다.
a.
로그 파일에 Database page corruption on disk or failed 메시지가 출력될 때는 대부분 이 경우에 해당한다.
b.
덤프를 통해서 데이터베이스를 다시 구축하는게 좋다.
2.
이 복구 모드에서는 백그라운드 스레드 가운데 메인 스레드를 시작하지 않고 MySQL 서버를 시작한다.
a.
메인 스레드가 언두 데이터를 삭제하는 과정에서 장애가 발생한다면 이 모드로 복구하면 된다.
3.
해당 모드에서는 커밋되지 않은 트랜잭션의 작업을 롤백하지 않고 그대로 놔둔다.
a.
서버가 시작되면 덤프를 이용해 데이터베이스를 재구축하는게 좋다.
4.
해당 모드에서는 InnoDB 스토리지 엔진이 인서트 버퍼의 내용을 무시하고 강제로 MySQL이 시작되게 한다.
a.
인서트 버퍼는 실제 데이터와 관련된 부분이 아니라 덤프 후 재구축 하면 손실없이 복구할 수 있다.
5.
InnoDB 엔진이 언두 로그를 모두 무시하고 MySQL을 시작할 수 있다.
a.
서버가 종료되던 시점에서 커밋되지 않았던 작업도 모두 커밋된 것처럼 처리되므로 실제로는 잘못된 데이터가 데이터베이스에 남는 것이라고 볼 수 있다.
6.
InnoDB 엔진은 리두 로그를 모두 무시한 채로 서버를 시작한다.
a.
마지막 체크포인트 시점의 데이터만 남는다고 볼 수 있다.
4.2.7 InnoDB 버퍼 풀
InnoDB의 가장 핵심적 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리 캐시해 두는 공간이다.
쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이한다.
데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다.
4.2.7.1 버퍼 풀의 크기 설정
버퍼 풀은 단순하게 설정해서 되는 값이 아니며, 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정해야 한다.
MySQL 서버 내에서 메모리를 필요로 하는 부분은 크게 없다 But. 독특한 경우 레코드 버퍼가 상당히 메모리를 사용한다.
•
레코드 버퍼는 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간
•
커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 꽤 많이 필요할 수 있음
•
해당 공간은 동적으로 해제되기도 하므로 정확히 필요한 메모리 공간의 크기 계산 불가
◦
MySQL 5.7 부터는 버퍼 푸의 크기를 동적 조절이 가능하게 됨
•
운영체제의 전체 메모리 공간이 8G 미만이라면 50% 정도만 버퍼 풀로 설정
•
그 이상이라면 InnoDB 버퍼 풀의 크기를 50%에서 시작해 조금씩 올려가며 최적점을 찾는다.
•
InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 동적으로 변경 할 수 있다.
◦
다만 줄이는건 시스템 영향도가 매우 크다.
•
innodb_buffer_pool_instances 변수를 통해서 버퍼 풀을 여러 개로 분리해서 관리할 수 있는데, 각 버퍼 풀을 버퍼 풀 인스턴스라고 표현한다.
◦
버 퍼 풀 인스턴스의 개수는 8개로 초기화되지만 전체 버퍼 풀을 위한 메모리 크기가 1GB 미만이면 버퍼 풀 인스턴스는 1개만 생성된다.
◦
메모리가 크다면 버퍼 풀 인스턴스당 5GB 정도가 되게 인스턴스 개수를 설정하는 것이 좋다.
4.2.7.2 버퍼 풀의 구조
InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이 지를 읽어서 각 조각에 저장한다.
•
LRU(Least Recently Used) 리스트와 플러시(Flush) 리스트, 그리고 프리(Free) 리스트라 는 3개의 자료 구조를 관리한다.
◦
프리 리스트는 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록
◦
사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다.
버퍼 풀 관리를 위한 LRU 리스트 구조
•
MySQL에서 사용하는 LRU는 LRU와 MRU(Most Recently Used) 리스트가 결합된 형태
•
‘Old 서브리스트’ 영역은 LRU 에 해당하며, ‘New 서브리스트’ 영역은 MRU 정도로 이해하면 된다.
•
LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것
InnoDB 스토리지 엔진에서 데이터를 찾는 과정
1.
필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
a.
InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색
b.
해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색
c.
버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
2.
디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
3.
버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
a.
Read Ahead와 같 이 대량 읽기의 경우 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만 실제 쿼리에서 사용되지는 않을 수도 있으며, 이런 경우에는 MRU로 이동되지 않음
4.
버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 Age가 부여 됨
a.
버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지에 부여된 Aging이 증가해 결국 해당 페이지는 버퍼 풀에서 제거 됨
b.
버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화 되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨짐
5.
필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가
처음 한 번 읽힌 데이터 페이지가 이후 자주 사용된다면 그 데이터 페이지는 InnoDB 버퍼 풀의 MRU 영역에서 계속 살아남게 된다.
•
거의 사용되지 않는다면 LRU의 끝으로 밀려나 결국 InnoDB 버퍼 풀에서 제거 된다.
플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리 함
•
변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록돼야 한다.
•
리두 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는 것을 항상 보장하지는 않는다.
4.2.7.3 버퍼 풀과 리두 로그
InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리의 성능이 빨라진다.
