아키텍쳐

  • MySQL의 서버는 머리 역할을 하는 MySQL 엔진과 손과 발 역할을 하는 스토리지 엔진으로 구분한다.

  • 이 중 스토리지 엔진은 핸들러 API를 만족시키면 누구든 구현하여 사용 가능하다.

    • 대표적인 스토리지 엔진은 InnoDB 스토리지 엔진, MyISAM 스토리지 엔진이 있다.

4.1 MySQL 엔진 아키텍처

4.1.1 MySQL의 전체 구조

  • MySQL도 다른 RDBMS와 마찬가지로 대부분의 프로그래밍 언어로부터 접근 방법을 지원한다.

  • 이 책에서는 MySQL엔진과 스토리지엔진 영역으로 구분하여 설명하고 이를 합쳐서 MySQL 서버라고 한다.

4.1.1.1 MySQL 엔진

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

4.1.1.2 스토리지 엔진

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

MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.

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

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

4.1.1.3 핸들러 API

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

4.1.2 MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 동작하며, 크게 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.

  • 엔터프라이즈 에디션과 Percona MySQL 서버에서는 스레드 풀 모델을 사용할 수 있다.

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

포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.

커넥션을 종료하면 스레드 캐시로 돌아간다. 이 때 이미 스레드 캐시에 일정 개수 이상의 스레드가 있으면 스레드를 종료시킨다. (thread_cache_size 이상이면)

InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.

4.1.2.2 백그라운드 스레드

InnoDB는 다음의 작업을 백그라운드로 처리한다.

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

사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다.

InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다. 하지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 긴으을 사용할 수 없다.

4.1.3 메모리 할당 및 사용 구조

메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.

글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당된다.

4.1.3.1 글로벌 메모리 영역

글로벌 메모리 영역은 모든 스레드에 의해 공유된다.

대표적인 글로벌 메모리 영역은 다음과 같다.

테이블 캐시
InnoDB 버퍼 풀
InnoDB 어댑티브 해시 인덱스
InnoDB 리두 로그 버퍼

4.1.3.2 로컬 메모리 영역

세션 메모리 영역, 클라이언트 메모리 영역이라고도 한다.

절대 공유되어 사용되지 않는다는 특징이 있다.

대표적인 로컬 메모리 영역은 다음과 같다.

정렬 버퍼
조인 버퍼
바이너리 로그 캐시
네트워크 버퍼

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

MySQL의 독특한 구조 중 대표적인 것이 플러그인 모델이다.

스토리지 엔진, 전문 검색 엔진을 위한 검색어 파서, 사용자 인증을 위한 Authentication 등이 플러그인으로 구현되어 제공된다.

MySQL에서 쿼리가 실행되는 과정에서 거의 대부분 MySQL 엔진에서 처리되고, 마지막 '데이터 읽기/쓰기' 작업만 스토리지 엔진에 의해 처리된다.

데이터 읽기/쓰기 작업은 핸들러(스토리지 엔진)에서 처리한다.

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

  • group by나 order by등 복잡한 처리는 스토리지 엔진 영역이 아니라 MySQL 엔진의 처리 영역인 '쿼리 실행기'에서 처리된다.

4.1.5 컴포넌트

MySQL 8.0부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다.

플러그인읜 단점

플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없음
플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음
플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움

4.1.6 쿼리 실행 구조

4.1.6.1 쿼리 파서

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

4.1.6.2 전처리기

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

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

4.1.6.3 옵티마이저

  • 사용자 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당하며, DBMS의 두뇌에 해당한다고 볼 수 있다.

4.1.6.4 실행 엔진

옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다.

실행 엔진이 하는 일을 예를 들어 살펴보자. 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 해보자

실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
즉, 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 욫어의 입력으로 연결하는 역할을 수행한다.

4.1.6.5 핸들러(스토리지 엔진)

핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다.

InnoDB 스토리지 엔진

4.1.7 복제

4.1.8 쿼리 캐시

SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보였다.

하지만, 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들을 모두 삭제해야 해서 심각한 동시처리 성능 저하를 유발한다.

8.0 버전부터는 서버의 기능에서 완전히 삭제되었다.

4.1.9 스레드 풀

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

테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 한다.

