MySQL 아키텍처

초코칩·2024년 1월 24일
Real MySQLmysql

MySQL

목록 보기
1/8
post-thumbnail

MySQL 서버는 사람의 머리 역할을 담당하는 MySQL 엔진과 손발 역할을 담당하는 스토리지 엔진으로 구분할 수 있다. 스토리지 엔진은 핸들러 API를 만족하면 누구든지 스토리지 엔진을 구현해서 MySQL 서버에 추가할 수 있다.

MySQL 엔진 아키텍처

MySQL 서버는 다른 DBMS에 비해 구조가 독특하다. 독특한 구조 때문에 엄청난 혜택을 누릴 수 있으며, 다른 DBMS에 문제되지 않을 것들이 가끔 발생할 수 있다.

MySQL의 전체 구조

MySQL은 일반 사용 RDBMS와 같이 대부분의 프로그래밍 언어(C, C++, Java, PHP, Perl, Python, Ruby, ...)로부터 접근 방법을 모두 지원한다.

MySQL 엔진

MySQL 엔진은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다. 또한 MySQL은 표준 SQL(ANSI SQL) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 다른 DBMS와 호환되어 실행될 수 있다.

스토리지 엔진

MySQL 엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하고, 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다. MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다. 다음 예제와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.

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

위 예제에서 test_table에 대한 작업은 InnoDB 스토리지 엔진이 처리한다.

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

핸들러 API

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

MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 동작하며, 크게 포그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분할 수 있다. MySQL 서버에서 실행 중인 스레드의 목록은 performance_schema 데이터베이스의 threads 테이블을 통해 확인할 수 있다.

백그라운드 스레드의 개수는 MySQL 서버의 설정 내용에 따라 가변적일 수 있다. 동일한 이름의 스레드가 2개 이상씩 보이는 것은 MySQL 서버의 설정 내용에 의해 여러 스레드가 동일 작업을 병렬로 처리하는 경우다.

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

포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다. 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread cache)로 되돌아간다. 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다. 이때 스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정한다.

스레드 캐시와 스레드 풀과 다른건가? 주요 핵심 차이는 스레드 풀은 스레드를 미리 생성하고 관리하며 필요할 때 할당하고 반납하는 구조를 가지고 있으며, 스레드 캐시는 이미 생성된 스레드를 재사용하는 것에 중점을 둡니다. 스레드 풀은 고정된 수의 스레드를 유지하고 필요에 따라 관리하는 반면, 스레드 캐시는 동적으로 스레드를 생성하고 삭제하지 않고 이미 생성된 스레드를 재사용합니다.

출처: https://docs.percona.com/percona-monitoring-and-management/details/dashboards/dashboard-mysql-instance-summary.html#mysql-thread-cache

포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스의 파일로부터 데이터를 읽어와서 처리한다. MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만(MyISAM도 지연된 쓰기가 있지만, 일반적인 방식이 아님) InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.

왜 백그라운드로 처리하게 했을까?
백그라운드 스레드와 포그라운드 스레드가 다른가?

백그라운드 스레드

MyISAM은 해당되지 않지만, InnoDB는 다음과 같이 여러 가지 작업이 백그라운드로 처리된다.

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

모두 중요한 역할을 하지만, 그중에서 가장 중요한 것은 로그 스레드(Log thread)와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드(Write thread)일 것이다. MySQL 5.5 버전부터 데이터 쓰기 스레드와 읽기 스레드의 개수를 2개 이상 지정할 수 있게 되었으며, innodb_write_io_threads와 innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정한다. InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 쓰레드는 많이 설정할 필요가 없지만 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크를 사용할 때는 2~4 정도, DAS나 SAN과 같은 스토리지를 사용할 때는 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하는 것이 좋다.

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

사용자가 SELECT 쿼리를 실행했는데, 요청된 SELECT는 10분 뒤에 결과를 돌려주겠다.라고 응답을 보내지 않는다.

그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있으며, InnoDB 또한 이러한 방식으로 처리한다. 하지만 MyISAM은 그렇지 않고 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계돼 있다. 이러한 이유로 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다. 하지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.

메모리 할당 및 사용 구조

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다. 글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당된다. 운영체제의 종류에 따라 다르겠지만 요청된 메모리 공간을 100% 할당해줄 수도 있고, 그 공간만큼 예약해두고 필요할 때 조금씩 할당해 줄 수 있다.

단순하게 MySQL의 시스템 변수로 설정해 둔 만큼 운영체제로부터 메모리를 할당받는다고 생각하자.

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

글로벌 메모리 영역

일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간을 할당된다. 단, 필요에 따라 2개 이상의 메모리 공간을 할당 받을 수도 있지만 클라이언트 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유된다.

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

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

로컬 메모리 영역

MySQL 서버상에서 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다. 클라이언트가 MySQL 서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터 요청을 처리하기 위해 스레드를 하나씩 할당하는데, 클라이언트 스레드가 사용하는 메모리 공간이라 해서 클라이언트 메모리 영역이라고도 한다. 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 세션 메모리 영역이라고도 표현한다.

로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다. 일반적으로 글로벌 메모리 영역의 크기는 주의해서 설정하지만 sort 버퍼와 같은 로컬 메모리 영역은 크게 신경 쓰지 않고 설정하는데, 최악의 경우를 대비해 적절한 메모리 공간을 설정하는 것이 중요하다.

로컬 메모리 공간의 중요한 특징으로는 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수 있다는 점이다.

대표적으로 sort 버퍼와 join 버퍼가 그에 해당된다.

그리고 로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간(커넥션 버퍼, 결과 버퍼)도 있고 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(sort 버퍼, join 버퍼)도 있다.

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

MySQL의 독특한 구조 중 대표적인 것은 바로 플러그인 모델이다. 스토리지 엔진, 검색어 파서, 사용자 인증 등도 모두 플러그인으로 구현되어 제공한다. MySQL은 많은 스토리지 엔진을 탑재했음에도 불구하고, 수많은 사용자의 요구 조건을 만족하기 위해 부가적인 플러그인을 사용할 수 있으며 필요에 따라 직접 개발할 수 있다.

MySQL 쿼리 실행 과정

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

만약 사용자가 새로운 스토리지 엔진을 만들어도 DBMS의 전체 기능이 아닌 일부분의 기능만 수행하는 엔진을 작성하는 것이다.

각 처리 영역에서 '데이터 읽기/쓰기' 작업은 대부분 1건의 레코드 단위로 처리된다. MySQL 엔진이 각 스토리지 엔진에게 데이터를 read/write 요청은 반드시 핸들러를 통해야한다. 핸들러가 인터페이스와 같은 역할을 하기 때문에 MySQL에서 MyISAM이나 InnoDB와 같이 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도 MySQL의 처리 내용은 대부분 동일하며, 단순히 '데이터 읽기/쓰기' 영역의 처리만 차이가 있다. 실질적인 GROUP BY나 ORDER BY 등 복잡한 처리는 스토리지 엔진 영역이 아닌 MySQL 엔진의 처리 영역인 쿼리 실행기에서 처리된다.

여기서 중요한 내용은 하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지, 아니면 스토리지 엔진 영역에서 치리되는지 구분할 줄 알아야한다.는 점이다. 즉, 각 단위 작업을 누가 처리하고 MySQL 엔진 영역과 스토리지 엔진 영역의 차이를 설명하는데 목적이 있다.

아래 명령어를 통해 MySQL 서버에서 지원되는 스토리지 엔진을 확인할 수 있다.

SHOW ENGINES;

MySQL 서버(mysqld)에 포함되지 않은 스토리지 엔진을 사용하려면 MySQL 서버를 다시 빌드(컴파일)해야 한다. 하지만 MySQL 서버가 적절히 준비된다면 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워 넣어 사용할 수 있다.

스토리지 엔진뿐만 아니라 모든 플러그인 내용은 아래 명령어로 확인할 수 있다.

SHOW PLUGINS;

MySQL 서버에서는 스토리지 엔진뿐만 아니라 다양한 기능을 플러그인 형태로 제공해준다. 또한 필요에 따라 새로운 기능을 구현할 수도 있다.

컴포넌트

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

기존 플러그인의 단점

MySQL 서버의 플러그인은 몇 가지 단점이 있다.

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

복잡한 의존 관계에서 의존 관계를 풀어야 하는데, 이를 초기화하기 어렵다.

MySQL 5.7버전까지는 비밀번호 검증 기능이 플러그인 형태로 제공되었지만 MySQL 8.0버전의 비밀번호 검증 기능은 컴포넌트로 개선되었다.

쿼리 실행 구조

위 그림처럼 MySQL의 구조를 간략하게 표현할 수 있다.

쿼리 파서

쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 parse 트리 형태의 구조로 만들어 내는 작업을 의미한다. 퀴리 문장의 기본 문법 오류를 발견하고 오류 메시지를 전달한다.

전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 이름이나 칼럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 수행한다.

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

옵티마이저

옵티마이저는 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 담당한다.

실행 엔진

실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들어 요청의 입력으로 연결하는 역할을 한다. 실행 엔진이 하는 일을 더 쉽게 GROUP BY를 통해 이해해보자.

  1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
  2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
  3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
  4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
  5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김

핸들러 (스토리지 엔진)

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

복제

16장 Replication 참고.

쿼리 캐시

MySQL 서버에서 쿼리 캐시는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환한다.

단점

쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 모든 데이터를 삭제한다. 이는 심각한 동시처리 성능 저하를 유발했고, 많은 버그의 원인이 되었다.

결국 MySQL 8.0으로 올라오면서 쿼리 캐시는 MySQL 서버의 기능에서 완전히 제거되었다.

스레드 풀

스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것을 목적으로 한다. 하지만 스레드 풀이 실제 서비스에서 눈에 띄는 성능 향상을 보여주는 경우는 드물다. 또한 스레드 풀은 앞에서 소개한 것처럼 동시에 실행 중인 스레드들을 CPU가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능이 때문에 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우에는 퀴리 처리가 더 느려지는 사례도 발생할 수 있다. 물론 제한된 수의 스레드만으로 CPU가 처리하도록 적절히 유도한다면 CPU의 프로세서 친화도도 높이고 OS 입장에서 불필요한 컨텍스트 스위칭을 줄일 수 있다.

프로세서 친화도(processor affinity)는 컴퓨터 시스템에서 프로세스나 스레드가 특정한 프로세서 코어 또는 프로세서 집합에 바인딩되어 실행되는 정도를 나타낸다. 일반적으로, 여러 개의 프로세서 코어를 가진 다중 코어 프로세서 시스템에서 프로세스나 스레드를 실행할 때 어느 코어에 할당할지 결정하는 데 사용된다.

Percona Server

MySQL 서버 엔터프라이즈 에디션은 스레드 풀 기능을 제공하지만, MySQL 커뮤니티 에디션은 스레드 풀 기능을 지원하지 않는다. 만약 MySQL 커뮤니티 에디션에서 스레드 풀을 사용하고자 한다면, 플러그인 형태로 제공되는 Percona Server를 사용할 수 있다.

Percona Server의 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성하는데, 스레드 그룹의 개수는 thread_pool_size 시스템 변수를 변경해서 조정할 수 있다. 하지만 일반적으로는 CPU 코어의 개수와 thread_pool_size를 맞추는 것이 좋다. MySQL 서버가 처리해야 할 요청이 생기면 스레드 풀로 처리를 이관하는데, 만약 이미 스레드 풀이 처리 중인 작업이 있는 경우에는 thread_pool_oversubscribe 시스템 변수에 설정된 개수만큼 추가로 더 받아들여서 처리한다. 이 값이 너무 크면 스케줄링할 스레드가 많아져서 스레드 풀이 비효율적으로 작동할 수 있다.

스레드 풀 Full 처리

스레드 그룹의 모든 스레드가 일을 처리하고 있다면 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드(Worker thread)를 추가할지, 아니면 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부를 판단해야 한다. 스레드 풀의 타이머 스레드는 주기적으로 스레드 그룹의 상태를 체그하면서 thread_pool_stall_limit 시스템 변수에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 작업을 끝내지 못하면 새로운 스레드를 생성해서 스레드 그룹에 추가한다. 이때 전체 스레드 풀에 있는 스레드의 개수는 thread_pool_max_threads 시스템 변수에 설정된 개수를 넘어설 수 없다. 즉, 모든 스레드 그룹의 스레드가 각자 작업을 처리하고 있는 상태에서 새로운 쿼리 요청이 들어오더라도 스레드 풀은 thread_pool_stall_limit 시간 동안 기다려야만 새로 들어온 요청을 처리할 수 있다는 뜻이다. 따라서 응답 시간에 민감한 서비스라면 thread_pool_stall_limit 시스템 변수를 적절히 낮게 설정해야 한다. 그렇다고해서thread_pool_stall_limit을 0에 가까운 값으로 설정하는 것은 권장하지 않는다. thread_pool_stall_limit을 0에 가까운 값으로 설정해야 한다면, 차라리 사용하지 않는 편이 나을 것이다.

선수위 큐와 후순위 큐

Percona Server의 스레드 풀 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다. 이렇게 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있던 잠금(lock)이 빨리 해제되고 잠금(lock) 경합을 낮춰서 전체적인 처리 성능을 향상시킬 수 있다.

재배치 순서는 어떻게 조절할까?

트랜잭션 지원 메타데이터

기존 파일 기반의 메타데이터 관리의 문제점

DB 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라 하는데, MySQL 서버 5.7 버전까지 테이블의 구조를 FRM 파일에 저장하고 이룹 스토어브 프로그램 또한 파일(*.TRN, *.TRG, ...) 기반으로 관리했다. 하지만 이러한 파일 기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블의 생성 또는 변경 도중에 MySQL 서버가 비정상적으로 종료되며 일관되지 않은 상태로 남는 문제가 있었다.

트랜잭션 지원 메타데이터

MySQL 8.0 버전부터는 이러한 문제점을 해결하기 위해 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 InnoDB의 테이블에 저장하도록 개선했다. MySQL 서버가 작동하는데 기본적으로 필요한 테이블들을 묶어서 시스템 테이블이라고 하는데, 대표적으로 사용자의 인증과 권한에 관련된 테이블들이 있다. MySQL 서버 8.0 버전부터는 이런 시스템 테이블을 모두 InnoDB 스토리지 엔진을 사용하도록 개선했으며, 시스템 테이블과 데이터 딕셔너리 정보를 모두 모아서 mysql DB에 저장하고 있다. mysql DB는 통째로 mysql.ibd라는 이름의 테이블스페이스에 저장된다. 그래서 MySQL 서버 의 데이터 디렉터리에 존재하는 mysql. ibd라는 파일은 다른 *.ibd 파일과 함께 특별히 주의해야한다.

메타 정보가 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장되므로 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료되단고 하더라도 스키마 변경이 완전히 성공 또는 실패로 정리된다. 기존의 작업 중간 상태로 남지 않는 것이다.

MyISAM 같은 경우는 여전히 메타 정보를 저장할 공간이 필요하므로, SDI(Serialized Dictionary Information) 파일을 사용한다.

InnoDB 스토리지 엔진 아키텍처

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하며, 그 때문에 높은 동시성 처리가 가능하고 안정적이고 성능이 뛰어나다.

PK에 의한 클러스터링

InnoDB의 모든 테이블은 기본적으로 PK를 기준으로 클러스터링되어 저장된다. 즉, PK 값의 순서대로 저장된다는 뜻이며, 모든 세컨더리 인덱스는 레코드의 주소 대신 PK의 값을 논리적인 주소로 사용한다. PK가 클러스터링 인덱스이기 때문에 PK를 이용한 Range Scan은 상당히 빨리 처리될 수 있다. 결과적으로 쿼리의 실행 계획에서 PK는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정된다.

비중이 높다는 것은 퀴리의 실행 계획에서 다른 보조 인덱스보다 PK가 선택될 확률이 높다.

MyISAM에서의 클러스터링

