Real MySQL - Ch 4

Posted May 10, 2025

By Hyeon Jae Kim

44 min read

Real MySQL - Ch 4

Ch 04. 아키텍처

MySQL 서버는 MySQL엔진 + 스토리지 엔진으로 구성된다.

4.1 MySQL 엔진 아키텍처

MySQL 서버는 다른 DBMS에 비해 구조가 독특하다.

MySQL 서버 전체 구조

MySQL 엔진: 프론트, 기획자, 옵티마이제이션 및 실행계획을 세움
스토리지 엔진: 백엔드, 실무자, 세워진 계획을 실제로 실행
- 스토리지 엔진에 따라 트랜잭션 처리 방법이 달라진다.
- 스토리지 엔진에서 보는 트랜잭션과, MySQL 엔진에서 보는 테이블락은 다르다.

4.1.1 MySQL 전체 구조

MySQL의 독특한 구조
- MySQL 은 plugin 형태로 잘 나뉘어짐
  - 장점 : 유지보수 좋다
  - 단점 : 예상과 다른 동작을 할 수 있는 복잡하다
MySQL 엔진
- SQL 인터페이스
- SQL 파서
- SQL 옵티마이저
- 캐시 & 버퍼
스토리지 엔진
- InnoDB
- MyISAM
- Memory
- 성능 향상을 위해서 키 캐시(MyISAM) 버퍼풀 (InnoDB)

4.1.1.1 MySQL 엔진

커넥션 핸들러 : 클라이언트로부터의 접속 및 쿼리 요청
SQL 파서
전처리기
옵티마이저 : 쿼리의 최적화된 실행
ANSI SQL (American Standard SQL 지원)

4.1.1.2 스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나, 디스크 스토리지로부터 데이터를 읽어오는 부분을 담당

MySQL 엔진은 하나지만 스토리지 엔진은 여러개를 동시에 사용할 수 있다. 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.

InnoDB 스토리지 엔진

        
      
  CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB

캐싱하는 법
- MyISAM 키캐시
- InnoDB 버퍼 풀

4.1.1.3 핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때 각 스토리지 엔진에 쓰기 또는 읽기를 요청, 이를 핸들러(Handler) 요청 이라고 함

핸들러 API : 핸들러 요청에 사용되는 API

SHOW GLOBAL STATUS LIKE 'Handler%' - 얼마나 많은 데이터(레코드) 작업이 있었는지 확인 가능

4.1.2 MySQL 스레딩 구조

MySQL 서버는 스레드 기반으로 작동.

Foregroud(포그라운드) 스레드

Background(백그라운드) 스레드
실행중인 스레드 목록

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

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

클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 스레드, 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재

기본적으론 클라이언트 당 thread
클라이언트 요청시 스레드가 한개씩 연결
쓰레드를 만드는 비용이 크기 때문에 쓰레드 캐시 라는 형태로 미리 만들어놓음 => 따라서 실제 접속한 클라이언트보다 살짝 더 많은 쓰레드가 존재할 수 있음
thread_cache_size : 쓰레드 캐시에 유지할 수 있는 최대 쓰레드 개수 설정 변수
쓰레드 풀과 쓰레드 캐시는 다르다!
- 쓰레드 캐시는 그냥 쓰레드를 미리 생성해 놓고, 연결해준다
  - 요청과 쓰레드가 1 대 1 매핑, 처리 종료 시에 쓰레드 캐시에 쓰레드가 max 개수를 넘어가면 해당 쓰레드를 종료
  - 쓰레드 캐시에 max 보다 작으면 반환
- 쓰레드 풀은 만들어 놓은 쓰레드를 재사용할 수 있다
  - 요청과 쓰레드가 1 대 1 매핑이 아니라, 요청이 큐 자료구조로 들어간다

4.1.2.2 백그라운드 스레드

아래 여러 작업들이 백그라운드로 처리된다.

Insert Buffer(인서트 버퍼)를 병합하는 스레드
로그를 디스크로 기록하는 스레드
InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
데이터를 버퍼로 읽어 오는 스레드
잠금이나 데드락을 모니터링하는 스레드

