OPTIMIZE TABLE과 공간 회수: tablespace 재구성과 운영 리스크
InnoDB의 DELETE와 공간 반환 차이, OPTIMIZE TABLE의 재구축 과정, tablespace별 회수 한계와 Aurora 운영 점검 기준을 정리한다.
대량 삭제 후 행 수가 줄었는데도 디스크 사용량이 그대로인 것은 반드시 이상 현상이 아니다. 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에 대응하는 재구축을 수행하고 통계를 갱신한다. 기존 데이터 페이지를 부분적으로 비우는 명령이 아니라, 행과 인덱스를 다시 구성하는 작업으로 이해해야 한다.
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에서 관측한다.
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):
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 상태에서 완료된다.
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):
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과도 구별한다.
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):
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과 전혀 다른 작업이다.
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):
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 종류, 파일 크기, 테이블 통계를 함께 본다. 다음 조회 뒤에는 실습 객체를 정리한다.
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):
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의 빈 공간은 그 파일이 제거되지 않으므로 동일하게 회수되지 않는다.
운영에서는 다음을 분리해 기록한다.
OPTIMIZE TABLE의 성공 여부와 실행 전후 테이블 내용 검증.- 작업 중 writer 및 reader의 지연·부하 변화.
- 작업 이후 CloudWatch
VolumeBytesUsed의 시간 추세. - cluster volume 사용량과 인스턴스 로컬 임시 공간의 가용량.
Aurora reader는 일반적인 binlog replica와 같은 방식으로 각자 데이터 파일을 유지보수하는 대상으로 간주하지 않는다. 재구축은 writer에서 지원되는 방식으로 수행하고 reader 영향은 별도로 관측한다. 앞의 Community MySQL 실험에서 줄어든 29 MiB를 Aurora 회수량·과금 감소량으로 환산해서는 안 된다. 이 글에서는 Aurora 클러스터에 접속하거나 실제 축소를 시험하지 않았다.
8. 실행 여부를 결정하는 체크리스트
-
FULLTEXT - 파일 크기와 내부 통계를 구분하고
DATA_FREE - 결과 집합의
error여부와 최종status - Aurora라면 SQL 종료와
VolumeBytesUsed
9. 정리
OPTIMIZE TABLE의 핵심은 여유 공간 숫자를 보고 정기적으로 청소하는 데 있지 않다. 삭제 후 공간의 수명, tablespace의 소유 단위, 재구축의 최고 자원 사용량을 함께 판단하는 것이 핵심이다. file-per-table에서는 실제 외부 공간 반환을 기대할 수 있지만, 공유 tablespace에서는 동일한 SQL이 성공해도 파일 크기가 줄지 않을 수 있다.
작업의 성공 기준도 “명령이 끝났다”에 두지 않는다. 데이터가 보존되었고 서비스 영향이 허용 범위였으며 목표 저장 계층에서 공간이 반환되었는지 각각 확인해야 한다. 이 기준은 이후 테이블 재구성, 파티션 수명 관리, 대형 스키마 변경의 작업 승인과 사후 검증에도 그대로 이어진다.