-
MySQL(MySQL서버)은 MySQL 엔진과 스토리지 엔진으로 구성된다.
- MySQL 엔진
- 커넥션 핸들러 : 클라이언트로부터의 쿼리 요청을 처리
- 전처리기
- SQL 파서
- 옵티마이저
- 쿼리 실행기
- 스토리지 엔진
- 실제 데이터를 디스크에 저장하고 읽어오는 부분을 담당한다.
- 플러그인으로 다양한 기능을 제공한다. -> 컴포넌트로 변경됨
- InnoDB, MyISAM, Memory, CSV, Archive 등이 있다.
- MySQL 엔진
-
❗핸들러 API : MySQL 엔진과 스토리지 엔진 사이의 인터페이스
- SHOW GLOBAL STATUS LIKE 'Handler%'; 명령어로 확인 가능
-
MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 동작한다.
-
- 프로세스 : 운영체제로부터 자원을 할당받은 작업의 단위
- 스레드 : 프로세스 내에서 실행되는 흐름의 단위
- 프로세스:스레드 = 1:N
- 프로세스는 독립된 메모리 영역을 가진다. 그러므로 컨텍스트 스위칭이 무겁고 자원의 공유가 어렵다.
- 스레드는 프로세스 내의 메모리 영역을 공유한다. 그러므로 컨텍스트 스위칭이 가볍고 자원의 공유가 쉽다.
-
스레드 기반의 RDBMS의 장점 (대표 : MySQL, Oracle, SQL Server)
- 스레드는 프로세스보다 가볍고 컨텍스트 스위칭이 빠르기 때문이다.
- 스레드는 프로세스보다 자원을 적게 사용한다.
- 스레드는 프로세스보다 자원을 공유하기 때문에 프로세스보다 효율적이다.
-
프로세스 기반의 RDBMS의 장점 (대표 : PostgreSQL)
- 독립성 : 클라이언트연결마다 별개의 프로세스를 가진다. 그렇기 때문에 하나의 프로세스에서 발생한 문제가 다른 연결에 영향을 주지 않는다.
- 보안: 프로세스 간 메모리가 분리되어 있어, 보안적인 측면에서 각 클라이언트의 데이터를 보호하는 데 유리합니다.
- 관리 용이성: 시스템 관리자가 각 프로세스를 개별적으로 모니터링하고 제어할 수 있어, 세밀한 리소스 관리가 가능합니다.
-
프로세스 기반의 RDBMS의 단점
- 자원 사용량: 각 연결마다 별도의 프로세스가 메모리와 CPU 자원을 사용하기 때문에, 많은 수의 동시 연결이 발생할 경우 시스템의 자원을 많이 소모하게 됩니다.
- 오버헤드: 프로세스 간의 컨텍스트 스위칭이 빈번하게 발생할 수 있으며, 이는 성능 저하를 일으킬 수 있습니다.
- 통신 비용: 프로세스 간 통신(IPC)은 스레드 간 통신보다 복잡하고 느릴 수 있습니다.
-
-
스레드는 Foreground 스레드와 Background 스레드로 나뉜다.
-
Foreground 스레드
- 클라이언트가 요청하는 쿼리를 처리하는 스레드
- 최소한 MySQL 서버에 접속한 클라이언트 수만큼 존재한다.
- 작업을 종료하면 스레드 캐시로 이동한다.
- 이 때 스레드 캐시의 최대 스레드 개수를 thread_cache_size로 설정할 수 있다.
- Foreground 스레드는 데이터를 MySQL 데이터 버퍼나 캐시로부터 가져온다.
- 데이터가 버퍼나 캐시에 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어온다.
- 이 때 MyISAM은 Foreground 스레드가 직접 디스크에서 데이터를 읽어오지만, InnoDB는 데이터 버퍼, 캐시까지만 Foreground 스레드가 처리하고 버퍼로부터 디스크까지 기록하는 작업은 Background 스레드가 처리한다.
-
Background 스레드
- 크게 아래의 역할을 담당한다.
- 인서터 버퍼를 병합하는 스레드
- ❗️로그를 디스크로 기록하는 스레드
- ❗️InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드 ( 위에서 설명 )
- 그렇기 때문에 InnoDB는 지연된 쓰기가 가능하다 MyISAM은 불가능
- 데이터를 버퍼로 읽어 오는 스레드
- 잠금이나 데드락을 모니터링하는 스레드
- 크게 아래의 역할을 담당한다.
-
MySQL의 메모리 공간은 크게 2가지 영역으로 나누어 사용한다.
- 복잡한 내용이라 단순하게 MySQL 시스템 변수로 설정해 둔 만큼 운영체제로부터 할당 받는다고 생각하자
- Global Memory
- Session(Local) Memory
-
글로벌 메모리 영역
- 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당
- 필요에 따라 2개 이상도 가능하지만 클라이언트 수와 무관
- 대표적인 글로벌 메모리 영역
- 테이블 캐시
- InnoDB 버퍼 풀
- InnoDB 해시 인덱스
- InnoDB 리두 로그 버퍼
-
세션 메모리 영역
- 클라이언트 스레드가 쿼리를 처리하기 위해 사용하는 메모리 영역
- 클라이언트가 사용해서 클라이언트 메모리 영역이라고도 함
- 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 세션 메모리 영역이라고도 함
- 스레드별로 독립적으로 할당되기 떄문에 절대 공유되지 않는다.
- 부적절하게 설정하면 메모리 부족으로 멈출 수 있다. ( 희박함 )
- 필요할 때만 할당되고 할당조차 하지 않을 수도 있다. (소트버퍼, 조인버퍼)
- 커넥션 상태에 따라 두 가지 종류로 나뉜다.
- 커넥션이 열려 있는 동안 계속 열려있는 메모리 영역
- 쿼리를 실행하는 순간에만 할당했다가 해제하는 메모리 영역 (소트버퍼, 조인버퍼)
- 대표적인 세션 메모리 영역
- 정렬 버퍼
- 조인 버퍼
- 바이너리 로그 캐시
- 네트워크 버퍼
- MySQL은 독특하게 플러그인을 지원한다.
- MySQL엔진이 아닌 스토리지 엔진을 플러그인으로 추가할 수 있다.
- MySQL 8.0부터는 컴포넌트로 변경되었다.
쿼리 실행 순서는 아래와 같다.
- 클라이언트의 SQL 요청
- [MySQL 엔진] 커넥션 핸들러
- [MySQL 엔진] 쿼리 파서
- [MySQL 엔진] 전처리기
- [MySQL 엔진] 옵티마이저
- [MySQL 엔진] 쿼리 실행기
- [스토리지 엔진] 데이터 읽기/쓰기
- [MySQL 엔진] 결과 반환
- 쿼리 파서
- 쿼리 파서는 사용자가 요청한 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만드는 작업
- 토큰 : MySQL이 인식할 수 있는 최소 단위의 어휘나 기호
- 기본 문법 오류는 이 과정에서 발견된다.
- 쿼리 파서는 사용자가 요청한 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만드는 작업
- 전처리기
- 쿼리 파서에 의해 만들어진 트리 기반의 쿼리 문장에서 구조적인 문제점이 있는지 확인한다.
- 테이블 명, 칼럼 명, 내장 함수와 같은 객체를 매핑해 실제 객체가 존재하는지 확인한다.
- 접근 권한을 확인한다.
- 실제 존재하지 않는 객체이거나 권한이 없는 객체에 접근하려고 하면 에러를 발생시킨다.
- ❗️옵티마이저
- DBMS의 두뇌
- 쿼리 문장을 저렴한 비용으로 가장 빠르게 실행할 수 있는 실행 계획을 수립한다.
- RDBMS의 성능을 결정짓는 중요한 요소
- RDMBS를 잘 다룬 다는 것은 옵티마이저가 더 나은 선택을 할 수 있도록 유도하는 것
- 실행 엔진
- 옵티마이저가 수립한 실행 계획을 실제로 실행하는 역할
- 만들어진 계획대로 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할
- 핸들러 (스토리지 엔진)
- 데이터를 디스크로 저장하고 읽어 오는 역할
- 결국 스토리지 엔진을 의미
- 쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고 있다가 동일한 SQL이 요청되면 캐시된 결과를 반환하는 기능
- 성능을 향상시키지만 테이블 데이터에 변경이 있는 경우 캐시를 삭제해야 하므로 심각한 성능 저하를 유발했음
- MySQL 8.0 부터는 쿼리 캐시 기능을 완전히 제거함
-
MySQL 엔터프라이즈 기능이지만 커뮤니티 에디션에서는 Percona Server에서 제공하는 스레드 풀 기능이 있다.
-
Percona Server는 플러그인 형태로 작동한다.
-
스레드 풀 : 사용자의 요청을 처리하는 스레드 개수를 줄여 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있도록 해 자원의 소모를 줄이는 것이 목적
-
스레드들을 CPU가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 해야함
-
적절하게 설정하면 CPU의 프로세서 친화도가 높이고 컨텍스트 스위치 비용을 줄일 수 있음
-
Percona Server의 스레드 풀은 기본적으로 CPU 코어 개수만큼 스레드 그룹을 생성함
- thread_pool_size 옵션을 통해 조정 가능
- 일반적으로 CPU 개수만큼 사용하는 것이 CPU 프로세서 친화도를 높이는 데 좋음
- 모든 스레드가 사용 중인 경우 thread_pool_stall_limit 만큼 기다려도 처리 중인 작업이 끝나지 않은 경우 새로운 스레드를 생성해 스레드 그룹에 추가한다.
- 그러므로 thread_pool_stall_limit 이 0에 가까운 것은 의미가 없음
- Percona Server는 선순위 큐, 후순위 큐를 이용해 특정 쿼리를 우선처리할 수 있는 기능도 제공
- 데이터 딕셔너리(메타데이터) : 저장 프로시저, 테이블 구조
- MySQL 5.7까지는 저장 프로시저, 테이블 구조 (데이터 딕셔너리, 메타데이터라고 함) 를 별도의 파일로 관리했음
- 트랜잭션을 지원하지 않아 깨지는 경우가 있었음
- MySQL 8.0부터는 트랜잭션을 지원하는 메타데이터를 사용함
- 코드 관련 정보를 전부 InnoDB(스트로지 엔진)에 저장함
- 사용자 정보
- 시스템 테이블 (MySQL 서버가 동작하기 위해 필요한 기본 테이블
- 시스템 테이블과 데이터 딕셔너리 정보를 모두 모아 mysql DB에 저장
- mysql.idb 테이블스페이스에 저장
- 저장은 하지만 조회할 수 없도록 처리 되어 있음
- 별도의 뷰테이블을 만들어 조회해야함
- InnoDB에 저장하기 떄문에 트랜잭션 처리가 가능해짐
- MyISAM, CSV 등 InnoDB 외의 스토리지 엔진
- 다른 스토리지 엔진의 메타 정보는 별도로 저장해야함
- SDI (Serealized Dictionary Information) 라는 방법으로 저장함
- .sdi 파일로 저정됨
- 5.7이하 번전의 .frm 파일과 같은 역할
- 스토리지 엔진 중 거의 유일하게 레코드 기반 잠금일 제공
- 높은 동시성 처리가 가능, 안정적, 성능 뛰어남
// TODO : 클러스터링이 무엇인지 정리
- 클러스터링이란 ?
- 프라이머리키를 기준으로 클러스터링(군집화)되어 저장된다.
- 세컨더리 인덱스는 레코드의 주소 대신 PK값을 논리적인 주소로 사용한다.
- 프라이머리키를 이용한 레인지 스캔은 빠르다
- 기본적으로 PK가 비중이 높다
- MyISAM, CSV 등은 PK와 세컨더리 인덱스의 비중이 같다.
- MyISAM은 PK 외의 다른 Index도 물리적인 레코드 주소 값을 갖는다.
- InnoDB 스토리지 엔진 레벨에서 제공하는 기능
- MyISAM, MEMORY 테이블에서는 사용 불가능
- 외래키 생성 시, 부모,자식 모두 해당 키에 인덱스가 있어야함
- 외래키 지정시 데이터 생성,수정,삭제 작업이 복잡해 진다.
- 긴급한 경우 foreign_key_checks 시스템 변수를 OFF 하면 일시적으로 멈출 수 있다.
- SET foreign_key_checks=OFF;
- SET foreign_key_checks=ON;
- ON 하기 전에 다시 부모,자식 데이터 정합성을 확인해야함
- 목적: 잠금을 사용하지 않는 일관된 데이터 읽기 제공
- InnoDB는 Undo log 를 이용해 MVCC를 구현
- 멀티버전 : 하나의 레코드에 대해 여러 버젼이 존재
- 예시 상황: (id:1, name:홍길동, area:서울) -> (id:1, name:홍길동, area:부산) 으로 업데이트 하는 경우
- 환경 : 메모리(InnoDB 버퍼 풀 + Undo log) + 데이터 파일(디스크)
- InnoDB는 ACID를 보장하기 때문에 버퍼 풀과 데이터 파일의 상태는 같다고 봐도 됨
- (id:1, name:홍길동, area:서울) -> (id:1, name:홍길동, area:부산) 으로 업데이트
- Undo log에 (id:1, area:서울) 저장 (PK + 변경되는 필드의 변경 전 값만 저장)
- 아직 커밋은 하지 않음
- 위의 상황에서 수정이 커밋 되기 전에 데이터를 읽으면 어떻게 될까 ?
- MySQL의 서버 시스템 변수(transaction insolation) 에 설정된 격리 수준(isolation level)에 따라 다르다
- READ_UNCOMMITTED : 커밋되지 않은 데이터를 읽을 수 있다.
- 즉, 커밋되지 않은 변경된 데이터 읽음 (id:1, name:홍길동, area:부산)
- READ_COMMITTED < REPEATABLE_READ, SERIALIZABLE : 커밋된 데이터만 읽는 격리 수준 이상의 격리 수준
- 커밋된 데이터만 읽음
- 언두 로그 영역의 데이터를 반환한다.
- 언두 로그의 삭제 시점
- Transaction이 Rollback 되면 언두 로그도 삭제
- Transaction이 Commit 되는 경우 바로 삭제되지 않음
- 이 언두 영역을 필요로하는 트랜잭션이 더는 없는 경우 삭제됨
- InnoDB 스토리지 엔진은 MVCC기술을 이용해 잠금 없는 일관된 읽기 작업을 수행함
- 격리 수준이 SERIALIZABLE이 아닌 경우의 READ 작업은 항상 잠금을 대기하지 않고 바로 수행함
- 변경 되기 전의 데이터를 읽어야 하는 경우 Undo log를 사용함
- 이 Undo log가 삭제되지 않고 오래 남으면 성능을 저하시킴
- 그러므로 최대한 빨리 트랜잭션을 커밋 또는 롤백하여 마무리 하는 것이 좋음
-
InnoDB 스토리지 엔진은 교착 상태를 확인하기 위해 잠금 대기 목록을 그래프 형태(Wait-for List)로 관리함
-
InnoDB는 데드락 감지 스레드를 가지고 있음
-
데드락에 빠진 경우 두 트랜잭션 중 하나를 강제로 종료시킴
- 트랜잭션 선택 기준 : 언두로그가 적은 트랙잭션을 롤백함
- MySQL 서버의 부하를 덜 유발하기 때문
-
InnoDB는 상위 레이어인 MySQL 엔진에서 관리하는 테이블 잠금은 확인 불가
-
그러므로 데드락 감지가 불확실할 수 있음
-
innodb_table_locks 시스템 변수를 활성화하면 InnoDB 레코드 잠금 뿐 아니라 테이블 레벨의 잠금까지 감지함
- 가능하면 활성화 하는 것이 좋음
-
이 데드락 감지 스레드는 동시 처리 쓰레드가 많아지면 CPU자원을 많이 소모하고 느려짐
- 다른 서비스 쿼리를 실행중인 쓰레드가 멈추게 됨 -> 서비스 악영향
-
그런 경우 innodb_deeadlock_detect 시스템 변수를 OFF
-
- 일정 시간이 지나면 자동으로 에러를 호출하는 innodb_lock_wait_timeout을 기본 값인 50초보다 매우 짧게 설정해
- 데드락에 빠진 후 일정 시간이 지나면 자동으로 에러 처리가 나도록 하는 것이 좋음
- MySQL 서버가 실작될 때 완료되지 않은 트랜잭션, 디스크에 일부만 기록된 정보의 복구 작업을 자동으로 진행함
- InnoDB는 견고해서 이런 경우 자체가 거의 없음
- 자동 복구할 수 없는 파일이 존재하는 경우 자동 복구를 멈추가 MySQL 서버를 종료함
- 이런 경우 innodb_force_recovery 시스템 변수를 사용해 MySQL 서버를 시작해야함
- 1~6까지의 값을 사용할 수 있음
- 숫자가 클수록 심각한 상황임
- 복구 모드에서는 SELECT 이외의 쿼리는 사용 불가능함
- 1~6까지 증가시키면서 MySQL 서버를 실행해보자
- 그래도 실패한다면 백업을 이용해 다시 구축하는 방법밖에 없다.
- InnoDB 버퍼 풀은 InnoDB 스토리지 엔진의 가장 핵심적인 부분
- 디스크의 데이터 파일, 인덱스 정보를 메모리에 캐시해 두는 공간
- 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 수행함
- 운영체제와 각 클라이언트 스레드가 사용할 메모리를 고려해 설정해야 함 InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리의 성능이 빨라진다.
- MySQL 서버 내에서 메모리를 사용하는 영역은 크게 없지만 독특하게 레코드 버퍼 는 메모리를 많이 소모함
- 레코드 버퍼 공간은 별도로 설정할 수 없음, 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블의 개수에 다라 결정됨
- 또한 레코드 버퍼는 자동으로 해제되기도 하므로 정확히 필요한 양을 계산할 수 없음
- 5.7 버전부터 InnoDB의 버퍼 풀의 크기를 동적으로 조절할 수 있게 개선됨
- 적절히 작은 값으로 시작해 상황을 봐 가면서 적절히 늘리는 것이 좋음
- innodb_buffer_pool_size 시스템 변수로 설정 가능
- 버퍼 풀의 크기 변경은 크리티컬한 변경이므로 MySQL 서버가 한가한 시점에 진행하는 것이 좋음
- 특히 줄이는 변경으 더욱 크리티컬함
- 변경의 단위는 128MB이다. 변경시에 MySQL 메뉴얼을 반드시 숙지하고 진행하자.
- innodb_buffer_pool_instances 시스템 변수로 버퍼 풀을 몇 개의 인스턴스로 나눌지 설정 가능
- 기존에는 버퍼 풀 전체를 관리하는 잠금(세마포어)를 사용했음 -> 내부 잠금 경합을 많이 유발하여 여러개로 분리하게 됨
- 기본적으로 8개로 설정되지만 메모리 크기가 1GB 미만인 경우 1개로 설정됨
- 버퍼 풀에 할당 가능한 메모리가 40GB 미만이라면 기본 8개로 설정됨
- 메모리가 크다면 버퍼 풀 인스턴스 1개당 5GB 메모리 사용 가능하도록 생성됨
-
InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기로(innodb_page_size 시스템 변수로 설정) 조각으로 쪼개 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어 각 조각에 저장한다.
-
버퍼 풀의 페이지 크기 조각을 고나리하기 위해 InnoDB 스토리지 엔진은 크게 LRU(Least Recentryl Used) 리스트와 플러시(Flush) 리스트, 그리고 프리(Free) 리스트라는 3개의 자료 구조를 관리한다.
-
프리 리스트 : 사용자 데이터로 채워지지 않은 비어 있는 페이지
-
플러시 리스트 : 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(더티 페이지라고 함)의 변경 시점 기준의 페이지 목록을 관리한다.
- 한 번 변경이 된 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록돼야 한다.
- 데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다.
- 리두 로그의 엔트리는 각 특정 페이지와 연결된다.
- 하지만 리두 로그가 디스크로 기록 됐다고 해서, 데이터가 디스크로 기록 됐다는 것을 항상 보장하지는 않는다.
-
LRU 리스트 : 엄밀하게 말하면 LRU + MRU(Most Recently Used) 리스트가 결합된 형태
- 사용 목적 : 디스크로부터 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해 디스크 읽기를 최소화 하는 것
- 필요한 레코드가, 지정된 데이터 페이지가 버퍼 풀에 있는 지 검사
- InnoDB 어댑티브 해시 인덱스를 이용해 페이지 검색
- 해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지 검색
- 버퍼 풀에 데이터 페이지가 있었다면 해당 페이지의 포인터를 MRU 방향으로 승급
- 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
- 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동 (Read Ahead와 같이 대량 일기의 경우 디스크의 데이터 페이지가 버퍼 풀로 적재는 되나 사용되지 않을 수도 있으므로 MRU로 이동하지 않음)
- 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이(Age)가 부여되며 버퍼 풀에 상주하는 동안 쿼라에서 오랫동안 사용되지 않으면 데이터 페이젱 부여된 나이가 오래된 데이터는 버퍼 풀에서 ㅈ제거됨
- 필요한 데이터가 자주 접근됐다면 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가 // TODO 어댑티브 해시 인덱스란
- InnoDB 버퍼 풀은 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두 가지 용도가 있다.
- 버퍼 풀의 메모리를 늘리면 단순히 데이터 캐시 기능만 향상시킨다.
- 쓰기 버퍼링 기능을 향상시키기 위해서는 버퍼 풀과 리두 로그 관계를 이해해야한다.
- 버퍼 풀에는 변경되지 않은 클린 페이지와 변경된 더티 페이지가 공존한다.
- 이 더티 페이지는 한 개의 리두 로그의 엔트리와 연결된다.
- 리두 로그는 고정 크기 파일을 연결해 순환 고리처럼 사용한다.
- 즉 계속 쌓이다 보면 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 업데이트 되야한다.
- 그러므로 리두 로그는 재사용 가능한 공간과 재사용 불가능한 공간을 구분한다.
- 재사용 가능 공간 : 활성 리두 로그 (Active Redo Log)
- 리두 로그는 기록될 때마다 로그 포지션은 계속 증가된 값을 갖는다.
- 이를 LSN(Log Sequence Number)이라고 한다.
- InnoDB엔진은 주기적으로 체크포인트 이벤트를 발생시켜 버퍼 풀의 더티 페이지를 디스크로 동기화한다.
- 이렇게 발생한 체크포인트 중 가장 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점이 된다.
- 가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지(Checkpoint Age)라고 한다.
- Checkpoint Age = Active Redo Log
- 5.6 버전 까지는 더티 페이지 플러시 기능이 부드럽지 않았음
- 갑자기 디스크 기록이 폭증해 사용자 쿼리 처리 성능에 영향을 주기도 함
- 8 버전으로 업그레이드 되면서 디스크 기록 폭증현상이 발생하지 않게 됨
- InnoDB는 더티 페이지들을 선으상의 악영향 없이 디스크에 동기화 하기 위해 2개의 플러시 기능을 백그라운드로 실행함
- 플러시 리스트 플러시
- LRU 리스트 플러시
- 리두 로그 공간을 지우기 위해서는 InnoDB 버퍼 풀의 더티 페이지가 반드시 디스크로 먼저 동기화 되야함
- 이를 위해 InnoDB 스토리지 엔진은 주기적으로 플러시 리스트 플러시 함수를 호출해 펄러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행함
- 이 때 얼마나 오래전부터, 얼마나 많은 더티 페이지를 한 번에 디스크로 기록하느냐에 따라 사용자의 쿼리가 악영향을 받지 않으면서 부드럽게 처리된다.
- 아래의 시스템 변수를 활용하자
- innodb_page_cleaners : 클리너 스레드의 개수를 조정
- 클리너 스레드 : 더티 페이즈를 디스크로 동기화하는 스레드
- 이 개수가 버퍼 풀보다 큰 경우 자동으로 버퍼 풀 개수와 맞춰줌
- 그러므로 가능한 버퍼 풀과 클리너 스레드의 개수를 같게 하는 것이 좋음
- innodb_max_dirty_pages_pct : 버퍼 풀 내의 더티페이지 비율 설정
- 높을 수록 지연 쓰기의 기능이 극대화 되 가능한 기본값(90%)이 좋음
- InnoDB 스토리지 엔진은 사용자의 DML애 대한 더티 페이지도 따로 가지고 있기 때문에 이를 고려한 90%임
- 하지만 너무 크게 되면 쓰기 폭발(Disk IO Burst)이 발생할 확률이 높다
- innodb_io_capacity 크기보다 더 큰 더티 페이지를 기록해야 하는 경우
- innodb_max_dirty_pages_pct_lwm : 쓰기 폭발을 완화하기 위한 장치
- 일정 수준의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록하게 하고 있음
- innodb_io_capacity : 일반적인 상황에서 디스크가 적절히 처리할 수 있는 양
- innodb_io_capacity_max : 드스크가 최대의 성능을 발휘할 때 디스크 읽고 쓰기 가능한 양
- 사용자 쿼리도 존재하므로 이를 고려해 값을 설정해야함
- innodb_flush_neighbors : 더티 페이지를 디스크에 쓸 때 근접 더티 페이지를 디스크에 쓰느 ㄴ옵션
- HDD를 사용할 때 주로 썼음
- 요즘은 SDD를 사용해 잘 사용하지 않는 추세임
- 기본값은 OFF
- innodb_adaptive_flushing : ON/OFF
- 사용하게 되면 innodb_io_capacity ,innodb_io_capacity_max에 의존하지 않고 새로운 알고리즘을 사용하게 됨
- 리두 로그의 증가 속도를 분석해 적절한 수준의 더티 페이지가 버퍼 풀에 유지되도록 디스크 쓰기를 실행함
- innodb_adaptive_flushing_lwm
- innodb_page_cleaners : 클리너 스레드의 개수를 조정
- InnoDB는 LRU 리스트에서 사용 빈도가 낮은 페이지를 제거해 새로운 페이지를 얻어올 공간을 만듦
- 이 때 LRU 리스트 플러시 함수를 실행
- LRU 리스트의 끝부터 시작해서 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼의 페지이들을 스캔
- 스캔된 페이지 중 더티 페이지는 즉시 동기화하고, 클린 페이지는 즉시 프리 페이지로 이동한다.
- 이 때 버퍼풀 인스턴스 별로 LRU 플러시를 실행하기 때문에
- 실질적 LRU 리스트의 스캔은 innodb_buffer_pool_instances * innodb_lru_scan_depth 만큼의 페이지를 스캔하게 된다.
- 버퍼 풀은 성능과 밀접하게 연관되어 있다.
- 서버 재시작 시 버퍼 풀의 상태를 저장해 놓은 뒤에 백업하는 것은 성능에 큰 영향을 미친다.
- innodb_buffer_pool_dump_now를 이용해 현재 상태를 백업해 놓을 수 있다.
- 데이터를 전부 저장하는 것이 아니라 LRU의 데이터 페이지의 메타 정보만 가지고 오기 때문
- innodb_buffer_pool_load_now를 이용해 백업된 상태를 복구할 수 있다.
- 복구는 메타 정보의 데이터를 가지고 와야 하기 때문에 시간이 오래 걸릴 수 있다.
- 시간이 오래 걸려 취소하고 싶을 때는
- SET GLOBAL innodb_buffer_pool_load_abort=ON;
- 버퍼 풀의 백업과 복구를 자동화 하려면
- innodb_buffer_pool_dump_at_shutdown
- innodb_buffer_pool_load_at_startup
- 위의 두 설정을 MySQL 서버의 설정 파일에 넣어두자
- inforamation_schema 데이터베이스에 innodb_cached_indexes 테이블을 이용해 버퍼 풀의 적재 내용을 확인할 수 있다.
- InnoDB 스토리지 엔진으 데이터 파일에 변경을 기록하기 전에 DoubleWrite Buffer 버퍼에 변경 내용을 기록한다.
- 의도치 않은 문제가 발생한 경우 재시작될 때 항상 DboubleWrite 버퍼의 내용과 디어터 파일의 페이지를 비교한다.
- 다른 내용을 가진 파일이 존재하는 경우 DoubleWrite 버퍼의 내용을 페이지로 복사한다.
- innodb_doublewrite 시스템 변수로 제어 가능하다.
- HDD 처럼 원판이 회전하는 디스크에서는 DoubleWrite 버퍼를 사용하는 것이 좋다.
- 하지만 SDD에서는 비용이 많이 발생한다.
- 트랜잭션과 격리 수준 보장을 위해 DML로 변경 되기 이전 버전의 데이터를 별도로 백업한다.
- 이 언두 로그는 두 가지 기능을 한다.
- 트랜잭션 보장
- 격리 수준 보장