MySQL 8.0 아키텍처

Mugeon Kim·2024년 1월 7일

서론

이전에 Real MySQL을 보면서 학습을 하였지만 SQL 레벨업 책을 읽고 추가적으로 학습이 필요한 부분을 작성을 하였다.
우리가 아키텍처에 대해서 알아야 하는 이유는 쿼리를 입력을 하였을 때 관계형 디비에서 (mysql) 해당 동작을 어떻게 처리하고 성능을 가질지 이해하기 위한 중요한 부분이라고 생각하여 정리를 한다.

본론

1.MySQL 구조

MySQL 서버는 크게 엔진과 스토리지 엔진으로 구분한다. 여기서 스토리지 엔진은 버전에 따라서 default에 따라서 다르다.

1-1. MySQL 엔진

MySQL은 간단하게 요약하면 (1) 쿼리를 해석하고 파싱 (2) 옵티마이저를 통해 실행 계획을 세우고 (3) I/O를 통해 읽어온 데이터를 메모리에 처리한다.

1-1-1. SQL 파서, 전처리기

파서는 SQL을 받은 것을 구문 분석을 한다. 사용자가 쉼표를 잊거나 from을 잊으면 에러가 발생한다.
전처리기는 테이블의 실존 여부, 권한 . 등런타임에만 판단 가능한 부분을 실제로 실행 가능한 쿼리인지 판단한다.

즉. 파서는 정적 단계의 구문 분석과 정적 단계에서 에러를 처리하고 전처리기는 런타임 환경에서 실제로 실행 가능한 쿼리인지 판단한다.

1-1-2. SQL 옵티마이저

SQL 쿼리를 효율적으로 사용할 수 있다록 실행 계획을 만든다. 쿼리 튜닝의 핵심으로 통계정보에 따라서 달라질 . 수있다.
이때 여러 개의 실행 계획을 만들어 이들의 비용을 연산하여 가장 낮은 비용을 가진 실행 계획을 선택한다.
이때 비용을 연산하기에 데이터베이스의 메타 데이터( 테이블 구조, 인덱스, 데이터 분포 )를 분석한다. 이때 이것을 카탈로그 매니저가 옵티마이저에서 중요한 정보를 관리한다.

1-1-2. 캐시 & 버퍼

MySQL에서는 쿼리 캐시를 지원하지 않는다.

테이블이 수정되면, 테이블과 관련된 cache들은 제거 되며 MySQL 5.6에서 기본적으로 비활성화 되었으며, 5.7.20에서 Deprecated 되었고 8.0 버전부터 제거된 기능이다.

MySQL 공식 홈페이지에서 나온 성능

비슷한 쿼리가 조금 달라서 cache 되지 않는다면, Query Cache로 인한 오버헤드가 13% 증가 (이 수치는 최악의 경우일 때를 가정하므로 실제 환경에서는 더 낮음)
단일 테이블에서 단일 row를 조회할 때 Query Cache를 사용하지 않으면 238% 빠른 결과를 얻음

다른 관계형 디비에서는 쿼리 캐시를 지원하지만 MySQL에서 쿼리 캐시를 지원하지 않고 인덱스 캐시, 키 캐싱을 지원한다.

1-2. 스토리지 엔진

디스크, 메모리에 접근하여 데이터를 Read, Write만 . 할수있다. 버퍼풀, 리두 로그, 언두 로구 등의 메모리 상의 캐싱, 버퍼 역활을 한다.

mysql 5.6 / mysql 8.0

기본적으로 5.6에서는 기본적으로 MyIsam로 설정이 되어져 있고 8.0에서는 InnoDB로 변경이 되었다.

간단하게 차이점을 살펴보면 다음과 같다.

(1) MyISAM : 처음에 기본 엔진으로 사용을 하였다. 키 캐싱이라는 장점이 있고 트랜잭션을 지원하지 않는다.

(2) InnoDB : 버퍼링, FK제약 조건, 충돌 복구, 트랜잭션을 지원하여 주로 InnoDB를 사용한다.

스토리지 엔진은 오직 핸들러 API를 통해서 통신할 수 있다. MySQL엔진 <-> 스토리지 엔진 <-> 디스크
즉. MySQL 엔진에서 쿼리 파서, 전처리기를 통해 정적, 동적 에러를 감지하고 옵티마이저로 실행 계획을 만들어 스레드 캐시, 버퍼를 통해 InnoDB 버퍼 풀을 사용하여 인덱스를 메모리에 캐싱하여 핸들러 API를 통해 스토리지 엔진과 통신하며 디스크I/O 작업을 하면 디스크에 통신을 한다.

