MySQL 서버는 사람의 머리 역할을 하는 MySQL 엔진과 손발 역할을 하는 스토리지 엔진으로 구분된다.

4.1 MySQL 엔진 아키텍쳐

4.1.1 MySQL의 전체 구조

4.1.1.1 MySQL 엔진

MySQL 엔진은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서, 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저로 이루어진다.

4.1.1.2 스토리지 엔진

MySQL 엔진은 요청된 SQL 문장을 분석, 최적화하는 DBMS의 머리 역할을 하고, 실제 디스크 스토리지에 저장, 읽어오는 부분은 스토리지 엔진이 전담한다.

4.1.1.3 핸들러 API

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

4.1.2 MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아니라 스레딩 기반. 크게 포그라운드 스레드, 백그라운드 스레드로 구분된다.

performance_schema 데이터베이스의 threads 테이블

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

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

클라이언트가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 스레드 캐시로 돌아간다. 스레드 캐시는 항상 일정한 수로 유지된다.

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

4.1.2.2 백그라운드 스레드

• 백그라운드 스레드가 하는 일

  • 인서트 버퍼 병합

  • 로그를 디스크로 기록

  • Inno DB 버퍼 풀의 데이터를 디스크에 기록

  • 데이터를 버퍼로 읽어옴

  • 잠금, 데드락 모니터링

    가장 중요한 것은 로그, 버퍼 작업 처리하는 쓰기 스레드

읽기는 클라이언트 스레드에서 처리. 쓰기는 백그라운드로 처리하므로 디스크를 최적으로 사용할 수 있도록 설정 필요.

4.1.3 메모리 할당 및 사용 구조

글로벌 영역 / 로컬 메모리 영역

MySQL 서버 내에 존재하는 많은 스레드가 공유하는 공간인지 여부에 따라 구분됨.

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

4.1.3.1 글로벌 메모리 영역

클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당됨

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

4.1.3.2 로컬 메모리 영역

MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역

클라이언트 메모리 영역, 세션 메모리 영역 이라고도 함

각 스레드별로 독립적으로 할당. 절대 공유되지 않음.

쿼리 용도별로 필요할 때만 할당되고, 할당되지 않을 수도 있음

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

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

• MySQL은 플러그인 모델을 가짐. 사용자가 직접 스토리지 엔진을 개발하는 것도 가능하다.
• 거의 대부분의 작업이 MySQL 엔진에서 처리되고, 마지막 데이터 읽기/쓰기 작업만 스토리지 엔진에 의해 처리된다.
• MySQL 서버에서 MySQL 엔진은 사람 역할, 각 스토리지 엔진은 자동차 역할, MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러를 사용한다. MySQL 엔진이 각 스토리지 엔진에게 데이터 읽기/쓰기 명령을 하려면 반드시 핸들러를 통해야 한다. MySQL 서버의 상태 변수 중, ‘Handler_’로 시작하는 상태 변수는 MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수이다.
  
• 하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 처리되는 곳이 MySQL 엔진 영역인지, 스토리지 엔진 영역인지 구분할 줄 알아야 한다.

4.1.5 컴포넌트

8.0 버전부터는 플러그인을 대체하여 컴포넌트 아키텍처가 사용된다.

• 플러그인의 단점
  • 플러그인끼리는 통신할 수 없음
  • 캡슐화 안 됨
  • 상호 의존 관계를 설정할 수 없어 초기화 어려움

4.1.6 쿼리 실행 구조

• 쿼리 파서 사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업 토큰 : MySQL이 인식할 수 있는 최소 단위의 어휘나 기호
  
• 전처리기 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적 문제가 있는지 확인한다. 각 토큰을 개체에 매핑해 해당 객체의 존재 여부와 접근 권한 등을 확인한다.
  
• 옵티마이저 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정한다. DBMS의 두뇌이다.
  
• 실행 엔진 옵티마이저가 두뇌, 실행 엔진과 핸들러는 손과 발이다. 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
  
• 핸들러 (스토리지 엔진) MySQL 서버 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 한다.
  

4.1.8 쿼리 캐시

