아키텍처

김민우·2023년 1월 2일
DBMSmysqlsql데이터베이스

MySQL

목록 보기
3/7
post-thumbnail

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진 두 가지로 구분할 수 있다. 이 둘을 간단히 사람에 비유하자면 다음과 같다.

  • MySQL 엔진 : 머리
  • 스토리지 엔진 : 손과 발

대충 어떤 느낌일지 감이 올 것이다. 손과 발의 역할을 하는 스토리지 엔진은 핸들러 API를 만족하면 누구든지 구현이 가능하며 이를 MySQL 서버에 추가해서 사용할 수 있다. 이 두개에 대한 설명은 뒤에서 자세히 살펴보도록 하자.

1. MySQL 엔진 아키텍처

먼저 MySQL의 쿼리를 작성하고 튜닝할 때 필요한 기본적인 MySQL 엔진의 구조를 훑어보자.

MySQL 서버는 다른 DBMS에 비해 구조가 상당히 독특하다. 이를 모르고 사용하는 사용자들 입장에선 차이가 거의 느껴지지 않지만 이러한 독특한 구조 덕분에 다른 DBMS는 가질 수 없는 엄청난 혜택을 누릴 수 있으나 이 때문에 다른 DBMS에서는 문제가 되지 않을 것이 문제가 되기도 하는 trade off가 존재한다.

1.1 MySQL의 전체 구조

위 그림에서 알 수 있듯이 MySQL 엔진과 스토리지 엔진을 합쳐서 MySQL 또는 MySQL 서버라 부른다.

MySQL은 일반 상용 RDBMS와 같이 대부분의 프로그래밍 언어로부터 접근 방법을 모두 지원한다. MySQL 고유의 C API부터 시작해 JDBC나 ODBC 그리고 .NET의 표준 드라이버를 제공하며, 이러한 드라이버를 이용해 C/C++, PHP, 자바, 펄, 파이썬, 루비나 .NET 및 코볼까지 모든 언어로 MySQL 서버에서 쿼리를 사용할 수 있게 지원한다.

1.1.1 MySQL 엔진

MySQL 엔진은 커넥션 핸들러와 SQL 파서 및 전처리기, 옵티마이저가 중심을 이룬다. 이에 대한 설명은 다음과 같다.

  • 커넥션 핸들러 : 클라이언트로부터 접속 및 쿼리 요청을 처리
  • 옵티마이저 : 쿼리의 최적화된 실행을 처리

또한 MySQL은 표준 SQL(ANSI SQL) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되서 실행될 수 있다.

1.1.2 스토리지 엔진

MySQL 엔진은 요청된 SQL 문장을 분석/최적화하는 등 DBMS의 두뇌에 해당하는 일들을 처리하고, 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 담당한다.
스토리지 엔진이라는 이름에서 알 수 있듯 어떤 저장공간에서 읽기 및 쓰기를 담당한다고 생각하면 된다.

MySQL 서버에서 MySQL 엔진은 하나이지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다. 다음 예제와 같이 테이블이 사용할 스토리지 엔진을 저장하면 이후 해당 테이블의 모든 읽기 및 쓰기 작업은 정의된 스토리지 엔진에서 처리한다.

mysql> CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;
  • test_table은 InnoDB 스토리지 엔진을 사용하도록 정의했다.
  • 이제 test_table 에대한 INSERT, UPDATE, DELETE, SELECT 등의 작업이 발생하면 InnoDB 스토리지 엔진이 이를 처리하게 된다.

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

1.1.3 핸들러 API

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

1.2 MySQL 스레딩 구조

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

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

1.2.1 포그라운드 스레드(클라이언트 스레드 또는 사용자 스레드)

포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수 만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다. 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread Cache)로 되돌아간다.

이 때, 이미 스레드 캐시에 일정 개수 이상의 대기중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만이 스레드 캐시에 존재할 수 있도록 한다.

참고
스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정한다.

참고
MySQL에서 사용자 스레드와 포그라운드 스레드는 똑같은 의미로 사용된다. 클라이언트가 MySQL 서버에 접속하게 되면 MySQL 서버는 그 클라이언트의 요청을 처리해 줄 스레드를 생성해 그 클라이언트에게 할당한다. 이 스레드는 DBMS 앞단에서 사용자(클라이언트)와 통신하기 때문에 포그라운드(Foreground) 스레드라고 하며, 사용자가 요청한 작업을 직접 처리하기 때문에 사용자 스레드라 하기도 한다.

1.2.2 백그라운드 스레드

MyISAM의 경우에는 별로 해당 사항이 없는 부분이지만 InnoDB는 다음과 같이 여러 가지 작업이 백그라운드로 처리된다. InnoDB의 경우만 이런 여러가지 작업들이 백그라운드 스레드로 처리된다는 것을 다시 한 번 명심하자. InnoDB의 백그라운드 스레드는 크게 5가지가 있다.

  • 인서트 버퍼(Insert Butter) 병합 스레드
  • 로그 스레드(Log Thread)
  • 쓰기 스레드(Write Thread)
  • 읽기 스레드(Read Thread)
  • 잠금, 데드락 모니터링 스레드

모두 중요한 역할이지만 가장 중요한 것은 로그 스레드와 쓰기 스레드이다. InnoDB에서도 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요는 없지만, 쓰기 스레드는 아주 많은 작업이 백그라운드 스레드로 처리되기 때문에 디스크를 최적으로 사용할 수 있을 만큼 충분히 설정하는 것이 좋다.

읽기 및 쓰기 스레드의 개수는 각각 innodb_read_io_threads, innodb_write_io_threads 시스템 변수로 설정할 수 있다.

사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다.(SELECT Query를 보냈는데 10분 뒤에 결과가 나온다는 것은 말이 안된다.) 그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있으며, InnoDB 또한 이러한 방식으로 처리한다.
하지만, MyISAM은 그렇게 처리하지 않고 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계돼있다.

이러한 이유로 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 버퍼링 덕분에 데이터가 디스크의 데이터 파일로 완전히 저장될 때 까지 기다리지 않아도 된다. 버퍼링은 버퍼에 저장할 데이터를 모아놨다가 한 번에 디스크에 저장하는 방식이므로 매번 디스크에 접근하지 않기 때문이다.
반대로, MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.

1.3 메모리 할당 및 사용 구조

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다. 이 두 영역은 MySQL 서버 내에 존재하는 많은 스레드가 공유하는 공간인지 여부에 따라 구분되며 이들에 대한 특징을 자세히 알아보자.

1.3.1 글로리 메모리 영역

글로리 메모리 영역은 모든 메모리 공간은 MySQL 서버가 시작되면서 OS로부터 할당을 받는다. MySQL의 시스템 변수로 설정해 둔 만큼 OS로 부터 할당 받는다고 알아두면 된다.

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

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

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

1.3.2 로컬 메모리 영역(세션 메모리 영역)

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

로컬 메모리는 각 클라이언트 스레드마다 독립적으로 존재하며 절대 공유되지 않는다는 특징이 있다. 일반적으로 글로벌 메모리 영역의 크기는 주의해서 설정하지만 정렬 버퍼(Sort Buffer)와 같은 로컬 메모리 영역은 크게 신경쓰지 않고 설정하는데, 최악의 경우에는 MySQL 서버가 메모리 부족으로 멈춰버릴 수 있으므로 적절한 메모리 공간을 설정하는 것이 중요하다.

또한, 쿼리의 용도에 따라 공간이 필요하지 않다고 판단되는 경우 MySQL 서버는 메모리 공간을 할당조차도 하지 않을 수 있다. 대표적으로 정렬 버퍼(Sort Buffer)나 조인 버퍼(Join Buffer)와 같은 공간이 있다.

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

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

  • 정렬 버퍼(Sort Buffer)
  • 조인 버퍼
  • 바이너리 로그 캐시
  • 네트워크 버퍼

1.4 플러그인 스토리지 엔진

MySQL의 독특한 구조들 중 대표적인 것이 바로 플러그인 모델이다. 플러그인해서 사용할 수 있는 것이 스토리지 엔진만 있는 것은 아니다.

전문 검색 엔진을 위한 파서(인덱싱할 키워드를 분리해내는 작업)도 플러그인 형태로 개발해서 사용하고, 사용자의 인증을 위한 Native Authentication과 Caching SHA-2 Authentication 등도 모두 플로그인으로 구현되어 제공된다.

MySQL은 기본적으로 많은 스토리지 엔진을 가지고 있으나, 이외에 부가적인 기능을 제공하는 추가 스토리지 엔진이 필요한 경우 사용자가 직접 스토리지 엔진을 개발하는 것도 가능은 하다.

MySQL에서 쿼리가 실행되는 과정을 크게 밑의 그림 4.5와 같이 나눈다면 거의 대부분의 작업이 MySQL 엔진에서 처리되고, 마지막 데이터 읽기/쓰기 작업만 스토리지 엔진에 의해 처리된다. (만약, 사용자가 새로운 용도의 스토리지 엔진을 만든다 하더라도 DBMS의 전체 기능이 아닌 일부분의 기능만 수행하는 엔진이 된다는 의미이다.)

