[RealMySQL8.0] 4장 - MySQL 엔진 아키텍처

hwwwa·2023년 7월 1일

mysql

📖 RealMySQL8.0

목록 보기

1/2

4장 - MySQL 엔진 아키텍처

MySQL 전체 구조

출처: Real MySQL 8.0

MySQL 엔진

커넥션 핸들러, SQL 인터페이스, 파서, 옵티마이저, 캐시&버퍼 등

스토리지 엔진

InnoDB, MyISAM, Memory 등
실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 것을 담당합니다.
MySQL 엔진과 다르게 스토리지 엔진은 여러 개를 동시에 사용할 수 있습니다.

	MyISAM	InnoDB
특징	지원 기능이 많지 않아 구조가 단순	지원 기능이 많아 구조가 복잡. 자원을 많이 사용
기능 지원	Full-text 인덱싱 가능	인덱싱(Full-text 포함), 외래키, 제약조건, 동시성제어, 트랜잭션 지원
Lock 단위	Table level-lock	Row level-lock
복구 능력	나쁨	좋음

핸들러 API

핸들러 요청이란 MySQL 엔진의 쿼리 실행기에서 데이터를 읽고 쓰기 위해 각 스토리지 엔진에 보내는 읽기 또는 쓰기 요청을 뜻합니다.
핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지 확인할 수 있습니다.

MySQL 스레딩 구조

MySQL은 Thread 기반으로 작동하며, Foreground Thread와 Background Thread로 구분됩니다.

출처: Real MySQL 8.0

Foreground Thread (Client Thread)

최소 MySQL 서버에 접속된 클라이언트 수만큼 존재하며, 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리합니다.
클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 Thread는 다시 Thread cache로 되돌아갑니다.
만약 Thread cache에 이미 일정 개수 이상의 Thread가 대기 중이라면 해당 Thread를 종료시켜 일정 개수의 Thread만 Thread cache에 존재하게 합니다.

포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와 작업을 처리합니다.
MyISAM의 경우 디스크 쓰기 작업까지 포그라운드 스레드가 처리하고,
InnoDB의 경우 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리하게 됩니다.

Background Thread

InnoDB의 백그라운드 처리에서 가장 중요한 것은 로그 스레드와 버퍼의 데이터를 디스크로 쓰기 작업을 처리하는 쓰기 스레드입니다. 쓰기 스레드는 아주 많은 작업들을 백그라운드로 처리하기 때문에 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하는 것이 좋습니다.
이외에도 인서트 버퍼를 병합하는 스레드, 데이터를 버퍼로 읽어오는 스레드, 데드락이나 잠금을 모니터링하는 스레드 등 다양한 작업들이 백그라운드 스레드에서 처리됩니다.

참고) MySQL의 Thread 모델
1. 전통적인 Thread 모델
커넥션별로 Foreground Thread가 하나씩 생성됨 -> 1:1 관계
2. Thread Pool 모델
엔터프라이즈 에디션과 Percona 서버에서 사용 가능
하나의 Thread가 여러 개의 커넥션 요청을 전담 -> 1:N 관계

메모리 할당 및 사용 구조

글로벌 메모리 영역
- 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당됨. 모든 스레드에 공유됨
- 테이블 캐시, InnoDB 버퍼 풀, InnoDB 어댑티브 해시 인덱스, InnoDB Redo 로그 버퍼, MyISAM 키 캐시, 바이너리 로그 버퍼
로컬 메모리 영역
- 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역. 세션(커넥션) 메모리 영역 혹은 클라이언트 메모리 영역이라고도 함.
- 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되지 않음
- 조인 버퍼, 소트 버퍼, 네트워크 버퍼, 바이너리 로그 캐시, 리드 버퍼

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

출처: Real MySQL 8.0

MySQL의 독특한 구조 중 대표적인 것이 플러그인 모델입니다. 스토리지 엔진 외에도 검색어 파서, 사용자 인증 등의 기능이 플러그인 형태로 존재합니다.
MySQL은 기본적으로 다양한 스토리지 엔진을 제공합니다.

출처: Real MySQL 8.0

쿼리가 실행되는 과정에서 거의 대부분의 작업은 MySQL 엔진이 수행하고, 데이터 읽기/쓰기 작업만 핸들러를 통해 스토리지 엔진에서 수행됩니다.
새로운 스토리지 엔진을 사용하더라도, 전체 기능에는 영향을 주지 않고 데이터 읽기/쓰기 작업에만 영향을 주게 됩니다.
객체지향에서 Interface 를 만들고 구현체를 바꿔끼우는 것과 유사합니다.
플러그인은 오직 MySQL 서버와 인터페이스 가능하며 플러그인간 통신이 불가능합니다.
MySQL 서버의 변수나 함수를 직접 호출하므로 안전하지 않고(캡슐화 불가능), 상호 의존 관계를 설정할 수 없어 초기화가 어렵다는 단점이 있습니다.

컴포넌트

MySQL 8.0부터 기존 플러그인 아키텍처 대체를 위한 컴포넌트 아키텍처를 지원합니다.
컴포넌트 아키텍처는 논리적 컴포넌트 집합으로 캡슐화하는 것을 목표로 합니다. 기능 확장을 위해 실행 중인 서버에 컴포넌트를 추가할 수 있으며, 동적 또는 정적으로 연결할 수 있습니다. 또한 컴포넌트는 서비스 API를 통해 다른 구성 컴포넌트와 통신 가능합니다.

쿼리 실행 구조

출처: Real MySQL 8.0

쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리하여 트리 형태의 구조로 만들어내는 작업을 수행합니다.
*토큰: MySQL이 인식할 수 있는 최소 단위의 어휘나 기호
해당 과정에서 쿼리 문장의 기본 문법 오류가 발견되고 사용자에게 오류 메세지 전달하게 됩니다.

전처리기

파서 트리를 기반으로 쿼리 문장에 구조적인 문제점을 확인합니다. 각 토큰을 테이블 이름, 칼럼 이름, 내장 함수와 개체와 매핑하여 객체의 존재 여부 및 접근 권한을 확인하는 과정을 수행합니다.

옵티마이저

쿼리를 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 수행합니다. DBMS의 두뇌에 해당하며 매우 중요하고 영향 범위가 매우 넓습니다.

실행 엔진

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

핸들러(스토리지 엔진)

MySQL 서버의 가장 밑단에서 실행 엔진의 요청에 따라 데이터를 디스크에 저장하거나 읽어오는 역할을 수행합니다. 핸들러는 결국 스토리지 엔진을 의미합니다.

쿼리 캐시

빠른 응답을 필요로 하는 웹 기반 응용 프로그램에서 매우 중요한 역할을 담당했지만, 테이블의 데이터가 변경되면 캐시에 저장된 결과에서 해당 테이블과 연관된 모든 것들을 삭제해야 하므로 동시 처리 성능 저하를 유발하는 문제점이 있었습니다.
이에 MySQL 8.0에서는 쿼리 캐시 기능이 완전히 제거되었고 관련 시스템 변수도 모두 제거되었습니다.

스레드 풀

내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많아도 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 하여 서버 자원 소모를 줄이는 것이 목적입니다.
CPU가 제한된 수의 스레드만으로 적절히 처리할 수 있도록 유도한다면 CPU의 프로세서 친화도를 높이고 불필요한 컨텍스트 스위치를 줄여 오버헤드를 낮출 수 있다는 장점이 있습니다. 일반적으로 스레드 그룹 개수는 CPU 코어의 개수와 맞추는 것이 좋습니다.

MySQL 서버 엔터프라이즈 에디션과 Percona Server에서만 스레드 풀 기능을 지원하며 커뮤니티 에디션에서는 지원하지 않습니다. 하지만 동일 버전 Percona Server의 스레드풀 플러그인 라이브러리(thread_pool.so 파일)을 MySQL 커뮤니티 에디션 서버에 설치하여 사용할 수 있습니다.

트랜잭션 지원 메타데이터

MySQL 5.7 버전까지는 테이블 구조 및 일부 스토어드 프로그램을 파일 기반으로 관리하였습니다.
하지만 이러한 파일 기반의 메타데이터는 생성 및 변경 작업에 트랜잭션을 지원하지 않아 테이블 생성 혹은 변경 도중 MySQL 서버가 비정상 종료된다면 일관성이 보장되지 않아 테이블이 깨지는 현상이 발생하였습니다.

이에 MySQL 8.0 부터는 관련 정보를 모두 InnoDB 테이블에 저장하도록 개선되었습니다.
시스템 테이블과 데이터 딕셔너리 정보는 모두 mysql DB에 저장됩니다. 실제 information_schema의 테이블 구조는 mysql DB의 tables 테이블을 참조하는 뷰로 만들어져 있습니다.

InnoDB 스토리지 엔진 아키텍처

MySQL 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금 제공하며, 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어난 스토리지 엔진입니다.

출처: Real MySQL 8.0

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

InnoDB의 모든 테이블은 프라이머리 키 값의 순서대로 디스크에 저장됩니다.
모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리 주소로 사용합니다.
프라이머리 키가 곧 클러스터링 인덱스이므로 프라이머리 키를 이용한 Range Scan이 매우 빠릅니다.
프라이머리 키가 다른 보조 인덱스에 비해 비중이 높게 설정되어 있어 쿼리 플랜에서 선택될 확률이 높습니다.

오라클의 IOT(Index Organized Table)와 동일한 구조
MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않아 프라이머리 키는 유니크 제약을 가진 세컨더리 인덱스일 뿐이며 모든 인덱스는 물리적인 레코드의 주소 값(ROWID)을 가짐

외래키 지원

InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로, MyISAM이나 Memory 테이블에서는 사용할 수 없습니다.
부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인한 DeadLock이 발생하는 경우가 높아져 실제 서비스 운영 환경에서 사용하지 않는 경우가 많습니다.
foreign_key_checks 변수를 OFF로 변경하여 일시적으로 체크 작업 및 연쇄 작업을 멈출 수 있으며, 일관성을 맞춰준 후에 다시 외래키 체크 기능을 활성화하여야 합니다.

MVCC (Multi Version Concurrency Control)

MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는데 있습니다. InnoDB 스토리지 엔진은 언두 로그를 이용해 MVCC 기능을 구현하였습니다.

MVCC는 하나의 레코드에 여러 버전이 동시에 관리되며 격리 수준(isolation level)에 따라 처리되는 방식이 다릅니다.

UPDATE를 실행하였을 때 커밋 여부와 관계없이 InnoDB 버퍼 풀의 내용이 변경되고 이전의 값이 Undo log에 저장되게 됩니다. (데이터 파일의 내용은 일반적으로 버퍼 풀과 동일)
격리 수준이 READ_UNCOMMITTED인 경우, 커밋 여부와 관계 없이 변경된 상태의 데이터를 반환하기 위해 버퍼 풀 또는 데이터 파일의 데이터를 읽어 반환합니다.
격리 수준이 READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE인 경우 변경 전 내용을 보관하는 Undo 영역의 데이터를 반환하게 됩니다.

