전체 글

[MySQL] 긴 트랜잭션이 Undo와 Purge를 지연시키는 과정 측정하기

데이터베이스를 다루다 보면 어느 날 갑자기 데이터 디렉터리의 undo 테이블스페이스 파일이 눈에 띄게 커져 있는 것을 발견할 때가 있습니다. 테이블 데이터는 그대로인데 디스크 사용량만 계속 늘어난다면, 대부분의 경우 범인은 어딘가에서 며칠째 커밋되지 않고 열려 있는 트랜잭션입니다. 이때 흔히 놓치는 사실은, 문제를 일으키는 트랜잭션이 반드시 대량의 쓰기를 하는 트랜잭션일 필요가 없다는 점입니다. 단순히 SELECT 한 번을 실행한 뒤 커밋도 롤백도 하지 않은 세션 하나가, 전혀 다른 테이블에서 다른 트랜잭션들이 계속 만들어내는 undo 레코드의 회수(Purge)를 통째로 막아버릴 수 있습니다. 이 글에서는 InnoDB의 Undo Log와 Purge가 어떻게 맞물려 동작하는지 기본 원리부터 짚고, Hist..

[MySQL] performance_schema로 현재 잠금 대기와 차단 세션 찾기

들어가며: 잠금 대기, 범인을 못 찾으면 못 풉니다데이터베이스를 다루다 보면 갑자기 특정 쿼리가 멈춘 것처럼 응답하지 않는 상황을 자주 마주하게 됩니다. 애플리케이션 로그에는 타임아웃만 찍혀 있고, SHOW PROCESSLIST를 열어봐도 State: waiting for handler commit이나 Waiting for table metadata lock 같은 문구만 보일 뿐, 정작 어떤 세션이 어떤 세션을 막고 있는지는 알려주지 않습니다. 이때 실무에서 흔히 하는 실수가 감으로 의심 가는 세션을 KILL부터 해버리는 것입니다. 하지만 진짜 차단 세션(blocker)이 아니라 대기 세션(waiter)을 죽여버리면 문제는 그대로 남고, 반대로 이미 곧 커밋될 트랜잭션을 성급하게 종료시키면 애플리케이션에..

[MySQL] 데드락 로그 읽는 법: 피해 트랜잭션과 잠금 순서 복원하기

데이터베이스를 다루다 보면 어느 날 애플리케이션 로그에 Deadlock found when trying to get lock이라는 에러가 찍히는 순간을 만나게 됩니다. 개발자 입장에서는 당황스럽지만, DBA 입장에서는 오히려 반가운 신호이기도 합니다. MySQL의 InnoDB 스토리지 엔진은 데드락을 스스로 감지해서 둘 중 한 트랜잭션을 강제로 롤백시키기 때문에, 서비스가 완전히 멈추는 대신 한쪽만 실패로 끝나기 때문입니다. 문제는 그다음입니다. "어떤 SQL과 어떤 레코드가 충돌했는가", "왜 하필 이 트랜잭션이 롤백됐는가"를 설명하지 못하면 똑같은 데드락이 반복해서 터지게 됩니다. 이때 가장 먼저 확인해야 하는 것이 바로 SHOW ENGINE INNODB STATUS가 남기는 LATEST DETECT..

[MySQL] 옵티마이저 힌트 사용 기준: JOIN_ORDER와 INDEX 힌트의 선택·제거 원칙

데이터베이스를 다루다 보면 같은 SQL인데도 데이터 분포나 통계 정보가 달라진 뒤 갑자기 실행 시간이 늘어나는 상황을 만나게 됩니다. 실행 계획을 확인해 보니 큰 테이블부터 조인하거나 선택도가 낮은 인덱스를 고르는 경우도 있습니다. 이때 가장 흔하게 검토하는 대응이 바로 옵티마이저 힌트(Optimizer Hint)를 이용한 실행 계획 강제입니다.하지만 힌트는 느린 SQL에 무조건 붙이는 성능 옵션이 아닙니다. 잘못 선택하면 현재 데이터에서는 빨라 보여도 데이터가 증가한 뒤 더 나쁜 계획을 고정할 수 있으며, 인덱스 이름 변경이나 쿼리 블록 변형으로 의도대로 적용되지 않을 수도 있습니다. 따라서 가장 중요한 것은 어떤 계획 요소가 잘못되었는지 구분하고, 적용 전후의 계획과 실제 실행 시간을 검증하며, 제거..

[MySQL] 중복 인덱스 찾기: 좌측 접두사 관계와 삭제 전 검증 절차

데이터베이스를 다루다 보면 하나의 테이블에 비슷한 컬럼으로 구성된 인덱스가 계속 추가되는 경우가 많습니다. 장애 대응이나 특정 쿼리 튜닝 과정에서 인덱스를 하나씩 만들다 보면 INDEX(a), INDEX(a, b), INDEX(a, b, c)가 함께 남기도 합니다. 이때 가장 흔히 하는 실수는 컬럼 앞부분이 같다는 이유만으로 짧은 인덱스를 즉시 삭제하는 것입니다.중복 인덱스는 조회 성능을 반드시 향상시키는 것이 아니라 저장 공간을 차지하고, INSERT·UPDATE·DELETE마다 함께 갱신되어 쓰기 비용을 높입니다. 하지만 짧은 인덱스가 특정 조회의 커버링 인덱스(Covering Index)로 사용되거나 더 작은 구조 덕분에 선택될 수 있으므로 단순 비교만으로 삭제해서는 안 됩니다. 이번 글에서는 중복..