각 처리 영역에서 데이터 읽기/쓰기 작업은 대부분 1건의 레코드 단위(예를 들어, 특정 인덱스의 레코드 1건 읽기 또는 마지막으로 읽은 레코드의 다음 또는 이전 레코드 읽기와 같이)로 처리된다. 그리고 MySQL을 사용하다 보면 핸들러(Handler)라는 단어를 자주 접하게 될 것이다.

핸들러라는 단어는 자동차에 비유해보면 쉽게 이해할 수 있다. 사람이 핸들을 이용해 자동차를 운전하듯이, 프로그래밍 언어에서는 어떤 기능을 호출하기 위해 사용하는 운전대와 같은 역할을 하는 객체를 핸들러(또는 핸들러 객체)라 한다.

MySQL 서버에서 MySQL 엔진은 사람 역할을 하고, 각 스토리지 엔진은 자동차 역할을 한다. 이 때, MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용한다. 잘 이해가 안간다면 최소한 MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야 하나는 점만 기억하자.

MySQL에서 MyISAM이나 InnoDB 같은 다른 스토리지 엔진을 사용하는 테이블에 대해 쿼리를 실행하더라도 MySQL의 처리 내용은 대부분 동일하며, 단순히 (그림 4.5의 마지막 단계) 데이터 읽기/쓰기 영역의 처리만 차이가 있을 뿐이다. 실질적인 GROUP BY나 ORDER BY 등 복잡한 처리는 스토리지 엔진 영역이 아닌 MySQL 엔진의 처리 영역인 쿼리 실행기에서 처리된다.

그렇다면 MyISAM이나 InnoDB 스토리지 엔진 가운데 뭘 사용하든 별 차이가 없다고 생각이 들지만 그렇진 않다. 여기서 설명한 내용은 아주 간략하게 언급한 것이고 실제로 데이터 읽기/쓰기 작업 처리 방식은 매우 다양하게 달라질 수 있다. 여기서 중요한 내용은 하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다는 것이다. 여기서는 각 단위의 작업을 누가 처리하고 MySQL 엔진 영역과 스토리지 엔진 영역의 차이를 설명하는데 목적이 있다.

참고
MySQL 서버에서는 스토리지 엔진뿐만 아니라 다양한 기능을 플러그인 형태로 지원한다. 인증이나 전문 검색 파서 또는 쿼리 재작성 같은 플로그인이 있으며, 비밀번호 검증과 커넥션 제어 등에 관련된 다양한 플러그인이 제공된다. 플러그인에 대한 자세한 정보는 MySQL 메뉴얼을 참고하자.

1.5 컴포넌트

MySQL 8.0 버전부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다. MySQL 서버의 플러그인에는 다음 단점들이 있기 때문이다.

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

MySQL 서버에서 기본으로 제공되는 컴포넌트에 대한 자세한 설명과 컴포넌트 개발과 관련된 자세한 사항은 MySQL 메뉴얼을 참조하자.

1.6 쿼리 실행 구조

  • 쿼리를 실행하는 관점에서 MySQL의 구조를 간략하게 그림으로 표현한 것이다. 기능별로 자세히 알아보자.

1.6.1 쿼리 파서

쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조를 만들어 내는 작업을 의미한다. 이렇게 만들어진 트리를 파서 트리라 하며, 쿼리 문장의 기본 문법오류는 이 과정에서 발견된다.

참고
파서 : 구성성분 단위로 문장(구문)을 분해 후 분석하는 것

1.6.2 전처리기

리턴받은 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지를 확인한다. 각 토큰을 테이블 이름이나 컬럼 이름 또는 내장 함수와 같은 개체를 매핑하여 해당 객체가 실제로 존재하는 지와 접근 권한 등을 확인한다. 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.

1.6.3 옵티마이저

단어 그대로 최적화기 이다. 사용자 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당한다. DBMS의 두뇌 역할을 한다고 볼 수 있다.

1.6.4 실행 엔진

옵티마이저가 두뇌 역할을 한다면 핸들러는 손과 발에 비유할 수 있다. 더 자세히 설명하자면 옵티마이저는 회사의 경영진, 실행 엔진은 중간 관리자, 핸들러는 각 업무의 실무자로 비유할 수 있다. (경영진이 결정하여 중간 관리자한테 요청하면 중간 관리자는 실제 일을할 실무자에게 업무를 주는 방식)

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

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

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

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

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

즉, 핸들러는 결국 스토리지 엔진을 의미하며, MyISAM 테이블을 조작하는 경우에는 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 된다.

1.7 복제

16장에서 자세히 살펴보자.

1.8 쿼리 캐시

MySQL 서버에서 쿼리 캐시(Query Cache)는 빠른 응답을 필요로 하는 웹 기반 응용 프로그램에서 매우 중요한 역할을 담당했다. 대부분의 웹 기반 응용 프로그램 서버에서 성능을 향상시키기 위해 캐시를 사용하는 것 처럼 MySQL 서버에서도 캐시를 사용한다고 생각하면 될 것 같다

쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 리턴하기 때문에 매우 빠른 성능을 보였다. 하지만 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들을 모두 삭제(Invalidate)해야 했다. 이는 심각한 동시 처리 성능 저하를 유발한다. 또한 MySQL 서버가 발전하면서 성능이 개선되는 과정에서 쿼리 캐시는 계속된 동시 처리 성능 저하와 많은 버그의 원인으로 지목됬다.

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

1.9 스레드 풀

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

그렇다고 MySQL 서버에 스레드 풀을 설치하면 드라마틱한 성능 향상이 있는 것은 아니다. 스레드의 수를 적절히 제한해야 하며 그렇지 않으면 오히려 사용하기 전보다 성능이 저하될 수 있다.

물론 적절한 수를 사용하면 CPU의 프로세스 친화도(Processor affinity)도 높이고 OS 입장에서 불필요한 컨텍스트 스위치(Context swtich)를 줄여서 오버헤드도 낮출 수 있다.

참고
컨텍스트 스위치(Context Switch) : 여러 개의 프로세스가 실행되고 있을 때, 기존에 실행되던 프로세스를 중단하고 다른 프로세스를 실행하는 것

Percona Server의 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성하는데, 스레드 그룹의 개수는 thread_pool_size 시스템 변수를 통해 조정할 수 있다. 하지만 일반적으로는 CPU 코어의 개수와 맞추는 것이 CPU 프로세서 친화도를 높이는데 좋다.

Percona Server의 스레드 풀 플러그인은 우선순위 큐(Priority Queue)를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다. 사용자로부터 요청이 유입된 작업 순서를 우선 순위 큐를 사용하여 적절하게 재배치한다. 이를 통해 전체적인 처리 성능을 향상시킬 수 있다.

1.10 트랜잭션 지원 메타데이터(데이터 딕셔너리)

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

MySQL 8.0 버전부터는 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB 테이블에 저장하도록 개선됬다. MySQL 서버가 작동하는데 기본적으로 필요한 테이블들을 묶어서 시스템 테이블이라 하는데, 대표적으로 사용자의 인증과 권한에 관련된 테이블들이 있다.

시스템 테이블과 데이터 딕셔너리 정보를 모두 모아서 mysql DB에 저장하는데, 통째로 mysql.ibd라는 이름의 테이블 공간에 저장된다. 그래서 MySQL 서버의 데이터 디렉터리에 존재하는 mysql.ibd 파일은 다른 *.ibd 파일과 함께 특별히 주의해야 한다.

참고
실제 mysql DB에서 테이블을 조회하면 실제 테이블의 구조가 저장된 테이블은 보이지 않는다. 데이터 딕셔너리 테이블의 데이터를 사용자가 임의로 사용하지 못하게 화면에 보여주지만 않을 뿐 실제로 테이블은 존재한다.

2. InnoDB 스토리지 엔진 아키텍처

MySQL의 스토리지 엔진 가운데 가장 많이 사용되는 엔진은 InnoDB 스토리지 엔진이라 할 수 있다. InnoDB의 특징이라 하면 MySQL에서 사용할 수 있는 스토리지 엔진중 거의 유일하게 레코드 기반의 잠금을 제공하기에 높은 동시성 처리가 가능하며 안정적이면서 성능이 뛰어나다.

그림 4.9는 InnoDB의 아키텍처를 아주 간단히 보여주는데, 각 부분에 대한 자세한 설명은 InnoDB 스토리지 엔진의 주요 특징들과 함께 하나씩 살펴보도록 하자.

2.1 프라미어리 키(PK)에 의한 클러스터링

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

참고
클러스터링 : 유사한 성격을 가진 개체를 묶어 그룹으로 구성하는 것

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

결과적으로 쿼리의 실행 계획에 PK는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정(쿼리의 실행 계획에서 다른 보조 인덱스보다 PK가 선택 될 확률이 높음)된다.

참고
오라클 DBMS의 IOT(Index organized table)와 동일한 구조가 InnoDB에서는 일반적인 테이블 구조가 된다.

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

2.2 외래 키(FK) 지원

FK에 대한 지원은 InnoDB 스토리지 엔진 단계에서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다. FK는 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 자주 있는데, 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다.

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

