MySQL 엔진 아키텍처

MySQL 엔진

커넥션 핸들러: 클라이언트로부터의 접속을 처리한다.
파서: 클라이언트로부터 들어온 쿼리 요청을 처리한다.
옵티마이저: 쿼리의 최적화된 실행을 도와준다.

스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 역할을 담당한다. MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 사용할 수 있다.

CREATE TABLE test_table(cal1, cal2, ...) ENGINE=INNODB;

핸들러 API

데이터를 읽거나 쓸 때 MySQL 엔진에서 스토리지 엔진으로 요청을 보내야 하는데, 이 때 사용하는 API를 핸들러 API라고 한다.

MySQL 스레딩 구조

MySQL 서버는 프로세스가 아닌 스레드 기반이다.

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

MySQL 서버에 접속된 클라이언트 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청한 쿼리 문장을 처리한다.
스레드가 작업을 마치고 커넥션이 종료되면 해당 스레드는 스레드 캐시로 들어간다. 근데 이미 스레드 캐시에 일정 개수 이상의 스레드가 존재한다면 스레드를 종료시킨다. (스레드풀 같은 느낌인 것 같다.)

포그라운드 스레드는 데이터를 캐시나 버퍼를 통해서 가져온다. 캐시나 버퍼에 데이터가 없다면 디스크에서 가져온다. MyISAM은 포그라운드 스레드가 캐시, 버퍼, 디스크 작업까지 모두 처리하지만 InnoDB는 캐시, 버퍼까지만 포그라운드 스레드가 처리하고 디스크 작업은 백그라운드 스레드가 처리한다.

백그라운드 스레드

InnoDB에서 다음 작업들이 백그라운드 스레드에 의해 처리된다.

인서트 버퍼 병합
로그를 디스크에 기록
버퍼 풀의 데이터를 디스크에 기록
데이터를 버퍼로 읽어옴
락이나 데드락을 모니터링

사용자의 읽기 작업은 지연될 수 없지만, 쓰기 작업은 지연되어 처리할 수 있다. InnoDB 또한 이러한 방식으로 쓰기 작업을 일괄 처리한다. 반면 MyISAM은 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계되어 있다.

메모리 할당 및 사용 구조

글로벌 메모리 영역

MySQL 서버가 시작되면서 운영제체로부터 할당받는다. MySQL 내의 모든 스레드가 공유해서 사용하는 영역이다.

다음과 같은 영역이 글로벌 메모리 영역이다.

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

로컬 메모리 영역

스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다. 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 세션 메모리 영역이라고도 부른다.

로컬 메모리 영역은 각 스레드 별로 독립적으로 할당받는다. 또한 쿼리 용도별로 필요할 때만 공간이 할당되고 필요하지 않으면 메모리를 할당조차 하지 않는다.

다음과 같은 영역이 로컬 메모리 영역이다.

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

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

MySQL에서 쿼리가 실행되는 과정은 거의 대부분이 MySQL 엔진에서 처리되고 데이터의 읽기/쓰기 작업만 스토리지 엔진에 의해 처리된다. 이 중에서도 실질적인 GROUP BY나 ORDER BY 등 복잡한 처리는 MySQL 엔진의 처리 영역인 '쿼리 실행기'에서 처리된다. 그럼에도 불구하고 각 스토리지 엔진 별로('읽기/쓰기' 처리 방식 별로) 매우 다른 동작의 차리를 보인다. 참고로 MySQL의 기본 스토리지 엔진은 InnoDB이다.

MySQL에서는 스토리지 엔진 뿐만 아니라 다양한 기능을 플러그인 형태로 지원한다. 인증이나 전문 검색 파서 또는 쿼리 재작성과 같은 플러그인이 존재한다. 또한 새로운 기능을 확장할 때도 플러그인을 이용해 구현할 수 있다.

참고로 MySQL 8.0 부터는 플러그인의 여러 단점을 보안하기 위해 컴포넌트 아키텍처가 지원된다.

쿼리 실행 구조

쿼리 파서

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

전처리기

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

옵티마이저

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

실행 엔진

옵티마이저가 수립한 계획을 토대로 핸들러를 이용해 실행하는 역할을 한다.

핸들러 (스토리지 엔진)

실행 엔진의 요청에 따라 데이터를 디스크로 저장하거나 읽어오는 역할을 한다. 즉, 스토리지 엔진(MyISAM, InnoDB)을 의미한다.

트랜잭션 지원 메타데이터