SQL의 실행 결과를 메모리에 캐시하고, 동일 쿼리가 실행되면 테이블을 읽지 않고 결과를 즉시 반환하므로 매우 빠른 성능을 보였다. 하지만 데이터 변경 시에는 모두 invalidate해야 했고, 이는 심각한 성능 저하를 유발했다. 그래서 8.0 버전부터는 완전히 제거됐다.

4.1.9 스레드 풀

엔터프라이즈 에디션에서만 제공하는 기능이며, 책에서 설명하는 것은 Percona Server에서 제공하는 스레드 풀 기능이다. (플러그인)

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

스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하면 쿼리 처리가 더 느려질 수 있다.

• 스레드풀 관련 변수
  • thread_pool_size 기본적으로 CPU 코어 개수만큼 스레드 그룹을 생성하는데, 이 변수로 조정 가능하다.
    
  • thread_pool_oversubscribe 이미 스레드 풀이 처리 중인 작업이 있는 경우, 이 변수에 설정된 개수만큼 추가로 더 받아들여서 처리한다.
    
  • thread_pool_stall_limit 스레드 풀의 타이머 스레드는 주기적으로 스레드 그룹의 상태를 체크하여, 이 변수에 정의된 밀리초만큼 작업 스레드가 지금 처리 중인 작업을 끝내지 못하면 새로운 스레드를 생성해서 스레드 그룹에 추가한다.
    
  • thread_pool_max_threads 전체 스레드 풀에 있는 스레드의 max 갯수
    

4.1.10 트랜잭션 지원 메타데이터

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

MySQL 서버가 작동하는 데에 필요한 테이블들을 묶어서 시스템 테이블이라 한다. 시스템 테이블, 데이터 딕셔너리 정보를 모두 모아서 mysql DB에 저장한다.(InnoDB 테이블) 이는 mysql.ibd라는 테이블스페이스에 저장된다. 이 파일은 주의해야 한다.

모두 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장되도록 개선되면서, 스키마 변경 작업 중간에 MySQL 서버가 비정상적으로 종료된다고 해도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리된다.

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

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

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

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

4.2.2 외래 키 지원

외래 키 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능이다.

외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 양쪽 테이블에 데이터가 있는지 체크해야하므로 잠금이 여러 테이블로 전파되고, 데드락이 발생할 수 있으므로 각별히 주의해야 한다.

4.2.3 MVCC (Multi Version Concurrency Control)

레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, 잠금을 사용하지 않는 일관된 읽기를 제공한다.

InnoDB는 언두 로그를 이용해 MVCC를 구현한다.

• 원리 update 문장이 실행되면 커밋 실행 여부와 관계없이 InnoDB의 버퍼 풀은 새로운 값으로 업데이트된다. 디스크 데이터 파일에는 체크포인트나 InnoDB의 쓰기 스레드에 의해 새로운 값으로 업데이트돼 있을 수도 있고 아닐 수도 있다. 아직 commit이나 rollback이 되지 않은 상태에서 다른 사용자가 작업 중인 레코드를 조회하면, MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준에 따라 다른 곳에서 데이터를 조회한다. read_committed 이상의 격리 수준이라면 아직 커밋되지 않았기 때문에 언두 영역의 데이터를 반환한다.
  

하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조이다.

커밋이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는 것은 아니며, 이 언두 영역을 필요로 하는 트랜잭션이 더 없을 때 삭제된다.

4.2.4 잠금 없는 일관된 읽기

MVCC를 통해 잠금을 걸지 않고 읽기 작업을 수행한다. 잠금을 걸지 않기 때문에 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 읽기 작업이 가능하다.

4.2.5 자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 데드락 감지를 위해 잠금 대기 목록을 그래프 형태로 관리한다. 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 데드락에 빠진 트랜잭션들을 찾아 그중 하나를 강제 종료한다. (언두 로그가 가장 적은 트랜잭션을 종료함)

InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금은 볼 수 없어 데드락 감지가 불확실할 수 있다. innodb_table_locks 시스템 변수를 활성화하면 모두 볼 수 있다.