InnoDB 스토리지 엔진과 달리 MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다. 따라서 MyISAM 테이블에서는 PK와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다. PK는 유니크 제약조건을 가진 세컨더리 인덱스일 뿐이다. 그리고 MyISAM 테이블의 PK를 포함한 모든 인덱스는 물리적인 레코드의 주소 값을 가진다.

외래 키 지원

외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다. 외래 키는 DB 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 자주 있는데, 그렇다 하더라도 개발 환경의 DB에서는 좋은 가이드 역할을 할 수 있다. InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래 키의 존재에 주의하는 것이 좋다.

외래 키 무결성 검사 해제

수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업이 실패할 수 있다. 물론 부모 테이블과 자식 테이블의 관계를 명확히 파악해서 순서대로 작업한다면 문제 없이 실행할 수 있지만 외래 키가 복잡하게 얽힌 경우 간단하지 않다. 이런 경우 foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다. 외래 키 체크 작업을 일시적으로 멈추면 대략 레코드의 적재나 삭제 등의 작업도 부가적인 체크가 필요 없기 때문에 훨씬 빠르게 처리할 수 있다.

외래 키 체크를 일시적으로 해제했다고 해서 부모, 자식 테이블 간의 관계가 깨진 상태로 유지해도 된다는 것을 의미하지 않는다. 반드시 일관성을 맞춰준 후에 외래 키 체크 기능을 활성화 해야 한다.