FK가 복잡하게 얼혀 부모 테이블과 자식 테이블의 관계를 명확히 파악하기 힘들어 순서대로 작업을 못하는 상황이라면 수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업이 실패할 수 있다. 또한 서비스에 문제가 있어서 긴급하게 뭔가 조치를 해야되는 상황에선 더 조급해질 수 있다.

이런 경우에는 foreign_key_checks 시스템 변수를 OFF로 설정하면 FK에 대한 체크 작업을 일시적으로 멈출 수 있다. 이렇게 하면 대략 레코드 적재나 삭제등의 작업도 부가적인 체크가 필요 없기 때문에 훨씬 빠르게 처리할 수 있다.

그러나, FK 체크를 일시적으로 해제했다고 해서 부모, 자식 테이블 간의 관계가 깨진 상태로 유지됨을 의미하진 않는다. 예를 들어, FK 체크를 해제한 상태에서 FK 관계를 가진 부모 테이블의 레코드를 삭제했다면 반드시 자식 테이블의 레코드도 삭제하여 일관성성을 맞춰준 후 FK 체크 기능을 활성화 해야한다. 또한, FK 체크가 일시적으로 해제되면 FK 관계의 부모 테이블에 대한 작업(ON DELETE/UPDATE CASCADE)도 무시하게 된다.

참고
foreign_key_checks 시스템 변수는 적용 범위를 GLOBAL, SESSION 모두 설정 가능한 변수이다. 그래서 이런 작업을 할 때는 반드시 현재 작업을 실행하는 세션에서만 FK 체크 기능을 멈추게 해야한다. 물론, SESSION 키워드를 명시하지 않으면 자동으로 현재 세션의 설정만 변경한다.

그리고, 작업이 완료되면 반드시 현재 세션을 종료하거나 현재 세션의 FK 체크기능을 다시 활성화해야한다.

2.3 MVCC(Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는데 있다. 잠금을 사용하지 않으면 다른 트랜잭션이 가지고 있는 잠금을 기다릴 필요가 없어진다는 큰 장점이 있다. InnoDB는 언두 로그(Undo log)를 이용해 이 기능을 구현한다.

여기서 멀티 버전이라 함은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미이다. 이해를 위해 격리 수준(isolation level)이 READ_COMMITTED인 MySQL 서버에서 InnoDB 스토리지 엔진을 사용하는 테이블의 데이터 변경을 어떻게 처리하는지 그림으로 살펴보자.

우선 다음과 같은 테이블에 한 건의 레코드를 INSERT한 다음 UPDATE해서 발생하는 변경 작업 및 절차를 확인해보자.

mysql> 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)
       );

mysql> INSERT INTO member (m_id, m_name, m_area)
		VALUES (12, '홍길동', '서울');
mysql> COMMIT;

INSERT 문이 실행되면 데이터베이스의 상태는 그림 4.10과 같은 상태로 바뀐다.

그림 4.11은 MEMBER 테이블에 UPDATE 문장이 실행될 때의 처리 절차를 그림으로 보여준다.

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

UPDATE 문장이 실행되면 COMMIT 실행 여부와 관계없이 InnoDB의 버퍼 풀은 새로운 값인 '경기'로 업데이트된다. 그리고 디스크의 데이터 파일에는 체크포인트나 InnoDB의 Write 스레드에 의해 새로운 값으로 업데이트될 수 도 있고 아닐 수도 있다.(InnoDB가 ACID를 보장하기 때문에 일반적으로는 InnoDB의 버퍼 풀과 데이터 파일은 동일하 상태라 봐도 무방하다.)

참고
ACID : 철자 순서대로 원자성, 일관성, 고립성, 지속성를 의미하며 데이터베이스 트랜잭션이 안전하게 수행된다는 것을 보장하기 위한 성질

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

mysql> SELECT * FROM member WHERE m_id=12;

이 질문의 답은 MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준(Isolation level)에 따라 다르다는 것이다. 격리 수준 값과 역할은 다음과 같다.

  • READ_UNCOMMIT
    • InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다.
    • 즉, 데이터의 커밋여부에 상관없이 변경된 데이터를 읽어서 반환한다.
  • READ_COMMIT, REPEATABLE_READ, SERIALIZABLE
    • 아직 커밋이 되지 않은 경우에는 내용이 변경되기 이전의 내용을 보관하고 있는 언두(Undo) 영역의 데이터를 반환한다.

즉, 하나의 레코드(회원번호가 12인 레코드)에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조다. 여기서는 한 개의 데이터만 가지고 설명했지만 관리해야 하는 예전 버전의 데이터는 무한히 많아질 수 있다.

트랜잭션이 길어지면 언두 영역에서 관리하는 예전 데이터가 삭제되지 못하고 오랫동안 관리되야 하며, 자연스럽게 언두 영역이 저장되는 시스템 테이블 공간이 많이 늘어나는 상황이 발생할 수 있다.

지금까지 UPDATE 쿼리가 실행되면 InnoDB 버퍼 풀은 즉시 새로운 데이터로 변경되고 기존 데이터는 언두 영역으로 복사되는 과정을 살펴봤는데, 이 상태에서 COMMIT 명령을 실행하면 InnoDB는 더 이상의 변경 작업 없이 지금의 상태를 영구적인 데이터로 만들어버린다.
하지만, ROLLBACK을 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제해버린다.

COMMIT이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는 것은 아니다. 이 언두 영역을 필요로 하는 트랜잭션이 더 이상 없는 경우 삭제 된다. 즉, ROLLBACK을 언두 영역을 통해 한다.

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

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

그림 4.12에서 특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았다 하더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다.
InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두 로그(Undo log)를 사용한다.

  • 그림 4.12 변경중인(COMMIT 전) 레코드 읽기

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

2.5 자동 데드락 감지

시작하기 앞서 데드락이 무엇인지에 대해 간단히 알아보고 가자.

데드락이란 두 개 이상의 프로세스나 스레드가 서로 자원을 얻지 못해서 다음 처리를 하지 못하는 상태 즉, 무한히 다음 자원을 기다리게 되는 상태를 말한다. 주로, 시스템적으로 한정된 자원을 여러 곳에서 사용하려고 할 때 발생한다.

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

참고
교착상태 : 두 개 이상의 작업들이 서로 상대방의 작업이 끝나기만을 기다리고 있어 결과적으로 아무것도 못하는 상태

InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랙잭션을 찾아서 그 중 하나를 강제 종료시킨다. 이 때, 어느 트랜잭션을 먼저 강제종료할 것인지를 판단하는 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜 잭션이 높은 우선순위를 가진다.
트랜잭션이 언두 로그 레코드를 적게 가졌다는 이야기는 롤백을 해도 언두 처리에 관한 비용이 더 적다는 것이며, 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문이다. 어찌보면 이는 비용과 부작용을 고려했을 때 당연하다.

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

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

이렇게 데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업을 진행하지 못하고 대기하면서 서비스에 악영향을 미치게 된다. 동시 처리 스레드가 매우 많은 경우 데드락 감지 스레드는 더 많은 CPU 자원을 소모할 수 있다.

이런 문제점을 해결하기 위해 MySQL 서버는 innodb_deadlock_detect 시스템 변수를 제공하며, 이 값을 OFF로 설정하면 데드락 감지 스레드는 더는 작동하지 않게 된다.
데드락 감지 스레드가 작동하지 않으면 InnoDB 스토리지 엔진 내부에서 2개 이상의 트랜잭션이 상대방이 가진 잠금을 요구하는 상황(데드락 상황)이 발생해도 누군가가 중재를 하지 않기 때문에 무한정 대기하게 될 것이다.

하지만 innodb_lock_wait_timeout 시스템 변수를 활성화하면 이런 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환하게 된다. innodb_lock_wait_timeout은 초 단위로 설정할 수 있으며 잠금을 설정한 시간 동안 획득하지 못하면 쿼리는 실패하고 에러를 반환한다.

데드락 감지 스레드가 부담되어 innodb_deadlock_detect를 OFF로 설정해서 비활성화하는 경우라면 innodb_lock_wait_timeout을 기본값인 50초보다 훨씬 낮은 시간으로 변경해서 사용할 것을 권장한다.

2.6 자동화된 장애 복구

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 값이 커질수록 그만큼 심각한 상황이어서 데이터 손실 가능성이 커지고 복구 가능성은 작아진다.

일단 MySQL 서버가 기동되고 InnoDB 테이블이 인식된다면 mysqldump를 이용해 데이터를 가능한 만큼 백업하고 그 데이터로 MySQL 서버의 DB와 테이블을 다시 생성하는 것이 좋다. InnoDB의 복구를 위해 innodb_force_recovery 옵션에 설정 가능한 값은 1부터 6까지 인데, 각 숫자값에 대한 설명은 직접 검색하여 참고해보자.

참고
innodb_force_recovery가 0이 아닌 복구 모드에서는 SELECT 이외에 INSERT, UPDATE, DELETE 같은 쿼리는 수행할 수 없다.

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