트랜잭션을 COMMIT하게 되면 현재의 상태를 영구 데이터로 만들고 Undo 영역을 필요로 하는 트랜잭션이 없을 때 Undo 백업 데이터를 삭제하게 됩니다.
트랜잭션을 ROLLBACK하게 되면 Undo 영역의 백업 데이터를 InnoDB 버퍼 풀로 복구하게 됩니다.

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

InnoDB 스토리지 엔진은 MVCC을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.
SERIALIZABLE를 제외한 다른 격리 수준에서는 INSERT와 연결되지 않은 SELECT 작업이라면 다른 트랜잭션의 잠금을 기다리지 않고 읽기 작업이 가능하다.

출처: Real MySQL 8.0

오랜 시간동안 트랜잭션이 활성 상태인 경우 일관된 읽기를 위해 Undo log를 삭제하지 못하므로 가능한 빨리 COMMIT이나 ROLLBACK을 통해 트랜잭션을 끝내는 것이 좋습니다.

자동 DeadLock 감지

InnoDB 스토리지 엔진은 DeadLock 감지를 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리합니다.
DeadLock 감지 스레드가 주기적으로 잠금 대기 그래프를 검사하여 교착 상태에 빠진 트랜잭션 중 하나를 강제 종료하게 되는데, 이때 Undo log의 양이 적은 트랜잭션이 먼저 종료됩니다.
(ROLLBACK시에 Undo 처리할 내용이 적으므로 MySQL 서버 부하가 적음)

DeadLock 감지 스레드가 잠금 목록을 체크할 때 잠금 목록에 새로운 잠금이 걸리므로 동시 처리 스레드가 매우 많은 경우 많은 CPU 자원 소모가 생길 수 있습니다.
이를 위해 innodb_deadlock_detect 시스템 변수로 DeadLock 감지 스레드를 비활성화하고, innodb_lock_wait_timeout 시스템 변수를 활성화하여 DeadLock 상황에서 일정 시간이 지나면 자동으로 요청 실패 후 에러 메세지를 반환하도록 할 수 있습니다.

💡 PK 또는 Secondary Index를 기반으로 매우 높은 동시성 처리를 요구하는 서비스가 있다면 innodb_deadlock_detect를 비활성화하여 성능을 비교해보면 좋습니다.

장애 복구 자동화

InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된(Partital Write) 데이터 페이지 등에 대해 자동 복구 작업을 수행합니다. 하지만 디스크나 서버 하드웨어 이슈로 자동 복구가 불가능하다면 자동 복구를 멈추고 MySQL 서버가 종료됩니다.

이때는 innodb_force_recovery 시스템 변수를 설정하여 InnoDB 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사를 선별적으로 진행하도록하여 MySQL 서버를 시작해야 합니다.
문제의 원인에 따라 1~6으로 값을 변경하여 MySQL 서버를 재시작하고 가능한 데이터를 백업한 후 데이터베이스를 재구축합니다.

만약 모든 설정값에 대해서도 MySQL 서버가 시작되지 않으면 백업을 이용해 다시 구축하여야 합니다. 마지막 풀 백업 시점부터 장애 시점까지의 바이너리 로그가 있다면 InnoDB 복구 사용보다 풀 백업과 바이너리 로그로 복구하는 것이 손실이 더 적을 수 있습니다.

InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간입니다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 하는 버퍼 역할을 함께 수행하여 디스크 작업 횟수를 줄일 수 있습니다.

버퍼 풀 크기를 설정할 때는 운영체제와 각 클라이언트 스레드가 사용할 메모리까지 충분히 고려하여 설정하여야 합니다. 증가시키는 것은 영향도가 적지만 줄이는 것은 영향도가 매우 크므로 줄이는 일이 없도록 주의하여야 합니다. 적절히 작은 값으로 설정한 후 상황을 봐가며 증가시키는 것이 좋습니다.

버퍼 풀 구조

버퍼 풀 메모리 공간은 페이지 단위 조각으로 쪼개어지고, 필요 시 데이터 페이지를 읽어 조각에 저장하는 구조를 가지고 있습니다.

InnoDB 스토리지 엔진은 버퍼 풀의 페이지 조각을 관리하기 위해 LRU 리스트, Flush 리스트, Free 리스트 3개의 자료 구조를 관리합니다.

LRU 리스트
- LRU(Least Recently Used)와 MRU(Most Recently Used) 리스트가 결합된 형태입니다.
- Aging을 통해 자주 사용되는 페이지는 계속해서 MRU 영역에 존재하게되고, 거의 사용되지 않는다면 LRU의 끝으로 밀리다가 버퍼 풀에서 제거됩니다.
Flush 리스트
- 디스크로 동기화되지 않은 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리합니다.
Free 리스트
- 비어 있는 페이지 목록으로, 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용됩니다.

버퍼 풀과 리두 로그

InnoDB 버퍼 풀은 데이터베이스 성능 향상을 위해 데이터 캐시 기능과 쓰기 버퍼링 기능을 지원합니다.

출처: Real MySQL 8.0

InnoDB 버퍼 풀과 리두 로그의 관계 설명의 흐름을 요약해보면 다음과 같습니다.

