Book/Real MySQL 8.0 上

04. 아키텍처

kinggora 2022. 11. 27. 17:37

4.1 MySQL 엔진 아키텍처

MySQL 의 전체 구조

 

MySQL 서버는 크게  MySQL 엔진과 스토리지 엔진으로 구분할 수 있다.

MySQL 엔진

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

MySQL 은 표준 SQL(ANSI SQL) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 다른 DBMS 와 호환된다.

스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나, 디스크 스토리지에서 데이터를 읽어오는 역할

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

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

핸들러 API

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

즉, 핸들러 API 를 통해 MySQL 엔진과 스토리지 엔진이 데이터를 주고받는다.

MySQL 스레딩 구조

MySQL의 스레딩 모델

MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 작동하며, 크게 포그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분된다.

 

참고: 여기서 소개하는 스레드 모델은 MySQL 서버가 전통적으로 가지고 있던 스레드 모델이며, MySQL 커뮤니티 에디션에서 사용되는 모델이다. MySQL 엔터프라이즈 에디션과 Percona MySQL 서버에서는 여기에 더해 스레드 풀(thread pool) 모델을 사용할 수 있다.

스레드 풀과 전통적인 스레드 모델의 가장 큰 차이점: 포그라운드 스레드와 커넥션과의 관계

  • 전통적인 스레드 모델: 커넥션과 포그라운드 스레드가 1:1 관계
  • 스레드 풀: 하나의 스레드가 여러 개의 커넥션 요청을 전담 -> 4.1.9절 스레드 풀

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

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

클라이언트가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 스레드 캐시로 반환된다.

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

 

포그라운드 스레드는 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.

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

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

백그라운드 스레드

InnoDB 의 백그라운드 스레드

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

데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요가 없지만 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 충분히 설정하는 것이 좋다.

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

그래서 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 있다. 그래서 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리(쓰기 작업)로 데이터가 변경되는 경우, 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다.

그러나 MyISAM은 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계되어 있기 때문에 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.

메모리 할당 및 사용구조

MySQL의 메모리 사용 및 할당 구조

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

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

운영체제 종류에 따라 메모리 할당 방식이 다르다.

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

글로벌 메모리 영역

일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다. 필요에 따라 2개 이상의 메모리 공간을 할당받을 수 있지만 클라이언트 스레드 수와는 무관하며, 모든 스레드가 공유하여 사용한다.

 

대표적인 글로벌 메모리 영역

  • Table Cache (테이블 캐시)
  • InnoDB Buffer Pool
  • InnoDB Adaptive Hash Index
  • InnoDB Redo Log Buffer

로컬 메모리 영역

클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다.

클라이언트가 MySQL 서버에 접속하면 클라이언트 요청을 처리하기 위해 스레드를 하나씩 할당하는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다.

클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 세션 메모리 영역이라고도 한다.

 

로컬 메모리는 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되지 않는다.

각 쿼리의 용도별로 필요할 때만 공간이 할당되고, 필요하지 않은 경우에는 MySQL 서버가 메모리 공간을 할당조차도 하지 않을 수 있다. (대표적으로 소트 버퍼나 조인 버퍼)

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

 

대표적인 로컬 메모리 영역

  • Sort Buffer (정렬 버퍼)
  • Join Buffer
  • Binary Log Cache
  • Network Buffer

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

MySQL 플러그인 모델

플러그인해서 사용할 수 있는 것이 스토리지 엔진만 있는 것은 아니다.

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

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

 

MySQL 엔진과 스토리지 엔진의 처리 영역

각 처리 영역에서 '데이터 읽기/쓰기' 작업은 데부분 1건의 레코드 단위로 처리된다.

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

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

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

MySQL 서버의 기능을 커스텀하게 활장할 수 있게 플러그인 API가 메뉴얼에 공개되어 있으므로 기존 MySQL 서버에서 제공하던 기능을 확장하거나 완전히 새로운 기능을 구현할 수 있다.

컴포넌트

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