백업은 있지만 복제의 바이너리 로그가 없거나 손실됬다면 마지막 백업 시점까지만 복구할 수 있다. 더 자세한 내용은 MySQL 메뉴얼의 innodb_force_recovery 시스템 변수의 내용을 참조한다.

2.7 InnoDB 버퍼 풀

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

일반적으로 어플리케이션에는 INSERT, UPDATE, DELETE 처럼 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시킨다. 하지만 버퍼 풀이 이러한 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업 횟수를 줄일 수 있다. (이는 버퍼링)

2.7.1 버퍼 풀의 크기 설정

일반적으로 전체 물리 메모리의 80% 정도를 InnoDB의 버퍼 풀로 설정하라는 말이 있는데, 이는 그렇게 단순하게 설정해서 되는 값이 아니다. 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정해야 한다. MySQL 서버 내에서 메모리를 필요로 하는 부분은 크게 없지만 아주 독특한 경우 레코드 버퍼가 상당한 메모리를 사용하기도 한다.

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

커넥션이 많고 사용하는 테이블이 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 많이 요구된다.

MySQL 서버가 사용하는 레코드 버퍼 공간은 별도로 설정할 수 없으며, 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블의 개수에 따라 결정된다. 또한, 이 버퍼 공간은 동적으로 해제되기도 하므로 정확히 필요한 체크 공간의 크기를 계산할 수 없다.

다행히 MySQL 5.7 버전부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 됬다. 그래서 가능하면 InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 봐가면서 증가시키는 방법이 최적이다.
일반적으로 회사에서 이미 MySQL 서버를 사용하고 있다면 그 서버의 메모리 설정을 기준으로 InnoDB 버퍼 풀의 크기를 조절하면 된다.

메모리 크기에 따른 대략적인 InnoDB 버퍼 풀의 크기는 다음과 같다.

  • 8GB 미만
    • 50% 정도만 InnoDB 버퍼 풀로 할당
    • 나머지 메모리 공간은 MySQL 서버와 운영체제, 그리고 다른 프로그램이 사용할 수 있는 공간으로 사용
  • 8GB 이상 50GB 미만
    • 50% 정도로 할당 후 점차 동적으로 늘려가면서 최적점을 찾는다.
  • 50GB 이상
    • 대략 15GB ~ 30GB 정도를 제외한 나머지를 InnoDB 버퍼 풀로 할당

InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 조절할 수 있으며, 동적으로 버퍼 풀의 크기를 확장할 수 있다. 하지만 버퍼 풀 크기 조절은 영향력이 큰 변경이므로 가능하면 서버가 한가한 시점에 변경을 하는 것이 좋다.

또한, InnoDB 버퍼풀을 더 크게 변경하는 작업은 시스템 영향도가 크지 않지만 크기를 줄이는 작업은 서비스 영향도가 매우 크므로 가능하면 버퍼 풀을 줄이는 작업을 하지 않도록 주의하자.

InnoDB 버퍼 풀은 여러 개로 쪼개어 관리할 수 있다. innodb_buffer_pool_instances 시스템 변수를 이용해 버퍼풀을 여러 개로 분리해서 관리할 수 있는데, 각 버퍼 풀을 버퍼 풀 인스턴스라 한다. 버퍼 풀을 여러 개의 페이지 조각으로 구성된다고 알아두자.

2.7.2 버퍼 풀의 구조

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

참고
페이지 크기는 innodb_page_size 시스템 변수로 설정이 가능하다.

버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 크기 LRU(Least Recently Used) 리스트와 플러시(Flush) 리스트 그리고 프리(Free) 리스트라는 3개의 자료 구조를 사용한다. 하나씩 살펴보자.

참고
LRU(Least Recently Used) : 가장 오랫동안 사용되지 않은 페이지를 제거한다는 뜻으로, 가장 오래 전 사용된 것이 재사용될 가능성이 제일 적다는 것에 기반하여 페이지를 관리하는 방식을 의미

Free 리스트
Free라는 단어 그대로 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지의 목록이다. 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다.

LRU 리스트

LRU는 아래 그림과 같은 구조를 띄고 있는데, 엄밀하게 LRU와 MRU(Most Recently Used) 리스트가 결합된 형태라고 보면 된다. 아래 그림에서 'Old 서브 리스트' 영역은 LRU에 해당하며 'New 서브리스트' 영역은 MRU 정도로 이해하면 된다.

참고
MRU(Most Recently Used) : 가장 최근에 사용한 페이지를 저장해두는 기능

  • 그림 4.13 버퍼 풀 관리를 위한 LRU 구조

LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것이다. InnoDB 스토리지 엔진에서 데이터를 찾는 과정은 대략 다음과 같다.

  1. 필요한 레코드가 저장된 데이터 페이지가 버퍼풀에 있는지 검사
    A. InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색
    B. 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색
    C. 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
  2. 디스트에 필요한 데이터 페이지를 버퍼 풀에 적재하고 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
  3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동(Read Ahead와 같이 대량 읽기의 경우 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만 실제 쿼리에서 사용되지는 않을 수도 있으며, 이런 경우네는 MRU로 이동되지 않음)
  4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이(Age)가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지에 부여된 나이가 오래되고(Aging) 결국 해당 페이지는 버퍼 풀에서 제거된다. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨진다.
  5. 필요한 데이터가 자주 접근됬다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

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

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

디스크에서 읽은 상태로 그대로 전혀 변경이 없다면 플러시 리스트에 관리되지 않지만, 일단 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록되어야 한다.

데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다. 그래서 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결된다. 하지만 리두 로그가 디스크로 기록되었다고 해서 데이터 페이지가 디스크로 기록되었다는 것을 항상 보장하진 않는다. 때로는 그 반대의 경우도 발생할 수 있다.

2.7.3 버퍼 풀과 리두 로그

InnoDB의 버퍼 풀과 리두 로그는 매우 밀접한 관계를 맺고 있다. 이미 디스크의 모든 데이터 파일이 버퍼 풀에 적재될 정도의 버퍼 풀 공간이 아니라면 InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 그게 설정하면 할수록 쿼리의 성능이 빨라진다.

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

InnoDB 버퍼풀의 쓰기 버퍼링 기능까지 향상시키려면 InnoDB 버퍼 풀과 리두 로그와의 관계를 먼저 이해해야 한다.

  • 그림 4.14 InnoDB 버퍼 풀과 리두 로그의 관계

InnoDB의 버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지(Clean Page)와 INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 더티 페이지(Dirty Page)도 가지고 있다.

더티 페이지는 디스크와 메모리(버퍼 풀)의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록되야 하기에 버퍼 풀에 무한정으로 머물 수 없다. InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용한다.

즉, 데이터 변경이 발생하면 리두 로그 파일에 기록되있는 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 덮어 쓰인다. 그래서 InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능 공간과 당상 재사용 가능한 공간을 구분해서 관리해야 한다.
재사용 불가능한 공간을 활성 리두 로그(Active Redo Log)라고 하며 그림 4.14에서 화살표를 가진 엔트리들이 활성 리두 로그 공간이다.

2.7.4 버퍼 풀 플러시(Buffer Pool Flush)

예전에는 더티 페이지를 디스크에 동기화 하는 과정(Dirty Page Flush)에서 디스크 쓰기 폭증 현상이 발생했으나 지금은 그러지 않는다. 여기서 InnoDB 스토리지 엔진의 더티 페이지의 디스크 쓰기 동기화와 관련된 시스템 설정을 살펴보자.

참고
특별히 서비스를 운영할 때 성능 이슈가 발생하지 않는다면 굳이 지금부터 설명하는 시스템 변수를 건드릴 필요가 없다.

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

  • 플러시 리스트(Flush_list) 플러시
  • LRU 리스트(LRU_list) 플러시

하나씩 살펴보자.

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