데이터베이스 서버에서 테이블의 구조, 스토어드 프로그램 등의 정보를 데이터 딕셔너리 혹은 메타데이터라고 하는데, MySQL 5.7 이하 버전에서는 이런 메타데이터를 파일 기반으로 관리했다. 따라서 트랜잭션을 지원하지 않아서 테이블을 생성하거나 변경하는 도중에 MySQL 서버가 비정상적으로 종료되면 테이블의 일관성이 깨지는 문제가 발생했다.

이러한 문제를 해결하기 위해 MySQL 8.0 이상 부터는 메타데이터를 테이블로 관리하기 시작했다. 따라서 테이블 스키마 변경 작업 도중 서버가 비정상적으로 종료돼도 트랜잭션이 적용되어 해당 작업이 원자성을 보장하게 된다. (MyISAM은 여전히 파일로 저장)

InnoDB 스토리지 엔진 아키텍처

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

InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 순서대로 디스크에 저장된다. 또한 모든 세컨더리 인덱스는 레코드의 주소 대신 프라미어리 키의 값을 논리적인 주소로 사용한다.

외래 키 지원

InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 확인하는 작업이 필요하므로 락이 여러 테이블로 전파되고 그로 인해 데드락이 발생할 확률이 높다.

외래 키로 인해 성능적 문제가 발생하면 foreign_key_checks 시스템 변수를 OFF로 설정하여 외래 키에 대한 검사를 잠시 멈출 수 있다(ON DELETE CASCADE와 같은 작업도 멈춘다.).

MVCC(Multi Version Concurrency Control)

MVCC의 가장 큰 목적은 락을 사용하지 않고 일관된 읽기를 제공하는 데 있다. InnoDB는 언두 로그를 이용해 이 기능을 구현하는데, 하나의 레코드에 여러 버전의 데이터를 관리함으로써 일관된 읽기를 제공할 수 있다.

member 데이터를 삽입한다면 처음에 위와 같이 데이터가 저장될 것이다.

그 후 해당 데이터를 UPDATE 쿼리를 통해 수정했다고 하면 위와 같이 버퍼 풀에는 수정된 데이터로 변경되고 언두 로그에는 수정되기 전 데이터가 저장된다. 또한 아직 커밋이 안된 상태이기 때문에 일반적으로 디스크에는 변경되기 전의 데이터가 저장되어 있다.

이 상태에서 해당 member를 조회하게 된다면, 트랜잭션 격리 수준에 따라 어떤 값을 조회할지 결정한다. 트랜잭션 격리 수준이 READ_UNCOMMITTED인 경우는 버퍼 풀에 저장되어 있는 변경된 데이터를 조회하고, READ_COMMITTED 이상의 격리 수준에서는 언두 로그에 저장되어 있는 변경되기 전의 데이터를 조회한다.

MVCC는 위와 같이 하나의 레코드에 대해 2개 이상의 버전이 관리되고 격리 수준에 따라 어떤 데이터가 보여지는지 달라지는 구조이다.

이 상태에서 커밋을 하면 이 상태로 영구적인 데이터를 만든다. 이때, 언두 로그에 있는 데이터를 바로 삭제하는 것이 아니라 해당 언두 영역을 참조하고 있는 트랜잭션이 더 이상 없다면 삭제한다. 반면에 롤백을 하면 언두 영역에 있는 데이터를 다시 버퍼 풀로 복구하고 언두 영역의 데이터를 삭제한다.

락 없는 일관된 읽기

MVCC를 통해 락을 걸지 않고 읽기 작업을 수행할 수 있다. 격리 수준이 SERIALIZABLE이 아닌 경우, INSERT와 연관없는 순수한 SELECT 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 락을 대기하지 않고 바로 실행된다. 하지만 이런 언두 로그를 오랫동안 유지해야 하는 경우 성능 문제가 발생할 수 있다. 이러한 경우를 방지하기 위해서는 트랜잭션을 짧게 가져가는게 좋다.

자동 데드락 감지

InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있다. 이 스레드가 주기적으로 잠금 대기 그래프를 검사해 데드락에 빠진 트랜잭션을 찾아서 그 중 하나를 강제 종료한다. 이때, 언두 로그에 저장되어 있는 데이터의 수가 더 적은 트랜잭션을 종료시킨다.

데드락 감지는 상위 레이어인 MySQL 엔진에서 관리되는 테이블 락은 볼 수 없어서 불확실 할 수 있지만 innodb_table_locks 시스템 변수를 활성화 시킴으로써 테이블 락까지 감지하도록 할 수 있다.