4.1.3 메모리 할당 및 사용 구조

MySQL 서버 내에 존재하는 많은 쓰레드가 공유해서 사용하는 공간인지 여부에 따라 글로벌 메모리 영역 과 로컬 메모리 영역 으로 구분

1. 글로벌 메모리 영역

클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다.

모든 스레드에 의해 공유된다.

- InnoDB 버퍼 풀
- MyISAM 키 캐시
- Binary 로그 버퍼
- Redo 로그 버퍼
- 테이블 캐시

2. 세션(커넥션) 로컬 메모리 영역

MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역

각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다.

- Join 버퍼
- 정렬(Sort) 버퍼
- 네트워크 버퍼
- Read 버퍼

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

ex) 전문 검색 엔진을 위한 검색어 파서, 사용자 인증을 위한 Native Authentication, Caching SHA-2 Authentication

‘데이터 읽기/쓰기’ 작업은 대부분 1건의 레코드 단위로 처리

MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야 한다.

Handler_ 로 시작하는 상태변수 : ‘MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수’

MyISAM / InnoDB 와 같이 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하면 데이터 읽기/쓰기 영역의 차이 => GROUP BY , ORDER BY 등 복잡한 처리는 스토리지 영역이 아닌 MySQL 엔진의 처리 영역인 쿼리 실행기에서 처리

하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다.

mysql> SHOW ENGIENS;

Support 컬럼

Yes : MySQL 서버에 해당 스토리지 엔진 포함, 사용 가능으로 활성화 상태 DEFAULT : YES 와 동일 = 필수 스토리지 엔진(없으면 MySQL 시작 안될 수 있음) NO : 현재 MySQL 서버에 포함 X, 사용하려면 MySQL 서버 다시 빌드(컴파일) 필요 DISABLED : 현재 MySQL 서버에 포함, 파라미터에 의해 비활성화 상태 플러그인 형태로 빌드된 스토리지 엔진 라이브러리 다운로드해서 사용 가능 -> 손쉽게 업그레이드 가능

4.1.5 컴포넌트

MySQL 8.0 부터 기존의 플러그인 아키텍처를 컴포넌트 아키텍처로 대체함

플러그인 단점

오직 MySQL 서버와 인터페이스할 수 있고 플러그인끼리 통신 불가
MySQL 서버의 변수나 함수를 직접 호출하기 때문에 불안전(캡슐화 안됨)
상호 의존 관계를 설정할 수 없어 초기화 어려움
MySQL 5.7까지 비밀번호 검증 기능이 플러그인 형태 -> MySQL 8.0은 컴포넌트로 개선

        
      
mysql> INSTALL COMPONENT 'file://component_validate_password';

4.1.6 쿼리 실행 구조

쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업

쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지 전달

전처리기

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

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

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

옵티마이저

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정

DBMS의 두뇌

실행 엔진

DBMS의 손과 발

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

ex) 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블 사용

	1. 실행 엔진이 핸들러에게 임시 테이블 만들라고 요청
    2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 저장하라고 다시 핸들러에게 요청

핸들러(스토리지 엔진)

MySQL 가장

4.1.7 복제(Replication)

4.1.8 쿼리 캐시(Query Cache)

SQL 실행 결과를 메모리에 캐시, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과 반환

테이블의 데이터 변경 시 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제(Invalidate) -> 동시 처리 성능 저하 유발, 많은 버그 원인

MySQL 8.0 부터 제거

4.1.9 스레드 풀

MySQL 서버 엔터프라이즈 에디션에서만 제공하는 기능

내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많아도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게하여 서버 자원 소모를 줄이는 목적

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

제한된 수의 스레드만으로 CPU가 처리하도록 유도하면 CPU의 프로세서 친화도 상승, 불필요한 컨텍스트 스위치를 줄여 오버헤드 감소 가능

CPU 코어 개수와 맞추는 것이 CPU 프로세서 친화도 상승 가능