이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트(Flush_list) 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행한다. 이를 위해 InnoDB 스토리지 엔진은 여러 시스템 변수들을 제공하는데 이는 다음과 같다.

  • innodb_page_cleaners
    • InnoDB 스토리지 엔진에서 더티 페이지를 디스크로 동기화하는 스레드인 클리너 스레드(Cleaner Thread)의 개수를 조정
    • 버퍼 풀 인스턴스 개수(innodb_buffer_pool_instances)를 넘을 수 없다. 만약, 이 값보다 크다면 자동으로 이 값으로 값이 설정된다.
  • innodb_max_dirty_pages_pct
    • 더티 페이지의 비율을 조정
    • 더티 페이지를 많이 가지고 있을수록 디스크 쓰기 작업을 버퍼링함으로써 여러 번의 디스크 쓰기를 한 번으로 줄이는 효과를 극대화할 수 있기에 디폴트값으로 유지하는 것을 권장
  • innodb_max_dirty_pages_pct_lwm
    • 더티 페이지가 innodb_max_dirty_pages_pct 비율에 도달하지 않도록 미리미리 플러시 하도록 하는 최소한의 더티 페이지 비율을 의미
    • 특정 상황에서 이 값을 조금 더 높은 값으로 저장하면 디스크 쓰기 횟수를 줄이는 효과를 얻을 수 있다.
  • innodb_io_capacity
    • InnoDB 스토리지 엔진은 이 값을 기준으로 더티 페이지 쓰기를 실행한다.
    • 일반적인 상황에서 사용되거나 사용할 수 있는 I/O의 한계를 의미한다.
  • innodb_io_capacity_max
    • 비상 상황에서 사용되거나 사용할 수 있는 I/O의 한계를 의미한다
    • 이로써 비상 상황에서도 I/O를 모두 소비하지 않도록 제한을 둘 수 있다.
  • innodb_adaptive_flushing
    • 디폴트는 어댑티브 플러시를 사용
    • 어댑티브 플러시 기능이 활성화되면 innodb_io_capacity, innodb_io_capacity_max를 사용하지 않는 새로운 알고리즘을 사용한다.
    • 이를 활성화하면 일일히 서버의 트래픽을 봐가면서 innodb_io_capacity, innodb_io_capacity_max를 설정하는 번거로운 일을 해결해준다.
  • innodb_adaptive_flushing_lwm
    • 전체 리두 로그 공간에서 활성 리두 로그의 공간이 이 값 미만이면 어댑티브 플러시가 작동하지 않고 이 값을 넘어서만 어댑티브 플러시가 작동한다.
    • 디폴트는 10%이다.
  • innodb_flush_negihbors
    • 활성화 시 더티 페이지를 디스크에 기록할 때 디스크에서 근접한 페이지들 중 더티 페이지가 있으면 InnoDB 스토리지 엔진이 함께 묶어서 디스크로 기록하게 해주는 기능
    • 디폴트인 비활성으로 유지하는 것을 권장

LRU 리스트 플러시
InnoDB 스토리지 엔진은 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 때 LRU 리스트(LRU_list) 플러시 함수를 사용한다.

이 때, InnoDB 스토리지 엔진은 LRU 리스트의 끝 부분부터 innodb_lru_scan_depth 에 설정된 값 만큼의 페이지들을 스캔한다.

스캔하면 InnoDB 스토리지 엔진은 더티 페이지는 디스크에 동기화하며 클린 페이지는 즉시 프리(Free) 리스트로 옮긴다.

2.7.5 버퍼 풀 상태 백업 및 복구

InnoDB 서버의 버퍼 풀은 쿼리의 성능에 매우 밀접하게 연결되있다. 쿼리 요청이 매우 빈번한 서버를 셧다운 후 다시 시작하여 서비스를 시작하면 쿼리 처리 성능이 평상시보다 1/10도 안되는 경우가 대부분이다. 버퍼 풀에 아무것도 없으면 데이터를 읽거나 쓸 때 디스크에 접근해야 하기 때문이다.

디스크의 데이터가 버퍼 풀에 적재되있는 상태를 워밍업(Warming Up)이라 하는데, 버퍼 풀이 잘 워밍업 된 상태에서는 워밍업이 안 된 상태에 비해 성능이 몇 십배 우수하다.

여러가지 불가피한 이유들 때문에 MySQL 서버를 재시작해야 하는 경우 셧다운 전에 다음과 같이 innodb_buffer_pool_dump_now 시스템 변수를 이용해 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있다.
그리고 서버를 재시작시 innodb_buffer_pool_load_now 시스템 변수를 이용해 백업된 버퍼 풀의 상태를 다시 복구할 수 있다.

<MySQL 서버 셧다운 전에 버퍼 풀의 상태 백업>
mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
<MySQL 서버 재시작 후, 백업된 버퍼 풀의 상태 복구>
mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;

실제 백업 데이터는 데이터 디렉토리에 ib_buffer_pool라는 이름으로 저장이 되는데, 파일의 크기를 살펴보면 버퍼 풀의 크기에 비해 상대적으로 매우 작은 것을 확인할 수 있다. 그 이유는 InnoDB 스토리지 엔진이 버퍼 풀의 LRU 리스트에서 적재된 데이터 페이지의 메타 정보만을 저장하기 때문이다.

이 때문에, 백업을 하는데는 시간이 상대적으로 적게 소모되지만 백업된 데이터를 불러오는데는 오랜 시간이 걸릴 수 있다.

만약, 버퍼 풀 적재 작업이 너무 오랜 시간걸려 중간에 멈추고자 한다면 어떻게 해야할까? innodb_buffer_pool_loat_abort 시스템 변수를 이용하면 된다. 버퍼 풀을 다시 복구하는 작업은 상당히 많은 디스크 읽기를 필요로 하기 때문에 복구가 진행 중인 상황에서 서비스를 재개하는 것은 좋지 않다. 따라서, 복구 중 불가피하게 서비스를 재개해야 하는 경우 다음과 같이 이를 활용할 수 있다.