MVCC (Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜젝션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다. InnoDB는 언두 로그(Undo Log)를 이용해 이 기능을 구현한다. 여기서 멀티 버전(Multi Version)이라 하면 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미다.

InnoDB가 레코드 단위로 잠금을 지원하기 때문인가?

이해를 위해 격리 수준이 READ_COMMITTED인 MySQL 서버에서 InnoDB 스토리지 엔진을 사용하는 테이블의 변경을 어떻게 처리하는지 알아보자.

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;

INSERT 문이 실행되면 다음과 같은 상태로 변한다.

UPDATE member SET m_area='경기' WHERE m_id=12;

UPDATE 문이 실행되면 커밋 실행 여부와 관계없이 InnoDB의 버퍼 풀은 새로운 값인 '경기'로 업데이트 된다. 그리고 디스크의 데이터 파일에는 체크포인트나 InnoDB의 Write 스레드에 의해 새로운 값으로 업데이트 돼 있을 수도 있고 아닐 수도 있다.

InnoDB가 ACID를 보장하기 때문에 일반적으로 InnoDB의 버퍼 풀과 데이터 파일은 동일한 상태라고 가정해도 무방하다.

SELECT * FROM member WHERE m_id=12;

READ_COMMITTED나 그 이상의 격리 수준(REPEATABLE_READ, SERIALIZABLE)인 경우에는 아직 커밋되지 않았기 때문에 InnoDB 버퍼 풀이나 데이터 파일에 있는 내용 대신 변경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다. 이러한 과정을 DBMS의 MVCC라고 표현한다.

여기서는 한 개의 데이터만을 가지고 설명했지만, 관리해야 하는 예전 버전의 데이터는 무한히 많아질 수도 있다. 트랜잭션이 길어지면 언두에서 관리하는 예전 데이터가 삭제되지 못하고 오랫동안 관리돼야 하며, 자연스럽게 언두 영역이 저장되는 시스템 테이블스페이스의 공간이 많이 늘어나는 상황이 발생하 수도 있다.

롤백

UPDATE 쿼리가 실행되면 InnoDB 버퍼 풀은 즉시 새로운 데이터로 변경되고 기존 데이터는 언두 영역으로 복사된다. 이 상태에서 COMMIT 명령을 실행하면 InnoDB는 더 이상의 변경 작업 없이 지금의 상태를 영구적인 데이터로 만들어버린다. 하지만 롤백을 실행하면 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제해 버린다. 커밋이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는 것은 아니다. 이 언두 영역을 필 요로 하는 트랜잭션이 더는 없을 때 비로소 삭제된다.

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

InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다. 잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업 이 가능하다. 격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMITTED나 READ_COMMITTED, REPEATABLE_READ 수준인 경우 INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다.

위 그림에서 특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았다 하더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다. 이를 '잠금 없는 일관된 읽기'라고 표현하며, InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용한다.

오랜 시간 동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 때가 가끔 있는데, 바로 이러한 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 발생하는 문제다. 따라서 트랜잭션이 시작됐다면 가능한 한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.

자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다. InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료한다. 이때 어느 트랜잭션을 먼저 강제 종료할 것인지를 판단하는 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다. 트랜잭션이 언두 레코드를 적게 가졌다는 이야기는 롤백을 해도 언두 처리를 해야 할 내용이 적다는 것이며, 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문이다.

테이블 레벨의 데드락 감지

참고로 InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금(LOCK TABLES 명령으로 잠긴 테이블)은 볼 수가 없어서 데드락 감지가 불확실할 수도 있는데,innodb_table_locks 시스템 변수를 활성화하면 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감지할 수 있게 된다. 특별한 이유가 없다면 innodb_table_locks 시스템 변수를 활성화하자.

데드락 감지 스레드의 오버헤드

일반적인 서비스에서는 데드락 감지 스레드가 트랜잭션의 잠금 목록을 검사해서 데드락을 찾아내는 작업은 크게 부담되지 않는다. 하지만 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려진다. 데드락 감지 스레드는 잠금 목록을 검사해야 하기 때문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트(잠금 테이블)에 새로운 잠금을 걸고 데드락 스레드를 찾게 된다. 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업을 진행하지 못하고 대기하면서 서비스에 악영향을 미치게 된다. 이렇게 동시 처리 스레드가 매우 많은 경우 데드락 감지 스레드는 더 많은 CPU 자원을 소모할 수도 있다.

이런 문제점을 해결하기 위해 MySQL 서버는 innodb_deadlock_detect 시스템 변수를 제공하며, innodb_deadlock_detect를 OFF로 설정하면 데드락 감지 스레드는 더는 작동하지 않게 된다. 데드락 감지 스레드가 작동하지 않으면 InnoDB 스토리지 엔진 내부에서 2개 이상의 트랜잭션이 상대방이 가진 잠금을 요구하는 상황(데드락 상황)이 발생해도 누군가가 중재를 하지 않기 때문에 무한정 대기하게 될 것이다. 하지만 innodb_lock_wait_timeout 시스템 변수를 활성화하면 이런 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환하게 된다. innodb_lock_wait_timeout은 초 단위로 설정할 수 있으며, 잠금을 설정한 시간 동안 획득하지 못하면 쿼리는 실패하고 에러를 반환한다. 데드락 감지 스레드가 부담되어 innodb_deadlock_detect를 OFF로 설정해서 비활성화하는 경우라면 innodb_lock_wait_timeout을 기본값인 50초보다 훨씬 낮은 시간으로 변경해서 사용할 것을 권장한다.

구글에서는 PK 기반의 조회 및 변경이 아주 높은 빈도로 실행되는 서비스가 많았는데, 이런 서비스는 매우 많은 트랜잭션을 동시에 실행하기 때문에 데드락 감지 스레드가 상당히 성능을 저하시킨다는 것을 알아냈다. 그리고 MySQL 서버의 소스코드를 변경해 데드락 감지 스레드를 활성화 또는 비활성화할 수 있게 변경해서 사용했다. 이 기능의 필요성을 인지하고 오라클에 이 가능을 요청해서 MySQL 서버에 추가된 것이다. 만약 PK 또는 세컨더리 인덱스를 기반으로 매우 높은 동시성 처리를 요구하는 서비스가 있다면 innodb_deadlock_detect를 비활성화해서 성능 비교를 해보는 것도 새로운 기회가 될 것이다.

자동화된 장애 복구

InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러 가지 메커니즘이 탑재돼 있다. 그러한 메커니즘을 이용해 MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 (Partial write) 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다.

InnoDB 스토리지 엔진은 매우 견고해서 데이터 파일이 손상되거나 MySQL 서버가 시작되지 못하는 경우는 거의 발생하지 않는다. 하지만 MySQL 서버와 무관하게 디스크나 서버 하드웨어 이슈로 InnoDB 스토리지 엔진이 자동으로 복구를 못하는 경우도 발생할 수 있는데, 일단 한 번 문제가 생기면 복구하기가 쉽지 않다. InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다. 이 단계에서 자동으로 복구될 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 서버는 종료돼 버린다.

복구 방법

이때는 MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 한다. 이 설정값은 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 한다.

  • InnoDB의 로그 파일이 손상됐다면 6으로 설정하고 MySQL 서버를 기동한다.
  • InnoDB 테이블의 데이터 파일이 손상됐다면 1로 설정하고 MySQL 서버를 기동한다.
  • 어떤 부분이 문제인지 알 수 없다면 innodb_force_recovery 설정값을 1부터 6까지 변경하면서 MySQL을 재시작 해본다. 즉 innodb_force_recovery 설정값을 1로 설정한 후 MySQL 서버를 재시작해 보고, MySQL이 시작되지 않으면 다시 2로 설정하고 재시작해 보는 방식이다. innodb_force_recovery 값이 커질수록 그만큼 심각한 상황이기에 데이터 손실 가능성이 커지고 복구 가능성은 적어진다.

일단 MySQL 서버가 기동되고 InnoDB 테이블이 인식된다면 mysqldump를 이용해 데이터를 가능한만큼 백업하고 그 데이터로 MySQL 서버의 DB와 테이블을 다시 생성하는 것이 좋다. InnoDB의 복구를 위해 innodb_force_recovery 옵션에 설정 가능한 값은 1부터 6까지인데, 각 숫자 값으로 복구되는 장애 상황과 해결 방법을 살펴보자. innodb_force_recovery가 0이 아닌 복구 모드에서는 SELECT 이외의 INSERT나 UPDATE, DELETE 같은 쿼리는 수행할 수 없다.

복구 모드

1(SRV_FORCE_IGNORE_CORRUPT)

InnoDB의 테이블스페이스의 데이터나 인덱스 페이지에서 손상된 부분이 발견돼도 무시하고 MySQL 서버를 시작 한다. 에러 로그 파일에 'Database page corruption on disk or a failed' 메시지가 출력될 때는 대부분 이 경우에 해당한다. 이때는 mysqldump 프로그램이나 SELECT INTO OUTFILE ... 명령을 이용해 덤프해서 데이터베이스를 다시 구축하는 것이 좋다.

2(SRV_FORCE_NO_BACKGROUND)

InnoDB는 쿼리의 처리를 위해 여러 종류의 백그라운드 스레드를 동시에 사용한다. 이 복구 모드에서는 이러한 백그라운드 스레드 가운데 메인 스레드를 시작하지 않고 MySQL 서버를 시작한다. InnoDB는 트랜잭션의 롤백을 위해 언두 데이터를 관리하는데, 트랜잭션이 커밋되어 불필요한 언두 데이터는 InnoDB의 메인 스레드에 의해 주기적으로 삭제(이를 Undo purge라고 함)된다. InnoDB의 메인 스레드가 언두 데이터를 삭제하는 과정에서 장애가 발생한다면 이 모드로 복구하면 된다.

3(SRV_FORCE_NO_TRX_UNDO)

InnoDB에서 트랜잭션이 실행되면 롤백에 대비해 변경 전의 데이터를 언두 영역에 기록한다. 일반적으로 MySQL 서버는 다시 시작하면서 언두 영역의 데이터를 먼저 데이터 파일에 적용하고 그 다음 리두 로그의 내용을 다시 덮어 써서 장애 시점의 데이터 상태를 만들어낸다. 그리고 정상적인 MySQL 서버의 시작에서는 최종적으로 커밋되지 않은 트랜잭션은 롤백을 수행하지만 innodb_force_recovery가 3으로 설정되면 커밋되지 않은 트랜잭션의 작업을 롤백하지 않고 그대로 놔둔다. 즉, 커밋되지 않고 종료된 트랜잭션은 계속 그 상태로 남아 있게 MySQL 서버를 시작하는 모드다. 이때도 우선 MySQL 서버가 시작되면 mysqldump를 이용해 데이터를 백업해서 다시 데이터베이스를 구축하는 것이 좋다.

4(SRV_FORCE_NO_IBUF_MERGE)

InnoDB는 INSERT, UPDATE, DELETE 등의 데이터 변경으로 인한 인덱스 변경 작업을 상황에 따라 즉시 처리할 수도 있고 인서트 버퍼에 저장해두고 나중에 처리할 수도 있다. 이렇게 인서트 버퍼에 기록된 내용은 언제 데이터 파일에 병합(Merge)될지 알 수 없다. MySQL을 종료해도 병합되지 않을 수 있는데, 만약 MySQL이 재시작되면서 인서트 버퍼의 손상을 감지하면 InnoDB는 에러를 발생시키고 MySQL 서버는 시작하지 못한다. 이때 innodb_force_recovery를 4로 설정하면 InnoDB 스토리지 엔진이 인서트 버퍼의 내용을 무시하고 강제로 MySQL이 시작되게 한다. 인서트 버퍼는 실제 데이터와 관련된 부분이 아니라 인덱스에 관련된 부분이므로 테이블을 덤프한 후 다시 데이터베이스를 구축하면 데이터의 손실 없이 복구할 수 있다.

5(SRV_FORCE_NO_UNDO_LOG_SCAN)

MySQL 서버가 장애나 정상적으로 종료되는 시점에 진행 중인 트랜잭션이 있었다면 MySQL은 그냥 단순히 그커 넥션을 강제로 끊어 버리고 별도의 정리 작업 없이 종료한다. MySQL이 다시 시작하면 InnoDB 엔진은 언두 레코드를 이용해 데이터 페이지를 복구하고 리두 로그를 적용해 종료 시점이나 장애 발생 시점의 상태를 재현해 낸다. 그리고 InnoDB는 마지막으로 커밋되지 않은 트랜잭션에서 변경한 작업은 모두 롤백 처리한다. 그런데 InnoDB의 언 두 로그를 사용할 수 없다면 InnoDB 엔진의 에러로 MySQL 서버를 시작할 수 없다. 이때 innodb_force_recovery 옵션을 5로 설정하면 InnoDB 엔진이 언두 로그를 모두 무시하고 MySQL을 시작할 수 있다. 하지만 이 모드로 복구되면 MySQL 서버가 종료되던 시점에 커밋되지 않았던 작업도 모두 커밋된 것처럼 처리되므로 실제로는 잘못된 데이터가 데이터베이스에 남는 것이라고 볼 수 있다. 이때도 mysqldump를 이용해 데이터를 백업하고, 데이터베이스를 새로 구축해야 한다.

6(SRV_FORCE_NO_LOG_REDO)

InnoDB 스토리지 엔진의 리두 로그가 손상되면 MySQL 서버가 시작되지 못한다. 이 복구 모드로 시작하면 InnoDB 엔진은 리두 로그를 모두 무시한 채로 MySQL 서버가 시작된다. 또한 커밋됐다 하더라도 리두 로그에만 기록되고 데이터 파일에 기록되지 않은 데이터는 모두 무시된다. 즉, 마지막 체크포인트 시점의 데이터만 남게 된다. 이 때는 기존 InnoDB의 리두 로그는 모두 삭제(또는 별도의 디렉터리에 백업)하고 MySQL 서버를 시작하는 것이 좋다. MySQL 서버가 시작하면서 리두 로그가 없으면 새로 생성하므로 별도로 파일을 만들 필요는 없다. 이때도 mysqldump를 이용해 데이터를 모두 백업해서 MySQL 서버를 새로 구축하는 것이 좋다.

백업 재구축

위와 같이 진행했음에도 MySQL 서버가 시작되지 않으면 백업을 이용해 다시 구축하는 방법밖에 없다. 백업이 있다면 마지막 백업으로 데이터베이스를 새로 구축하고, 바이너리 로그를 사용해 최대한 장애 시점까지의 데이터를 복구할 수도 있다. 마지막 풀 백업 시점부터 장애 시점까지의 바이너리 로그가 있다면 InnoDB의 복구를 이용하는 것보다 풀 백업과 바이너리 로그로 복구하는 편이 데이터 손실이 더 적을 수 있다. 백업은 있지만 복제의 바이너리 로그가 없거나 손실됐다면 마지막 백업 시점까지만 복구할 수 있다.

InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간이다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다. 일반적이 애플리케이션에서는 INSERT, UPDATE, DELETE처럼 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시킨다. 하지만 버퍼 풀이 변경된 데이터를 모아서 처리하면 랜던함 디스크 작업의 횟수를 줄일 수 있다.

버퍼 풀의 권장 메모리 크기

MySQL 서버 내에서 메모리를 필요로 하는 부분은 크게 없지만 아주 독특한 경우 레코드 버퍼가 상당한 메모리를 사용한다. 레코드 버퍼는 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간을 말하는데, 커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 꽤 많이 필요해질 수도 있다. MySQL 서버가 사용하는 레코드 버퍼 공간은 별도로 설정할 수 없으며, 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블의 개수에 따라서 결정된다. 또한 동적으로 해제되기도 하므로 정확히 필요한 메모리 공간의 크리를 계산할 수 없다.

MySQL 5.7 버전부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 개선됐다. 그래서 가능하면 InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 봐 가면서 증가시키는 방법이 최적이다. 일반적으로 회사에서 이미 MySQL 서버를 사용하고 있다면 그 서버의 메모리 설정을 기준으로 InnoDB 버퍼 풀의 크기를 조정하면 된다. 하지만 처음으로 MySQL 서버를 준비한다면 다음과 같은 방법으로 InnoDB 버퍼 풀 설정을 찾아가는 방법을 권장한다.

  • 운영체제의 전체 메모리 공간이 8GB 미만: 50% 정도만 InnoDB 버퍼 풀로 설정하고 나머지 메모리 공간은 MySQL 서버와 운영체제, 그리고 다른 프로그램이 사용할 수 있는 공간으로 확보한다.
  • 전체 메모리 공간이 8GB 이상: InnoDB 버퍼 풀의 크기를 전체 메모리의 50%에서 시작해서 조금씩 올려가면 서 최적점을 찾는다.
  • 운영체제의 전체 메모리 공간이 50GB: 대략 15GB에서 30GB 정도를 운영체제와 다른 응용 프로그램을 위해서 남겨두고 나머지를 InnoDB 버퍼 풀로 할당한다.

버퍼 풀 크기 변경

InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있으며, 동적으로 버퍼 풀의 크기를 확장할 수 있다. 하지만 버퍼 풀의 크기 변경은 크리티컬한 변경이므로 가능하면 MySQL 서버가 한가한 시점을 골라서 진행하는 것이 좋다. 또한 InnoDB 버퍼 풀을 더 크게 변경하는 작업은 시스템 영향도가 크지 않지만, 버퍼 풀의 크기를 줄이는 작업은 서비스 영향도가 매우 크므로 가능하면 버퍼 풀의 크기를 줄이는 작업은 하지 않도록 주의하자. InnoDB 버퍼 풀은 내부적으로 128MB 청크 단위로 쪼개어 관리되는데, 이는 버퍼 풀의 크기를 줄이거나 늘리기 위한 단위 크기로 사용된다. 그래서 버퍼 풀의 크기를 줄이거나 늘릴 때는 128MB 단위로 처리된다. 버퍼 풀의 크기를 동적으로 변경해야 한다면 반드시 먼저 MySQL 매뉴얼의 내용을 숙지하고 진행하기를 권장한다.

분산 버퍼 풀

InnoDB 버퍼 풀은 전통적으로 버퍼 풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합을 많이 유발해왔는데, 이런 경합을 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선됐다. 버퍼 풀이 여러 개의 작은 버퍼 풀로 쪼개지면서 개별 버퍼 풀 전체를 관리하는 잠금(세마포어) 자체도 경합이 분산되는 효과를 내게 되는 것이다. innodb_buffer_pool_instances 시스템 변수를 이용해 버퍼 풀을 여러 개로 분리해서 관리할 수 있는데, 각 버퍼 풀을 버퍼 풀 인스턴스라고 표현한다. 기본적으로 버퍼 풀 인스턴스의 개수는 8개로 초기화되지만 전체 버퍼 풀을 위한 메모리 크기가 1GB 미만이면 버퍼 풀 인스턴스는 1개만 생성된다. 버퍼 풀로 할당할 수 있는 메모리 공간이 40GB 이하 수준이라면 기본 값인 8을 유지하고, 메모리가 크다면 버퍼 풀 인스턴스당 5GB 정도가 되게 인스턴스 개수를 설정하는 것이 좋다.

버퍼 풀의 구조

InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기(16kb)(innodb_page_size 시스템 변수에 설정된)의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다. 버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 크게 LRU(Least Recently Used) 리스트와 플러시(Flush) 리스트, 그리고 프리(Free) 리스트라는 3개의 자료 구조를 관리한다. 프리 리스트는 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록이며, 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다. 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 헤더 부분으로 이동한다.
  4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했는지에 따라 나이(age)가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지에 부여된 나이가 오래되고 결국 해당 버퍼 풀에서 제거된다. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨진다.
  5. 필요한 데이터가 자주 접근되었다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가한다.

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

플러시 리스트

플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(이를 더티 페이지라고 함)의 변경 시점 기준의 페이지 목록을 관리한다. 디스크에서 읽은 상태 그대로 전혀 변경이 없다면 플러시 리스트에 관리되지 않지만, 일단 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록돼야 한다. 데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다. 그래서 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결된다. 하지만 리두 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는 것을 항상 보장하지는 않는다. 때로는 그 반대의 경우도 발생할 수 있는데, InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화하게 된다. 체크포인트는 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 리두 로그의 어느 부분부터 복구를 실행해야 할지 판단하는 기준점을 만드는 역할을 한다.

버퍼 풀과 리두 로그

리두 로그란 MySQL 장애 시 버퍼 풀에 저장되어 있던 데이터의 유실을 방지(데이터 복구)하기 위해 사용된다. InnoDB의 버퍼 풀과 리두 로그는 매우 밀접한 관계를 맺고 있다. InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리의 성능이 빨라진다. 물론 이미 디스크의 모든 데이터 파일이 버퍼 풀에 적재될 정도의 버퍼 풀 공간이라면 더는 버퍼 풀 크기를 늘려도 성능에 도움이 되지 않겠지만, 그렇지 않다면 디스크의 데이터가 버퍼 풀 메모리로 적재되면 성능이 좋아질 것이다. 하지만 InnoDB 버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데, 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐시 기능만 향상시키는 것이다. InnoDB 버퍼 풀의 쓰기 버퍼링 기능까지 향상시키려면 InnoDB 버퍼 풀과 리두 로그와의 관계를 이해해야 한다.

InnoDB의 버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지(Clean Page)와 함께 INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 더터 페이지(Dirty Page)를 함께 가지고 있다. 더티 페이지는 디스크와 메모리(버퍼 풀)의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록돼야 한다. 하지만 더티 페이지는 버퍼 풀에 무한정 머무를 수 있는 것은 아니다. InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용한다. 즉, 데이터 변경이 계속 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 덮어 쓰인다. 그래서 InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리해야 하는데, 재사용 불가능한 공간을 활성 리두 로그(Active Redo Log) 라고 한다. 위 그림에서 화살표를 가진 엔트리들이 활성 리두 로그 공간인 것이다. 리두 로그 파일의 공간은 계속 순환되어 재사용되지만 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 갖게 되는데, 이를 LSN(Log Sequence Number)이라고 한다. InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화하는데, 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 된다. 하지만 활성 리두 로그 공간의 마지막은 계속해서 증가하기 때문에 체크포인트와 무관하다. 그리고 가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지 (Checkpoint Age)라고 한다. 즉 체크포인트 에이지는 활성 리두 로그 공간의 크기를 일컫는다. InnoDB 버퍼 풀의 더티 페이지는 특정 리두 로그 엔트리와 관계를 가지고, 체크포인트가 발생하면 체 크포인트 LSN보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화돼야 한다. 물론 당연히 체크포인트 LSN보다 작은 LSN 값을 가진 리두 로그 엔트리도 디스크로 동기화돼야 한다.

예제

이제 버퍼 풀의 더티 페이지 비율과 리두 로그 파일의 전체 크기가 어떤 관계인지 이해했으니 실제 간단한 예제를 한번 생각해보자.

  1. InnoDB 버퍼 풀은 100GB이며 리두 로그 파일의 전체 크기는 100MB인 경우
  2. InnoDB 버퍼 풀은 100MB이며 리두 로그 파일의 전체 크기는 100GB인 경우

1번의 경우 리두 로그 파일의 크기가 100MB밖에 안되기 때문에 체크포인트 에이지(Checkpoint Age)도 최대 100MB만 허용된다. 예를 들어, 평균 리두 로그 엔트리가 4KB였다면 25600개 (100MB/4KB) 정도의 더티 페이지만 버퍼 풀에 보관할 수 있게 된다. 데이터 페이지가 16KB라고 가 정한다면 허용 가능한 전체 더티 페이지의 크기는 400MB 수준밖에 안 되는 것이다. 결국 이 경우는 버퍼 풀의 크기는 매우 크지만 실제 쓰기 버퍼링을 위한 효과는 거의 못 보는 상황인 것이다. 2번의 경우 도 1번과 동일한 방식으로 계산해볼 수 있는데, 대략 400GB 정도의 더티 페이지를 가질 수 있다. 하 지만 버퍼 풀의 크기가 100MB이기 때문에 최대 허용 가능한 더티 페이지는 100MB 크기가 된다(물론 InnoDB 버퍼 풀의 여러 가지 설정으로 인해 100MB까지는 아니지만 여기서는 설명의 편의를 위해서다).

그렇다면 1번과 2번 중 어떤 경우가 좋은 것일까? 사실 둘 다 좋은 설정은 아니라고 할 수 있다. 1번 의 경우는 잘못된 설정이라는 것을 쉽게 알 수 있다. 그리고 2번의 경우는 이론적으로는 아무 문제가 없어 보여도 실제 이 상태로 서비스를 운영하다 보면 급작스러운 디스크 쓰기가 발생할 가능성이 높다. 버퍼 풀에 더티 페이지의 비율이 너무 높은 상태에서 갑자기 버퍼 풀이 필요해지는 상황이 오면 InnoDB 스토리지 엔진은 매우 많은 더티 페이지를 한 번에 기록해야 하는 상황이 온다. 처음부터 리두 로그 파일의 크기를 적절히 선택하기 어렵다면 버퍼 풀의 크기가 100GB 이하의 MySQL 서버에서는 리두 로그 파일의 전체 크기를 대략 5~10GB 수준으로 선택하고 필요할 때마다 조금씩 늘려가면서 최적값을 선택하는 것이 좋다.

당연한 이야기지만 버퍼 풀의 크기가 100GB라고 해서 리두 로그의 공간이 100GB가 돼야 한다는 것은 아니다. 일반적으로 리두 로그는 변경분만 가지고 버퍼 풀은 데이터 페이지를 통째로 가지기 때문에 데이터 변경이 발생해도 리두 로그는 훨씬 작은 공간만 있으면 된다.

버퍼 풀 플러시(Buffer Pool Flush)

MySQL 5.6 버전까지는 InnoDB 스토리지 더티 페이지 플러시 기능이 그다지 부드럽게 처리되지 않 았다. 예를 들어, 급작스럽게 디스크 기록이 폭증해서 MySQL 서버의 사용자 쿼리 처리 성능에 영향을 받는 경우가 많았다. 하지만 MySQL 5.7 버전을 거쳐서 MySQL 8.0 버전으로 업그레이드되면서 대부분의 서비스에서는 더티 페이지를 디스크에 동기화하는 부분(더티 페이지 플러시)에서 예전과 같은 디스크 쓰기 폭증 현상은 발생하지 않았다. 여기서 InnoDB 스토리지 엔진의 더티 페이지의 디스크 쓰기 동기화와 관련된 시스템 설정을 살펴보겠지만 특별히 서비스를 운영할 때 성능 문제가 발생하지 않는 상태라면 굳이 이 시스템 변수들을 조정할 필요는 없다.

InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 다음과 같이 2개의 플러시 기능을 백그라운드로 실행한다.

플러시 리스트 플러시

InnoDB 스토리지 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 한다. 그런데 이때 오래된 리두 로그 공간이 지워지려면 반드시 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화돼야 한다. 이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트(Flush list) 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행한다. 이때 언제부터 얼마나 많은 더티 페이지를 한 번에 디스크로 기록하느냐에 따라 사용자의 퀴리 처리가 악영향을 받지 않으면서 부드럽게 처리된다. 이를 위해 InnoDB 스토리지 엔진은 다음과 같은 시스템 변수들을 제공한다.

  • innodb_page_cleaners
  • innodb_max_dirty_pages_pct_lwm
  • innodb_max_dirty_pages_pct
  • innodb_io_capacity
  • innodb_io_capacity_max
  • innodb_flush_neighbors
  • innodb_adaptive_flushing
  • innodb_adaptive_flushing_lwm

InnoDB 스토리지 엔진에서 더티 페이지를 디스크로 동기화하는 스레드를 클리너 스레드(Cleaner Thread)라고 하는데, innodb_page_cleaners 시스템 변수는 클리너 스레드의 개수를 조정할 수 있게 해준다. InnoDB 스토리지 엔진은 여러 개의 InnoDB 버퍼 풀 인스턴스를 동시에 사용할 수 있는데, innodb_page_cleaners 설정값이 버퍼 풀 인스턴스 개수보다 많은 경우에는 innodb_buffer_pool_instances 설정값으로 자동으로 변경한다. 즉, 하나의 클리너 스레드가 하나의 버퍼 풀 인스턴스를 처리하도록 자동으로 맞춰준다. 하지만 innodb_page_cleaners 시스템 변수의 설정값이 버퍼 풀 인스턴스 개수보다 적은 경우에는 하나의 클리너 스레드가 여러 개의 버퍼 풀 인스턴스를 처리한다. 따라서 가능하면 innodb_page_cleaners 설정값은 innodb_buffer_pool_instances 설정값과 동일한 값으로 설정하자.

InnoDB 버퍼 풀은 클린 페이지뿐만 아니라 사용자의 DML(INSERT, UPDATE, DELETE)에 의해 변경된 더티 페이지도 함께 가지고 있다. 여기서 InnoDB 버퍼 풀은 한계가 있기 때문에 무한정 더티 페이지를 그대로 유지할 수 없다. 기본적으로 InnoDB 스토리지 엔진은 전체 버퍼 풀이 가진 페이지의 90%까지 더티 페이지를 가질 수 있는데, 때로는 이 값이 너무 높을 수도 있다. 이런 경우에는 innodb_max_dirty_pages_pct라는 시스템 설정 변수를 이용해 더티 페이지의 비율을 조정할 수 있다. 일반적으로 InnoDB 버퍼 풀은 더티 페이지를 많이 가지고 있을수록 디스크 쓰기 작업을 버퍼링함으로써 여러 번의 디스크 쓰기를 한 번으로 줄이는 효과를 극대화할 수 있다. 그래서 innodb_max_dirty_pages_pct 시스템 설정은 가능하면 기본값을 유지하는 것이 좋다.

디스크 쓰기 폭발 예방

여기서 한 가지 더 문제점이 발생하는데, InnoDB 버퍼 풀에 더티 페이지가 많으면 많을수록 디스크 쓰기 폭발(Disk IO Burst) 현상이 발생할 가능성이 높아진다. InnoDB 스토리지 엔진은 innodb_io_capacity 시스템 변수에 설정된 값을 기준으로 더티 페이지 쓰기를 실행한다. 하지만 디스크로 기록되는 더티 페이지 개수보다 더 많은 더티 페이지가 발생하면 버퍼 풀에 더티 페이지가 계속 증가하게 되 고, 어느 순간 더티 페이지의 비율이 90%를 넘어가면 InnoDB 스토리지 엔진은 급작스럽게 더티 페이지를 디스크로 기록해야 한다고 판단한다. 그래서 급작스럽게 디스크 쓰기가 폭증하는 현상이 발생한다. 이런 문제를 완화하기 위해 InnoDB 스토리지 엔진에서는 innodb_max_dirty_pages_pct_lam이라는 시스템 설정 변수를 이용해 일정 수준 이상의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록하게 하고 있다. innodb_max_dirty_pages_pct_lam 시스템 변수의 기본값은 10% 수준인데, 만약 더티 페이지의 비율이 얼마 되지 않는 상태에서 디스크 쓰기가 많이 발생하고 더티 페이지의 비율이 너무 낮은 상태로 계속 머물러 있다면 innodb_max_dirty_pages_pct_lam 시스템 변수를 조금 더 높은 값으로 조정하는 것도 디스크 쓰기 횟수를 줄이는 효과를 얻을 수 있다.

innodb_io_capacity와 innodb_io_capacity_max 시스템 변수는 각 데이터베이스 서버에서 어느 정도의 디스크 읽고 쓰기가 가능한지를 설정하는 값이다. innodb_io_capacity는 일반적인 상황에서 디스크가 적절히 처리할 수 있는 수준의 값을 설정하며, innodb_io_capacity_max 시스템 변수는 디스크가 최대의 성능을 발휘할 때 어느 정도의 디스크 읽고 쓰기가 가능한지를 설정한다. 여기서 언급하는 디스크 읽고 쓰기란 InnoDB 스토리지 엔진의 백그라운드 스레드가 수행하는 디스크 작업을 의미하는데, 대부분 InnoDB 버퍼 풀의 더티 페이지 쓰기가 이에 해당한다. 하지만 InnoDB 스토리지 엔진은 사용자의 쿼리를 처리하기 위해 디스크 읽기도 해야 한다. 그래서 현재 장착된 디스크가 초당 1000 IOPS를 처리 할 수 있다고 해서 이 값을 그대로 innodb_io_capacity와 innodb_io_capacity_max 시스템 변수에 설정해서는 안 된다.

innodb_io_capacity와 innodb_io_capacity_max 시스템 변수에 1000을 설정한다고 해서 초당 1000번의 디스크 쓰기를 보장하는 것은 아니다. InnoDB 스토리지 엔진은 내부적인 최적화 알고리즘을 가지고 있어서 설정된 값 들을 기준으로 적당히 계산된 횟수만큼 더티 페이지 쓰기를 하기 때문이다. 그래서 innodb_io_capacity와 innodb_io_capacity_max 시스템 변수를 설정하고, 어느 정도 디스크 쓰기를 하는지 관찰하면서 분석하고 패턴을 익하는 것이 중요하다.

어댑티브 플러시

관리해야 할 MySQL 서버가 많다면 일일이 서버의 트래픽을 봐 가면서 innodb_io_capacity와 innodb_io_capacity_max를 설정하는 것은 상당히 번거로운 일이 될 것이다. 그래서 InnoDB 스토리지 엔진은 어댑티브 플러시(Adaptive flush)라는 기능을 제공한다. 어댑티브 플러시는 innodb_ adaptive_flushing 시스템 변수로 켜고 끌 수 있는데, 기본값은 어댑티브 플러시를 사용하는 것이다. 어댑티브 플러시 기능이 활성화되면 InnoDB 스토리지 엔진은 단순히 버퍼 풀의 더티 페이지 비율이나 innodb_io_capacity, innodb_io_capacity_max 설정값에 의존하지 않고 새로운 알고리즘을 사용한다. 더티 페이지를 어느 정도 디스크로 기록해야 할지는 사실 어느 정도 속도로 더티 페이지가 생성되는지를 분석하는 것인데, 이는 결국 리두 로그가 어느 정도 속도로 증가하는 지를 분석하는 것과 같다. 그래서 어댑티브 플러시 알고리즘은 리두 로그의 증가 속도를 분석해서 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행한다. innodb_adaptive_flushing_lwm 시스템 변수의 기본값은 10%인데, 이는 전체 리두 로그 공간에서 활성 리두 로그의 공간이 10% 미만이면 어댑티브 플러시가 작동하지 않다가 10%를 넘어서면 그때부터 어댑티브 플러시 알고리즘이 작동하게 한다.

Neighbors(Locality) flush

마지막으로 innodb_flush_neighbors 시스템 변수는 더티 페이지를 디스크에 기록할 때 디스크에서 근접 한 페이지 중에서 더티 페이지가 있다면 InnoDB 스토리지 엔진이 함께 묶어서 디스크로 기록하게 해주는 기능을 활성화할지 결정한다. 예전에 많이 사용하던 하드디스크(HDD)의 경우 디스크 읽고 쓰기는 매우 고비용의 작업이었다. 그래서 많은 데이터베이스 서버들은 한 번이라도 디스크 읽고 쓰기를 줄 이기 위해 많은 노력을 기울였는데, 이웃 페이지들의 동시 쓰기(innodb_ _flush_neighbors)는 그러한 노력의 결과라고 볼 수 있다. 데이터 저장을 하드디스크로 하고 있다면 innodb_flush_neighbors 시스템 변수 를 1 또는 2로 설정해서 활성화하는 것이 좋다. 하지만 요즘은 대부분 솔리드 스테이트 드라이브(SSD)를 사용하기 때문에 기본값인 비활성 모드로 유지하는 것이 좋다.

왜 비활성 모드가 좋을까?

LRU 리스트 플러시

InnoDB 스토리지 엔진은 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 하는데, 이를 위해 LRU 리스트(LRU_list) 플러시 함수가 사용된다. InnoDB 스토리지 엔진은 LRU 리스트의 끝부분부터 시작해서 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼의 페이지들을 스캔한다. InnoDB 스토리지 엔진은 이때 스캔하면서 더티 페이지는 디스크에 동기화하게 하며, 클린 페이지는 즉시 프리(Free) 리스트로 페이지를 옮긴다. InnoDB 스토리지 엔진은 InnoDB 버퍼 풀 인스턴스별로 최대 innodb_lru_scan_depth 개수만큼 스캔하기 때문에 실질적으로 LRU 리스트의 스캔은 (innodb_buffer_pool_instances * innod_lru_scan_depth) 수만큼 수행한다.

버퍼 풀 상태 백업 및 복구

InnoDB 서버의 버퍼 풀은 퀴리의 성능에 매우 밀접하게 연결돼 있다. 쿼리 요청이 매우 빈번한 서버를 셧다운했다가 다시 시작하고 서비스를 시작하면 쿼리 처리 성능이 평상시보다 1/10도 안 되는 경우가 대부분일 것이다. 버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비돼 있으므로 디스크에서 데이터를 읽지 않아도 쿼리가 처리될 수 있기 때문이다. 이렇게 디스크의 데이터가 버퍼 풀에 적재돼 있는 상태를 워밍업(Warming Up)이라고 표현하는데, 버퍼 풀이 잘 워밍업된 상태에서는 그렇지 않은 경우보다 몇십 배의 쿼리 처리 속도를 보이는 것이 일반적이다. 그래서 MySQL 5.5 버전에서는 점검을 위해 MySQL 서버를 셧다운했다가 다시 시작하는 경우 서비스를 오픈하기 전에 강제 워밍업을 위해 주요 테이블과 인덱스에 대해 풀 스캔을 한 번씩 실행하고 서비스를 오픈했었다.

하지만 MySQL 5.6 버전부터는 버퍼 풀 덤프 및 적재 기능이 도입됐다. 서버 점검이나 기타 작업을 위해 MySQL 서버를 재시작해야 하는 경우 MySQL 서버를 셧다운하기 전에 다음과 같이 innodb_buffer_pool_dump_now 시스템 변수를 이용해 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있다. 그리고 MySQL 서버를 다시 시작하면 innodb_buffer_pool_load_now 시스템 변수를 이용해 백업된 버퍼 풀의 상태를 다시 복구할 수 있다.

// MySQL 서버 셧다운 전에 버퍼 풀의 상태 백업
SET GLOBAL innodb_buffer_pool_dump_now=ON;

// MySQL 서버 재시작 후, 버퍼 풀의 상태 복구
SET GLOBAL innodb_buffer_pool_load_now=ON;

InnoDB 버퍼 풀의 백업은 데이터 디렉터리에 ib_buffer_pool 이라는 이름의 파일로 생성되는데, 실제 이 파일의 크기를 보면 아무리 InnoDB 버퍼 풀이 크다 하더라도 몇십 MB 이하인 것을 알 수 있다. 이는 InnoDB 스토리지 엔진이 버퍼 풀의 LRU 리스트에서 적재된 데이터 페이지의 메타 정보만 가져와서 저장하기 때문이다. 그래서 버퍼 풀의 백업은 매우 빨리 완료된다. 하지만 백업된 버퍼 풀의 내용 을 다시 버퍼 풀로 복구하는 과정은 InnoDB 버퍼 풀의 크기에 따라 상당한 시간이 걸릴 수도 있다. 이는 백업된 내용에서 각 테이블의 데이터 페이지를 다시 디스크에서 읽어와야 하기 때문이다. 그래서 InnoDB 스토리지 엔진은 버퍼 풀을 다시 복구하는 과정이 어느 정도 진행됐는지 확인할 수 있게 상태 값을 제공한다. 

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status '\G 

Variable_name: Innodb_ buffer_pool_dump_status 
Value: Buffer pool(s) dump completed at 200712 23:38:58

버퍼 풀 적재 작업 중지/재개

버퍼 풀 적재 작업에 너무 시간이 오래 걸려서 중간에 멈추고자 한다면 innodb_ buffer_pool_load_abort 시스템 변수를 이용하면 된다. InnoDB의 버퍼 풀을 다시 복구하는 작업은 상당히 많은 디스크 읽기를 필요로 하기 때문에 버퍼 풀 복구가 실행 중인 상태에서 서비스를 재개하는 것은 좋지 않은 선택일 수도 있다. 그래서 버퍼 풀 복구 도중에 급히 서비스를 시작해야 한다면 다음과 같이 버퍼 풀 복구를 멈출 것을 권장한다.

mysql> SET GLOBAL innodb_buffer_ pool_load_ abort=ON;

백업과 복구 자동화

지금까지 수동으로 InnoDB 버퍼 풀의 백업과 복구를 살펴봤는데, 사실 이 작업을 수동으로 하기는 쉽지 않다. 다른 작업을 위해 MySQL 서버를 재시작하는 경우 해야 할 작업에 집중한 나머지 버퍼 풀의 백업과 복구 과정을 잊어버리기 십상이다. 그래서 InnoDB 스토리지 엔진은 MySQL 서버가 셧다운 되기 직전에 버퍼 풀의 백업을 실행하고, MySQL 서버가 시작되면 자동으로 백업된 버퍼 풀의 상태를 복구할 수 있는 기능을 제공한다. 버퍼 풀의 백업과 복구를 자동화하려면 innodb_buffer_pool_dump_at_shutdown과 innodb_buffer_pool_load_at_startup 설정을 MySQL 서버의 설정 파일에 넣어두면 된다.

InnoDB 버퍼 풀의 백업은 ib_buffer pool 파일에 기록되는데, 그렇다고 해서 반드시 셧다운하기 직전의 파일일 필요는 없다. InnoDB 스토리지 엔진은 ib_buffer_pool 파일에서 데이터 페이지의 목록을 가져와서 실제 존재하는 데이터 페이지이면 InnoDB 버퍼 풀로 적재하지만 그렇지 않은 경우에는 그냥 조용히 무시해버린다. 그래서 실제 존재하지 않는 데이터 페이지 정보가 ib_buffer_pool 파일에 명시돼 있다고 해서 MySQL 서버가 비정상적으로 종 료되거나 하지는 않는다.

버퍼 풀의 적재 내용 확인

버퍼 풀의 내용 확인의 오버헤드

버퍼 풀의 적재 내용 확인 MySQL 5.6 버전부터 MySQL 서버의 information_schema 데이터베이스의 innodb_buffer_page 테이블을 이용해 InnoDB 버퍼 풀의 메모리에 어떤 테이블의 페이지들이 적재돼 있는지 확인할 수 있었다. 하지만 InnoDB 버퍼 풀이 큰 경우에는 이 테이블 조회가 상당히 큰 부하를 일으키면서 서비스 쿼리가 많이 느려지는 문제가 있었다. 그래서 실제 서비스용으로 사용되는 MySQL 서버에서는 버퍼 풀의 상태를 확인하는 것이 거의 불가능했다.

인덱스별 데이터 페이지 확인

MySQL 8.0 버전에서는 이러한 문제점을 해결하기 위해 information_schema 데이터베이스에 innodb_cached_indexes 테이블이 새로 추가됐다. 이 테이블을 이용하면 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있다.

이것도 어차피 같은 테이블 조회가 아닌가?

SELECT
	it.name table_name,
    ii.name index_name,
    ici.n_cached_pages n_cached_pages
  FROM information_schema.innodb_tables it
  	INNER JOIN information_schema.innodb_indexes ii ON ii.table_id = it.table_id
    INNER JOIN information_schema.innodb_cached_indexes ici ON ici.index_id = it.index_id
    WHERE it.name=CONCAT('employees', '/', 'employees');

조금만 응용하면 테이블 전체(인덱스 포함) 페이지 중에서 대략 어느 정도 비율이 InnoDB 버퍼 풀에 적재돼 있는지 다음과 같이 확인해볼 수 있다. 

SELECT 
	(SELECT SUM(ici.n_cached_pages) n_cached_pages 
    FROM information_schema.innodb_tables it 
    INNER JOIN information_schema.innodb_indexes ii ON ii.table_id = it. table_id 
    INNER JOIN information_schema.innodb_cached_indexes ici ON ici.index_id = ii.index_id 
    WHERE it.name=CONCAT(t.table_schema, '/' t.table_name)) as total_cached_I pages, ((t.data_length + t.index_ length - t.data_free)/@@innodb_page_size) as total_pages 
    FROM information_schema.tables t 
    WHERE t.table_schema='employees'
    AND t.table_name= 'employees';

아직 MySQL 서버는 개별 인덱스별로 전체 페이지 개수가 몇 개인지는 사용자에게 알려주지 않기 때문에 information_schema의 테이블을 이용해도 테이블의 인덱스별로 페이지가 InnoDB 버퍼 풀에 적재된 비율은 확인할 수가 없다. 그래서 앞의 예제에서는 테이블 단위로 전체 데이터 페이지 개수와 InnoDB 버퍼 풀에 적재된 데이터 페이지 개수의 합을 조회해본 것이다.

Double Write Buffer

파셜 페이지(Partial-page)/톤 페이지(Torn -page) 문제

InnoDB 스토리지 엔진의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다. 이로 인해 InnoDB의 스토리지 엔진에서 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제가 발생하면 그 페이지의 내용은 복구할 수 없을 수도 있다. 이렇게 페이지가 일부만 기록 되는 현상을 파셜 페이지(Partial-page) 또는 톤 페이지(Torn -page)라고 하는데, 이런 현상은 하드 웨어의 오작동이나 시스템의 비정상 종료 등으로 발생할 수 있다.

Double-Write 기법

InnoDB 스토리지 엔진에서는 이 같은 문제를 막기 위해 Double-Write 기법을 이용한다. 아래 그림은 InnoDB의 Double-Write 기법이 작동하는 방식을 표현한 것이다. 그림에서와 같이 InnoDB에서 'A' ~ 'E'까지의 더티 페이지를 디스크로 플러시한다고 가정해보자. 이때 InnoDB 스토리지 엔진은 실제 데이터 파일에 변경 내용을 기록하기 전에 'A' ~ 'E'까지의 더티 페이지를 우선 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 Double Write 버퍼에 기록한다. 그리고 InnoDB 스토리지 엔진은 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기를 실행한다.

이렇게 시스템 테이블스페이스의 DoubleWrite 버퍼 공간에 기록된 변경 내용은 실제 데이터 파일에 'A' ~ 'E' 더티 페이지가 정상적으로 기록되면 더이상 필요가 없어진다. DoubleWrite 버퍼의 내용은 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래의 목적으로 사용된다. 'A'와 'B' 페이지는 정상적 으로 기록됐지만 'C' 페이지가 기록되는 도중에 운영체제가 비정상적으로 종료됐다고 가정해보자. 그러 면 InnoDB 스토리지 엔진은 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들 을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다. DoubleWrite 기능을 사용할지 여부는 innodb_doublewrite 시스템 변수로 제어할 수 있다.

용법

DoubleWrite 버퍼는 데이터의 안정성을 위해 자주 사용되는데, HDD처럼 자기 원판(Platter)이 회전 하는 저장 시스템에서는 어차피 한 번의 순차 디스크 쓰기를 하는 것이기 때문에 별로 부담이 되지 않지만 SSD처럼 랜덤 IO나 순차 IO의 비용이 비슷한 저장 시스템에서는 상당히 부담스럽다. 하지만 데이터의 무결성이 매우 중요한 서비스에서는 DoubleWrite의 활성화를 고려하는 것이 좋다. 만약 데이 터베이스 서버의 성능을 위해 InnoDB 리두 로그 동기화 설정(innodb_flush_log_at_trx_commit 시스템 변수)을 1이 아닌 값으로 설정했다면 DoubleWrite도 비활성화하는 것이 좋다.

일반적으로 MySQL 서버에서는 복제를 이용해 동일 데이터에 대해 여러 개의 사본을 유지하기 때문에 MySQL 서버가 비정상 종료되면 버리고 바이너리 로그를 이용해 다시 동기화하는 경우도 많다. MySQL 서버의 데이터 무결성에 민감한 서비스라면 DoubleWrite뿐만 아니라 InnoDB의 리두 로그와 복제를 위한 바이너리 로그 등 트 랜잭선을 COMMIT하는 시점에 동기화할 것들이 많다는 점에 주의하자. 즉, 리두 로그는 동기화하지 않으면서(innodb_flush_log_at_trx_commit 시스템 변수가 1이 아닌 경우) DoubleWrite만 활성화한다는 것은 잘못된 선택이다.

언두 로그

InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업한다. 이렇게 백업된 데이터를 언두 로그(Undo Log)라고 한다. 언두 로그가 어떻게 사용되는지 간단히 한번 살펴보자.

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

언두 로그는 InnoDB 스토리지 엔진에서 매우 중요한 역할을 담당하지만 관리 비용도 많이 필요하다. 여기서는 언두 로그가 어떤 문제점을 가지고 있고, 이를 위해 InnoDB 스토리지 엔진이 어떤 기능을 제공하는지 살펴보자.

언두 로그 레코드 모니터링

언두 로그의 데이터가 어떻게 저장되고 어떤 목적으로 사용되는지 살펴보자. 언두 영역은 INSERT, UPDATE, DELETE 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터(이전 데이터)를 보관하는 곳이다. 예를 들어, 다음과 같은 업데이트 문장을 실행했다고 해보자.

UPDATE nember SET name='홍길동' WHERE member_id=1;

위 문장이 실행되면 트랜잭션을 커밋하지 않아도 실제 데이터 파일(데이터/인덱스 버퍼) 내용은 '홍길동'으로 변경된다. 그리고 변경되기 전의 값이 '벽계수'였다면, 언두 영역에는 '벽계수'라는 값이 백업되는 것이다. 이 상태에서 사용자가 커밋하면 현재 상태가 그대로 유지되고, 롤백하면 언두 영역의 백업된 데이터를 다시 데이터 파일로 복구한다.

언두 로그 데이터 영역 용도

언두 로그의 데이터는 크게 두 가지 용도로 사용되는데, 첫 번째 용도가 바로 위에서 언급한 트랜잭션의 롤백 대비용이다. 두 번째 용도는 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는 데 있다. 트랜잭션의 격리 수준이라는 개념이 있는데, 이는 동시에 여러 트랜잭션이 데이터를 변경하거나 조회할 때 한 트랜잭션의 작업 내용이 다른 트랜잭션에 어떻게 보일지를 결정하는 기준이다.

대용량 처리에서 언두 로그 문제점

MySQL 5.5 이전 버전의 MySQL 서버에서는 한 번 증가한 언두 로그 공간은 다시 줄어들지 않았다. 예를 들어, 1억 건의 레코드가 저장된 100GB 크기의 테이블을 DELETE로 삭제한다고 가정해보자. 그러면 MySQL 서버는 이 테이블에서 레코드를 한 건 삭제하고 언두 로그에 삭제되기 전 값을 저장한다. 이렇게 1억 건의 레코드가 테이블에서는 삭제되지만 언두 로그로 복사돼야 한다. 즉, 테이블의 크기만큼 언두 로그의 공간 사용량이 늘어나 결국 언두 로그 공간이 100GB가 되는 것이다.

대용량의 데이터를 처리하는 트랜잭션뿐만 아니라 트랜잭션이 오랜 시간 동안 실행될 때도 언두 로그의 양은 급격히 증가할 수 있다. 트랜잭션이 완료됐다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것은 아니다. 아래 그림은 3개의 트랜잭션이 서로 시작과 종료 시점이 다르게 실행되는 것을 보여준다. 그림에서 B 트랜잭션과 C 트랜잭션은 완료됐지만 가장 먼저 시작된 트랜잭션 A는 아직 완료되지 않은 상태다. 이때 트랜잭션 B와 C는 각각 UPDATE와 DELETE를 실행했으므로 변 경 이전의 데이터를 언두 로그에 백업했을 것이다. 하지만 먼저 시작된 A 트랜잭션이 아직 활성 상태이 기 때문에 B와 C 트랜잭션의 완료 여부와 관계없이 B와 C 트랜잭션이 만들어낸 언두 로그는 삭제되지 않는다.

일반적으로 응용 프로그램에서 트랜잭션 관리가 잘못된 경우 이런 현상이 발생할 수 있지만 사용자의 실수로 인해 더 자주 문제가 되곤 한다. 서비스용으로 사용되는 MySQL 서버에서 사용자가 트랜잭션을 시작한 상태에서 완료하지 않고 하루 정도 방치했다고 가정해보자. 그러면 InnoDB 스토리지 엔진은 이 트랜잭션이 시작된 시점부터 생성된 언두 로그를 계속 보존할 것이다. 결국 InnoDB 스토리지 엔진의 언두 로그는 하루치 데이터 변경을 모두 저장하고, 디스크의 언두 로그 저장 공간은 계속 증가한다. 이렇게 누적된 언두 로그로 인해 디스크의 사용량이 증가하는 것은 그다지 큰 문제가 아닐 수도 있다. 하지만 그동안 빈번하게 변경된 레코드를 조회하는 쿼리가 실행되면 InnoDB 스토리지 엔진은 언두 로그의 이력을 필요한 만큼 스캔해야만 필요한 레코드를 찾을 수 있기 때문에 쿼리의 성능이 전반적으로 떨어지게 된다.

언두 로그 자동 해제

MySQL 5.5 버전까지는 이렇게 언두 로그의 사용 공간이 한 번 늘어나면 MySQL 서버를 새로 구축하지 않는 한 줄일 수가 없었다. 언두 로그가 늘어나면 디스크 사용량뿐만 아니라 매번 백업할 때도 그만큼 더 복사를 해야 하는 문제점이 발생한다. 다행스럽게도 MySQL 5.7과 MySQL 8.0으로 업그레이드 되면서 언두 로그 공간의 문제점은 완전히 해결됐다. MySQL 8.0에서는 언두 로그를 돌아가면서 순차 적으로 사용해 디스크 공간을 줄이는 것도 가능하며, 때로는 MySQL 서버가 필요한 시점에 사용 공간 을 자동으로 줄여 주기도 한다.

하지만 여전히 서비스 중인 MySQL 서버에서 활성 상태의 트랜잭션이 장시간 유지되는 것은 성능상 좋지 않다. 그래서 MySQL 서버의 언두 로그 레코드가 얼마나 되는지는 항상 모니터링하는 것이 좋은 데, 다음과 같이 MySQL 서버의 언두 로그 레코드 건수를 확인할 수 있다.

// MySQL 8.0 버전에서 사용 가능한 명령어
SELECT count
	FROM information_schema.innodb_metrics
    WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';

MySQL 서버에서 실행되는 INSERT, UPDATE, DELETE 문장이 얼마나 많은 데이터를 변경하느냐에 따라 평상시 언두 로그에 존재하는 레코드 건수는 상이할 수 있다. 그래서 MySQL 서버별로 이 값은 차이를 보이는데, 서버별로 안정적인 시점의 언두 로그 레코드 건수를 확인해 이를 기준으로 언두 로그의 급증 여부를 모니터링하는 것이 좋다.

언두 테이블스페이스 관리

언두 로그가 저장되는 공간을 언두 테이블스페이스(Undo Tablespace)라고 한다. 언두 테이블스페이스는 MySQL 서버의 버전별로 많은 변화가 있었다. MySQL 5.6 이전 버전에서는 언두 로그가 모두 시스템 테이블스페이스(ibdata, ibd) 에 저장됐다. 하지만 시스템 테이블스페이스의 언두 로그는 MySQL 서버가 초기화될 때 생성되기 때문에 확장의 한계가 있었다. 그래서 MySQL 5.6 버전에서는 innodb_ undo_tablespaces 시스템 변수가 도입됐고, innodb_undo_tablespaces 시스템 변수를 2보다 큰 값을 설정하면 InnoDB 스토리지 엔진은 더이상 언두 로그를 시스템 테이블스페이스에 저장하지 않고 별도의 언두 로그 파일을 사용한다. 하지만 MySQL 5.6 이후 버전에서도 innodb_ undo_tablespaces를 0으로 설 정하면 여전히 MySQL 5.6 이전의 버전과 동일하게 언두 로그가 시스템 테이블스페이스에 저장됐다. MySQL 8.0으로 업그레이드되면서(MySQL 8.0.14 버전부터) innodb_undo_tablespaces 시스템 변수는 효력이 없어졌으며(Deprecated) 언두 로그는 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록되도록 개선됐다.

언두 테이블스페이스 구조

아래 그림은 언두 테이블스페이스가 어떤 형태로 구성되는지를 보여준다. 하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯(Undo Slot)을 가진다.

하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수만큼의 언두 슬롯을 가진다. 예를 들어, InnoDB의 페이지 크기가 16KB라면 하나의 롤백 세그먼트는 1024개의 언두 슬롯을 갖게 된다. 하나의 트랜잭션이 필요로 하는 언두 슬롯의 개수는 트랜잭션이 실행하는 INSERT, UPDATE, DELETE 문장의 특성에 따라 최대 4개까지 언두 슬롯을 사용하게 된다. 일반적으로는 트랜잭션이 임시 테이블을 사용하지 않으므로 하나의 트랜잭션은 대략 2개 정도의 언두 슬롯을 필요로 한다고 가정하면 된다. 그래서 최대 동시 처리 가능한 트랜잭션의 개수는 다음 수식으로 예측해볼 수 있다. 

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

가장 일반적인 설정인 16KB InnoDB에서 기본 설정(innodb_undo_tablespaces=2, innodb_rollback_segments=128)을 사용한다고 가정하면 대략 2097152(=16 * 1024 * 128 * 2 / 2)개 정도의 트랜잭션이 동시에 처리 가능해진다. 물론 일반적인 서비스에서 이 정도까지 동시 트랜잭션이 필요하진 않겠지만 기본값으로 해서 크게 문제될 건 없으므로 가능하면 기본값을 유지하자. 언두 로그 공간이 남는 것은 크게 문제되지 않지만 언두 로그 슬롯이 부족한 경우에는 트랜잭션을 시작할 수 없는 심각한 문제가 발생한다. 언두 로그 관련 시스템 변수를 변경해야 한다면 적절히 필요한 동시 트랜잭선 개수에 맞게 언두 테이블스페이스와 롤백 세그먼트의 개수를 설정해야 한다.

언두 로그 변경

MySQL 8.0 이전까지는 한 번 생성된 언두 로그는 변경이 허용되지 않고 정적으로 사용됐지만 MySQL 8.0 버전부터는 CREATE UNDO TABLESPACE나 DROP TABLESPACE 같은 명령으로 새로운 언두 테이블 스페이스를 동적으로 추가하고 삭제할 수 있게 개선됐다.

MySQL 5.6 이전 버전 에서는 언두 로그는 모두 시스템 테이블스페이스(ibdata.ibd)에 저장됐다. 하지만 시스템 테이블스페이스의 언두 로 그는 MySQL 서버가 초기화될 때 생성되기 때문에 확장의 한계가 있었다. 그래서 MySQL 5.6 버전에서는 innodb_undo_tablespaces 시스템 변수가 도입됐고, innodb_undo_tablespaces 시스템 변수에 2보다 큰 값을 설정하면 InnoDB 스토리지 엔진은 더이상 언두 로그를 시스템 테이블스페이스에 저장하지 않고 별도의 언두 로그 파일을 사용한다. 하지만 MySQL 5.6 이후 버전에서도 innodb_undo_tablespaces를 0으로 설정하면 여전히 MySQL 5.6 이전의 버전과 동일하게 언두 로그가 시스템 테이블스페이스에 저장됐다. MySQL 8.0으로 업그레이드되면서(MySQL 8.0.14 버전부터) innodb_undo_tablespaces 시스템 변수는 효력이 없어졌으며(Deprecated), 언두 로그는 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록되도록 개선됐다.

언두 테이블스페이스 공간 반납

언두 테이블스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것을 Undo tablespace truncate라고 한다. 언두 테이블스페이스의 불필요한 공간을 잘라내는(Truncate) 방법은 자동과 수동으로 두 가지 방법이 있는데, 두 가지 방법 모두 MySQL 8.0부터 지원된다.

  • 자동 모드: 트랜잭션이 데이터를 변경하면 이전 버전의 데이터를 언두 로그로 기록하는데, 트랜잭션이 커밋되면 더 이상 언두 로그에 복사된 이전 값은 불필요해진다. InnoDB 스토리지 엔진의 퍼지 스레드(Purge Thread)는 주기적으로 깨어나서 언두 로그 공간에서 불필요해진 언두 로그를 삭제하는 작업을 실행하는데, 이 작업을 언두 퍼지 (Undo Purge)라고 한다. MySQL 서버의 innodb_undo_log_truncate 시스템 변수가 ON으로 설정되면, 퍼지 스레드는 주기적으로 언두 로그 파일에서 사용되지 않는 공간을 잘라내고 운영체제로 반납하게 된다. 언두 로그 파일을 잘라내는 작업을 더 빈번하게 또는 덜 빈번하게 실행되게 하려면 innodb_purge_rseg_truncate_frequency 시스템 변수의 값을 조정하면 된다.
  • 수동 모드: innodb_undo_log_truncate 시스템 변수가 OFF로 설정되어 언두 로그 파일의 잘라내기가 자동으로 실행되지 않거나 예상보다 자동 모드로 언두 테이블스페이스의 공간 반납이 부진한 경우에는 언두 테이블스페이스를 비활성화해서 언두 테이블스페이스가 더이상 사용되지 않도록 설정하면 퍼지 스레드는 비활성 상태의 언두 테이블 스페이스를 찾아서 불필요한 공간을 잘라내고 운영체제로 해당 공간을 반납하게 된다. 반납이 완료되면 언두 테이 블스페이스를 다시 활성화한다. 그리고 수동 모드는 언두 테이블스페이스가 최소 3개 이상은 돼야 작동한다는 것도 기억해 두자. 
-- // 언두 테이블스페이스 비활성화 
mysql> ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

-- / / 퍼지 스레드에 의해 언두 테이블스페이스 공간이 반납되면 다시 활성화 
mysql> ALTER UNDO TABLESPACE tablespace_name SET ACTIVE;

체인지 버퍼

RDBMS에서 레코드가 INSERT되거나 UPDATE될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요하다. 그런데 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모하게 된다. 그래서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키게 되는데, 이때 사용하는 임시 메모리 공간을 체인지 버퍼(Change Buffer)라고 한다.

사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다. 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드(Merge thread)라고 한다. MySQL 5.5 이전 버전까지는 INSERT 작업에 대해서만 이러한 버퍼링이 가능(그래서 MySQL 5.5 이전 버전까지는 이 버퍼를 인서트 버퍼라고 함)했는데, MySQL 5.5부터 조금씩 개선되면서 MySQL 8.0에서는 INSERT, DELETE, UPDATE로 인해 키를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 될 수 있게 개선됐다. 또 MySQL 5.5 이전 버전에서는 별도의 시스템 변수 설정 없이 기본적으로 기능이 활성화됐지만 MySQL 5.5부터는 innodb_change_buffering이라는 시스템 변수가 새로 도입되어 작업의 종류별로 체인지 버퍼를 활성화할 수 있으며, 체인지 버퍼가 비효율적일 때는 체인지 버퍼를 사용하지 않게 설정할 수 있게 개선됐다. innodb_change_buffering 시스템 변수에 설정할 수 있는 값은 다음과 같다.

  • all: 모든 인덱스 관련 작업(inserts deletes + purges)을 버퍼링
  • none: 버퍼링 안함
  • inserts: 인덱스에 새로운 아이템을 추가하는 작업만 버퍼링
  • deletes: 인덱스에서 기존 아이템을 삭제하는 작업(삭제됐다는 마킹 작업)만 버퍼링
  • changes: 인덱스에 추가하고 삭제하는 작업만(inserts + deletes) 버퍼링
  • purges: 인덱스 아이템을 영구적으로 삭제하는 작업만 버퍼링(백그라운드 작업)

체인지 버퍼와 메모리

체인지 버퍼는 기본적으로 InnoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있게 설정돼 있으며, 필요하다면 InnoDB 버퍼 풀의 50%까지 사용하게 설정할 수 있다. 체인지 버퍼가 너무 많은 버퍼 풀 공간을 사용하지 못하도록 한다거나 INSERT나 UPDATE 등이 너무 빈번하게 실행되어 체인지 버퍼가 더 많은 버퍼 풀을 사용할 수 있게 하고자 한다면 innodb_change_buffer_max_size 시스템 변수에 비율을 설정하면 된다.

체인지 버퍼가 버퍼 풀의 메모리를 얼마나 사용 중인지, 그리고 얼마나 많은 변경 사항을 버퍼링하고 있는지는 다음과 같이 확인할 수 있다.

체인지 버퍼가 사용 중인 메모리 공간의 크기 확인)

