MySQL Architecture

[daehwan2da]

MySQL engine

client 와의 network I/O 를 담당한다.

• Connection Handler
• SQL parser
• 전처리기
• Optimizer

Storage engine

Disk I/O 를 담당한다.

• 성능에 직결되며, 성능 향상을 위한 여러 처리들이 적용됨
  • key cache (MyISAM)
  • Buffer pool (Inno)

InnoDB

MySQL Threading architecture

process 가 아닌 thread 기반 동작

• inno db 에서 background 활용을 적극적으로한다.

foreground

접속된 client 의 수 (session) 만큼 존재하며, 각 클라이언트의 요청 쿼리를 수행한다. 사용자의 connection 이 끝나면 Thread Cache (Thread Pool) 로 재 적재.

• buffer 나 cache 로 부터 데이터를 읽어오며, hit 하지 않는 경우 disk 에 접근해 read
  • MyISAM 의 경우 disk write 작업을 foreground thread 에서 수행
  • inno db 의 경우 disk write 작업은 background thread 에서 수행

background

사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연처리 (버퍼링) 되어질 수 있지만, 읽기 작업은 지연될 수 없다. (동기적?)

• inno db 같은 경우 쓰기 작업을 buffer 하고, 일괄적으로 처리하게 되어진다.
  • 따라서 write 작업 시 disk 에 완전히 쓰여질 때까지 기다릴 필요없이 정상 응답을 client 에게 제공이 가능하다.

[daehwan2da]

메모리 할당 및 사용 구조

global 영역과 local (session) 영역으로 구분해 할당되어진다.

global

• 모든 쓰레드가 공유하는 메모리 공간
• table cache
• inno db buffer pool
• redo buffer
• ..

local (session)

client thread 의 쿼리 작업에 필요한 메모리 공간으로, 각 요청 쓰레드마다 독립적으로 할당받아진다. (공유되어지지 않는다.)

• sort buffer
• join buffer
• network buffer
• ..

[daehwan2da]

Transaction 지원 metadata

MySQL 5.0 에서는 파일 기반의 메타데이터 생성 및 변경 작업에 대한 transaction 은 제공되어지지 않았기 때문에 파일 생성 또는 변경 도중 MySQL 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남는 문제가 있었다.

• MySQL 8.0 부터는 data dictionary, system table 모두 transaction 기반의 inno db storage 엔진에 저장되도록 개선
  • 스키마 변경 작업 중 server 가 종료되어도 원자성이 제공되어진다. (완전한 성공 혹은 실패)
  • 진행 중인 상태로 남으며 발생하는 문제를 막는다.

[daehwan2da]

InnoDB Storage Engine Architecture

mysql storage engine 중 유일하게 레코드 기반의 잠금을 제공

• 높은 동시성 처리가 가능하고, 안전적이며 성능이 뛰어나다.

primary key 에 의한 clustering

pk 순서대로 disk 에 저장되어지며, primary key 가 clustering index 이기 때문에 primary key 를 이용한 range scan 은 상당히 빠르게 처리되어진다.

• optimizer 에서 primary key 는 가장 높은 우선순위를 지니게 된다.

(MyISAM 의 경우 clustering key 를 지원하지 않아서, primary key 와 secondary index 는 구조적으로 아무런 차이가 없다.)

MVCC (Multi Version Concurrency Control)

record level 의 transaction 을 지원하기 위한 기능으로, 잠금을 사용하지 않는 일관된 읽기를 제공한다.

• innoDB 는 undo log 를 통해 해당 기능을 구현한다.
  • commit 이 된다고 undo 가 항상 삭제되어지는 것이 아니라, 필요로 하는 transaction 이 모두 완료되었을때 비로소 삭제되어진다.
• 하나의 record 에 대해 두가지 버전이 유지되어지고 있고, 필요에 따라 어느 데이터가 보여지는지 여러 상황에 따라 달라지는 구조

multi version 은, 하나의 record 에 대해 여러 개의 version 이 동시에 관리되어지는 경우를 의미한다.
- isolation level 에 대한 이해 필요

## READ_COMMITED 인 innoDB 테이블의 데이터 변경 처리 과정

### 한 건의 record 를 insert 하고 update 과정의 절차
[1] INSERT, COMMIT
  - insert 문이 실행된 경우 db 상태
    - (inno db) buffer pool 에 record 가 적재되어지고, disk 에 write 되어진다.

[2] UPDATE
  - update 문이 실행된 경우 db 상태
    - commit 여부와 관계 없이 buffer pool 의 record 가 update 되어지고, undo log 에 update 전 데이터가 buffer 되어진다.
    - disk 에는 값의 변경이 반영되어졌을수도 있고, 아닐 수도 있다.
      - Inno DB 가 ACID 를 보장하기 때문에 일반적으로 inno DB 의 buffer pool 과 file 의 상태는 동일한 상태라고 봐도 무방하다

(아직 COMMIT 을 요청한 상태는 아님)

[3] SELECT 를 통해 record 조회 
  - mysql server 의 시스템 변수인 transaction_isolation 에 설정된 isolation level 에 따라 달라진다.
  - READ_COMMITED 상태이므로, buffer 의 값이 아닌, undo 영역의 데이터를 반환한다.

Non-locking Consistent Read

격리 수준이 SERIALIZABLE 이 아닌 READ_UNCOMMITED, READ_COMMITED, REPEATABLE_READ 의 경우는 insert 와 연결되지 않은 select 작업에 대해 다른 transaction 의 변경 작업 없이 잠금 대기 없이 바로 실행되어진다.

• READ_UNCOMMITED
  • commit 되어지지 않은 buffer 의 값에 접근해 read
• READ_COMMITED, REPEATABLE_READ
  • undo log 로 접근해 read

---

InnoDB Buffer Pool

disk 의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간

• 쓰기 작업을 지연시켜 일괄 처리를 도와주는 buffer 역할 buffer pool 은 변경된 데이터들을 모아서 처리해 랜덤한 disk 작업 횟수를 줄인다.
• insert, update, delete 처럼 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 disk 작업을 발생시킨다.

Buffer pool 의 구조

innodb_page_size 의 값을 바탕으로 쪼개어 inno db storage engine 이 데이터를 필요로 할 때 읽어서 저장\

LRU list

• 필요한 레코드가 저장된 data page 가 buffer pool 에 있는지 검사
  • adaptive hash index 를 이용해 page 검색
  • table 의 b-tree 를 이용해 buffer pool 에서 page 검색
  • buffer pool 에 data page 가 있다면, 해당 page 의 pointer 를 MRU 로 승급
• disk 에 필요한 data page 를 buffer pool 에 적재, 적재된 page 에 대한 pointer 를 LRU header 로 추가
• LRU header 에 적재된 data page 가 실제로 읽히면 MRU header 로 이동
• buffer pool 에 상주하는 data page 는 얼마나 최근에 접근했는지 에 따라 Age 부여

Flush list

disk 로 동기화되지 않은 데이터를 가진 data page (dirty page) 의 변경 시점 기준의 페이지 목록

• disk 에서 읽은 상태 그대로라면 flush list 에서 관리되어지지 않음
• 데이터 변경이 가해진 data page 에 대해 관리, 특정 시점이 되면 disk 로 기록돼야 한다.

Free list

innoDB buffer pool 에서 비어 있는 페이지들의 목록, 사용자의 쿼리가 disk 로 부터 i/o 가 발생해 적재되어야하는 경우 사용됨 (cache miss)