MySQL 5.7버전 까지는 FRM 파일에 저장하고 관리했다. 파일 기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블의 생성 및 변경 도중에 MySQL 서버가 비정상 종료되면 일관되지 않은 상태로 남아서 데이터베이스나 테이블이 깨질 수 있다.

MySQL 8.0버전 부터는 관련 정보를 모두 InnoDB 테이블에 저장하도록 개선됐다.

  • 시스템 테이블과 데이터 딕셔너리 정보를 모두 모아서 mysql DB에 저장하고 있다. mysql.ibd라는 이름의 테이블 스페이스에 저장된다.

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

스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공한다.

그 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

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

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

  • 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며, 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다. 프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.

4.2.2 외래 키 지원

외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 주의해야 한다.

4.2.3 MVCC(Multi Version Concurrency Control)

목적 : 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다.

  • InnoDB는 언두 로그를 이용해 이 기능을 구현한다.

멀티 버전이라 함은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미다.

다음의 예를 살펴보자

INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울');
COMMIT:
UPDATE memer SET m_area='경기' WHERE m_id=12;

위와 같이 업데이트 문장이 실행되면 커밋 실행 여부와 관계없이 InnoDB 버퍼 풀은 새로운 값인 '경기'로 업데이트된다.

  • 아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 다음과 같은 쿼리로 작업 중인 레코드를 조회하면 어디에 있는 데이터를 조회할까?

SELECT * FROM member WHERE m_id=12;

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

격리 수준이 READ_UNCOMMITTED인 경우에는 InnoDB버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다.
READ_COMMITTED나 그 이상의 격리 수준인 경우에는 언두 영역의 데이터를 반환한다.
이러한 과정을 DBMS에서는 MVCC라고 표현한다.
즉, 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조다.
커밋이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는 것은 아니다. 이 언두 영역을 필요로 하는 트랜잭션이 더는 없을 때 비로소 삭제된다

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

InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.

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

트랜잭션이 오랫동안 지속되면 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하고 이로인해 MySQL서버가 느려지거나 문제가 발생할 수 있다.

트랜잭션이 시작됐다면 가능한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.

4.2.5 자동 데드락 감지

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

데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그 중 하나를 강제 종료한다.

  • 어느 트랜잭션을 종료할지 판단하는 기준은 언두 로그의 양이다. (언두 로그가 적으면 롤백도 적게 한다.)

데드락 감지 스레드는 잠금 목록을 검사해야 하기 때문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트에 새로운 잠금을 걸고 데드락 스레드를 찾게 된다. 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업을 진행하지 못하고 대기하면서 서비스에 악영향을 미치게 된다.

innodb-deadlock_detect 시스템 변수를 통해 데드락 감지 스레드의 작동을 제어할 수 있다.
OFF 로 설정하면 스레드가 작동하지 않는다.
데드락 감지 스레드가 작동하지 않을 때는 innodb_lock_wait_timeout 시스템 변수를 활성화하여 일정 시간이 지나면 자동으로 요청이 실패하도록 타임아웃을 설정하자.

4.2.6 자동화된 장애 복구

MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다.

이 단계에서 자동으로 복구될 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 서버는 종료돼 버린다. 이때는 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 한다.

InnoDB의 로그 파일이 손상됐다면 6으로 설정하고 MySQL 서버를 기동한다.
InnoDB 테이블의 데이터 파일이 손상됐다면 1로 설정하고 MySQL 서버를 기동한다.
어떤 부분이 문제인지 알 수 없다면 설정값을 1 부터 6까지 변경하면서 재시작해본다.
이래도 안되면 백업을 이용해 다시 구축하는 방법밖에 없다.
Setting
innodb_force_recovery
1(SRV_FORCE_IGNORE_CORRUPT)
2(SRV_FOrCe_NO_BACKGROUND)
3(SRV_FORCE_NO_TRX_UNDO)
4(SRV_FORCE_NO_IBUF_MERGE)
5(SRV_FORCE_NO_UNDO_LOG_SCAN)
6(SRV_FORCE_NO_LOG_REDO)

4.2.7 InnoDB 버퍼 풀

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

쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다.

쓰기 작업(INSERT, UPDATE, DELETE)이 랜덤 디스크 작업을 발생시키는데 버퍼 풀이 이러한 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다.