일반적으로 데드락을 감지하는 스레드는 성능에 큰 영향을 끼치지 않지만 트랜잭션 락의 수가 매우 많아지면 성능 문제가 발생할 수 있다. 이런 경우 innodb_deadlock_detect를 OFF로 설정하여 자동 데드락 감지 기능을 비활성화 시킬 수 있다. 이때 데드락이 발생하면 무한정 대기하기 때문에 이를 해결하고자 innodb_lock_wait_timeout 시스템 변수를 활성화하여 데드락 상황에서 일정 시간이 지나면 자동으로 요청에 실패하도록 설정할 수 있다.

자동화된 장애 복구

손실이나 장애로부터 데이터를 복구하기 위한 다양한 메커니즘을 지원한다. MySQL 서버가 시작될 때 완료하지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 복구 작업이 자동으로 진행된다.

만약, 하드웨어같이 서버와 연관 없는 문제로 인해 자동 복구를 할 수 없는 상황이 발생할 수 있는데, 이런 경우 MySQL 서버가 시작되지 않고 그대로 종료해버린다. 이때는 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 된다.

InnoDB 버퍼 풀

디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 영역이다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있도록 도와주기도 한다.

버퍼 풀의 크기 설정

버퍼 풀의 크기는 적절히 작은 값을 설정한 뒤 서서히 증가시켜 나가는 방법이 최선이다. 버퍼 풀의 크기를 변경하는 것은 크리티컬한 작업이기 때문에 MySQL 서버가 한가할 때 하는게 좋다. 버퍼 풀의 크기를 늘리는 작업은 시스템에 크게 영향을 끼치지 않지만, 크기를 줄이는 작업은 시스템에 큰 영향을 끼칠 수 있으므로 웬만하면 하면 안된다.

버퍼 풀의 구조

InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다. 버퍼 풀의 페이지 크기 조각을 관리하기 위해 크게 LRU 리스트와 플러시 리스트, 프리 리스트 3개의 자료 구조를 관리한다.

프리 리스트는 실제 사용자 데이터로 채워지지 않은 비어있는 페이지들의 목록이며, 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다. LRU 리스트는 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화 하기 위해 사용된다.

LRU 리스트는 위와 같은 구조로 이루어져 있다. 이때 New 서브리스트는 MRU(Most Recently Used), Old 서브리스트는 LRU(Least Recently Used)이다. 즉, LRU 리스트는 엄밀하게 LRU와 MRU 리스트가 결합된 형태라 보면 된다.

InnoDB 에서 데이터를 읽는 과정은 다음과 같다.

필요한 레코드가 버퍼 풀에 있는지 검사
1. 버퍼 풀에 이미 해당 데이터 페이지가 있다면 해당 페이지 포인터를 MRU 방향으로 승급
디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
버퍼 풀에 상주하는 데이터는 사용자 쿼리가 얼마나 최근에 접근했는지에 따라 나이가 부여되며 오랫동안 사용되지 않았다면 나이가 늘어나게 되고 결국 버퍼 풀에서 제거된다. 반면에, 버퍼 풀에 데이터가 쿼리에 의해 사용되면 나이가 초기화된다.

따라서 데이터가 자주 읽히게 되면 그 데이터 페이지는 버퍼 풀에 MRU 영역에 오랫동안 살아남게 된다. 반면에 잘 조회되지 않는 데이터 페이지는 LRU 끝으로 밀려나 결국 버퍼 풀에서 제거된다.

플러시 리스트는 디스크로 동기화 되지 않은 데이터를 가진 페이지의 변경 시점 기준의 페이지 목록을 관리한다. 디스크에서 읽은 상태로 변경이 없는 데이터는 플러시 리스트에 관리되지 않지만, 한 번이라도 변경이 가해진 데이터 페이지는 플러시 리스트에 의해 관리되고 특정 시점이 되면 디스크로 기록되어야 한다.

버퍼 풀과 리두 로그

버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 기능을 제공한다. 이때, 쓰기 버퍼링 기능은 리두 로그와 연관되어 있다.

버퍼 풀에는 변경되지 않는 데이터를 가지고 있는 클린 페이지와 변경된 데이터를 가진 더티 페이지가 존재한다. 더티 페이지는 디스크와 메모리의 데이터 상태가 다르기 때문에 언젠가는 디스크로 기록되어야 한다. 데이터가 변경되면 변경 내용을 리두 로그에 기록한다. 이때, 데이터가 계속해서 변경되면 리두 로그 파일에 기록됐던 로그 엔트리는 다시 새로운 로그 엔트리로 덮어 쓰인다. 매번 리두 로그에 기록될 때마다 로그 포지션은 계속해서 증가하는데, 이를 LSN이라고 한다.

InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화하는데, 이렇게 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 된다. 그리고 가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지라고 하는데 이는 곧 활성 리두 로그 공간의 크기를 말한다.

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