MySQL 서버가 처리해야 할 요청이 생기면 스레드 풀로 처리를 이관하는데 이미 스레드 풀이 처리 중인 작업이 있는 경우, thread_pool_oversubscribe (기본값 3) 에 설정 된 개수만큼 추가로 더 받아들여 처리하는데 너무 크면 스케줄링 해야 할 스레드가 많아져 스레드 풀이 비효율적으로 작동 가능

스레드 그룹의 모든 스레드가 일을 처리하면 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드를 추가할 지 아니면 기존 작업 스레드가 처리를 완료할 때까지 기다릴 지 여부 판단

thread_pool_stall_limit 시스템 변수 에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 작업을 끝내지 못하면 새로운 스레드를 생성하여 스레드 그룹에 추가
전체 스레드 풀에 있는 스레드의 개수는 thread_pool_max_threads 시스템 변수에 설정된 개수를 넘을 수 없음
응답 시간에 민감한 서비스면 thread_pool_stall_limit 을 낮춰 설정하지만 0과 가까운 값으로 설정 비추천(스레드 풀 사용하는 이유 없음)

Percona Server = 플러그인 형태

선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능 제공

먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있던 잠금이 빨리 해제되고 잠금 경합을 낮춰서 전체적인 처리 성능 향상 가능

4.1.10 트랜잭션 지원 메타 데이터

데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보

MySQL 5.7 까지 테이블의 구조를 FRM 파일 에 저장하고 일부 스토어드 프로그램 또한 파일 기반 으로 관리

파일 기반 은 생성 및 변경 작업이 트랜잭션을 지원하지 않아 테이블의 생성 또는 변경 도중 MySQL 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남는 문제 발생(데이터베이스나 테이블이 깨졌다)
MySQL 8.0 부터 InnoDB 테이블 에 저장
시스템 테이블(MySQL 서버가 작동하는 데 기본적으로 필요한 테이블)을 InnoDB 스토리지 엔진을 사용, 시스템 테이블과 데이터 딕셔너리 정보를 모두 모아 mysqlDB 에 저장
mysql DB는 mysql.ibd 테이블 스페이스에 저장
InnoDB 스토리지 엔진 이외의 스토리지 엔진을 사용하는 테이블을 위해 SDI(Seriaized Dictionary Information) 파일 사용(기존의 *.FRM 파일과 동일)

4.2 InnoDB 스토리지 엔진 아키텍처

InnoDB 가 스토리지 엔진 중 거의 유일하게 레코드 기반 잠금 제공하여 높은 동시성 처리, 안정적, 뛰어난 성능 제공

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

InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장

프라이머리 키값의 순서대로 디스크에 저장, 모든 세컨데리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용

쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정

오라클 DBMS의 IOT(Index organized table)와 동일한 구조

MyISAM 스토리지 엔진

클러스터링 키 지원 안하여 프라이머리 키는 유니크 제약을 가진 세컨더리 인덱스일 뿐이다.
프라이머리 키를 포함한 모든 인덱스는 레코드의 주소 값(ROWID) 가지고 있다.

4.2.2 외래 키 지원

InnoDB 스토리지 엔진에서 지원하는 기능

다른 엔진에서는 지원하지 않음

데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터 베이스에서는 생성 안함

부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성 필요하고 변경 시에는 반드시 부모/자식 테이블에 데이터가 있는 지 체크하는 작업 필요하여 잠금이 여러 테이블로 전파되고 데드락이 발생함

수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업 실패 가능

foreign_key_checks 시스템 변수를 OFF로 설정하면 체크 작업 일시적으로 멈출 수 있음 -> 외래 키 관계의 부모 테이블에 작업도 무시

foreign_key_checks 시스템 변수는 적용 범위를 GLOBAl, SESSION 모두 설정 가능. 반드시 현재 작업을 실행하는 세션에서만 기능을 멈추는 것을 추천.

        
mysql> SET foreign_key_checks=OFF;
mysql> SET SESSION foreign_key_checks=OFF;

SESSION 키워드 명시하지 않으면 자동으로 현재 세션의 설정만 변경 작업 완료 후, 반드시 현재 세션을 종료하거나 현재 세션의 외래 키 체크를 다시 활성화 처리 추천