4.2.7.1 버퍼 풀의 크기 설정

InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 봐 가면서 증가시키는 방법이 최적이다.

  • innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있다.

  • 늘리는 것은 시스템 영향도가 크지 않은데, 줄이는 것은 매우 크다.

버퍼 풀은 여러개의 인스턴스로 쪼개어 관리할 수 있게 개선됐다.

  • innodb_buffer_pool_instances 시스템 변수를 이용해 여러 개로 분리하여 관리할 수 있다. 기본은 8개다.

4.2.7.2 버퍼 풀의 구조

InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기(innodb_page_size)의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다.

버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 크게 LRU(Least Recently Used) 리스트와 플러시 리스트, 프리 리스트라는 3개의 자료구조를 관리한다.

  • 프리 리스트는 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록

LRU 리스트는 다음과 같다.

  • 엄밀하게 말해서 LRU와 MRU(Most Recently Used)리스트가 결합된 형태라고 보면 된다.

  • OLD 서브리스트 영역은 LRU, New 서브리스트 영역은 MRU

  • InnoDB 스토리지 엔진에서 데이터를 찾는 과정은 다음과 같다.

    1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사 A. InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색 B. 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색 C. 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급

    2. 디스크에 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가

    3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동(Read Ahead와 같이 대량 읽기의 경우 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만 실제 쿼리에서 사용되지는 않을 수도 있으며, 이런 경우에는 MRU로 이동되지 않음)

    4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이(Age)가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지에 부여된 나이가 오래되고 결구 해당 페이지는 버퍼 풀에서 제거된다. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨진다.

    5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

  • 처음 한 번 읽힌 데이터 페이지가 이후 자주 사용된다면 그 페이지는 InnoDB 버퍼 풀의 MRU 영역에서 계속 살아남게 되고, 반대로 거의 사용되지 않는다면 새롭게 디스크에서 읽히는 데이터 페이지들에 밀려서 LRU의 끝으로 밀려나 결국은 InnoDB 버퍼 풀에서 제거될 것이다.

  • 플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리한다.

    • 특정 시점이 되면 디스크로 기록돼야 한다.

    • 데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다.

  • InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화한다.

4.2.7.3 버퍼 풀과 리두 로그

  • InnoDB 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리의 성능이 빨라진다.

  • InnoDB 버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데, 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐시 기능만 향상시키는 것이다.

  • 쓰기 버퍼링 성능 향상을 위해서는 버퍼 풀과 리두 로그 파일의 전체 크기를 같이 조정해야 한다.

4.2.7.4 버퍼 풀 플러시

4.2.7.4.1 플러시 리스트 플러시

  • 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야한다. 이때 오래된 리두 로그 공간이 지워지려면 반드시 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화돼야 한다.

    • 이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트 플러시 함수를 호출해서 동기화 작업을 수행한다.

  • InnoDB 스토리지 엔진에서 더티 페이지를 디스크로 동기화하는 스레드를 클리너 스레드라고 하는데, innodb_page_cleaners 시스템 변수는 클리너 스레드의 개수를 조정할 수 있게 해준다.하나의 클리너 스레드가 하나의 버퍼 풀 인스턴스를 담당하면 되므로 innodb_page_cleaners 설정값은 innodb_buffer_pool_instances 설정값과 동일한 값으로 설정하자

4.2.7.4.2 LRU 리스트 플러시

  • LRU리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 하는데 이를 위해 LRU 리스트 플러시 함수가 사용된다.

  • LRU 리스트의 끝부분부터 시작해서 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼의 페이지들을 스캔한다.

4.2.7.5 버퍼 풀 상태 백업 및 복구

  • InnoDB 서버의 버퍼 풀은 쿼리의 성능에 매우 밀접하게 연결돼 있다. 쿼리 요청이 매우 빈번한 서버를 셧다운했다가 다시 시작하고 서비스를 시작하면 쿼리 처리 성능이 평상시보다 1/10도 안되는 경우가 대부분일 것이다. 버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비돼 있으므로 디스크에서 데이터를 읽지 않아도 쿼리가 처리될 수 있기 때문이다.

  • MySQL5.5 버전에서는 강제 워밍업을 위해 주요 테이블과 인덱스에 대해 풀 스캔을 하고 서비스를 오픈했었다.

  • 5.6 버전부터는 버퍼 풀 덤프 및 적재 기능이 도입됐다.

  • innodb_buffer_pool_dump_now 시스템 변수를 이용해 백업 하고 시스탬 재시작 후 innodb_buffer_pool_load_now=ON 으로 복구한다.

    • 버퍼 풀의 백업과 복구를 자동화하려면 innodb_buffer_pool_dump_at_shutdown 과 innodb_buffer_pool_load_at_startup 설정을 넣어두면 된다.