동시 처리 스레드가 매우 많으면 데드락 감지 스레드는 더 많은 CPU 자원을 소모할 수 있다. innodb_deadlock_detect 시스템 변수를 제공하며, 끄면 데드락 감지 스레드가 작동하지 않는다. 그러면 데드락 상태에 빠진 트랜잭션이 무한정 대기할 수 있다. innodb_lock_wait_timeout 시스템 변수를 활성화하면 이런 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환한다.

4.2.6 자동화된 장애 복구

InnoDB 스토리지 엔진은 여러 복구 메커니즘을 탑재하고 있고, 매우 견고하다.

InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다. 이 단계에서 자동으로 복구될 수 없는 손상이 있다면 자동 복구를 멈추고 서버는 종료된다.

innodb_force_recovery 시스템 변수를 설정하여, MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 한다.

• innodb_force_recovery 1~6까지 설정할 수 있다. 값이 커질수록 심각한 상황이라 데이터 손실 가능성이 커지고 복구 가능성이 작아진다. 각 숫자 값으로 복구되는 장애 상황과 해결 방법은 책에 잘 나와있다.
  

4.2.7 InnoDB 버퍼 풀

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

쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 한다. 버퍼에 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다.

4.2.7.1 버퍼 풀의 크기 설정

버퍼 풀의 크기는 운영체제와 각 클라이언트 스레드가 사용할 메모리를 충분히 고려해서 설정해야 한다.

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

버퍼 풀의 크기를 변경할 수 있지만, 크기 변경은 크리티컬한 변경이므로 MySQK 서버가 한가한 시점에 진행하는 것이 좋다. 크기를 줄이는 작업은 서비스 영향도가 매우 크므로, 줄이는 작업은 하지 말자.

버퍼 풀은 내부적으로 128MB 청크 단위로 쪼개어 관리된다.

버퍼 풀이 여러 개의 작은 버퍼 풀로 쪼개지면서 개별 버퍼 풀 전체를 관리하는 잠금 자체도 경합이 분산되는 효과가 있다.

4.2.7.2 버퍼 풀의 구조

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

버퍼 풀의 페이지 크기 조각을 관리하기 위해, LRU 리스트, 플러시 리스트, 프리 리스트를 관리한다.

• 프리 리스트 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않는 비어있는 페이지들의 목록
  
• LRU 리스트 (Least Recently Used) LRU와 MRU(Most Recently Used) 리스트가 결합된 형태 LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것이다. 자주 사용된다면 MRU 영역에 살아남고, 거의 사용되지 않는다면 LRU 끝으로 밀려나 버퍼 풀에서 제거된다.
  
• 플러시 리스트 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지의 변경 시점 기준의 페이지 목록을 관리한다. 데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다. InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화한다.
  

4.2.7.3 버퍼 풀과 리두 로그

InnoDB 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정할수록 쿼리 성능이 빨라진다. InnoDB 버퍼 풀은 데이터베이스 서버 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두가지 용도가 있는데, 버퍼 풀의 메모리 공간만 늘리는 것은 데이터 캐시 기능만 향상시킨다.

InnoDB 버퍼 풀은 클린 페이지와 더티 페이지를 갖는다. 더티 페이지는 언젠가 디스크로 기록되어야 한다.

데이터 변경이 계속 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는 새로운 로그 엔트리로 덮어 쓰인다. 그래서 InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능한 공간, 불가능한 공간을 구분해서 관리해야 하는데, 재사용 불가능한 공간이 활성 리두 로그다.

리두 로그 파일의 공간은 계속 재사용되지만 매번 기록될 때마다 로그 포지션은 계속 증가하며, 이를 LSN(Log Sequence Number)라고 한다. 가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN 차이를 체크포인트 에이지라고 하며, 체크포인트 에이지는 활성 리두 로그 공간의 크기다.

4.2.7.4 버퍼 풀 플러시

InnoDB 스토리지 엔진은 버퍼 풀에서 더티 페이지들을 성능 저하 없이 디스크에 동기화하기 위해 플러시 리스트 플러시, LRU 리스트 플러시를 백그라운드에서 실행한다.

