Database ‐ Database Performance and MySQL Architecture
| 계층 | 역할 |
|---|---|
| 커넥션 핸들러 | 클라이언트 접속 관리 |
| 파서 | SQL 문법 검사와 구조 분석 |
| 옵티마이저 | 최적의 실행 계획 수립 |
| 실행 엔진 | 실행 계획에 따라 스토리지 엔진 호출 |
| 스토리지 엔진 | 실제 데이터 저장과 조회 |
- 커넥션 핸들러는 클라이언트의 접속을 관리하는 계층이다.
- 클라이언트가 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, 메모리, 락 대기 지표가 악화되기 시작하는 지점을 찾는다. 그 직전 값이 해당 환경의 최대 적정 커넥션 개수이다.
- 파서는 SQL 문자열을 분석하는 계층이다.
- 구문 분석(Parsing) : SQL 문자열을 토큰으로 분해하고, 문법 규칙에 맞는지 검증한다.
- 파스 트리(Parse Tree) : 문법이 올바르면 내부 트리 구조로 변환해 옵티마이저에 전달한다.
- 옵티마이저(Optimizer)는 쿼리를 실행하는 최적의 경로를 결정하는 계층이다.
- MySQL의 옵티마이저는 비용 기반 옵티마이저이다. 이 경로로 가면 디스크를 몇 번 읽어야 하고, 비용이 얼마나 드는가를 계산해 가장 비용이 낮은 실행 계획을 선택한다.
- 어떤 인덱스를 사용할 것인가?
- 테이블을 어떤 순서로 접근할 것인가?
- 어떤 조인 알고리즘을 사용할 것인가?
❗옵티마이저는 항상 최선의 판단을 내리는 것이 아니다. 통계 정보가 부정확하면 잘못된 경로를 선택할 수 있다.
- 실행 엔진은 옵티마이저가 수립한 실행 계획을 실제로 수행하는 계층이다.
- 실행 엔진은 스토리지 엔진과 핸들러 API라는 표준화된 인터페이스를 통해 통신한다. 핸들러 API 덕분에 어떤 스토리지 엔진이든 동일한 방식으로 호출할 수 있다.
- 실행 엔진이 하는 일을 단순화시키면 다음과 같다.
1. 스토리지 엔진에서 행을 하나씩 가져온다.(반복)
2. 가져온 각 행에 서버 레벨 필터링을 적용한다.
3. 결과를 모아 필요한 정렬, 그룹핑을 수행한다.
4. 최종 결과를 클라이언트에 반환한다.
- 스토리지 엔진은 실제 데이터를 저장하고 읽어오는 계층이다.
- MySQL의 스토리지 엔진은 교체가 가능하다. MySQL은 Pluggable Storage Engine Architecture를 사용한다. 상위의 SQL 계층은 그대로 두고 하위의 스토리지 엔진만 바꿀 수 있다.
- InnoDB가 기본으로 선택된 이유는 다음과 같다.
- 트랜잭션 지원 : ACID를 보장한다.
- 행 수준 잠금 : 동시성이 좋다.
- 외래 키 지원 : 참조 무결성을 보장한다.
- 크래시 복구 : 비정상 종료 후에도 데이터를 복구할 수 있다.
- 스토리지 엔진은 실제 데이터를 보관하는 곳으로 생각하면 된다. 따라서 최종적으로 디스크에 데이터를 보관한다.
- 메모리(RAM) : 매우 빠르다. 나노초(ns) 단위로 접근한다.
- 디스크(SSD/HDD) : 메모리보다 수백 ~ 수만 배 느리다. 마이크로초(us) ~ 밀리초(ms) 단위로 접근한다.
- 디스크의 성능 문제를 보완하기 위해 InnoDB 아키텍처는 크게 다음과 같은 두 구조로 나뉜다.
- 메모리 구조
- 디스크 구조
- 모든 데이터를 메모리에 올리면 빠르겠지만, 메모리는 비싸고 용량이 제한적이다. 그래서 InnoDB는 자주 사용하는 데이터를 메모리에 캐싱하고, 전체 데이터를 디스크에 안전하게 보관한다.
- 버퍼 풀은 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파일 하나를 가진다.
- 테이블스페이스는 실제 테이블 데이터와 인덱스가 저장되는 파일이다. InnoDB는 기본적으로 테이블 하나당
- InnoDB의 데이터 저장은 페이지 ⭢ 익스텐트 ⭢ 세그먼트 ⭢ 테이블스페이스 순서의 계층 구조를 가진다.
- 페이지(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)는 연속된 페이지의 그룹이다.
- 페이지가 I/O의 최소 단위라면 익스텐트는 공간 할당의 단위이다.
- 테이블이 작을 때(32페이지 이하) : 페이지를 하나씩 할당한다. 작은 테이블에 1MB를 통째로 잡으면 낭비이기 때문이다.
- 테이블이 커지기 시작하면 : 익스텐트 단위(1MB)로 전환한다. 1개씩 할당하던 것을 64개 연속 페이지 묶음으로 바꾼다.
- 테이블이 충분히 크면 : 한 번에 최대 4개의 익스텐트까지 할당한다. 테이블이 빠르게 성장 중이면 할당 횟수를 줄여 오버헤드를 낮추는 것이다.
- 인덱스 하나당 2개의 세그먼트가 할당된다.
- 비리프노드 세그먼트 : B+Tree의 내부 노드(검색 경로)를 저장한다.
- 리프노드 세그먼트 : 실제 데이터(또는 데이터를 가리키는 포인터)를 저장한다.
- MySQL InnoDB의 PK 인덱스는 리프 노드에 실제 데이터를 가지고 있고 PK가 아닌 인덱스는 리프 노드에 PK값을 가진다.
- 테이블스페이스는 세그먼트를 포함하는 최상위 저장 단위이다. 실제 디스크 파일
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 때마다 함께 갱신되어야 한다. 인덱스를 무작정 많이 만들면 안되는 이유가 바로 여기에 있다.