MySQL 서버의 플러그인은 다음과 같은 단점이 있는데, 컴포넌트는 이러한 단점들을 보완한다.

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

쿼리 실행 구조

쿼리 실행 구조

쿼리 파서

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

전처리기

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

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

옵티마이저

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할. 옵티마이저의 역할은 중요하다 영향 범위 또한 넓다.

실행 엔진

옵티마이저 - 회사의 경영진

실행 엔진 - 중간 관리자

핸들러 -  각 업무의 실무자

 

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

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

핸들러(스토리지 엔진)

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

쿼리 캐시(Query Cache)

쿼리 캐시는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당했다.

SQL의 실행 결과를 메모리에 캐시하고 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보였다. 하지만 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제(Invalidate)해야 했다. 이는 심각한 동시 처리 성능 저하를 유발한다. 데이터 변경은 거의 없고 읽기만 하는 서비스에서는 매우 훌륭한 기능이지만 이런 요건을 가진 서비스는 거의 없다.

결국 8.0 에서 쿼리 캐시는 MySQL 서버의 기능에서 완전히 제거되고, 관련된 시스템 변수도 모두 제거되었다.

스레드 풀(Thread Pool)

커뮤니티 에디션은 스레드 풀 기능을 지원하지 않는다.

엔터프라이즈 에디션과 Percona Server 중에 Percona Server 에서 제공하는 스레드 풀 기능을 설명한다.

  • 엔터프라이즈 에디션: 스레드 풀 기능이 MySQL 서버 프로그램에 내장
  • Percona Server: 스레드 풀 플러그인 라이브러리 형태로 작동

 

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

스레드 풀은 동시 실행 중인 스레드를 CPU가 최대한 잘 처리할 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능이기 때문에 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우에는 쿼리 처리가 더 느려지는 사례도 발생할 수 있다는 점에 주의해야 한다. 

 

Percona Server 의 스레드 풀은 기본적으로 CPU 코어 개수만큼 스레드 그룹을 생성한다. 스레드 그룹의 모든 스레드가 일을 처리하고 있다면 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드(Worker thread)를 추가할지, 아니면 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부를 판단해야 한다. 서비스의 성격에 따라 관련 시스템 변수를 설정할 수 있다.

 

Percona Server 의 스레드 풀 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다. 

트랜잭션 지원 메타데이터

메타데이터 또는 데이터 딕셔너리

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

 

5.7 버전까지는 메타데이터를 파일 기반으로 관리했으나 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 비정상적으로 서버가 종료되면 일관되지 않은 상태로 남는 문제가 있었다.

8.0 버전부터는 메타데이터를 InnoDB 테이블에 저장하도록 개선되었다. MySQL 서버가 작동하는데 기본적으로 필요한 테이블을 시스템 테이블이라고 하는데, 이런 시스템 테이블과 데이터 딕셔너리를 모두 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장하도록 했다.

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

InnoDB 는 MySQL의 스토리지 엔진 가운데 가장 많이 사용된다.

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

InnoDB 구조

Primary Key 에 의한 클러스터링

InnoDB의 모든 테이블은 기본적으로 Primary Key 를 기준으로 클러스터링 되어 저장된다.

즉, Primary Key 값의 순서대로 디스크에 저장되며 모든 Secondary Index 는 레코드의 주소 대신 Primary Key 값을 논리 주소로 사용한다.

Primary Key 가 클러스터링 인덱스이기 때문에 Primary Key 를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.

결과적으로 쿼리의 실행 계획에서 Primary Key 는 기본적으로 다른 보조 인덱스보다 비중이 높게 설정된다.

(오라클 DBMS 의 Index Organized Table 과 동일한 구조가 InnoDB 의 일반적인 테이블 구조)

 

MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다. 그래서 Primary Key 와 Secondary Index 는 구조적으로 아무런 차이가 없다. 그리고 Primary Key 를 포함한 모든 인덱스는 물리적인 레코드의 주소 값(ROWID)을 가진다.

외래 키 지원

외래 키는 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 있지만, 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다.

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

