Database ‐ Database Performance and MySQL Architecture - thought-corner/backend-roadmap GitHub Wiki

MySQL 서버 5계층 아키텍처

계층	역할
커넥션 핸들러	클라이언트 접속 관리
파서	SQL 문법 검사와 구조 분석
옵티마이저	최적의 실행 계획 수립
실행 엔진	실행 계획에 따라 스토리지 엔진 호출
스토리지 엔진	실제 데이터 저장과 조회

1계층 정리 : 커넥션 핸들러(Connection Handler)

• 커넥션 핸들러는 클라이언트의 접속을 관리하는 계층이다.
• 클라이언트가 MySQL 서버에 접속하면 다음과 같은 과정이 일어난다.

1. TCP/IP 등으로 연결 요청을 받는다.
2. 사용자명, 비밀번호, 호스트를 확인한다.
3. 이 사용자가 어떤 데이터베이스에 접근할 수 있는지 권한을 확인한다.
4. 전용 쓰레드(Thread)를 할당한다.

• MySQL은 기본적으로 쓰레드 기반(thread-per-connection) 모델을 사용한다. 클라이언트 하나가 접속하면 전용 쓰레드 하나를 배정받는다. 이 쓰레드가 해당 클라이언트의 모든 쿼리를 처리한다.

❗웹 애플리케이션 서버(WAS)가 커넥션 풀(Connection Pool)을 통해 MySQL에 접속한다. 커넥션 풀 크기를 max_connections보다 크게 설정하면 Too many connections 에러가 발생할 수 있다.

• 실무에서는 웹 애플리케이션 서버가 1대가 아니다. 트래픽이 늘어나면 WAS를 여러 대로 수평 확장하는 경우가 일반적이고 각 WAS마다 독립적인 커넥션 풀을 가진다. 따라서 MySQL이 실제로 받는 전체 커넥션 개수는 WAS 대수 x WAS당 풀 크기로 계산해야 한다.
• 예를 들어, WAS 10대가 각각 커넥션 풀 크기를 50으로 설정했다면 MySQL은 최대 500개의 커넥션을 받게 된다.
• 따라서, max_connections는 다음과 같이 설정하는 것이 안전하다.
• 산식 : (WAS 대수 x WAS당 풀 크기) + 배치/관리자/모니터링용 여유분 + 관리자 예비 슬롯
• 반대로 커넥션을 무작정 늘리는 것이 정답이 아니다. 커넥션 하나당 CPU, 메모리, 디스크 I/O, 네트워크, InnoDB 내부 락 등 여러 자원을 함께 소모하기 때문에 불필요하게 크게 잡으면 서버 전체 성능이 오히려 저하된다. 적정 커넥션 수 = 실제 동시 처리량 관점에서 설계해야 한다.
• 정확한 커넥션 수는 성능 테스트를 통해 결정해야 한다. 서비스의 실제 트래픽 패턴을 분석해서 커넥션 수를 조금씩 늘려가며, 응답 시간과 CPU, 메모리, 락 대기 지표가 악화되기 시작하는 지점을 찾는다. 그 직전 값이 해당 환경의 최대 적정 커넥션 개수이다.

2계층 : 파서(Parser)

• 파서는 SQL 문자열을 분석하는 계층이다.
• 구문 분석(Parsing) : SQL 문자열을 토큰으로 분해하고, 문법 규칙에 맞는지 검증한다.
• 파스 트리(Parse Tree) : 문법이 올바르면 내부 트리 구조로 변환해 옵티마이저에 전달한다.

3계층 : 옵티마이저(Optimizer)

• 옵티마이저(Optimizer)는 쿼리를 실행하는 최적의 경로를 결정하는 계층이다.
• MySQL의 옵티마이저는 비용 기반 옵티마이저이다. 이 경로로 가면 디스크를 몇 번 읽어야 하고, 비용이 얼마나 드는가를 계산해 가장 비용이 낮은 실행 계획을 선택한다.
• 어떤 인덱스를 사용할 것인가?
• 테이블을 어떤 순서로 접근할 것인가?
• 어떤 조인 알고리즘을 사용할 것인가?

❗옵티마이저는 항상 최선의 판단을 내리는 것이 아니다. 통계 정보가 부정확하면 잘못된 경로를 선택할 수 있다.

4계층 : 실행 엔진(Execution Engine)

• 실행 엔진은 옵티마이저가 수립한 실행 계획을 실제로 수행하는 계층이다.
• 실행 엔진은 스토리지 엔진과 핸들러 API라는 표준화된 인터페이스를 통해 통신한다. 핸들러 API 덕분에 어떤 스토리지 엔진이든 동일한 방식으로 호출할 수 있다.
• 실행 엔진이 하는 일을 단순화시키면 다음과 같다.

