Published on

4장. 아키텍처

4장. 아키텍처

MySQL 엔진 아키텍처

클라이언트 - MySQL 엔진 - 스토리지 엔진 - 운영체제 하드웨어

여기서 MySQL 엔진과 스토리지 엔진을 합쳐 MySQL 서버라고 합니다.

MySQL 엔진

사람으로 치면 두뇌에 해당하는 부분입니다.

  • 커넥션 핸들러: 클라이언트로부터의 접속 및 쿼리 요청 처리
  • SQL파서: SQL 구문 검사
  • 옵티마이저: 쿼리 최적화
  • 캐시 및 버퍼

스토리지 엔진

사람으로 치면 손발 역할을 하는 부분입니다.

  • 디스크 스토리지에 저장 및 읽기
  • MySQL 엔진은 하나이지만 스토리지 엔진은 여러 개를 사용할 수 있음
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;

이처럼 테이블 마다 사용할 엔진을 선택할 수 있습니다.

핸들러 API

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

SHOW GLOBAL STATUS LIKE 'Handler%';

위 명령어를 통해 핸들러 API가 얼마나 많은 데이터 작업이 있었는지 확인할 수 있습니다.

MySQL 스레딩 구조

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

크게 두 가지 스레드로 구분할 수 있습니다.

  • 포그라운드(Foreground) 스레드
  • 백그라운드(Background) 스레드
SELECT thread_id, name, type, processlist_user, processlist_host
FROM performance_schema.threads ORDER BY type, thread_id;

위 명령어를 통해 서버에서 실행 중인 스레드를 확인할 수 있습니다.

여기서 백그라운드 스레드 개수는 MySQL 서버의 설정 내용에 따라 가변적일 수 있습니다.

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

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

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

이때 일정 개수 이상의 스레드가 존재하면 스레드 캐시에 넣지 않고 종료시켜 스레드의 개수를 유지합니다.

데이터 버퍼나 캐시를 통해 데이터를 가져오며 만약 없다면 디스크나 인덱스 파일로 부터 데이터를 읽어와서 처리합니다.

백그라운드 스레드

InnoDB는 다음과 같이 여러 가지 작업이 백그라운드로 처리됩니다.

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

이 중 가장 중요한 것은 로그 스레드의 버퍼의 데이터를 디스크로 냉려쓰는 작업을 처리하는 쓰기 쓰레드 입니다.

MySQL 버전 5.5 부터는 데이터 쓰기 스레드와 데이터 읽기 스레드의 개수를 2개 이상 지정할 수 있게 되었습니다.

innodb_write_io_threads
innodb_read_io_threads

위 두 시스템 변수로 스레드의 개수를 설정 합니다.

쓰기 작업은 지연되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없기 때문에 일반적인 상용 DBMS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 있으며, InnoDB 또한 이러한 방식으로 처리합니다.

메모리 할당 및 사용 구조

글로벌 메모리 영역

일반적으로 하나의 메모리 공강만 할당됩니다.

단, 필요에 따라 2개 이상의 메모리 공간을 할당받을 수도 있지만 클라이언트의 스레드 수와는 무관합니다.

대표적인 글로벌 메모리 영역은 아래와 같습니다.

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

로컬 메모리 영역

세션 메모리 영역이라고도 부르며, MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역입니다.

대표적인 로컬 메모리 영역은 아래와 같습니다.

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

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

플러그인을 사용하면 다양한 스토리지 엔진을 사용할 수 있습니다.

스토리지 엔진은 쓰기 및 읽기만을 하는 엔진이지만 이 부분이 상당히 중요합니다.

SHOW PLUGINS;

위 명령어를 통해 설치 된 플러그인들을 모두 확인할 수 있습니다.

컴포넌트

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

플러그인 아키텍처는 아래와 같이 몇 가지 단점이 있습니다.

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

예를 들어 비밀번호 검증 기능 컴포넌트를 설치 하는법은 아래와 같습니다.

INSTALL COMPONENT 'file://component_validate_password';

설치 된 컴포넌트를 확인하는 명령어는 아래와 같습니다.

SELECT * FROM mysql.component

쿼리 실행 구조

  1. SQL 요청
  2. 쿼리 파서
  3. 전처리기
  4. 옵티마이저
  5. 쿼리 실행기
  6. 스토리지 엔진

쿼리 파서

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 구조로 만들어 내는 작업을 의미 합니다.