SELECT EVENT_NAME, CURRENT_NUMBER_OF_ BYTES_ USED 
FROM performance_ schema. memory_summary_global_by_event_name 
WHERE EVENT_ NAME= 'memory/innodb/ibuf@ibuf';

체인지 버퍼 관련 오퍼레이션 처리 횟수)

SHOW ENGINE INNODB STATUS \G

리두 로그 및 로그 버퍼

리두 로그 및 로그 버퍼 리두 로그(Redo Log)는 트랜잭션의 4가지 요소인 ACID 중에서 D(Durable)에 해당하는 영속성과 가장 밀접하게 연관돼 있다. 리두 로그는 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.

MySQL 서버를 포함한 대부분 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록한다. 거의 모든 DBMS에서 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료 구조를 가지고 있기 때문에 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요하다. 그래서 변경된 데이터를 데이터 파일에 기록하려면 상대적으로 큰 비용이 필요하다. 이로 인한 성능 저하를 막기 위해 데이터베이스 서버는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있으며, 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전의 상태로 복구한다. 데이터베이스 서버는 ACID도 중요하지만 성능도 중요하기 때문에 데이터 파일뿐만 아니라 리두 로그를 버퍼링할 수 있는 InnoDB 버퍼 풀이나 리두 로그를 버퍼링할 수 있는 로그 버퍼와 같은 자료 구조도 가지고 있다.