1. 스토리지 엔진에서 행을 하나씩 가져온다.(반복)
2. 가져온 각 행에 서버 레벨 필터링을 적용한다.
3. 결과를 모아 필요한 정렬, 그룹핑을 수행한다.
4. 최종 결과를 클라이언트에 반환한다.

5계층 : 스토리지 엔진(Storage Engine)

• 스토리지 엔진은 실제 데이터를 저장하고 읽어오는 계층이다.
• MySQL의 스토리지 엔진은 교체가 가능하다. MySQL은 Pluggable Storage Engine Architecture를 사용한다. 상위의 SQL 계층은 그대로 두고 하위의 스토리지 엔진만 바꿀 수 있다.
• InnoDB가 기본으로 선택된 이유는 다음과 같다.
  • 트랜잭션 지원 : ACID를 보장한다.
  • 행 수준 잠금 : 동시성이 좋다.
  • 외래 키 지원 : 참조 무결성을 보장한다.
  • 크래시 복구 : 비정상 종료 후에도 데이터를 복구할 수 있다.

InnoDB 아키텍처

• 스토리지 엔진은 실제 데이터를 보관하는 곳으로 생각하면 된다. 따라서 최종적으로 디스크에 데이터를 보관한다.
  • 메모리(RAM) : 매우 빠르다. 나노초(ns) 단위로 접근한다.
  • 디스크(SSD/HDD) : 메모리보다 수백 ~ 수만 배 느리다. 마이크로초(us) ~ 밀리초(ms) 단위로 접근한다.
• 디스크의 성능 문제를 보완하기 위해 InnoDB 아키텍처는 크게 다음과 같은 두 구조로 나뉜다.
  • 메모리 구조
  • 디스크 구조
• 모든 데이터를 메모리에 올리면 빠르겠지만, 메모리는 비싸고 용량이 제한적이다. 그래서 InnoDB는 자주 사용하는 데이터를 메모리에 캐싱하고, 전체 데이터를 디스크에 안전하게 보관한다.

메모리 영역 : 버퍼 풀(Buffer Pool)

• 버퍼 풀은 InnoDB에서 가장 중요한 메모리 영역이다.
• 버퍼 풀은 테이블 데이터와 인덱스 데이터를 페이지 단위로 캐싱한다. 페이지는 InnoDB가 데이터를 읽고 쓰는 최소 단위(기본 16KB)이다.
• 버퍼 풀의 핵심 원리는 단순하다.

1. 쿼리가 특정 데이터를 요청한다.
2. 해당 데이터가 버퍼 풀에 있으면 메모리에서 바로 읽는다.
3. 해당 데이터가 버퍼 풀에 없으면 디스크에서 읽어와서 버퍼 풀에 올린 다음에 읽는다.

• 버퍼 풀의 히트율이 높을수록 디스크 I/O가 줄어들고 성능이 좋아진다. 데이터베이스 전용 서버의 경우 물리 메모리의 최대 80%까지 버퍼 풀로 할당하는 것을 권장한다.

❗실제로 운영 서버에서 innodb_buffer_pool_size를 128MB 기본값으로 두는 일은 거의 없다. 전용 DB 서버라면 물리 메모리의 60 ~ 80%를 할당한다. 예를 들어 16GB 메모리 서버라면 10 ~ 12GB를 버퍼 풀로 설정하는 것을 권장한다. 나머지는 OS를 포함한 MySQL의 커넥션별 메모리 영역에서 나누어 쓰기 위해 남겨둔다. DB 세부 설정 방법은 추가로 공부해보면 좋다.

기타 영역

• 디스크 영역 : 리두 로그(Redo Log)
  • 리두 로그는 크래시 복구용 디스크 구조이다. InnoDB는 데이터 파일을 변경하기 전에 리두 로그에 먼저 기록하는 WAL(Write-Ahead Loggin) 원칙을 따른다. 서버가 갑자기 죽어도 재시작 시 리두 로그를 재생해 커밋된 트랜잭션을 복구한다.
• 메모리 영역 : 로그 버퍼(Log Buffer)
  • 로그 버퍼는 리두 로그를 디스크에 쓰기 전 임시로 모아두는 메모리 영역이다. 변경이 일어날 때마다 매번 디스크에 쓰면 너무 느리므로, 메모리에 쌓아두었다가 커밋 시점(또는 주기적으로) 한꺼번에 디스크에 내려쓴다.
• 디스크 영역 : 언두 로그(Undo Log)
  • 언두 로그는 트랜잭션 롤백과 MVCC(Multi-Version Concurrency Control)에 사용되는 이전 버전 데이터이다.
• 디스크 영역 : 테이블스페이스(TableSpace)
  • 테이블스페이스는 실제 테이블 데이터와 인덱스가 저장되는 파일이다. InnoDB는 기본적으로 테이블 하나당 .ibd 파일 하나를 가진다.

데이터 저장 구조 4계층

