2022. 3. 6. 23:08ㆍSQL
0. Overview
이번 게시글에서는 MySQL의 핵심 구조은 InnoDB 스토리지 엔진의 구조에 대해 서술해보겠다. 이렇게 '구조'에 대해서 공부하는 것은 프로젝트의 기능 구현에 직접적으로 도움이 되진 않을 것이다. 그러나, '구조'를 통해 작동원리를 모르고 데이터 액세스 로직을 사용할 경우, 데이터 액세스 로직을 가성비 있게 사용하지 못하는 경우가 발생한다. 애플리케이션의 성능을 떨어트리는 요소가 네트워크 IO와 데이터베이스 IO인데, 그중에서 개발자가 종종 관여하는 부분이 데이터베이스라는 점을 감안해보면, 구조를 파악하여, 성능을 최대한 확보하는 것은 매우 중요해 보인다.
1.1 MySQL의 전체 구조
우선 전체적인 구조를 나타내기 위해, 전체 구조 그림으로 나타내 보았다. RealMySQL 8.0 에서는 더 많은 내용을 디테일하게 담고 있지만, 이 글의 목적이 요약인 만큼 모든 내용을 담진 않겠다. 그림에서도 볼 수 있듯이 보통 MySQL 서버라고 한다면, 크게 MySQL Engine과 Storage Engine을 함께 포함하는 경우라고 보면 되겠다.
1.1.1 MySQL Engine
- MySQL Engine은 클라이언트와의 접속 및 쿼리 요청을 처리하는 Connection Handler와 SQL Parser 그리고 쿼리 실행의 최적화를 담당하는 Optimizer가 중심을 이룬다.
- MySQL의 경우 표준 SQL인 ANSI SQL 문법을 지원하기 때문에 해당 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행할 수 있다.
1.1.2 Storage Engine
- DBMS의 두뇌에 해당한다.
- 실제 데이터를 디스크 스토리지에 저장하거나(write) 디스크 스토리지로부터 데이터를 읽어오는 부분(read)을 담당한다.
- DDL 및 DML 등의 작업을 처리하는 것을 담당한다.
- 스토리지 엔진의 종류에 따라 작동 방식은 다르겠지만, 대체로 성능 향상을 위해 캐시(MyISAM) 혹은 버퍼 풀(InnoDB)과 같은 기능이 내장되어 있다.
1.1.3 Handler API
MySQL Engine의 쿼리 실행기에서 데이터를 읽거나 쓰기를 수행하기 위해 Storage Engine에 읽기/쓰기 요청을 하는데, 이를 Handler 요청이라 하고, 해당 요청을 수행하는 API를 Handler API라고 한다.
1.2 MySQL 스레딩 구조
MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 Forground thread와 BackgroundThread로 이루어져 있다.
1.2.1 Foreground thread(클라이언트 스레드)
- Forground thread는 MySQL 서버에 접속한 클라이언트의 수만큼 존재하며, 클라이언트 사용자가 요청하는 쿼리를 처리한다. 해당 클라이언트 사용자가 작업을 마치고 Connection을 종료하면 기존 Connection을 처리하던 스레드는 Thread cache로 돌아간다.
thread_cache_size설정에 따라 다르지만, 일정 개수 이상의 thread가 Thread cache에 대기하게 되면 초과하게 되는 Thread는 종료하게 된다. - Foreground thread는 기본적으로는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오지만, 버퍼나 캐시에 데이터가 없다면, 디스크에 직접 접근하거나, 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다. Storage Engine의 종류에 따라 다르지만, InnoDB의 경우, Foreground thread는 데이터 버퍼나 캐시까지만 접근하여 처리하고, 버퍼로부터 디스크까지 기록하는 작업은 Background thread가 처리한다.
1.2.2 Background thread
InnoDB를 기준으로 Background thread 처리하는 작업은 다음과 같다.
- Insert Buffer를 병합하는 스레드
- log를 디스크로 기록하는 스레드
- InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
- 데이터를 버퍼로 읽어오는 스레드
- DeadLock이나 Lock을 모니터링하는 스레드
위의 작업들 중에서 가장 중요한 것은 Write thread(로그 및 버퍼의 데이터를 디스크로 내려쓰는 작업) 일 것이다. InnoDB를 기준으로 Foreground thread는 주로 읽기 작업을, Background thread은 주로 쓰기 작업을 담당한다.
SELET 쿼리를 실행했는데, 해당 요청이 10분 뒤에 결과를 돌려주겠다는 것과 같은 기능은 없는 것처럼, 보통 InnoDB의 읽기(read)의 경우, 쓰기(thread) 작업과는 달리 지연 처리(버퍼링)를 지원하지 않는다.
1.3 메모리 할당 및 사용 구조
MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역이 있겠다.
- 글로벌 메모리 영역의 경우, 운영체제로부터 할당받는데, 요청된 메모리 중에서 실제로 할당해주는 메모리의 비율은 운영체제의 종류에 따라 다르다.
- 글로벌 메모리 영역과 로컬 메모리 영역은 MySQL 서버 내의 스레드들이 공유하는 공간인지 여부에 따라 구분된다.
1.3.1 글로벌 메모리 영역
클라이언트 스레드의 수와 무관하게 하나의 메모리 공간으로 할당된다. 즉 용어에서도 유추해볼 수 있듯이, 모든 스레드들이 공유하는 공간이며, 글로벌 메모리 영역의 종류는 다음과 같다.
- 테이블 캐시
- InnoDB 버퍼 풀
- InnoDB 어댑티브 해시 인덱스
- InnoDB 리두 로그 버퍼
1.3.2 로컬 메모리 영역
- 세션 메모리 영역이라고도 하는데, 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다.
- 세션 메모리 영역이라고도 부르는 이유는 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 부르기 때문이다.
- 로컬 메모리는 각 클라이언트 스레드 별로 독립적으로 할당되며, 다른 스레드와 공유되지 않는 특징을 가졌다.
- 동적으로 할당되는 영역인 만큼, OOM(out of memory)의 위험이 있으므로, 적절한 메모리 공간을 설정하는 것이 중요하다.
대표적인 로컬 메모리 영역은 다음과 같다.
- 정렬 버퍼
- 조인 버퍼
- 바이너리 로그 캐시
- 네트워크 버퍼
1.4 쿼리 실행 구조
위 그림은 쿼리의 실행하는 관점에서 MySQL의 구조를 간략하게 그림으로 표현하였다. 구조를 기능별로 나누고 정의해보면 다음과 같이 정리해볼 수 있겠다.
1.4.1 쿼리 파서
사용자의 요청으로 들어온 쿼리 문장을 MySQL이 인식할 수 있는 최소 단위의 어휘 혹은 기호(토큰)로 분리하여 트리 형태의 구조로 만들어내는 작업을 의미한다. 문법 오류는 이 과정에서 나타나고, 사용자에게 오류 메시지를 전달한다.
1.4.2 전처리기
쿼리 파서로 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제가 있는지 확인한다. 구조적인 문제점이라고 한다면, 각 토큰을 통해, 전처리기는...
- 테이블이나 컬럼이 존재 유무 확인
- 내장 함수와 같은 개체를 매핑하여, 해당 객체의 존재 여부 및 접근 권한 확인
이 과정에서 존재하지 않거나, 권한상 사용이 불가능한 개체의 토큰은 이 단계에서 걸러진다.
1.4.3 옵티마이저
쿼리 문장을 좀 더 저렴하게 처리하는 방향을 결정하는 역할을 맡으며, DBMS의 두뇌에 해당한다.
1.4.4 실행 엔진
옵티마이저가 쿼리를 처리하는 방법을 최적화하면, 실행 엔진은 그 방법을 실행하는 역할을 내린다.
1.4.5 핸들러(스토리지 엔진)
1.1.3 Handler API에서 언급했듯이, 핸들러는 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다. 스토리지 엔진의 경우 크게 InnoDB와 MyISAM이 있는데, 핸들러가 조작하는 테이블의 종류에 따라 InnoDB 스토리지 혹은 MyISAM 스토리지를 사용한다.
1.4.6 쿼리 캐시(8.0 이후로 사용 x)
동일한 SQL 쿼리를 실행하면 테이블을 읽지 않고 즉시 결과를 반환하여 성능을 높이는 것을 목적으로 만든 기능이었다. 하지만, 테이블의 데이터가 변경되면 캐시에 저장된 결과를 조회하여 삭제해야 하는 상황이 발생하는데, 이 과정이 오히려 동시 처리 성능 저하를 유발했다.
그래서 결국 MySQL 8.0 이후로 쿼리 캐시를 완전히 제거하였고, 인위적인 설정조차 할 수 없도록, 관련 시스템 변수까지 제거했다. 이 기능은 읽기만 하는 테이블을 관리하는 MySQL 서버일 경우, 매우 훌륭하지만, 당연하게도 읽기만 하는 테이블은 거의 없다.
1.4.7 스레드 풀
스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 제한하여, 서버의 자원 소모를 줄이는 것이 목적이다. 스레드 풀의 경우, 프로세서 친화도를 높이고 운영체제 입장에서는 컨텍스트 스위칭을 줄여 오버헤드를 줄일 수 있다. 하지만, CPU 스케줄링 과정에서 요청을 처리하기 위한 스레드를 확보하기 위한 CPU 시간을 확보하지 못할 경우, 오히려 쿼리 처리가 더 느려질 수도 있다.
스레드 풀 기능은 MySQL 서버 엔터프라이즈 에디션(돈 내고 쓰는 버전)에서만 지원한다.
1.5 InnoDB 스토리지 엔진 아키텍처
InnoDB의 구조의 경우, 구조가 다소 복잡해서 우선은 책의 그림을 그대로 가져오게 되었다. InnoDB의 특징으로는 레코드 기반의 잠금을 통한 높은 동시성 처리라고 할 수 있겠다. 그리고, 뒤에서 자세히 서술하겠지만, MySQL에서는 유일하게 트랜잭션 처리를 해주는 엔진이기에 요즘에는 MySQL을 쓴다면, InnoDB 엔진을 사용하는 경우가 대부분이라 할 수 있겠다. 세부 구조의 특징을 하나씩 살펴보자면...
1.5.1 프라이머리 키에 의한 클러스터링
- InnoDB의 모든 테이블은 기본적으로는 PK를 기준으로 클러스터링이되어 저장된다.
- 즉 PK의 값 순서대로 디스크에 저장된다는 뜻이며, 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
- 따라서 PK를 이용한 클러스터링 인덱스의 경우 비교적 빠른 레인지 스캔이 가능하다.
1.5.2 외래 키 지원
- 이 기능은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM, MEMORY 테이블에서는 사용이 불가능하다.
- 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 대한 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하다.
- 이러한 작업을 수행하게 될 경우, 부모/자식으로 연관된 여러 테이블에 락이 전파되는데, 이는 데드락을 일으킬 수 있다.
- 그래서 종종 서비스용 데이터베이스에서는 운영의 편의를 위해 외래 키를 사용하지 않는 경우가 종종 있다.
- 외래 키의 제약으로 인해, 종종 데이터 및 스키마 변경이 실패할 수 있는데,
foreign_key_checks시스템 변수를 OFF 하여 FK의 제약 사항을 일시적으로 멈출 수 있다.SET foreign_key_checks=OFF;라는 쿼리문을 통해 설정이 가능한데, 주의할 점은 기존에 지켜주어야 했던CASACADE제약 역시 인위적으로 맞춰줘야 하는 경우가 생긴다.
1.5.3 MVCC(Multi Version Concurrency Control)
- 일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, 'MVCC를 통해 잠금 없는 일관된 읽기'를 지원하는 것을 목적으로 한다.
- InnoDB의 경우 언두 로그(Undo log)를 활용하여 이 기능을 구현한다.
- MVCC에서 Multi Version에서 의미하는 바는 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 뜻이다.
'레코드에 대해 여러 개의 버전을 동시에 관리'를 좀 더 구체적으로 설명해보기 위해 예를 들어보자면, 다음과 같이 스키마를 작성하고, 해당 테이블에 레코드를 삽입하는 쿼리를 보자.
1단계 - 스키마 작성(CREATE TABLE) 이후 레코드 삽입(INSERT) 이후 커밋
CREATE TABLE member (
m_id INT NOT NULL,
m_name VARCHAR(20) NOT NULL,
m_area VARCHAR(100) NOT NULL,
PRIMARY KEY (m_id),
INDEX ix_area (m_area)
);
INSERT INTO member (m_id, m_name, m_area) VALUES (12, '김님딤', '서울');
COMMIT;위의 쿼리를 시작하면 DB의 상태는 다음과 같을 것이다.
메모리
InnoDB 버퍼 풀
m_id m_name m_area 12 김님딤 서울
언두 로그
- (비어있음)
데이터 파일(디스크)
m_id m_name m_area 12 김님딤 서울
2단계 - 이후에 해당 레코드 UPDATE(커밋 x)
이 상태에서 다음과 같은 UPDATE 쿼리를 입력해보자
UPDATE member SET m_area='경기' WHERE m_id=12;위의 쿼리를 실행한 이후의 DB의 상태는 다음과 같을 것이다.
메모리
InnoDB 버퍼 풀
m_id m_name m_area 12 김님딤 경기
언두 로그
m_id m_area 12 서울
- 데이터 파일(디스크)
m_id m_name m_area 12 김님딤 ?(InnoDB 버퍼 풀의 변경 내역은 백그라운드 스레드에서 처리한다. 따라서, 해당 변경 내역이 디스크의 데이터 파일에 기록되는 지 여부는 시점에 따라 다르다.)
3단계 - 커밋하지 않은 상태로 UPDATE 했던 레코드 조회(SELECT)
이 상태에서 다음과 같은 SELECT 쿼리를 실행한다면 어떤 데이터가 조회가 될까?
SELECT * FROM MEMBER WHERE m_id=12;이 부분은 격리 수준에 따라 다르다.
- READ_UNCOMMITTED : Commit 여부에 상관없이 InnoDB 버퍼 풀의 내용 read (m_area = '경기')
- READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE : Commit을 하지 않았다면, 언두 영역의 내용 read(m_area = '서울')
DB 상태와 관련해서 주의해야할 점은 트랜잭션이 지속되면 언두 영역의 데이터가 메모리에 쌓이는 것이다.
위 내용을 요약해보면, UPDATE와 같은 쿼리가 실행되면, InnoDB 버퍼 풀에선 변경된 데이터를, 언두 영역에서는 변경되기 전의 컬럼값을 저장한다.
여기서 Commit을 하면, InnoDB 버퍼 풀에 변경된 데이터를 영구적인 데이터로 만들지만, rollback을 할 경우, 언두 영역에서의 데이터를 다시 InnoDB 버퍼 풀에 적용하여 말그대로 undo 처리를 한다.
그리고, 언두 영역의 경우, Commit 이후로 바로 삭제되는 것이 아니라, 이 언두 영역을 필요로하는 트랜잭션이 더는 없을 때 비로소 삭제한다.
1.5.4 잠금 없는 일관된 읽기(Non-Locking Consistent Read)
InnoDB 스토리지 엔진의 MVCC 기술을 통해 InnoDB에서의 읽기 작업은 다른 트랜잭션의 락을 기다리지 않은 상태에서 읽기 작업이 가능하다. 격리 수준이 serializable 보다 낮은 단계(read_uncommited, read_commited, repeatable_read)의 경우, SELECT 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 락을 대기하지 않고 바로 실행한다.
MySQL 서버의 성능을 저하시키는 경우가 대부분 일관된 읽기를 유지하기 위해 트랜잭션이 오랫동안 활성화되어 언두 로그를 삭제하지 못하게 되기 때문이다. 따라서, 트랜잭션이 시작되었다면, 가능한 한 빨리 롤백이나 커밋으로 트랜잭션을 완료시키는 것이 좋다.
1.5.5 자동 데드락 감지
- InnoDB 스토리지 엔진은 데드락을 방지해기위해 락 대기 목록을 그래프 형태로 관리한다.
- 교착 상태에 빠진 트랜잭션을 강제 종료하는 기능을 제공하는데, 강제 종료의 기준은 트랜잭션의 언두 로그 양을 기준으로 삼는다.
- 언두 로그에서 레코드를 더 적게 가진 트랜잭션을 롤백 대상으로 삼는데, 이는 강제롤백으로 인한 MySQL 서버의 부하를 최소화해준다.
- 자동 데드락 감지 기능은 트랜잭션의 락 대기 목록을 조회하기 위해 또 하나의 락을 거는 형태이기 때문에, 데드락 감지 기능을 수행하기 위한 락을 확보하기 위해 오히려 더 심각한 데드락을 야기시킬 수 있다.
- 따라서, 보통은 트랜잭션마다 락 대기 시간을 설정하여, 해당 시간동안 락을 획득하지 못하면 에러를 반환하는 방식으로 데드락을 해결한다.
1.5.6 InnoDB 버퍼 풀
- InnoDB 스토리지 엔진의 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.
- 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다.
- 일반적인 애플리케이션에서는
INSERT, UPDATE, DELETE처럼 데이터를 변경하는 쿼리는 레코드의 위치가 랜덤하기 때문에, 랜덤한 디스크 작업을 발생시킨다. - 하지만 버퍼 풀이 있다면, 변경된 데이터를 모아서 처리하여 랜덤한 디스크 작업의 횟수를 줄일 수 있다.
1.5.8 언두 로그
InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업한다. 언두 로그가 활용되는 부분은 다음과 같다.
- 트랜잭션 보장 : 트랜잭션이 롤백되면 변경되었던 데이터를 다시 그 이전으로 복구해야 하는데, 이럴 때 언두 로그를 활용한다.
- 격리 수준 보장 : 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회할때 트랜잭션 격리 수준에 맞게 변경중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.
언두 로그는 DB의 가장 중요한 특성인 트랜잭션 보장에 활용되는 아주 중요한 부분인데, 관리 비용이 많이 필요한 영역이다. 따라서, 트랜잭션의 생명주기를 공유하는 언두로그의 특성상, 트랜잭션을 너무 오래 방치하면, 당연히 MySQL 서버의 성능에 지대한 영향을 끼친다.
1.5.7 어댑티브 해시 인덱스
- 일반적으로 '인덱스'라고 하면 B-Tree 알고리즘으로 정렬된 SortedList가 연상된다. 그러나 어댑티브 해시 인덱스(Adaptive Hash Index)는 'Adaptive'라는 단어의 뜻에서 유추해볼 수 있듯이 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다.
- B-Tree 인덱스를 통해 특정 값을 찾기 위해선, B-Tree의 노드들을 순회 하는데, 이 작업을 몇 천개의 스레드로 동시에 처리해야하는 상황이 오면, 프로세스 스케줄링 과다로 쿼리의 성능이 떨어진다.
- 어댑티브 해시 인덱스(Adaptive Hash Index)는 자주 사용하는 데이터의 페지의 키 값을 만들고, 해당 데이터를 조회할 때마다, 해당 키 값으로 바로 조회할 수 있도록 한다.
- 즉, 어댑티브 해시 인덱스(Adaptive Hash Index)를 통해 검색 시간이 줄고, 결과적으로는 내부 잠금(세마포어)의 횟수도 줄어든다.
1.6 MySQL 로그 파일
로그 파일은 MySQL 서버의 상태를 많은 지식을 요구하지 않고도 확인할 수 있는 방법을 제공해준다. 로그 파일들을 자세히 보면 MySQL 서버에 문제가 생겼을 때, 해결 방안을 제시해준다. 로그 파일의 종류는 다음과 같다.
- 에러 로그 파일 : MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일.
- 제너럴 쿼리 로그 파일 : MySQL 서버에서 실행되는 쿼리를 확인 시켜주는 로그 파일.
- 슬로우 쿼리 로그 : 지정한 초(seconds) 이상의 시간이 소요된 쿼리가 기록되는 로그 파일이다. 서비스 운영 중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝에 활용할 수 있다.
마무리
아키텍쳐의 경우, 다른 챕터보다 유난히 내용이 길었던 부분인 것 같다. 게다가, MySQL이라는 소프트웨어의 구조에 대해 주로 이야기 하는 챕터다 보니, 운영체제에서 이야기하는 스레드와 하드웨어와 관련된 용어도 많이 나온다. 예전 같았으면 이러한 용어들만 나오면 전혀 감을 못잡곤 했었는데, 최근에 공부했던, 공룡책이 크게 도움이 되었던 것 같다. Real MySQL은 정말 좋은 내용이 많은 만큼 깊고 어렵다. 한 두번 읽고, 정리하는 것으로는 머리에 다 들어오진 않겠지만, 이런식으로 정리하고, 프로젝트 작업을 하면서, 외우면서라도 익혔던 부분을 이해하기 시작하는 과정을 가지면 정말 좋을 것 같다.
'SQL' 카테고리의 다른 글
| MySQL을 통해 SQL 파헤치기 -3- 인덱스 (0) | 2022.03.10 |
|---|---|
| MySQL을 통해 SQL 파헤치기 -2- 트랜잭션과 락 그리고 격리 수준 (0) | 2022.03.07 |
| MySQL을 통해 SQL 파헤치기 -0- 개요 및 자주 나오는 용어 정리 (0) | 2022.03.06 |