• 플러시 리스트 플러시 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 한다. 리두 로그 공간이 지워지려면 InnoDB 버퍼 풀의 더티 페이지가 디스크로 동기화되어야 한다. 이를 위해 주기적으로 플러시 리스트 플러시 함수를 호출한다. 언제부터 얼마나 많은 더티 페이지를 한 번에 디스크로 기록하느냐에 따라 쿼리 처리 성능에 영향을 미치고, 이를 다양한 시스템 변수로 조정할 수 있다.
  
• LRU 리스트 플러시 LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 하고, 이때 LRU 리스트 플러시 함수가 사용된다.
  

4.2.7.5 버퍼 풀 상태 백업 및 복구

버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비돼 있으므로, 디스크에서 데이터를 읽지 않아도 쿼리가 처리될 수 있다. 디스크의 데이터가 버퍼 풀에 적재되어있는 상태를 워밍업이라 한다.

MySQL은 버퍼 풀 덤프, 적재 기능을 제공한다. 서버를 종료하기 전에 백업하고, 서버를 재시작한 후 백업된 버퍼 풀의 상태를 복구할 수 있다.

버퍼 풀의 LRU 리스트에서 적재된 데이터 페이지의 메타 정보만 가져와서 저장하기 때문에 백업 파일의 크기는 작고, 백업은 빠르게 완료된다. 하지만 백업된 버퍼 풀의 내용을 다시 버퍼 풀로 복구하는 과정은 해당 내용을 디스크에서 읽어와야 하므로 시간이 걸린다.

4.2.7.6 버퍼 풀의 적재 내용 확인

information_schema 데이터 베이스에 innodb_cached_indexed 테이블을 이용하면, 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있다.

4.2.8 Double Write Buffer

• Double-write InnoDB 스토리지 엔진의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록한다. 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제가 발생하면 그 페이지의 내용은 복구할 수 없을 수도 있다. 이 문제를 막기 위해 InnoDB 스토리지 엔진은 Double-write 기법을 사용한다.
  
• Double Write Buffer 실제 데이터 파일에 변경 내용을 기록하기 전에 더티 페이지를 묶어서 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록한다. 그리고 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤으로 쓴다. DoubleWrite 버퍼의 내용은 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래 목적으로 사용된다. 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다. 데이터의 무결성이 매우 중요한 서비스에서는 DoubleWrite를 사용하면 좋다.
  

4.2.9 언두 로그

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

• 언두 로그 사용처
  • 트랜잭션 보장 트랜잭션이 롤백됐을 때, 언두 로그에 백업해 둔 이전 버전의 데이터를 이용해 복구한다.
    
  • 격리 수준 보장 트랜잭션 격리 수준에 맞게 언두 로그에 백업해둔 데이터를 읽기도 한다.
    

4.2.9.1 언두 로그 레코드 모니터링

• 대용량의 데이터를 처리하는 트랜잭션뿐만 아니라, 트랜잭션이 오랜 시간 실행될 때도 언두 로그의 양은 급격히 증가할 수 있다. 트랜잭션이 완료됐다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것은 아니다. 사용자가 트랜잭션을 시작한 상태에서 완료하지 않고 하루 정도 방치했을 때, 이 트랜잭션이 시작된 시점부터 생성된 언두 로그가 계속 보존되어 디스크의 언두 로그 저장 공간은 계속 증가한다. 언두 로그가 증가하면 조회할 때마다 언두 로그의 이력을 필요한 만큼 스캔해야하므로 쿼리 성능이 떨어진다.
  
• MySQL 서버의 언두 로그 레코드 양을 항상 모니터링 하는 것이 좋고, 다음 명령으로 언두 로그 레코드 건수를 확인할 수 있다. SHOW ENGINE INNODB STATUS
  
• 서버별로 안정적인 시점의 언두 로그 레코드 건수를 확인해 이를 기준으로 급증 여부를 모니터링하는 것이 좋다.

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

