4. 아키텍처

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

4.1 MySQL 엔진 아키텍처

• 4.1.1 MySQL의 전체 구조 : C API, JDBC, ODBC, .NET의 표준 드라이버를 제공하여 모든 언어로 MYSQL 서버에서 쿼리를 사용할 수 있게 지원한다.
  • 4.1.1.1 MySQL 엔진 :
    • 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 옵티마이저로 구성됨.
    • 또한 MYSQL은 표준 SQL 문법(ANSI SQL)을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있다.
    • ANSI SQL이란? American National Standards Institute가 각기 다른 DBMS에서 공통으로 사용할 수 있도록 고안한 표준 SQL문 작성방법.
  • 4.1.1.2 스토리지 엔진
    • MYSQL엔진은 요청된 SQL문장을 분석, 최적화하는 DBMS의 두뇌에 해당하는 처리 수행하고 실제 데이터 접근은 스토리지 엔진이 전담한다.
    • MYSQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용 가능하다. 
      EX) CREATE TABLE TEST_TABLE (fd1 INT, fd2 INT) ENGINE=INNODB;
      
    • 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM 스토리지 엔진)나 INNODB 버퍼 풀(INNODB 스토리지 엔진)과 같은 기능 내장.
  • 4.1.1.3 핸들러 API
    • MYSQL 엔진의 쿼리 실행기에서 데이터 접근 시에는 스토리지 엔진에 쓰기 또는 읽기를 요청한다.(=핸들러 요청) 여기서 사용되는 API를 핸들러 API라고 한다. INNODB 스토리지 엔진 또한 이 핸들러 API를 이용해 MYSQL 엔진과 데이터를 주고받는다.
    • SHOW GLOBAL STATUS LIKE 'Handler%';
• 4.1.2 MySQL 스레딩 구조 : MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동함. 크게 포그라운드 스레드, 백그라운드 스레드로 구분 가능. 실행 중인 스레드 목록은 performance_schema 데베의 threads 테이블을 통해 확인 가능.
  thread/sql/one_connection 스레드만 실제 사용자의 요청을 처리하는 포그라운드 스레드이다.
  * MYSQL 커뮤니티 에디션에서 사용되는 모델임. 엔터프라이즈 에디션과 Percona MySQL 서버에서는 스레드 풀 모델을 사용할 수도 있다. 전통적인 스레드 모델은 커넥션과 포그라운드 스레드가 1 : 1 관계. 스레드 풀은 하나의 스레드가 여러 커넥션 요청을 전담한다. 왜? 어떤 원리로 하나의 스레드가 여러 커넥션 요청을 전담 가능해? 4.1.9에 추가 설명?
  
  • 4.1.2.1 포그라운드 스레드(클라이언트 스레드, 사용자 스레드)
    • MYSQL 서버에 접속된 클라이언트의 수만큼 존재. 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
    • 사용자가 커넥션 종료 시 스레드는 다시 스레드 캐시로 되돌아 가는데, 이미 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 한다. (thread_cache_size)
    • 데이터를 MYSQL의 데이터 버퍼나 캐시로부터 가져오며, 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와 작업을 처리한다. MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만 INNODB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.
  • 4.1.2.2. 백그라운드 스레드
    • INNODB는 여러 가지 작업이 백그라운드로 처리된다.
      • 인서트 버퍼를 병합하는 스레드
      • 로그를 디스크로 기록하는 스레드
      • INNODB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
      • 데이터를 버퍼로 읽어 오는 스레드
      • 잠금이나 데드락을 모니터링하는 스레드
    • 5.5 버전 이후 부터 쓰기 스레드, 읽기 스레드를 2개 이상 지정할 수 있게 됨.
    • 대부분 DBMS는 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 있음. =JPA에서 영속성 컨텍스트의 쓰기 저장소에 보관했다가 수정이나 삭제 발생 시 일괄 업데이트 하는 느낌인가?
    • 이러한 이유로 INNODB는 데이터가 변경(INSERT, UPDATE, DELETE)되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다. = 저장 되기 전에 모든 작업이 가능하다. 아니였다면 일일히 저장이 되고 나서야 다른 작업이 가능함. (쓰기는 속도가 느리다. 조회를 하려면 쓰기 이후에 가능하다고 하면 느려짐. 조회 계속하다 마지막에 쓰기 해버리면 됨.)
    • MyISAM은 그렇지 않고 사용자 스레드가 쓰기 작업까지 함께 처리하도록 설계됨. 그래서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다. (지연된 쓰기가 있지만 일반적인 방식은 아님)
• 4.1.3 메모리 할당 및 사용 구조 : 글로벌 메모리 영역, 로컬 메모리 영역으로 구분. 글로벌 메모리 영역은 MYSQL서버가 시작되면서 운영체제로부터 할당되고, MYSQL의 시스템 변수로 설정해 둔 만큼 운영체제로부터 메모리를 할당받는다.
  • 4.1.3.1 글로벌 메모리 영역
    • 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다. 2개 이상 메모리 공간을 할당받더라도 모든 스레드에 의해 공유된다.
    • 테이블 캐시, INNODB 버퍼 풀, INNODB 어댑티브 해시 인덱스, INNODB 리두 로그 버퍼
  • 4.1.3.2 로컬 메모리 영역(=세션 메모리 영역=클라이언트 메모리 영역)
    • 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역.
    • 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되지 않는다.
    • 글로벌 메모리 영역의 크기는 주의하여 설정하지만 소트 버퍼와 같은 로컬 메모리 영역은 크게 신경X, BUT 최악의 경우 메모리 부족날 수 있으니 적절한 메모리 공간 설정 중요함.
    • 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우 MYSQL이 메모리 공간을 할당조차 하지 않을 수 있다. 대표적으로 소트 버퍼나 조인 버퍼와 같은 공간이 그러하다.
    • 커넥션이 열려있는 동안 계속 할당된 상태로 남아있는 공간도 있고(커넥션 버퍼, 결과 버퍼) 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼, 조인 버퍼)도 있다.
    • 정렬 버퍼, 조인 버퍼, 바이너리 로그 캐시, 네트워크 버퍼
    • *버퍼란? 소트버퍼, 조인버퍼, 버퍼 풀, 커넥션 버퍼란??