버퍼 풀 플러시

InnoDB 스토리지 엔진의 더티 페이지의 디스크 쓰기 동기화와 관련된 시스템 설정은 다음 두 가지가 있다.

플러시 리스트 플러시
- 플러시 리스트 플러시 함수를 호출하여 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화 하는 작업을 수행한다.
LRU 리스트 플러시
- LRU 리스트 플러시 함수를 호출하여 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거하여 새로운 페이지들을 읽어올 공간을 만든다.

버퍼 풀 상태 백업 및 복구

버퍼 풀은 쿼리 성능과 매우 밀접하게 연관되어 있다. 버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비되어 있기 때문에 디스크에서 데이터를 가져오지 않아도 처리할 수 있기 때문이다. 따라서 MySQL 서버를 재시작하게 되어 버퍼 풀의 데이터가 모두 사라지면 버퍼 풀이 다시 채워지기 전까지는 쿼리 성능이 매우 떨어질 수 있게 되는데, 이런 문제를 해결하고자 MySQL 5.6 버전부터는 innodb_buffer_pool_dump_now 시스템 변수를 이용해 현재 버퍼 풀의 상태를 백업할 수 있다. 또한 서버를 재시작하면 innodb_buffer_pool_load_now 시스템 변수를 이용해 백업된 버퍼 풀의 상태를 다시 복구할 수 있다. (이를 자동화 하는 설정도 있다.)

Double Write Buffer

하드웨어의 오작동이나 비정상적인 종료에 의해 더티 페이지를 디스크로 플러시할 때 일부만 기록되는 문제가 발생할 수 있다. 이런 문제를 해결하기 위해 Double-Write 기법을 제공한다.

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

DoubleWrite 버퍼의 내용은 더티 페이지의 일부 내용이 누락됐을 경우에만 사용된다. 만약 그런 경우, 서버가 재시작될 때 항상 Double Write 버퍼의 내용과 데이터 파일들의 페이지를 모두 비교하여 다른 내용을 담고 있다면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다.

언두 로그

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

언두 로그를 통해 다음과 같은 효과를 얻을 수 있다.

트랜잭션 보장
- 트랜잭션이 롤백되면 변경되기 전의 데이터로 복구해야 하는데, 이 때 언두 로그에 저장되어 있는 데이터를 이용해 복구한다.
격리 수준 보장
- 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 해당 데이터를 조회한다면, 격리 수준에 맞게 언두 로그에 백업해둔 데이터를 조회한다.

언두 로그 레코드 모니터링

언두 로그에 데이터가 저장되는 과정을 살펴보면 다음과 같다.

id가 1인 데이터의 name 컬럼을 Kong에서 Kim으로 변경한다.
트랜잭션을 커밋 전에 실제 데이터 파일(버퍼)의 내용은 Kim으로 변경된다.
언두 로그에는 Kong이라는 변경 전의 값이 백업된다.
커밋을 하면 현재 상태가 그대로 유지된다.
롤백 하면 언두 영역에 백업된 데이터를 다시 데이터 파일로 복구한다.

이처럼 언두 로그는 데이터를 백업하는 용도로 사용될 수 있다.

MySQL 5.5 버전까지는 언두 로그에 데이터가 쌓이게 되면 성능 문제가 발생할 수 있었다. 하지만 MySQL 5.7 버전 이상부터는 이렇게 언두 로그에 데이터가 쌓이는 문제가 해결되었다.

체인지 버퍼

레코드가 INSERT, UPDATE 되면, 데이터 파일 뿐만 아니라 인덱스 데이터까지 업데이트 해줘야 한다. 이 작업은 자원을 꽤 많이 소요하게 되기 때문에 InnoDB는 버퍼에 인덱스 페이지가 있으면 바로 업데이트를 수행하지만 없다면 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다. 이때 사용하는 임시 메모리 공간을 체인지 버퍼라고 한다.

체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드라고 한다. 참고로 중복 여부를 검사해야 하는 유니크 인덱스의 경우 체인지 버퍼를 사용하지 않는다.

데이터의 삽입, 수정과 같은 연산이 빈번하여 체인지 버퍼가 더 많은 버퍼 풀을 사용할 수 있도록 하기 위해서 관련된 시스템 변수의 비율을 변경해주면 된다.

리두 로그

리두 로그는 트랜잭션의 4가지 요소 중에서 지속성과 가장 큰 연관이 있다. 메모리 버퍼에 저장되어 있는 데이터는 서버가 갑작스럽게 종료되면 사라져버린다. 이런 경우 리두 로그는 하드웨어나 소프트웨어 등 여러 문제점으로 인해 서버가 비정상적으로 종료되었을 때 데이터 파일에 기록되지 못한 데이터들을 잃지 않게 해주는 안정 장치 역할을 한다.

