MySQL 서버는 사람의 머리 역할을 하는 MySQL 엔진과 손발 역할을 담당하는 스토리지 엔진으로 구분된다. 그리고, 스토리지 엔진은 핸들러 API를 만족하면 누구든지 스토리지 엔진을 구현해서 MySQL 서버에 추가해서 사용할 수 있다. 4장에서는 MySQL과 MySQL 서버에서 기본으로 제공되는 InnoDB 스토리지 엔진, 그리고 MyISAM 스토리지 엔진에 대해 살펴본다.
4.1 MySQL 엔진 아키텍처
4.1.1 MySQL의 전체 구조
프로그래밍 API: MySQL은 대부분의 프로그래밍 언어로부터 접근 방법을 모두 지원한다.
MySQL 엔진: 커넥션 핸들러, SQL 파서 및 전처리기, 옵티마이저가 중심을 이룬다.
쿼리 파서: 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업을 한다. 쿼리 문장의 기본 문법 오류는 이 과정에서 발견된다.
전처리기: 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 개체에 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인한다.
옵티마이저: 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정한다.
실행 엔진: 옵티마이저가 만든 실행 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
쿼리 캐시: 성능 저하와 많은 버그의 원인이 되어, MySQL 8.0으로 올라오면서부터 쿼리 캐시는 기능에서 완전히 제거되었다.
MySQL 8.0에서는 주로 InnoDB 스토리지 엔진 기반의 Buffer Pool 캐시를 적극 활용한다. (글로벌 메모리 영역)
스토리지 엔진(핸들러): 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분을 담당한다.
테이블이 사용할 스토리지 엔진을 저장하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.
4.1.2 MySQL 스레딩 구조 (스토리지 엔진 내부)
MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.
포그라운드 스레드
최소한 MySQL 서버에 접속한 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 백그라운드 스레드가 디스크에 기록하는 작업을 한다.(InnoDB의 경우)
백그라운드 스레드
InnoDB는 로그 스레드, 쓰기 스레드, 읽기 스레드 등 여러 가지 작업이 백그라운드 스레드로 처리된다.
일반적인 상용 DBMS는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 있다. 즉, 데이터가 변경되는 경우 완전히 저장될 때까지 기다리지 않아도 된다.
4.1.3 메모리 할당 및 사용 구조
MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.
글로벌 메모리 영역
일반적으로 클라리언트 스데르 수와 무관하게 하나의 메모리 공간만 할당된다. 이는 모든 스레드에 의해 공유된다.
대표적으로 테이블 캐시, InnoDB 버퍼 풀, InnoDB 어댑티브 해시 인덱스, InnoDB 리두 로그 버퍼가 있다.
로컬 메모리 영역
세션 메모리 영역이라고도 표현하며, 클라이언트 스레드가 쿼리를 처리하는 데 사용되는 메모리 영역이다. 각 클라이언트 스레드별로 독립적으로 할당되며, 절대 공유되어 사용되지 않는다는 특징이 있다.
커넥션이 열려 있는 동안 계속 할당왼 상태로 남아 있는 공간(커넥션 버퍼, 결과 버퍼)도 있고, 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(정렬 버퍼, 조인 버퍼)도 있다.
4.1.4 MySQL 확장 모델
MySQL의 기능을 확장하는 방법에는 플러그인 모델과 컴포넌트 모델이 있다.
플러그인 모델
MySQL 서버에서는 스토리지 엔진뿐만 아니라 다양한 기능을 플러그인 형태로 지원한다. (현재 스토리지 엔진 및 인증 플러그인 등은 플러그인 방식으로 작동)
플러그인 모델은, 플러그인끼리 통신할 수 없고, MySQL 서버의 변수나 함수를 직접 호출하여 안전하지 않으며 상호 의존 관계를 설정할 수 없어서 초기화가 어렵다는 등의 단점이 있다.
컴포넌트 모델
MySQL 8.0부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다.
대표적으로 새로 도입된 암호화 키 저장 컴포넌트, 성능 및 모니터링 컴포넌트가 있다.
4.1.5 스레드 풀
스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다.MySQL 커뮤니티 에디션에서 스레드 풀 기능을 사용하고자 한다면 동일 버전의 Percona Server에서 스레드 풀 플러그인 라이브러리를 설치해서 사용하면 된다.
많은 사람들이 MySQL 서버에서 스레드 풀만 설치하면 성능이 그냥 두 배쯤 올라갈 거라고 기대하는데, 스레드 풀이 실제 서비스에서 눈에 띄는 성능 향상을 보여준 경우는 드물었다.
Percona Server의 스레드 풀 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다. 이렇게 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있던 잠금이 빨리 해제되고 잠금 경합을 낮춰서 전체적인 처리 성능을 향상시킬 수 있다.
빠르게 끝나는 작업 먼저? 이거 이해 안됨 ;;
4.1.6 트랜잭션 지원 메타데이터
MySQL 8.0 버전부터는 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB 테이블에 저장하도록 개선됐다. 이런 시스템 테이블을 모두 mysql.ibd라는 이름의 테이블 스페이스에 저장된다. 이렇게 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장되도록 개선되면서, 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료된다 하더라도 스키마 변경이 완전한 성공 또는 실패로 정리된다.
4.2 InnoDB 스토리지 엔진 아키텍처
InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유이하게 레코드 기반의 잠금을 제공하며, 그 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.
4.2.1 프라이머리 키에 의한 클러스터링
InnoDB의 모든 테이블은 기본적으로 프라이머리 키(PK)를 기준으로 클러스터링되어 저장된다. 프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.
4.2.2 외래 키 지원
외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로, MyISAM이나 MEMORY 테이블에서는 사용할 수 없다. 외래 키는 데이터베이스 서버 운영에 불편함(아래 정리) 때문에 운영 데이트베이스에서는 생성하지 않는 경우도 자주 있는데, 그렇다 하더라도 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다.
부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요
변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파
그로 인한 데드락이 발생하는 경우가 많음
즉, 부모 테이블과 자식 테이블의 관계를 명확히 파악해서 순서대로 작업한다면 문제 없이 실행할 수 있지만 외래 키가 복잡하게 얽힌 경우에는 간단하지 않다. 이러한 경우에는 수동으로 외래 키 관계에 대한 체크 작업을 멈출 수 있다. (foreign_key_checks 변수)
4.2.3 MVCC(Multi Version Concurrency Control)
MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다. InnoDB는 언두 로그(Undo log)를 이용해 이 기능을 구현한다. 아래와 같이 UPDATE 문장이 실행되면 커밋 실행 여부와 관계없이 InnoDB의 버퍼 풀은 새로운 값인 '경기'로 업데이트 된다. 아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 작업 중인 레코드를 조회하면 언두 영역의 데이터를 반환한다. (READ_UNCOMMITED 격리 수준 제외)
또한, 롤백이 실행되면 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구한다.
4.2.4 잠금 없는 일관된 읽기(Non-Locking Consistent Read)
InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다. 즉, INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다. (Serializable은 순수한 SELECT 작업조차도 경우에 따라 잠금을 대기하게 되어 동시성이 매우 떨어진다.)
4.2.5 자동 데드락 감지
InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하지 위해 잠금 대기 목록을 그래프 형태로 관리한다. 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그 중 하나를 강제로 종료한다.
만약 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려지므로 innodb_deadlock_detect 변수를 OFF로 설정하여 데드락 감지 스레드를 작동하지 않도록 할 수 있다. 이런 경우에는 innodb_lock_wait_timeout을 기본값인 50초보다 훨씬 낮은 시간으로 변경해서 사용할 것을 권장한다.
4.2.6 자동화된 장애 복구
MySQL의 자동화된 장애 복구란 MySQL이 예기치 않은 시스템 장애나 충돌 후 데이터 손실 없이 자동으로 복구하는 과정을 의미한다. 하드웨어 장애 등으로 인한 상황에서 데이터베이스가 다시 시작될 때, 자동화된 장애 복구는 다음과 같은 두 단계로 이루어진다.
장애 이전에 이미 커밋된 트랜잭션이지만, 실제 데이터 페이지에 반영되지 못한 변경 사항
Redo Log에서 변경 사항을 읽어와 데이터 페이지에 적용한다.
아직 완료(커밋)되지 않은 채로 중단된 트랜잭션
Undo Log를 이용하여 트랜잭션의 변경 사항을 모두 원래 상태로 되돌린다.
만약 자동으로 복구될 수 없는 손상이 있다면 MySQL 서버는 자동 복구를 멈추고 종료되어 버린다. 이때는 MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 다시 시작해야한다. 그래도 시작되지 않으면 백업을 이용해 다시 구축하는 방법밖에 없다.
4.2.7 InnoDB 버퍼 풀
InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 역할과 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 한다.
4.2.7.1 버퍼 풀의 구조
InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다.
InnoDB 버퍼 풀의 내부 구조는 크게 다음 세 가지로 구성된다.
클린 페이지(바뀌지 않은 페이지)
버퍼 풀의 가장 큰 영역이며, 데이터 및 인덱스 페이지를 메모리에 캐싱한다.
버퍼 풀은 페이지를 관리하기 위해 LRU(Least Recently Used) 알고리즘을 사용한다.
버퍼 풀이 가득 차면, 최근 사용 빈도가 가장 낮은 페이지를 버퍼 풀에서 제거한다.
새 페이지를 로딩할 때는 LRU 목록의 앞쪽(MRU: Most Recently Used)에 넣는다.
페이지가 사용되면 해당 페이지의 포인터를 MRU 방향으로 이동하고, 사용이 없으면 LRU 방향을 이동한다.
프리 리스트(비어있는 페이지)
사용되지 않고 비어있는 메모리 공간으로, 새로운 페이지를 로드하거나 데이터를 추가할 때 사용된다.
더티 페이지(플러시 리스트, 변경된 페이지)
버퍼 풀에서 변경된 후, 아직 디스크에 반영되지 않은 페이지로, 백그라운드 스레드가 주기적으로 더티 페이지를 디스크에 쓴다.
변경 사항은 우선 Redo Log에 기록되고, 버퍼 풀의 데이터 페이지도 디스크에 변경 내용을 반영한다.
😒 리두 로그랑 더티 페이지 두 개를 사용하는 이유는 뭘까? 두 개념은 비슷하지만, 서로 다른 목적을 가지고 있다!
4.2.7.2 버퍼 풀과 리두 로그
데이터 캐시 기능 향상뿐만 아니라, InnoDB 버퍼 풀의 쓰기 버퍼링 기능까지 향상시키려면 InnoDB 버퍼 풀과 리두 로그와의 관계를 먼저 이해해야 한다. InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용한다. 리두 로그를 무한정 누적하면 디스크 용량이 부족하므로, 설정한 크기 안에서 계속 돌려쓰는 구조로 효율적이다.
리두 로그 파일의 공간은 게속 순한되어 사용되지만, 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 갖게 되는데, 이를 LSN(Log Sequence Number)라고 한다. 페이지가 메모리(버퍼 풀)에서 수정되면, 더티 페이지가 되고, 이 더티 페이지에도 LSN이 기록된다. 트랜잭션이 수행되면, 변경 내용을 리두 로그 버퍼에 다음과 같이 기록한다.
Redo Log Entry:
- LSN 1050: Page 123의 age 필드 = 30 으로 변경됨
체크 포인트를 통해 InnoDB가 지금까지의 변경 사항 중 LSN의 xxx까지는 디스크에 안전하게 반영됐다고 선언하고, 이 시점 이후의 변경 사항(활성 리두 로그)은 아직 디스크에 반영되지 않았음을 알 수 있다.
🧠 메모리(버퍼 풀)
├─ Dirty Page (LSN 1050)
└─ Redo Log Buffer (LSN 1050)
💾 디스크
├─ Redo Log File → LSN 1050까지 저장
├─ 데이터 파일 (.ibd) → LSN 1030까지만 저장
└─ Checkpoint LSN = 1030
👉 장애 발생 시 → LSN 1031~1050까지 Redo Log 읽어서 복구
버퍼 풀과 리두 로그 용량을 설정할 시, 다음을 주의해야한다.
버퍼 풀이 아무리 커도, 리두 로그가 작으면 더티 페이지를 오래 못 보관해서 자꾸 디스크에 flush 해야한다. 즉, 쓰기 성능에 제한이 생긴다.
리두 로그 용량이 아무리 커도, 버퍼 풀이 작으면 더티 페이지 크기에 한계가 생긴다.
즉, 리두 로그 크기를 버퍼 풀 크기에 비례하여 10~20% 유지하면 좋다.
4.2.7.3 버퍼 풀 플러시(Buffer Pool Flush)
InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능 상의 악영향 없이 디스크에 동기화하기 위해 다음과 같이 2개의 플러시 기능을 백그라운드로 실행한다.
플러시 리스트 플러시
체크 포인트를 진행하거나 더티 페이지가 너무 많아졌을 때 실행된다.
장애 복구를 위한 디스크 동기화로, LSN이 오래된 페이지부터 flush 한다.
디스크 반영 후 해당 페이지는 클린 상태로 전환한다.
InnoDB는 상황에 따라 더티 페이지 비율이 빠르게 증가하면 flush 속도를 자동으로 증가시키는 어댑티브 플러시 기능도 제공한다.
LRU 리스트 플러시
새로운 페이지를 읽어야 하는데 LRU 리스트에 자리가 없을 때 실행된다.
버퍼 풀 공간 확보를 위한 목적으로, LRU 기준으로 자주 안 쓰인 페이지부터 flush 한다.
4.2.8 Double Write Buffer
더티 페이지를 디스크 파일로 플러시할 때, 일부만 기록되는 현상을 파셜 페이지 또는 톤 페이지라고 하는데, 이런 현상은 하드웨어의 오작동이나 시스템의 비정상 종료 등으로 발생할 수 있다. InnoDB 스토리지 엔진에서는 이 같은 문제를 막기 위해 Double-Write 기법을 이용한다.
'A'와 'B' 페이지는 정상적으로 기록됐지만 'C' 페이지가 기록되는 도중에 운영체제가 비정상적으로 종료됐다고 가정해보자. 그러면 InnoDB 스토리지 엔진은 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다.
🐋리두 로그 vs Doublewrite Buffer
즉, “나는 문서 3번을 수정했음”이라고 적어둠 → Redo Log. 하지만 문서 3번 자체가 날아갔다면? → 복구 못 함 또한, Doublewrite Buffer는 "깨지지 않은 페이지"를 보장하지만 그 페이지가 "진짜 반영돼야 하는 커밋된 결과"인지는 Redo Log 없이는 알 수 없다.
4.2.9 언두 로그
InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 전에, 이전 버전의 데이터를 별도로 백업한다. 이렇게 백업된 데이터를 언두 로그라고 한다. 언두 로그는 InnoDB 스토리지 엔진에서 매우 중요한 역할을 담당하지만, 관리 비용도 많이 필요하다.
4.2.9.1 언두 로그 레코드 모니터링
대용량의 데이터를 처리하는 트랜잭션뿐만 아니라 트랜잭션이 오랜 시간동안 실행될 때도 언두 로그의 양은 급격히 증가할 수 있다. 트랜잭션이 완료됐다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것은 아니다.
먼저 시작된 A 트랜잭션이 아직 활성 상태이기 때문에 B와 C 트랜잭션의 완료 여부와 관계없이 B와 C 트랜잭션이 만들어낸 언두 로그는 삭제되지 않는다.
MySQL 5.5버전까지 Undo 로그가 purge 되어도, 파일 크기는 줄지 않고 계속 누적되기만 했다. 다행스럽게도 버전이 업그레이드되면서 언두 로그를 돌아가면서 순차적으로 사용해 디스크 공간을 줄이는 것도 가능해졌다. 하지만 여전히 서비스 중인 MySQL 서버에서 활성 상태의 트랜잭션이 장시간 유지되는 것은 성능상 좋지 않다. 그래서 MySQL 서버의 언두 로그 레코드가 얼마나 되는지는 항상 모니터링하는 것이 좋다.
mysql> SHOW ENGINE INNODB STATUS \G
4.2.9.2 언두 테이블스페이스 관리
언두 로그가 저장되는 공간을 언두 테이블스페이스라고 한다. MySQL 5.6 이전 버전에서는 언두 로그가 모두 시스템 테이블스페이스(ibdata.ibd)에 저장됐다. MySQL 5.6 이후 버전부터는 시스템 테이블스페이스에 저장하지 않고 별도의 언두 로그 파일을 사용한다.
시스템 테이블스페이스(ibdata1)는 InnoDB 내부에서 데이터 딕셔너리, 더블라이트 버퍼, 트랜잭션 메타정보, 일부 버퍼 구조 를 저장하는 공간이며, 과거에는 모든 테이블/Undo 로그까지 넣는 범용 저장소였지만, MySQL 5.6+부터는 역할이 많이 분리되고 가벼워졌다.
언두 테이블스페이스의 불필요한 공간을 잘라내는(Truncate) 방법은 자동과 수동으로 두 가지 방법이 있는데, 두 방법 모두 MySQL 8.0부터 지원된다.
4.2.10 체인지 버퍼
RDBMS에서 레코드가 INSERT되거나 UPDATE될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요하다. InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 그렇지 않고 디스크로부터 읽어와서 업데이트를 해야한다면 이를 즉시 실행하지 않고 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키게 되는데, 이때 임시로 저장해두는 메모리 공간을 체인지 버퍼라고 한다.
// email이 보조 인덱스라고 가정
INSERT INTO users(email) VALUES ('kim@a.com');
// -> email 보조 인덱스를 업데이트해야하지만, 해당 인덱스 페이지가 아닌 버퍼 풀(메모리)에 없을 때!
// -> 체인지 버퍼에 이 페이지에 이 값 넣어야함 이라고만 기록
체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드라고 한다.
4.2.11 리두 로그 및 로그 버퍼
InnoDB 트랜잭션의 4가지 요소(ACID) 중 지속성(Durability)를 책임지는 핵심, 리두 로그와 로그 버퍼에 대해 알아보자.
리두 로그란, 데이터 변경 작업이 실제 데이터 파일(.ibd)에 반영되기 전에, 그 변경 내용을 순차적으로 기록해두는 로그 파일이다.
커밋됐지만 데이터 파일에 기록되지 않은 데이터
롤백됐지만 데이터 파일에 이미 기록된 데이터(더티 페이지 자리가 부족해서 그냥 플러시한 경우)
1번의 경우 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 된다. 하지만 2번의 경우에는 리두 로그로는 해결할 수 없는데, 이때는 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면 된다. (이때 리두 로그는 그 변경이 커밋됐는지, 롤백됐는지, 아니면 트랜잭션의 실행 중간 상태였는지를 확인하기 위해 필요하다.)
사용량이 매우 많은 DBMS 서버의 경우에는 리두 로그의 기록 작업이 큰 문제가 되는데, 변경 내용을 잠시 메모리에 보관하는 공간이 로그 버퍼이다.
4.2.11.1 리두 로그 아카이빙
Redo 로그는 순환 구조로 동작하기 때문에 일정 용량을 넘기면 오래된 로그는 덮어써진다. 그런데 MySQL 서버에 유입되는 데이터 변경이 너무 많으면 리두 로그가 매우 증가하고, 이때 아직 복사하지 못한 리두 로그가 덮어쓰이면 백업 툴이 리두 로그 엔트리를 복사할 수 없어서 백업은 실패실패하게 된다. MySQL 8.0의 리두 로그 아카이빙 기능은 데이터 변경이 많아서 리두 로그가 덮어쓰인다고 하더라도 백업 실패하지 않게 해준다.
4.2.12 어댑티브 해시 인덱스
일반적으로 '인덱스'라고 하면 이는 테이블에 사용자가 생성해둔 B-Tree 인덱스를 의미한다. 하지만 여기서 언급하는 '어댑티브 해시 인덱스'는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스를 말한다.
어댑티브 해시 인덱스가 성능 향상에 크게 도움이 되지 않는 경우
디스크 읽기가 많은 경우
특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
어댑티브 해시 인덱스가 성능 향상에 많은 도움이 되는 경우
디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
4.3 MySQL 로그 파일
MySQL 서버에 문제가 생겼을 때에는 다음의 로그 파일들을 자세히 확인하는 습관을 들일 필요가 있다.
4.3.1 에러 로그 파일
MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일로 MySQL 설정파일(my.cnf)에서 log_error라는 이름의 파라미터로 정의된 경로에 .err 확장자가 붙은 파일로 생성된다. 다음의 메시지들이 주로 출력된다.
MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지
쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
비정상적으로 종료된 커넥션 메시지
InnoDB의 모니터링 또는 상태 조회 명령의 결과 메세지
MySQL의 종료 메시지
4.3.2 제너럴 쿼리 로그 파일(제너럴 로그 파일)
가끔 MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토해볼 때가 있는데, 이때느 쿼리 로그를 활성화해서 쿼리를 쿼리 로그 파일로 기록하게 한 다음, 그 파일을 검토하면 된다.
슬로우 쿼리 로그와는 조금 다르게 실행되기 전에 MySQL이 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록된다.
4.3.3 슬로우 쿼리 로그 파일
MySQL 서버의 쿼리 튜닝은 크게 서비스가 적용되기 전에 전체적으로 튜닝하는 경우와 서비스 운영 중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝으로 나눌 수 있다.
전자의 경우에는 검토해야 할 대상 쿼리가 전부라서 모두 튜닝하면 되지만, 후자의 경우에는 어떤 쿼리가 문제의 쿼리인지 판단하기가 상당히 어렵다. 이런 경우에 서비스에서 사용되는 쿼리 중에 어떤 쿼리가 문제인지를 판단하는 데 슬로우 쿼리 로그가 상당히 많은 도움이 된다.
슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다. 이는 실제 소요된 시간을 기준으로 슬로우 쿼리 로그에 기록할지 여부를 판단하기 때문에 반드시 쿼리가 정상적으로 실행이 완료돼야 슬로우 쿼리 로그에 기록될 수 있다.
InnoDB의 경우 MySQL 엔진 레벨의 잠금과 스토리지 엔진 자체 잠금을 가지고 있기 때문에, 슬로우 쿼리 로그에 출력되는 내용이 상당히 혼란스러울 수 있다.
Time: 쿼리가 종료된 시점을 의미한다. 쿼리가 시작된 시점을 알기 위해서는 Time 에서 Query_time 만큼 빼야한다.
Query_time: 쿼리가 실행되는 데 걸린 전체 시간이다.
Lock_time: MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간만 표현한다.
Row_examined, Row_sent: 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지를 의미하며, Row_sent는 실제 몇 건의 처리 결과를 클라리언트로 보냈는지를 의미한다. 일반적으로 Row_examined의 레코드 건수는 높지만 Rows_sent에 표시된 레코드 건수가 상당히 적다면 튜닝해 볼 가치가 있는 것이다. (집합 함수가 아닌 경우에)