InnoDB 버퍼 풀은 클린 페이지와 더티 페이지(변경된 데이터)를 가집니다.
더티 페이지는 언젠가 디스크에 기록되어야 합니다.
한정된 메모리 공간인 버퍼 풀에 더티 페이지가 계속 머무를 수 없으므로 리두 로그 파일과의 순환 고리를 이용해 데이터 변경을 기록합니다.
리두 로그 파일에 데이터 변경이 기록될 때마다 LSN이라는 시퀀스 번호를 증가시킵니다.
InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시키고 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 파일의 시작점이 됩니다.
체크포인트가 발생하면 LSN 시작점부터 그보다 작은 LSN을 가진 리두 로그 엔트리와 관련된 버퍼 풀의 더티 페이지를 디스크로 동기화합니다.

이렇듯, 버퍼 풀의 더티 페이지 비율과 리두 로그 파일의 전체 크기는 쓰기 버퍼링 기능 성능에 관계되어 있습니다. 버퍼 풀의 메모리 공간을 늘리는 것은 데이터 캐시 기능만 향상시키는 것이며, 쓰기 버퍼링 기능까지 향상시키기 위해서는 InnoDB 버퍼 풀과 리두 로그 파일의 전체 크기를 적절히 선택해 최적 값을 찾아야 합니다.

Buffer Pool Flush

InnoDB 스토리지 엔진은 더티 페이지를 성능 악영향 없이 디스크에 동기화하기 위해 Flush_list 플러시 기능과 LRU_list 플러시 기능을 백그라운드로 실행합니다.

Flush_list 플러시는 오래전에 변경된 데이터 페이지 순으로 디스크에 동기화하고, LRU_list 플러시는 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지를 제거합니다.

현재 MySQL 8.0 버전에서는 디스크 쓰기 폭증과 같은 현상이 대부분 발생하지 않으므로 특별한 성능 문제가 발생한 경우가 아니라면 관련 시스템 변수들을 조정할 필요는 없습니다.

버퍼 풀 상태 백업 및 복구

쿼리 요청이 매우 빈번한 서버를 재시작하면 성능이 저하되는 것을 볼 수 있는데, 이는 버퍼 풀에 데이터가 올라가 있지 않아서 모든 데이터를 디스크로부터 읽어와야 하기 때문에 발생하는 성능 저하입니다.

이에 MySQL 5.5 버전에서는 Warming Up(디스크의 데이터가 버퍼 풀에 적재되어 있는 상태)을 위해 서버 시작 전 주요 테이블과 인덱스에 대해 풀 스캔을 실행하고 서비스를 오픈하기도 하였습니다.
MySQL 5.6 버전부터는 innodb_buffer_pool_dump_now 시스템 변수로 현재 버퍼 풀의 상태 백업할 수 있게 되었습니다.

Double Write Buffer

하드웨어 오작동이나 시스템 비정상 종료로 인해 데이터 페이지의 일부만 디스크에 기록되는 Partial-page (Torn-page) 현상이 발생하게 됩니다. InnoDB 스토리지 엔진은 이를 막기 위해 Double-Write 기법을 사용합니다.

출처: Real MySQL 8.0

실제 데이터 파일에 변경 내용을 기록하기 전에 더티 페이지를 묶어 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록합니다.
InnoDB 스토리지 엔진은 각 더티 페이지를 파일의 적당한 위치에 랜덤 쓰기를 실행합니다.
데이터 파일의 페이지들과 DoubleWirte 버퍼의 내용을 비교합니다.
데이터 파일 페이지와 DoubleWirte 버퍼의 내용이 다르다면 DoubleWirte 버퍼의 내용을 데이터 파일의 페이지로 복사합니다.

데이터 무결성이 매우 중요한 서비스에서 Double-Write 기능을 활성화하는 것을 권장합니다. 만약 성능을 위해 InnoDB Redo 로그 동기화 설정(innodb_flush_log_at_trx_commit)을 1이 아닌 값으로 설정했다면 Double-Write도 비활성화하는 것이 좋습니다.

언두 로그

언두 로그란 트랜잭션과 격리 수준을 보장하기 위해 DML 변경 이전 버전의 데이터를 별도로 백업해둔 데이터입니다.

트랜잭션 보장 용도
- 트랜잭션 롤백 시 Undo log를 사용해 변경 전 데이터로 복구합니다.
격리 수준 보장 용도
- 데이터 변경 도중 다른 커넥션에서 데이터 조회 시 격리 수준에 맞게 Undo log에 백업해둔 데이터를 읽어서 반환합니다.

트랜잭션이 장시간 유지된다면 Undo Log의 양이 점점 증가하게 되어 쿼리 성능이 떨어질 수 있으므로 Undo log의 급증 여부를 모니터링하는 것이 필요합니다.

-- MySQL 서버의 모든 버전에서 사용 가능한 명령 
SHOW ENGINE INNODB STATUS \G 
-- MySQL 8.0 버전에서 사용 가능한 명령 
SELECT count 
FROM information_schema.innodb_metrics 
WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';

언두 테이블스페이스

언두 테이블스페이스란 언두 로그가 저장되는 공간으로, 이 공간은 버전마다 저장되는 공간이 다릅니다.

MySQL 5.6 이전
- 모두 시스템 테이블스페이스(ibdata.ibd)에 저장됨
MySQL 5.6
- innodb_undo_tablespaces 시스템 변수를 0으로 설정 시 시스템 테이블스페이스에 저장, 2보다 큰 값으로 설정 시 별도의 Undo log 파일에 저장
MySQL 8.0
- 모두 별도의 Undo log 파일에 저장

하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 Undo Slot(16바이트 단위)을 가집니다.

출처: Real MySQL 8.0

하나의 트랜잭션이 필요로 하는 Undo Slot의 개수는 대략 2개 정도이며, DML의 특성에 따라 최대 4개까지 사용하게 됩니다.

