MySQL 리드·아키텍트 기술면접
새 면접Q. InnoDB의 Adaptive Hash Index가 어떤 원리로 동작하며, 어떤 상황에서 성능 향상을 가져오고 반대로 어떤 경우에는 비활성화하는 것이 더 나은지 설명해주세요. 대규모 서비스에서 이를 어떻게 모니터링하고 판단하시겠습니까?
버퍼풀 내에서 자주 접근되는 인덱스 페이지에 대한 메모리 구조와, 워크로드 패턴에 따른 경합 문제를 생각해보세요.
Adaptive Hash Index는 InnoDB가 자주 접근되는 인덱스 페이지에 대해 B-Tree 탐색을 건너뛰고 직접 접근할 수 있도록 메모리에 해시 인덱스를 자동으로 구축하는 기능입니다. 동일한 검색 패턴이 반복되는 OLTP 환경에서 인덱스 조회 성능을 크게 향상시킬 수 있습니다. 하지만 고동시성 환경에서 AHI 내부의 래치 경합이 발생하거나, 테이블 스캔이 많거나 검색 패턴이 랜덤한 경우 오히려 오버헤드가 됩니다. show engine innodb status에서 semaphores 섹션의 대기와 AHI 히트율을 모니터링하고, 동시 사용자가 많을 때 mutex contention이 관찰되면 innodb_adaptive_hash_index를 OFF하는 것을 고려해야 합니다. 특히 MySQL 5.7 이전 버전에서는 단일 래치로 인한 병목이 심각했으나, 이후 파티셔닝되어 개선되었으므로 버전별 특성도 함께 고려해야 합니다.
- • B-Tree 탐색을 건너뛰는 메모리 해시 인덱스 자동 구축
- • 반복적인 검색 패턴에서 성능 향상, 랜덤 패턴에서는 오버헤드
- • 고동시성 환경에서 래치 경합 발생 가능성
- • show engine innodb status로 히트율과 세마포어 대기 모니터링
대규모 트래픽 서비스에서 동시 접속자 증가 시 CPU 사용률은 높은데 쿼리 처리량이 오르지 않는 현상의 원인으로 AHI 경합을 진단하고 해결합니다.
MySQL 8.0 환경에서 AHI 파티션 수를 조정할 수 있는 파라미터와, 실제 프로덕션에서 AHI 관련 성능 이슈를 겪으신 경험이 있으신가요?
Q. MySQL의 GTID 기반 복제와 기존 바이너리 로그 포지션 기반 복제의 내부 동작 차이를 설명하고, GTID를 도입할 때 레거시 시스템에서 발생할 수 있는 문제점과 마이그레이션 전략을 제시해주세요. 특히 Multi-Source Replication 환경에서의 고려사항도 포함해주세요.
트랜잭션 식별 방식의 차이와, 복제 토폴로지 변경 시 자동화 가능성, 그리고 GTID 홀과 일관성 문제를 중심으로 생각해보세요.
기존 바이너리 로그 포지션 방식은 파일명과 오프셋으로 복제 위치를 추적하여 페일오버 시 수동으로 정확한 포지션을 찾아야 하지만, GTID는 각 트랜잭션에 글로벌 고유 ID를 부여해 자동으로 복제 위치를 파악합니다. GTID 도입 시 레거시 시스템에서는 비GTID 트랜잭션 혼재, 특정 SQL 문(CREATE TABLE SELECT, 임시 테이블 등)의 제약, 그리고 GTID 홀 발생 시 복구 복잡도 증가 문제가 있습니다. 마이그레이션은 gtid_mode=OFF_PERMISSIVE부터 시작해 단계적으로 ON_PERMISSIVE, ON으로 전환하며, enforce_gtid_consistency를 먼저 활성화해 호환성을 검증해야 합니다. Multi-Source 환경에서는 각 소스의 GTID 집합이 겹치지 않도록 server_uuid와 GTID 범위를 관리하고, 채널별로 독립적인 GTID executed 세트를 유지해야 합니다.
- • GTID는 트랜잭션 단위 글로벌 식별자로 자동 페일오버 지원
- • 레거시 환경에서 특정 SQL 제약과 GTID 홀 문제 발생
- • gtid_mode 단계적 전환과 enforce_gtid_consistency 선행 검증
- • Multi-Source에서 채널별 GTID 집합 독립 관리 필요
마스터 장애 시 수동 개입 없이 자동으로 새로운 마스터로 슬레이브를 연결해야 하는 HA 구성에서 GTID를 필수로 사용합니다.
GTID 홀이 발생했을 때 실제로 어떻게 대응하셨는지, 그리고 Orchestrator나 MHA 같은 자동 페일오버 도구와 GTID를 함께 운영한 경험이 있으신가요?
Q. MySQL 8.0의 히스토그램과 기존 인덱스 통계의 차이를 설명하고, 히스토그램이 옵티마이저의 실행 계획 수립에 어떤 영향을 미치는지 구체적으로 설명해주세요. 또한 히스토그램 생성과 관리 전략을 어떻게 수립하시겠습니까?
카디널리티 추정의 정확도와 데이터 분포의 편향성, 그리고 통계 수집 비용을 중심으로 생각해보세요.
기존 인덱스 통계는 인덱스의 카디널리티(고유값 개수)만 제공하여 데이터 분포를 정확히 반영하지 못하지만, 히스토그램은 컬럼 값의 분포를 버킷 단위로 저장해 옵티마이저가 더 정확한 행 수를 추정할 수 있게 합니다. 특히 WHERE 절의 범위 조건이나 조인 조건에서 데이터가 편향되어 있을 때 히스토그램을 통해 인덱스 선택, 조인 순서, 조인 알고리즘 결정이 개선됩니다. MySQL은 Singleton과 Equi-Height 두 가지 히스토그램 타입을 지원하며, ANALYZE TABLE UPDATE HISTOGRAM 명령으로 생성합니다. 관리 전략으로는 데이터 분포가 편향된 핵심 필터링 컬럼에 우선 적용하고, 데이터 변경이 많은 테이블은 주기적으로 재생성하되, 히스토그램 생성 자체가 테이블 스캔을 유발하므로 저부하 시간대에 배치로 수행해야 합니다. information_schema.column_statistics를 모니터링하여 효과를 측정합니다.
- • 히스토그램은 컬럼 값 분포를 버킷으로 저장해 카디널리티 추정 정확도 향상
- • 편향된 데이터 분포에서 조인 순서와 인덱스 선택 개선
- • Singleton과 Equi-Height 타입 지원
- • 핵심 필터링 컬럼 우선 적용, 저부하 시간 배치 생성
특정 상태값이나 날짜 범위에 데이터가 몰려있는 테이블에서 조인 시 잘못된 인덱스를 선택하는 문제를 히스토그램으로 해결합니다.
실제로 히스토그램 적용 전후로 실행 계획이 크게 개선된 사례가 있으신가요? 그리고 히스토그램이 오히려 잘못된 계획을 만든 경우는 어떻게 대응하셨나요?
Q. InnoDB의 Gap Lock과 Next-Key Lock의 동작 원리를 설명하고, 이것이 Phantom Read를 방지하는 메커니즘을 상세히 설명해주세요. 또한 이러한 락 메커니즘이 실무에서 데드락을 유발하는 전형적인 패턴과 해결 방법을 제시해주세요.
Repeatable Read 격리 수준에서 범위 조건의 잠금 방식과, 인덱스 스캔 과정에서 락이 걸리는 범위를 생각해보세요.
Gap Lock은 인덱스 레코드 사이의 간격을 잠그고, Next-Key Lock은 인덱스 레코드와 그 앞의 간격을 함께 잠그는 방식으로, Repeatable Read 격리 수준에서 범위 조건 조회 시 해당 범위에 새로운 레코드가 삽입되는 것을 방지해 Phantom Read를 막습니다. 예를 들어 WHERE id > 10 조건 조회 시 10 이후의 모든 간격이 잠겨 다른 트랜잭션이 id=15를 삽입할 수 없게 됩니다. 실무에서 전형적인 데드락 패턴은 두 트랜잭션이 서로 다른 순서로 범위를 스캔하거나, 유니크 인덱스 충돌 시 Gap Lock이 걸린 상태에서 서로를 기다리는 경우입니다. 해결 방법으로는 트랜잭션 범위 최소화, 인덱스 설계 최적화로 스캔 범위 축소, 동일한 순서로 레코드 접근, Read Committed 격리 수준 사용(Phantom Read 허용 가능한 경우), 그리고 innodb_lock_wait_timeout 적절히 설정하여 빠른 롤백을 유도합니다.
- • Gap Lock은 간격 잠금, Next-Key Lock은 레코드+간격 잠금
- • Repeatable Read에서 범위 조건의 Phantom Read 방지
- • 서로 다른 순서의 범위 스캔이 데드락 주요 원인
- • 트랜잭션 최소화, 인덱스 최적화, 접근 순서 통일로 해결
재고 차감이나 포인트 적립 같은 동시성 높은 트랜잭션에서 범위 조건 사용 시 데드락이 빈번하게 발생하는 문제를 진단하고 해결합니다.
Read Committed 격리 수준으로 변경했을 때 애플리케이션 로직에서 어떤 부분을 검증하고 수정해야 하는지, 실제 경험을 공유해주실 수 있나요?
Q. MySQL의 파티션 프루닝이 어떻게 동작하며, 파티션 키 선택이 쿼리 성능에 미치는 영향을 설명해주세요. 대용량 로그 데이터를 다루는 시스템에서 파티셔닝 전략을 수립할 때 고려해야 할 사항과, 파티션 관리 자동화 방안을 제시해주세요.
WHERE 절과 파티션 키의 관계, 파티션별 통계 정보, 그리고 파티션 추가/삭제의 운영 비용을 중심으로 생각해보세요.
파티션 프루닝은 쿼리의 WHERE 절을 분석하여 필요한 파티션만 스캔하도록 최적화하는 기능으로, EXPLAIN PARTITIONS로 확인할 수 있습니다. 파티션 키가 WHERE 절에 포함되지 않으면 모든 파티션을 스캔하므로 성능 이점이 없으며, 조인 시에도 양쪽 테이블의 파티션 키가 조인 조건에 포함되어야 효과적입니다. 로그 데이터의 경우 시계열 기반 RANGE 파티셔닝이 적합하며, 일별 또는 월별로 파티션을 나누고 오래된 데이터는 DROP PARTITION으로 빠르게 삭제합니다. 고려사항으로는 파티션 개수 제한(MySQL 8.0 이전 8192개), 글로벌 인덱스 미지원으로 인한 유니크 키 제약, 파티션별 통계 정보 관리가 있습니다. 자동화 방안은 이벤트 스케줄러로 미래 파티션 사전 생성과 오래된 파티션 삭제를 스크립트화하고, 파티션 개수와 데이터 분포를 모니터링하여 알람을 설정하는 것입니다.
- • 파티션 프루닝은 WHERE 절 분석으로 필요한 파티션만 스캔
- • 파티션 키가 WHERE 절과 조인 조건에 포함되어야 효과적
- • 시계열 로그는 RANGE 파티셔닝으로 DROP PARTITION 활용
- • 이벤트 스케줄러로 파티션 생성/삭제 자동화
수억 건의 접속 로그나 결제 이력 테이블에서 최근 데이터만 조회하는 쿼리 성능을 높이고, 오래된 데이터를 빠르게 삭제하기 위해 파티셔닝을 적용합니다.
파티셔닝된 테이블에서 인덱스 전략은 어떻게 다르게 가져가야 하는지, 그리고 파티션 재구성이 필요한 상황에서 무중단 마이그레이션 경험이 있으신가요?
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!