비정상 종료

MySQL 서버가 비정상 종료되는 경우 InnoDB 스토리지 엔진의 데이터 파일은 다음과 같은 두 가지 종류의 일관되지 않은 데이터를 가질 수 있다.

  1. 커밋됐지만 데이터 파일에 기록되지 않은 데이터
  2. 롤백됐지만 데이터 파일에 이미 기록된 데이터

1번의 경우 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 된다. 하지만 2번의 경우에는 리두 로그로는 해결할 수 없는데, 이때는 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면 된다. 그렇다고 해서 2번의 경우 리두 로그가 전혀 필요하지 않은 것은 아니다. 최소한 그 변경이 커밋됐는지, 롤백됐는지, 아니면 트랜잭션의 실행 중간 상태였는지를 확인하기 위해서라도 리두 로그가 필요하다.

리두 로그 동기화 오버헤드

데이터베이스 서버에서 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정하는 것을 권장한다. 그리고 당연히 그렇게 돼야만 서버가 비정상적으로 종료됐을 때 직전까지의 트랜잭션 커밋 내용이 리두 로그에 기록될 수 있고, 리두 로그를 이용해 장애 직전 시점까지의 복구가 가능해진다. 하지만 이처럼 트랜잭션이 커밋될 때마다 리두 로그를 디스크에 기록하는 작업은 많은 부하를 유발한다. 그래서 InnoDB 스토리지 엔진에서 리두 로그를 어느 주기로 디스크에 동기화할지를 결정하는 innodb_flush_log_at_trx_commit 시스템 변수를 제공한다.