[MySQL] 혼합 정렬 ORDER BY 최적화: 내림차순 인덱스로 filesort 피하기

데이터베이스를 다루다 보면 최신 데이터부터 보여 주되, 같은 시각의 데이터는 작은 식별자 순서로 정렬하거나 우선순위는 높게, 마감일은 빠르게 나열해야 할 때가 많습니다. 이때 가장 흔하게 사용되는 것이 바로 ORDER BY created_at DESC, id ASC와 같은 혼합 정렬입니다.정렬할 행이 적으면 큰 문제가 드러나지 않지만, 조회 범위가 커지면 MySQL이 인덱스 순서를 그대로 이용하지 못하고 별도의 정렬 단계인 filesort를 수행할 수 있습니다. 이름과 달리 filesort가 항상 디스크 파일을 사용한다는 의미는 아니지만, 후보 행을 모으고 정렬해야 한다는 점은 같습니다. 따라서 대용량 목록 조회에서는 실행 시간과 메모리 사용량, 임시 파일 발생 가능성까지 함께 커질 수 있어 주의가 필요..

[MySQL] 접두사 인덱스 길이 정하기: 카디널리티와 충돌률로 계산하는 실전 기준

데이터베이스를 다루다 보면 URL, 이메일 주소, 상품 코드, 해시 문자열처럼 길이가 긴 VARCHAR 컬럼을 빠르게 검색해야 할 때가 많습니다. 이때 전체 문자열에 인덱스(Index)를 생성하면 검색에는 유리할 수 있지만 인덱스 용량이 커지고, 버퍼 풀(Buffer Pool) 효율과 쓰기 성능에도 부담을 줄 수 있습니다. 반대로 접두사를 지나치게 짧게 지정하면 같은 접두사를 가진 행이 많이 발생하여 스토리지 엔진이 불필요한 후보를 대량으로 읽게 됩니다.이때 사용할 수 있는 것이 바로 접두사 인덱스(Prefix Index)입니다. 하지만 모든 컬럼에 10자나 20자를 일률적으로 적용하는 방식은 안전하지 않습니다. 적절한 길이는 문자열의 최대 길이가 아니라 실제 데이터의 카디널리티(Cardinality),..

[MySQL] 트랜잭션 격리 수준별 동시성 실험: 조회 결과와 잠금 차이

데이터베이스를 다루다 보면 한 트랜잭션이 데이터를 수정하는 동안 다른 트랜잭션에서는 무엇이 보여야 하는지 판단해야 할 때가 많습니다. 이때 가장 흔하게 사용되는 기준이 바로 트랜잭션 격리 수준(Transaction Isolation Level)입니다. 격리 수준을 높이면 항상 안전하고 낮추면 항상 빠르다고 생각하기 쉽지만, 실제 동작은 일반 SELECT인지 잠금 읽기인지, 트랜잭션 안에서 첫 읽기가 언제 실행됐는지에 따라 달라집니다.이 글에서는 MySQL 8.4의 InnoDB를 기준으로 READ UNCOMMITTED부터 SERIALIZABLE까지 두 세션으로 재현하는 방법을 살펴보겠습니다. Dirty Read, Non-repeatable Read, Phantom Read가 어느 단계에서 보이는지뿐 아니라..

[MySQL] 파생 테이블 조건 푸시다운 확인하기: 외부 WHERE가 내려가지 않는 경우

데이터베이스를 다루다 보면 파생 테이블(Derived Table)에서 많은 행을 만든 뒤, 외부 쿼리의 WHERE 절로 극히 일부만 남기는 SQL을 만나게 됩니다. 이때 가장 흔하게 기대하는 최적화가 바로 파생 조건 푸시다운(Derived Condition Pushdown)입니다.파생 조건 푸시다운은 외부 조건을 파생 테이블 내부로 옮겨 불필요한 중간 결과를 줄이는 최적화입니다. 하지만 모든 조건이 내려가는 것은 아닙니다. LIMIT처럼 결과의 의미가 달라질 수 있는 연산이 있거나, 조건 자체에 서브쿼리가 포함되거나, 윈도 함수 결과를 필터링하는 경우에는 적용되지 않거나 일부 조건만 내려갑니다. 이번 글에서는 외부 WHERE 조건이 내려가지 않는 경우를 중심으로 기본 동작, 실행 계획 확인법, 버전별 검..

[MySQL] CTE 인라인과 구체화 판단법: WITH 쿼리의 반복 스캔 줄이기

데이터베이스를 다루다 보면 복잡한 서브쿼리를 읽기 쉽게 분리하거나 같은 중간 결과를 여러 곳에서 재사용하기 위해 공통 테이블 표현식(Common Table Expression, CTE)을 사용하게 됩니다. 이때 가장 흔히 하는 실수는 WITH 절을 작성하면 결과가 항상 한 번만 계산되어 저장된다고 생각하는 것입니다.MySQL 옵티마이저는 CTE를 외부 쿼리 블록에 합치는 병합(Merge), 흔히 말하는 인라인 방식으로 처리할 수도 있고, 내부 임시 테이블에 저장하는 구체화(Materialization)를 선택할 수도 있습니다. 어느 쪽이 빠른지는 문법만으로 결정되지 않습니다. CTE 내부 연산의 비용, 외부 조건의 밀어 넣기 가능 여부, 참조 횟수, 구체화된 행 수를 함께 봐야 합니다. 이번 글에서는 M..