4.2.3 MVCC(Multi Version Concurrency Control)

멀티 버전 = 하나의 레코드에 대해 여러 개의 버전이 동시에 관리

잠금을 사용하지 않는 일관된 읽기 제공

InnoDB는 Undo log를 이용 INSERT 문 실행시 아래와 같이 바뀜

        
      
mysql> UPDATE member SET m_area='경기' WHER m_id=12;

UPDATE 문장이 실행되면 커밋 실행 여부와 관계없이 InnoDB 버퍼 풀은 새로운 값으로 업데이트되지만 디스크의 데이터 파일은 새로운 값으로 업데이트 될 수도 있고 아닐 수도 있음(InnoDB는 ACID를 보장하기 때문에 InnoDB의 버퍼 풀과 데이터 파일은 동일한 상태라고 가정해도 무방)
commit 이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 레코드 조회를 하면 MySQL 서버의 transaction_isolation에 설정된 Isolation level에 따라 다른 값이 나옴.
READ_UNCOMMITED인 경우, InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환
READ_COMMITTED나 그 이상의 격리 수준(REPEATABLE_READ, SERALIZABLE)인 경우 커밋 전이기 때문에 InnoDB 버퍼 풀이나 데이터 파일에 있는 내용 대신 변경 전 내용을 보관하고 있는 언두 영역의 데이터 반환 => MVCC
COMMIT 명령을 실행하면 InnoDB는 더 이상 변경 작업 없이 지금의 상태를 영구적인 데이터로 변환
롤백 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구, 언두 영역의 내용을 삭제
커밋이 된다고 바로 언두 영역의 백업 데이터가 삭제되지 않고 필요로 하는 트랜잭션이 더는 없을 때 삭제

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

MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업 수행

격리 수준이 SERALIZABLE이 아닌 READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ 인 경우 INSERT와 연결되지 않은 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행

트랜잭션이 시작됐다면 가능한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료 추천

4.2.5 자동 데드락 감지

내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리

주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료

어느 것을 먼저 강제 종료할 것인지 판단하는 기준은 트랜잭션의 언두 로그 양, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상

InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금은 볼 수 없어서 데드락 감지 불확실 => innodb_table_locks 활성화 하면 가능

동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려짐 => 더 많은 CPU 자원 소모

innodb_deadlock_detect=OFF 로 설정하면 작동 X => 2개 이상의 트랜잭션이 상대방이 가진 잠금을 요구하는 상황이 발생해도 누군가가 중재를 하지 않기 때문에 무한정 대기
innodb_lock_wait_timeout 활성화하면 데드락 상황에서 일정 시간이 지나면 자동으로 요청 실패 후 에러 메시지 반환, 초단위로 설정 가능, 50초보다 낮은 시간 추천

4.2.6 자동화된 장애 복구

손실이나 장애로부터 데이터를 보호하기 위한 여러 가지 메커니즘 탑재

MySQL 서버와 무관하게 디스크나 하드웨어 이슈로 자동 복구 못하는 경우 innodb_force_recovery 시스템 변수 설정해서 MySQL 서버 시작

MySQL 서버가 기동되고 InnoDB 테이블이 인식된다면 mysqldump 를 이용해 데이터를 가능한 만큼 백업하고 MySQL 서버의 DB와 테이블을 다시 생성 추천

만약 MySQL 서버가 시작되지 않으면 백업 이용하여 다시 구축

마지막 백업으로 데이터베이스를 새로 구축, 바이너리 로그를 사용해 최대한 장애 시점까지의 데이터 복구
InnoDB 복구보다 풀 백업과 바이너리 로그로 복구하는 편이 데이터 손실이 적음

백업은 있지만 복제의 바이너리 로그가 없거나 손실됐다면 마지막 백업 시점까지만 복구 가능

4.2.7 InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간

쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업 횟수 줄일 수 있음

버퍼 풀 크기 설정

운영체제와 각 클라이언트 스레드가 사용할 메모리 고려하여 설정

레코드 버퍼는 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간