SET GLOBAL innodb_buffer_pool_loat_abort`=ON;

지금까지 수동으로 InnoDB 버퍼 풀의 백업과 복구를 살펴봤는데 사실 이 작업은 InnoDB 스토리지 엔진이 자동으로 해주기 때문에 수동으로 할 필요는 없다.
만약, 자동화를 하려면 innodb_buffer_pool_dump_at_shutdown과 innodb_buffer_pool_load_at_startup 설정을 MySQL 서버의 설정 파일에 넣어두면 된다.

2.7.6 버퍼 풀의 적재 내용 확인

information_schema 데이터베이스의 innodb_cached_index 테이블을 조회하면 테이블의 인덱스별 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재되있는지 확인할 수 있다.

하지만, 아직 MySQL 서버는 개별 인덱스별 전체 페이지 개수가 몇 개인지는 사용자에게 알려주지 않기에 information_schema 테이블을 이용해도 인덱스별 페이지가 InnoDB 버퍼 풀에 적재된 비율을 확인할 수는 없다.

2.8 Double Write Buffer

InnoDB 스토리지 엔진의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다.
이로 인해 InnoDB의 스토리지 엔진에서 더티 페이지를 디스크 파일로 플러시할 때 일부분만 기록되는 문제가 발생하면 그 페이지의 내용을 복구하지 못할 가능성이 있다.

이렇게 페이지가 일부만 기록되는 현상을 파셜 페이지(Partial-page) 또는 톤 페이지(Torn-page)라고 하는데, 이런 현상은 하드웨어의 오작동이나 시스템의 비정상 종료 등으로 발생할 수 있다.

InnoDB 스토리지 엔진에서는 이 같은 문제를 막기 위해 Double-Write 기법을 이용한다. 아래 그림은 InnoDB의 Double-Write 기법이 작동하는 방식을 표현한 것이다. 아래 그림과 같이 'A' ~ 'E'까지의 더티 페이지를 디스크로 플러시한다고 가정해보자.

  • 그림 4.15 Double Write 작동 방식

이 때, InnoDb 스토리지 엔진은 실제 데이터 파일에 변경 내용을 기록하기 전에 'A' ~ 'E'까지의 더티 페이지를 우선 묶어서 한 번의 디스크 쓰기로 시스템 테이블 스페이스의 DoubleWrite 버퍼에 기록한다.
그리고 InnoDB 스토리지 엔진은 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기를 실행한다.

이렇게 시스템 테이블 스페이스에 DoubleWrite 버퍼 공간에 기록된 변경 내용은 실제 데이터 파일에 'A' ~ 'E' 더티 페이지가 정상적으로 기록되면 더 이상 필요가 없어진다. 즉, DoubleWrite 버퍼는 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래의 목적으로 사용된다.

'A'와 'B' 페이지는 정상적으로 기록되었으나 'C' 페이지가 기록되는 도중에 OS가 비정상적으로 종료되었다고 가정해보자. 그러면 InnoDB 스토리지 엔진은 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다.

이러한 DoubleWrite 기능을 사용할지 여부는 innodb_doublewrite 시스템 변수로 제어할 수 있다.

DoubleWrite 버퍼는 데이터의 안정성을 위해 자주 사용되는데, 만약 데이터의 무결성이 중요한 서비스에서는 DoubleWrite의 활성화를 고려하는 것이 좋으나, 데이터베이스의 성능을 위해 InnoDB 리두 로그 동기화 설정(innodb_flush_log_at_trx_commit)을 1이 아닌 값으로 설정했다면 DoubleWrite도 비활성화 하는 것이 좋다.

참고
데이터 무결성(Data integrity) : 컴퓨팅 분야에서 완전한 수명 주기를 거치며 데이터의 정확성과 일관성을 유지하고 보증하는 것을 가리키며, 데이터베이스나 RDBMS 시스템의 중요한 기능이다.

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

2.9 언두 로그

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

트랜잭션, 격리 수준 보장이라는 말이 잘 이해가 안가는데 간단히 살펴보자.

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

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

2.9.1 언두 로그 모니터링

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

mysql> UPDATE member SET name='홍길동' WHERE member_id=1;

위 문장이 실행되면 트랜잭션을 커밋하지 않아도 실제 데이터에 내용이 반영된다. 만약, 변경되기 전의 값이 '벽계수'였다면 언두 영역에는 '벽계수' 라는 값이 백업되는 것이다.

이 상태에서 사용자가 커밋을 하면 현재 상태가 그대로 유지되고, 롤백을 하면 언두 영역에 저장된 데이터를 다시 복구한다.

언두 로그의 데이터는 크게 다음 두 가지 용도로 사용이 된다.

  • 트랜잭션의 롤백 대비용
  • 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공

참고
트랜잭션의 격리 수준(isolation level of transaction) : 동시에 여러 트랜잭션이 데이터를 변경하거나 조회할 때 한 트랜잭션의 작업 내용이 다른 트랙잭션에 어떻게 보일지를 결정하는 기준

그러면 언두 로그의 문제점이 어떻게 생기는지 예시를 통해 알아보자.

만약 1억 건의 레코드가 저장된 100GB 테이블을 DELETE 로 삭제한다고 생각해보자. 그러면 MySQL 서버는 이 테이블의 레코드를 한 건 삭제하고 언두로그에 데이터를 저장한다. 이렇게 1억건의 레코드가 테이블에서는 삭제되지만 언두 로그에 복사가 되야한다. 즉, 테이블의 크기만큼 언두 로그의 공간 사용량이 늘어난다.

이외에 대용량의 데이터를 처리하는 트랜잭션뿐만 아니라 트랜잭션이 오랜 시간 실행될 때도 언두로그의 양이 급격히 증가할 수 있다. 트랜잭션이 완료됬다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것이 아니다. 아래 그림을 보자.

  • 그림 4.16 장시간 활성화된 트랜잭션

그림 4.16은 3개의 트랜잭션이 서로 시작과 종료 시점이 다르게 실행되는 상태이다. B, C 트랜잭션은 완료되었지만 가장 먼저 시작된 A 트랜잭션은 종료되지 않은 상태이다. 이 때, 트랜잭션 B, C는 각각 UPDATE, DELETE를 실행했으므로 변경 이전의 데이터를 언두 로그에 백업했을 것이다. 하지만 먼저 시작된 A 트랜잭션이 아직 활성 상태이기 때문에 B, C 트랜잭션의 완료 여부와 관계없이 B, C 트랜잭션이 만들어낸 언두 로그는 삭제되지 않는다.

일반적으로 응용 프로그램에서 트랜잭션 관리가 잘못되는 경우 이런 현상이 발생할 수 있지만 보통 사용자의 실수로 인해 더 자주 문제가 되곤 한다.

서비스용으로 사용되는 MySQL 서버에서 사용자가 트랜잭션을 시작한 상태에서 완료하지 않고 하루 정도 방치했다고 생각해보자. 그러면 InnoDB 스토리지 엔진은 이 트랜잭션이 시작된 시점부터 생성된 언두 로그를 계속 보존해야 한다.

이렇게 누적된 언두 로그로 인해 디스크의 사용량이 증가하는 것은 상대적으로 큰 문제가 아니다. 하지만 그동안 빈번하게 변경되는 레코드를 조회하는 쿼리가 자주 실행되면 InnoDB 스토리지 엔진은 언두 로그의 이력을 필요한 만큼 스캔해야만 필요한 레코드를 찾을 수 있기 때문에 쿼리의 성능이 급격히 저하된다.

다행히도 MySQL 버전이 올라가면서 언두 로그 공간의 문제점은 완전히 해결됬다. MySQL 8.0 부터는 언두 로그를 돌아가면서 순차적으로 사용하는 방식으로 디스크 공간을 줄이는 것을 가능하게 했으며, 때로는 MySQL 서버가 필요한 시점에 사용 공간을 자동으로 줄여주기도 한다.

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

mysql> SHOW ENGINE INNODB STATUS \G

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

주의
MySQL 서버에서 INSERT 문장으로 인한 언두 로그와 UPDATE(DELETE 포함) 문장으로 인한 언두 로그는 별도로 관리된다. 후자는 MVCC와 데이터 복구(롤백 포함)에 모두 사용되지만 전자는 MVCC에는 사용되지 않는다.

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

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

  • 그림 4.17 언두 테이블스페이스 구조

하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 수 만큼의 언두 슬롯을 가진다. 하나의 트랜잭션을 필요로 하는 언두 슬롯의 개수는 트랜잭션이 실행하는 INSERT, UPDATE, DELETE 문장의 특성에 따라 최대 4개의 언두 슬롯을 사용하게 된다.

일반적으로 트랜잭션이 임시 테이블을 사용하지 않으므로 하나의 트랜잭션은 대략 2개 정도의 언두 슬롯을 필요로 한다고 가정된다.

최대 동시 트랜잭션 수는 가장 일반적인 설정인 16KB InnoDB에서 기본 설정을 사용한다고 하면 대략 131072(= 16 * 1024 / 16 * 128 * 2 / 2)정도인다. 일반적인 서비스에서 이정도로 동시 트랜잭션이 필요하진 않겠지만 이렇게 둬도 크게 문제될 것은 없으므로 가능하면 디폴트값을 유지하자.

언두 로그 공간이 남는 것은 크게 문제되지 않지만 언두 로그 공간이 남는 것은 크게 문제되지 않지만 언두 로그 슬롯이 부족한 경우에는 트랜잭션을 시작할 수 없는 심각한 문제가 발생한다. 따라서, 언두 로그 관련 시스템 변수를 변경해야 한다면 적절히 필요한 동시 트랜잭션 개수에 맞게 언두 테이블 스페이스와 롤백 세그먼트의 개수를 설정해야 한다.

MySQL 8.0부터는 CREATE UNDO TABLESPACE나 DROP TABLESPACE 같은 명령으로 새로운 언두 테이블스페이스를 동적으로 추가하고 삭제할 수 있게 개선되었다.

언두 테이블스페이스 공간을 필요로 한 만큼 남기고 불필요하거나 과도하게 할당된 공간을 OS로 반납하는 것을 'Undo tablespace truncate'라 하는데 이는 자동과 수동적 방법이 있다. 두 가지 방법 모두 MySQL 8.0 부터 지원된다.

2.10 체인지 버퍼

RDBMS에서 레코드가 INSERT 되거나 UPDATE 될 때는 데이터 파일을 변경하는 작업뿐만 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업 또한 필요하다. 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하기에 테이블에 인덱스가 많다면 이는 상당히 많은 자원을 소모하게 된다.

그래서 InnoDB는 변경해야 하는 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 디스크에서 읽어와야 한다면 즉시 실행하지 않고 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태를 통해 성능을 향상 시키는데, 이 때 사용하는 임시 메모리를 체인지 버퍼(Change Buffer)라 한다.

사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스의 경우는 체인지 버퍼를 사용할 수 없다. 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드(Merge Thread)라 한다.

이러한 체인지 버퍼는 innodb_change_buffering이라는 시스템 변수를 통해 활성화여부를 결정할 수 있어 체인지 버퍼 사용이 비효율적이라 판단될 때는 이를 사용하지 않도록 할 수 있다. 이 시스템 변수에서 설정할 수 있는 값은 다음과 같다.

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

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

2.11 리두 로그 및 로그 버퍼

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

MySQL 서버를 비롯한 대부분 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록한다. (이러한 특징 때문에 일부 DBMS에서는 리두 로그를 WAL 로그라고 하기도 한다.) 거의 모든 DBMS에서 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료구조를 가지고 있기 때문에 데이터 파일 쓰기는 디스크의 랜덤 엑세스가 필요하다.

그래서 변경된 데이터를 데이터 파일에 기록하려면 상대적으로 큰 비용이 필요하다. 이로 인한 성능 저하를 막기 위해 데이터베이스 서버는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있으며 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 종료 직전 상태로 복구한다.

데이터 베이스 서버는 ACID도 중요하긴 하지만 성능도 중요하기 때문에 데이터 파일뿐 아니라 리두 로그를 버퍼링 할 수 있는 InnoDB 버퍼 풀이나 리두 로그를 버퍼링할 수 있는 로그 버퍼와 같은 자료구조도 가지고 있다.

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

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

1번의 경우 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 된다. 하지만, 2번의 경우는 리두 로그로는 해결이 불가능하다. 이 때는 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하며 된다.

그렇다고 해서 2번의 경우 리두 로그가 전혀 필요하지 않은 것은 아니다. 최소한 그 변경이 커밋, 롤백 또는 트랜잭션 실행 중 상태였는지를 확인하기 위해서라도 리두 로그가 필요하다.

데이터베이스 서버에서 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정하는 것을 권장한다. 당연히 그렇게 되어야만 서버가 비정상 종료됬을 때 직전까지의 트랜잭션 커밋 내용이 리두 로그에 기록될 수 있고 이를 통해 장애 직전 상태로 복구가 가능하기 때문이다.

하지만 이처럼 트랜잭션이 커밋될 때 마다 리두 로그를 디스크에 기록하는 것은 작업의 과부화를 유발한다. 그래서 InnoDB 스토리지 엔진에서 리두 로그를 어느 주기로 디스크에 동기화할지 결정하는 innodb_flush_log_at_trx_commit 시스템 변수를 제공한다. 이 시스템 변수는 0, 1, 2의 값을 가질 수 있고 이 값에 따른 역할은 다음과 같다.

  • 0인 경우
    • 1초에 한 번씩 리두 로그를 디스크에 쓰고 동기화를 실행한다.
    • 서버가 비정상 종료되면 해당 트랜잭션에서 변경한 데이터가 유실될 수 있다.
  • 1인 경우
    • 매번 트랜잭션이 커밋될 때마다 디스크로 기록되고 동기화까지 수행한다.
    • 트랜잭션이 커밋되면 해당 트랜잭션에서 변경한 데이터는 사라진다.
  • 2인 경우
    • 매번 트랜잭션이 커밋될 때마다 디스크로 기록은 되지만 동기화가 1초에 한 번씩 실행된다.
    • 트랜잭션이 커밋되면 변경 내용이 OS의 메모리 버퍼로 기록되는 것을 보장한다.
    • 때문에 MySQL 서버가 종료됬더라도 OS가 정상적으로 작동한다면 해당 트랜잭션의 데이터는 사라지지 않지만, OS도 비정상적으로 종료된다면 데이터가 유실될 수 있다.

innodb_flush_log_at_trx_commit 시스템 변수가 0이나 2로 설정되는 경우 디스크 동기화 작업이 항상 1초 간격으로 실행되는 것은 아니다. 스키마 변경을 위한 DDL이 실행되면 리두 로그가 디스크로 동기화되기 때문에 InnoDB 스토리지 엔진이 스키마 변경을 위한 DDL을 실행했다면 1초보다 간격이 적을 수 있다.

하지만 스키마 작업은 자주 실행되는 작업이 아니므로 리두 로그는 최대 1초 정도 손실이 발생할 수 있다는 정도로 기억해두자. 또한 이 시스템 변수 값이 2인 경우, 디스크 동기화 시간 간격을 innodb_flush_log_at_timeout 시스템 변수를 이용해 변경할 수 있다. 디폴트 값은 1초이며, 일반적인 서비스에서 이 간격을 변경할 만한 특별한 이유는 없다.

InnoDB 스토리지 엔진의 리두 로그 파일들의 전체 크기는 InnoDB 스토리지 엔진이 가지고 있는 버퍼 풀의 효율성을 결정하기 때문에 신중하게 결정을 해야 한다. 이를 결정하는 시스템 변수들은 다음과 같다.

  • innodb_log_file_size : 리두 로그 파일의 크기를 결정
  • innodb_log_files_in_group : 리두 로그 파일의 개수를 결정

전체 리두 로그 파일의 크기는 이 두 시스템 변수들의 곱으로 결정된다.

또한, 적절히 변경된 내용을 버퍼 풀에 모았다가 한 번에 디스크로 기록해야 하므로 리두 로그 파일의 전체 크기가 InnoDB 버퍼 풀의 크기에 맞게 적절히 선택되어야 한다.

하지만 사용량(특히 변경이 잦은 작업)이 매우 많은 DBMS의 경우는 이 리두 로그 기록 작업이 큰 문제가 되는데, 이러한 부분을 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링 한다. 이러한 리두 로그 버퍼링에 사용되는 공간이 로그 버퍼이다. 로그 버퍼의 크기는 데이터를 자주 변경하는 경우에는 크게 설정하는 것이 좋고 일반적인 상황에서는 기본 값 수준에서 설정하는 것이 적합하다.

중요
ACID는 데이터베이스에서 트랜잭션의 무결성을 보장하기 위해 꼭 필요한 4가지 요소(기능)들을 의미한다.

  • A(Atomic) : 트랜잭션은 원자성 작업이여야 함을 의미
  • C(Consistent) : 일관성
  • I(Isolated) : 격리성
  • D(Durable) : 한 번 저장된 데이터는 지속적으로 유지되어야 함을 의미

일관성과 격리성은 쉽게 정의하기는 힘들지만, 이 두가지 속성은 서로 다른 두 개의 트랜잭션에서 동일 데이터를 조회 및 변경하는 경우에도 상호 간섭이 없어야하는 것을 의미한다.

2.11.1 리두 로그 아카이빙

참고
아카이빙 : 기본 스토리지를 확보하고 시스템 성능을 향상시키기 위해 더 이상 자주 사용하지는 않지만 다른 스토리지 시스템에 보존해야 하는 데이터를 옮기는 작업

백업 툴(MySQL 엔터프라이즈 백업, Xtrabackup 툴)은 MySQL 스토리지 엔진의 리두 로그에 쌓은 내용을 계속 추적하면서 새로 추가된 리두 로그 엔트리를 복사한다. 데이터 파일을 복사하는 동안 추가된 리두 로그 엔트리가 같이 백업되지 않는다면 복사된 데이터 백업 파일은 일관된 상태를 유지하지 못한다.

그런데 MySQL 서버에 유입되는 데이터 변경이 너무 많으면 이에 따라 리두 로그가 매우 빠르게 증가하여 새로 추가되는 리두 로그 내용을 복사하기도 전에 덮어쓰일 수 있다. 이렇게 아직 복사하지 못한 리두 로그가 덮어쓰이면 백업 툴이 리두 로그 엔트리를 복사할 수 없어서 백업이 실패하게 된다.

MySQL 8.0의 리두 로그 아카이빙기능은 데이터 변경이 많아서 리두 로그가 덮어쓰인다 하더라도 백업이 실패하지 않게 해준다.

백업 툴이 리두 로그 아카이빙을 사용하기 위해선 먼저 MySQL 서버에서 아카이빙된 리두 로그가 저장될 경로를 다음과 같이 innodb_redo_log_archive_dirs 시스템 변수에 설정해야 하며, 이 경로는 OS의 MySQL 서버를 실행하는 유저(일반적으로 mysql 유저)만 접근이 가능해야 한다.

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

디렉토리가 준비되면 다음과 같이 리두 로그 아카이빙을 시작하도록 innodb_redo_log_archive_start UDF(사용자 정의 함수 : User Defined Function)를 실행하면 된다. UDF는 1개 또는 2개의 파라미터를 입력할 수 있는데 다음과 같다.

  • 첫 번째 파라미터
    • 리두 로그를 아키이빙할 디렉터리에 대한 레이블
  • 두 번째 파라미터
    • 서브 디렉토리의 이름
    • 입력하지 않아도 되는데, 이 경우는 innodb_redo_log_archive_dirs 시스템 변수의 레이블에 해당하는 디렉토리에 별도 서브 디렉토리없이 리두 로그를 복사한다.
DO innodb_redo_log_archive_start('백업', '20200722');

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

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

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

리두 로그 아카이빙을 종료할 때는 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를 호출해서 아카이빙을 정상적으로 종료해야 한다.

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

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

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

MySQL 8.0 버전 부터는 수동으로 리두 로그를 활성화 또는 비활성화 할 수 있다. 그래서, 데이터를 복구하거나 대용량 데이터를 한 번에 적재하는 경우엔 다음과 같이 리두 로그를 비활성화 하여 데이터의 적재 시간을 줄일 수 있다.

<리두 로그를 비활성화>
mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;

<리두 로그를 비활성화 한 후 대용량 데이터 적재 실행>
mysql> LOAD DATA ...

<리두 로그를 활성화>
mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;

Innodb_redo_log_enabled 상태 변수를 살펴보면 리두 로그가 활성화 또는 비활성화 여부를 확인할 수 있다.

<리두 로그 활성화 여부 확인>
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으로 설정한 후 다시 시작해야 한다.

리두 로그를 비활성화 한 후 이런저런 작업을 하다 보면 다시 리두 로그를 활성화하는 것을 잊어버리기도 하는데, 의도치 않게 데이터가 손실될 수 있으니 주의하자.

그래서 데이터가 중요하지 않다 하더라도 서비스 도중에는 리두 로그를 활성화해서 MySQL 서버가 정상적으로 종료되어도 특정 시점의 일관적인 데이터를 가질 수 있도록 하자.

만약, MySQL 서버가 비정상적으로 종료되어 데이터의 일부가 손실되어도 괜찮다 하면 리두 로그를 비활성화 하기보단 innodb_flush_log_at_trx_commit 시스템 변수를 1이 아닌 0또는 2로 설정하여 사용하는 것을 권장한다.

2.12 어댑티브 해시 인덱스

일반적으로 '인덱스'라 하면 이는 테이블에서 사용자가 생성해둔 B-Tree 인덱스를 의미한다. 인덱스가 사용하는 알고리즘이 B-Tree는 아니더라도, 사용자가 직접 테이블에 생성해둔 인덱스가 우리가 알고 있는 인덱스일 것이다.

여기서 언급하는 '어댑티브 해시 인덱스(Adaptive Hash Index)'는 사용자가 수동으로 생성하는 인덱스가 아닌 InnoDB 스토리지 엔진에서 사용자가 요청 하는 데이터에 대해 자동으로 생성하는 인덱스이며, 사용자는 innodb_adaptive_hash_index 시스템 변수를 통해 이 기능을 활성화 또는 비활성화할 수 있다.

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

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

덕분에, B-Tree를 루트 노드부터 리프 노드 까지 찾아가는 비용이 없어지고 그만큼 CPU는 적은 일을 하며 쿼리의 성능은 빨라진다. 추가로 컴퓨터는 더 많은 쿼리를 동시에 처리할 수 있게 된다.

해시 인덱스은 다음 두 값을 쌍으로 관리하는데 그것들에 대한 설명은 다음과 같다.

  • 인덱스 키 값
    • B-Tree 인덱스와 고유번호(Id)와 B-Tree 인덱스의 실제 키 값의 조합
    • InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스는 하나만 존재하기 때문에 B-Tree 인덱스의 실제 키 값이 포함된다.
    • 즉, 모든 B-Tree 인덱스에 대한 해시 인덱스가 하나의 해시 인덱스에 저장되며, 특정 키 값이 어느 인덱스에 속한 것인지를 구분해야 한다.
  • 데이터 페이지 주소
    • 실제 키 값이 저장된 데이터 페이지의 주소
    • InnoDB 버퍼 풀에 로딩된 페이지의 주소
    • 따라서, 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리된다.

하지만 이러한 어댑티브 해시 인덱스를 의도적으로 비활성화하는 경우도 많다. 대표적인 경우를 살펴보면 다음과 같다.

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

다음과 같은 경우는 어댑티브 해시 인덱스를 활성화하는 것이 성능 향상에 도움이 많이 된다.

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

하지만, 단순히 어댑티브 해시 인덱스가 도움이 될지 여부를 판단하는 것은 쉽지 않다. 또한, 어댑티브 해시 인덱스는 저장 공간인 메모리를 사용하며 때로는 상당히 많은 공간을 사용할 수 있다. 한 가지 확실한 것은 어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀)내에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에, 데이터 페이지를 디스크에서 읽어오는 경우가 잦은 경우에는 비활성화하는 것이 좋다.

어댑티브 해시 인덱스는 삭제 작업에도 많은 영향을 미친다. 어떤 테이블의 인덱스가 어댑티브 해시 인덱스에 적재되어 있다고 가정해보자.
이 때, 테이블을 삭제(DROP)하거나 변경(ALTER)하려고 하면 InnoDB 스토리지 엔진은 이 테이블이 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거해야 한다.
이로 인해 테이블이 삭제되거나 스키마가 변경되는 동안 상당히 많은 CPU 자원을 사용하게 되고, 그만큼 데이터베이스 서버의 처리 성능이 저하된다.

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

위 이유들 때문에 어댑티브 해시 인덱스가 우리 서비스 패턴에 맞게 도움이 되는지 여부를 판단해야 하는데 정확한 판단을 할 수 있는 가장 쉬운 방법은 MySQL 서버 상태 값들을 살펴보는 것이다.

기본적으로 어댑티브 해시 인덱스는 활성화되있는 것을 참고하여 활성화/비활성화 마다 성능을 체크해보고 어댑티브 해시 인덱스가 사용중인 메모리 사용량이 높다면 비활성화하는 것이 좋을 수 있다.

어댑티브 해시 인덱스 메모리 사용량은 performace_schema를 이용해서 확인이 가능하니 참고하자.

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

지금까지는 MyISAM이 기본 스토리지 엔진으로 사용되는 경우가 많았다. 그러나, MySQL 8.0 부터는 MySQL 서버의 모든 시스템들이 InnoDB 스토리지 엔진으로 교체되었고 공간 좌표 검색이나 전문 검색 기능들 또한 모두 InnoDB 스토리지 엔진을 지원하도록 개선되었다.

즉, MySQL의 모든 서버 기능들을 InnoDB 스토리지 엔진으로만 구면할 수 있게 된 것이다. 따라서, 추후 MyISAM 스토리지 엔진은 없어질 것으로 예상 된다.

지금도 가끔씩 MyISAM이나 MEMORY 스토리지 엔진에 대한 성능상 장점을 기대하는 사용자들이 있는데, MySQL 8.0부터는 더이상 무의미한 비교가 될 것으로 보인다. 그들만이 가진 유니크한 장점이 없는 상태이다.

3. MySQL 로그 파일

로그 파일을 이용하면 MySQL 서버의 깊은 내부 지식 없이도 내부적으로 문제를 일으키는 원인을 쉽게 찾아 해결할 수 있다. MySQL 서버에 문제가 생겼을 때 무엇보다 로그 파일들을 자세히 확인하는 습관을 들이자.

3.1 에러 로그 파일

MySQL이 실행 도중 발생하는 에러나 경고 메시지가 출력되는 로그이다. MySQL 설정 파일(my.cnf)에서 log_error 라는 이름의 파라미터로 정의된 경로에 생성된다. MySQL 설정 파일에 별도로 정의되지 않은 경우엔 데이터 디렉토리(datadir 파리미터에 설정된 디렉토리)에 .err 라는 확장자가 붙은 파일로 생성된다.

여러 가지 메시지가 포함되있지만 다음에서 설명하는 메시지들을 가장 많이 보게 된다.

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

MySQL의 설정 파일을 변경하거나 데이터베이스가 비정상적으로 종료된 이후 다시 시작하는 경우에는 반드시 MySQL 에러 로그파일을 통해 설정된 변수의 이름이나 값이 의도대로 적용되었는지 확인해야 한다.

MySQL 서버가 정상적으로 기동했고('mysqld: ready for connections' 메시지 확인), 새로 변경하거나 추가한 파라미터에 대한 에러나 경고가 없다면 정상적으로 적용된 것이다.

그렇지 않고 특정 변수가 무시(ignore)된 경우에는 MySQL 서버는 정상 적동하나 해당 파라미터는 MySQL 서버에 적용되지 못했음을 의미한다.

만약 변수명을 인식하지 못하거나 설정된 파라미터 값의 내용을 인식하지 못하는 경우에는 MySQL 서버가 에러 메시지를 출력하며 시작하지 못했다는 메시지를 보여준다.

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

InnoDB의 경우 MySQL 서버의 비정상적 종료 후 재시작시 완료되지 못한 트랜잭션을 정리하고 디스크에 기록되지 못한 데이터가 있다면 다시 기록하는 재처리 작업을 하게 된다. 이 과정에 대해 간단한 메시지가 출력되는데, 간혹 문제가 있어 이를 못한 경우 에러 메시지를 출력하고 MySQL은 종료된다.

일반적으로 이 단계에서 발생하는 문제는 상대적으로 해결하기 어려운 문제일 때가 많고, 때로는 innodb_force_recovery 파라미터를 0보다 큰 값으로 설정해야 MySQL이 시작될 수 있다.

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

쿼리의 실행 도중 발생한 에러나 복제에 문제가 될 만한 쿼리에 대한 경고 메시지가 에러 로그파일에 기록되기 때문에 주기적으로 에러 로그파일을 검토하는 과정에서 발견하게 된다. 그러므로 쿼리 도중 발생하는 문제점은 사전 예방이 어렵다.

따라서, 로그 에러파일을 자주 검토하는 것이 데이터베이스의 숨겨진 문제점을 찾는데 많은 도움이 된다.

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

어떤 데이터베이스 서버의 로그 파일을 보면 이 메시지가 상당히 많이 누적되있는 경우가 있다. 클라이언트 어플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우 MySQL 서버는 에러 로그 파일에 이런 내용이 기록된다.

이러한 메시지가 많아지면 어플리케이션의 커넥션 종료 로직을 검토해볼 필요가 있다.

대표적으로 max_connect_errors 시스템 변수값이 너무 낮은 경우에 클라이언트 프로그램이 MySQL 서버에 접속하지 못하고 "Host 'host_name' is blocked" 라는 에러가 발생할 수 있다.

하지만 단순히 이 시스템 변수 값을 조정하기 보단 이 에러가 어떻게 발생했는지 그 원인을 살펴보는 것이 좋다.

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

InnoDB의 테이블 모니터링이나 락 모니터링 또는 InnoDB의 엔진 상태를 조회하는 명령은 상태적으로 큰 메시지를 에러 로그파일에 기록한다.

특히, InnoDB의 모니터링이 활성화 된 상태로 유지하는 경우 에러 로그파일이 매우 커지는 상황이 발생하기에 모니터링을 사용한 이후에는 다시 비활성화해서 이러한 일을 방지하자.

3.1.6 MySQL의 종료 메시지

가끔 MySQL이 아무도 모르게 종료되있거나 때로는 아무도 모르게 재시작되는 경우가 있다. 이러한 경우엔 에러 로그 파일에서 MySQL이 마지막에 종료되면서 출력한 메시지를 확인해 왜 종료된 원인을 찾아야 한다.

만약 누군가가 MySQL 서버를 종료시켰다면 'Received SHUTDOWN from user ...' 이라는 메시지를 확인할 수 있고, 아무런 종료 관련 메시지가 없거나 스택 트레이스(대표적으로 16진수의 주소값이 잔뜩 출력되는)와 같은 경우에는 MySQL 서버가 세그먼테이션 폴드(Segmentation fault)로 비정상적으로 종료된 것임을 확인할 수 있다.

비정상적으로 종료되었다면 스택 트레이스의 내용을 최대한 참조하여 MySQL의 버그와 관련있는지 조사한 후 조치를 하는 것이 최선이다.

profile
김민우
이전 포스트

사용자 및 권한

다음 포스트

트랜잭션과 잠금

0개의 댓글