💡 최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 (롤백 세그먼트 개수) (언두 테이블스페이스 개수) / 2

언두 로그 공간이 남는 것은 크게 문제가 되지 않지만, 부족한 경우 트랜잭션을 시작할 수 없는 심각한 문제가 발생하므로 가능한 기본값을 유지하는 것이 좋습니다. MySQL 8.0 부터는 CREATE UNDO TABLESPACE나 DROP TABLESPACE와 같은 명령으로 언두 테이블스페이스를 동적으로 추가 및 삭제할 수 있습니다.

체인지 버퍼

체인지 버퍼는 변경해야 할 인덱스 페이지를 디스크로부터 읽어와야 하는 경우 자원 소모를 줄이기 위해 사용되는 임시 메모리 공간입니다.

출처: MySQL 8.0 Document - InnoDB Storage Engine Change Buffer

데이터 변경 시 InnoDB는 즉시 업데이트를 실행하지 않고 체인지 버퍼에 저장해두었다가 사용자에게 결과를 반환하는 형태로 성능을 향상시킵니다. 이후 체인지 버퍼에 임시 저장된 인덱스 레코드 조각은 이후에 백그라운드 스레드 중 버퍼 머지 스레드에 의해서 병합됩니다.

*유니크 인덱스의 경우 중복 여부를 체크해야 하므로 체인지 버퍼 사용할 수 없습니다.

MySQL 5.5 이전 버전까지는 INSERT 작업에 대해서만 버퍼링 가능(인서트 버퍼)했지만, 이후 점차 개선되어 MySQL 8.0부터는 INSERT, DELETE, UPDATE 작업도 버퍼링 가능하게 되었습니다.

-- 체인지 버퍼가 사용중인 메모리 공간 크기 확인
mysql> SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
       FROM performance_schema.memory
       WHERE EVENT_NAME='memory/innodb/ibuf0ibuf';
-- 체인지 버퍼 관련 오퍼레이션 처리 횟수 확인
mysql> SHOW ENGINE INNODB STATUS \G

리두 로그 및 로그 버퍼

리두 로그

리두 로그는 트랜잭션 요소 중 영속성(Durability)과 밀접하게 연관되어 있으며, 하드웨어나 소프트웨어 등 여러 문제점으로 인해 MySQL 서버가 비정상 종료되었을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치 역할을 합니다.

모든 DBMS는 읽기 성능을 고려한 자료구조를 가지고 있어 쓰기를 위해 디스크의 랜덤 액세스가 필요한데, InnoDB는 이로 인한 성능 저하를 막기 위해 리두 로그를 사용합니다.

MySQL 서버가 비정상 종료되었을 때 InnoDB 스토리지 엔진의 데이터 파일에 발생하는 문제를 리두 로그를 사용하여 해결하는 방법은 다음과 같습니다.

커밋되었지만 데이터 파일에 기록되지 않은 데이터
- 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사
롤백되었지만 파일에 이미 기록된 데이터
- 리두 로그를 통해 트랜잭션 상태(커밋/롤백/실행중)를 확인하고 언두 로그의 내용을 데이터 파일에 복사

변경 작업이 매우 많은 DBMS 서버의 경우 리두 로그의 기록 작업이 성능 저하로 이어질 수 있습니다. 이를 위해 ACID 속성을 보장하는 수준에서 로그 버퍼 공간에 리두 로그 버퍼링을 수행합니다.

MySQL 8.0 부터는 리두 로그 수동 활성화/비활성화할 수 있으며, 데이터를 복구하거나 대용량 데이터를 한 번에 적재하는 경우 리두 로그를 비활성화하여 데이터 적재 시간을 감소시킬 수 있습니다. 데이터 적재 이후에는 꼭 다시 활성화하도록 주의합니다.

-- 로그 비활성화 
ALTER INSTANCE DISABLE INNODB REDO_LOG;
-- ... 대량 데이터 적재 수행...
-- 리두 로그 활성화 
ALTER INSTANCE ENABLE INNODB REDO_LOG;

리두 로그 아카이빙

MySQL 서버에 유입되는 데이터 변경이 너무 많으면 리두 로그가 빠르게 증가하고, 새로 추가되는 리두 로그 내용을 복사하기 전에 덮어 쓰일 수 있습니다. 이렇게 되면 데이터 백업 파일은 일관된 상태를 유지하지 못하고 데이터 백업에 실패하게 됩니다.

이를 위해 MySQL 8.0 버전부터는 리두 로그 아카이빙 기능을 지원하여 데이터 변경이 많아서 리두 로그가 덮어 쓰인다고 해도 백업이 실패하지 않도록 합니다.

Adaptive Hash Index

어댑티브 해시 인덱스란 사용자가 수동으로 생성한 인덱스가 아닌 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스입니다.
innodb_adaptive_hash_index 시스템 변수를 이용하여 어댑티브 해시 인덱스를 활성화/비활성화할 수 있습니다.

B-Tree 인덱스의 검색 시간을 줄이기 위해 도입된 기능으로, 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고 필요할 때마다 어댑티브 해시 인덱스를 검색해 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있게 해줍니다.

출처: 카카오 테크 - MySQL InnoDB의 Adaptive Hash Index 활용

해시 인덱스는 인덱스 키 값과 해당 인덱스의 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리됩니다. 이때 인덱스 키 값은 B-Tree 인덱스의 고유 ID와 B-Tree 인덱스의 실제 키 값의 조합이며, 데이터 페이지 주소는 실제 키 값이 저장된 데이터 페이지의 메모리 주소(InnoDB 버퍼 풀에 로딩된 페이지 주소)입니다.