서비스에 문제가 있어서 긴급하게 조치를 해야할 때는 foreign_key_checks 시스템 변수를 OFF 로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.

외래 키 체크를 일시적으로 중지하더라도 일관성을 맞춰준 후 외래 키 체크 기능을 활성화해야 한다. 외래 키 관계의 부모 테이블에 대한 작업(ON DELETE CASCADE 와 ON UPDATE CASCADE 옵션)도 무시하기 때문이다.

MVCC(Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC 의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는데 있다. InnoDB 는 Undo Log 를 이용하여 이 기능을 구현한다.

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

 

격리 수준(Isolation level)이 READ_COMMITED 일 때 InnoDB 테이블의 데이터 변경

1. 한 건의 레코드 INSERT

2. UPDATE

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

이때 기존 데이터가 Undo Log 로 복사된다.

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

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

격리 수준이 READ_UNCOMMITED 인 경우에는 InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다.

즉, 데이터가 커밋됐든 아니든 변경된 상태의 데이터를 반환한다. 

격리 수준이 READ_COMMITED 이나 그 이상(REPEATABLE_READ, SERIALIZABLE) 인 경우에는 아직 커밋되지 않았기 때문에 변경되기 이전의 내용을 보관하고 있는 Undo 영역의 데이터를 반환한다.

이러한 과정을 DBMS 에서는 MVCC 라고 표현한다. 즉, 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 상황에 따라 달라지는 구조이다.

 

이 상태에서 COMMIT 을 실행하면 InnoDB는 지금의 상태를 영구적인 데이터로 만든다.

ROLLBACK 을 실행하면 Undo 영역에 있는 백업 데이터를 InnoDB 버퍼 풀로 다시 복구하고, Undo 영역의 내용을 삭제한다.

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

InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다. 잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 읽기 작업이 가능하다.

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

 

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

잠겨진 레코드 읽기

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

자동 데드락 감지

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

 

일반적인 서비스에서는 데드락 감지 스레드의 활동은 크게 부담되지 않지만, 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 (ex. 프라이머리 키 기반의 조회 및 변경이 아주 높은 빈도로 실행되는 서비스) 데드락 감지 스레드가 성능을 저하시킬 수 있다. 이런 문제를 해결하기 위해 innodb_deadlock_detect, innodb_lock_wait_timeout 등의 시스템 변수를 제공한다.

자동화된 장애 복구

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

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

  • innodb_force_recovery : MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 한다. 1부터 6까지 값을 통해 복구되는 장애 상황과 해결 방법을 설정 할 수 있다.

InnoDB 버퍼 풀

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

버퍼 풀의 크기 설정

운영체제와 각 클라이언트 스레드가 사용할 메모리를 충분히 고려해서 설정해야 한다. MySQL 5.7 버전부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 개선됐다. 가능하면 InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 보며 증가시키는 방법이 최적이다. 버퍼 풀을 더 크게 변경하는 작업은 시스템 영향도가 크지 않지만, 버퍼 풀의 크기를 줄이는 작업은 서비스 영향도가 매우 크므로 가능하면 버퍼 풀의 크기를 줄이는 작업은 하지 않도록 주의하자. InnoDB 버퍼 풀은 128MB 단위로 줄이거나 늘릴 수 있다. 버퍼 풀은 innodb_buffer_pool_instances 시스템 변수를 이용해 여러 개로 분리해서 관리 할 수 있다.

버퍼 풀의 구조

InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기(innodb_page_size 시스템 변수에 설정된)의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때마다 해당 데이터 페이지를 읽어서 각 조각에 저장한다. 버퍼 풀의 페이지 크기 조각을 관리하기 위해 LRU(Least Recently Used) 리스트와 플러시(Flush) 리스트, 그리고 프리(Free) 리스트라는 3개의 자료 구조를 관리한다. 

  • 프리 리스트: InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록, 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우에 사용
  • LRU 리스트: LRU 와 MRU(Most Recently Used) 리스트가 결합된 형태. 디스크에서 한 번 읽어온 페이지를 오랫동안 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하는 목적
    • 아래 사진에서 New 서브리스트가 MRU, Old 서브리스트가 LRU 이다.
    • 한 번 읽힌 데이터 페이지가 이후 자주 사용된다면 버퍼 풀의 MRU 영역에서 계속 살아남고, 거의 사용되지 않는다면 새로 디스크에서 읽히는 데이터 페이지에 밀려서 LRU 의 끝으로 밀려나 결국 버퍼 풀에서 제거될 것이다.
  • 플러시 리스트: 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(=더티 페이지)의 변경 시점 기준의 페이지 목록을 관리한다.
    • 디스크에서 읽은 상태 그대로 변경이 없다면 플러시 리스트에서 관리되지 않지만, 일단 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록돼야 한다. 
    • 데이터가 변경되면 InnoDB는 변경 내용을 Redo Log 에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다. 그래서 Redo Log 와 각 엔트리는 특정 데이터 페이지와 연결된다. 

버퍼 풀 관리를 위한 LRU 리스트 구조

< InnoDB 스토리지 엔진에서 데이터를 찾는 과정 >

버퍼 풀과 리두 로그

InnoDB 의 버퍼 풀과 리두 로그는 매우 밀접한 관계를 맺고 있다. InnoDB 버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있는데, 버퍼 풀의 메모리 공간만 늘리는 것은 데이터 캐시 기능만 향상시키는 것이다. 쓰기 버퍼링 기능까지 향상시키려면 버퍼 풀과 리두 로그와의 관계를 먼저 이해해야 한다.

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

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

 

더티 페이지는 디스크와 메모리(버퍼 풀)의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록돼야 한다. 하지만 더티 페이지는 버퍼 풀에 무한정 머무를 수 있는 것은 아니다. 리두 로그 파일은 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용하기 때문에 데이터 변경이 계속 발생하면 기록했던 로그 엔트리가 어느 순간 새로운 로그 엔드리로 덮어 쓰인다. 그래서 InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능한 공간과 재사용 불가능한 공간을 구분해서 관리해야 하는데, 재사용 불가능한 공간을 활성 리두 로그(Active Redo Log) 라고 한다. 그림에서 화살표를 가진 엔트리들이 활성 리두 로그 공간이다.

리두 로그 파일의 엔트리는 로그 포지션 값을 가지는데, 이를 LSN(Log Sequence Number) 라고 하며 매번 기록될 때마다 증가된 값이 부여된다. InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화하는데, 이렇게 발생한 체크 포인트 중 가장 최근에 동기화된 엔트리의 LSN이 활성 리두 공간의 시작점이 된다. 데이터 변경이 발생하면 활성 리두 로그 공간의 마지막은 계속 증가하는데, 이 마지막 리두 로그 엔트리의 LSN과 가장 최근 체크포인트(디스크와 동기화된) 의 LSN 의 차이를 체크포인트 에이지(Checkpoint Age) 라고 한다. 체크포인트 에이지는 활성 리두 로그 공간의 크기를 일컫는다.

InnoDB 버퍼 풀의 더티 페이지는 특정 리두 로그 엔트리와 관계를 가지고, 체크포인트가 발생하면 체크포인트 LSN 보다 작은 리두 로그 엔트리+관련된 더티 페이지는 모두 디스크로 동기화 되어야 한다. 

 

1. InnoDB 버퍼 풀: 100GB, 리두 로그 파일의 전체 크기: 100MB

2. InnoDB 버퍼 풀: 100MB, 리두 로그 파일의 전체 크기: 100GB

 

1번은 리두 로그 파일의 크기가 너무 작기 때문에 버퍼 풀에서 허용 가능한 더티 페이지의 크기가 작아서 실제 쓰기 버퍼링의 효과는 거의 없다. 

2번은 이론적으로는 아무 문제가 없어보여도 실제 서비스를 운영하다 보면 급작스러운 디스크 쓰기가 발생할 가능성이 높다. 버퍼 풀에 더티 페이지의 비율이 높은 상태에서 갑자기 버퍼 풀이 필요해지면 많은 더티 페이지를 한 번에 디스크에 기록해야 하는 상황이 온다. 

 

참고: 일반적으로 리두 로그는 변경분만 가지고 버퍼 풀은 데이터 페이지를 통째로 가지기 때문에 데이터 변경이 발생해도 리두 로그는 훨씬 작은 공간만을 필요로 한다.

버퍼 풀 플러시(Buffer Pool Flush)

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

  • 플러시 리스트 플러시
  • LRU 리스트 플러시

플러시 리스트 플러시

InnoDB 스토리지 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 한다. 이 때 먼저 해당 엔트리와 연관된 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화돼야 한다. InnoDB 스토리지 엔진은 주기적으로 플러시 리스트 플러시 함수를 호출해서 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행한다. 

일반적으로 InnoDB 버퍼 풀은 더티 페이지를 많이 가지고 있을수록 디스크 쓰기 작업을 버퍼링함으로써 여러 번의 디스크 쓰기를 한 번으로 줄이는 효과를 극대화할 수 있다. 그런데 버퍼 풀에 더티 페이지가 많으면 많을수록 디스크 쓰기 폭발(Disk IO Burst) 현상이 발생할 가능성이 높아진다. 디스크로 기록되는 더티 페이지 개수보다 더 많은 더티 페이지가 발생하여 더티 페이지의 비율이 90%를 넘어가면 갑작스럽게 디스크 쓰기가 폭증하는 현상이 발생한다. 이때는 innodb_max_dirty_pages_pct_lwm 이라는 시스템 설정 변수를 이용하여 일정 수준 이상의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록하게 할 수 있다. 반면에 이 값을 높이면 디스크 쓰기 횟수를 줄이는 효과를 얻을 수 있다.

 

  • 어댑티브 플러시(Apaptive Flush): 기본값은 어댑티브 플러시를 사용하는 것으로, 단순히 버퍼 풀의 더티 페이지 비율이나 관련 설정값에 의존하지 않고 리두 로그의 증가 속도를 분석하여 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행한다.

LRU 리스트 플러시

InnoDB 스토리지 엔진은 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 하는데, 이를 위해 LRU 리스트 플러시 함수가 사용된다. LRU 리스트의 끝부분부터 시작해서 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼 페이지를 스캔하면서 더티 페이지는 디스크에 동기화하고, 클린 페이지는 즉시 프리(Free) 리스트로 페이지를 옮긴다.

버퍼 풀 상태 백업 및 복구

InnoDB 서버의 버퍼 풀은 쿼리의 성능에 매우 밀접하게 연결돼 있다. 원래 사용하던 서비스의 버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비되어 있으므로 디스크에서 데이터를 읽지 않아도 쿼리가 처리될 수 있기 때문이다. 

따라서 점검을 위해 서버를 셧다운 했다가 다시 시작하는 경우 강제 워밍업(Warming Up, 디스크의 데이터가 버퍼 풀에 적재된 상태)을 시행한다. 이때 버퍼 풀 데이터를 백업했다가 다시 복구를 진행한다.

버퍼 풀의 적재 내용 확인

MySQL 8.0 버전부터는 information_schema 데이터베이스에 innodb_cached_indexes 테이블을 이용하여 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재되어 있는지 확인할 수 있다.

 

Double Write Buffer

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

InnoDB 스토리지 엔진은 이런 문제를 막기 위해 Double-Write 기법을 사용한다. 

 

Double Write 작동 방식

 

실제 데이터 파일에 변경 내용을 기록하기 전에 'A' ~ 'E' 의 더티 페이지를 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록한다. 만약 각 더티 페이지를 파일에 쓰기를 실행 중에 오류가 발생하여 비정상적으로 종료된다면, InnoDB 스토리지 엔진이 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지를 비교하여 다른 내용을 담고 있는 페이지에 DoubleWrite 버퍼의 내용을 복사한다.

 

HDD 에서는 어차피 한 번의 순차 디스크 쓰기를 하기 때문에 DoubleWrite 버퍼가 별로 부담이 되지 않지만, SSD 처럼 랜덤 IO나 순차 IO 의 비용이 비슷한 저장 시스템에서는 상당히 부담스럽다. 하지만 데이터의 무결성이 매우 중요한 서비스에서는 DoubleWrite의 활성화를 고려하는 것이 좋다.

 

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

언두 로그(Undo Log)

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

 

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

언두 로그는 매우 중요한 역할을 담당하지만 관리 비용도 많이 필요하다.

언두 로그 레코드 모니터링

대용량의 데이터를 처리하는 트랜잭션 뿐만 아니라 트랜잭션이 오랜 시간동안 실행될 때도 언두 로그의 양은 급격히 증가할 수 있다. 트랜잭션이 완료됐다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것은 아니다. 여러 트랜잭션이 변경을 시도하여 트랜잭션 완료 시점이 모두 다를 수 있기 때문이다. 언두 로그가 증가하면 디스크 사용량 뿐만 아니라 매번 백업할 때도 그만큼 더 복사를 해야하는 문제점이 발생한다. 따라서 MySQL 서버에서 활성 상태의 트랜잭션이 장시간 유지되는 것은 성능상 좋지 않다. 그래서 MySQL 서버의 언두 로그 레코드를 항상 모니터링하는 것이 좋다.

 

8.0 버전부터는 언두 로그가 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록된다.

체인지 버퍼(Change Buffer)

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

사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스에서는 체인지 버퍼를 사용할 수 없다.

리두 로그 및 로그 버퍼

리두 로그는 트랜잭션의 4가지 요소인 ACID 중에서 D(Durable)에 해당하는 영속성과 가장 밀접하게 연관되어 있다. 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치 역할을 한다.

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

 

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

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

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

2번의 경우에는 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사해야 한다. 하물며 그 변경이 커밋됐는지, 롤백됐는지의 여부, 트랜잭션이 실행 중간 상태였는지를 확인하기 위해서라도 리두 로그가 필요하다.

 

로그 버퍼의 경우 BLOB 나 TEXT 와 같이 큰 데이터를 자주 변경하는 경우에 더 크게 설정하는 것이 좋다.

 

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

리두 로그 아카이빙

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

어댑티브 해시 인덱스(Adaptive Hash Index)

일반적으로 '인덱스'라고 하면 테이블에 사용자가 설정해둔 B-Tree 인덱스를 의미한다. 하지만 어댑티브 해시 인덱스는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이며, 시스템 변수를 통해 이 기능을 활성화하거나 비활성화 할 수 있다.

어댑티브 해시 인덱스는 B-Tree 검색 시간을 줄여주기 위해 도입된 기능으로, 자주 읽히는 데이터의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다. B-Tree 를 루트 노드부터 리프 노드까지 찾아가는 비용이 없어지고 쿼리 성능이 빨라질 수 있다.

 

  • 해시 인덱스: '인덱스 키 값'과 해당 인덱스 키 값이 저장된 '데이터 페이지 주소'의 쌍으로 관리
  • 인덱스 키 값: B-Tree 인덱스의 고유 번호(Id) 와 B-Tree 인덱스의 실제 키 값의 조합으로 생성

인덱스 키 값에 'B-Tree 인덱스의 고유 번호'가 포함되는 이유는 InnoDB 스토리지 엔진에서 어댑티브 해시 인덱스는 하나만 존재(물론 파티션되는 기능이 있지만)하기 때문이다. 즉, 모든 B-Tree 인덱스에 대한 어댑티브 해시 인덱스가 하나의 해시 인덱스에 저장되며, 특정 키 값이 어느 인덱스에 속한 것인지도 구분해야 하기 때문이다.

그리고 '데이터 페이지 주소'는 실제 키 값이 저장된 데이터 페이지의 메모리 주소, 즉 InnoDB 버퍼 풀에 로딩된 페이지의 주소를 의미한다. 그래서 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 버퍼 풀에서 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지의 정보는 사라진다. 

어댑티브 해시 인덱스를 활용하면 B-Tree의 루트 노드부터 검색이 많이 줄면서 InnoDB 내부 잠금(세마포어)의 횟수가 줄고 CPU 사용률 또한 낮출 수 있다. 

 

어댑티브 해시 인덱스가 성능 향상에 크게 도움이 되지 않는 경우

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

어댑티브 해시 인덱스가 성능 향상에 크게 도움이 되는 경우

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

어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀) 내에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 데이터베이스 서버에서는 아무런 도움이 되지 않는다.