innodb_flush_log_at_trx_commit 시스템 변수는 다음과 같은 값을 가질 수 있다.

  • innodb_flush_log_at_trx_commit = 0.1초에 한 번씩 리두 로그를 디스크로 기록(write)하고 동기화(sync)를 행한다. 그래서 서버가 비정상 중료되면 최대 1초 동안의 트랜잭션은 커밋됐다고 하더라도 해당 트랜잭션에서 변경한 데이터는 사라질 수 있다.
  • innodb_flush_log_at_trx_commit = 1: 매번 트랜잭션이 커밋될 때마다 디스크로 기록되고 동기화까지 수행된다. 그래서 트랜잭션이 일단 커밋되면 해당 트랜잭션에서 변경한 데이터는 사라진다.
  • innodb_flush_log_at_trx_commit = 2: 매번 트랜잭션이 커밋될 때마다 디스크로 기록(write)은 되지만 실질적인 동기화(sync)는 1초에 한 번씩 실행된다. 일단 트랜잭션이 커밋되면 변경 내용이 운영체제의 메모리 버퍼로 기록되는 것이 보장된다. 그래서 MySQL 서버가 비정상 종료됐더라도 운영체제가 정상적으로 작동한다면 해당 트랜잭 션의 데이터는 사라지지 않는다. MySQL 서버와 운영체제가 모두 비정상적으로 종료되면 최근 1초 동안의 트랜잭션 데이터는 사라질 수도 있다.

innodb_flush_log_at_trx_commit 시스템 변수가 0이나 2로 설정되는 경우 디스크 동기화 작업이 항상 1초 간격으로 실행되는 것은 아니다. 스키마 변경을 위한 DDL이 실행되면 리두 로그가 디스크로 동기화되기 때문에 InnoDB 스토리지 엔진이 스키마 변경을 위한 DDL을 실행했다면 1초보다 간격이 작을 수도 있다. 하지만 스키마 변경 작업은 자주 실행되는 작업은 아니므로 리두 로그는 최대 1초 정도 손 실이 발생할 수 있다는 정도로 기억해두자. 또한 innodb_flush_log_at_trx_commit 시스템 변수가 0이나 2인 경우, 디스크 동기화 시간 간격을 innodb_flush_log_at_timeout 시스템 변수를 이용해 변경할 수 있다. 기본값은 1초이며, 일반적인 서비스에서 이 간격을 변경할 만한 특별한 이유는 없을 것으로 보인다.

리두 로그 파일 크기 설정

InnoDB 스토리지 엔진의 리두 로그 파일들의 전체 크기는 InnoDB 스토리지 엔진이 가지고 있는 버퍼 풀의 효율성을 결정하기 때문에 신중히 결정해야 한다. 리두 로그 파일의 크기는 innodb_log_file_size 시스템 변수로 결정하며, innodb_log_files_in_group 시스템 변수는 리두 로그 파일의 개수를 결정 한다. 그래서 전체 리두 로그 파일의 크기는 두 시스템 변수의 곱으로 결정된다. 그리고 리두 로그 파일의 전체 크기가 InnoDB 버퍼 풀의 크기에 맞게 적절히 선택돼야 InnoDB 스토리지 엔진이 적절히 변 경된 내용을 버퍼 풀에 모았다가 한 번에 모아서 디스크에 기록할 수 있다. 하지만 사용량(특히 변경 작업)이 매우 많은 DBMS 서버의 경우에는 이 리두 로그의 기록 작업이 큰 문제가 되는데, 이러한 부분 을 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링한다. 이러한 리두 로그 버퍼링에 사용되는 공간이 버퍼다.

로그 버퍼의 크기는 기본값인 16MB 수준에서 설정하는 것이 적합한데, BLOB이나 TEXT와 같이 큰 데이터를 자주 변경하는 경우에는 더 크게 설정하는 것이 좋다.

리두 로그 아카이빙

MySQL 8.0 버전부터 InnoDB 스토리지 엔진의 리두 로그를 아카이빙할 수 있는 기능이 추가됐다. MySQL 엔터프라이즈 백업이나 Xtrabackup 툴은 데이터 파일을 복사하는 동안 InnoDB 스토리지 엔진의 리두 로그에 쌓인 내용을 계속 추적하면서 새로 추가된 리두 로그 엔트리를 복사한다. 데이터 파일을 복사하는 동안 추가된 리두 로그 엔트리가 같이 백업되지 않는다면 복사된 데이터 백업 파일은 일관된 상태를 유지하지 못한다. 그런데 MySQL 서버에 유입되는 데이터 변경이 너무 많으면 리두 로그가 매우 빠르게 증가하고, 엔터프라이즈 백업이나 Xtrabackup 틀이 새로 추가되는 리두 로그 내용을 복사하기도 전에 덮어쓰일 수도 있다. 이렇게 아직 복사하지 못한 리두 로그가 덮어쓰이면 백업 툴이 리두 로그 엔트리를 복사할 수 없어서 백업은 실패하게 된다. MySQL 8.0의 리두 로그 아카이빙 기능은 데이터 변경이 많아서 리두 로그가 덮어쓰인다고 하더라도 백업이 실패하지 않게 해준다.

백업 툴이 리두 로그 아카이빙을 사용하려면 먼저 MySQL 서버에서 아카이빙된 리두 로그가 저장될 디렉터리를 innodb_redo_log_archive_dirs 시스템 변수에 설정해야 하며, 이 디렉터리는 운영체제의 MySQL 서버를 실행하는 유저(일반적으로 mysql 유저)만 접근이 가능해야 한다. 

linux> mkdir /var/log/mysql_redo_ archive 
linux> cd /var/log/mysql_redo_archive 
linux> mkdir 20200722 
linux> chmod 700 20200722 

mysql> SET GLOBAL innodb_redo_log_archive_dirs='backup:/var/log/mysql_redo_archive';

디렉터리가 준비되면 다음과 같이 리두 로그 아카이빙을 시작하도록 innodb_redo_log_archive_start UDF(사용자 정의 함수: User Defined Function)를 실행하면 된다. innodb_redo_log_archive_start UDF는 1개 또는 2개의 파라미터를 입력할 수 있는데, 첫 번째 파라미터는 리두 로그를 아카이빙할 디렉터리에 대한 레이블이며 두 번째 파라미터는 서브디렉터리의 이름이다. 두 번째 파라미터는 입력하지 않아도 되는데, 이때는 innodb_redo_log_archive_dirs 시스템 변수의 레이블에 해당하는 디렉터리에 별도 서브디렉터리 없이 리두 로그를 복사한다. 