어댑티브 해시 인덱스와 성능

어댑티브 해시 인덱스를 사용한다고 해서 무조건 성능이 향상되는 것은 아닙니다.

성능 향상에 크게 도움이 되지 않는 경우
- 디스크 읽기가 많은 경우
- 특정 패턴의 쿼리가 많은 경우(LIKE 패턴 검색이나 조인)
- 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
성능 향상에 도움이 되는 경우
- 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
- 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
- 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

서비스 패턴을 파악해 어댑티브 해시 인덱스가 도움이 되고 있는지 불필요한 오버헤드를 발생시키고 있는지 판단하여 적절하게 사용해야 합니다.

MyISAM 스토리지 엔진 아키텍처

출처: Real MySQL 8.0

Key cache (Key Buffer)

InnoDB의 버퍼풀과 비슷한 역할을 수행하며, 인덱스 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할 수행합니다.
키 캐시 효율성 확인: 키 캐시 히트율(Hit rate) = 100 - (Key_reads / Key_read_requests * 100)

운영체제의 캐시 및 버퍼

MyISAM은 디스크 I/O를 위한 캐시 또는 버퍼링 기능이 없으므로 데이터 읽기나 쓰기 작업은 항상 운영체제의 디스크 읽기 또는 쓰기 작업으로 요청됩니다. 이때 운영체제에 존재하는 캐시나 버퍼링 메커니즘이 사용되지만, InnoDB처럼 데이터 특성을 알고 전문적으로 캐시나 버퍼링 하는 것은 불가능합니다.
운영체제의 캐시 공간은 남는 메모리를 사용하므로 MyISAM 테이블을 주로 사용한다면 메모리를 충분히 비워두어야 합니다.

데이터 파일과 프라이머리 키(인덱스) 구조

InnoDB와 다르게 MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이 레코드는 INSERT 순으로 데이터 파일에 저장됩니다.
프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 물리적인 주소값인 ROWID를 포인터로 가지게 됩니다.

ROWID 저장 방법으로는 레코드가 INSERT된 순번이 ROWID로 사용되는 고정 길이 방법, 데이터 파일에서 레코드 위치가 ROWID로 사용되는 가변 길이 방법이 있습니다. 고정 길이 방법은 4바이트 정수를 사용하여 가질 수 있는 레코드 개수가 한정되어 있습니다. 가변 길이 방법은 myisam_data_pointer_size 시스템 변수 설정 값만큼의 공간을 사용하고 ROWID 길이와 실제 ROWID로 구성됩니다.

InnoDB, MyISAM, MEMORY 스토리지 엔진

기존에는 시스템 테이블이 MyISAM 스토리지 엔진으로 저장되어 있었고 공간 좌표 검색, 전문 검색 기능을 위해 MyISAM스토리지 엔진을 사용하는 경우가 있었습니다. 하지만 MySQL 8.0 이후 MySQL 서버의 모든 시스템 테이블이 InnoDB 스토리지 엔진으로 변경되었으며, InnoDB 스토리지 엔진에서 공간 좌표 검색, 전문 검색 기능 등을 지원하도록 개선되었습니다.

또한 기존에는 사용자 쿼리 처리를 위한 내부 임시 테이블로 MEMORY 스토리지 엔진을 사용하였으나 가변 길이 타입의 컬럼을 지원하지 않아 MySQL 8.0 부터는 TempTable 스토리지 엔진으로 대체되었습니다. (선택 가능)

이처럼 MySQL 서버의 모든 기능이 InnoDB 스토리지 엔진 기반으로 재편되었으므로 이후 MyISAM 스토리지 엔진과 MEMORY 스토리지 엔진이 없어질 수 있을 것으로 보입니다.

4.4 MySQL 로그 파일

로그 파일을 이용하면 MySQL 서버에 대한 내부 지식이 없어도 MySQL의 상태나 부하의 원인을 쉽게 찾을 수 있습니다.

에러 로그 파일

에러 로그 파일은 MySQL 실행 도중 발생하는 에러나 경고 메세지가 출력되는 로그 파일으로, MySQL 설정 파일(my.conf)의 log_error 파라미터에 정의된 경로에 생성됩니다. (별도로 정의되지 않았다면 datadir 파라미터에 정의된 데이터 디렉토리에 .err 확장자로 생성됨)

에러 로그 파일에서 확인할 수 있는 정보는 다음과 같습니다.

MySQL 시작 과정과 관련된 정보성 및 에러 메세지

MySQL 설정 파일 변경 혹은 데이터베이스 비정상 종료 후 재시작한 경우 출력되는 메세지입니다.

서버 정상 기동 여부, 변경/추가된 파라미터에 대한 에러나 경고 메세지, 특정 변수 ignore 여부, 변수명 인식 불가 혹은 설정된 파라미터 값 인식 불가 등의 에러메세지를 확인할 수 있습니다.

비정상 종료 시 나타나는 InnoDB 트랜잭션 복구 메세지

비정상 종료 후 재시작 시 InnoDB에서 트랜잭션을 정리하고 데이터 기록의 재처리 작업을 수행하는 과정에 대해 간단히 출력되는 메세지입니다.

어떠한 문제로 인해 복구 불가능하다면 에러메세지를 출력하고 MySQL이 다시 종료됩니다. 해결하기 어렵고 innodb_force_recovery 파라미터 조정이 필요한 경우가 많습니다.