• 언두 로그가 저장되는 공간을 언두 테이블스페이스라고 한다.
• 언두 로그는 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록된다.
• 하나의 언두 테이블스페이스는 1~128개의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯을 가진다. 하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수만큼의 언두 슬롯을 가진다.
• 언두 로그 공간이 남는 것은 크게 문제되지 않지만, 언두 로그 슬롯이 부족한 경우에는 트랜잭션을 시작할 수 없는 심각한 문제가 발생한다.
• 언두 테이블스페이스 공간을 필요한 만큼만 남기고, 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것을 Undo tablespace truncate 라고 한다. 자동, 수동 모드가 있다.
  • 자동 모드 트랜잭션이 커밋되면 더이상 언두 로그에 복사된 이전 값은 불필요해진다. InnoDB 스토리지 엔진의 퍼지 스레드가 주기적으로 언두 로그 공간에서 불필요해진 언두 로그를 삭제한다. 이를 언두 퍼지(undo purge)라고 한다.
    
  • 수동 모드 자동 모드의 성능이 별로일 때, 언두 테이블스페이스를 비활성화해서 언두 테이블스페이스가 더이상 사용되지 않도록 설정하면, 퍼지 스레드는 비활성 상태의 언두 테이블스페이스를 찾아서 불필요한 공간을 잘라내고 운영체제로 해당 공간을 반납한다. 수동 모드는 언두 테이블스페이스가 최소 3개 이상은 돼야 작동한다.
    

4.2.10 체인지 버퍼

레코드가 변경되면, 인덱스도 업데이트해야 한다. 인덱스 업데이트는 디스크를 랜덤하게 읽어야해서 많은 자원을 소모한다. 그래서 체인지 버퍼를 사용한다.

• 체인지 버퍼 변경할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하고, 그렇지 않고 디스크에서 읽어와야 한다면 즉시 실행하지 않고 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다. 이때 사용하는 임시 메모리공간이 체인지 버퍼다.
  
• 유니크 인덱스는 사용자에게 결과를 전달하기 전에 중복 여부를 체크해야하므로 체인지 버퍼를 사용할 수 없다.
• 체인지 버퍼 머지 스레드 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각을 백그라운드에서 병합하는 스레드
  

4.2.11 리두 로그 및 로그 버퍼

• 리두 로그는 트랜잭션의 ACID에서 D에 해당하는 영속성과 밀접하다.
• 대부분의 데이터베이스 서버는 데이터 변경 내용을 로그로 먼저 기록한다. 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요하므로 큰 비용이 필요하다. 이로 인한 성능 저하를 막기 위해 데이터베이스 서버는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 갖는다. 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 서버가 종료되기 직전의 상태로 복구한다.
• 리두 로그도 버퍼링이 된다.
• 서버가 비정상 종료되는 경우, 변경이 커밋됐는지, 롤백됐는지, 트랜잭션 중간 상태였는지 확인하기 위해 리두 로그가 필요하다.
• 트랜잭션이 커밋될 때마다 리두 로그를 디스크에 기록하는 작업은 많은 부하를 유발한다. 그래서 리두 로그를 어느 주기로 디스크에 동기화할지를 결정하는 시스템 변수가 있다.
• 리두 로그 파일들의 전체 크기는 InnoDB 스토리지 엔진이 가지고 있는 버퍼 풀의 효율성을 결정하므로 신중히 결정해야 한다.
• 사용량이 매우 많은 DBMS 서버의 경우, 리두 로그의 기록 작업이 큰 문제가 된다. 이것을 보완하기 위해 최대한 ACID를 보장하는 수준에서 버퍼링한다. 리두 로그 버퍼링에 사용되는 공간이 로그 버퍼다.

4.2.11.1 리두 로그 아카이빙

아직 복사하지 못한 리두 로그가 덮어쓰이면 백업 툴이 리두 로그 엔트리를 복사할 수 없어 백업은 실패한다. 리두 로그 아카이빙 기능은 데이터 변경이 많아서 리두 로그가 덮어쓰인다고 해도 백업이 실패하지 않게 해준다.

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

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

