MySQL delete 실행 시 Table disk size가 줄어들지 않음

개발 도중 데이터 정리가 필요해 보여 특정 테이블 데이터 전체를 다 날렸는데 테이블 사이즈가 줄어들지 않아 의문이 들었다. 분명 테이블을 조회 하면 데이터가 0개 인데 테이블 사이즈는 1gb에서 바뀌지 않았다.

아래 쿼리로 실제 테이블의 크기를 확인 할 수 있다. Data_length, Index_length 컬럼을 합친 값이 실제 테이블이 점유하는 테이블 크기이다.

SHOW TABLE STATUS LIKE 'tb_t1';

테이블 사이즈를 줄인 후 쿼리를 실행해 사이즈가 작은 것이다. 값이 아닌 컬럼 정보를 확인하자.

MySQL의 테이블스페이스는 데이터베이스 테이블 데이터, 인덱스가 저장되는 물리적인 공간(파일)을 의미 하며 아래 5가지 테이블스페이스를 가진다. 이 중 실제 데이터는 File-Per_Table Tablespace에 저장 된다.

The System Tablespace

File-Per_Table Tablespace

General Tablespace

Undo Tablespace

Temporary Tablespace

File-Per_Table Tablespace

File-Per_Table Tablespace는 단일 InnoDB테이블에 대한 데이터와 인덱스를 포함하는 테이블스페이스입니다. 파일 시스템에 단일 데이터 파일로 저장된다.

File-Per_Table Tablespace 문서를 확인하면서 내가 확인하고자 했던 내용을 확인할 수 있었다. "테이블당 파일 테이블스페이스를 사용하면 각 테이블에는 동일한 테이블의 행에서만 사용할 수 있는 사용되지 않은 공간이 있을 수 있으며, 제대로 관리하지 않으면 공간이 낭비될 수 있습니다."

나의 경우 공간이 낭비된다고 생각해 데이터를 삭제했지만 실제 디스크 영역이 할당 되어 있어 기대와 달랐던 것이 이해가 되었다.

 

강제로 물리적인 테이블 사이즈 조정을 위해 "OPTIMIZE TABLE tb_t1" 쿼리를 물리적 공간을 최적화 시켜주었다.

OPTIMIZE TABLE은 테이블 데이터 및 인덱스 데이터의 물리적 스토리지를 재구성해 실제 스토리지를 줄이고 테이블에 엑세스할 때 I/O 효율성을 올린다.

삭제한 행으로 인해 테이블 내에 빈 공간이 발생한 경우 해당 공간을 회수 하며 테이블의 실제 크기를 조정한다. 이는 insert가 발생했을 때 공간을 효율적으로 활용하기 위함이며, 이로인해, 테이블 파일이 디스크공간을 낭비가 있을 수도 있다.

OPTIMIZE TABLE을 실행하면 InnoDB엔진은 테이블을 복사하고 기존 테이블을 대체 합니다. 이 과정에서 빈 공간이 회수되며 테이블 파일의 실제 크기가 줄어들어 디스크 공간을 절약할 수 있습니다.

 

참고 : https://dev.mysql.com/doc/refman/8.0/en/innodb-file-per-table-tablespaces.html

 https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

 https://dev.mysql.com/doc/refman/8.0/en/innodb-purge-configuration.html

 https://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table