• 4.1.4 플러그인 스토리지 엔진 모델
  • ? 스토리지 엔진은 플러그인에 포함된다. MYSQL엔진은 포함되나? NO. (플러그인 = 스토리지 엔진)
  • MYSQL 엔진(쿼리 엔진)은 플러그인 인터페이스(핸들러?)를 통해 다양한 플러그인과 상호작용한다. 쿼리 처리 과정에서 필요한 기능을 해당 플러그인에 요청하고 결과를 받아 처리한다.
  • 직접 플러그인을 개발해서 사용할 수 있으며, 인증을 위한 Nativer Authentication, Caching SHA-2 Authentication 등도 모두 플러그인으로 구현되어 제공된다.
  • SQL 파서 <-> SQL 옵티마이저 <-> SQL 실행기 <--핸들러--> 데이터 읽기/쓰기 <-> 디스크
  • ----------MYSQL 엔진 처리 영역--------------------|-------------스토리지 엔진 처리-|
  • 어떤 기능을 호출하기 위해 사용하는 운전대와 같은 역할을 하는 객체 = 핸들러
  • MYSQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야 한다. 스프링의 핸들러 어댑터와 비슷한 역할인듯. 앞선에 핸들러라는 인터페이스를 두어서 원하는 구현체를 찾는 것. 왜냐하면 스토리지 엔진이 매우 많으니까. 찾아갈 수 있는 핸들러가 필요함.
  • GROUP BY나 ORDER BY 등 복잡한 처리는 스토리지 엔진 영역이 아닌 MYSQL엔진의 처리 영역인 '쿼리 실행기'에서 처리된다.
  • MYSQL 서버(mysqld)에서 지원되는 스토리지 엔진 종류. mysqld란?
    • 다양하다. MyISAM, InnoDB, MEMORY, CSV, BLACKHOLE 등
    • Support 칼람에 표시될 수 있는 값
      • YES: MYSQL 서버에 해당 스토리지 엔진이 포함돼 있고, 사용 가능으로 활성화된 상태
      • DEFAULT: YES와 동일한 상태이지만 필수 스토리지 엔진을 의미함. (이 엔진이 없으면 MYSQL이 시작되지 않을 수도 있음을 의미함)
      • NO: 현재 MYSQL 서버(mysqld)에 포함되지 않았음을 의미함. 사용하려면 서버를 다시 빌드(컴파일)해야 한다. 하지만 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워 넣으면 사용 가능하다.
      • DISABLED: 현재 MYSQL 서버에는 포함됐지만 파라미터에 의해 비활성화된 상태
    • 모든 플로그인의 내용은 SHOW PLUGINS 명령으로 스토리지 엔진뿐 아니라 인증, 전문 검색용 파서 같은 플러그인 확인 가능하다.
  • MYSQL 서버에서는 스토리지 엔진 뿐만 아니라 다양한 기능을 플러그인 형태로 지원한다. 인증, 전문 검색 파서, 쿼리 재작성과 같은 플러그인, 비밀번호 검증, 커넥션 제어 등에 관련된 다양한 플러그인 제공된다. 뿐만 아니라 MYSQL 서버의 기능을 커스텀하게 확장할 수 있게 플러그인 API가 매뉴얼에 공개돼있다.
• 4.1.5 컴포넌트
  • MYSQL 서버의 기능을 확장하거나 새로운 기능을 추가하는 모듈식 단
  • 더 현대적이고 일관된 API 제공, 컴포넌트 간의 의존성 관리, 서버 재시작 없이 컴포넌트 로드/언로드 가능
  • MySQL 8.0부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍쳐가 지원된다.
  • 플러그인 아키텍처 단점
    • 플러그인은 오직 MYSQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없음.
    • 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음(캡슐화 안됨)
    • 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움
  • 5.7 버전까지 비밀번호 검증 기능이 플러그인 형태로 제공됐지만 8.0의 비밀번호 검증 기능은 컴포넌트로 개선됐다.