DO innodb_ redo... log_archive_start( 'backup , '20200722');

이제 리두 로그 아카이빙이 정상적으로 실행되는지 확인하기 위해 간단히 데이터 변경 명령을 몇 개 실행해보자. 

CREATE TABLE test (id bigint auto_increment, data mediumtext, PRIMARY KEY(id)); 
INSERT INTO test (data) 
SELECT repeat('123456789' , 10000) FROM employees.salaries LIMIT 100;

INSERT를 실행하고 리두 로그 아카이빙 디렉터리를 확인해보면 다음과 같이 리두 로그의 내용이 아카 이빙 파일로 복사된 것을 확인할 수 있다. InnoDB의 리두 로그 아카이빙은 로그 파일이 로테이션될 때 복사되는 것이 아니라 리두 로그 파일에 로그 엔트리가 추가될 때 함께 기록되는 방식을 사용하고 있어서 데이터 변경이 발생하면 즉시 아카이빙된 로그 파일의 크기가 조금씩 늘어나는 것을 확인할 수 있다. 

linux> ls -alh 20200722/ -I--I---- 1 matt.lee 991M 7 22 11:12 archive. 5b30884e-726c-11ea-951c-f91ea9f6d340. 000001.log

리두 로그 아카이빙을 종료할 때는 innodb_redo_log_archive_stop UDF를 실행하면 된다. innodb_redo_log_archive_stop UDF를 실행하면 InnoDB 스토리지 엔진은 리두 로그 아카이빙을 멈추고 아카이빙 파일도 종료한다. 하지만 아카이빙 파일을 삭제하지는 않기 때문에 사용이 완료되면 사용자가 수동으로 삭제해야 한다. 

mysql> DO innodb_redo_log_archive_stop();

innodb_redo_log_archive_start UDF를 실행한 세션이 계속 연결이 끊어지지 않고 유지돼야 리두 로그 아카이빙이 계속 실행된다. 만약 리두 로그 아카이빙을 시작한 세션이 innodb_redo_log_archive_stop UDF를 실행하기 전에 연결이 끊어진다면 InnoDB 스토리지 엔진은 리두 로그 아카이빙을 멈추고 아 카이빙 파일도 자동으로 삭제해버린다. 아카이빙을 시작한 세션이 비정상적으로 종료되면서 아카이빙 된 리두 로그도 쓸모가 없기 때문이다. 그래서 아카이빙된 리두 로그를 정상적으로 사용하려면 커넥션 을 그대로 유지해야 하며, 작업이 완료되면 반드시 innodb_redo_log_archive_stop UDF를 호출해서 아 카이빙을 정상적으로 종료해야 한다.

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

InnoDB 스토리지 엔진의 리두 로그는 하드웨어나 소프트웨어 등 여러 가지 문제점으로 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 트랜잭션을 복구하기 위해 항상 활성화돼 있다. MySQL 서버에서 트랜잭션이 커밋돼도 데이터 파일은 즉시 디스크로 동기화되지 않는 반면, 리두 로그(트랜잭션 로그)는 항상 디스크로 기록된다. MySQL 8.0 이전 버전까지는 수동으로 리두 로그를 비활성화할 수 있는 방법이 없었지만 MySQL 8.0 버전부터는 수동으로 리두 로그를 활성화하거나 비활성화할 수 있게 됐다. 그래서 MySQL 8.0 버전부터는 데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우 다음과 같이 리두 로그를 비활성화해서 데이터의 적재 시간을 단축시킬 수 있다. 

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG; 
- // 리두 로그를 비활성화한 후 대량 데이터 적재를 실행 
mysql> LOAD DATA ...
mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;

ALTER INSTANCE [ENABLE I DISABLE] INNODB REDO_LOG 명령을 실행한 후, Innodb_redo_log_enabled 상태 변수를 살펴보면 리두 로그가 활성화되거나 비활성화됐는지 확인할 수 있다.

nysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';

리두 로그를 비활성화하고 데이터 적재 작업을 실행했다면 데이터 적재 완료 후 리두 로그를 다시 활성화하는 것을 잊지 말자. 리두 로그가 비활성화된 상태에서 MySQL 서버가 비정상적으로 종료된다면 MySQL 서버의 마지막 체크포인트 이후 시점의 데이터는 모두 복구할 수 없게 된다. 더 심각한 문제는 MySQL 서버의 데이터가 마지막 체크포인트 시점의 일관된 상태가 아닐 수 있다는 것이다. 예를 들어, 마지막 체크포인트가 10시 정각에 실행됐고, 10시 1분에 MySQL 서버가 비정상적으로 종료됐다고 가 정해보자. 이때 리두 로그가 없었다면 재시작된 MySQL 서버의 데이터 파일의 각 부분들은 10시 정각부터 10시 1분까지 다양한 시점의 데이터를 골고루 갖게 되는 것이다.

MySQL 서버는 항상 새롭게 시작될 때 자신이 가진 리두 로그에서 데이터 파일에 기록되지 못한 데이터가 있는지 검사를 하게 된다. 그런데 ALTER INSTANCE DISABLE INNODB REDO_LOG 명령으로 리두 로그가 비활성화된 상태에서 MySQL 서버가 비정상적으로 종료되면 리두 로그를 이용한 복구가 불가능하기 때문에 MySQL 서버는 정상적으로 시작되지 못할 수도 있다. 이렇게 리두 로그가 비활성화된 상태에서 MySQL 서버가 재시작되는 경우에는 innodb_force_recovery 시스템 변수를 6으로 설정 후 다시 시작해야 한다. ALTER INSTANCE DISABLE INNODB REDO_LOG 명령으로 리두 로그를 비활성화한 후 이런저런 작업을 하다 보면 다시 리두로그를 활성화하는 것을 잊어버리기도 하는데, 이러한 경우 의도하지 않게 데이터가 손실될 수도 있으니 주의하자.

그래서 데이터가 중요하지 않다 하더라도 서비스 도중에는 리두 로그를 활성화해서 MySQL 서버가 비정상적으로 종료돼도 특정 시점의 일관된 데이터를 가질 수 있게 하자. 만약 MySQL 서버가 비정상적으로 종료되어 데이터가 일부 손실돼도 괜찮다면 리두 로그를 비활성화하는 것보다 innodb_flush_log_ at_trx_commit 시스템 변수를 1이 아닌 0 또는 2로 설정해서 사용할 것을 권장한다.

어댑티브 해시 인덱스

일반적으로 '인덱스'라고 하면 이는 테이블에 사용자가 생성해둔 B-Tree 인덱스를 의미한다. 인덱스가 사용하는 알고리즘이 B-Tree는 아니더라도, 사용자가 직접 테이블에 생성해둔 인덱스가 우리가 일반적으로 알고 있는 인덱스일 것이다. 하지만 여기서 언급하는 '어댑티브 해시 인덱스(Adaptive Hash Index)'는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이며, 사용자는 innodb_adaptive_hash_index 시스템 변수를 이용해서 어댑티브 해시 인덱스 기능을 활성화하거나 비활성화할 수 있다.

B-Tree 인덱스에서 특정 값을 찾는 과정은 매우 빠르게 처리된다고 많은 사람이 생각한다. 하지만 결국 빠르냐 느리냐의 기준은 상대적인 것이며, 데이터베이스 서버가 얼마나 많은 일을 하느냐에 따라 B-Tree 인덱스에서 값을 찾는 과정이 느려질 수도 있고 빨라질 수도 있다. B-Tree 인덱스에서 특정 값을 찾기 위해서는 B-Tree의 루트 노드를 거쳐서 브랜치 노드, 그리고 최종적으로 리프 노드까지 찾아가야 원하는 레코드를 읽을 수 있다. 적당한 사양의 컴퓨터에서 이런 작업을 동시에 몇 개 실행한다고 해서 성능 저하가 보이지는 않을 것이다. 하지만 이런 작업을 동시에 몇천 개의 스레드로 실행하면 컴퓨터의 CPU는 엄청난 프로세스 스케줄링을 하게 되고 자연히 퀴리의 성능은 떨어진다.

어댑티브 해시 인덱스는 이러한 B-Tree 검색 시간을 줄여주기 위해 도입된 기능이다. InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다. B-Tree를 루트 노드부터 리프 노드까지 찾아가는 비용이 없어지고 그만큼 CPU는 적은 일을 하지만 쿼리의 성능은 빨라진다. 그와 동시에 컴퓨터는 더 많은 쿼리를 동시에 처리할 수 있게 된다.

해시 인덱스는 '인덱스 키 값'과 해당 인덱스 키 값이 저장된 '데이터 페이지 주소'의 쌍으로 관리되는데, 인덱스 키 값은 'B-Tree 인덱스의 고유번호(Id)와 B-Tree 인덱스의 실제 키 값' 조합으로 생성된다. 어댑티브 해시 인덱스의 키 값에 'B-Tree 인덱스의 고유번호'가 포함되는 이유는 InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스는 하나만 존재(물론 파티션되는 기능이 있지만)하기 때문이다. 즉, 모 든 B-Tree 인덱스에 대한 어댑티브 해시 인덱스가 하나의 해시 인덱스에 저장되며, 특정 키 값이 어느 인덱스에 속한 것인지도 구분해야 하기 때문이다. 그리고 '데이터 페이지 주소'는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가지는데, 이는 InnoDB 버퍼 풀에 로딩된 페이지의 주소를 의미한다. 그래서 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 버퍼 풀에서 해 당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지의 정보는 사라진다.

성능 비교

어댑티브 해시 인덱스가 보여줄 수 있는 성능 효과를 그래프로 잠시 살펴보자.

두 그래프는 다음과 같이 단순한 쿼리를 MySQL 서버가 최대한 처리할 수 있는 수준까지 실행 하는 상태에서 어댑티브 해시 인덱스를 활성화했을 때의 변화를 보여준다. 

mysql> SELECT fd1 FROM tab WHERE idx_fd2 IN (?, ?, ?, ?, ...);

어댑티브 해시 인덱스가 활성화되지 않았을 때는 초당 20,000개 정도의 쿼리를 처리하면서 CPU 사용률은 100%였다. 그런데 어댑티브 해시 인덱스를 활성화한 후 쿼리의 처리량은 2배 가까이 늘어났음에 도 불구하고 CPU 사용률은 오히려 떨어진 것을 볼 수 있다. 물론 B-Tree의 루트 노드부터 검색이 많이 줄면서 InnoDB 내부 잠금(세마포어)의 횟수도 획기적으로 줄어든다.

인덱스 파티션

예전 버전까지는 어댑티브 해시 인덱스는 하나의 메모리 객체인 이유로 어댑티브 해시 인덱스의 경합 (Contention)이 상당히 심했다. 그래서 MySQL 8.0부터는 내부 잠금(세마포어) 경합을 줄이기 위해 어댑티브 해시 인덱스의 파티션 기능을 제공한다. innodb_adaptive_hash_index_parts 시스템 변수를 이용해 파티션 개수를 변경할 수 있는데, 기본값은 8개이며 만약 어댑티브 해시 인덱스가 성능에 많은 도 움이 된다면 파티션 개수를 더 많이 설정하는 것도 어댑티브 해시 인덱스의 내부 잠금 경합을 줄이는 데 많은 도움이 될 것이다.

장단점

여기까지만 보면 InnoDB 스토리지 엔진의 어댑티브 해시 인덱스는 팔방미인처럼 보이지만, 실제 어댑티브 해시 인덱스를 의도적으로 비활성화하는 경우도 많다. 어댑티브 해시 인덱스가 성능 향상에 크게 도움이 되지 않는 경우는 다음과 같다.

  • 디스크 읽기가 많은 경우
  • 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
  • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

그리고 다음과 같은 경우에는 성능 향상에 많은 도움이 된다.

  • 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
  • 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
  • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

하지만 단순히 어댑티브 해시 인덱스가 도움이 될지 아닐지를 판단하기는 쉽지 않다. 한 가지 확실한 것은 어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀) 내에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 데이터베이스 서버에서는 아무런 도움이 되지 않는다는 점이다. 하나 더 기억해야 할 것은 어댑티브 해시 인덱스는 '공짜 점심'이 아니라는 것이다. 어댑티브 해시 인덱스 또한 저장 공간인 메모리를 사용하며, 때로는 상당히 큰 메모리 공간을 사용할 수도 있다. 어댑티브 해시 인덱스 또한 데이터 페이지의 인덱스 키가 해시 인덱스로 만 들어져야 하고 불필요한 경우 제거돼야 하며, 어댑티브 해시 인덱스가 활성화되면 InnoDB 스토리지 엔진은 그 키 값이 해시 인덱스에 있든 없든 검색해봐야 한다는 것이다. 즉, 해시 인덱스의 효율이 없는 경우에도 InnoDB는 계속 해시 인덱스를 사용할 것이다.

어댑티브 해시 인덱스는 테이블의 삭제 작업에도 많은 영향을 미친다. 어떤 테이블의 인덱스가 어댑티 브 해시 인덱스에 적재돼 있다고 가정해보자. 이때 이 테이블을 삭제(DROP)하거나 변경(ALTER)하려고 하면 InnoDB 스토리지 엔진은 이 테이블이 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거해야 한다. 이로 인해 테이블이 삭제되거나 스키마가 변경되는 동안 상당히 많은 CPU 자원을 사용하고, 그만큼 데이터베이스 서버의 처리 성능이 느려진다. 이후 버전에서는 개선되겠지만 MySQL 8.0.20 버전에서는 다음과 같은 INSTANT 알고리즘의 Online DDL도 상당한 시간이 소요되기도 한다. 

mysql> ALTER TABLE employees ADD address VARCHAR(200), ALGORI THM=INSTANT;

어댑티브 해시 인덱스의 도움을 많이 받을수록 테이블 삭제 또는 변경 작업(Online DDL 포함)은 더 치명적인 작업이 되는 것이다. 이는 어댑티브 해시 인덱스의 사용에 있어서 매우 중요한 부분이므로 꼭 기억해 두자.

어댑티브 해시 인덱스가 우리 서비스 패턴에 맞게 도움이 되는지 아니면 불필요한 오버헤드만 만들고 있는지를 판단해야 하는데, 정확한 판단을 할 수 있는 가장 쉬운 방법은 MySQL 서버의 상태 값들을 살펴보는 것이다. MySQL 서버에서 어댑티브 해시 인덱스는 기본적으로 활성화돼 있기 때문에 특별히 서버 설정을 변경하지 않았다면 이미 어댑티브 해시 인덱스를 사용 중인 상태이며, 아래 상태 값들이 유효한 통계를 가지고 있을 것이다. 어댑티브 해시 인덱스가 비활성화돼 있다면 다음 상태 값 중에서 'hash searches/s 의 값이 0으로 표시될 것이다.

mysql> SHOW ENGINE INNODB STATUS\G 
...
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
...
Hash table size 8747, node heap has 1 buffer(s) 
Hash table size 8747, node heap has 0 buffer(s) 
Hash table size 8747, node heap has 0 buffer(s) 
Hash table size 8747, node heap has 0 buffer(s) 
Hash table size 8747, node heap has 0 buffer(s) 
Hash table size 8747, node heap has 0 buffer(s) 
Hash table size 8747, node heap has 0 buffer(s) 
Hash table size 8747, node heap has 0 buffer(s) 
1.03 hash searches/s, 2.64 non-hash searches/s
...

위의 결과를 보면, 초당 3.67(=2.64 + 1.03)번의 검색이 실행됐는데, 그중 1.03번은 어댑티브 해시 인 덱스를 사용했으며 2.64번은 해시 인덱스를 사용하지 못했다는 것을 알 수 있다. 여기서 searches는 쿼 리의 실행 횟수를 의미하는 것이 아니라 퀴리가 처리되기 위해 내부적으로 키 값의 검색이 및 번 실행됐느 냐를 의미한다. 어댑티브 해시 인덱스의 효율은 검색 횟수가 아니라 두 값의 비율(해시 인덱스 히트율)과 어댑티브 해시 인덱스가 사용 중인 메모리 공간, 그리고 서버의 CPU 사용량을 종합해서 판단해야 한 다. 위 예제에서는 28% 정도가 어댑티브 해시 인덱스를 이용했다는 것을 알 수 있다. 이 서버의 CPU 사용량이 100%에 근접하다면 어댑티브 해시 인덱스는 효율적이라고 볼 수 있다. 그런데 CPU 사용량 은 높지 않은데 28% 정도의 히트율이라면 어댑티브 해시 인덱스를 비활성화하는 편이 더 나을 수도 있 다. 이 경우에는 어댑티브 해시 인덱스가 사용 중인 메모리 사용량이 높다면 어댑티브 해시 인덱스를 비활성화해서 InnoDB 버퍼 풀이 더 많은 메모리를 사용할 수 있게 유도하는 것도 좋은 방법이다. 어 댑티브 해시 인덱스의 메모리 사용량은 다음과 같이 performance_schema를 이용해서 확인 가능하다. 


mysql> SELECT EVENT_NAME, CURRENT_ NUMBER_OF_BYTES_USED 
FROM performance_schema. memory_summary._global_by_event_name 
WHERE EVENT_NAME='memory/innodb/adaptive hash index';

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

지금까지는 MyISAM이 기본 스토리지 엔진으로 사용되는 경우가 많았다. MySQL 5.5부터는 InnoDB 스토리지 엔진이 기본 스토리지 엔진으로 채택됐지만 MySQL 서버의 시스템 테이블(사용자 인증 관련된 정보와 복제 관련된 정보가 저장된 mysql DB의 테이블)은 여전히 MyISAM 테이블을 사용했다. 또한 전문 검색이나 공간 좌표 검색 기능은 MyISAM 테이블에서만 지원됐다. 하지만 MySQL 8.0 으로 업그레이드되면서 MySQL 서버의 모든 시스템 테이블이 InnoDB 스토리지 엔진으로 교체됐고, 공간 좌표 검색이나 전문 검색 기능이 모두 InnoDB 스토리지 엔진을 지원하도록 개선됐다. MySQL 8.0 버전부터는 MySQL 서버의 모든 기능을 InnoDB 스토리지 엔진만으로 구현할 수 있게 된 것이다. InnoDB 스토리지 엔진에 대한 기능이 개선되는 만큼 MyISAM 스토리지 엔진의 기능은 도태되는 상 황이며, 이후 버전에서는 MyISAM 스토리지 엔진은 없어질 것으로 예상한다.

지금도 가끔씩 MyISAM이나 MEMORY 스토리지 엔진에 대한 성능상 장점을 기대하는 사용자들이 있는데, MySQL 5.1과 5.5 버전이라면 의미가 있는 비교겠지만 MySQL 8.0에서는 더이상 무의미한 비 교가 될 것으로 보인다. 이미 MySQL 8.0에서는 MySQL 서버의 모든 기능이 InnoDB 스토리지 엔진 기반으로 재편됐고, MyISAM 스토리지 엔진만이 가지는 장점이 없는 상태다.

때로는 MEMORY 스토리지 엔진이 'MEMORY'라는 이름 때문에 과대평가를 받는 경우가 있지만 MEMORY 스토리지 엔진 또한 동시 처리 성능에 있어서 InnoDB 스토리지 엔진을 따라갈 수 없다. MEMORY 스토리지 엔진은 모든 처리를 메모리에서만 수행하니 빠를 것이라고 예상할 수 있겠지만 하나의 스레드에서만 데이터를 읽고 쓴다면 InnoDB보다 빠를 수 있다. 하지만 MySQL 서버는 일반 적으로 온라인 트랜잭션 처리를 위한 목적으로 사용되며, 온라인 트랜잭션 처리에서는 동시 처리 성 능이 매우 중요하다. 동시에 몇십 또는 몇백 개의 클라이언트에서 쿼리 요청이 실행되는 경우라면 MEMORY 스토리지 엔진은 테이블 수준의 잠금으로 인해 제대로 된 성능을 내지 못할 것이다.

MySQL 서버는 사용자의 쿼리를 처리하기 위해 내부적으로 임시 테이블을 사용할 수도 있다. MySQL 5.7 버전까지만 해도 MEMORY 스토리지 엔진이 내부 임시 테이블의 용도로 사용됐다. 하지만 MEMORY 스토리지 엔진은 가변 길이 타입의 칼럼을 지원하지 않는다는 문제점 때문에 MySQL 8.0 부터는 TempTable 스토리지 엔진이 MEMORY 스토리지 엔진을 대체해 사용되고 있다. MySQL 8.0 에서는 internal_tmp_mem_storage_engine 시스템 변수를 이용해 내부 임시 테이블을 위해 TempTable 엔진을 사용할지 MEMORY 엔진을 사용할지 선택할 수 있다. internal_tmp_mem_storage_engine 시스템 변수의 기본값은 TempTable인데, 이를 MEMORY 스토리지 엔진으로 변경할 수 있다. 하지만 굳 이 MEMORY 스토리지 엔진을 선택해서 얻을 수 있는 장점이 없어졌으며, MEMORY 스토리지 엔진 은 이전 버전과의 호환성 유지 차원일 뿐 향후 버전에서는 제거될 것으로 보인다.