4.2.7.6 버퍼 풀의 적재 내용 확인

  • MySQL 5.6 버전부터 MySQL 서버의 information_schema 데이터베이스의 innodb_buffer_page 테이블을 이용해 InnoDB 버퍼 풀의 메모리에 어떤 테이블의 페이지들이 적재돼 있는지 확인할 수 있었다.

4.2.8 Double Write Buffer

  • InnoDB 스토리지 엔진의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다.

  • 이로 인해 InnoDB 스토리지 엔진에서 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제가 발생하면 복구할 수 없을 수도 있다.

    • 이 문제를 파셜 페이지, 톤페이지라고 한다.

    • 이를 해결하기 위해 Double_Write 기법을 이용한다. innodb_doublewrite 시스템 변수로 제어할 수 있다.

    • 데이터 무결성이 매우 중요한 서비스에서 활성화를 고려하자.

4.2.9 언두 로그

  • InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업한다.

  • 언두로그의 목적

    • 트랜잭션 보장 : 트랜잭션이 롤백되면 도중 변경된 데이터를 변경 전 데이터로 복구할 때 사용한다.

    • 격리 수준 보장 : 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 레코드를 읽거나 언두 로그를 읽어서 반환한다.

4.2.9.1 언두 로그 레코드 모니터링

  • 대용량의 데이터를 처리하는 트랜잭션에서는 언두로그가 대용량으로 쌓일 수 있다.

  • 트랜잭션이 오랜 시간동안 실행될 때도 언두 로그의 양은 급격히 증가할 수 있다.

4.2.9.2 언두 테이블스페이스 관리

  • 언두 로그가 저장되는 공간을 언두 테이블스페이스라고 한다.

4.2.10 체인지 버퍼

  • RDBMS에서 레코드가 INSERT되거나 UPDATE될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요하다.

  • 그런데 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모하게된다.

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

  • 이때 사용하는 임시 메모리 공간을 체인지 버퍼라고 한다.

    • 중복 여부를 체크해야하는 유니크인덱스는 체인지 버퍼를 사용할 수 없다.

    • 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드라고 한다.

4.2.11 리두 로그 및 로그 버퍼

  • 리두 로그는 트랜잭션의 4가지 요소인 ACID중에서 D(Durable)에 해당하는 영속성과 가장 밀접하게 연관돼 있다.

  • 리두 로그는 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.

    • 데이터 변경 내용은 로그로 먼저 기록한다.

  • 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료 구조를 가지고 있기 때문에 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요하다.

    • 그래서 변경된 데이터를 기록하려면 상대적으로 큰 비용이 발생하는데 이를 최소화하기 위해 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있다.

    • 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전의 상태로 복구한다.

  • 비정상 종료 시 다음과 같은 일관되지 않은 데이터를 가질 수 있다.

    1. 커밋됐지만 데이터 파일에 기록되지 않은 데이터

    2. 롤백됐지만 데이터 파일에 이미 기록된 데이터

  • 1번의 경우 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 된다.

  • 2번의 경우 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면 된다.

    • 2번의 경우도 리두로그를 통해 그 변경이 커밋됐는지, 롤백됐는지 확인해야 한다.

4.2.11.1 리두 로그 아카이빙

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

  • InnoDB 스토리지 엔진의 리두 로그는 하드웨어나 소프트웨어 등 여러가지 문제점으로 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 트랜잭션을 복구하기 위해 항상 활성화돼있다.

  • MySQL 서버에서 트랜잭션이 커밋돼도 데이터 파일은 즉시 디스크로 동기화되지 않는 반면, 리두 로그(트랜잭션 로그)는 항상 디스크로 기록된다.

  • MySQL 8.0 버전부터는 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우 다음과 같이 리두 로그를 비활성화 해서 데이터의 적재 시간을 단축시킬 수 있다.

    1. ALTER INSTANCE DISABLE INNODB REDO_LOG;

    2. 데이터 적재

    3. ALTER INSTANCE ENABLE INNODB REDO_LOG;