데이터를 복구하거나 대용량 데이터를 한번에 적재하는 경우, 리두 로그를 비활성화해서 데이터 적재 시간을 단축시킬 수 있다.

4.2.12 어댑티브 해시 인덱스

• 어뎁티브 해시 인덱스는 사용자가 수동으로 생성하는 인덱스가 아니라, InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다.
• B-Tree 인덱스에서 특정 값을 찾는 과정은 동시에 몇천 개의 스레드로 실행하면 CPU는 엄청난 프로세스 스케줄링을 하게 되고 쿼리 성능이 떨어진다. 어뎁티브 해시 인덱스는 이러한 B-Tree 검색 시간을 줄여주기 위해 도입된 기능이다. 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어뎁티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다. 그만큼 CPU는 적은 일을 하고, 쿼리 성능은 빨라진다.
  
• 해시 인덱스의 키 값은 ‘B-Tree 인덱스의 고유번호와 B-Tree 인덱스의 실제 키 값’ 조합으로 생성된다. 모든 B-Tree 인덱스에 대한 어뎁티브 해시 인덱스가 하나의 해시 인덱스에 저장되며, 특정 키 값이 어느 인덱스에 속한 것인지 구분해야 하므로, 해시 인덱스의 키 값에 B-Tree 인덱스의 고유번호가 포함된다.
  
• 어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되고, 버퍼 풀에서 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 페이지의 정보는 사라진다.
• 내부 잠금(세마포어) 경합을 줄이기 위해 어댑티브 해시 인텍스의 파티션 기능을 제공한다.
• 어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀) 내에서 접근하는 것을 더 빠르게 만드는 기능이므로, 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 서버에서는 도움이 되지 않는다.
  • 어댑티브 해시 인덱스가 성능 향상에 도움되지 않는 경우
    • 디스크 읽기 많은 경우
    • 특정 패턴의 쿼리가 많은 경우
    • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
  • 어댑티브 해시 인덱스가 성능 향상에 도움이 되는 경우
    • 디스크 읽기가 많지 않은 경우 (디스크 데이터가 버퍼 풀 크기와 비슷한 경우)
    • 동등 조건 검색이 많은 경우
    • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
• 어댑티브 해시 인덱스도 저장 공간인 메모리를 사용한다.
• 어댑티브 해시 인덱스는 테이블의 삭제, 변경 작업에 많은 영향을 준다. 사용하려는 서비스 패턴에 맞게 도움이 되는지 판단하고 사용해야 한다.
  

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

4.3.1 키 캐시

• 키 캐시 InnoDB의 버퍼 풀과 비슷한 역할을 하는 것이 MyISAM의 키 캐시이다. MyISAM 키 캐시는 인덱스만을 대상으로 작동하며, 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 한다.
  
• 키 캐시 히트율

  키 캐시 히트율 = 100 - (Key_reads / Key_read_requests * 100)

  • Key_read : 인덱스를 디스크에서 읽어들인 횟수를 저장하는 상태 변수

  • Key_read_requests : 키 캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수

    히트율이 99% 미만이라면 키 캐시를 조금 더 크게 설정하는 것이 좋다.

• 기본 키 캐시 이외의 명명된 키 캐시 영역은 아무런 설정을 하지 않으면 메모리 할당만 해두고 사용하지 않게 된다. 기본이 아닌 명명된 추가 키 캐시는 어떤 인덱스를 캐시할지 MySQL에 알려줘야 한다.

4.3.2 운영체제의 캐시 및 버퍼

• MyISAM 테이블의 데이터에 대해서는 디스크로부터의 IO를 해결해 줄 만한 어떠한 캐시나 버퍼링 기능도 MyISAM 스토리지 엔진은 가지고 있지 않다. 그래서 MyISAM 테이블의 데이터 읽기나 쓰기 작업은 항상 운영체제의 디스크 읽기 또는 쓰기 작업으로 요청될 수밖에 없다.
• 운영체제의 캐시 공간은 남는 메모리를 사용하는 것이 기본 원칙이다. 데이터베이스에서 MyISAM 테이블을 주로 사용한다면, 운영체제가 사용할 수 있는 캐시 공간을 위해 충분한 메모리를 비워둬야 한다.
  

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