그리고 어댑티브 해시 인덱스 또한 메모리를 사용하며 때로는 상당히 큰 메모리 공간을 사용할 수도 있다. 어댑티브 해시 인덱스 또한 데이터 페이지의 인덱스 키가 해시 인덱스로 만들어져야 하고, 불필요한 경우 제거돼야 하며, 일단 활성화 되면 그 키값이 해시 인덱스에 있든 없든 검색하는 과정이 필요하다. 테이블에 변경 사항(DROP이나 ALTER)이 생기면 어댑티브 해시 인덱스에도 반영되는 데도 상당히 많은 CPU 자원을 사용한다.

MySQL 서버에서 어댑티브 해시 인덱스의 효율은 쿼리 실행 횟수와 키 값 검색 횟수의 비율(해시 인덱스 히트율)과 어댑티브 해시 인덱스가 사용 중인 메모리 공간, 그리고 서버의 CPU 사용량을 종합해서 판단해야 한다. 

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

MySQL 5.5 부터

  • InnoDB 스토리지 엔진이 기본 스토리지 엔진으로 채택
  • MySQL 서버의 시스템 테이블은 여전히 MyISAM 테이블 사용
  • 전문 검색이나 공간 좌표 검색 기능은 MyISAM 테이블에서만 지원

MySQL 8.0

  • MySQL 서버의 모든 시스템 테이블이 InnoDB 스토리지 엔진으로 교체
  • 공간 좌표 검색, 전문 검색 기능 모두 InnoDB 스토리지 엔진을 지원
  • MyISAM 스토리지 엔진만이 가지는 장점 사라짐

