MySQL DELETE 후 파일 크기가 줄지 않는 이유: 90% 삭제와 OPTIMIZE TABLE 실측

대량 DELETE를 끝내고 디스크 사용량을 확인했는데 숫자가 그대로라면, 가장 먼저 드는 생각은 “삭제가 제대로 안 됐나?”입니다. 그다음에는 통계가 늦게 반영된 것인지, 롤백된 것인지, 아니면 MySQL이 공간을 붙잡고 있는 것인지 하나씩 의심하게 됩니다.

이 글은 그 의심을 실측값으로 확인한 기록입니다. MySQL 8.4.11에서 10만 행 테이블을 만들고 9만 행을 삭제한 뒤, 삭제 직후와 ANALYZE TABLE 이후, OPTIMIZE TABLE 이후의 파일 크기와 내부 여유 공간을 같은 조회 SQL로 측정했습니다.

결과부터 보면: 124MiB → 124MiB → 124MiB → 19MiB

실제 행 수는 삭제 직후 10,000행으로 줄었습니다. 그런데 테이블스페이스 파일(.ibd)의 크기는 삭제 직후에도, 통계를 다시 수집한 뒤에도 124MiB였습니다. 파일이 줄어든 시점은 테이블을 재구성한 OPTIMIZE TABLE 이후였고, 이때 19MiB가 되었습니다.

단계	실제 행 수	내부 여유 공간(DATA_FREE)	파일 크기(FILE_SIZE)
삭제 전	100,000	7MiB	124MiB
9만 행 DELETE 후	10,000	106MiB	124MiB
ANALYZE TABLE 후	10,000	106MiB	124MiB
OPTIMIZE TABLE 후	10,000	2MiB	19MiB

즉 DELETE는 행을 지우고 그 자리를 테이블 내부의 여유 공간으로 돌려놓을 뿐, 운영체제에 파일 공간을 돌려주지는 않았습니다. 아래에서는 이 결론에 이르기까지 어떤 조건에서 무엇을 측정했는지, 그리고 이 결과를 보고 흔히 하는 오해가 무엇인지 순서대로 정리합니다.

실험 조건: 1,000바이트 행 10만 개, 개별 파일 테이블스페이스

공간 변화가 다른 요인에 묻히지 않도록 별도 스키마에 테이블 하나만 만들었습니다. 행마다 payload를 1,000바이트로 고정했고 보조 인덱스는 만들지 않았습니다. 인덱스가 여러 개 있으면 각 인덱스의 공간 변화가 섞이기 때문입니다.

