MySQL 미드레벨 트러블슈팅 면접
새 면접Q. 프로덕션 환경에서 특정 테이블의 SELECT 쿼리가 점점 느려지고 있습니다. 인덱스는 적절히 설정되어 있고 EXPLAIN 결과도 인덱스를 잘 타고 있는데, 시간이 지날수록 동일한 쿼리의 응답 시간이 증가하는 현상이 발생했습니다. 이런 점진적 성능 저하의 원인을 진단하는 방법과 가능한 원인들, 그리고 해결 방안을 설명해주세요.
인덱스의 물리적 상태와 통계 정보, 그리고 데이터 증가에 따른 구조적 변화를 고려해보세요.
인덱스 프래그멘테이션이 주요 원인일 수 있습니다. 데이터의 INSERT/UPDATE/DELETE가 반복되면서 인덱스의 페이지들이 물리적으로 분산되고, B+Tree의 깊이가 증가하거나 페이지 활용률이 낮아질 수 있습니다. 또한 테이블 통계 정보가 오래되어 옵티마이저가 부정확한 실행 계획을 선택할 수 있습니다. SHOW TABLE STATUS로 데이터 파일 크기와 프래그멘테이션을 확인하고, ANALYZE TABLE로 통계를 갱신하거나 OPTIMIZE TABLE로 테이블을 재구성해야 합니다. 정기적인 통계 업데이트와 테이블 최적화 스케줄링이 재발 방지에 중요합니다.
- • 인덱스 프래그멘테이션으로 인한 물리적 I/O 증가
- • B+Tree 구조 변화와 페이지 활용률 저하
- • 테이블 통계 정보의 부정확성으로 인한 잘못된 실행 계획
- • OPTIMIZE TABLE과 ANALYZE TABLE을 통한 해결
- • 정기적인 유지보수 스케줄 수립
대량의 데이터 변경이 빈번한 이커머스 주문 테이블에서 시간이 지날수록 조회 성능이 저하되는 문제를 해결할 때 사용됩니다.
OPTIMIZE TABLE 실행 시 테이블 잠금이 발생하는데, 무중단으로 프래그멘테이션을 해결할 수 있는 방법은 무엇인가요?
Q. 프로덕션 환경에서 특정 UPDATE 쿼리가 실행되면 다른 관련 없어 보이는 테이블의 SELECT 쿼리들까지 대기 상태에 빠지는 현상이 발생했습니다. 두 테이블 간 명시적인 외래 키 관계는 없고, 애플리케이션 로그상으로도 연관성이 보이지 않습니다. 이런 예상치 못한 락 전파 현상의 원인을 진단하고 해결하는 방법을 설명해주세요.
메타데이터 락과 트랜잭션 범위, 그리고 암묵적인 테이블 의존성을 살펴보세요.
메타데이터 락(Metadata Lock)이 원인일 가능성이 높습니다. 긴 트랜잭션 내에서 여러 테이블에 접근하면 해당 트랜잭션이 커밋될 때까지 모든 테이블에 메타데이터 락이 유지되며, 이는 DDL 작업뿐 아니라 특정 상황에서 다른 쿼리에도 영향을 줄 수 있습니다. performance_schema.metadata_locks 테이블로 현재 락 상태를 확인하고, SHOW PROCESSLIST와 information_schema.INNODB_TRX로 긴 트랜잭션을 식별해야 합니다. 트리거나 스토어드 프로시저 내부에서 암묵적으로 다른 테이블에 접근하는 경우도 확인이 필요합니다. 트랜잭션 범위를 최소화하고, 불필요한 트리거를 제거하며, 애플리케이션 레벨에서 트랜잭션 경계를 명확히 관리해야 합니다.
- • 메타데이터 락의 전파와 영향 범위
- • 긴 트랜잭션 내 여러 테이블 접근으로 인한 락 유지
- • 트리거와 스토어드 프로시저의 암묵적 테이블 접근
- • performance_schema.metadata_locks를 통한 진단
- • 트랜잭션 범위 최소화와 명확한 경계 설정
마이크로서비스 환경에서 여러 서비스가 공유 데이터베이스를 사용할 때 예상치 못한 락 경합 문제를 해결할 때 필요합니다.
메타데이터 락과 InnoDB 레코드 락의 차이점과, 각각이 시스템에 미치는 영향을 비교해서 설명해주세요.
Q. Master-Slave 리플리케이션 환경에서 Slave의 SQL Thread가 멈춰있고, SHOW SLAVE STATUS에서 Last_SQL_Error에 Duplicate entry 에러가 표시되었습니다. Master에서는 정상적으로 데이터가 들어가고 있지만, Slave는 특정 시점부터 복제가 중단된 상태입니다. 이 상황의 원인 분석과 데이터 정합성을 유지하면서 복제를 재개하는 방법, 그리고 재발 방지 전략을 설명해주세요.
Slave에서 직접 데이터 변경이 발생했거나, 비결정적 쿼리가 실행되었을 가능성을 고려해보세요.
Slave에서 직접 데이터가 수정되었거나, UUID나 NOW 같은 비결정적 함수가 Statement 기반 복제에서 사용된 경우 발생할 수 있습니다. 먼저 pt-table-checksum으로 Master와 Slave 간 데이터 차이를 확인하고, 충돌하는 레코드의 실제 값을 비교해야 합니다. 임시 조치로 SET GLOBAL sql_slave_skip_counter=1로 해당 이벤트를 건너뛸 수 있지만, 데이터 정합성이 깨질 수 있으므로 신중해야 합니다. 근본적으로는 slave_exec_mode를 IDEMPOTENT로 설정하거나, Row 기반 복제(binlog_format=ROW)로 전환해야 합니다. Slave의 read_only 설정을 활성화하여 직접 쓰기를 방지하고, 애플리케이션에서 비결정적 함수 대신 결정적 값을 사용하도록 수정해야 합니다.
- • Slave 직접 수정이나 비결정적 쿼리로 인한 데이터 불일치
- • pt-table-checksum으로 데이터 차이 검증
- • Row 기반 복제로 전환하여 비결정적 문제 해결
- • slave_exec_mode=IDEMPOTENT 설정
- • Slave read_only 설정으로 직접 쓰기 방지
글로벌 서비스에서 지역별 읽기 전용 Slave를 운영할 때 복제 중단 장애를 빠르게 복구해야 하는 상황에서 사용됩니다.
Statement 기반 복제와 Row 기반 복제의 장단점을 비교하고, Mixed 모드는 어떤 상황에서 사용하는지 설명해주세요.
Q. 프로덕션 MySQL 서버에서 갑자기 모든 쿼리가 느려지고, 서버 모니터링 결과 디스크 I/O 대기(iowait)가 90% 이상으로 치솟은 상황이 발생했습니다. CPU와 메모리 사용률은 정상 범위이고, 쿼리 패턴도 평소와 동일합니다. 이런 급격한 디스크 I/O 증가의 원인을 진단하는 체계적인 방법과 즉각적인 조치, 그리고 장기적인 해결 방안을 설명해주세요.
버퍼 풀의 상태와 체크포인트 동작, 그리고 디스크 서브시스템의 문제를 확인해보세요.
InnoDB 버퍼 풀이 부족하여 디스크 읽기가 급증했거나, 더티 페이지 플러시(Checkpoint)가 과도하게 발생한 경우일 수 있습니다. SHOW ENGINE INNODB STATUS로 버퍼 풀 히트율과 더티 페이지 비율을 확인하고, Innodb_buffer_pool_reads와 Innodb_buffer_pool_read_requests 비율로 캐시 효율을 점검해야 합니다. iostat이나 iotop으로 실제 디스크 부하를 확인하고, 특정 프로세스나 쿼리가 원인인지 식별합니다. 즉각적으로는 innodb_io_capacity를 조정하여 플러시 속도를 제어하고, 불필요한 풀 테이블 스캔 쿼리를 중단시킵니다. 장기적으로는 innodb_buffer_pool_size를 증설하거나, SSD로 스토리지를 업그레이드하고, 쿼리 최적화를 통해 디스크 접근을 최소화해야 합니다.
- • 버퍼 풀 부족으로 인한 디스크 읽기 증가
- • 더티 페이지 플러시와 체크포인트 부하
- • SHOW ENGINE INNODB STATUS로 버퍼 풀 상태 확인
- • innodb_io_capacity 조정으로 플러시 속도 제어
- • 버퍼 풀 크기 증설과 스토리지 업그레이드
대량의 배치 작업이나 갑작스런 트래픽 증가로 인해 데이터베이스 전체 응답 속도가 저하될 때 원인을 빠르게 파악하고 해결해야 합니다.
InnoDB의 더티 페이지 플러시 메커니즘과 adaptive flushing이 어떻게 동작하는지 설명해주세요.
Q. 이벤트 쿠폰 발급 시스템에서 동시에 여러 사용자가 마지막 남은 쿠폰을 요청할 때, 재고는 1개인데 2명 이상에게 쿠폰이 발급되는 오버이슈 문제가 발생했습니다. 애플리케이션에서는 SELECT로 재고 확인 후 UPDATE로 차감하는 로직이고, 트랜잭션 격리 수준은 REPEATABLE READ입니다. 이 동시성 문제의 근본 원인을 분석하고, 데이터베이스 레벨과 애플리케이션 레벨에서 해결할 수 있는 방법들을 비교 설명해주세요.
읽기와 쓰기 사이의 시간 차이와 락 획득 시점을 고려해보세요.
SELECT와 UPDATE 사이에 다른 트랜잭션이 끼어들 수 있는 Race Condition이 원인입니다. REPEATABLE READ에서 일반 SELECT는 락을 걸지 않아 여러 트랜잭션이 동시에 같은 재고를 읽을 수 있습니다. SELECT ... FOR UPDATE를 사용하여 읽는 시점에 베타적 락을 획득하거나, UPDATE 쿼리에 WHERE 절로 재고 조건을 포함시켜 원자적으로 처리해야 합니다(UPDATE coupons SET stock = stock - 1 WHERE id = ? AND stock > 0). 또는 Redis의 DECR 같은 원자적 연산을 사용하거나, 애플리케이션 레벨에서 분산 락을 구현할 수 있습니다. 데이터베이스 제약 조건(CHECK constraint)으로 재고가 음수가 되지 않도록 보장하고, 실패한 요청은 적절히 처리해야 합니다. 성능과 정확성의 트레이드오프를 고려하여 비관적 락 또는 낙관적 락을 선택합니다.
- • SELECT와 UPDATE 사이의 Race Condition
- • SELECT ... FOR UPDATE로 베타적 락 획득
- • WHERE 절 조건을 포함한 원자적 UPDATE
- • 분산 환경에서 Redis를 활용한 원자적 연산
- • 데이터베이스 제약 조건으로 무결성 보장
한정 수량 상품 구매, 선착순 이벤트, 좌석 예약 시스템에서 동시성 문제로 인한 오버셀링을 방지해야 할 때 사용됩니다.
SELECT ... FOR UPDATE와 UPDATE ... WHERE 방식의 성능 차이와, 각각 어떤 상황에서 더 적합한지 설명해주세요.
Q. 프로덕션 MySQL 서버의 메모리 사용량이 시간이 지날수록 계속 증가하여 결국 OOM Killer에 의해 프로세스가 강제 종료되는 문제가 반복적으로 발생하고 있습니다. innodb_buffer_pool_size는 전체 메모리의 70%로 적절히 설정되어 있고, 쿼리 패턴도 특별한 변화가 없습니다. 이런 메모리 누수 문제의 원인을 진단하는 방법과 해결 전략을 설명해주세요.
세션별 메모리 할당과 임시 테이블, 그리고 커넥션 관리를 확인해보세요.
세션별 메모리 버퍼(sort_buffer_size, join_buffer_size, read_buffer_size)가 과도하게 설정되었거나, 커넥션이 제대로 정리되지 않고 누적되는 경우일 수 있습니다. performance_schema의 memory_summary_by_thread_by_event_name으로 스레드별 메모리 사용량을 확인하고, SHOW PROCESSLIST로 오래된 커넥션과 Sleep 상태의 연결을 식별해야 합니다. 대용량 정렬이나 조인으로 인한 임시 테이블 생성(tmp_table_size, max_heap_table_size)도 메모리를 소진할 수 있습니다. 커넥션 풀의 max_connections와 wait_timeout 설정을 검토하고, 애플리케이션에서 커넥션을 제대로 반환하는지 확인합니다. 세션별 버퍼 크기를 적절히 조정하고, 불필요한 커넥션을 정리하며, 쿼리 최적화로 임시 테이블 사용을 줄여야 합니다.
- • 세션별 메모리 버퍼의 과도한 설정
- • 커넥션 누수와 Sleep 상태 연결 누적
- • 임시 테이블로 인한 메모리 소진
- • performance_schema로 스레드별 메모리 추적
- • 커넥션 풀 설정과 타임아웃 관리
대규모 트래픽을 처리하는 서비스에서 메모리 부족으로 인한 서비스 중단을 방지하고 안정적인 운영을 유지해야 할 때 필요합니다.
Global 메모리 영역과 Session 메모리 영역의 차이를 설명하고, 전체 MySQL 메모리 사용량을 예측하는 공식을 설명해주세요.
Q. 프로덕션 MySQL 서버에서 갑자기 모든 쓰기 작업이 멈추고, 에러 로그에 'binary log disk full' 메시지가 표시되었습니다. 디스크 공간을 확인해보니 바이너리 로그 파일들이 디스크를 가득 채운 상태입니다. 서비스 중단을 최소화하면서 이 문제를 해결하는 긴급 조치와, 바이너리 로그 관리 정책을 수립하는 방법을 설명해주세요.
바이너리 로그의 역할과 안전한 삭제 조건, 그리고 자동 정리 설정을 고려해보세요.
바이너리 로그는 리플리케이션과 포인트 인 타임 복구에 필수적이므로 무작정 삭제하면 안 됩니다. 먼저 SHOW MASTER STATUS와 SHOW SLAVE STATUS로 현재 사용 중인 바이너리 로그를 확인하고, 모든 Slave가 읽어간 로그만 안전하게 삭제해야 합니다. PURGE BINARY LOGS TO 또는 PURGE BINARY LOGS BEFORE 명령으로 특정 시점 이전의 로그를 삭제합니다. 긴급 상황에서는 임시로 바이너리 로그를 다른 디스크로 이동시킬 수도 있습니다. 재발 방지를 위해 expire_logs_days 또는 binlog_expire_logs_seconds를 설정하여 자동 정리를 활성화하고, 바이너리 로그 크기(max_binlog_size)를 조정하며, 디스크 모니터링 알림을 설정해야 합니다. 정기적인 백업 후에는 불필요한 바이너리 로그를 정리하는 정책을 수립합니다.
- • 리플리케이션 상태 확인 후 안전한 로그 삭제
- • PURGE BINARY LOGS 명령으로 선택적 삭제
- • expire_logs_days 설정으로 자동 정리
- • 바이너리 로그의 리플리케이션과 복구 역할 이해
- • 디스크 모니터링과 정기적인 로그 관리 정책
예상치 못한 트래픽 증가나 대량의 데이터 변경으로 바이너리 로그가 급증하여 디스크가 가득 찰 때 빠르게 대응해야 합니다.
바이너리 로그를 비활성화하면 어떤 기능들을 사용할 수 없게 되는지, 그리고 성능에는 어떤 영향을 미치는지 설명해주세요.
아직 댓글이 없습니다. 첫 번째 댓글을 남겨보세요!