이 단계에서 기본 문법 오류가 발견됩니다.

전처리기

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

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

옵티마이저

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

DBMS의 두뇌에 해당한다고 볼 수 있습니다.

실행 엔진

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

옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 가정하면 과정은 아래와 같습니다.

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

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

핸들러(스토리지 엔진)

MySQL 가장 밑단에서 실행 엔진의 요청에 따라 데이터를 디스크에 저장 및 읽어오는 역할을 합니다.

쿼리 캐시

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

SQL 실행 결과를 메모리에 캐시하고 동일한 SQL 쿼리가 실행되면 바로 메모리에 있는 값을 반환하여 성능을 높였으나, 수정된 테이블과 관련 있는 값들은 모두 삭제해야 했기에 심각한 동시 처리 성능 저하를 유발했습니다.

MySQL 8.0에서는 쿼리 캐시 및 관련 시스템 변수는 모두 제거 되었습니다. 이로 얻을 수 있는 성능 향상 보다는 수많은 버그의 원인으로 지목되는 경우가 더 많았기 때문입니다.

스레드 풀

MySQL 서버 엔터프라이즈 에디션은 스레드 풀 기능을 제공하지만 커뮤니티 에디션은 스레드 풀 기능을 지원하지 않습니다.

여기서는 Percona Server에서 제공하는 스레드 풀 기능에 대해 살펴봅니다.

MySQL 커뮤니티 에디션에서도 스레드 풀 기능을 사용하고자 한다면 동일 버전의 Percona Server에서 스레드 풀 플러그인 라이브러리(thread_pol.so)를 설치하면 됩니다.

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

Percona Server의 스레드 풀은 기본적으로 CPU 코어의 개수 만큼 스레드 그룹을 생성합니다.

스레드 그룹의 개수는 thread_pool_size 시스템 변수를 변경해서 조정할 수 있습니다.

일반적으로는 CPU 코어의 개수와 맞추는 것이 CPU 프로세서 친화도를 높이는데 좋습니다.

스레드 그룹의 모든 스레드가 일을 처리하고 있다면 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드를 추가 할지 기다릴지 여부를 판단해야 합니다.

스레드 풀이 타이머 스레드는 주기적으로 스레드 그룹의 상태를 체크합니다.

thread_pool_stall_limit 시스템 변수에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 작업을 끝내지 못하면 새로운 스레드를 생성하여 스레드 그룹에 추가합니다.

여기서 스레드 개수는 thread_pool_max_threads 시스템 변수에 설정된 개수를 넘어설 수 없습니다.

응답 시간에 아주 민감한 서비스라면 thread_pool_stall_limit 시스템 변수를 적절히 나줘서 설정해야 합니다.

하지만 0으로 설정하는 것은 권장하지 않으며, 이럴 경우 스레드 풀을 사용하지 않는 편이 더 좋습니다.

트랜잭션 지원 메타데이터

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

MySQL 5.7버전까지는 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일 기반으로 관리했습니다. 이러한 관리는 테이블 생성 및 수정 중 서버가 비정상적으로 종료되면 일관도지 않은 상태로 남는 문제가 있었습니다.

MySQL 8.0 버전 부터는 이러한 문제점을 해결하기 위해 시스템 테이블을 모두 InnoDB를 사용하도록 개선했습니다.

MySQL DB는 통째로 mysql.ibd라는 이름의 테이블스페이스에 저장됩니다.

InnoDB 스토리지 엔진 아키텍처

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

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

모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용합니다.

프라이머리 키가 클러스터링 인덱스이기 떄문에 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정 됩니다.

MyISAM 스토리지 엔진은 클러스터링 키를 지원하지 않기 때문에 프라이머리 키나 세컨더리 인덱스나 동일합니다.

외래 키 지원

InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY 스토리지 엔진에서는 사용할 수 없습니다.

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

foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크를 일시적으로 멈출 수 있습니다.

SET foreign_key_checks=OFF;

외래키 체크를 일시적으로 해제했다고 해서 부모와 자식 테이블 간의 고나계가 깨진 상태로 유지해도 된다는 것을 의미하지 않습니다.

다시 일관성을 맞춘 후 반드시 활성화 처리를 해야 합니다.

이런 작업을 할 때는 반드시 세션에서만 멈추게 해야합니다.

