MySql 엔진 아키텍쳐
크게 MySql 엔진과 스토리지 엔진으로 구분할 수 있다
MySql 엔진
- 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 Sql 파서 및 전처리기, 쿼리 최적화 실행을 위한 옵티마이저로 구성
스토리지 엔진
- 디스크 스토리지에 데이터를 저장하고 읽어오는 부분을 담당
핸들러 API
- 핸들러 요청: 스토리지 엔진이 읽기 혹은 쓰기 위해 하는 요청
- 핸들러 API: 핸들러 요청을 위해 사용하는 API
MySql 스레딩 구조
- MySql 서버는 프로세스 기반이 아니라 스레드 기반으로 작동
- 스레드는 포그라운드와 백그라운드 스레드로 구분할 수 있다
포그라운드 스레드
- 최소한 MySql 서버에 접속된 클라이언트 수만큼 존재
- 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리
- 클라이언트 사용자가 작업을 마치고 커넥션 종료하면 해당 커넥션을 담당하는 스레드는 다시 스레드 캐시로 돌아간다
- 스레드 캐시에 유지할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정
- 데이터를 MySql의 데이터 버퍼나 캐시로부터 가져오거나, 캐시에 데이터가 없는 경우 직접 디스크의 데이터나 인덱스 파일로 부터 직접 읽어온다
백그라운드 스레드
다음 작업들을 실행
- 인서트 버퍼를 병합
- 로그를 디스크로 기록
- InnoDB 버퍼 풀의 데이터를 디스크에 기록
- 데이터를 버퍼로 읽어온다
- 잠금이나 데드락을 모니터링
메모리 할당 및 사용 구조
글로벌 메모리
- MySql 서버가 시작되면서 운영체제로부터 할당
로컬 메모리
- 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역
- 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유 되지 않는다
- 서버 메모리 부족으로 MySql 서버가 멈출 수 있으니 적절한 메모리 공간을 할당해야 한다
플러그인 스토리지 엔진 모델
- 기본적으로 제공되는 스토리지 엔진 이외에 부가적인 기능을 제공하는 엔진이 필요할때 사용
- 아래 명령으로 설치된 플러그인 확인 가능
SHOW PLUGINS;컴포넌트
다음과 같은 플러그인의 단점을 보완하기 위해 등장
- 플러그인은 오직 MySql 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없음
- 플러그인은 MySql 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음
- 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화하기 어려움
쿼리 실행 구조
- 쿼리 파서: 쿼리 문장을 토큰으로 분리해 트리 형태 구조를 만들어 낸다
- 전처리기: 파서트리를 기반으로 쿼리가 구조적인 문제가 있는지 확인
- 옵티마이저: 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정
- 실행 엔진: 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할
- 핸들러: 엔진의 요청에 따라 데이터를 디스크로 저장하고 읽어온다(스토리지 엔진)
쿼리 캐시
동일한 쿼리를 실행했을때 캐시된 결과를 반환해 성능 향상을 가져올 수 있지만, 테이블에 변경 사항이 있을때는 다른 결과를 반환하는 단점이 있었다
이에 MySql8.0에서는 삭제되었음
스레드풀
- 엔터프라이즈 버전에서만 제공
- Percona Server에는 존재하나 플러그인 형태로 존재
- 스레드풀의 목적: 사용자 요청을 처리하는 스레드 개수를 줄여 동시 처리되는 요청이 많다 하더라도 MySql 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것
- CPU 프로세스 친화도를 높이고, 불필요한 컨텍스트 스위치를 줄이면 성능을 높일 수 있다
- 기본적으로는 CPU 코어의 개수만큼 스레드 그룹을 생성하지만 변경 가능
- 모든 스레드 그룹의 스레드가 각자 작업을 처리하고 있다면 새로운 쿼리 요청이 들어오더라도 스레드 풀은 thread_pool_max_threads 시스템 변수에 설정된 개수를 넘어 설 수 없고 thread_pool_stall_limit의 시간 동안 기다려야 새로운 요청을 처리할 수 있다
트랜잭션 지원 메타데이터
MySql 8.0 버전부터는 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB 테이블에 저장
스키마 변경 작업 중간에 서버가 종료되더라도 스키마 변경이 완전 성공 혹은 완전 실패로 정리
InnoDB 스토리지 엔진 아키텍처
레코드 기반의 잠금을 제공하여 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다
프라이머리 키에 의한 클러스터링
프라이머리 키 값의 순서대로 디스크에 저장
세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용
프라이머리키를 이용한 레인지 스캔이 빠르기 때문에 프라이머리 키의 비중이 높게 설정 됨
외래 키 지원
외래키는 좋은 가이드의 역할을 할 수 도 있지만 때로는 데드락을 발생 시키는 문제를 야기할 수 있기에 주의해 사용해야 한다
외래키가 복잡하게 얽혀서 수정하기 어려울때는 다음 커맨드로 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다
SET foreign_key_checks=OFF;외래키 관계를 가진 부모 테이블의 레코드를 삭제했다면 반드시 자식 테이블의 레코드도 삭제해서 일관성을 맞춘 후 다시 외래키 체크 기능을 활성화 해야 한다
SET foreign_key_checks=ON;MVCC(Multi Version Concurrency Control)
잠금을 사용하지 않는 일관된 읽기를 제공하는데 목적이 있음
멀티 버전: 하나의 레코드에 대해 여러 개의 버전이 동시에 관리
시스템 변수에 설정된 격리 수준에 따라 작업 중인 레코드의 결과가 다르게 조회 됨
READ_UNCOMMITTED: InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어 반환
READ_COMITTED 이상의 격리 수준: 변경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환
잠금 없는 일관된 읽기
격리 수준이 SERIALIZABLE이 아닌 경우 읽기 작업은 다른 트랜잭션의 변경 작업과 관계 없이 잠금을 대기하지 않고 바로 실행
자동 데드락 감지
잠금이 교착상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태로 관리
InnoDB 스토리지 엔진이 교착상태에 빠진 트랜잭션들을 찾아서 그 중 하나를 강제 종료 시킴(이 때 기준은 언두 로그 양이 적은 것을 종료)
동시 처리 스레드가 매우 많아지면 데드락 감지 비용이 비싸지기 때문에 innodb_deadlock_detect를 OFF로 설정하는 것도 가능하다(이때 default 값은 50초인데 이보다 낮게 설정하는게 좋다)
자동화된 장애 복구
InnoDB 데이터 파일은 MySql 서버가 시작될 때 항상 자동 복구를 수행
InnoDB의 복구를 위해 innodb_force_recovery 옵션에 설정 가능한 값은 1부터 6까지
- SRV_FORCE_IGNORE_CORRUPT: 테이블스페이스, 데이터, 인덱스 페이지에서 손상된 부분이 발견돼도 무시하고 서버 실행 mysqldump를 이용해 데이터베이스를 다시 구축하는 것이 좋다
- SRV_FORCE_NO_BACKGROUND: 백그라운드 스레드 중 메인 스레드를 시작하지 않고 서버를 시작. InnoDB의 메인 스레드가 언두 데이터를 삭제하는 과정에서 장애가 발생한다면 이와 같은 방법으로 복구
- SRV_FORCE_NO_TRX_UNDO: 롤백에 대비해 변경 전의 데이터를 언두 영역에 기록. 커밋되지 않고 종료된 트랜잭션은 계속 그 상태로 남아 있게 서버를 시작하는 모드. mysqldump를 이용해 데이터베이스를 다시 구축하는 것이 좋다
- SRV_FORCE_NO_IBUF_MERGE: 인서트 버퍼의 내용을 무시하고 강제로 서버 실행. 테이블을 덤프한 후 다시 데이터베이스를 구축하면 데이터의 손실 없이 복구 가능
- SRV_FORCE_NO_UNDO_LOG_SCAN: 장애나 정상적으로 종료되는 시점에 진행 중인 트랜잭션이 있었다면 커낵션을 강제로 끊어 버리고 별도의 정리 작업 없이 종료. InnoDB는 마지막으로 커밋되지 않은 트랜잭션에서 변경한 작업은 모두 롤백 처리
- SRV_FORCE_NO_LOG_REDO: InnoDB엔진은 리두 로그를 모두 무시한 채로 MySql 서버가 시작되며 마지막 체크 포인트 시점의 데이터만 남게 된다. 이때도 mysqldump를 이용해 데이터를 모두 백업해서 MySql 서버를 새로 구축하는 것이 좋다
InnoDB 버퍼 풀
스토리지 엔진에서 가장 핵심적인 부분으로 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간
버퍼풀의 크기 설정
크기를 조절할 수 있기 때문에 버퍼 풀의 크기를 적절히 작은 값으로 설정해 상황을 봐 가면서 증가 시키는 것이 좋다
전체 메모리 공간이 8GB 미만이라면 50% 정도 InnoDB 버퍼 풀로 설정
그 이상이라면 50%에서 시작해 조금 씩 올려서 조정하면 된다
innodb_buffer_pool_size 시스템 변수로 크기를 설정하는 것이 가능하며 동적으로 크기를 확장하는 것이 가능하다
크리티컬한 변경이기에 가능하면 한가한 시점을 골라 진행하는 것이 좋다
버퍼풀을 여러개로 쪼개어 관리할 수 있게 개선하여 내부 잠금 경합 문제를 개선했다
버퍼 풀의 구조
버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 크게 LRU 리스트, 플러시, 그리고 프리 리스트라는 3가지 자료 구조를 관리
LRU 리스트는 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 버퍼풀 메모리에 유지해 디스크 읽기를 최소화 하는게 목적이다
InnoDB 스토리지 엔진에서 데이터를 찾는 과정
- 필요한 레코드가 지정된 데이터 페이지가 버퍼 풀에 있는지 검사
- 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
- LRU 헤더 부분에 적재된 데이터가 읽히면 MRU 헤더 부분으로 이동
- 쿼리가 얼마나 최근에 접근했었는지에 따라 나이가 부여되어 오래되면 제거, 사용이 되면 나이가 초기화 되고 MRU의 헤더 부분으로 이동
- 필요한 데이터가 자주 접근 됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가
플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지의 변경 시점 기준의 페이지 목록을 관리
버퍼 풀과 리두 로그
InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정할 수 록 쿼리 성능이 빨라진다
InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리 처럼 사용
LSN: 리두 로그 파일은 기록될 때마다 로ㅗ그 포지션은 계속 증가됨 → 리두 로그 공간의 시작점이 된다
버퍼 풀 플러시
8.0으로 업데이트 되면서 더티 페이지를 디스크에 동기화하는 부분에서 예전과 같은 디스크 쓰기 폭증 현상은 발생하지 않는다
다음 두가지 플러시 기능을 백그라운드로 실행
- 플러시 리스트 플러시
- 주기적으로 플러시 리스트 플러시 함수를 호출해서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화
- LRU 리스트 플러시
- LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해 공간을 확보
버퍼 풀 상태 백업 및 복구
워밍업: 디스크의 데이터가 버퍼 풀에 적재돼 있는 상태
워밍업이 잘되어 있으면 성능이 매우 뛰어나진다
버퍼풀을 덤프하여 버퍼 풀의 상태를 백업할 수 있고 서버를 다시 시작하면 버퍼 풀의 상태를 다시 복구할 수 있다
이렇게 자동으로 버퍼 풀의 상태를 자동으로 복구할 수 있게 설정하는 것이 좋다
버퍼 풀의 적재 내용 확인
information_schema 데이터베이스에 innodb_cached_indexes 테이블에서 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있다
Double Writer Buffer
데이터 안정성을 위해 사용하는데 HDD에서는 별 부담이 되지 않지만 SSD에서는 부담스럽다.
부담스럼더라도 데이터의 무결성을 위해서 활성화 하는 것이 좋다
언두 로그
트랜잭션과 격리 수준 보장을 위해 Insert, Update, Delete로 변경되기 이전 버전의 데이터를 백업한 것
트랜잭션 보장: 트랜잭션이 롤백되면 변경 이전 데이터로 복구해야 하는데, 이때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구
격리 수준 보장: 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어 반환
언두로그 레코드 모니터링
8.0으로 업데이트하면서 순차적으로 사용해 디스크 공간을 줄이는 것도 가능하며, 필요한 시점에 사용 공간을 자동으로 줄여 주기도 한다
트랜잭션이 장시간 유지되는 것은 성능상 좋지 않기 때문에 모니터링 하고 있는 것이 좋다
언두 테이블스페이스 관리
언두 테이블스페이스: 언두 로그가 저장되는 공간
하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯을 가진다
최대 동시 트랜잭션 수 = InnoDB 페이지 크기 / 16 롤백 세그먼트 개수 언두 테이블 스페이스 개수
체인지 버퍼
인덱스를 업데이트 하는 작업은 인덱스가 많다면 많은 자원을 소모
체인지 버퍼: 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상 시키는 방법
리두 로그 및 로그 버퍼
리두 로그는 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치
MySql은 성능을 고려해 쓰기보다 읽기 성능을 고려한 자료 구조를 가지고 있고 성능저하를 막기 위해 데이터 변경 내용을 먼저 로그로 기록한다
리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 해야 비정상적으로 종료됐을 때 직전까지의 트랜잭션 커밋 내용이 리두 로그에 기록될 수 있고 장애 직전 시점까지의 복구가 가능해진다
로그 버퍼의 크기는 16MB 수준에서 설정하는 것이 좋은데 TEXT 같이 큰 데이터를 자주 변경하면 크게 설정하는 것이 좋다
리두 로그 아카이빙
MySql의 리두 로그 아카이빙 기능은 데이터 변경이 많아서 리두 로그가 덮어쓰인다고 하더라도 백업이 실패하지 않게 해준다
아카이빙된 리두 로그가 저장될 디렉터리를 innodb_redo_log_archive_dirs 시스템 변수에 설정해야 한다
리두 로그 활성화 및 비활성화
MySql 8.0 부터는 수동으로 리두 로그를 활성화하거나 비활성화할 수 있다
ALTER INSTANCE DISABLE INNODB REDO_LOG;리두 로그가 비활성화된 상태에서 비정상적으로 종료되면 마지막 체크포인트 이후 시점의 데이터는 복구할 수 없고, 데이터의 일관성이 깨질 수 있기 때문에 끄지 않는 것이 좋다
어댑티브 해시 인덱스
어댑티브 해시 인덱스: 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스
기존의 B-Tree 인덱스도 데이터의 처리가 빠르지만, 특정 값을 찾기 위해서는 리프노드를 찾아가야 하기 동시에 많은 작업을 처리하면 성능이 안좋아지는데, 이때 어댑티브 해시 인덱스는 검색 시간을 줄여줄 수 있다
어댑티브 해시 인덱스는 자주 읽히는 페이지의 키 값을 이용해 인덱스를 만들어 루트부터 리프 노드까지 찾아가는 비용이 없어진다
어댑티브 해시 인덱스가 성능 향상에 도움이 되지 않는 경우
- 디스크 읽기가 많은 경우
- 특정 패턴의 쿼리가 많은 경우
- 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
어댑티브 해시 인덱스가 성능 향상에 도움이 되는 경우
- 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우
- 동등 조건 검색이 많은 경우
- 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
어댑티브 해시 인덱스 또한 저장 공간인 메모리를 사용하고, 테이블의 삭제 작업에도 많은 영향을 미치는 등 불필요한 오버헤드를 발생 시킬 수 있다
InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교
MySql 8.0 부터는 모든 시스템 테이블이 InnoDB를 사용하게 교체
MyISAM, MEMORY 스토리지 엔진은 도태되는 중
MyISAM 스토리지 엔진 아키텍처
키 캐시
InnoDb의 버퍼 풀과 비슷한 역할을 하지만 인덱스만을 대상으로 작동
키 캐시는 99%를 유지하는게 좋고 99% 미만이면 캐시를 조금 더 크게 설정하는 것이 좋다
운영체제의 캐시 및 버퍼
MyISAM은 디스크로부터의 I/O를 해결해 줄 만한 캐시나 버퍼링 기능도 갖고 있지 않다
운영체제의 캐시 기능은 InnoDB보다 떨어지지만 없는 것 보다는 낫다
여유 메모리가 없을 시 MyISAM 테이블에 대한 쿼리 처리가 느려질 수 있다
데이터 파일과 프라이머리 키 구조
InnoDB: 프라이머리 키에 의해서 클러스터링되어 저장
MyISAM: 프라이머리 키에 의한 클러스터링 없이 힙 공간처럼 활용
MySql 로그 파일
로그 파일로 상태나 부하 원인을 쉽게 찾을 수 있다
에러 로그 파일
MySql 설정 파일에서 log_error라는 이름의 파라미터로 정의된 경로에 생성
별도로 정의되지 않은 경우에는 데이터 디렉터리에 .err라는 확장자가 붙은 파일로 생성
다음 정보들을 포함
- MySql이 시작하는 과정과 관련된 정보성 및 에러 메시지
- 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지
- 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
- 비정상적으로 종료된 커넥션 메시지
- InnoDB의 모니터링 또는 상태 조회 명령의 결과 메시지
- MySql의 종료 메시지
제너럴 쿼리 로그 파일
general_log_file이라는 이름의 파라미터에 설정이 되어 있다
SHOW GLOBAL VARIABLES LIKE 'general_log_file';슬로우 쿼리 로그
슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다
정상적으로 실행되고 스레시 홀드를 넘긴 쿼리들만 기록이 된다
pt-query-digest 명령 실행 시 —order-by 옵션으로 정렬 순서를 변경할 수 있다