커넥션이 많고 사용하는 테이블도 많으면 많이 필요

레코드 버퍼 공간은 별도 설정 불가, 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블의 개수에 따라서 결정

MySQL 5.7부터 동적조절 가능 크기를 줄이는 방법은 서비스의 영향도가 크기 때문에 비추천 128MB 청크 단위로 쪼개어 관리되어 줄이거나 늘릴 때 128MB 단위로 처리 버퍼 풀 전체를 관리하는 잠금으로 인해 내부 잠금 경합을 많이 유발했지만 여러개로 쪼개어 관리할 수 있게 개선 innodb_buffer_pool_instances로 여러 개로 분리해서 관리하고 각 버퍼 풀을 버퍼 풀 인스턴스 8개로 초기화, 메모리 크기가 1GB 미만이면 하나 생성

버퍼풀의 구조

페이지 크기(innodb_page_size)의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 읽어서 각 조각에 저장
프리 리스트 : InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록
LRU 리스트 : LRU + MRU 리스트

디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기 최소화

자주 사용되면 MRU에 계속 있고, 아니면 LRU의 끝으로 밀려나서 InnoDB버퍼풀에서 제거

더티 페이지 : 디스크로 동기화되지 않은 데이터를 가진 페이지

플러시 리스트는 더티페이지의 변경 시점 기준의 페이지 목록 관리

데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에 반영(보장 X)

버퍼 풀과 리두 로그

** 버퍼 풀**

서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링
메모리 공간 늘리면 캐시 기능 향상

** 리두 로그 **

1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용
재사용 가능한 공간과 불가능한 공간을 구분(Active Redo Log)
체크 포인트 에이지는 활성 리두 로그 공간의 크기
더티 페이지는 특정 리두 로그 엔트리와 관계, 체크 포인트 발생하면 체크포인트 LSN(Long Sequence Number) 보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화

버퍼 풀 플러시(Buffer Pool Flush)

InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 동기화 하기 위해 Flush list 플러시, LRU 리스트 플러시 실행

플러시 리스트 플러시

InnoDB 스토리지 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 리두 로그 앤트리가 사용하는 공간을 비워야하는데, 이 때 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화 필요
플러시 리스트 플러시 함수를 호출해 동기화
더티 페이지를 디스크로 동기화 하는 클리너 스레드가 하나의 버퍼 풀 인스턴스 처리하도록 자동으로 맞춰 줌
더티 페이지가 많으면 디스크 쓰기 폭발 현상 발생 가능
어뎁티브 플러시를 사용하면 버퍼 풀의 더티 페이지 비율이나 설정값 의존하지 않고 알고리즘 사용

LRU 리스트 플러시

LRU 리스트의 끝부분부터 시작해서 설정 개수의 페이지들 스캔하면서 더티 페이지는 디스크에 동기화
클린 페이지는 프리 리스트로 옮겨짐

버퍼 풀 상태 백업 및 복구

MySQL 5.6 부터 도입
백업된 내용을 각 테이블의 데이터 페이지를 다시 디스크에서 읽어와야 되서 시간이 많이 걸림

버퍼 풀의 적재 내용 확인

MySQL 5.6부터 innodb_buffer_page 테이블에서 적재 확인 가능
InnoDB 버퍼 풀이 큰 경우 조회가 느려져서 MySQL 8.0부터 innodb_cached_indexes 테이블 추가

4.2.8 Double Write Buffer

InnoDb 스토리지 엔진의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록하고 더티페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제가 발생 가능

Partial-page, Torn-page 문제
- 페이지가 일부만 기록되는 현상
- Redo 로그가 페이지 전체가 아니라 변경된 내용만 기록하기 때문에 발생
Double-Write 를 통해 문제를 해결
- HDD 에서는 좋지만, SSD 에서는 Double-Write 를 비활성화하는게 좋음

4.2.9 언두 로그(Undo Log)

InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 Insert, Update, Delete 로 변경되기 이전 버전의 데이터를 별도로 백업

Transaction 과 Isolation Level 에 필요