쿼리 처리 도중에 발생하는 에러메세지

쿼리 실행 도중 발생한 에러나 복제에서 문제가 될 만한 쿼리에 대한 경고 메세지입니다. 예방이 어려우며 주기적으로 에러 로그 파일을 검토하는 과정에서 알게되므로 자주 검토하는 것이 필요합니다.

비정상적으로 종료된 커넥션 메세지 (Aborted connection)

클라이언트 애플리케이션에서 정상적으로 접속 종료하지 못하고 프로그램이 종료된 경우에 기록되는 메세지입니다. 네트워크 문제로 인해 의도치 않게 접속이 끊어지는 경우도 포함됩니다.

Aborted Connection 메세지가 너무 많은 경우에는 애플리케이션 커넥션 종료 로직 검토 후 max_connect_errors 시스템 변수 값을 필요에 따라 조정합니다.

InnoDB 모니터링 또는 상태 조회 명령의 결과 메세지

InnoDB 테이블 모니터링, 락 모니터링, 엔진 상태 조회 명령(SHOW ENGINE INNODB STATUS 등)에 대한 결과 메세지입니다.

상대적으로 큰 메세지가 기록되며, InnoDB 모니터링 활성화 후 방치 시 에러 로그 파일이 매우 커지게 되므로 모니터링 사용 후에는 다시 비활성화하여 에러 로그 파일이 커지지 않도록 주의합니다.

MySQL 종료 메세지

MySQL이 마지막으로 왜 종료됐는지 확인할 수 있는 메시지입니다. 누군가 종료시킨 것인지, Segmentation fault로 비정상 종료된 것인지 등을 확인할 수 있습니다.

만약 Segmentation fault로 인해 비정상 종료되었다면, 스택 트레이스의 내용을 최대한 참조하여 MySQL 버그와의 연관성 조사 후 버전 업그레이드하거나 회피책을 찾아야 합니다.

제너럴 쿼리 로그 파일

MySQL 서버에서 실행되는 쿼리의 전체 목록을 확인하기 위해서 쿼리 로그를 활성화하여 제너럴 쿼리 로그 파일을 검토할 수 있습니다. 슬로우 쿼리 로그와 다르게 실행되기 전에 쿼리 요청 시 바로 기록되므로 쿼리 실행 중 에러가 발생하여도 쿼리 로그 파일에 기록됩니다.

general_log_file 파라미터에 설정된 경로에 저장되며 log_output 파라미터로 쿼리 로그를 파일이 아닌 테이블에 저장하도록 설정 가능합니다.

슬로우 쿼리 로그

long_query_time 시스템 변수 설정값 이상의 시간이 소요된 쿼리가 모두 슬로우 쿼리 로그로 기록됩니다.

서비스 운영 중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝 시 어떤 쿼리가 문제의 쿼리인지 확인하기 위해 슬로우 쿼리 로그를 확인할 수 있습니다.

실제 소요 시간을 기준으로 하므로 쿼리가 정상적으로 실행 완료되어야 슬로우 쿼리 로그로 기록될 수 있습니다.
log_output 파라미터로 쿼리 로그를 파일로 기록할지 테이블로 기록할지 선택할 수 있습니다.

아래는 슬로우 쿼리 로그의 예시입니다.

# Time: 2020-07-19T15:44:22.178484+09:00
# User@Host: root[root] @ localhost []  Id:    14
# Query_time: 1.180245 Lock_time: 0.002658 Rows_sent: 1 Rows_examined: 2844047
use employees;
SET timestamp=1595141060;
select emp_no, max(salary) from salaries;

Time: 쿼리 종료 시점. 쿼리 시작 시간은 Time - Query_time
User@Host: 쿼리를 실행한 사용자
Query_time: 쿼리 실행 전체 시간
Lock_time: MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간이므로 매우 작은 값이면 무시해도 됨
Rows_examined: 쿼리 처리를 위해 접근한 레코드 건수
Row_sent: 클라이언트로 보낸 실제 처리 결과 건수. Rows_examined에 비해 매우 적다면 튜닝이 필요할 수 있음(집합 함수 쿼리 제외)

❗️MySQL 서버에 문제가 생겼을 때에는 로그 파일을 자세히 확인하는 습관을 가지자!

요약

MySQL 서버는 MySQL 엔진과 스토리지 엔진으로 나눌 수 있습니다.
엔진에서는 SQL문장을 분석하고 검증하고 최적화한 뒤 스토리지 엔진을 통해 데이터를 읽고 씁니다.
스토리지 엔진은 실제 디스크에 접근해 데이터를 읽고 쓰는 역할을 수행하며 InnoDB, MyISAM 등을 선택할 수 있습니다.
그 중 InnoDB 스토리지 엔진은 기본으로 선택되는 스토리지 엔진이며, 버퍼풀, 언두 로그 등을 통해 MVCC를 제공합니다.

스터디 코멘트

현재 운영 시 기본 값으로 어댑티브 해시 인덱스가 활성화되어 있음. 대량 삭제 작업 시 hang이 걸리는 문제로 인하여 대량 삭제 작업 전에 어댑티브 해시 인덱스를 꺼두고 작업후 다시 켬. 그러면 나중에 알아서 점점 어댑티브 해시 인덱스가 업데이트 됨

PostgreSQL은 논클러스터링 테이블이므로 insert시에 빠름.
MySQL은 insert시에 정리되어 저장되므로 읽기 성능이 높음