• InnoDB의 데이터 저장은 페이지 ⭢ 익스텐트 ⭢ 세그먼트 ⭢ 테이블스페이스 순서의 계층 구조를 가진다.

페이지 - 모든 I/O의 최소 단위

• 페이지(Page)는 InnoDB의 최소 I/O 단위이다.
• MySQL 8.4의 페이지 기본 크기는 16KB이다.
• 모든 I/O는 페이지 단위이다. 하나의 행만 읽더라도 그 행이 들어있는 16KB 페이지 전체를 디스크에서 읽어야 한다는 뜻이다. 특정 바이트 하나만 콕 집어서 읽는 것이 불가능하다. 최소 읽기 단위가 있고 InnoDB는 이를 16KB 페이지로 관리한다. 한 번 읽어온 페이지 안에는 여러 행이 들어있으므로, 같은 페이지의 다른 행을 읽을 때는 디스크를 다시 읽을 필요가 없다.
• 인덱스의 B+Tree에서 노드 하나 = 페이지 하나를 말한다.
  • 순차 I/O가 빠른 이유 : 연속된 페이지를 한꺼번에 읽을 수 있기 때문이다.
  • 랜덤 I/O가 느린 이유 : 서로 다른 위치의 페이지를 하나씩 읽어야 하기 때문이다.
• 버퍼 풀이 페이지 단위로 캐싱하는 이유 : I/O 단위 자체가 페이지이기 때문이다.
  • 작으면(4KB) : 같은 데이터를 담는 데 더 많은 페이지가 필요하다. B+Tree의 높이가 깊어져 검색 시 읽어야 할 페이지가 늘고, 큰 행은 한 페이지에 못 들어가 오버플로우가 잦아진다.
  • 크면(64KB) : 행 한 줄만 필요해도 64KB 전체를 읽고 메모리에 올려야 해서 버퍼 풀이 낭비된다. 페이지 하나에 더 많은 행이 몰려 동시성 경합도 커진다.
  • 균형(16KB) : 대부분의 행이 한 페이지에 들어간다. B+Tree 노드 하나에 수백 개의 키를 담을 수 있어 수십억 건 테이블도 대개 3~4단계 깊이로 끝난다. OS/SSD 기본 블록(4KB)의 정수배라 I/O 정렬도 깔끔해진다.

익스텐트(Extent) - 공간 할당의 단위

• 익스텐트(Extent)는 연속된 페이지의 그룹이다.
• 페이지가 I/O의 최소 단위라면 익스텐트는 공간 할당의 단위이다.
  • 테이블이 작을 때(32페이지 이하) : 페이지를 하나씩 할당한다. 작은 테이블에 1MB를 통째로 잡으면 낭비이기 때문이다.
  • 테이블이 커지기 시작하면 : 익스텐트 단위(1MB)로 전환한다. 1개씩 할당하던 것을 64개 연속 페이지 묶음으로 바꾼다.
  • 테이블이 충분히 크면 : 한 번에 최대 4개의 익스텐트까지 할당한다. 테이블이 빠르게 성장 중이면 할당 횟수를 줄여 오버헤드를 낮추는 것이다.

세그먼트(Segment) - 인덱스 저장 단위

• 인덱스 하나당 2개의 세그먼트가 할당된다.

• 비리프노드 세그먼트 : B+Tree의 내부 노드(검색 경로)를 저장한다.
• 리프노드 세그먼트 : 실제 데이터(또는 데이터를 가리키는 포인터)를 저장한다.
• MySQL InnoDB의 PK 인덱스는 리프 노드에 실제 데이터를 가지고 있고 PK가 아닌 인덱스는 리프 노드에 PK값을 가진다.

테이블스페이스(TableSpace) - 최상위 저장 단위

• 테이블스페이스는 세그먼트를 포함하는 최상위 저장 단위이다. 실제 디스크 파일 ibd에 매핑된다.
• InnoDB는 기본적으로 각각의 테이블을 각각의 파일로 관리한다.

-- 테이블별 데이터 크기와 페이지 수 계산
SELECT TABLE_NAME,
       TABLE_ROWS                              AS estimated_rows,
       ROUND(DATA_LENGTH / 1024 / 1024, 1)     AS data_mb,
       ROUND(DATA_LENGTH / @@innodb_page_size) AS estimated_pages,
       ROUND(INDEX_LENGTH / 1024 / 1024, 1)    AS index_mb,
       ROW_FORMAT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'shop'
ORDER BY DATA_LENGTH DESC;

• 인덱스도 생각보다 많은 용량을 차지한다. 인덱스는 조회를 빠르게 해주지만 공짜가 아니라는 점을 명확히 알고 있어야 한다.
• 디스크 공간을 차지하고 INSERT/UPDATE/DELETE 때마다 함께 갱신되어야 한다. 인덱스를 무작정 많이 만들면 안되는 이유가 바로 여기에 있다.