4.2.12 어댑티브 해시 인덱스

  • 일반적으로 '인덱스'라고 하면 이는 테이블에 사용자가 생성해둔 B-Tree 인덱스를 의미한다.

  • 어댑티브 해시 인덱스는 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이며, 사용자는 innodb_adaptive_hash_index 시스템 변수를 이용해서 어댑티브 해시 인덱스 기능을 활성화하거나 비활성화할 수 있다.

  • 어댑티브 해시 인덱스는 B-Tree 검색 시간을 줄여주기 위해 도입된 기능이다.

  • InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다.

  • B-Tree를 루트 노드부터 리프 노드까지 찾아가는 비용이 없어지고 그만큼 CPU는 적은 일을 하지만 쿼리의 성능은 빨라진다.

  • 해시 인덱스는 인덱스 키 값과 데이터 페이지 주소의 쌍으로 관리된다.

    • 인덱스 키 값은 B-Tree 인덱스의 고유번호와 B-Tree 인덱스의 실제 키 값 조합으로 생성된다. (어댑티브 해시 인덱스는 스토리지 엔진에서 하나만 존재한다.)

    • 데이터 페이지 주소는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가진다. 이는 InnoDB 버퍼 풀에 로딩된 페이지의 주소를 의미한다.

4.2.13 InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교

  • 걍 InnoDB가 서열정리 했다고 보면 된다.

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

4.3.1 키 캐시

4.3.2 운영체제의 캐시 및 버퍼

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

4.4 MySQL 로그 파일

  • MySQL 서버에 문제가 생겼을 때는 다음에 설명하는 로그 파일들을 자세히 확인하는 습관을 들이자.

4.4.1 에러 로그 파일

  • MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일이다.

  • 에러 로그 파일의 위치는 MySQL 설정 파일(my.cnf)에서 log_error 라는 이름의 파라미터로 정의된 경로에 생성된다.

  • 별도로 지정하지 않은 경우에는 데이터 디렉터리(datadir 파라미터에 설정된 디렉터리)에 .err라는 확장자가 붙은 파일로 생성된다.

4.4.1.1 MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지

4.4.1.2 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB 트랜잭션 복구 메시지

4.4.1.3 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지

  • 사전 예방이 어려우며, 주기적으로 에러 로그 파일을 검토하는 과정에서 알게 된다.

  • 쿼리의 실행 도중 발생한 에러나 복제에서 문제가 될 만한 쿼리에 대한 경고 메시지가 에러로그에 기록된다.

4.4.1.4 비정상적으로 종료된 커넥션 메시지

  • 클라이언트 애플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우 MySQL 서버의 에러 로그 파일에 이런 내용이 기록된다.

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

4.4.1.6 MySQL의 종료 메시지

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

  • MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토해 볼 때가 있는데, 이때는 쿼리 로그를 활성화해서 쿼리를 쿼리 로그 파일로 기록하게 한 다음, 그 파일을 검토하면 된다.

  • 쿼리 로그 파일의 경로는 general_log_file 이라는 이름의 파라미터에 설정돼 있다.

  • 쿼리 로그를 파일이 아닌 테이블에 저장하도록 설정할 수도 있다.

  • 쿼리 로그를 파일로 저장할지 테이블로 저장할지는 log_output 파라미터로 결정된다.

4.4.3 슬로우 쿼리 로그

  • 서비스에서 사용되는 쿼리 중에서 문제를 판단하는 데 슬로우 쿼리 로그가 상당히 많은 도움이 된다.

  • 슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다.

  • log_output 옵션을 이용해 슬로우 쿼리 로그를 파일로 기록할지 테이블로 기록할지 설정할 수 있다.

  • pt-query-digest 스크립트를 이용하면 쉽게 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다.

  • 로그 파일의 분석이 오나료되면 그 결과는 다음의 3개의 그룹으로 나뉘어 저장된다.

PreviousSETTINGNext트랜잭션과 잠금

Last updated