MEMORY 스토리지 엔진 또한 동시 처리 성능에 있어 InnoDB 스토리지 엔진을 따라갈 수 없다. MySQL 서버는 일반적으로 온라인 트랜잭션 처리를 위한 목적으로 사용되기 때문에 동시 처리 성능이 매우 중요하다. 

MySQL 서버는 사용자의 쿼리를 처리하기 위해 내부적으로 임시 테이블을 사용할 수 있는데, 5.7 버전까지 내부 임시 테이블 용도로 사용되던 MEMORY 스토리지 엔진이 가변 길이 타입의 컬럼을 지원하지 않는 문제점 때문에 8.0 부터는 TempTable 스토리지 엔진으로 대체되었다. internal_temp_mem_starage_engine 시스템 변수를 이용해 내부 임시 테이블로 TempTable 엔진(기본값)을 사용할지, MEMORY 엔진을 사용할지 선택할 수 있다.

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

MyISAM 스토리지 엔진의 성능에 영향을 미치는 요소인 키 캐시와 운영체제의 캐시/버퍼

MyISAM 스토리지 엔진 구조

 

키 캐시(Key Cache)

InnoDB 의 버퍼 풀과 비슷한 역할을 하는 것이 MyISAM 의 키 캐시(키 버퍼라고도 불림)이다. 하지만 이름 그대로 키 캐시는 인덱스만을 대상으로 작동하며, 또한 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 한다.

 

  • 키 캐시 히트율(Hit rate) = 100 - (Key_reads / Key_read_request * 100)
  • Key_reads: 인덱스를 디스크에서 읽어들인 횟수를 저장하는 상태 변수
  • Key_read_requests: 키 캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수
  • 이 상태 값들은 SHOW GLOBAL STATUS 명령을 통해 확인 가능

