Zettelkasten

OPTIMIZE TABLE은 테이블을 통째로 리빌드해 DELETE로 남은 미반환 공간을 OS로 회수한다

·수정 1회
mysqlinnodbrdsdisk

요약

  • InnoDB에서 OPTIMIZE TABLE은 테이블을 새 파일에 처음부터 다시 써넣는 리빌드 작업이다.
  • DELETE 후 페이지에 남은 빈 공간(data_free)을 제거해 디스크를 OS로 반환하고, 단편화 해소·인덱스 재구성·통계 갱신을 함께 한다.
  • 리빌드 특성상 대상 테이블 크기만큼 임시 디스크가 추가로 필요해서, 디스크가 꽉 찬 상태에서 큰 테이블에 돌리면 실패한다.

본문

DELETE가 디스크를 돌려주지 않는 이유

InnoDB는 행을 DELETE해도 그 행을 "삭제됨"으로 표시만 하고 페이지 안에 구멍(fragment)으로 남겨둔다. 디스크는 OS로 반환하지 않고 들고 있다가 이후 INSERT에 재사용한다. 이렇게 비워졌지만 반환되지 않은 공간이 information_schema.TABLES.data_free로 집계된다. 대량 DELETE/UPDATE를 한 테이블은 이 값이 수십 GB까지 커질 수 있다.

SELECT table_name,
       ROUND(data_free/1024/1024/1024,2) AS free_gb,
       ROUND((data_length+index_length)/1024/1024/1024,2) AS total_gb
FROM information_schema.TABLES
WHERE table_schema = '<DB명>' AND data_free > 0
ORDER BY data_free DESC;

OPTIMIZE TABLE이 실제로 하는 일

OPTIMIZE TABLE은 InnoDB에서 내부적으로 ALTER TABLE ... FORCE(온라인 리빌드)로 매핑되며, 기존 테이블의 살아있는 행만 새 테이블 파일에 처음부터 다시 써넣는다. 그 과정에서 네 가지가 일어난다.

  1. 빈 공간 제거 (디스크 회수) — 페이지의 구멍이 사라지고 살아있는 행이 조밀하게 재배치되면서, innodb_file_per_table이 켜져 있으면(RDS 기본값) 남는 공간이 OS로 반환된다. data_free가 회수되는 지점.
  2. 단편화 해소 — 흩어진 행을 PK 순서대로 물리적으로 재배치한다. 범위 스캔 성능이 개선될 수 있다.
  3. 인덱스 재구성 — 모든 인덱스를 새로 빌드해 조밀하게 만든다.
  4. 통계 갱신 — 마지막에 옵티마이저 통계(ANALYZE)를 다시 계산한다.

ALTER TABLE ... ENGINE=InnoDB는 같은 리빌드 동작을 다른 문법으로 부르는 것이다.

비용 — 임시 디스크가 핵심 함정

"테이블을 다시 만든다"가 본질이라, 리빌드가 끝날 때까지 새 파일과 옛 파일이 동시에 존재한다. 따라서 대상 테이블 크기만큼 여유 디스크가 추가로 필요하다. 디스크가 거의 꽉 찬 상태에서 큰 테이블을 OPTIMIZE하면 디스크 풀로 작업이 실패하고, 최악엔 인스턴스가 storage-full로 빠진다. 그래서 회수는 작은 테이블부터 돌려 여유를 확보한 뒤 큰 테이블로 진행하는 순서가 안전하다. 온라인 DDL이라 시작·끝의 짧은 메타데이터 락 외엔 읽기/쓰기가 대체로 가능하지만, 대용량이면 I/O 부하와 복제 지연이 커서 저트래픽 시간대를 권장한다.

RDS에서

OPTIMIZE TABLE / ALTER TABLE ... ENGINE=InnoDB 모두 RDS MySQL·Aurora MySQL에서 그대로 동작한다. 실행 시 Table does not support optimize, doing recreate + analyze instead 경고가 뜨는데, "지원 안 함"이 아니라 리빌드로 대체 실행한다는 뜻이며 공간은 정상 회수된다. 임시 디스크는 RDS 할당 스토리지(FreeStorageSpace)에서 소모된다. mysqlcheck 같은 OS 레벨 도구는 RDS 서버에 직접 접근할 수 없어 SQL로만 가능하다.

관련 노트

참고

함께 읽기 좋은 글