Mysql 8.0 Architecture

Mysql Architecture

Mysql 엔진

Mysql 서버의 머리 역할을 담당한다

• 커넥션 핸들러: 클라이언트로부터의 접속 및 쿼리 요청
• SQL 인터페이스: DML, DDL 등 인터페이스 제공
• SQL 파서: 쿼리를 트리 구조의 작은 단위로 분리하는 작업을 수행
• 전처리기: 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리에 문제가 있는지 파악
• SQL 옵티마이저
• 쿼리 실행기: 스토리지 엔진에 쿼리 수행을 요청하며 이때 핸들러 API를 이용
• 캐시 & 버퍼

핸들러 API

쿼리 실행기에서 데이터를 쓰거나 읽어야 할때는 각 스토리지 엔진에 핸들러 요청을 수행해야 한다

스토리지 엔진

데이터 읽기/쓰기와 같은 Mysql 서버의 손과 발 역할을 수행한다

쿼리 캐시

데이터가 잦은 변경이 일어나는 경우에는 잦은 업데이트가 필요해 오히려 동시 처리 성능 저하를 유발한다
또한 이 기능은 많은 버그의 원인이 되어 mysql 8.0에서 제거 됨

InnoDB 스토리지 엔진

Mysql에서 사용할수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금 사용

프라이머리 키에 의한 클러스터링 사용

프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻
모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용.
PK가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔이 상당이 빨리 처리될 수 있다.
Oracle DBMS의 IOT(Index organized table)와 동일한 구조가 InnoDB에서는 일반적인 구조이다.

Oracle DBMS의 IOT

Table random Access (한 번에 하나의 블록만을 액세스 하는 싱글 블록 I/O 방식)가 발생하지 않도록 처음부터 인덱스 구조로 생성된 테이블이다
PK 컬럼 순으로 데이터가 정렬된 상태를 유지하기 때문에 클러스터링 팩터가 좋다
또한 PK 인덱스를 위한 별도의 세그먼트 생성이 불필요하다
Random Access가 아닌 Sequential Access 방식이므로 넓은 범위의 Access에서 장점을 가진다
하지만 DML시에 인덱스 분할로 인하여 부하가 발생한다는 단점도 존재한다

외래키 지원

MyISAM과 Memory 테이블에서는 사용 불가능.

MVCC (Multi Version Concurrency Control)

Undo log를 이용하여 이 기능을 제공하며 잠금을 사용하지 않는 일관된 읽기를 제공하는것이 가장 큰 목적이다
일관된 읽기를 위해 언두 로그를 생성하였으나 오랜 시간 동안 트랜잭션이 활성 상태로 존재한다면 언두 로그를 삭제하지 못하여 서버가 느려지는 등 문제가 발생할수 있다

Undo Log의 용도

• 트랜잭션의 롤백 대비용
• 트랜잭션의 격리 수준을 유지하면서 높은 동시성 제공

자동 데드락 감지

내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리
데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사하여 교착 상태의 트랜잭션들을 찾아 강제 종료함.
언두 로그 레코드를 더 적게 가진 트랜잭션을 일반적으로 먼저 롤백하여 제거함 (롤백해도 언두 처리를 할 내용이 적기 때문)

InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금은 볼수가 없어 데드락 감지가 불확실할수 있는데,
innodb_table_locks 시스템 변수를 활성화하여 테이블 레벨의 잠금까지도 감지할수 있게 지정할수 있다. (추천)

InnoDB 버퍼 풀

데이터를 변경하는 쿼리는 랜덤한 디스크 작업을 발생 시키기 때문에
버퍼 풀을 이용하여 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다
이를 위해 더티 페이지(디스크에 반영되지 않은 데이터)를 주기적으로 체크하여 디스크에 쓰는 작업을 수행한다

InnoDB 버퍼 풀 구조

• New 서브리스트 -> MRU (Most Recently Used)
• Old 서브리스트 -> LRU (Least Recently Used)

성능 향상을 위해 다음 두가지 용도가 있음.

• 데이터 캐시
• 쓰기 버퍼링

Redo log

