MySQL 시니어 기술면접
새 면접Q. InnoDB의 Double Write Buffer의 목적과 동작 원리를 설명하고, Partial Page Write 문제가 무엇인지 설명해주세요. 또한 SSD 환경에서 Double Write Buffer를 비활성화하는 것이 안전한지, innodb_doublewrite 파라미터 설정 전략과 성능 트레이드오프를 설명해주세요.
Double Write Buffer는 InnoDB가 Partial Page Write(16KB 페이지를 디스크에 쓰는 중 시스템 장애 발생 시 일부만 기록되는 문제)를 방지하기 위한 메커니즘입니다. 데이터를 실제 데이터 파일에 쓰기 전에 연속된 공간인 Double Write Buffer에 먼저 순차적으로 기록한 후, 이를 실제 위치에 쓰는 2단계 과정을 거칩니다. SSD 환경이라도 Atomic Write를 지원하지 않는 한 비활성화는 위험하며, Fusion-io나 특정 NVMe SSD처럼 Atomic Write를 보장하는 경우에만 비활성화를 고려할 수 있습니다. 비활성화 시 쓰기 성능은 약 5-10% 향상되지만, 복구 불가능한 데이터 손상 위험이 있어 대부분의 프로덕션 환경에서는 활성화 상태를 유지하는 것이 권장됩니다.
- • Partial Page Write는 16KB 페이지 쓰기 중 일부만 완료되어 데이터 손상이 발생하는 문제
- • Double Write Buffer는 시스템 테이블스페이스에 연속된 공간으로 먼저 순차 쓰기 후 실제 위치에 쓰는 2단계 과정
- • Atomic Write를 지원하는 특정 스토리지 장치에서만 비활성화 고려 가능
- • 성능 향상보다 데이터 무결성이 중요한 프로덕션 환경에서는 활성화 유지 권장
innodb_flush_method 파라미터의 O_DIRECT와 fsync 옵션이 Double Write Buffer와 어떤 관계가 있으며, 각각 어떤 상황에서 사용해야 하나요?
Q. MySQL에서 서브쿼리(Subquery)와 조인(JOIN)의 성능 차이를 설명하고, Derived Table과 Materialization의 동작 원리를 설명해주세요. 특히 MySQL 5.6 이후 서브쿼리 최적화가 어떻게 개선되었는지, 그리고 여전히 서브쿼리를 조인으로 변경해야 하는 경우를 실무 관점에서 설명해주세요.
MySQL 5.5 이전에는 서브쿼리가 Dependent Subquery로 실행되어 외부 쿼리의 각 행마다 서브쿼리가 반복 실행되는 성능 문제가 있었습니다. MySQL 5.6부터는 Subquery Materialization(서브쿼리 결과를 임시 테이블로 한 번만 실행)과 Semi-Join Optimization이 도입되어 많은 경우 자동으로 조인처럼 최적화됩니다. 하지만 SELECT 절의 스칼라 서브쿼리, 상관 서브쿼리(Correlated Subquery), NOT IN 서브쿼리 등은 여전히 비효율적으로 실행될 수 있어 EXPLAIN으로 확인 후 조인으로 변경하는 것이 좋습니다. Derived Table은 FROM 절의 서브쿼리로, MySQL 5.7부터 Merge 최적화가 가능하지만 집계 함수나 LIMIT가 있으면 Materialization되어 임시 테이블이 생성됩니다.
- • MySQL 5.6 이후 Subquery Materialization과 Semi-Join으로 IN 서브쿼리가 크게 개선됨
- • SELECT 절의 스칼라 서브쿼리와 상관 서브쿼리는 여전히 행마다 반복 실행되어 비효율적
- • Derived Table은 집계/LIMIT 없으면 Merge되지만, 있으면 Materialization되어 임시 테이블 생성
- • EXPLAIN에서 DEPENDENT SUBQUERY 표시 시 조인으로 리팩토링 검토 필요
EXISTS와 IN 서브쿼리의 성능 차이는 무엇이며, 각각 어떤 상황에서 사용하는 것이 유리한가요?
Q. MySQL의 Orchestrator, ProxySQL, HAProxy를 조합한 고가용성 아키텍처를 설계할 때 각 컴포넌트의 역할과 장애 시나리오별 페일오버 전략을 설명해주세요. 특히 Split-Brain 문제를 방지하는 방법과 자동 페일오버 시 데이터 손실을 최소화하는 설정을 제시해주세요.
Orchestrator는 복제 토폴로지 관리와 자동 페일오버를 담당하며, Raft 합의 알고리즘을 사용한 다중 노드 구성으로 Split-Brain을 방지합니다. ProxySQL은 쿼리 라우팅과 읽기/쓰기 분리를 담당하며, Orchestrator의 API를 통해 마스터 변경 시 자동으로 라우팅 테이블을 업데이트합니다. HAProxy는 ProxySQL 앞단에서 L4 로드밸런싱과 헬스체크를 수행합니다. 자동 페일오버 시 데이터 손실을 최소화하려면 Semi-Synchronous Replication을 활성화하고 rpl_semi_sync_master_wait_for_slave_count를 최소 1로 설정하며, Orchestrator의 RecoverMasterClusterFilters와 DetachLostReplicasAfterMasterFailover 설정으로 안전한 페일오버 정책을 구성합니다. 또한 GTID 기반 복제를 사용하여 페일오버 후 복제 재구성을 자동화합니다.
- • Orchestrator는 Raft 합의 알고리즘으로 다중 노드 구성하여 Split-Brain 방지
- • ProxySQL은 Orchestrator API 연동으로 마스터 변경 시 자동 라우팅 업데이트
- • Semi-Sync Replication과 GTID로 페일오버 시 데이터 손실 및 복제 재구성 문제 최소화
- • HAProxy로 ProxySQL 자체의 고가용성 확보 및 헬스체크 수행
MHA(Master High Availability)와 Orchestrator를 비교했을 때 각각의 장단점과 선택 기준은 무엇인가요?
Q. 100GB 이상의 대용량 테이블에서 무중단으로 컬럼을 추가하거나 인덱스를 변경해야 할 때, Online DDL의 동작 원리와 ALGORITHM=INPLACE, LOCK=NONE의 의미를 설명하고, pt-online-schema-change와 gh-ost의 차이점 및 각각의 장단점을 설명해주세요.
MySQL 5.6 이후 Online DDL은 ALGORITHM=INPLACE로 테이블 복사 없이 메타데이터만 변경하거나, Row Log를 사용해 DDL 중 발생한 DML을 추적 후 적용합니다. LOCK=NONE은 DDL 중에도 읽기/쓰기가 가능하지만, 모든 DDL이 지원되는 것은 아니며 PRIMARY KEY 변경 등은 여전히 테이블 재생성이 필요합니다. pt-online-schema-change는 트리거 기반으로 원본 테이블의 변경사항을 새 테이블에 복제하며, 청크 단위로 데이터를 복사해 부하를 분산시키지만 트리거 오버헤드가 있습니다. gh-ost는 트리거 없이 바이너리 로그를 파싱하여 변경사항을 추적하므로 원본 테이블에 부하가 적고, 언제든 중단 및 재시작이 가능하며, 스로틀링 제어가 우수하여 대용량 테이블 변경 시 선호됩니다.
- • Online DDL ALGORITHM=INPLACE는 Row Log로 DDL 중 DML 추적하지만 모든 DDL이 지원되는 것은 아님
- • pt-online-schema-change는 트리거 기반으로 간단하지만 트리거 오버헤드 존재
- • gh-ost는 바이너리 로그 파싱으로 트리거 없이 동작하여 원본 부하 최소화
- • gh-ost는 동적 스로틀링, 일시정지/재개 기능으로 프로덕션 환경에서 안전성 높음
Online DDL 실행 중 Replication Lag이 발생하는 이유와 이를 모니터링하고 제어하는 방법은 무엇인가요?
Q. 일별 1억 건의 이벤트 로그를 저장하는 시스템에서 실시간 집계 쿼리와 배치 분석을 모두 지원해야 합니다. MySQL만으로 이를 구현할 때의 한계점을 설명하고, Summary Table 패턴, Time-Series DB 도입, OLAP 엔진(ClickHouse 등) 연동 등 다양한 아키텍처 옵션을 비교하여 각 옵션의 트레이드오프와 선택 기준을 제시해주세요.
MySQL은 OLTP에 최적화되어 있어 대용량 로그 데이터의 실시간 집계는 인덱스 부하와 쿼리 성능 저하를 야기합니다. Summary Table 패턴은 시간대별/카테고리별 집계를 미리 계산해 별도 테이블에 저장하는 방식으로, 실시간 대시보드에는 효과적이지만 집계 차원이 증가하면 테이블 수가 폭발하고 유연성이 떨어집니다. Time-Series DB(InfluxDB, TimescaleDB)는 시계열 데이터에 최적화되어 압축률과 쿼리 성능이 우수하지만 트랜잭션 지원이 약하고 별도 인프라 관리가 필요합니다. ClickHouse 같은 OLAP 엔진은 컬럼 기반 스토리지로 대용량 분석 쿼리에 탁월하며, MySQL에서 CDC(Debezium 등)로 실시간 동기화하는 Lambda Architecture를 구성하면 OLTP와 OLAP를 분리할 수 있습니다. 선택 기준은 쿼리 복잡도, 실시간성 요구사항, 운영 복잡도, 비용을 종합적으로 고려해야 합니다.
- • MySQL은 OLTP 최적화로 대용량 로그 집계 시 인덱스 부하와 성능 저하 발생
- • Summary Table은 간단하지만 집계 차원 증가 시 관리 복잡도 증가
- • OLAP 엔진은 컬럼 기반 스토리지로 분석 쿼리에 탁월하나 별도 인프라 필요
- • Lambda Architecture로 MySQL(OLTP)과 ClickHouse(OLAP)를 CDC로 연동하여 역할 분리
CDC 파이프라인에서 데이터 일관성을 보장하고 지연을 모니터링하는 방법과, 장애 시 데이터 유실을 방지하는 전략은 무엇인가요?
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!