클라이언트 애플리케이션에서 정상적으로 접속 종료하지 못하고 프로그램이 종료된 경우에 기록되는 메세지입니다.
-> 커넥터에서 성능을 위해 일부러 종료시키기도 함. 클라이언트가 종료 메세지를 보내지 않고 종료.
(-> 도큐먼트에서 빠른 종료가 가능하다고 설명하는 기능의 뜻. DBMS 입장에서는 비정상 종료. 어플리케이션에서는 의도한 종료)
-> 수치가 맞지 않을 수 있음

애초에 DB에 접근이 안된 상태라면 DB 입장에서는 실패한 지 알 수 없음. 모니터링 결과의 값은 접근한 것에 대해서만 알 수 있음.

슬로우 쿼리가 발생하였지만 DB에 슬로우쿼리로 발생하지 않았다면, DB 이전의 과정에서 문제점이 발생한 것.

버전에 따라 기능 개선이 발생할 수 있으므로, 책 내용과 공식 도큐를 비교해서 현재도 유효한 내용인지 확인하는 것이 필요함.
MySQL 8.0 부터는 TempTable 스토리지 엔진으로 대체되었습니다. --> 현재는 오류로 인해 TempTable 스토리지 엔진이 완전히 빠지게 됨
항상 현재 도큐 내용과 비교해보기
참고: https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
참고: http://jongguheo.blogspot.com/2020/11/temptable-storage-engine.html

innodb 버퍼풀은 대부분 50~80로 설정하고 나머지는 비워두도록 옵션을 두긴하지만, 제어가 안되는 경우가 있음.
세션이 들어오면 세션별로 할당되는 값이 있는데, 커넥션을 맺을때마다 메모리가 늘어나서 메모리가 터져서 죽는 경우가 있음. 일부러 여유있게 잡아둔다.
그래서 max_connection으로 최대 커넥션 수도 제한하는 것.

thread 구조는 메모리 정보 공유도 쉽고, 메모리를 공유해서 쓰다보니 메모리 낭비도 적고 중복된 데이터에 대한 조회도 빠르다. 개발 편의
단점은 CPU를 증설했을 때 성능이 선형적으로 증가하지 않음. 어느 순간부터는 더 이상 성능이 증가하지 않음.

MySQL 스토리지 엔진과 유사한 형태가 MongoDB의 플러그인 스토리지 엔진. 서드파티로 개발한 것을 집어넣을 수 있음. 다른 DB에는 잘 없는 구조.

MySQL 엔진에서 처리가 일어나면 하드웨어 장치 중에서 CPU, Memory에 영향을 줄 수 있음. grouping, sort 시에 CPU가 터질 수 있음
스토리지 엔진에서 처리가 일어나면 하드웨어 장치 중에서 Memory, Disk에 영향을 줄 수 있음. full table scan 시에 Disk IO를 모두 사용
-> 쿼리를 보고 어떤 하드웨어 자원에 영향이 갈 지 예상 가능.
필터링 조건 별로 없이 대부분의 데이터를 조회하는 경우 full table scan 발생 -> 더 조건을 작성할 수 있는 것이 없는 지 검토
적절한 인덱스 없이 grouping, sort 시에 CPU, Memory 영향.
-> 쿼리 플랜 보기 전에 덜 영향가도록 작성 가능

핸들러가 반환하는 값을 스텝퍼스로 볼 수 있음. 전후 값을 캡쳐두고 보면 인덱스를 탔는지 안탔는지 (호출하는 API가 다름) 확인 가능.

hwwwa

다음 포스트

[RealMySQL8.0] 4장 - MySQL 엔진 아키텍처

📖 RealMySQL8.0

4장 - MySQL 엔진 아키텍처

MySQL 전체 구조

MySQL 엔진

스토리지 엔진

핸들러 API

MySQL 스레딩 구조

Foreground Thread (Client Thread)

Background Thread

메모리 할당 및 사용 구조

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

컴포넌트

쿼리 실행 구조

쿼리 파서

전처리기

옵티마이저

실행 엔진

핸들러(스토리지 엔진)

쿼리 캐시

스레드 풀

트랜잭션 지원 메타데이터

InnoDB 스토리지 엔진 아키텍처

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

외래키 지원

MVCC (Multi Version Concurrency Control)

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

자동 DeadLock 감지

장애 복구 자동화

InnoDB 버퍼 풀

버퍼 풀 구조

버퍼 풀과 리두 로그

Buffer Pool Flush

버퍼 풀 상태 백업 및 복구

Double Write Buffer

언두 로그

언두 테이블스페이스

체인지 버퍼

리두 로그 및 로그 버퍼

리두 로그

리두 로그 아카이빙

Adaptive Hash Index

어댑티브 해시 인덱스와 성능

MyISAM 스토리지 엔진 아키텍처

Key cache (Key Buffer)

운영체제의 캐시 및 버퍼

데이터 파일과 프라이머리 키(인덱스) 구조

InnoDB, MyISAM, MEMORY 스토리지 엔진

4.4 MySQL 로그 파일

에러 로그 파일

MySQL 시작 과정과 관련된 정보성 및 에러 메세지

비정상 종료 시 나타나는 InnoDB 트랜잭션 복구 메세지

쿼리 처리 도중에 발생하는 에러메세지

비정상적으로 종료된 커넥션 메세지 (Aborted connection)

InnoDB 모니터링 또는 상태 조회 명령의 결과 메세지

MySQL 종료 메세지

제너럴 쿼리 로그 파일

슬로우 쿼리 로그

요약

스터디 코멘트

[RealMySQL8.0] 5장 - 트랜잭션과 잠금

0개의 댓글