메뉴얼에서는 일반적으로 키 캐시를 이용한 쿼리의 비율(히트율)을 99% 이상으로 유지하라고 권장한다. 히트율이 99% 미만이라면 키 캐시를 조금 더 크게 설정하는 것이 좋다. 

  • 32비트 운영체제: 하나의 키 캐시 4GB 이하로 한정
  • 64비트 운영체제: OS_PER_PROCESS_LIMIT 값에 설정된 크기만큼 메모리를 할당
  • 제한 값 이상을 할당하고 싶다면 기본 키 캐시 이외에 별도의 명명된(이름이 붙은) 키 캐시 공간을 따로 설정해야 함. 이때 캐시할 테이블과 인덱스를 지정

운영체제의 캐시 및 버퍼

MyISAM 테이블의 인덱스는 키 캐시를 이용해 디스크를 검색하지 않고도 충분히 빠르게 검색할 수 있다. 하지만 MyISAM 테이블의 데이터에 대한 디스크로부터 I/O 를 해결해줄 만한 캐시나 버퍼링 기능은 없다. 그래서 MyISAM 테이블의 데이터 읽기나 쓰기 작업은 항상 운영체제의 디스크 읽기 또는 쓰기 작업으로 요청된다. 물론 운영체제 자체의 캐시, 버퍼링 기능이 있기 때문에 MySQL 서버가 요청하는 디스크 읽기 작업을 위해 매번 디스크의 파일을 읽지는 않는다.

