Database ‐ Database Performance and MySQL Architecture

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)