트랜잭션 보장 트랜잭션이 롤백 되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데, 이때 언두 로그에 백업해 둔 이전 버전 데이터를 이용해 복구
격리 수준 보장 특정 커넥션에서 데이터 변경하는 도중에 다르 커넥션에서 데이터 조회하면 트랜잭션 격리 수준에 맞게 변경중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.

언두 로그 레코드 모니터링

MySQL 8.0 에서 언두 로그를 돌아가면서 순차적으로 사용해 디스크 공간을 줄이거나 MySQL 서버가 필요한 시점에 사용 공간을 자동으로 조절

언두 테이블스페이스 관리

언두로그가 저장되는 공간

MySQL 8.0부터 시스템 테이블 스페이스 외부의 별도 로그 파일에 기록

언두 로그 슬롯이 부족하면 트랜잭션을 시작 못하여 언두 테이블 스페이스와 롤백 세그먼트의 개수를 설정

자동/수동 모드를 통해 언두 테이블스페이스의 불필요한 공간을 잘라서 이용

4.2.10 체인지 버퍼

InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트 해야한다면 즉시 실행 안하고 임시 공간에 저장하여 사용자에게 반환하는 형태로 성능 향상

쿼리 속도를 빠르게 해줌

4.2.11 Redo 로그 및 로그 버퍼

ACID 중 D(Durable)
서버가 비정상적으로 종료시, 기록되지 못한 데이터를 잃지 않게 해줌
데이터 변경 내용을 Redo Log 로 기록해서, 변경실패시 재요청

리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정 권장

리두 로그 아카이빙

MySQL8.0 부터 가능

데이터 파일을 복사하는 동안 리두 로그에 쌓인 내용을 계속 추적하면서 새로 추가된 리두 로그 엔트리를 복사

리두 로그 활성화 및 비활성화

MySQL 8.0 부터 활성화 비활성화 처리 가능

4.2.12 어댑티브 해시 인덱스

사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스

인덱스 키 값은 B-Tree 인덱스의 고유 번호와 B-Tree 인덱스의 실제 키 값 조합으로 생성

데이터 페이지 주소는 실제 키 값이 저장된 데이터 페이지의 메모리 주소이며 InnoDB 버퍼 풀에 로딩괸 페이지의 주소

해시 인덱스 히트율과 어댑티브 해시 인덱스가 사용중인 메모리 공간, 서버의 CPU 사용량을 종합해서 판단

4.3 MyISAM 스토리지 엔진 아키텍처

InnoDB 가 압도적으로 우세하므로 간단하게만 정리

4.3.1 키 캐시

InnoDB 의 버퍼 풀과 비슷한 역할

인덱스만을 대상으로 작동

4.3.2 운영체제의 캐시 및 버퍼

데이터에 대해서는 어떠한 캐시나 버퍼링 기능도 없다.

데이터 읽기나 쓰기 작업은 항상 운영체제의 디스크 읽기 or 쓰기 작업으로 요쳥될 수 밖에 없다.

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

InnoDB 스토리지 엔진에서는 프라이머리 키에 의해 클러스터링 되었지만,
MyISAM 은** 클러스터링 없이 데이터파일이 힙(Heap) 공간처럼 활용된다.**

4.4 MySQL 로그 파일

서버 깊은 내부 지식 없이도 서버 상태나 부하 일으키는 원인 쉽게 찾을 수 있다.

4.4.1 에러 로그 파일

실행 도중 발생하는 에러나 경고 메시지의 로그 파일
my.cnf 에서 log_error 에 정의된 경로에 .err 확장자 파일로 생성됨

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

실행된 쿼리가 무엇이 있었는지 확인가능한 로그
my.cnf 의 general_log_file 에 정의된 경로에 생성
로그를 파일이 아닌 테이블에 저장하도록 할 수도 있음

4.4.3 슬로우 쿼리 로그

long_query_time 으로 설정한 실행 시간을 초과한 쿼리들을 기록
쿼리 실행시간을 통계낼 때 편리한 로그

데이터베이스

Database RealMySQL 8.0

This post is licensed under CC BY 4.0 by the author.