MySQL 전체 구조
4MySQL의 전체 구조는 MySQL 엔진, 스토리지 엔진, 핸들러 API으로 나뉜다.
MySQL 스레딩 구조
MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 동작한다. MySQL 서버에서 동작하는 스레드는 포그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분된다.
포그라운드(Foreground) 스레드는 최소 MySQL 서버에 접속된 클라이언트의 수만큼 존재한다. 그리고 커넥션 종료 후 스레드 캐시(Thread cache)로 돌아간다. 포그라운드 스레드는 MySQL의 데이터 버퍼나 캐시로부터 가져오고 없으면 직접 디스크의 데이터나 인덱스 파일로부터 데이터 읽어오는 역할을 한다. 다만 InnoDB는 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리한다.
백그라운드(Background) 스레드의 종류는 대표적으로 로그를 디스크로 기록하는 로그 스레드(Log thread), 쓰기 스레드(Write thread)이 있다. 읽기 작업과 달리 쓰기 작업은 지연(버퍼링)될 수 있어 InnoDB는 쓰기 작업을 버퍼링해서 일괄 처리한다.
메모리 할당 및 사용 구조
MySQL은 글로벌 메모리 영역과 로컬 메모리 영역으로 구분된다.
글로벌 메모리은 MySQL 서버가 시작되면서 운영체제로부터 할당받는 영역이다. 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당받는다. 모든 스레드에 의해 공유되며 테이블 캐시, InnoDB 버퍼 풀, InnoDB 어댑티브 해시 인덱스, InnoDB 리두 로그 버퍼가 글로벌 메모리 영역에 속한다.
반대로 세션 메모리라고도 불리는 로컬 메모리는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다. 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되지 않는다. 각 쿼리의 용도별로 필요할 때만 공간 할당되며 필요하지 않는 경우 MySQL이 메모리 공간을 할당조차 하지 않는다. 로컬 메모리 영역은 정렬 버퍼(Sort Buffer), 조인 버퍼, 바이너리 로그 캐시, 네트워크 버퍼가 있다.
쿼리 실행 구조
쿼리 파서는 쿼리 문장을 토큰으로 분리해 트리 형태의 구조 만든다. 쿼리 문장의 기본 문법 오류는 이 과정에서 발견된다.
전처리기는 파서 트리를 기반으로 쿼리 문장에 구조적 문제점이 있는지 확인한다.
옵티마이저는 쿼리 문장을 저렴한 비용으로 빠르게 처리할지 결정한다.
실행엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결한다.
핸들러(스토리지 엔진)는 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할이다.
쿼리 캐시
MySQL 8.0으로 올라오면서 쿼리 캐시는 MySQL 서버의 기능에서 완전히 제거되었다.
InnoDB 스토리지 엔진 아키텍처
InnoDB는 MySQL의 스토리지 엔진 가운데 가장 많이 사용된다.
프라이머리 키에 의한 클러스터링
InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링한다. 프라이머리 키 값의 순서대로 디스크에 저장하며 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키 값을 논리적인 주소로 사용한다. 그리고 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정된다.
외래 키 지원
MyISAM이나 MEMORY 테이블에서 사용 못하고 InnoDB 스토리지 엔진 레벨에서 지원한다. 다만 외래 키는 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는다.
foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다. 비활성화시 외래 키 관계의 부모 테이블에 대한 작업(ON DELETE CASCADE와 ON UPDATE CASCADE 옵션)도 무시된다.
MVVC(Multi Version Concurrency Control)
스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공한다. 때문에 높은 동시성 처리 가능 및 안정적, 성능 뛰어나다.
MVVC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 것이다. InnoDB는 언두 로그(undo log)를 이용해 이 기능을 구현한다. 하나의 레코드에 대해 여러 개의 버전이 유지되고 필요에 따라 어느 데이터가 보여지는지 상황에 따라 달라진다. 여기서 필요의 따라는 격리 수준(Isolation level)에 따라 다르다는 것을 뜻한다.
커밋이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제 안돼고 이 언두 영역을 필요로하는 트랜잭션이 더는 없을 때 비로소 삭제된다.
잠금 없는 일관된 읽기(Non-Locking Consistent Read)
InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다. 격리 수준이 SERIALIZABLE이 아닐 경우 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 읽기 작업 가능하다.
이때 오랜 시간 동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제 발생하므로 주의해야 한다. 일관된 읽기를 위해 언두 로그를 삭제하기 못하고 계속 유지하기 때문이다.
자동 데드락 감지
InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다.
이 그래프를 통해 교착 상태에 빠진 트랜잭션을 찾아서 그중 하나를 강제 종료한다. 여기서 언두 로그 레코드를 더 적게 가진 트랜잭션이 롤백의 대상이다. 왜? 롤백해도 언두 처리를 해야 할 내용이 적고 따라서 MySQL 서버의 부하도 적기 때문이다.
자동화된 장애 복구
MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된(Partial write) 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다.
InnoDB 버퍼풀
디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시하고쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할이다.
버퍼 풀 크기는 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정해야 한다. MySQL 5.7부터 InnoDB 버퍼 풀의 크기를 동적으로 조절 가능하다.
InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정한 뒤 조금씩 증가하는게 좋다. 하지만 버퍼 풀의 크기 변경은 크리티컬한 변경으므로 MySQL 서버가 한가한 시점에 진행해야 한다. 다만 버퍼 풀의 크기를 줄이는 작업은 서비스 영향도가 매우 크므로 가능하면 하지 않는게 좋다.
버퍼 풀 구조
버퍼 풀이라는 거대한 메모리 공간은 페이지 크기의 조각으로 쪼개져 있다. InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다.
버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 크게 LRU 리스트, 플러시 리스트, 프리 리스트 3개의 자료 구조 관리한다.
프리 리스트는 InnoDB 버퍼풀에서 비어 있는 페이지들의 목록으로 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다.
LRU 리스트 = LRU + MRU(Most Recently Used)로 Old 서브리스트는 LRU에 해당하고 New 서브리스트는 MRU에 해당한다. LRU를 관리함으로써 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼풀의 메모리에 유지해 디스크 읽기 최소화한다.
플러시 리스트는 일단 한 번 데이터 변경이 가해진 데이터 페이지 관리한다. 그리고 특정 시점이 되면 디스크로 기록된다.
버퍼 풀과 리두 로그
InnoDB 버퍼풀과 리두 로그는 매우 밀접한 관계이다. 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐싱 기능만 향상, 쓰기 버퍼링과 무관하다.
버퍼풀은 디스크에서 읽은 상태로 전형 변경되지 않은 클린 페이지(Clean Page)와 함께 변경된 데이터를 가진 더티 페이지(Dirty Page)를 가진다. InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 더티 페이지를 디스크로 동기화한다.
일반적으로 리두 로그는 변경분만 가지고 버퍼 풀은 데이터 페이지를 통째로 가지고 있다. 따라서 버퍼 풀의 크기가 100GB 이하의 MySQL 서버에서는 리두 로그 파일의 전체 크기를 대략 5~10GB 수준으로 선택하는 것이 좋다.
버퍼 풀 플러시(Buffer Pool Flush)
InnoDB 스토리지 엔진은 버퍼 풀에서 더티 페이지들을 디스크에 동기화하기 위해 2개의 플러시 기능을 백 그라운드에서 실행한다.
- 플러시 리스트(Flush_list) 플러시
- LRU 리스트(LRU_list) 플러시
플러시 리스트 플러시
리두 로그 공간 재활용을 위한 오래된 리두 로그 엔트리의 사용 공간을 비워야 한다. 오래된 리두 로그 공간이 지워지려면 반드시 InnoDB 버퍼풀의 더티 페이지 먼저 디스크 동기화해야 한다. 이때 얼마나 많은 더티 페이지를 한 번에 디스크로 기록하는냐에 따라 사용자의 쿼리가 악영향을 받지 않으면서 부드럽게 처리된다. 이를 위해 InnoDB 스토리지 엔진은 여러가지 시스템 변수를 지원한다.
- innodb_max_dirty_pages_pct : 더티 페이지 비율 조정
- innodb_max_dirty_pages_lwm : 일정 수준 이상의 더티 페이지 발생할면 더티 페이지를 디스크 기록
- innodb_io_capacity, innodb_io_capacity_max : 데이터베이스 서버에서 어느 정도의 디스크 읽고 쓰기가 가능한지 설정
관리해야 할 MySQL 서버가 많다면 일일이 서버의 트래픽을 봐가면서 시스템 변수를 조절하는 것은 어렵다. 따라서 리두 로그의 증가 속도를 분석해서 적절한 수준의 더티 페이지가 버퍼풀에 유지될 수 있도록 디스크 쓰기 실행하는 어댑티브 플러시 기능을 제공한다.
LRU 리스트 플러시
LRU 리스트에서 사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만든다. LRU 리스트의 끝부분부터 시작해서 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼 페이지들을 스캔한다. 이때 스캔하면서 더티 페이지는 디스크에 동기화한다. 그리고 클린 페이지는 즉시 프리(Free) 리스트로 페이지 옮겨진다.
버퍼 풀 상태 백업 및 복구
디스크에 데이터가 버퍼풀에 적재되어이 있는 상태를 워밍업(Warming Up)이라 한다. 버퍼 풀이 잘 워밍업된 상태에서는 그렇지 않은 경우보다 몇십 배의 쿼리 처리 속도를 보인다.
MySQL 5.6 버전부터 버퍼 풀 덤프 및 적재 기능을 도입했다. 서버 점검 등 MySQL 서버를 재시작해야 하는 경우 셧다운 전 InnoDB 버퍼 풀의 상태 백업한다. 그리고 MySQL 서버를 다시 시작하면 백업된 버퍼 풀의 상태를 다시 복구한다.
Double Write Buffer
페이지가 일부만 디스크 파일에 기록되는 현상을 파셜 페이지(Partial-page) 또는 톤 페이지(Torn-page)라 한다. 실제 데이터 파일에 변경 내용을 기록하기 전에 더티 페이지를 묶어서 한 번의 디스크 쓰기로 테이블스페이스의 DoubleWrite 버퍼에 기록한다. 참고로 버퍼의 내용은 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래의 목적으로 사용된다.
언두 로그
언두로그는 DML로 변경되기 이전 데이터를 별도로 백업한다. 롤백되면 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구하여 트랜잭션을 보장한다. 특정 커넥션에서 데이터를 변경하는 도중 다른 커넥션에서 조회하면 격리 수준에 맞게 변경 중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어 반환하여 격리 수준 보장한다.
장기간 활성화된 트랜잭션은 문제를 발생시킨다. 트랜잭션이 시작된 시점부터 생성된 언두 로그를 계속 보존한다. 그리고 빈번하게 변경된 레코드를 조회하는 쿼리가 실행되면 언두 로그의 이력을 필요한 만큼 스캔해야 하므로 쿼리 성능이 떨어진다.
언두 테이블스페이스
언두 로그가 저장되는 공간으로 하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯(Undo Slot)을 가진다.
언두 테이블스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것을 Undo tablespace truncate라고 한다.
체인지 버퍼
레코드가 insert, update될 때 해당 테이블에 포함된 인덱스를 업데이트하는 작업이 필요하다. 이 작업은 랜덤하게 디스크를 읽어야 하므로 테이블에 인덱스 많다면 많은 자원이 소모된다.
인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트 수행되지만 디스크로부터 읽어와야 한다면 체인지 버퍼라는 임시 메모리에 저장된다. (인덱스 쓰기 지연) 다만 결과 전달 전 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼 사용 불가하다.
리두 로그 및 로그 버퍼
리두 로그(Redo Log)는 영속성과 관련있다. 리두 로그는 쓰기 비용이 낮은 자료 구조이다. 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록된다.
여기서 리두 로그 버퍼와 리두 로그 파일은 다르다. 디스크 기록이란 리두 로그 파일을 말한다. (이때 용어가 정확히 무엇을 가리키는지 몰라서 스터디원과 마구 대화했다🤪)
MySQL 서버가 비정상 종료되어 일관되지 않는 데이터 가질 경우 리두 로그와 언두 로그를 이용해 복구한다.
- 커밋됐지만 데이터 파일에 기록 안됨 → 리두 로그에 데이터 복사
- 롤백됐는데 데이터 파일에 기록됨 → 언두 로그에서 변경되기 전 데이터 가져와 복사
어댑티브 해시 인덱스
B-Tree는 사용자가 수동으로 생성하는 인덱스이다. 반변 어댑티브 해시 인덱스는 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다. B-Tree의 검색 시간 줄여주기 위해 도입되었고 B-Tree를 루트 노드부터 리프 노드까지 찾아가는 비용을 없애기 위함이다.
어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리한다. 따라서 버퍼 풀에서 해당 페이지 정보가 없어지면 어댑티브 해시 인덱스에서도 사라진다.
참고로 데이터 페이지를 디스크에서 읽어오는 경우가 빈번할 때 어댑티브 해시 인덱스가 성능 향상에 도움 안된다.
어댑티브 해시 인덱스는 저장 공간인 메모리 사용한다. 테이블 삭제하거나 변경하려고 하면 InnoDB 스토리지 엔진은 테이블의 모든 데이터 페이지 내용을 어댑티브 해시 인덱스에서 삭제해야 한다. 따라서 어댑티브 해시 인덱스의 도움을 많이 받을 수록 테이블 삭제 또는 변경 작업은 더 치명적인 작업이 된다.
MySQL 서버의 상태 값들을 통해 어댑티브 해시 인덱스가 도움이 되는지 불필요한 오버헤드만 만들고 있는지 판단해야 한다.
InnoDB와 MyISAM, MEMORY 스토리지 엔진 비교
MySQL 8.0으로 업그레이드되면서 MySQL 서버의 모든 시스템 테이블이 InnoDB 스토리지 엔진으로 교체되었다.
MySQL 8.0에서 MySQL 서버의 모든 기능이 InnoDB 스토리지 엔진 기반으로 개편되었다. 따라서 MyISAM 스토리지 엔진만이 가지는 장점 없어졌다. MEMORY 스토리지 엔진은 이전 버전과의 호환성 유지 차원일 뿐 향후 버전에선 제거될 것으로 예상된다.
MySQL 로그 파일
로그 파일을 이용하면 MySQL 서버의 깊은 내부 지식이 없어도 MySQL의 상태나 부하를 일으키는 원인을 쉽게 찾아서 해결할 수 있다.
에러 로그 파일
MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일이다. MySQL 설정 파일(my.cnf)에서 log_error라는 이름의 파라미터로 정의된 경로에 생성된다.
제너럴 쿼리 로그 파일(General log)
쿼리 로그 파일에는 시간 단위로 실행됐던 쿼리의 내용이 모두 기록된다. 슬로우 쿼리 로그와 다르게 제너럴 쿼리 로그는 실행되기 전에 MySQL이 쿼리 요청을 받으면 바로 기록한다. 쿼리 실행 중 에러가 발생해도 일단 로그 파일에 기록한다.
슬로우 쿼리 로그
long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다. 반드시 쿼리가 정상적으로 실행이 완료돼야 슬로우 쿼리 로그에 기록된다.
참고
- https://dev.mysql.com/doc/refman/8.0/en/ (MySQL 8.0 공식문서)
- RealMySQL 8.0