변경된 데이터의 내용을 파일에 기록하는 작업은 읽기 작업에 비해서 상대적으로 큰 비용이 든다. 이러한 문제점을 해결하기 위해 데이터베이스 서버는 쓰기 비용이 낮은 자료구조를 가진 리두 로그를 가지고 있다.

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

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

1번의 경우 리두 로그에 저장된 데이터를 데이터 파일이 복사하기만 하면 된다. 2번의 경우는 언두 로그에 저장된 데이터를 가져와 데이터 파일에 복사하면 된다. 이때 리두 로그는 커밋됐는지, 롤백됐는지 등의 작업 내역을 확인하는데 사용된다.

여러가지 설정에 따라 리두 로그를 다르게 관리할 수 있다.

1초에 한 번씩 리두 로그를 디스크에 기록하고 데이터 파일과 동기화
- 트랜잭션이 커밋되고 1초 이내로 서버가 비정상적으로 종료된다면 해당 데이터가 사라질 수 있음
트랜잭션이 커밋되면 리두 로그가 디스크에 기록되고 데이터 파일과 동기화한다. (권장)
트랜잭션이 커밋될 때마다 리두 로그가 디스크에 기록되지만 데이터 파일과 동기화는 1초에 한 번씩 일어난다.

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

MySQL 8.0 이상 부터는 리두 로그를 비활성화 하여 대량의 데이터를 삽입해야 하는 경우 속도를 단축시킬 수 있다.

어댑티브 해시 인덱스

사용자가 직접 생성하는 인덱스가 아니라 사용자가 자주 요청하는 데이터에 대해서 자동으로 생성하는 인덱스를 의미한다. InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 바로 찾아갈 수 있다. 이를 통해 B-Tree를 탐색하는 시간이 줄어들면서 CPU 점유율은 감소하고 쿼리 성능은 빨라진다.

어댑티브 해시 인덱스가 효과적인 경우
- 디스크의 데이터가 버퍼 풀의 크기와 비슷한 경우 (디스크 읽기가 적은 경우)
- 동등 조건 검색(IN, 동등 비교)이 많은 경우
- 쿼리가 데이터 중 일부에만 집중되는 경우
어댑티브 해시 인덱스가 효과적이지 않은 경우
- 디스크 읽기가 많은 경우
- 특정 패턴의 쿼리가 많은 경우(조인, LIKE 패턴 검색)
- 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

어댑티브 해시 인덱스는 버퍼 풀에 있는 데이터 페이지에 대한 접근을 더 빠르게 하도록 도와주는 인덱스이기 때문에 디스크 읽기가 많은 경우 비효율적이라는 것을 알아야 한다. 또한, 테이블이 변경되거나 삭제되는 경우 관련된 어댑티브 해시 인덱스 데이터도 모두 삭제해 줘야 하기 때문에 부하가 많이 생길 수 있다.

MySQL 로그 파일

에러 로그 파일

MySQL이 실행되는 도중에 발생하는 에러나 경고 메세지가 출력되는 로그 파일으로, 다음과 같은 메세지가 있다.

MySQL이 시작하는 과정과 연관된 정보성 및 에러 메세지
쿼리 처리 도중 발생하는 문제에 대한 에러 메세지
비정상적으로 종료된 커넥션 메세지
MySQL의 종료 메세지

제너럴 쿼리 로그 파일

시간 단위로 실행했던 쿼리의 내용이 모두 기록된다.

슬로우 쿼리 로그 파일

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

슬로우 쿼리 로그는 위와 같은 형태로 출력된다.

Time : 쿼리가 종료된 시점
User@Host : 쿼리를 실행한 사용자의 계정
Query_time : 쿼리가 실행되는 데 걸린 전체 시간
- Lock_time : MySQL 엔진 레벨에서 관장하는 테이블 락에 대한 대기 시간만 표현한다. 이 값이 매우 작은 값이라면 무시해도 된다. (0이 아니라고 꼭 테이블 잠금이 일어난 것은 아니다.)
Row_examined : 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지를 의미
- Rows_sent : 실제 몇 건의 처리 결과를 클라이언트로 보냈는지를 의미

슬로우 쿼리 파일의 로그들을 하나씩 확인하면서 슬로우 쿼리를 찾는게 힘들다면 Percona Toolkit의 pt-query-digest 스크립트를 이용하면 쉽게 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다.

저작자표시

[Real MySQL] 아키텍처