2. 스토리지 엔진 아키텍처

2-1. PK, FK 클러스터링, 지원

PK는 기본적으로 프라이머리 키를 기준으로 인덱스가 생기고 이것을 기준으로 세컨 인덱스가 주소를 사용하고 데이터를 저장한다. 또한 외래 키를 지원하여 외래키에 대한 처리를 수행한다.

2-2. MVCC

다중 버전 동시성 제어를 의미한다. 이것을 사용하는 가장 큰 이유는 트랜잭션 때문이다. MVCC를 통해 데이터베이스의 동시성을 제어한다. 이때 스냅샷을 이용하여 하나의 레코드에 대한 여러 버전을 관리한다.

락을 사용하지 않고 MVCC를 사용하는 이유

락을 사용하면 가장 동시성을 쉽게 처리할 . 수있지만 동시 요청이 많이 들어오면 성능에 문제가 간다. 이를 해결하기 위해서 MYSQL에서는 MVCC를 사용하여 스냅샷으로 언두 로그를 활용한다.

위 사진을 보면 커밋을 하면 언두 로그에 쌓여 커밋이 되기 이전에 언두 로그를 통해 데이터를 . 알 수 있다. 트랜잭션과 격리 수준을 보장을 한다.
하지만 여기서 주의할 부분은 커밋하면 현재 상태를 유지하고 백업하면 롤백하기 때문에 (1) 대량의 데이터 변경 또는 삭제, 트랜잭션이 오래 유지하는 경우에는 주의를 해야된다.

즉. 트랜잭션은 가능한 짧게 유지하는 것이 중요하다.

2-3. 버퍼풀

버퍼풀은 디스크의 데이터나 인덱스 정보를 메모리에 캐시해두어 디스크 I/O가 발생하면 성능적으로 이점을 가질 수 있다.

MySQL은 기본적으로 Random I/O를 처리를 합니다. 만약에 Insert, Update, Delete 작업을 수행하게 되면 디스크에 많이 접근해서 작업을 처리해야 되는데 버퍼풀에 데이터를 먼저 바꾸고 그것을 한번에 처리하면 디스크 I/O가 줄어들어 성능적인 이점을 가져갈 수 있다. 이것을 지연로딩을 의미한다.

버퍼 풀 크기 설정하기

일반적으로 크기는 innodb_buffer_pool_size 변수를 통해서 설정한다. 일반적으로 물리 메모리의 50% 잡은 후에 조금씩 늘린다.

핵심은 버퍼풀의 구조는 자주 참조되는 페이지를 오래 버퍼 풀에 유지시켜 캐시 성능을 향상 시키는 것이다.
간단 한 원리는 참조되는 페이지는 MRU영역으로 보내 계속 살아남게 하고 자주 참조 되지 않으면 LRU영역으로 밀려나 제거될 것이다.

버퍼 풀을 크게 잡으면 생기는 오버헤드

메모리 부족 : 시스템의 다른 프로세스나 운영 체제가 필요로 하는 메모리가 부족해질 수 있습니다. 이는 시스템 전체의 성능 저하로 이어질 수 있으며, 최악의 경우 시스템의 안정성에 영향을 줄 수도 있습니다.
스왑 활동 증가: 메모리가 부족하게 되면, 운영 체제는 디스크의 스왑 공간을 사용하게 됩니다. 스왑 활동이 증가하면 시스템의 전체 성능이 저하될 수 있습니다.
캐시 관리 오버헤드: 버퍼 풀이 너무 크면, MySQL은 버퍼 풀 내의 데이터를 관리하는 데 더 많은 CPU 자원을 소모하게 됩니다. 이는 특히 버퍼 풀 내의 데이터가 자주 변경되는 환경에서 두드러질 수 있습니다.

2-4. 리두로그

버퍼 풀에 commit된 데이터는 아직 디스크에 반영되지 않았으므로 서버가 비정상 종료되면 데이터가 유실 될 것이다. 이러한 부분을 보완하기 위해서 데이터가 디스크에서 처음 읽은 (클린 페이지) 상태에서 변경된 부분만 따로 리두로그라는 부분에 기록한다. 리두로그도 주기적으로 디스크에 flush한다.