MySQL 로그 파일

MySQL 서버에서 서버의 상태를 진단할 수 있는 많은 도구들이 지원되지만 이러한 기능들은 많은 지식을 필요로 하는 경우가 많다. 하지만 로그 파일을 이용하면 MySQL 서버의 깊은 내부 지식이 없어도 MySQL의 상태나 부하를 일으키는 원인을 쉽게 찾아서 해결할 수 있다. 많은 사용자가 로그 파일의 내용을 무시하고 다른 방법으로 해결책을 찾으려고 노력하곤 하는데, 무엇보다 MySQL 서버에 문제가 생겼을 때는 다음에 설명하는 로그 파일들을 자세히 확인하는 습관을 들일 필요가 있다.

에러 로그 파일

MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일이다. 에러 로그 파일의 위치는 MySQL 설정 파일(my.cnf)에서 log_error라는 이름의 파라미터로 정의된 경로에 생성된다. MySQL 설정 파일에 별도로 정의되지 않은 경우에는 데이터 디렉터리(datadir 파라미터에 설정된 디렉터리)에 .err라는 확장자가 붙은 파일로 생성된다. 여러 가지 메시지가 다양하게 출력되지만 다음 메시지들을 가장 자주 보게 될 것이다.

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

MySQL의 설정 파일을 변경하거나 데이터베이스가 비정상적으로 종료된 이후 다시 시작하는 경우에는 반드시 MySQL 에러 로그 파일을 통해 설정된 변수의 이름이나 값이 명확하게 설정되고 의도한 대로 적용됐는지 확인해야 한다. MySQL 서버가 정상적으로 기동했고('mysqld: ready for connections' 메시지 확인), 새로 변경하거나 추가한 파라미터에 대한 특별한 에러나 경고성 메시지가 없다면 정상적으로 적용된 것으로 판단하면 된다. 그렇지 않고 특정 변수가 무시(ignore)된 경우에는 MySQL 서버는 정상적으로 기동하지만 해당 파라미터는 MySQL에 적용되지 못했음을 의미한다. 그리고 변수명을 인식하지 못하거나 설정된 파라미터 값의 내용을 인식하지 못하는 경우에는 MySQI. 서버가 에러 메시지를 출력하고 시작하지 못했다는 메시지를 보여줄 것이다.

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

InnoDB의 경우에는 MySQL 서버가 비정상적 또는 강제적으로 종료됐다면 다시 시작되면서 완료되지 못한 트랜잭션을 정리하고 디스크에 기록되지 못한 데이터가 있다면 다시 기록하는 재처리 작업을 하게 된다. 이 과정에 대한 간단한 메시지가 출력되는데, 간혹 문제가 있어서 복구되지 못할 때는 해당 에 러 메시지를 출력하고 MySQL은 다시 종료될 것이다. 일반적으로 이 단계에서 발생하는 문제는 상대 적으로 해결하기가 어려운 문제점일 때가 많고, 때로는 innodb_force_recovery 파라미터를 0보다 큰 값으로 설정하고 재시작해야만 MySQL이 시작될 수도 있다.

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

쿼리 도중 발생하는 문제점은 사전 예방이 어려우며, 주기적으로 에러 로그 파일을 검토하는 과정에서 알게 된다. 쿼리의 실행 도중 발생한 에러나 복제에서 문제가 될 만한 쿼리에 대한 경고 메시지가 에러 로그에 기록된다. 그래서 자주 에러 로그 파일을 검토하는 것이 데이터베이스의 숨겨진 문제점을 해결하는 데 많이 도움될 것이다.

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

어떤 데이터베이스 서버의 로그 파일을 보면 이 메시지가 상당히 많이 누적돼 있는 경우가 있다. 클라이언트 애플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우 MySQL 서버의 에러 로그 파일에 이런 내용이 기록된다. 물론 중간에 네트워크에 문제가 있어서 의도하지 않게 접속이 끊어지는 경우에도 이런 메시지가 기록된다. 이런 메시지가 아주 많이 기록된다면 애플리케이션 의 커넥션 종료 로직을 한번 검토해볼 필요가 있다. max_connect_errors 시스템 변숫값이 너무 낮게 설정된 경우 클라이언트 프로그램이 MySQL 서버에 접속하지 못하고 "Host host_name is blocked"라 는 에러가 발생할 수도 있다. 이 메시지는 클라이언트 호스트에서 발생한 에러(커넥션 실패나 강제 연결 종료와 같은)의 횟수가 max_connect_errors 변수의 값을 넘게 되면 발생하는데, 이 경우 max_connect_ errors 시스템 변수의 값을 증가시키면 된다. 하지만 먼저 이 에러가 어떻게 발생하게 됐는지 그 원인을 살펴보는 것이 좋다.

InnoDB의 모니터링 또는 상태 조회 명령(SHOW ENGINE INNODB STATUS 같은)의 결과 메시지

InnoDB의 테이블 모니터링이나 락 모니터링, 또는 InnoDB의 엔진 상태를 조회하는 명령은 상대적으로 큰 메시지를 에러 로그 파일에 기록한다. InnoDB의 모니터링을 활성화 상태로 만들어 두고 그대로 유지하는 경우에는 에러 로그 파일이 매우 커져서 파일 시스템의 공간을 다 사용해 버릴지도 모른다. 모니터링을 사용한 이후에는 다시 비활성화해서 에러 로그 파일이 커지지 않게 만들어야 한다.

MySQL의 종료 메시지

가끔 MySQL이 아무도 모르게 종료돼 있거나 때로는 아무도 모르게 재시작되는 경우를 본 적이 있을 것이다. 이러한 경우 에러 로그 파일에서 MySQL이 마지막으로 종료되면서 출력한 메시지를 확인하는 것이 왜 MySQL 서버가 종료됐는지 확인하는 유일한 방법이다. 만약 누군가가 MySQL 서버를 종료시 켰다면 에러 로그 파일에서 'Received SHUTDOWN from user'이라는 메시지를 확인할 수 있을 것이다. 그렇지 않고 아무런 종료 관련 메시지가 없거나 스택 트레이스(대표적으로 16진수의 주솟값이 잔뜩 출력되는)와 같은 내용이 출력되는 경우에는 MySQL 서버가 세그먼테이션 폴트(Segmentation fault)로 비정상적으로 종료된 것으로 판단할 수 있다. 세그먼테이션 폴트로 종료된 경우에는 스택 트 레이스의 내용을 최대한 참조해서 MySQL의 버그와 연관이 있는지 조사한 후 MySQL의 버전을 업그 레이드하거나 회피책(WorkAround)을 찾는 것이 최적의 방법이다.

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

가끔 MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토해 볼 때가 있는 데, 이때는 쿼리 로그를 활성화해서 쿼리를 퀴리 로그 파일로 기록하게 한 다음, 그 파일을 검토하면 된다. 쿼리 로그 파일에는 다음과 같이 시간 단위로 실행됐던 쿼리의 내용이 모두 기록된다. 슬로우 쿼리 로그와는 조금 다르게 제너럴 퀴리 로그는 실행되기 전에 MySQL이 퀴리 요청을 받으면 바로 기록하 기 때문에 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록된다. 

/Users/matt/App/mysql-8.0.21- -macos10. 15-x86_64/bin/mysqld, Version: 8.0.21 (MySQL Community Server - GPL). started with: 
Tcp port: 3306 Unix socket: /tmp/mysql.: sock 
Time								Id	Command	Argument
2020-07-19T15:27:34.549010+09:00	14	Connect	root@localhost on using Socket
2020-07-19T15:27:34.549197+09:00 	14	Query	select @@version_comment limit 1
2020-07-19T15:27:39.874970+09:00	14	Query 	show databases
2020-07-19T15:27:43.877694+09:00 	14	Query 	SELECT DATABASE()
2020-07-19T15:27:43.878707+09:00 	14	Query 	show databases
2020-07-19T15:27:47.635497+09:00 	14	Query 	show tables
2020-07-19T15:27:53.832132+09:00 	14	Query 	show table

쿼리 로그 파일의 경로는 general_log_file이라는 이름의 파라미터에 설정돼 있다. 또한 퀴리 로그를 파일이 아닌 테이블에 저장하도록 설정할 수 있으므로 이 경우에는 파일이 아닌 테이블을 SQL로 조회해서 검토해야 한다.

mysql> SHOW GLOBAL VARIABLES LIKE 'general_log_file';

퀴리 로그를 파일로 저장할지 테이블로 저장할지는 log_output 파라미터로 결정된다. 제너럴 로그와 관 련된 상세한 내용은 MySQL 매뉴얼의 'log_output 설정 파라미터'와 'The General Query Log' 절 을 참조하자.

슬로우 쿼리 로그

MySQL 서버의 뭐리 튜닝은 크게 서비스가 적용되기 전에 전체적으로 튜닝하는 경우와 서비스 운영 중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝으로 나눌 수 있다. 전자의 경우에는 검토해야 할 대상 쿼리가 전부라서 모두 튜닝하면 되지만, 후자의 경우에는 어떤 쿼리가 문제의 쿼리인지 판단하기가 상당히 어렵다. 이런 경우에 서비스에서 사용되는 쿼리 중에서 어떤 쿼리가 문제인지를 판단하는 데 슬로우 쿼리 로그가 상당히 많은 도움이 된다.

슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간(long_query_time 파라미터는 초단위로 설정하지만 소수점 값으로 설정하면 마이크로 초 단위로 설정 가능함) 이상의 시간이 소요된 쿼리가 모두 기록된다. 슬로우 퀴리 로그는 MySQL이 쿼리를 실행한 후, 실제 소요된 시간을 기준으로 슬로우 쿼리 로그에 기록할지 여부를 판단하기 때문에 반드시 쿼리가 정상적으로 실행이 완료돼야 슬 로우 쿼리 로그에 기록될 수 있다. 즉, 슬로우 퀴리 로그 파일에 기록되는 쿼리는 일단 정상적으로 실행 이 완료됐고 실행하는 데 걸린 시간이 long_query_time에 정의된 시간보다 많이 걸린 쿼리인 것이다.

log_output 옵션을 이용해 슬로우 쿼리 로그를 파일로 기록할지 테이블로 기록할지 선택할 수 있다. log_output 옵션을 TABLE로 설정하면 제너럴 로그나 슬로우 쿼리 로그를 mysql DB의 테이블(general_log와 slow_log 테이블)에 저장하며, FILE로 설정하면 로그의 내용을 디스크의 파일로 저장한다. log_ output 옵션을 TABLE로 설정하더라도 mysql DB의 slow_log 테이블과 general_log 테이블은 CSV 스토리지 엔진을 사용하기 때문에 결국 CSV 파일로 저장하는 것과 동일하게 작동한다.

위와 같이 설정하면 실제 슬로우 쿼리 로그 파일에는 다음과 같은 형태로 내용이 출력된다. MySQL 의 잠금 처리는 MySQL 엔진 레벨과 스토리지 엔진 레벨의 두 가지 레이어로 처리되는데, MyISAM 이나 MEMORY 스토리지 엔진과 같은 경우에는 별도의 스토리지 엔진 레벨의 잠금을 가지지 않지만 InnoDB의 경우 MySQL 엔진 레벨의 잠금과 스토리지 엔진 자체 잠금을 가지고 있다. 이런 이유로 슬 로우 쿼리 로그에 출력되는 내용이 상당히 혼란스러울 수 있다.

# 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 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간만 표현한다. 위 예제의 경우, 이 SELECT 문장을 실행하기 위해 0.002658초간 테이블 락을 기다렸다는 의미가 되는데, 여기서 한 가지 더 중요한 것은 이 값이 0이 아니라고 해서 무조건 잠금 대기가 있었다고 판단하기는 어렵다는 것이다. Lock_time에 표기된 시간은 실제 쿼리가 실행되는 데 필요한 잠금 체크와 같은 코드 실행 부분의 시간까지 모두 포함되기 때문이다. 즉, 이 값이 매우 작은 값이면 무시해도 무방하다.
  • Rows_examined 는 이 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지를 의미하며, Rows_sent는 실제 몇 건의 처리 결과를 클라이언트로 보냈는지를 의미한다. 일반적으로 Rows_examined 의 레코드 건수는 높지만 Rows_sent에 표시된 레코드 건수가 상당히 적다면 이 쿼리는 조금 더 적은 레코드만 접근하도록 튜닝해 볼 가치 가 있는 것이다(GROUP BY나 COUNT(), MIN(), MAX(), AVG() 등과 같은 집합 함수가 아닌 쿼리인 경우만 해당).

MyISAM이나 MEMORY 스토리지 엔진에서는 테이블 단위의 잠금을 사용하고 MVCC와 같은 메커니 즘이 없기 때문에 SELECT 쿼리라고 하더라도 Lock_time이 1초 이상 소요될 가능성이 있다. 하지만 가끔 InnoDB 테이블에 대한 SELECT 쿼리의 경우에도 Lock_time이 상대적으로 큰 값이 발생할 수 있는데, 이 는 InnoDB의 레코드 수준의 잠금이 아닌 MySQL 엔진 레벨에서 설정한 테이블 잠금 때문일 가능성이 높다. 그래서 InnoDB 테이블에만 접근하는 쿼리 문장의 슬로우 쿼리 로그에서는 Lock_time 값은 튜닝 이나 쿼리 분석에 별로 도움이 되지 않는다. 일반적으로 슬로우 쿼리 또는 제너럴 로그 파일의 내용이 상당히 많아서 직접 퀴리를 하나씩 검토하기 에는 시간이 너무 많이 걸리거나 어느 쿼리를 집중적으로 튜닝해야 할지 식별하기가 어려울 수도 있다. 이런 경우에는 Percona에서 개발한 Percona Toolkit 의 pt-query-digest 스크립트를 이용하면 쉽게 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다.

## General Log 파일 분석 linux> pt-query-digest --type= genlog' general . 10g > parsed_general.log

## Slow Log 파일 분석 
linux> pt-query-digest --type= 'slowlog' mysql-slow. 10g > parsed_mysql-slog.log

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

슬로우 쿼리 통계

분석 결과의 최상단에 표시되며, 모든 쿼리를 대상으로 슬로우 퀴리 로그의 실행 시간(Exec time), 그리 고 잠금 대기 시간(Lock time) 등에 대해 평균 및 최소/최대 값을 표시한다.

실행 빈도 및 누적 실행 시간순 랭킹

각 쿼리별로 응답 시간과 실행 횟수를 보여주는데, pt-query-digest 명령 실행 시 --order-by 옵션으로 정렬 순서를 변경할 수 있다. Query ID는 실행된 쿼리 문장을 정규화(쿼리에 사용된 리터럴을 제거)해 서 만들어진 해시 값을 의미하는데, 일반적으로 같은 모양의 쿼리라면 동일한 Query ID를 가지게 된다.

쿼리별 실행 횟수 및 누적 실행 시간 상세 정보

Query ID별 쿼리를 쿼리 랭킹에 표시된 순서대로 자세한 내용을 보여준다. 랭킹별 쿼리에서는 대상 테 이블에 대해 어떤 쿼리인지만을 표시하는데, 실제 상세한 쿼리 내용은 개별 퀴리의 정보를 확인해보면 된다. 여기서는 쿼리가 얼마나 실행됐는지, 쿼리의 응답 시간에 대한 히스토그램 같은 상세한 내용을 보여준다.

Ref

Real MySQL 8.0 1편

profile
초코칩
초코칩처럼 달콤한 코드를 짜자
다음 포스트

트랜잭션과 잠금

0개의 댓글