--- title: "OPTIMIZE TABLE과 공간 회수: tablespace 재구성과 운영 리스크" description: "InnoDB의 DELETE와 공간 반환 차이, OPTIMIZE TABLE의 재구축 과정, tablespace별 회수 한계와 Aurora 운영 점검 기준을 정리한다." tags: [ MySQL, InnoDB, 성능최적화, 운영 ] image: "mysql-report-bg.png" published: "2026-10-22" updated: "2026-10-22" author: "MySQL 기술 노트" source_url: "" --- 대량 삭제 후 행 수가 줄었는데도 디스크 사용량이 그대로인 것은 반드시 이상 현상이 아니다. InnoDB에서 **행을 삭제하는 일, 페이지를 재사용할 수 있게 하는 일, tablespace 파일을 줄여 운영체제에 반환하는 일**은 서로 다른 단계다. 이를 구분하지 않으면 공간이 거의 소진된 시점에 `OPTIMIZE TABLE`을 실행해 오히려 추가 공간 부족과 서비스 지연을 일으킬 수 있다. 이 글은 MySQL 8.0 이상 InnoDB를 기준으로 공간 회수의 원리와 판단 절차를 설명한다. SQL은 폐기 가능한 MySQL 8.0.46 인스턴스에서 실행했다. file-per-table과 general tablespace의 차이 및 재구축 후 데이터 일치를 검증했으며, 운영 규모의 처리량·동시 쓰기·복제 지연이나 Aurora의 실제 용량 감소를 측정한 것은 아니다. ## 1. DELETE 이후 공간은 어디에 남는가 InnoDB의 clustered index는 기본 키 순서로 행을 보관한다. `DELETE`는 트랜잭션 가시성과 rollback을 유지하면서 레코드를 삭제 처리한다. 이전 버전을 필요로 하는 Read View가 사라지면 purge가 불필요한 이력을 정리할 수 있다. 장기 트랜잭션이 있으면 이 단계가 늦어질 수 있다. 그러나 purge가 진행되어도 `.ibd` 파일의 끝을 자동으로 잘라 주는 것은 아니다. 페이지 안의 빈 부분이나 tablespace의 비어 있는 extent는 이후 쓰기에서 재사용할 수 있지만, 그 공간이 모두 운영체제의 가용 공간으로 바뀌지는 않는다. 따라서 다음 상태를 분리해 판단해야 한다. - **논리적 데이터량:** 현재 조회되는 행과 값의 크기다. `COUNT(*)` 감소는 이것만 증명한다. - **InnoDB 내부 재사용 공간:** 삭제·purge·페이지 정리 이후 InnoDB가 다시 활용할 수 있는 영역이다. 모든 빈 부분이 `DATA_FREE` 하나에 완전하게 표현되지는 않는다. - **tablespace 파일 크기:** 파일에 할당된 전체 공간이다. 같은 행 수라도 삽입 순서, 페이지 분할, 인덱스 구성에 따라 달라진다. - **운영체제 또는 관리형 스토리지 사용량:** 실제 파일시스템 할당량이나 Aurora cluster volume 사용량이다. 논리적 파일 길이와 항상 같지는 않다. 지속적으로 같은 양의 데이터가 들어오는 순환형 테이블은 내부 여유 공간을 다시 사용하는 편이 나을 수 있다. 모든 빈 공간을 없앤 직후 다시 확장해야 한다면 재구축 비용만 추가된다. 반대로 일회성 대량 삭제 후 장기간 작은 크기를 유지할 테이블은 공간 반환을 검토할 가치가 있다. ## 2. OPTIMIZE TABLE은 가벼운 청소가 아니라 재구축이다 일반적인 InnoDB 테이블에서 `OPTIMIZE TABLE`은 `ALTER TABLE ... FORCE`에 대응하는 재구축을 수행하고 통계를 갱신한다. 기존 데이터 페이지를 부분적으로 비우는 명령이 아니라, 행과 인덱스를 다시 구성하는 작업으로 이해해야 한다. ```mermaid flowchart TD A[대량 DELETE 및 커밋] --> B[이전 버전 필요 여부에 따라 purge 진행] B --> C[내부 재사용 공간 증가 가능] C --> D{외부 공간 반환이 필요한가} D -->|아니오| E[재사용 및 증가 추세 관측] D -->|예| F[실제 tablespace와 작업 여유 공간 확인] F --> G[MDL 확보 및 중간 테이블 준비] G --> H[행과 인덱스 재구축 및 동시 변경 반영] H --> I[종료 단계 MDL 및 메타데이터 전환] I --> J{저장 방식} J -->|file-per-table| K[기존 파일 제거로 외부 공간 반환 가능] J -->|공유 tablespace| L[공유 파일 내부 재사용 공간으로 남음] ``` 일반적인 online DDL 경로에서는 주요 재구축 구간에 동시 DML을 허용하지만, 준비·종료 단계에서 배타적 잠금이 필요하다. `INPLACE`라는 분류는 **테이블 재구축도 없고 새 파일도 만들지 않는다**는 뜻이 아니다. 준비 단계의 잠금 확보가 지연되거나 종료 단계에 반영할 변경이 많이 쌓이면 서비스 영향이 커진다. MySQL은 InnoDB 처리 결과에 `Table does not support optimize, doing recreate + analyze instead`라는 `note`를 반환할 수 있다. 이것은 그 자체로 실패가 아니다. 뒤따르는 `status: OK` 또는 `error` 행까지 확인해야 한다. 자동화도 프로세스 종료 코드만 보지 말고 결과 집합의 `Msg_type`과 `Msg_text`를 해석해야 한다. 다만 이 설명을 모든 테이블에 그대로 적용해서는 안 된다. `FULLTEXT` 인덱스가 있는 테이블은 일반 online 경로의 예외이며, `old_alter_table` 등의 설정도 처리 경로에 영향을 준다. `innodb_optimize_fulltext_only=1`은 전문 검색 인덱스 유지보수 모드이므로 전체 테이블의 공간 회수와 동일하게 취급하지 않는다. ## 3. file-per-table: 삭제 전후와 재구축 후를 비교한다 아래 예제는 **비어 있는 별도 실습 데이터베이스에서 순서대로** 실행한다. 임의의 운영 테이블에 적용하지 않는다. `CREATE TABLESPACE`와 InnoDB 메타데이터 조회에는 추가 권한이 필요할 수 있다. 정리 명령은 마지막 예제에 있다. 실행 결과는 한 번의 검증에서 얻은 값이다. 준비 문장의 반복과 시간 표시는 일부 줄였으며, 축약한 명령은 `...`로 표시했다. 파일 크기는 버전·설정·페이지 배치에 따라 달라질 수 있으므로 고정 기대값으로 사용하지 않는다. ### 3.1. 고정 데이터와 삭제 전 크기 준비 기본 키가 1부터 10000까지이고 행마다 서로 구별되는 문자열을 가진 테이블을 만든다. `TABLESPACE=innodb_file_per_table`을 명시하여 전역 기본값과 별개로 저장 방식을 고정한다. 크기는 행·인덱스 통계의 합이 아니라 `INNODB_TABLESPACES`에서 관측한다. ```sql CREATE TABLE opt_digits (n INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO opt_digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE TABLE opt_file ( id INT PRIMARY KEY, payload VARCHAR(1000) NOT NULL ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC TABLESPACE=innodb_file_per_table; INSERT INTO opt_file SELECT 1 + a.n + 10*b.n + 100*c.n + 1000*d.n, RPAD(CONCAT('row-', 1 + a.n + 10*b.n + 100*c.n + 1000*d.n),1000,'x') FROM opt_digits a CROSS JOIN opt_digits b CROSS JOIN opt_digits c CROSS JOIN opt_digits d; CREATE TABLE opt_sizes ( phase_no INT PRIMARY KEY, phase VARCHAR(30) NOT NULL, file_bytes BIGINT UNSIGNED, allocated_bytes BIGINT UNSIGNED ) ENGINE=InnoDB; INSERT INTO opt_sizes SELECT 1, 'before_delete', FILE_SIZE, ALLOCATED_SIZE FROM information_schema.INNODB_TABLESPACES WHERE NAME=CONCAT(DATABASE(), '/opt_file'); SELECT COUNT(*) AS initial_rows FROM opt_file; ``` 실행 결과(MySQL 8.0.x): ```text mysql> CREATE TABLE opt_file (...); Query OK, 0 rows affected mysql> INSERT INTO opt_file SELECT ...; Query OK, 10000 rows affected Records: 10000 Duplicates: 0 Warnings: 0 mysql> SELECT COUNT(*) AS initial_rows FROM opt_file; +--------------+ | initial_rows | +--------------+ | 10000 | +--------------+ 1 row in set ``` ### 3.2. 삭제는 행 수를 줄이지만 파일 크기를 바로 줄이지 않는다 9000행을 삭제하고 남은 1000행을 별도 기준 테이블에 복사한다. 이 복사본은 실습의 내용 비교용이지 운영 백업의 대체 수단이 아니다. 삭제는 autocommit 상태에서 완료된다. ```sql DELETE FROM opt_file WHERE id <= 9000; CREATE TABLE opt_expected LIKE opt_file; INSERT INTO opt_expected SELECT * FROM opt_file; INSERT INTO opt_sizes SELECT 2, 'after_delete', FILE_SIZE, ALLOCATED_SIZE FROM information_schema.INNODB_TABLESPACES WHERE NAME=CONCAT(DATABASE(), '/opt_file'); SELECT COUNT(*) AS remaining_rows FROM opt_file; SELECT phase, file_bytes, allocated_bytes FROM opt_sizes ORDER BY phase_no; ``` 실행 결과(MySQL 8.0.x): ```text mysql> DELETE FROM opt_file WHERE id <= 9000; Query OK, 9000 rows affected mysql> SELECT COUNT(*) AS remaining_rows FROM opt_file; +----------------+ | remaining_rows | +----------------+ | 1000 | +----------------+ 1 row in set mysql> SELECT phase, file_bytes, allocated_bytes FROM opt_sizes ORDER BY phase_no; +---------------+------------+-----------------+ | phase | file_bytes | allocated_bytes | +---------------+------------+-----------------+ | before_delete | 37748736 | 37748736 | | after_delete | 37748736 | 37748736 | +---------------+------------+-----------------+ 2 rows in set ``` 이번 실행에서는 행 수가 1000으로 줄었지만 `FILE_SIZE`와 `ALLOCATED_SIZE`는 삭제 직후 모두 유지되었다. 이 결과를 “purge가 고장 났다”거나 “삭제가 커밋되지 않았다”는 증거로 해석해서는 안 된다. 이 예제는 purge 완료 시점을 별도로 계측하지 않았고, purge 완료와 외부 파일 축소 자체도 별개의 현상이다. ### 3.3. 재구축 후에는 크기뿐 아니라 내용도 확인한다 `lock_wait_timeout`은 현재 접속에만 설정한다. 이 값은 metadata lock 대기에 적용하는 방어선이며, `OPTIMIZE TABLE` 전체 실행 시간을 5초로 제한하는 설정이 아니다. 행 잠금 대기용 `innodb_lock_wait_timeout`과도 구별한다. ```sql SET SESSION lock_wait_timeout=5; OPTIMIZE TABLE opt_file; INSERT INTO opt_sizes SELECT 3, 'after_optimize', FILE_SIZE, ALLOCATED_SIZE FROM information_schema.INNODB_TABLESPACES WHERE NAME=CONCAT(DATABASE(), '/opt_file'); SELECT phase, file_bytes, allocated_bytes FROM opt_sizes ORDER BY phase_no; SELECT COUNT(*) AS mismatch_rows FROM opt_expected e LEFT JOIN opt_file f ON f.id=e.id WHERE f.id IS NULL OR CAST(f.payload AS BINARY) <> CAST(e.payload AS BINARY); SELECT COUNT(*) AS unexpected_rows FROM opt_file f LEFT JOIN opt_expected e ON e.id=f.id WHERE e.id IS NULL; SELECT COUNT(*) AS final_rows FROM opt_file; ``` 실행 결과(MySQL 8.0.x): ```text mysql> OPTIMIZE TABLE opt_file; +--------------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------+----------+----------+-------------------------------------------------------------------+ | mysql_tech_note.opt_file | optimize | note | Table does not support optimize, doing recreate + analyze instead | | mysql_tech_note.opt_file | optimize | status | OK | +--------------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set mysql> SELECT phase, file_bytes, allocated_bytes FROM opt_sizes ORDER BY phase_no; +----------------+------------+-----------------+ | phase | file_bytes | allocated_bytes | +----------------+------------+-----------------+ | before_delete | 37748736 | 37748736 | | after_delete | 37748736 | 37748736 | | after_optimize | 7340032 | 7340032 | +----------------+------------+-----------------+ 3 rows in set mysql> SELECT COUNT(*) AS mismatch_rows -> FROM opt_expected e LEFT JOIN opt_file f ON f.id=e.id -> WHERE f.id IS NULL OR CAST(f.payload AS BINARY) <> CAST(e.payload AS BINARY); +---------------+ | mismatch_rows | +---------------+ | 0 | +---------------+ 1 row in set mysql> SELECT COUNT(*) AS unexpected_rows -> FROM opt_file f LEFT JOIN opt_expected e ON e.id=f.id WHERE e.id IS NULL; +-----------------+ | unexpected_rows | +-----------------+ | 0 | +-----------------+ 1 row in set mysql> SELECT COUNT(*) AS final_rows FROM opt_file; +------------+ | final_rows | +------------+ | 1000 | +------------+ 1 row in set ``` 검증에서는 삭제 전과 삭제 직후 파일이 36 MiB였고, 재구축 후 7 MiB로 줄었다. 잔여 행은 1000개이며, 기준 테이블 대비 누락·내용 차이와 예상하지 않은 행이 모두 0이었다. 즉, 단순히 행 개수가 같은지만 확인한 것이 아니라 기본 키별 payload를 바이트 단위로 대조했다. 이 수치는 공간 회수가 실제로 일어난 한 사례이지 회수율 보장값이 아니다. 실행 시간 역시 작은 임시 인스턴스의 값이므로 운영 소요 시간 산정에 사용할 수 없다. 재구축 후 파일에도 extent 단위 할당과 여유 공간이 남을 수 있다. ## 4. 공유 tablespace: 재구축 성공과 외부 공간 회수는 다르다 file-per-table의 `.ibd`와 general tablespace의 `.ibd`는 확장자가 같아도 수명과 공간 관리 단위가 다르다. general tablespace는 여러 테이블을 담을 수 있는 공유 파일이며, 테이블 하나의 데이터를 줄여도 파일 전체를 자동으로 축소하지 않는다. system tablespace에도 같은 종류의 제약이 있다. 다음 예제는 general tablespace에 같은 데이터를 만들고, 같은 삭제와 재구축을 수행한다. 마지막에는 **실습용 테이블을 삭제한 후 빈 tablespace 자체를 제거**한다. 이 정리는 남은 데이터를 보존하는 `OPTIMIZE TABLE`과 전혀 다른 작업이다. ```sql CREATE TABLESPACE opt_shared ADD DATAFILE 'opt_shared.ibd' ENGINE=InnoDB; CREATE TABLE opt_shared_data ( id INT PRIMARY KEY, payload VARCHAR(1000) NOT NULL ) ENGINE=InnoDB TABLESPACE=opt_shared; INSERT INTO opt_shared_data SELECT 1 + a.n + 10*b.n + 100*c.n + 1000*d.n, RPAD(CONCAT('row-', 1 + a.n + 10*b.n + 100*c.n + 1000*d.n),1000,'x') FROM opt_digits a CROSS JOIN opt_digits b CROSS JOIN opt_digits c CROSS JOIN opt_digits d; SELECT FILE_SIZE INTO @shared_before FROM information_schema.INNODB_TABLESPACES WHERE NAME='opt_shared'; DELETE FROM opt_shared_data WHERE id <= 9000; OPTIMIZE TABLE opt_shared_data; SELECT SPACE_TYPE, @shared_before AS before_bytes, FILE_SIZE AS after_bytes, FILE_SIZE < @shared_before AS file_shrank FROM information_schema.INNODB_TABLESPACES WHERE NAME='opt_shared'; SELECT COUNT(*) AS shared_mismatch_rows FROM opt_expected e LEFT JOIN opt_shared_data s ON s.id=e.id WHERE s.id IS NULL OR CAST(s.payload AS BINARY) <> CAST(e.payload AS BINARY); SELECT COUNT(*) AS shared_final_rows FROM opt_shared_data; DROP TABLE opt_shared_data; DROP TABLESPACE opt_shared; ``` 실행 결과(MySQL 8.0.x): ```text mysql> CREATE TABLESPACE opt_shared ADD DATAFILE 'opt_shared.ibd' ENGINE=InnoDB; Query OK, 0 rows affected mysql> OPTIMIZE TABLE opt_shared_data; +---------------------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------------+----------+----------+-------------------------------------------------------------------+ | mysql_tech_note.opt_shared_data | optimize | note | Table does not support optimize, doing recreate + analyze instead | | mysql_tech_note.opt_shared_data | optimize | status | OK | +---------------------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set mysql> SELECT SPACE_TYPE, @shared_before AS before_bytes, FILE_SIZE AS after_bytes, -> FILE_SIZE < @shared_before AS file_shrank -> FROM information_schema.INNODB_TABLESPACES WHERE NAME='opt_shared'; +------------+--------------+-------------+-------------+ | SPACE_TYPE | before_bytes | after_bytes | file_shrank | +------------+--------------+-------------+-------------+ | General | 37748736 | 37748736 | 0 | +------------+--------------+-------------+-------------+ 1 row in set mysql> SELECT COUNT(*) AS shared_mismatch_rows -> FROM opt_expected e LEFT JOIN opt_shared_data s ON s.id=e.id -> WHERE s.id IS NULL OR CAST(s.payload AS BINARY) <> CAST(e.payload AS BINARY); +----------------------+ | shared_mismatch_rows | +----------------------+ | 0 | +----------------------+ 1 row in set mysql> SELECT COUNT(*) AS shared_final_rows FROM opt_shared_data; +-------------------+ | shared_final_rows | +-------------------+ | 1000 | +-------------------+ 1 row in set mysql> DROP TABLESPACE opt_shared; Query OK, 0 rows affected ``` `OPTIMIZE TABLE`은 성공했고 잔여 데이터도 일치했지만, 공유 파일은 36 MiB로 유지되어 `file_shrank=0`이었다. 작업 조건에 따라 공유 tablespace가 재구축 중 더 커질 수도 있다. 내부 공간 재사용과 파일 축소를 혼동하면 “재구축이 성공했으니 디스크도 줄었다”는 잘못된 결론을 내리게 된다. general tablespace를 완전히 없애려면 그 안의 모든 테이블을 이동하거나 삭제하고 비워진 tablespace를 제거해야 한다. system tablespace는 이러한 방식으로 간단히 삭제·축소할 대상이 아니다. 별도 인스턴스로의 논리적 이관 등 훨씬 큰 작업 범위를 검토해야 한다. `innodb_file_per_table`을 켰다는 사실만으로 기존 테이블의 저장 위치까지 바뀌었다고 판단하지 않는다. ## 5. 진단값을 읽을 때의 경계 전역 기본값, 실제 테이블의 tablespace 종류, 파일 크기, 테이블 통계를 함께 본다. 다음 조회 뒤에는 실습 객체를 정리한다. ```sql SELECT @@innodb_file_per_table AS file_per_table_default, @@innodb_optimize_fulltext_only AS fulltext_only, @@old_alter_table AS old_alter_table; SELECT t.NAME AS table_name, s.SPACE_TYPE, s.FILE_SIZE, s.ALLOCATED_SIZE FROM information_schema.INNODB_TABLES t JOIN information_schema.INNODB_TABLESPACES s ON s.SPACE=t.SPACE WHERE t.NAME=CONCAT(DATABASE(), '/opt_file'); SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='opt_file'; DROP TABLE opt_expected, opt_file, opt_sizes, opt_digits; ``` 실행 결과(MySQL 8.0.x): ```text mysql> SELECT @@innodb_file_per_table AS file_per_table_default, -> @@innodb_optimize_fulltext_only AS fulltext_only, -> @@old_alter_table AS old_alter_table; +------------------------+---------------+-----------------+ | file_per_table_default | fulltext_only | old_alter_table | +------------------------+---------------+-----------------+ | 1 | 0 | 0 | +------------------------+---------------+-----------------+ 1 row in set mysql> SELECT t.NAME AS table_name, s.SPACE_TYPE, s.FILE_SIZE, s.ALLOCATED_SIZE -> FROM information_schema.INNODB_TABLES t -> JOIN information_schema.INNODB_TABLESPACES s ON s.SPACE=t.SPACE -> WHERE t.NAME=CONCAT(DATABASE(), '/opt_file'); +--------------------------+------------+-----------+----------------+ | table_name | SPACE_TYPE | FILE_SIZE | ALLOCATED_SIZE | +--------------------------+------------+-----------+----------------+ | mysql_tech_note/opt_file | Single | 7340032 | 7340032 | +--------------------------+------------+-----------+----------------+ 1 row in set mysql> SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE -> FROM information_schema.TABLES -> WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='opt_file'; +------------+-------------+--------------+-----------+ | TABLE_NAME | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +------------+-------------+--------------+-----------+ | opt_file | 1589248 | 0 | 2097152 | +------------+-------------+--------------+-----------+ 1 row in set mysql> DROP TABLE opt_expected, opt_file, opt_sizes, opt_digits; Query OK, 0 rows affected ``` 해석 기준은 다음과 같다. - `@@innodb_file_per_table=1`은 기본 생성 정책이다. 실제 대상의 `INNODB_TABLES.SPACE`와 `INNODB_TABLESPACES.SPACE_TYPE`을 확인해야 한다. 파티션 테이블은 파티션별 객체도 조사한다. - `FILE_SIZE`는 파일 크기, `ALLOCATED_SIZE`는 실제 할당된 공간을 파악하는 데 사용한다. 압축·sparse file·파일시스템 특성에 따라 두 값이 다를 수 있다. 이 실습은 임시 파일시스템에서 두 값이 같았던 사례다. - `DATA_LENGTH`와 `INDEX_LENGTH`는 InnoDB 통계에 기반한 값이다. 파일 전체 크기나 정확한 업무 데이터 바이트 수가 아니다. `INDEX_LENGTH=0`도 인덱스가 전혀 없다는 뜻은 아니다. clustered index는 `DATA_LENGTH`에 반영된다. - `DATA_FREE`는 즉시 회수 가능한 바이트 수가 아니다. 공유 tablespace에서는 해당 테이블 하나에 귀속되지 않는 공간이 포함될 수 있고, 각 페이지 내부의 빈 부분을 모두 합한 값도 아니다. 이를 테이블별로 단순 합산하거나 전체 파일에서 뺀 값을 회수량으로 보장하지 않는다. - 통계 갱신이 목적이라면 `ANALYZE TABLE`을 검토한다. 이 명령을 파일 재구축이나 공간 반환 명령으로 해석해서는 안 된다. ## 6. 운영 리스크: 공간을 줄이기 전에 공간이 더 필요하다 ### 추가 공간과 쓰기 부하 원본을 보존한 상태에서 중간 테이블과 인덱스를 만들기 때문에 재구축 중의 최고 사용량이 최종 사용량보다 클 수 있다. 원본 테이블이 있는 파일시스템의 중간 파일, `tmpdir` 또는 `innodb_tmpdir`의 정렬용 파일, 동시 변경을 기록하는 online alter log를 별도로 고려한다. `innodb_tmpdir`을 다른 디스크로 옮겨도 원본 디렉터리에 만들어지는 중간 테이블 파일까지 이동하는 것은 아니다. 공식 문서는 임시 정렬 공간이 데이터와 인덱스 규모에 이를 수 있고, 중간 테이블도 원본에 준하는 공간을 요구할 수 있다고 설명한다. 따라서 “현재 여유 공간이 예상 회수량보다 크다”는 조건만으로 실행을 승인하지 않는다. 같은 마운트에 겹치는 파일, 정상 쓰기 증가분, binlog와 relay log 보존량까지 포함해 용량 여유를 잡는다. 동시 DML이 많으면 변경 기록이 쌓인다. `innodb_online_alter_log_max_size`를 초과하면 DDL이 실패할 수 있다. 한도를 크게 만드는 것은 공짜 해결책이 아니며, 종료 시 변경 적용과 잠금 유지 구간이 길어질 수 있다. I/O 포화, 버퍼 풀 경쟁, 쿼리 지연도 관측해야 한다. ### MDL 대기와 중단 기준 테이블을 읽은 뒤 커밋하지 않은 장기 트랜잭션은 DDL의 metadata lock 확보를 막을 수 있다. 대기 중인 DDL 뒤에 신규 접근까지 대기하면서 지연이 확산되는 상황도 고려한다. 작업 전에는 `information_schema.INNODB_TRX`, 진행 중에는 `performance_schema.metadata_locks`와 `sys.schema_table_lock_waits` 등을 통해 대기 관계를 확인한다. 관련 instrumentation과 진단 계정 권한도 사전에 준비한다. 허용 대기 시간, 서비스 지연, 파일시스템 여유 공간, 복제 지연에 대해 환경별 중단 기준을 정한다. 취소를 요청해도 중간 결과 정리와 rollback이 즉시 끝난다고 보장할 수 없다. 작업 세션을 중단했다는 이유로 임시 파일을 운영체제에서 임의 삭제하지 않는다. DDL을 `START TRANSACTION`으로 감싸면 언제든 되돌릴 수 있다고 생각하는 것도 잘못이다. ### binlog와 replica MySQL은 기본적으로 `OPTIMIZE TABLE`을 binary log에 기록하여 replica에서도 실행되도록 한다. source의 작업 종료가 모든 replica의 작업 종료를 의미하지 않으며, replica에서 재구축 비용과 적용 지연이 생길 수 있다. `NO_WRITE_TO_BINLOG` 또는 그 별칭 `LOCAL`을 사용할 수 있지만, 이는 각 replica의 유지보수를 따로 책임지겠다는 운영 선택이다. SQL 결과의 논리적 일관성과 각 노드의 물리적 공간 배치는 구별해야 한다. 이 글의 검증 인스턴스는 binlog를 비활성화했으므로 복제 부하나 전파 자체를 실험한 결과는 제시하지 않는다. ## 7. Aurora MySQL에서는 cluster volume의 회수를 본다 Aurora는 인스턴스 로컬의 일반 `.ibd` 파일 크기만으로 저장 비용을 판단하는 구조가 아니다. AWS 문서에 따르면 동적 볼륨 축소는 tablespace 공간이 제거되는 작업에 적용되며, 단순한 `DELETE`는 그 자체로 축소를 일으키지 않는다. 대량 삭제 후 `OPTIMIZE TABLE`로 테이블을 재구성해 축소 가능한 공간을 만들 수 있다. 다만 SQL 성공 시점과 cluster volume 회수 완료 시점은 같지 않다. 동적 축소는 점진적이며 엔진 버전·리전의 기능 적용 상태와 클러스터 상태를 확인해야 한다. system tablespace의 빈 공간은 그 파일이 제거되지 않으므로 동일하게 회수되지 않는다. 운영에서는 다음을 분리해 기록한다. 1. `OPTIMIZE TABLE`의 성공 여부와 실행 전후 테이블 내용 검증. 2. 작업 중 writer 및 reader의 지연·부하 변화. 3. 작업 이후 CloudWatch `VolumeBytesUsed`의 시간 추세. 4. cluster volume 사용량과 인스턴스 로컬 임시 공간의 가용량. Aurora reader는 일반적인 binlog replica와 같은 방식으로 각자 데이터 파일을 유지보수하는 대상으로 간주하지 않는다. 재구축은 writer에서 지원되는 방식으로 수행하고 reader 영향은 별도로 관측한다. 앞의 Community MySQL 실험에서 줄어든 29 MiB를 Aurora 회수량·과금 감소량으로 환산해서는 안 된다. 이 글에서는 Aurora 클러스터에 접속하거나 실제 축소를 시험하지 않았다. ## 8. 실행 여부를 결정하는 체크리스트 - [ ] 삭제된 공간이 앞으로 다시 사용될지, 외부 반환이 실제로 필요한지 구분했는가? - [ ] 대상 테이블과 파티션의 실제 tablespace 종류를 확인했는가? - [ ] `FULLTEXT`, 전역 설정, 대상 버전의 online DDL 제한을 확인했는가? - [ ] 파일 크기와 내부 통계를 구분하고 `DATA_FREE`를 회수 보장량으로 사용하지 않았는가? - [ ] 중간 테이블·정렬 파일·online alter log·정상 쓰기를 포함한 최고 공간 사용량을 추산했는가? - [ ] 장기 트랜잭션, MDL 대기, 복제 상태와 작업 시간대를 확인했는가? - [ ] lock 대기·서비스 지연·여유 공간에 대한 중단 기준과 취소 후 정리 절차가 있는가? - [ ] 복원 검증된 백업과 작업 전후 데이터 비교 기준이 있는가? - [ ] 결과 집합의 `error` 여부와 최종 `status`까지 확인하도록 자동화했는가? - [ ] Aurora라면 SQL 종료와 `VolumeBytesUsed` 감소를 별도의 완료 조건으로 관리하는가? ## 9. 정리 `OPTIMIZE TABLE`의 핵심은 여유 공간 숫자를 보고 정기적으로 청소하는 데 있지 않다. **삭제 후 공간의 수명, tablespace의 소유 단위, 재구축의 최고 자원 사용량을 함께 판단하는 것**이 핵심이다. file-per-table에서는 실제 외부 공간 반환을 기대할 수 있지만, 공유 tablespace에서는 동일한 SQL이 성공해도 파일 크기가 줄지 않을 수 있다. 작업의 성공 기준도 “명령이 끝났다”에 두지 않는다. 데이터가 보존되었고 서비스 영향이 허용 범위였으며 목표 저장 계층에서 공간이 반환되었는지 각각 확인해야 한다. 이 기준은 이후 테이블 재구성, 파티션 수명 관리, 대형 스키마 변경의 작업 승인과 사후 검증에도 그대로 이어진다. ## 참고 문서 - [MySQL 8.0 Reference Manual — OPTIMIZE TABLE](https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html) - [MySQL 8.0 Reference Manual — File-Per-Table Tablespaces](https://dev.mysql.com/doc/refman/8.0/en/innodb-file-per-table-tablespaces.html) - [MySQL 8.0 Reference Manual — Online DDL Space Requirements](https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-space-requirements.html) - [Amazon Aurora User Guide — Managing performance and scaling](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Managing.Performance.html)