CREATE TABLE lab39_delete_space.delete_space_test (
    id INT UNSIGNED NOT NULL,
    payload VARCHAR(1000) CHARACTER SET ascii NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB
  ROW_FORMAT = DYNAMIC
  TABLESPACE = innodb_file_per_table;

데이터는 id 1부터 100,000까지 입력하고 RPAD로 payload를 1,000바이트로 채웠습니다. 입력이 끝난 뒤 버전, 페이지 크기, autocommit 상태와 실제 행 수, 문자열 길이를 한 번에 확인했습니다.

항목	값
MySQL 버전	8.4.11
스키마 / 테이블	lab39_delete_space.delete_space_test
초기 데이터	100,000행, 행당 payload 1,000바이트
인덱스	PRIMARY KEY (id)만 사용
저장 방식	InnoDB, DYNAMIC, 개별 파일 테이블스페이스
페이지 크기	16,384바이트
autocommit	활성화
통계 캐시	information_schema_stats_expiry = 0 (세션)

마지막 항목은 측정 정확도와 관련이 있습니다. MySQL 8.0 이후 INFORMATION_SCHEMA.TABLES의 통계 컬럼은 기본적으로 일정 시간 캐시된 값을 보여 줍니다. 단계마다 조회 결과가 갱신되지 않은 것처럼 보이는 일을 막기 위해 세션 값을 0으로 두었습니다.

SET SESSION information_schema_stats_expiry = 0;

이 설정은 저장 엔진에서 최신 통계를 가져오게 할 뿐입니다. 추정 행 수를 정확한 값으로 바꾸거나 파일 크기에 영향을 주는 설정은 아닙니다.

측정 SQL: 테이블 통계와 파일 메타데이터를 함께 봅니다

DELETE 후 공간을 확인할 때 TABLES 뷰만 보면 판단이 흔들립니다. 그 뷰에는 파일 크기가 없기 때문입니다. 그래서 INFORMATION_SCHEMA.TABLES와 INNODB_TABLESPACES를 조인해 내부 공간 지표와 파일 지표를 한 행에 놓았습니다. 각 단계에서는 phase 값만 바꿔 같은 SQL을 실행했습니다.

SELECT
    'after_delete' AS phase,
    (
        SELECT COUNT(*)
        FROM lab39_delete_space.delete_space_test
    ) AS actual_rows,
    t.TABLE_ROWS AS estimated_rows,
    ROUND(t.DATA_LENGTH / 1048576, 2) AS data_mib,
    ROUND(t.DATA_FREE / 1048576, 2) AS free_mib,
    ROUND(s.FILE_SIZE / 1048576, 2) AS file_mib,
    ROUND(s.ALLOCATED_SIZE / 1048576, 2) AS allocated_mib
FROM information_schema.TABLES t
JOIN information_schema.INNODB_TABLESPACES s
    ON s.NAME = CONCAT(t.TABLE_SCHEMA, '/', t.TABLE_NAME)
WHERE t.TABLE_SCHEMA = 'lab39_delete_space'
  AND t.TABLE_NAME = 'delete_space_test';

운영 서버에서 대상 테이블만 바꿔 그대로 사용할 수 있습니다. 각 컬럼이 무엇을 뜻하는지는 다음과 같습니다.

컬럼	원본 지표	의미
actual_rows	COUNT(*)	실제로 남아 있는 행 수
estimated_rows	TABLE_ROWS	통계 기반 추정 행 수
data_mib	DATA_LENGTH	클러스터 인덱스에 할당된 공간의 근사값
free_mib	DATA_FREE	테이블스페이스가 보고하는 내부 여유 공간
file_mib	FILE_SIZE	.ibd 파일의 논리적 크기
allocated_mib	ALLOCATED_SIZE	디스크에 실제로 할당된 파일 공간

용량은 바이트를 1,048,576으로 나눈 MiB 값입니다. 이 값들은 테이블 하나의 파일에 대한 메타데이터이며 서버 전체 디스크 사용량을 뜻하지는 않습니다.

단계별 측정 결과

삭제 전: 파일 124MiB, 추정 행 수는 이미 실제와 다름

기준값은 실제 100,000행, 데이터 할당량 112.63MiB, 여유 공간 7MiB, 파일 124MiB입니다. 눈여겨볼 부분은 추정 행 수가 92,872행이라는 점입니다. 아무 작업도 하지 않은 상태에서 이미 7% 가까이 차이가 납니다.

TABLE_ROWS는 InnoDB가 일부 페이지를 샘플링해 계산하는 추정치입니다. 삭제 전후의 TABLE_ROWS를 빼서 “몇 건이 지워졌다”고 판단하면 이 오차가 그대로 결과에 섞입니다. 삭제 건수는 COUNT(*)나 DELETE 문이 돌려준 영향 행 수로 확인해야 합니다.

9만 행 DELETE 후: 내부 여유 공간만 106MiB로 증가

DELETE FROM lab39_delete_space.delete_space_test
WHERE id <= 90000
LIMIT 90000;

COMMIT;

실제 행 수는 10,000행으로 정확히 줄었습니다. 삭제 자체는 끝까지 수행되었고 커밋도 반영되었다는 뜻입니다.

공간 지표는 서로 다른 방향으로 움직였습니다. DATA_LENGTH는 112.63MiB에서 13.02MiB로 줄었고, DATA_FREE는 7MiB에서 106MiB로 늘었습니다. 그런데 FILE_SIZE와 ALLOCATED_SIZE는 124MiB 그대로였습니다.

정리하면, 지워진 행이 쓰던 공간은 사라진 것이 아니라 테이블스페이스 안의 빈 공간으로 이동했습니다. 파일의 바깥 크기는 그대로이고 안쪽에 빈 영역이 크게 생긴 상태입니다. 이후 같은 테이블에 들어오는 데이터는 이 영역을 먼저 사용할 수 있습니다.

다만 이 캡처만으로는 삭제된 레코드를 실제로 정리하는 백그라운드 작업(purge)이 언제 끝났는지까지는 알 수 없습니다. 측정 시점에 지표가 위와 같았다는 사실만 기록합니다.

ANALYZE TABLE 후: 모든 지표가 그대로

혹시 파일 크기가 오래된 통계로 표시되는 것은 아닌지 확인하려고 통계를 다시 수집했습니다.

ANALYZE TABLE lab39_delete_space.delete_space_test;

추정 행 수 9,295행, DATA_LENGTH 13.02MiB, DATA_FREE 106MiB, 파일 124MiB로 삭제 직후와 완전히 같았습니다. 9만 행이 바뀌면서 InnoDB의 자동 통계 재계산 기준(변경 행 10%)을 넘었기 때문에, 삭제 직후 측정 시점에 이미 통계가 갱신되어 있었던 것으로 보입니다. 캐시를 0으로 둔 덕분에 그 갱신된 값이 조회에 바로 반영되었습니다.

값이 바뀌지 않았다고 해서 ANALYZE TABLE이 실패한 것은 아닙니다. 이 명령은 키 분포 통계를 다시 계산하는 작업이고, 파일을 정리하는 기능은 처음부터 없습니다. 통계를 아무리 갱신해도 파일 크기를 결정하는 것은 실제 파일 상태입니다.

OPTIMIZE TABLE 후: 파일 19MiB, 105MiB 반환

OPTIMIZE TABLE lab39_delete_space.delete_space_test;

ANALYZE TABLE lab39_delete_space.delete_space_test;

InnoDB 테이블에 OPTIMIZE TABLE을 실행하면 MySQL은 이를 테이블 재구성(recreate)과 통계 갱신으로 처리합니다. 남아 있는 행만으로 새 테이블스페이스를 만들고 기존 파일을 교체하는 방식이라, 개별 파일 테이블스페이스에서는 쓰이지 않던 공간이 파일에서 빠집니다.

행 수는 10,000행 그대로이고, 파일 크기와 할당 크기는 124MiB에서 19MiB로 줄었습니다. 105MiB, 약 84.68%가 반환되었습니다. DATA_FREE는 106MiB에서 2MiB가 되었습니다.

네 단계를 모두 합친 측정값은 다음과 같습니다. 단위는 MiB입니다.

단계	실제 행 수	추정 행 수	DATA_LENGTH	DATA_FREE	FILE_SIZE	ALLOCATED_SIZE
삭제 전	100,000	92,872	112.63	7.00	124.00	124.00
삭제 후	10,000	9,295	13.02	106.00	124.00	124.00
ANALYZE 후	10,000	9,295	13.02	106.00	124.00	124.00
OPTIMIZE 후	10,000	9,295	13.52	2.00	19.00	19.00

이 결과를 보고 하기 쉬운 오해 네 가지

오해 1. 파일이 그대로니 DELETE가 실패했다

삭제 직후 COUNT(*)는 10,000행이었습니다. 파일 크기는 삭제 성공 여부를 판단하는 지표가 아닙니다. 삭제가 됐는지는 행 수로, 공간이 어디에 있는지는 DATA_FREE와 FILE_SIZE로 나눠서 봐야 합니다.

오해 2. 90%를 지웠으니 파일도 90% 줄어든다

재구성 후에도 감소율은 약 84.68%였습니다. 남은 1만 행 외에도 페이지 구조, 테이블스페이스 관리 영역, 익스텐트 단위 할당이 파일 크기에 포함되기 때문입니다. 행 수 비율로 반환될 용량을 계산하면 실제보다 크게 잡히기 쉽습니다.

오해 3. DATA_FREE만큼 정확히 디스크가 확보된다

재구성 전 DATA_FREE는 106MiB였고 실제로 줄어든 파일 크기는 105MiB였습니다. 이번에는 차이가 작았지만, DATA_FREE는 완전히 비어 있는 익스텐트 위주로 계산되는 값이라 페이지 안에 흩어진 빈 공간은 반영되지 않습니다. 재구성 효과를 미리 가늠하는 참고값으로는 쓸 수 있지만 반환 용량을 보장하는 숫자는 아닙니다.

오해 4. 재구성 후 DATA_LENGTH가 늘었으니 공간이 더 들었다

OPTIMIZE 후 DATA_LENGTH는 13.02MiB에서 13.52MiB로 0.50MiB 늘었습니다. 이 숫자만 보면 재구성이 손해처럼 보이지만, 같은 시점에 파일은 105MiB 줄었습니다. 재구성 과정에서 페이지를 새로 채우고 할당하는 방식이 달라지면서 근사값인 DATA_LENGTH가 조금 움직일 수 있습니다. 이번 측정만으로 0.50MiB의 세부 원인을 특정할 수는 없으므로, 공간 회수 여부는 FILE_SIZE와 ALLOCATED_SIZE로 판단했습니다.

운영 테이블에 OPTIMIZE TABLE을 실행하기 전 점검 항목

10만 행 테스트 테이블에서는 재구성이 금방 끝났지만, 운영 테이블은 사정이 다릅니다. 실행 전에 다음 항목을 확인합니다.

• 공간을 정말 돌려받아야 하는가: 삭제 후에도 같은 테이블에 데이터가 계속 쌓인다면 내부 여유 공간은 곧 다시 쓰입니다. 디스크 여유가 충분하다면 재구성 없이 두는 편이 나을 수 있습니다. 이번 실험은 재삽입을 하지 않았으므로 재사용 속도까지 측정하지는 않았습니다.
• 작업용 디스크 공간: 재구성은 남은 데이터를 새 파일로 옮겨 적는 방식이므로 작업 중에는 새 파일이 들어갈 여유 공간이 필요합니다. 디스크가 거의 찬 상황에서 공간을 확보하려고 실행하면 오히려 작업이 실패할 수 있습니다.
• 잠금과 I/O: 온라인 DDL로 처리되더라도 시작과 끝에 메타데이터 잠금이 필요합니다. 오래 열린 트랜잭션이 있으면 그 잠금을 기다리며 뒤따르는 쿼리까지 막힐 수 있으므로, 실행 전 INNODB_TRX와 PROCESSLIST에서 장기 트랜잭션을 먼저 확인합니다.
• 복제 환경: OPTIMIZE TABLE은 기본적으로 바이너리 로그에 기록되어 레플리카에서도 실행됩니다. 레플리카 지연이 생길 수 있는 시간대인지 함께 고려합니다.
• 테이블스페이스 형태: 이번 결과는 테이블마다 .ibd 파일을 쓰는 조건입니다. 시스템 테이블스페이스(ibdata1)나 일반 공유 테이블스페이스에 있는 테이블은 재구성해도 해당 파일이 같은 방식으로 줄어들지 않습니다.

대형 테이블에서 이번 결과를 근거로 소요 시간이나 필요한 여유 공간을 추정할 수는 없습니다. 운영 적용 전에는 비슷한 크기의 복제본에서 먼저 실행해 보고 시간을 재 두는 것이 안전합니다.

운영에서 적용한 방식: 최한시 수동 실행에서 정기 배치로

실제 운영에서는 DML이 심하게 일어나는 테이블을 대상으로 OPTIMIZE TABLE을 수행한 적이 있습니다. 입력, 수정, 삭제가 계속 반복되는 테이블은 이번 실험처럼 내부 여유 공간이 쌓이기 쉽고, 파일 크기도 실제 데이터에 비해 커진 상태로 남습니다.

처음에는 해당 테이블에 액세스가 거의 없는 최한시를 골라 수동으로 실행했습니다. 앞에서 정리한 잠금과 I/O 부담을 서비스 시간대에 떠안지 않기 위해서였습니다. 효과가 확인된 뒤에는 같은 작업을 자동 배치로 등록해 정해진 시간에 주기적으로 수행되도록 했습니다.

정기적으로 재구성을 돌린 뒤로는 해당 테이블의 성능이 꾸준하게 유지되었고, 재구성 때마다 스토리지 공간도 반환되어 디스크 여유를 확보할 수 있었습니다. 이번 실험에서 DELETE 후 124MiB가 OPTIMIZE 후 19MiB로 줄어든 것과 같은 일이 운영 테이블에서는 주기마다 반복된 셈입니다.

자동 배치로 돌릴 때는 사람이 실행 직전에 상황을 보지 않는다는 점을 감안해야 합니다. 배치 스크립트에 장기 트랜잭션 존재 여부와 디스크 여유 공간을 먼저 확인하는 단계를 두고, 조건이 맞지 않으면 그날 작업을 건너뛰게 만들어 두면 메타데이터 잠금 대기나 작업 중 공간 부족 같은 위험을 줄일 수 있습니다. 실행 후에는 위의 측정 SQL로 FILE_SIZE를 기록해 두면 재구성 주기가 적절한지 판단하는 근거로도 쓸 수 있습니다.

DELETE 후 공간이 줄지 않을 때 확인하는 순서

1. COUNT(*)로 실제 행 수를 확인해 삭제와 커밋이 반영됐는지 봅니다. TABLE_ROWS는 추정치이므로 이 판단에 쓰지 않습니다.
2. 위 측정 SQL로 DATA_FREE를 확인해 삭제된 공간이 내부 여유 공간으로 잡혔는지 봅니다.
3. FILE_SIZE와 ALLOCATED_SIZE를 확인해 파일 크기가 그대로인지 봅니다. 이 상태가 정상적인 DELETE 이후의 모습입니다.
4. 앞으로의 데이터 증가량과 디스크 여유를 비교해 재구성이 꼭 필요한지 판단합니다.
5. 필요하다면 작업 공간, 장기 트랜잭션, 복제 영향을 점검한 뒤 한가한 시간대에 OPTIMIZE TABLE을 실행하고, 같은 SQL로 FILE_SIZE 감소를 확인합니다.

이번 실험에서 DELETE는 공간을 테이블 안에 남겨 두었고, ANALYZE TABLE은 그 상태를 그대로 보여 주었으며, 파일을 실제로 줄인 것은 재구성뿐이었습니다. 디스크 사용량만 보고 삭제 실패를 의심하기 전에, 공간이 테이블 안에 있는지 파일 밖으로 나갔는지부터 나눠서 확인하면 원인을 훨씬 빨리 좁힐 수 있습니다.