• MyISAM 테이블에 레코드는 프라이머리 키 값과 무관하게 INSERT되는 순서대로 데이터 파일에 저장된다. MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주솟값을 가지는데, 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가진다.
• MyISAM 테이블에서 ROWID는 가변 길이와 고정 길이 두 가지 방법으로 저장될 수 있다.
  • 고정 길이 ROWID MyISAM 테이블이 가질 수 있는 레코드의 개수가 한정되면 MyISAM 테이블은 ROWID 값으로 4바이트 정수를 사용한다.
    
  • 가변 길이 ROWID MyISAM 테이블의 ROWID는 2~7바이트까지 가변적인 ROWID를 갖게 된다.
    

4.4 MySQL 로그 파일

4.4.1 에러 로그 파일

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

• 에러 로그 파일의 종류
  • MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지 MySQL의 설정 파일을 변경하거나 데이터베이스가 비정상적으로 종료된 이후 다시 시작하는 경우에는 반드시 MySQL 에러 로그 파일을 통해 설정된 변수의 이름이나 값이 명확하게 설정되고 의도한 대로 확인해야 한다. 이때 이 메시지를 확인한다.
    
  • 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지 MySQL 서버가 다시 시작되면서 완료되지 못한 트랜잭션을 정리하고 디스크에 기록되지 못한 데이터가 있다면 다시 기록하는 재처리 작업을 한다.
    
  • 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
  • 비정상적으로 종료된 커넥션 메시지
  • InnoDB의 모니터링 또는 상태 조회 명령의 결과 메시지 InnoDB의 모니터링을 활성화해두면 에러 로그 파일이 매우 커진다. 모니터링을 사용한 후에는 다시 비활성화해서 에러 로그 파일이 커지지 않게 해야 한다.
    
  • MySQL의 종료 메시지

4.4.2 제너럴 쿼리 로그 파일

• 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토할 때 사용한다.
• 쿼리 로그를 활성화해서 쿼리를 쿼리 로그 파일로 기록하게 한 다음, 그 파일을 검토하면 된다.
• 시간 단위로 실행됐던 쿼리의 내용이 모두 기록된다.

4.4.3 슬로우 쿼리 로그

• 서비스에서 사용되는 쿼리 중에서 어떤 쿼리가 문제인지를 판단하는 데 슬로우 쿼리 로그가 많은 도움이 된다.
• 슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다. 슬로우 쿼리 로그 파일에 기록되는 쿼리는 일단 정상적으로 실행이 완료됐고 실행하는 데 걸린 시간이 long_query_time에 정의된 시간보다 많이 걸린 쿼리이다.
  
• 슬로우 쿼리 로그를 파일에 기록할지 테이블로 기록할지 선택할 수 있다.
• 슬로우 쿼리 내용
  • Time : 쿼리가 종료된 시점
  • User@Host : 쿼리를 실행한 사용자의 계정
  • Query_time : 쿼리가 실행되는 데 걸린 전체 시간
  • Lock_time : MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간 0이 아니라고 해서 무조건 잠금 대기가 있었다고 판단할 순 없다. 실제 쿼리가 실행되는 데 필요한 잠금 체크와 같은 코드 실행 부분의 시간까지 모두 포함된다. 즉, 매우 작은 값이면 무시해도 된다.
    
  • Rows_examined : 이 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지
  • Rows_sent : 실제 몇 건의 처리 결과를 클라이언트로 보냈는지 Rows_examined의 레코드 건수는 높지만, Rows_sent에 표시된 레코드 건수가 적다면 이 쿼리는 조금 더 적은 레코드만 접근하도록 튜닝해볼 가치가 있다.
    
• Percona Toolkit의 pt-query-digest 스크립트에서 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다. 결과는 다음과 같이 3개의 그룹으로 나뉘어 저장된다.
  • 슬로우 쿼리 통계
  • 실행 빈도 및 누적 실행 시간순 랭킹
  • 쿼리별 실행 횟수 및 누적 실행 시간 상세 정보