운영체제의 캐시 공간은 남는 메모리를 사용하는 것이 기본이기 때문에 애플리케이션에서 메모리를 모두 사용해 버린다면 운영체제가 캐시 용도로 사용할 수 있는 메모리 공간이 없어진다. 이런 경우에는 MyISAM 테이블의 데이터를 캐시하지 못하고 쿼리 처리가 느려진다. 데이터베이스에서 MyISAM 테이블을 주로 사용한다면 운영체제가 사용할 수 있는 캐시 공간을 위해 충분한 메모리를 비워둬야 이런 문제를 방지할 수 있다. 

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

InnoDB 스토리지 엔진을 사용하는 테이블은 프라이머리 키에 의해서 클러스터링되어 저장되는 반면, MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙(Heap) 공간처럼 활용된다. 즉 MyISAM 테이블에 레코드는 프라이머리 키 값과 무관하게 INSERT 되는 순서대로 데이터 파일에 저장된다. 그리고 모두 ROWID 라는 물리 주소값을 가지는데, 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드와 ROWID 값을 포인터로 가진다. 

 

ROWID 저장 방법

MAX_ROWS: 테이블 생성 옵션, 최대로 가질 수 있는 레코드 수 

  • 고정 길이: MAX_ROWS 을 명시하면, ROWID 값으로 4바이트 정수 사용. 레코드가 INSERT 된 순번
  • 가변 길이: MAX_ROWS 을 명시하지 않으면, myisam_data_pointer_size 시스템 변수에 따라 가변적으로 사용. 첫번째 바이트에 ROWID 의 길이를 저장하고 나머지 공간에 실제 ROWID 를 저장. 데이터파일에서 레코드의 위치(offset)

4.4 MySQL 로그 파일

MySQL 서버 상태를 진단할 수 있는 도구 중 하나. 

에러 로그 파일

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

  • MySQL이 시작하는 과정과 관련된 정보성 및 에러 메세지
  • 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메세지
  • 쿼리 처리 도중에 발생하는 문제에 대한 에러 메세지
  • 비정상적으로 종료된 커넥션 메세지(Aborted connection)
  • InnoDB의 모니터링 또는 상태 조회 명령(SHOW ENGINE INNODB STATUS 같은)의 결과 메세지
  • MySQL의 종료 메세지

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

MySQL 서버에서 실행되는 쿼리 목록. 슬로우 쿼리 로그와 조금 다르게 쿼리가 실행되기 전 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록된다.

슬로우 쿼리 로그

long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다. MySQL이 쿼리를 실행한 후, 실제 소요된 시간을 기준으로 기록 여부를 판단하기 때문에 반드시 쿼리가 정상적으로 실행이 완료되어야 슬로우 쿼리 로그에 기록될 수 있다.