• 4.1.6 쿼리 실행 구조

  

  • 4.1.6.1 쿼리 파서
    • 사용자 요청으로 들어온 쿼리 문장을 토큰(MYSQL이 인식할 수  있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다. 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 오류 발생시킴.
  • 4.1.6.2 전처리기
    • 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 이름이나 칼럼 이름, 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부, 객체의 접근 권한 등을 확인한다.
  • 4.1.6.3 옵티마이저
    • 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할.
  • 4.1.6.4 실행 엔진
    • 옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발이다. 옵티마이저는 경영진, 실행 엔진은 중간 관리자, 핸들러는 각 업무의 실무자이다.
    • 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
  • 4.1.6.5 핸들러(스토리지 엔진)
    • MYSQL 서버의 가장 밑단에서 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다. 핸들러는 결국 스토리지 엔진을 의미하며, MyISAM 테이블 조작할 경우에는 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 된다.
• 4.1.7 복제 : 16장 복제에서 살펴봄.
• 4.1.8 쿼리 캐시
  • SQL 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 즉시 결과를 반환하기에 매우 빠른 성능을 보였다. 하지만 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제해야했다. 심각한 동시 처리 성능 저하를 유발한다. 
  • MySQL 8.0으로 올라오면서 쿼리 캐시는 MySQL 서버의 기능에서 완전히 제거되었다.
  • 그러면 캐시는 어떻게 적용하는데? INNODB 버퍼 풀?, 애플리케이션 수준에서
• 4.1.9 스레드 풀
  • 커뮤니티 에디션은 스레드풀 지원X
  • Percona Server에서 스레드 풀 플러그인 라이브러리(thread_pool.so 파일)을 커뮤니티 에디션에 설치하면 사용 가능
  • 스레드풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MYSQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다.
  • 스케쥴링 과정에서 CPU 시간을 제대로 확보하지 못하면 쿼리 처리가 더 느려질 수 있다.
  • Percona Server의 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성한다. 개수는 thread_pool_size 시스템 변수를 변경해 조정할 수 있다. 일반적으로는 CPU 코어의 개수와 맞추는 것이 CPU 프로세서 친화도를 높이는 데 좋다.
  • 이미 스레드 풀이 처리중인 작업이 있으면 thread_pool_oversubscribe 시스템 변수(기본값 3)에 설정된 개수만큼 추가로 더 받아들여서 처리한다. 이 값이 너무 크면 스케쥴링할 스레드가 많아져 스레드풀이 비효율적으로 작동할 수 있다.
  • 스레드 그룹의 모든 스레드가 일을 처리중이라면 새로운 요청이 들어왔을 때, 해당 스레드 그룹에 새로운 작업 스레드(Worker thread)를 추가할지, 아니면 기존 작업중인 스레드의 작업 완료를 기다릴지 판단해야 한다. 스레드 풀의 타이머 스레드는 주기적으로 스레드 그룹의 상태를 체크헤서 thread_pool_stall_limit 시스템 변수에 정의된 밀리초 내에 작업 스레드가 지금 처리중인 작업을 끝내지 못하면 새로운 스레드를 생성한다. 이때 전체 스레드 풀에 있는 스레드 수는 thread_pool_max_threads 시스템 변수에 설정된 개수를 넘어설 수 없다. 즉, 모든 스레드 그룹의 스레드가 각자 작업을 처리하고 있는 상태에서 새로 쿼리 요청이 들어오더라도 스레드 풀은 thread_pool_stall_limit 시간 동안 무조건 기다려야 한다는 것이다. 응답시간에 아주 민감한 서비스라면 이 시간값을 적절히 낮춰서 설정해야 하지만, 0에 가까운 값으로 설정하는 것은 권장하지 않는다. 이 경우는 스레드 풀을 사용하지 않는 편이 나을 것.
  • Percona Server의 스레드 풀 플러그인은 선순위 큐, 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다. 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해줘서 잠금을 빨리 해제하고 락 경합을 낮춰서 전체적인 처리 성능을 향상시킨다.
• 4.1.10 트랜잭션 지원 메타데이터
  • 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 한다.
  • 5.7 버전까지 테이블 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일(TRN, TRG, PAR)기반으로 관리했다. 하지만 이러한 파일 기반 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블 생성 또는 변경 도중 MYSQL 서버가 비정상적으로 종료되면 일관성이 깨졌다.
  • 8.0 버전부터는 해결을 위해 테이블 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB의 테이블에 저장하도록 개선됐다. MYSQL 서버가 작동하는데 기본적으로 필요한 테이블들을 시스템 테이블(대표적으로 사용자의 인증과 권한에 관련된 테이블들)이라고 하는데, 시스템 테이블들이 모두 InnoDB 스토리지 엔진을 사용하도록 개선했다.
  • 시스템 테이블과 데이터 딕셔너리 정보를 모두 모아서 mysql DB에 저장하고 있다. mysql DB는 통째로 mysql.ibd라는 이름의 테이블스페이스에 저장된다. 그래서 MYSQL의 서버의 데이터 디렉터리에 존재하는 mysql.ibd라는 파일은 다른 *.ibd 파일과 함께 특별히 주의해야 한다.
  • (실제 테이블의 구조가 저장된 테이블은 보이지는 않는다. 수정 못하게 막아놓음) 대신 데이터 딕셔너리 정보를 information_schema DB의 TABLES, COLUMNS 등과 같은 뷰를 통해 조회할 수 있게 하고 있다.
  • 이제 데이터 딕셔너리와 시스템 테이블이 모두 트랜잭션 기반의 InnoDB 스토리지 엔진에 저장됨으로써 일관성 문제를 해결했다.
  • INNODB 스토리지 엔진을 사용하는 테이블은 메타 정보가 INNODB 테이블 기반의 딕셔너리에 저장되지만 MyISAM이나 CSV 등과 같은 스토리지 엔진의 메타 정보는 여전히 저장할 공간이 필요하다. 
  • MYSQL 서버는 INNODB 스토리지 엔진 외의 스토리지 엔진을 사용하는 테이블들을 위해 SDI(Serialized Dictionary Information) 파일을 사용한다. SDI 포맷의 *.sdi 파일이 존재하며 기존의 *.FRM 파일과 동일한 역할을 한다. 그리고 SDI는 직렬화를 위한 포맷이므로 INNODB 테이블들의 구조도 SDI 파일로 변환할 수 있다. ibd2sdi 유틸리티를 이용하면 INNODB 테이블 스페이스에서 스키마 정보를 추출할 수 있다.

 

 

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

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

• 4.2.1 프라이머리 키에 의한 클러스터링
  • 오라클 DBMS의 IOT와 동일한 구조가 INNODB에서 일반적인 테이블의 구조이다.
  • IOT(Index Organized Table): table random access가 발생하지 않도록 처음부터 인덱스 구조로 생성된 테이블.
  • MyISAM 스토리지 엔진은 클러스터링 키를 지원하지 않는다. 모든 인덱스는 물리적인 레코드의 주소값을 가진다
• 4.2.2 외래 키 지원
  • foreign_key_checks 시스템 변수를 통해 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.
  • 반드시 현재 작업을 실행하는 세션에서만 적용해야 한다. 기본값: SESSION. GLOBLA도 설정 가능.
• 4.2.3 MVCC(Multi Version Concurrency Control)
  • 잠금을 사용하지 않는 일관된 읽기를 제공하는 목적.
  • 일반적으로 InnoDB의 버퍼 풀과 데이터 파일(디스크)는 동일한 상태이다. INNODB가 ACID를 보장하기 때문.
  • READ_UNCOMMITTED인 경우 변경된 데이터를 읽고, 그 이상 격리 수준은 모두 언두 영역의 데이터를 반환한다
  • 트랜잭션이 길어지면 언두에서 관리되는 데이터는 매우 많아지고, 언두 영역이 저장되는 시스템 테이블 스페이스의 공간이 많이 늘어나는 상황이 발생할 수 있다.
  • 커밋이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는 것은 아니다. 언두 영역을 필요로 하는 트랜잭션이 없을 때 삭제된다.
• 4.2.4 잠금 없는 일관된 읽기(Non-Locking Consistent Read)
  • SERIALIZABLE이 아닌 격리 수준인 경우 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계 없이 항상 잠금을 대기하지 않고 바로 실행된다.
  • 트랜잭션이 오랜 시간 활성 상태이면 언두 로그를 삭제하지 못하기 때문에 가능한 한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.
• 4.2.5 자동 데드락 감지
  • InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있다.
  • 교착 상태에 빠지면 언두 로그 레코드를 더 적게 가진 트랜잭션을 일반적으로 롤백한다.
  • InnoDB 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금은 볼 수가 없어서 데드락 감지가 불확실할 수 있는데, innodb_table_locks 시스템 변수 사용하면 테이블 레벨 잠금까지 감지 가능하다.
  • 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려진다. 왜냐하면 잠금 목록이 저장된 리스트를 다시 잠금을 걸어서 데드락 스레드를 찾기 때문이다. 
  • 데드락 감지 스레드가 느려지면 서비스 쿼리 처리 스레드는 진행을 못하게 되고 악영향을 미친다. 더 많은 cpu 자원 소모함.
  • 이 문제를 해결하기 위해 MySQL 서버는 innodb_deadlock_detect 를 OFF로 설정해 데드락 감지 스레드를 작동 안시킬 수 있다. 하지만 데드락이 발생할 수 있는데, innodb_lock_wait_timeout 을 활성화 시켜서 데드락 상황에서 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환시킬 수 있다.
  • PK 기반의 조회 및 변경이 매우 높은 빈도로 실행되는 서비스가 많으면 데드락 감지 스레드로 인해 성능 저하가 발생할 수 있다. detect 를 ON, OFF로 둘 다 해보고 성능을 비교해보는 것도 좋다.
• 4.2.6 자동화된 장애 복구
  • INNODB 스토리지 엔진은 매우 견고해서 웬만해선 데이터 파일이 손상되거나 MYSQL 서버가 시작되지 못하는 경우는 거의 없다. 하지만 MYSQL 서버와 무관하게 디스크나 서버 하드웨어 이슈로 INNODB 엔진이 자동으로 복구를 못하는 경우도 발생할 수 있다. INNODB 데이터 파일은 기본적으로 MYSQL 서버가 시작될 때 항상 자동 복구를 수행하는데(완료되지 못한 트랜잭션이나 디스크에 일부만 기록된(Partial write) 데이터 페이지 등), 복구될 수 없는 손상이 있다면 복구를 멈추고 서버가 종료된다.
  • 이때, innodb_force_recovery 를 설정해서 MySQL 서버를 시작하면 서버가 시작될 때 INNODB 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 한다. (값이 커질수록 심각한 상황)
  • 일단 MYSQL 서버가 기동되고 INNODB 테이블이 인식된다면 mysqldump를 이용해 데이터를 가능한 만큼 백업하고 db와 테이블을 다시 생성하는 것이 좋다.
  • innodb_force_recovery가 0이 아닌 복구 모드에서는 데이터 변경은 불가능하다.
  • 1(SRV_FORCE_IGNORE_CORRUPT): INNODB의 테이블스페이스의 데이터나 인덱스페이지에서 손상된 부분이 발견돼도 무시하고 시작한다. 에러 로그가 Database page corruption on disk or a failed 메시지 출력
  • 2(SRV_FORCE_NO_BACKGROUND): 백그라운드 스레드 중 메인 스레드를 시작하지 않고 시작한다. INNODB의 메인 스레드가 언두 데이터를 삭제하는 과정에서 장애가 발생할 시 사용
  • 3(SRV_FORCE_NO_TRX_UNDO): 커밋되지 않은 트랜잭션의 작업을 롤백하지 않고 서버를 재시작한다.
  • 4(SRV_FORCE_NO_IBUF_MERGE): INNODB는 인덱스 변경 작업을 즉시 처리할 수도, 인서트 버퍼에 저장 후 나중에 처리할 수도 있다. MYSQL을 종료해도 병합되지 않을 수 있는데, MYSQL이 재시작되면서 인서트 버퍼의 손상을 감지하면 INNODB는 에러를 발생시키고 서버가 시작되지 못한다. 4로 설정하면 인서트 버퍼의 내용을 무시하고 강제로 MYSQL이 시작되게 한다. 인서트 버퍼는 실 데이터가 아닌 인덱스에 관련된 부분이다.
  • 5(SRV_FORCE_NO_UNDO_LOG_SCAN): INNODB의 언두 로그를 사용할 수 없으면 INNODB 엔진의 에러로 MYSQL 서버 시작 불가능. 5로 설정 시 언두 로그를 모두 무시하고 시작할 수 있다. 하지만 이 모드로 복구하면 종료되던 시점에 커밋되지 않은 작업이 모두 커밋된 것처럼 처리되므로 데이터 정합성 문제 발생.
  • 6(SRV_FORCE_NO_LOG_REDO): 리두 로그를 모두 무시한 채 서버 시작된다. 커밋됐다 하더라도 리두 로그에만 기록되고 데이터 파일에 기록되지 않은 데이터는 모두 무시된다. 즉, 마지막 체크포인트 시점의 데이터만 남게된다.
  • *COMMIT시 바로 데이터 파일에 기록되는 게 아니라 리두 로그 버퍼에 먼저 저장된다. 이후 일정 주기로 디스크의 리두 로그 파일로 플러시됨.
  • * 체크포인트 발생 시점: 일정 주기, 리두 로그 파일이 특정 비율로 차, INNODB 버퍼 풀의 더티 페이지가 일정 비율을 넘기면, 서버 종료 시
    
  • 백업이 있다면 INNODB 복구 이용하는 것보다 백업과 바이너리 로그를 사용해 더 좋을 수 있다.
• 4.2.7 InnoDB 버퍼 풀: InnoDB 스토리지 엔진에서 가장 핵심적인 부분. 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간. 쓰기 지연 역할인 버퍼 역할도 같이 한다. = 랜덤한 디스크 작업의 횟수를 줄일 수 있다.
  • 4.2.7.1 버퍼 풀의 크기 설정
    • INNODB 버퍼 풀 VS 레코드 버퍼?
    • 레코드 버퍼는 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간이다. 별도로 설정할 수 없으며 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블의 개수에 따라서 결정된다. = 정확히 필요한 메모리 공간의 크기를 계산할 수 없다.
    • 그래서 INNODB 버퍼 풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 보며 증가시키는 것이 최적.
    • InnoDB 버퍼 풀은 innodb_buffer_pool_size 로 크기 설정 가능하고 동적으로 확장 가능하다. 크게 변경하는 작업은 시스템 영향도가 크지 않지만 줄이는 작업은 서비스 영향도가 매우 크다.(안하는 것이 베스트) -왜?
    • 버퍼 풀 전체를 관리하는 잠금(세마포어)로 내부 잠금 경합을 많이 유발했었는데, 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선됨. innodb_buffer_pool_instances로 관리 가능. 각 버퍼 풀을 버퍼 풀 인스턴스라 함. 개당 메모리 5GB 정도가 좋다.
  • 4.2.7.2 버퍼 풀의 구조
    • 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 나누어서 데이터를 저장한다.
    • 페이지 크기 조각을 관리하기 위해 LRU 리스트, 플러시 리스트, 프리 리스트라는 3개의 자료구조를 관리한다.
    • 프리 리스트는 INNODB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않는 비어 있는 페이지들의 목록이다.
    • LRU 리스트는 LRU(Old 서브리스트) + MRU(New 서브리스트)의 결합이다.
      • INNODB 엔진에서 데이터를 찾는 과정
        1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
           • INNODB 어댑티브 해시 인덱스를 이용해 페이지 검색
           • 해당 테이블의 인덱스(B-TREE)를 이용해 버퍼 풀에서 페이지를 검색
           • 버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
        2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
        3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동(대량 읽기의 경우 버퍼 풀로 적재는 되지만 실제 쿼리에서 사용되지 않을 수 있으니 MRU로 이동 안함)
        4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이로 관리된다. 나이 많아지면 제거되고 사용되면 젊어진다.
        5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가
    • 플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록을 관리한다. 
    • 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결된다. (데이터 변경시 변경 내용을 리두로그에 기록하고 버퍼 풀의 데이터 페이지에도 반영해야 해서) 
    • 하지만 항상 보장하지는 않는다. 위의 4가지 조건에 안맞을 시 반영 안될 수 있음. 그래서 INNODB 엔진은 체크포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화하게 된다. (위의 체크포인트 4가지 조건에 맞춰서 동기화 진행)
  • 4.2.7.3 버퍼 풀과 리두 로그
    • 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐시 기능만 향상시키는 것이다.
    •  

      

    • 버퍼 풀은 클린 페이지 + 더티 페이지가 존재함. 
    • 순환 고리로 계속 재사용하는데, 재사용 불가능 공간을 활성 리두 로그라고함(화살표 가진 리두 로그)
    • 로그 포지션은 계속 증가하는데 이 값을 LSN(Log Sequence Number)라고 함. 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 된다. = 계속해서 증가하는 값.
    • 가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지라고 한다. = 활성 리두 로그 공간의 크기
    • 버퍼 풀 100GB + 리두 로그 파일 100MB = 100MB 정도의 더티 페이지만 버퍼 풀에 보관 가능. 비효율
    • 버퍼 풀 100MG + 리두 로그 파일 100GM = 버퍼 풀에 더티 페이지 일정 비율 이상 될 시 급작스러운 디스크 쓰기 발생함. 성능 저하
    • 일반적으로 리두 로그는 변경분만 가지고 버퍼 풀은 데이터 페이지를 통째로 가지기 때문에 리두 로그는 훨씬 작은 공간만 있으면 된다.
  • 4.2.7.4 버퍼 풀 플러시(Buffer Pool Flush): INNODB 엔진은 더티 페이지들을 성능 상 악영향 없이 디스크에 동기화 하기 위해 2개의 플러시 기능을 백그라운드로 실행한다.
    • 4.2.7.4.1 플러시 리스트 플러시
      • INNODB 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 한다. 지우려면 더티 페이지가 먼저 동기화가 돼야함. 플러시 리스트 플러시 함수를 호출해서 동기화 작업을 수행한다. 이때 언제부터, 얼마나 많은 더티 페이지를 한 번에 디스크에 기록하냐에 따라 사용자의 쿼리 처리가 악영향을 받지 않으면서 부드럽게 처리된다.
      • innodb_page_cleaners: 클리너 스레드(더티 페이지를 디스크로 동기화하는 스레드)의 개수를 조정
      • innodb_page_cleaners 설정값이 버퍼 풀 인스턴스 개수보다 많은 경우에는 innodb_buffer_pool_instances 설정값으로 자동으로 변경한다. 즉, 하나의 클리너 스레드가 하나의 버퍼 풀 인스턴스를 처리하도록 자동으로 맞춘다. 적은 경우에는 하나의 클리너 스레드가 여러 개의 버퍼 풀 인스턴스를 처리한다. 가능하면 동일하게 설정하자.
      • innodb_max_dirty_pages_pct:  기본적으로 INNODB 엔진은 90%까지 더티 페이지를 가질 수 있는데, 이 설정을 통해 비율을 조정할 수 있다. 일반적으로 더티 페이지를 많이 가지고 있을 수록 디스크 쓰기 작업을 버퍼링하면서 성능 향상을 할 수 있다.
      • *의문*: INNODB 스토리지 엔진은 캐싱 + 쓰기 지연 역할을 하는데 90% 더티 페이지를 가진다면 나머지 10%를 캐싱 기능에 쓰는 건가?
      • 더티 페이지가 많을 수록 디스크 쓰기 폭발(Disk IO Burst) 현상 발생 가능성 높아진다. innodb_io_capacity에 설정된 값을 기준으로 더티 페이지 쓰기를 실행한다. 하지만 디스크로 기록되는 더티 페이지 개수보다 더 많은 더티 페이지가 발생하면 스토리지 엔진은 더티 페이지를 디스크에 기록해야 한다고 판단하여 디스크 쓰기가 폭증한다. 이를 완화하기 위해 innodb_max_dirty_pages_pct_lwm 설정 변수를 이용해 일정 수준 이상의 더티페이지 발생 시 조금씩 더티 페이지를 기록하게 한다. 
      • innodb_io_capacity_(max): 각 데베 서버에서 어느 정도의 디스크 읽고 쓰기가 가능한지 설정하는 값. max은 디스크가 최대의 성능을 발휘할 때 어느 정도 가능한지 설정한다. 하지만 설정된 값 그대로 보장하는 게 아니라, 적절히 설정해가면서 분석하고 패턴을 익혀야 한다.
      • 하지만 위와 같은 과정은 상당히 번거로울 수 있다. 그래서 스토리지 엔진은 어댑티브 플러시(Adaptive flush) 기능을 제공한다. innodb_adaptive_flushing으로 켜고 끌 수 있는데, 기본값은 사용하는 것이다. 이를 사용하면 위의 비율, capacity를 사용하지 않고 새로운 알고리즘을 사용한다. 리두 로그의 증가 속도를 분석해서 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행한다. innodb_adaptive_flushing_lwm의 기본값은 10%이다. (10% 넘어가면 알고리즘 작동함)
      • innodb_flush_neighbors는 더테 페이지를 기록할 때 디스크에 근접한 페이지 중 디스크에서 근접한 페이지 중에서 더티 페이지가 있다면  스토리지 엔진이 함께 묶어서 디스크로 기록하게 해주는 기능을 활성화할지 결정한다. 하드디스크로 저장하면 활성화하는 것이 좋다. 하지만 요즘은 SSD로 하기 때문에 기본값인 비활성 모드 유지가 좋다.
    • 4.2.7.4.2 LRU 리스트 플러시
      • LRU 리스트에서 사용 빈도가 낮은 데이터 페이지 제거할 때 사용하는 함수
      • 스토리지 엔진은 LRU 리스트 끝부분부터 시작해서 최대 innodb_lru_scan_depth 설정된 개수만큼 스캔하면서 더티 페이지는 동기화하고, 클린 페이지는 프리 리스트로 페이지를 옮긴다. 
      • 버퍼 풀 인스턴스 별로 실행하기 때문에 실질적으로 LRU 리스트의 스캔은 innodb_buffer_pool_instances *  innodb_lru_scan_depth 수만큼 수행한다. 
  • 4.2.7.5 버퍼 풀 상태 백업 및 복구
    • 워밍업: 디스크의 데이터가 버퍼 풀에 적재돼 있는 상태
    • innodb_buffer_pool_dump_now 사용해서 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있다.
    • 서버 다시 시작할 때 innodb_buffer_pool_load_now로 복구할 수 있다.
    • 백업 파일은 ib_buffer_pool 이름의 파일로 생성되는데 용량이 매우 작다. 이는 스토리지 엔진이 버퍼 풀의 LRU 리스트에서 적재된 데이터 페이지의 메타 정보만 가져와서 저장하기 때문이다.
    • 백업은 빠르지만, 버퍼 풀의 크기에 따라 상당한 시간이 걸릴 수 있어서 복구가 실행중인 상태에서 서비스를 재개하는 것은 좋지 않는 선택일 수 있다.
    • 위의 내용은 수동이고, 잊어버리기 쉬우니 자동으로 복구를 진행하려면 innodb_buffer_pool_dump_at_shutdown과 innodb_buffer_pool_load_at_startup 설정을 MYSQL 서버의 설정 파일에 넣어두면 된다. (반드시 셧다운 직전의 파일일 필요는 없음)
  • 4.2.7.6 버퍼 풀의 적재 내용 확인
    • 8.0버전 부터 버퍼 풀 메모리 페이지들 조회 시 부하 발생을 막기 위해 information_schema 데베에 innodb_cached_indexes 테이블이 새로 추가되었다. 응용하면 테이블 전체(인덱스 포함) 페이지 중에서 대략 어느 정도 비율이 INNODB 버퍼 풀에 적재돼 있는지 확인해 볼 수 있다.
• 4.2.8 Double Write Buffer
  • 하드웨어 오작동이나 시스템 비정상 종료로 인해 파셜 페이지 또는 톤 페이지가 발생할 수 있다. = 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 현상.
  • 이를 막기 위해 Double-Write 기법 사용한다.
  • 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래의 목적으로 사용된다. 만약 비정상적인 종료로 다 쓰기 못했을 때, 스토리지 엔진은 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지 존재 시 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다.  사용할지 여부는innodb_doublewrite 변수로 제어할 수 있다.
  • HDD처럼 자기 원판이 회전하는 저장 시스템에서는 어차피 한 번의 순차 디스크 쓰기이기 때문에 별로 부담이 안되지만 SSD처럼 랜덤 IO나 순차 IO의 비용이 비슷한 저장 시스템에서는 상당히 부담스럽다(DoubleWrite는 순차 쓰기이고 결국 두 번 쓰는건데, HDD는 어차피 순차 쓰기니까 크게 부담X이지만 SSD는 랜덤ID와 순차 IO의 비용이 적은데 DW를 위해 순차 쓰기와 두 번 쓰기를 하면 HDD보다 크게 부담됨) . 데이터의 무결성이 매우 중요한 서비스에서는 DoubleWrite의 활성화를 고려하는 것이 좋다.
  • 만약 서버 성능을 위해 리두 로그 동기화 설정을 비활성화했다면 DoubleWrite도 비활성화 하는 것이 좋다.
  • 리두 로그는 동기화 하지 않으면서(innodb_flush_log_at_trx_commit 변수가 1이 아닌 경우) DoubleWrite만 활성화 하는 것은 잘못된 선택이다.
  • 리두 로그 동기화 + DoubleWrite 활성화할거면 리두 로그를 동기화 같이 해라.
• 4.2.9 언두 로그: 트랜잭션 보장, 격리 수준 보장
  • 4.2.9.1 언두 로그 모니터링
    • 트랜잭션의 롤백 대비용
    • 트랜잭션의 격리 수준을 유지하면서 높은 동시성 제공
    • 5.5 이전 버전에서 한 번 증가한 언두 로그 공간은 다시 줄어들지 않았음 = 대용량 데이터 처리하는 트랜잭션이나 트랜잭션이 오랜 시간 실행되면 언두 로그의 양 급격히 증가.(트랜잭션 완료되었다고 언두로그 즉각 삭제 가능하지 않음)
    • 누적된 언두 로그로 인해 디스크의 사용량이 증가하는 것은 큰 문제가 아닐 수 있지만 빈번하게 변경된 레코드를 조회하는 쿼리가 실행되면 언두 로그의 이력을 필요한 만큼 스캔해야해서 성능 저하 발생함. 또한 백업할 때도 매번 그만큼 더 복사를 해야 함.
    • 8.0 버전부터는 언두로그를 돌아가면서 순차적으로 사용해 디스크 공간을 줄이고 때로는 MYSQL 서버가 필요한 시점에 사용 공간을 자동으로 줄여준다.
    • 하지만 여전히 활성 상태의 트랜잭션이 장시간 유지되는 것은 성능상 좋지 않기에 항상 얼마나 증가했는지 모니터링이 필요하다.
    • MYSQL 서버에서 INSERT 문장으로 인한 언두 로그와 UPDATE, DELETE 문장으로 인한 언두 로그는 별도로 관리된다. UPDATE, DELETE 언두 로그는 MVCC와 데이터 복구(롤백 포함)에 모두 사용되지만, INSERT 언두 로그는 MVCC를 위해서는 사용되지 않고 데이터 복구와 롤백을 위해서만 사용된다. -> INSERT는 Version이 필요없다. 롤백되면 그저 언두 로그의 값으로 갈아끼우면 되니까. 버전 선택 느낌이 아니다.
  • 4.2.9.2 언두 테이블스페이스 관리
    • 언두 테이블스페이스란 언두 로그가 저장되는 공간이다.
    • 5.6 이전 버전에서는 언두 로그가 모두 시스템 테이블스페이스에 저장됐다. 하지만 시스템 테이블스페이스의 언두 로그는 MYSQL 서버가 초기화될 때 생성되기 때문에 확장의 한계가 존재했다.
    • 8.0부터 언두 로그는 항상 시스템 테이블스페이스 외부의 별도 로그 파일에 기록되도록 개선됐다.
    • 하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬록을 가진다.
    • 하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수만큼의 언두 슬롯을 가진다. (페이지 크기가 16KB라면 하나의 롤백 세그먼트는 1024개의 언두 슬롯을 갖게 된다.)
    • 하나의 트랜잭션이 필요로 하는 언두 슬롯의 개수는 최대 4개이고, 일반적으로 대략 2개의 언두 슬롯을 필요로 한다.
    • 최대 동시 처리 가능한 트랜잭션의 개수: (InnoDB 페이지 크기) / 16 * 롤백 세그먼트 개수 * 언두 테이블스페이스 개수
    • 언두 로그 공간이 남는건 문제없지만 부족하면 트랜잭션을 시작할 수 없는 문제가 발생한다.
    • Undo tablespace truncate: 언두 테이블스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것.
      • 자동 모드: InnoDB 스토리지 엔진의 퍼지 스레드는 주기적으로 불필요해진 언두 로그를 삭제하는 작업을 실행함.(언두 퍼지) MYSQL 서버의 innodb_undo_log_truncate 시스템 변수가 ON이면 실행한다. innodb_purge_rseg_truncate_frequency 로 더 빈번, 덜 빈번 실행 조정 가능.
      • 수동 모드: innodb_undo_log_truncate 시스템 변수가 OFF거나 예상보다 자동모드로 언두 테이블스페이스의 공간 반납이 부진하면 언두 테이블스페이스를 비활성화해서 더이상 사용되지 않도록 설정하면 퍼지 스레드는 비활성 상태의 언두 테이블스페이스를 찾아서 불필요한 공간을 잘라내고 OS에 해당 공간을 반납한다. 반납 완료하면 다시 활성화한다. 언두 테이블스페이스가 최소 3개 이상은 돼야 작동한다.
• 4.2.10 체인지 버퍼
  • 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트하지만, 디스크에서 읽어온다면 즉시 실행하지 않고 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상하는데, 이 임시 공간이 체인지 버퍼이다.
  • 사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다.
  • 체인지 버퍼에 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드라고 한다.
  • 5.5 버전까지는 INSERT 작업에만 이러한 버퍼링이 가능했는데, 8.0 부터는 INSERT, DELETE, UPDATE로 인해 키를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 될 수 있게 개선됐다.
  • innodb_change_buffering 시스템 변수로 작업의 종류별로 체인지 버퍼를 활성화할 수 있다.
• 4.2.11 리두 로그 및 로그 버퍼: 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MYSQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치. 대부분의 데베 서버는 데이터 변경 내용을 로그로 먼저 기록한다.(WAL로그, Write Ahead Log) 데이터 파일 쓰기(디스크 랜덤 액세스)의 성능 저하를 막기 위해 데베 서버는 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있음. 또한 리두 로그를 버퍼링할 수 있는 InnoDB 버퍼 풀이나 리두 로그를 버퍼링할 수 있는 로그 버퍼와 같은 자료 구조도 가지고 있다.
  1. 커밋됐지만 데이터 파일에 기록되지 않은 데이터: 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 됨.
  2. 롤백됐지만 데이터 파일에 이미 기록된 데이터: 변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사하면 된다. 그 변경이 커밋,롤백,트랜잭션의 실행 중간 상태였는지 확인하기 위해서 리두 로그가 필요하다.
  하지만 트랜잭션이 커밋될 때마다 리두 로그를 디스크에 기록하는 작업은 많은 부하를 유발한다. 그래서 어느 주기로 리두 로그를 디스크에 동기화할지를 결정하는 innodb_flush_log_at_trx_commit 시스템 변수를 제공한다.
  0: 1초에 한 번씩 리두 로그를 디스크로 기록하고 동기화 실행. 서버가 비정상 종료되면 최대 1초 동안의 트랜잭션에서 변경한 데이터 사라질 수 있다.
  1: 트랜잭션이 커밋될 때마다 디스크로 기록되고 동기화 실행. 트랜잭션이 일단 커밋되면 해당 트랜잭션에서 변경한 데이터 사라진다.
  2: 트랜잭션이 커밋될 때마다 디스크로 기록은 되지만 동기화는 1초마다 실행된다. 일단 커밋되면 변경 내용이 OS의 메모리 버퍼로 기록되는 것이 보장된다. MYSQL 서버가 비정상 종료됐더라도 OS가 정상 작동한다면 데이터 사라지지 않는다. MYSQL 서버, OS가 모두 비정상 종료되면 최근 1초 동안의 트랜잭션 데이터 사라질 수 있다.
  리두 로그 파일들의 전체 크기는 버퍼 풀의 효율성을 결정한다. 최대한 ACID 속성을 보장하는 수준에서 버퍼링한다. 리두로그 버퍼링에 사용되는 공간이 로그 버퍼이다. 로그 버퍼의 크기는 16MB 수준이 적합하다. BLOB, TEXT는 더 크게.
  
  • 4.2.11.1 리두 로그 아카이빙
    • 8.0부터 스토리지 엔진의 리두 로그를 아카이빙할 수 있는 기능이 추가되었다.
    • 이 기능은 데이터 변경이 많을 때 리두 로그가 덮어쓰인다고 하더라도 백업이 실패하지 않게 해준다.
    • 세션이 계속 연결되어 있어야 이 기능이 정상 작동한다.
  • 4.2.11.2 리두 로그 활성화 및 비활성화
    • 리두 로그는 항상 활성화되어 있다. MYSQL 서버에서 트랜잭션이 커밋돼도 데이터 파일은 즉시 디스크로 동기화되지 않는 반면, 리두 로그(트랜잭션 로그)는 항상 디스크로 기록된다.
    • 8.0부터 수동으로 리두 로그를 활성화/비활성화할 수 있다.
• 4.2.12 어댑티브 해시 인덱스
  • InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스. 활성화/비활성화 가능하다. 
  • B-Tree 인덱스는 빠르더라도 몇천 개의 스레드로 실행하면 CPU는 엄청난 프로세스 스케줄링을 하게 되고 쿼리의 성능은 떨어진다.
  • 이를 보완하기 위해 어댑티브 해시 인덱스가 나왔다. 자주 읽히는 데이터 페이지으 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다.
  • 인덱스 키 값과 해당 인덱스 키 값이 저장된 데이터 페이지 주소의 상으로 관리된다.
  • 인덱스 키 값은 B-Tree 인덱스의 고유번호와 B-Tree 인덱스의 실제 키 값의 조합이다.
  • 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리된다.
  • B-Tree의 루트 노드부터 검색이 많이 줄면서 InnoDB 내부 잠금(세마포어)의 횟수도 획기적으로 줄어든다.
  • 이전 버전에는 어댑티브 해시 인덱스는 하나의 메모리 객체인 이유로 경합이 상당히 심했다. 8.0부터는 경합을 줄이기 위해 파티션 기능을 제공한다. (innodb_adaptive_hash_index_parts) 기본값은 8개.
  • 성능 향상에 크게 도움이 되지 않는 경우
    • 디스크 읽기가 많은 경우
    • 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
    • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
  • 도움이 되는 경우
    • 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
    • 동등 조건 검색(동등 비교와 IN 연사자)이 많은 경우
    • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
  • 어댑티브 해시 인덱스 또한 저장공간인 메모리를 사용하고 때로는 상당히 큰 메모리 공간을 사용할 수 있다. 적용된 테이블을 삭제하거나 변경하려고 하면 테이블이 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거해야 한다.
  • 어댑티브 해시 인덱스의 도움을 많이 받을수록 테이블 삭제 또는 변경작업은 더 치명적이다.
  • 기본적으로 활성화되어있다.
• 4.2.13 InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교
  • 8.0 버전부터 MySQL 서버의 모든 시스템 테이블이 InnoDB 스토리지 엔진으로 교체됐고, 공간 좌표 검색이나 전문 검색 기능이 모두 InnoDB 스토리지 엔진을 지원하도록 개선됐다. MYSQL 서버의 모든 기능을 InnoDB 스토리지 엔진만으로 구현할 수 있게됨.
  • MEMORY 스토리지 엔진은 하나의 스레드인 경우에는 빠를 수 있지만, 요즘 추세인 동시 처리 성능을 관점에서는 MEMORY 스토리지 엔진은 테이블 수준의 잠금으로 인해 제대로 된 성능을 내지 못할 것이다.
  • MYSQL 서버는 사용자의 쿼리를 처리하기 위해 내부적으로 임시 테이블을 사용할 수도 있다. 하지만 MEMORY 스토리지 엔진은 가변 길이 타입의 컬럼을 지원하지 않기에 8.0 부터는 TempTable 스토리지 엔진이 MEMORY 스토리지 엔진을 대체해 사용중이다.

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

• 4.3.1 키 캐시
  • InnoDB의 버퍼 풀과 비슷한 역할을 한다.
  • 하지만 이름대로 인덱스만을 대상으로 작동한다.
  • 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 한다.
• 4.3.2 운영체제의 캐시 및 버퍼
  • 인덱스는 디스크를 검색하지 않고도 충분히 빠르게 검색할 수 있다.
  • 하지만 MyISAM 테이블의 데이터에 대해서는 디스크로부터 IO를 해결해줄 어떠한 캐시나 버퍼링 기능도 MyISAM 스토리지 엔진은 가지고 있지 않다. 그래서 항상 데이터 읽기나 쓰기 작업은 운영체제의 디스크 읽기 또는 쓰기 작업으로 요청될 수 밖에 없다.
  • OS의 캐시 기능은 InnoDB처럼 데이터의 특성을 알고 전문적으로 캐시나 버퍼링을 하지는 못하지만 없는 것보다는 낫다. 남는 메모리를 사용하는 것이 기본 원칙이다. MyISAM 테이블을 주로 사용한다면 OS가 사용할 수 있는 캐시 공간을 위해 충분한 메모리를 비워둬야 된다.
• 4.3.3 데이터 파일과 프라이머리 키(인덱스) 구조
  • 데이터 파일이 힙공간처럼 활용된다. INSERT되는 순서대로 데이터 파일에 저장된다. MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주솟값을 가지는데, PK키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가진다.
  • 고정 길이 ROWID: MAX_ROWS 옵션을 명시하면 MYSQL 서버는 최대로 가질 수 있는 레코드가 한정된 테이블을 생성한다.
  • 가변 길이 ROWID: 최대 myisam_data_pointer_size 에 설정된 바이트 수만큼의 공간 사용 가능하다. 기본값 7.

4.4 MySQL 로그 파일

MYSQL 서버에 문제가 생겼을 때는 로그 파일들을 자세히 확인하는 습관을 들일 필요가 있다.

• 4.4.1 에러 로그 파일: MYSQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일이다. MYSQL 설정 파일(my.cnf)에서 log_error라는 이름의 파라미터로 정의된 경로에 생성된다. 설정 파일에 별도로 정의되지 않은 경우에는 데이터 디렉터리(datadir 파라미터에 설정된 디렉터리)에 .err라는 확장자가 붙은 파일로 생성된다.
  • 4.4.1.1 MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
    • MYSQL의 설정 파일을 변경하거나 데베가 비정상적으로 종료된 이후 다시 시작하는 경우에는 반드시 MYSQL 에러 로그 파일을 통해 설정된 변수의 이름이나 값이 명확하게 설정되고 의도한 대로 적용됐는지 확인해야 한다.
  • 4.4.1.2 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지
    • InnoDB의 경우에 MYSQL 서버가 비정상적 또는 강제적으로 종료됐다면 다시 시작하면서 완료되지 못한 트랜잭션을 정리하고 디스크에 기록되지 못한 데이터가 있다면 다시 기록하는 재처리 작업을 한다. 이 과정에서 문제가 있어서 복구되지 못하면 에러를 출력하고 서버는 종료된다.
  • 4.4.1.3 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
    • 쿼리 도중 발생하는 문제는 사전 예방이 어려우며, 주기적으로 에러 로그 파일을 검토하는 과정에서 알게된다. 자주 에러 로그 파일을 검토하는 것이 데베의 숨겨진 문제점을 해결하는 데 많이 도움될 것이다.
  • 4.4.1.4 비정상적으로 종료된 커넥션 메시지(Aborted connection)
    • 클라이언트 애플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우 MYSQL 서버의 에러 로그 파일에 이런 내용이 기록된다.
    • 이런 메시지가 아주 많이 기록된다면 애플리케이션의 커넥션 종료 로직을 검토해볼 필요가 있다.
  • 4.4.1.5 InnoDB의 모니터링 또는 상태 조회 명령(SHOW ENGINE INNODB STATUS 같은)의 결과 메시지
    • 모니터링은 상대적으로 큰 메시지를 에러 로그 파일에 기록한다. 모니터링을 활성화 상태로 만들어 두고 유지하면 에로 로그 파일이 매우 커져서 파일 시스템의 공간을 다 사용해 버릴지도 모른다. 모니터링 사용한 후에는 다시 비활성화해야 한다.
  • 4.4.1.6 MySQL의 종료 메시지
    • 아무도 모르게 종료돼 있거나 아무도 모르게 재시작되는 경우, 에러 로그 파일에서 MYSQL이 마지막으로 종료되면서 출력한 메시지를 확인하는 것이 유일한 방법이다. 누군가가 종료시켰다면 Received SHUTDOWN from user ..., 아무런 종료 관련 메시지가 없거나 스택 트레이스와 같은 내용이 출력된다면 MYSQL 서버가 세그먼테이션 폴트로 비정상적으로 종료된 것.
• 4.4.2 제너럴 쿼리 로그 파일(제너럴 로그 파일, General log)
  • MYSQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아 검토해볼 때, 쿼리 로그를 활성화해서 쿼리를 쿼리 로그 파일로 기록하게 한 다음, 그 파일을 검토하면 된다. 제너럴 쿼리 로그는 실행되기 전에 MYSQL이 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중 에러가 발생해도 일단 로그 파일에 기록된다.
  • 경로는 general_log_file이라는 이름의 파라미터에 설정돼 있다.
• 4.4.3 슬로우 쿼리 로그: 쿼리 튜닝은 크게 서비스가 적용되기 전에 전체적으로 튜닝하는 경우 / 서비스 운영중에 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝으로 나뉜다. 전자는 모두 튜닝하면 되지만 후자는 어떤 쿼리가 문제인지 판단하기가 상당히 어렵다. 슬로우 쿼리 로그가 상당히 많은 도움이 된다.
  슬로우 쿼리 로그 파일에는 long_query_time 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다.
  슬로우 쿼리 로그는 MYSQL이 쿼리를 실행한 후, 실제 소요된 시간을 기준으로 슬로우 쿼리 로그에 기록할지 여부를 판단하기 때문에 반드시 쿼리가 정상적으로 실행이 완료돼야 슬로우 쿼리 로그에 기록될 수 있다. 즉, 정상적으로 실행이 완료됐고, 시간이 long_query_time에 정의된 시간보다 많이 걸린 쿼리이다.
  InnoDB의 경우 MYSQL 엔진 레벨의 잠금과 스토리지 엔진 자체 잠금을 가지고 있다. (MyISAM, MEMORY는 별도의 스토리지 엔진 레벨 잠금 없다. 테이블 단위의 잠금을 사용하고 MVCC와 같은 메커니즘이 없어서 Lock_time(MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간만 표시)이 1초 이상 소요될 가능성이 있다.) 이러한 이유로 쿼리 로그에 출력되는 내용이 혼란스러울 수 있다.
  Percona에서 개발한 Percona Toolkit의 pt-query-digest 스크립트를 이용하면 쉽게 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다.
  
  • 4.4.3.1 슬로우 쿼리 통계: 모든 쿼리를 대상으로 슬로우 쿼리의 실행 시간(Exec time), 잠금 대기 시간(Lock time) 등에 대해 평균 및 최소/최대 값을 표시
  • 4.4.3.2 실행 빈도 및 누적 실행 시간순 랭킹: 각 쿼리별로 응답 시간과 실행 횟수를 보여준다. 같은 모양의 쿼리라면 동일한 Query ID를 가진다.
  • 4.4.3.3 쿼리별 실행 횟수 및 누적 실행 시간 상세 정보: Query ID별 쿼리를 쿼리 랭킹에 표시된 순서대로 자세한 내용을 보여준다. 쿼리가 얼마나 실행됐는지, 쿼리의 응답 시간에 대한 히스토그램 같은 상세한 내용을 보여준다.