버퍼 풀은 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지와 변경된 데이터를 가지고 있는 더티 페이지를 가지고 있다
더티 페이지는 디스크와 버퍼 풀의 상태가 다르기 때문에 언젠가 디스크로 기록되므로 버퍼 풀에 무한정 머무르지 않는다
리두 로그는 1개 이상의 고정 크기 파일을 연결하여 순환 고리처럼 사용되는데, 기존 로그 엔트리를 새로운 로그 엔트리가 계속 덮어 씌우면서 순환된다
이때 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분하면서 관리를 하게 되는데, 재사용 불가능한 공간을 활성 리두 로그(Active Redu Log)라 한다
리두 로그 파일은 매번 기록될 때마다 로그 포지션이 증가하는데 이를 LSN(Log Sequence Number)라 하고,
InnoDB 스토리지 엔진은 주지적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화를 한다.

What's difference between Undo log and Redo log

언두 로그는 트랜잭션의 기록이고 Redo log는 디비에 대한 모든 변경사항을 파일로 기록하여 disk, system failure에 대한 보호 역할을 수행한다

Double Write Buffer

InnoDB의 스토리지 엔진에서 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 문제가 발생할 수 있다.
이렇게 페이지의 일부만 기록되는 현상을 파셜 페이지(Partial-page) 혹은 톤 페이지(Torn-page)라고 한다.

이를 막기 위해 InnoDB 버퍼 풀의 더티 페이지에 존재하는 데이터를 디스크로 쓰기 전에
Double Write Buffer(시스템 테이블스페이스)에 변경된 데이터 페이지를 한번에 기록하고,
그 이후에 데이터 페이지를 개별로 파일에 기록한다

체인지 버퍼 (Change Buffer)

인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 많은 자원을 소모하게 된다
그래서 인덱스를 디스크가 아닌 버퍼 풀에서 업데이트 하는 방법으로 성능을 향상시키게 되는데,
이 임시 메모리 공간을 체인지 버퍼라고 한다

어댑티브 해시 인덱스

InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이며 innodb_adaptive_hash_index 시스템 변수를 이용하여 활성화/비활성화 설정 가능하다
B-Tree 인덱스는 비교적 빠르지만 루드 노드에서 브랜치 노드, 리프 노드까지 찾아가는데 걸리는 시간이 중첩되면 많은 CPU 프로세스 스케줄링을 소모하게 된다
그로인해 이러한 B-Tree 검색 시간을 줄이기 위해 어댑티브 해시 인덱스가 도입되었다
해시 인덱스는 인덱스 키 값과 해당 인덱스 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리되는데,
인덱스 키 값은 B-Tree 인덱스의 고유번호(Id)와 B-Tree 인덱스의 실제 키값 조합으로 생성된다

성능 향상에 도움이 되지 않는 경우

• 디스크 읽기가 많은 경우
• 특정 패턴의 쿼리가 많은 경우 (조인이나 LIKE 패턴 검색)
• 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

성능 향상에 도움이 되는 경우

• 디스크의 데이터가 innoDB 버퍼 풀 크기와 비슷한 경우 (디스크 읽기가 많지 않은 경우)
• 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
• 쿼리가 데이터 중에서 일부 데이터만 집중되는 경우

MyISAM 스토리지 엔진

InnoDB가 프라이머리 키에 의해 클러스터링 되는 반면, MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙(Heap) 공간처럼 활용된다
즉 PK 값과 무관하게 Insert 되는 순서대로 데이터 파일에 저장된다
MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주소값을 가지는데, 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가진다

테이블 잠금을 지원하는 MyISAM과 다르게 레코드 잠금을 지원하는 InnoDB는 mysql 8.0버전부터 모든 기능을 InnoDB 스토리지 엔진만으로 구현할수 있게 되었고,
그에 따라 MyISAM 스토리지 엔진의 기능으 도태되고 있다

Memory 스토리지 엔진

Mysql 5.7버전까지만 Memory 스토리지 엔진이 내부 임시 테이블의 용도로 사용되었으나 가변 길이 타입의 칼럼을 지원하지 않는 문제점 때문에
8.0 버전부터는 TempTable 스토리지 엔진이 해당 자리를 대체하고 있다

참고

Real Mysql 8.0 서적