MVCC(Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능입니다.

MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는데 있습니다.

InnoDB의 경우 트랜잭션 격리 레벨이 READ_COMMITTED라면 언두 로그에 변경 전 레코드를 저장하고 커밋이전에 읽기 요청이 오면 해당 값을 반환합니다.

언두 로그의 값을 더이상 아무곳에서도 사용되지 않을 때 삭제됩니다.

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

InnoDB 스토리지 엔진에서 트랜잭션 격리 수준이 SERIALIZABLE이 아니라면 순수 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행됩니다.

자동 데드락 감지

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

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

이때, 어떤 것을 강제 종료할 것인지 판단 여부는 언두 로그의 양입니다.

MySQL 엔진에서 관리되는 테이블 잠금은 볼 수가 없어서 데드락 감지가 불확실할 수도 있습니다.

이땐 innodb_table_locks 시스템 변수를 활성화하면 볼 수 있습니다.

특별한 이유가 없다면 활성화 하도록 합시다.

동시 처리 스레드가 매우 많은 경우 데드락 감지 스레드는 더 많은 CPU 자원을 소모할 수도 있습니다.

이럴 땐, innodb_deadlock_detect 시스템 변수를 OFF로 설정하면 데드락 감지 스레드는 작동하지 않습니다.

이렇게 데드락 감지 스레드가 작동하지 않을 때 데드락 상황이 발생하면 무한정 대기 상태에 들어가게 됩니다.

이럴 땐, innodb_lock_wait_imeout 시스템 변수를 활성화하면 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패합니다.

innodb_deadlock_detect를 OFF로 설정 했다면 innodb_lock_wait_imeout는 기본값 50보다 더 낮은 시간으로 변경하여 사용하는 것을 권장합니다.

자동화된 장애 복구

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

InnoDB 스토리지 엔진은 매우 견고해서 데이터 파일이 손상되거나 MySQL 서버가 시작되지 못하는 경우는 거의 발생하지 않습니다.

하지만 그와 무관하게 디스크나 서버 하드웨어 이슈로 문제가 생겨 자동으로 복구를 못 하는 경우가 발생할 수도 있습니다.

이럴 땐 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 합니다.

  • 1(SRV_FORCE_IGNORE_CORRUPT)
    • InnoDB의 테이블스페이스의 데이터나 인덱스 페이지에서 손상된 부분이 발경되어도 무시하고 서버를 시작
    • Database page corruption on disk or a failed 메세지가 출력될 때 대부분 이에 해당 -덤프해서 DB를 다시 구축하는 것이 좋음
  • 2(SRV_FORCE_NO_BACKGROUND)
    • InnoDB는 쿼리의 처리를 위해 여러 종류의 백그라운드 스레드를 동시에 사용하는데, 이 복구 모드에서는 이러한 백그라운드 스레드 가운데 메인 스레드를 시작하지 않고 서버를 시작
    • InnoDB의 메인 스레드가 언두 데이터를 삭제하는 과정에서 장애가 발생한다면 이 모드를 사용
  • 3(SRV_FORCE_NO_TRX_UNDO)
    • 커밋되지 않은 트랜잭션의 작업을 롤백하지 않고 그대로 놔둔 채 서버 시작
    • 서버가 시작되면 데이터를 백업해 DB를 다시 구축하는 것이 좋음
  • 4(SRV_FORCE_NO_IBUF_MERGE)
    • 인서트 버퍼의 내용을 무시하고 강제로 서버 시작
    • MySQL이 재시작되면서 인서트 버퍼의 손상을 감지하면 에러를 발생시키고 서버는 시작하지 못함
    • 인서트 버퍼는 실제 데이터와 관련된 부분이 아니라 인덱스에 관련된 부분이므로 테이블을 덤프한 후 다시 DB를 구축하면 데이터의 손실 없이 복구 할 수 있음
  • 5(SRV_FORCE_NO_UNDO_LOG_SCAN)
    • 언두 로그를 모두 무시하고 서버를 시작
    • 서버가 장애나 정상적으로 종료되는 시점에 트랜잭션이 있었다면 커넥션을 강제로 끊어버리고 별도의 정리 작업 없이 종료
    • 서버를 다시 시작하면 언두 레코드를 이용해 데이터 페이지를 복구하고 리두 로그를 적용해 종료 시점이나 장애 발생 시점의 상태를 재현하고 커밋되지 않은 트랜잭션은 롤백 처리 함
    • 언두 로그를 사용 할 수 없다면 서버를 시작할 수 없음
    • 이때도 데이터를 백업하고 DB를 새로 구축해야 함
  • 6(SRV_FORCE_NO_LOG_REDO)
    • 리두 로그를 모두 무시한 채로 서버 시작
    • 커밋됐다 하더라도 리두 로그에만 기록되고 데이터 파일에 기록되지 않은 데이터를 모두 무시 됨
    • 디존 InnodB의 리두 로그는 모두 삭제(또는 별도의 디렉터리에 백업)하고 서버를 시작하는 것이 좋음
    • 데이터를 모두 백업해서 서버를 새로 구축하는 것이 좋음

숫자가 높을 수록 그만큼 심각한 상황이며 데이터 손실 가능성이 커지고 복구 가능성은 적어집니다.

InnoDB 버퍼 풀

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

쓰기 버퍼 역할도 같이 합니다.

버퍼 풀의 크기 설정

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

처음으로 서버를 준비한다면 아래와 같이 설정 후 찾아가는 것을 권장합니다.

  • 메모리 8GB 미만: 50%
  • 그 이상이라면 전체 메모리의 50%에서 시작해서 조금씩 올라가면서 최적점 찾기

innodb_vuffer_pool_size 시스템 변수로 크기를 설정할 수 있습니다.

버퍼 풀의 크기 변경은 크리티컬한 변경이므로 가능하면 한가한 시점에 진행하는 것이 좋습니다.

또한 버퍼 풀의 크기를 늘리는 것은 시스템 영향도가 크지 않지만, 줄이는 작업은 영향도가 매우 크므로 줄이는 작업은 하지 않도록 주의해야 합니다.

InnoDB 버퍼 풀은 전통적으로 버퍼 풀 전체를 관리하는 잠금으로 인해 내부 잠금 경합을 많이 유발해왔는데, 이런 경합을 중링기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선되었습니다.

innodb_buffer_pool_instances 시스템 변수를 이용해 설정할 수 있습니다.

  • 기본적으로 버퍼 풀 인스턴스는 8개로 초기화
  • 메모리가 1GB이하 라면 1개만 생성 됨
  • 메모리가 40GB이하 수준이라면 8개를 유지
  • 그 이상이라면 인스턴스 당 5GB 정도가 되게 인스턴스 개수를 설정

버퍼 풀의 구조

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

이를 관리하기 위해 아래 3가지 자료구조를 관리합니다.

  • LRU(Least Recently Used) 리스트
  • Flush 리스트
  • Free 리스트

LRU 리스트를 관리하는 목적은 디스크로 부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것입니다.

과정은 아래와 같습니다.

  1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
    1. InnoDB 어댑티브 해시 인덱스를 이용해 페이지를 검색
    2. 해당 테이블의 인덱스를 이용해 버퍼 풀에서 페이지를 검색
    3. 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
  2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
  3. 버퍼 풀의 LRU 헤더 부분에 적대된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
  4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이가 부여되며, 오래 된 페이지는 버퍼 풀에서 제거
  5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

버퍼 풀과 리두 로그

버퍼 풀과 리두 로그는 매우 밀접한 관계를 맺고 있습니다.

버퍼 풀은 DB 서버의 성능 향상을 위해 두 가지 용도가 있습니다.

  • 데이터 캐시
  • 쓰기 버퍼링

메모리 공간을 늘린것은 캐시 기능만 향상 시킨것 입니다.

쓰기 버퍼링 기능을 향상 시키기 위해서는 리두 로그와의 관계를 이해해야 합니다.

버퍼 풀은 두 가지 페이지를 가지고 있습니다.

  • 클린 페이지: 디스크에서 읽은 상태
  • 더티 페이지: 변경된 상태

더티 페이지는 디스크와 버퍼 풀의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록돼야 합니다. 하지만 더티 페이지는 버퍼 풀에 무한정 머무를 수 있는 것은 아닙니다.

리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용합니다.

즉, 데이터 변경이 계속 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는 다시 새로운 로그 엔트리로 덮어 쓰입니다.

그렇기 때문에 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리해야 하는데, 재사용 불가능한 공간을 활성 리두 로그 라고합니다.

리두 로그 파일은 매번 기록 될 때 마다 로그 포지션을 계속 증가하는데 이를 LSM(Log Sequence Number)라고 합니다.

InnoDB는 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 동기화 시킵니다.

가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 됩니다. 하지만 활성 리두 로그 공간의 마지막은 계속 증가하기 때문에 체크포인트와 무관합니다.

가장 최근 체크포인트의 LSN와 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지라고 합니다. 즉, 체크포인트 에이지는 활성 리두 로그 공간의 크기를 의미합니다.

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

물론 체크 포인트 LSN보다 작은 LSN값을 가진 리두 로그 엔트리도 디스크로 동기화 되어야 합니다.

리두 로그는 변경분만 가지고 버퍼 풀은 데이터 페이지를 통째로 가지기 때문에 데이터 변경이 발생해도 리두 로그는 훨씬 작은 공간만 있으면 됩니다.

버퍼 풀 플러시(Buffer Pool Flush)

MySQL 5.6 까지는 더티 페이지 플러시 기능이 그다지 부드럽게 처리되지 않았습니다.

하지만 MySQL 8.0 까지 오면서 대부분의 더티 페이지를 디스크에 동기화하는 부분에서 디스크 쓰기 폭증 현상은 발생하지 않았습니다.

더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 아래의 두 개의 플러시 기능을 백그라운드로 실행합니다.

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

특별히 서비스를 운영할 때 성능 문제가 발생하지 않는 상태라면 굳이 이 시스템 변수들을 조정할 필요는 없습니다.

플러시 리스트 플러시

리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 합니다.

이때, 오래된 리두 로그 공간이 지워지려면 반드시 더티 페이지가 먼저 디스크로 동기화 되어야 합니다.

이를 위해 InnoDB는 주기적으로 플러시 리스트 플러시 함수를 호출해서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행합니다.

LRU 리스트 플러시

LRU리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만드는 역할을 합니다.

LRU 리스트의 끝부분부터 시작해서 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼의 페이지들을 스캔하는데, 이때 더티 페이지는 디스크에 동기화 되며 클린 페이지는 즉시 프리 리스트로 페이지를 옮깁니다.

버퍼 풀 상태 백업 및 복구

버퍼 풀은 쿼리 성능과 매우 밀접하게 연결되어 있습니다.

서버를 다시 시작하고 서비스를 시작하면 쿼리 처리 성능이 평상시보다 1/10도 안 되는 경우가 대부분 일텐데 이는, 버퍼 풀에 데이터가 적재되어 있지 않아서 그렇습니다.

버퍼 풀에 데이터가 적재되어 있는 상태를 워밍업(Warming Up)이라고 표현합니다.

innodb_buffer_pool_dump_now 시스템 변수를 이용해 현재 버퍼 풀의 상태를 백업할 수 있습니다.

  • 서버 셧다운 전에 버퍼 풀의 상태 백업
SET GLOBAL innodb_buffer_pool_dump_now=ON;
  • 서버 재시작 후, 백업된 버퍼 풀의 상태 복구
SET GLOBAL innodb_buffer_pool_load_now=ON;
  • 버퍼 풀 적재 작업에 너무 시간이 오래 걸려 멈추고 싶은 경우
SET GLOBAL innodb_buffer_pool_load_abort=ON;

버퍼 풀의 적재 내용 확인

MySQL 5.6 버전 부터 information_schema DB의 innodb_buffer_page 테이블을 이용해 버퍼 풀의 메모리에 어떤 테이블의 페이지들이 적재되어 있는지 확인할 수 있었습니다.

하지만 상당히 큰 부하로 인해 서비스 쿼리가 느려져 사용하는게 불가능했습니다.

MySQL 8.0에서는 innodb_cached_indexs 테이블이 추가되어 이런 문제를 해결합니다.

SELECT
	it.name table_name,
	ii.name table_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 = ii.index-id
WHERE it.name=CONCAT('employees','/','employees')

Double Write Buffer

InnoDB는 리두 로그의 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록합니다.

그렇기 때문에 더티 페이지를 디스크 파일로 플러시할 떄 일부분만 기록되는 문제가 발생하면 그 페이지의 내용은 복구 할 수 없을 수도 있습니다.

이렇게 페이지가 일부만 기록되는 현상을 Partial-page 또는 Torn-page라고 합니다.

주로 하드웨어의 오작동이나 시스템의 비정상 종료 등으로 발생할 수 있습니다.

이런 문제를 막기 위해 InnoDB는 DoubleWrite 기법을 사용합니다.

  • 실제 데이터 파일에 변경 내용을 기록하기 전에 A ~ E까지의 더티 페이지를 우선 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록
  • 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓰기를 실행
  • 비정상 종료 시 데이터 파일과 DoubleWrite 버퍼를 비교하여 다른 내용이 있다면 DoubleWrite버퍼를 데이터 파일에 복사

innod_doublewrite 시스템 변수로 활성화 여부를 지정할 수 있습니다.

여기서 리두 로그 동기화 설정(innodb_flush_log_at_trx_commit)을 1이 아닌 값으로 설정했다면 DoubleWrite 또한 비활성화 하는 것이 좋습니다.

언두 로그

트랜잭션과 격리 수준을 보장하기 위해 변경 되기 이전 버전의 데이터를 별도로 백업하는데 이렇게 백업된 데이터를 언두 로그라고 합니다.

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

언두 로그 레코드 모니터링

MySQL 5.5 이전 버전에서는 한 번 증가한 언두 로그 공간은 다시 줄어들지 않았습니다.

예를 들어 100GB 크기의 데이블을 삭제하면 그대로 언두 로그 공간이 100GB가 되는 것입니다.

트랜잭션이 길어지면 언두 로그가 계속 남아있게 되어 언두 로그 이력을 필요한 만큼 스캔해야 하므로 쿼리의 성능 또한 전반적으로 떨어지게 됩니다.

MySQL 8.0으로 업그레이드 되면서 이 문제점은 완전히 해결 되었습니다.

하지만 여전히 서비스 중인 MySQL 서버에서 활성 상태의 트랜잭션이 장시간 유지되는 것은 성능상 좋지 않습니다.

이런 부분은 모니터링이 필요합니다.

아래 명령어를 통해 활성화 된 트랜잭션이 몇 개인지 확인 할 수 있습니다.

  • 모든 버전에서 사용 가능
SHOW ENGINE INNODB STATUS \G
  • MySQL 8.0에서 사용 가능
SELECT count
FROM information_schema.innodb_metrics
WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';

언두 테이블스페이스 관리

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

버전 별로 많은 변화가 있었지만 결론적으로 MySQL 8.0에서는 innodb_undo_tablespaces 시스템 변수는 Deprecated 되었고 이제 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록되도록 개선되었습니다.

하나의 언두 테이블 스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯을 가집니다.

하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수 만큼의 언두 슬롯을 가집니다.

예를 들어 페이지 크기가 16KB라면 롤백 세그먼트는 1024개의 언두 슬롯을 가지는 것 입니다.

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

MySQL 8.0이 전에는 한 번 생성된 언두 로드는 변경이 허용되지 않았으나 MySQL 8.0 버전부터는 새로운 언두 테이블을 동적으로 추가하고 삭제할 수 있게 개선되었습니다.

CREATE UNDO TABLESPACE

DROP TABLESPACE

언두 테이블스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것을 Undo tablespace truncate 라고 합니다.

체인지 버퍼

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

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

MySQL 5.5 이전 까지는 INSERT에만 적용(인서트 버퍼)되었지만 이제는 개선 되어 INSERT, UPDATE, DELETE 모두 적용 됩니다.

innodb_change_buffering

시스템 변수를 통해 세부적인 설정할 수 있습니다.

  • all
  • none
  • inserts
  • deletes
  • updates: insert + deletes 버퍼링
  • purges: 인덱스 아이템을 영구적으로 삭제하는 작업만 버퍼링 (백그라운드작업)

innoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있으나 50%까지 사용하게 설정할 수 있습니다.

innodb_change_buffer_max_size

시스템 변수를 통해 변경 가능합니다.

리두 로그 및 버퍼

리두 로그는 ACID 중에서 D(Durable)에 해당하는 영속성과 가장 밀접하게 연관돼 있습니다.

서버가 비정상적으로 종료됐을 떄 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안정장치입니다.

대부분의 DBMS는 쓰기 성능 보단 읽기 성능을 고려한 자료구조를 가지고 있습니다.

그래서 쓰기를 하려면 비용이 높은데, 이를 해결하기 위해 쓰기 비용이 낮은 자료구조를 가진 리두 로그를 가지고 있습니다.

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

  1. 커밋됐지만 데이터 파일에 기록되지 않은 데이터
    • 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 됨
  2. 롤백됐지만 데이터 파일에 이미 기록된 데이터
    • 리두 로그로 해결 불가
    • 변경되지 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사
    • 최소한 그 변경이 커밋됐는지, 롤백됐는지, 트랜잭션의 실행 중간 상태였는지를 확인 하기 위해 리두 로그가 쓰임

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

이렇게 해야 서버가 비정상적으로 종료됐을 때 직전까지의 트랜잭션 커밋 내용이 리두 로그에 기록될 수 있고, 그 리두 로그를 이용해 장애 직전 시점까지의 복구가 가능해집니다.

그러나 심한 부하를 유발하기 때문에 어느 주기로 디스크에 동기화할지 시스템 변수를 제공합니다.

innodb_flush_log_at_trx_commit
  • 0: 1초에 한 번씩 리두 로그를 디스크로 기록
  • 1: 매번 트랜잭션이 커밋될 때마다 디스크로 기록되고 동기화까지 수행
  • 2: 매번 트랜잭션이 커밋될 때마다 디스크로 기록은 되지만 실질적인 동기화는 1초에 한 번씩 실행
innodb_flush_log_at_trx_commit

시간 간격을 위 시스템 변수를 통해 변경할 수 있습니다.

리두 로그 파일들의 전체 크기는 InnoDB 스토리지 엔진이 가지고 있는 버퍼 풀의 효윶성을 결정하기 때문에 신중히 결정해야 합니다.

innodb_log_file_size

위 시스템 변수를 통해 리두 로그 파일의 크기를 결정합니다.

innodb_log_files_in_group

위 시스템 변수를 통해 리두 로그 파일의 개수를 결정할 수 있습니다.

즉, 전체 리두로그 파일의 크기는 두 시스템 변수의 곱으로 결정됩니다.

사용량이 매우 많은 DBMS 서버의 경우 이 리두 로그의 기록 작업이 큰 문제가 되기도 합니다.

이러한 부분을 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링합니다.

이러한 리두 로그 버퍼링에 사용되는 공간이 로그 버퍼입니다.

로그 버퍼의 크기는 기본값인 16MB 수준에서 설정하는 것이 적합합니다.

BLOB나 TEXT같은 큰 데이터를 자주 변경하는 경우에는 더 크게 설정하는 것이 좋습니다.

ACID 란?

  • A(Atomic): 원자성
  • C(Consistent): 일관성
  • I(Isolated): 격리성
  • D(Durable): 한 번 저장된 데이터는 지속적으로 유지돼야 함

리두 로그 아카이빙

MySQL 8.0 부터 InnoDB 스토리지 엔진의 리두 로그를 아카이빙할 수 있는 기능이 추가되었습니다.

innodb_redo_log_archive_dirs

위 시스템 변수에 아카이빙 된 리두 로그가 저장될 디렉터리를 설정해야 합니다.

D0 innodb_read_log_archive_start('backup', '20221012')
  • 첫 번째 파라미터는 아카이빙할 디렉터리에 대한 레이블
  • 두 번째 파라미터는 서브디렉터리 이름
D0 innodb_read_log_archive_stop()

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

MySQL 8.0 부터는 리두 로그를 비활성화 할 수 있게 되었습니다.

ALTER INSTANCE DISABLE INNODB REDO_LOG;
ALTER INSTANCE ENABLE INNODB REDO_LOG;

innodb_read_log_enabled

위 환경 변수를 통해 리두 로그 활성화 여부를 확인할 수 있습니다.

어댑티브 해시 인덱스

일반저긍로 인덱스라고 하면 이는 테이블에 상ㅇ자가 생성해둔 B-Tree 인덱스를 의미합니다.

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

innodb_adaptive_hash_index

위 환경 변수를 통해 활성/비활성 설정을 할 수 있습니다.

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

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

B-Tree를 루트 노드부터 리프 노드까지 찾아가는 비용이 없어지고 그만큼 성능이 향상하게 됩니다.

하지만 어댑티브 해시 인덱스가 성능 향상에 크게 도움이 되지 않아 비활성화 하는 경우도 많습니다.

  • 도움 되지 않은 경우

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

  • 도움 되는 경우

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

즉, 디스크 읽기가 빈번한 경우 크게 도움이 되지 않습니다.

MySQL 로그 파일

로그 파일을 이용하면 MySQL 서버의 깊은 내부 지식이 없어도 MySQL의 상태나 부하를 일으키는 원인을 쉽게 찾아서 해결할 수 있습니다.

에러 로그 파일

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

MySQL의 설정 파일(my.cnf)에서 log_error라는 이름의 파라미터로 정의 된 경로에 생성됩니다.

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

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

MySQL의 설정 파일을 변경하거나 DB가 비정상적으로 종료 된 이루 다시 시작하는 경우에는 반드시 MySQL 에러 로그를 통해 설정된 변수의 이름이나 값이 명확하게 설정되고 의도한 대로 적용됐는지 확인해야 합니다.

정상 기동 시 메시지: mysqlid: ready for connections

무시 또는 경고 문구가 있을 경우 제대로 값이 적용되지 않았음을 의미합니다.

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

InnoDB의 경우에는 MySQL 서버가 비정상적 또는 강제적으로 종료됐다면 다시 시작되면서 완료되지 못한 트랜잭션을 정리하고 디스크에 기록되지못한 데이터가 있다면 다시 기록하는 재처리 작업을 하게 됩니다.

복구에 실패하면 에러 메시지를 출력하고 MySQL은 다시 종료됩니다.

이 단계에서 발생하는 문제는 상대적아로 해결하기 어려운 문제점일 때가 많습니다.

innodb_force_recovery 파라미터를 0보다 큰 값으로 설정하고 재시작해야 할 수도 있습니다.

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

쿼리 도중 발새하는 문제점은 사전 예방이 어렵습니다.

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

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

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

중간에 네트워크에 문제로 인한 커넥션 종료도 이런 메시지가 기록됩니다.

이런 메시지가 많다면 커넥션 종료 로직을 한번 검토해볼 필요가 있습니다.

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이 아무도 모르게 종료돼 있거나 재시작되는 경우가 있었을겁니다.

이런 경우 에러 로그 파일에서 마지막으로 종ㄹ되면서 출력한 메시지를 확인하는 것이 유일한 방법입니다.

  • 누군가 서버를 종료 시: Received SHUTDOWN from user ...
  • 스택 트레이스(16진수 주소 값): 세그먼테이션 폴트(Segmentation fault)로 비정상적인 종료로 판단

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

MySQL 서버에서 살향되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 보고 싶다면 쿼리 로그를 활성화해서 쿼리를 쿼리 로그 파일로 기록하게 한 다음 그 파일을 보면 됩니다.

슬로우 쿼리 로그와는 다르게 제너럴 쿼리 로그는 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록됩니다.

쿼리 로그 파일의 경로는 general_log_file파라미터에 설정되어 있습니다.

파일이 아닌 테이블에 저장하도록 설정할 수도 있기 때문에 이 때는 SQL로 조회해야 합니다.

SHOW GLOBAL VARIABLES LIKE 'general_log_file';

쿼리 로그의 저장 방식은 log_output 파라미터로 결졍됩니다. (FILE, TABLE)

슬로우 쿼리 로그 (Slow log)

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

쿼리를 실행한 후, 실제 소요된 시간을 기준으로 슬로우 쿼리 로그에기록할지 여부를 판단하기 때문에 반드시 쿼리가 정상적으로 실행이 완료되어야 슬로우 쿼리 로그에 기록됩니다.

제너럴 쿼리 로그와 같이 log_output 파라미터를 통해 저장 방식을 지정합니다.

  • Time: 쿼리가 종료된 시점 (Time - Query_time = 쿼리 시작 시점)
  • User@Host: 쿼리를 실행한 사용자 계정
  • Query_time: 쿼리가 실행되 데 걸린 전체 시간
  • Lock_time: MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한대기 시간만 표현 (값이 너무 작다면 무시해도 무방)
  • Rows_send: 결과 반환 개수
  • Rows_examined: 결과를 찾기 위해 접근한 레코드 개수

InnoDB에서도 Lock_time이 상대적으로 큰 값이 발생 할 수 있는데, 이는 레코드 수준의 잠근이 아닌 MySQL 엔진 레벨에서 설정한 테이블 잠금 때문일 가능성이 높기 때문에 InnoDB 테이블에만 접근하는 쿼리 문장의 슬로우 쿼리 로그에서는 Lock_time 값은 튜닝이나 쿼리 분석에 별로 도움되지 않습니다.

Percona에서 개발한 Percona Toolkit의 pt-query-digest 스크립트를 이용하면 제너럴 쿼리 로그나 슬로우 쿼리 로그의 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있습니다.

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

## SLow Log 파일 분석
linux> pt-query-digest --type='slowlog' mysql-slow.log > persed_mysql-slog.log

분석이 완료 되면 아래 3개의 그룹으로 나뉘어 저장됩니다.

  • 슬로우 쿼리 통계
  • 실행 빈도 및 누적 실행 시간순 랭킹
  • 쿼리별 실행 횟수 및 누적