MySQL에서는 .ibd 확장자를 가진 데이터베이스 파일 용량을 줄여야 한다. ibd 파일의 용량은 테이블 데이터를 delete 해도 줄어들지 않는 특징이 있다.
이 글에선 ibd 파일 용량을 줄이는 방법을 크게 3가지로 소개하는데 운영 환경이 모두 다를 거라 무작정 따라하는 것보다는 각각의 방법을 이해하고 적절한 것으로 하면 좋을 거 같다.
참고로 MariaDB도 동일하게 작업이 가능하다.
MySQL ibd 파일 용량 줄이기
실습 환경 구성
실습 환경을 구성하고 차근 차근 진행해보자.
BigDB라는 데이터베이스에 TEST라는 테이블을 만들었다. 그리고 이 테이블에 데이터를 600MB 쯤 insert 하는 프로시저를 만들었다.
DELIMITER //
CREATE PROCEDURE fillTestData()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1024 * 500 DO
INSERT INTO TEST VALUES (REPEAT('A', 1024));
SET i = i + 1;
END WHILE;
END //
DELIMITER ;CALL fillTestData();프로시저를 호출해서 데이터를 insert 하고 TEST.ibd 파일을 확인해보니 용량이 588MB로 늘어난 게 확인 된다.
1. truncate
디스크 용량 확보를 위해 가장 먼저 추천하는 방법은 truncate 이다. 가장 깔끔하고 빠르게 디스크를 비울 수 있다.
truncate을 하면 테이블 안에 있는 데이터를 모조리 삭제함과 동시에 디스크를 차지하고 있는 ibd 파일 용량도 전부 없애준다. 그러면서도 테이블은 삭제되지 않아서 그대로 계속 쓸 수 있다.
문제는 truncate은 delete와 다르게 where절과 함께 쓸 수 없다는 것인데 그말은 테이블 데이터를 통째로 날려야 된다는 의미다.
MariaDB [BigDB]> truncate table TEST;
Query OK, 0 rows affected (0.002 sec)하지만 강력한 장점이 있다. truncate은 부하가 없다. 600MB 정도 되는 테이블도 0.002 초만에 비워버리는 걸 볼 수 있는데 1TB 단위로 가도 1초내로 끝난다.
만약 데이터 백업이 필요하지 않은 테이블이다? 그럼 묻지도 따지지도 말고 truncate 하는 게 정신 건강에 좋다.
[root@localhost BigDB]# ls -lh | grep TEST
-rw-rw----. 1 mysql mysql 4.5K Jan 21 20:24 TEST.frm
-rw-rw----. 1 mysql mysql 64K Jan 21 20:41 TEST.ibdtruncate 이후 TEST.ibd 용량이 64k로 줄어든 걸 확인할 수 있다.
2. optimize
이제부턴 데이터를 선별적으로 지워야하는 경우에 선택하는 방법이다.
먼저 데이터를 delete 하고 테이블을 optimize 하는 방법인데 일단 쓰긴했지만 별로 추천하고 싶지는 않다.
왜냐하면 optimize는 테이블의 크기가 MB나 수 GB 단위 정도의 귀여운 수준에선 사용에 전혀 문제가 없지만 수백 GB, TB 단위가 되면 수행 속도가 말도 안 되게 느려질 수 있기 때문이다.
그래도 방법을 보자면 이렇다.
위에서는 A라는 문자열만 600MB 쯤 넣었는데 이번엔 B라는 문자열을 600MB 추가 했다.
[root@localhost BigDB]# ls -lh | grep TEST
-rw-rw----. 1 mysql mysql 4.5K Jan 21 20:24 TEST.frm
-rw-rw----. 1 mysql mysql 1.2G Jan 21 20:46 TEST.ibdTEST.ibd 용량이 두 배가 되어 1.2GB다.
여기서 B를 모두 삭제하고 A만 남기기 위해
MariaDB [BigDB]> DELETE FROM TEST WHERE TEXT LIKE '%B';이렇게 delete 쿼리를 날린다. 실제 환경에선 아마 날짜 같은 컬럼을 기준으로 지울 것이다.
이후 TEST.ibd 를 확인해도 여전히 1.2G 인걸 볼 수 있는데 이때
MariaDB [BigDB]> OPTIMIZE TABLE TEST;이렇게 optimize를 수행하면
[root@localhost BigDB]# ls -lh | grep TEST
-rw-rw----. 1 mysql mysql 4.5K Jan 21 20:50 TEST.frm
-rw-rw----. 1 mysql mysql 668M Jan 21 20:51 TEST.ibddelete 한 만큼 ibd 파일 용량이 줄어든 걸 볼 수 있다. 내 경우엔 1.2GB 테이블에 대해 4초 정도 걸리긴 했는데 이건 테이블 구조가 너무 간단하기도 하고 인덱싱도 전혀되어 있지 않았기 때문에 빨랐다고 봐야할 거 같다.
3. 신규 테이블 생성
남길 데이터만 선별해서 신규 테이블을 create하고 기존 테이블을 drop하는 방법이다.
예를 들어 TEST 테이블에서 A 텍스트를 포함하는 데이터만 TEST_TMP 라는 신규 테이블에 생성한다.
MariaDB [BigDB]> create table TEST_TMP select * from TEST where TEXT like '%A';
Query OK, 512000 rows affected (24.573 sec)
Records: 512000 Duplicates: 0 Warnings: 0
이것도 보면 알겠지만 고작 600MB 정도 create 하고 insert 하는데 24초가 걸리는 걸 볼 수 있다. 단순 계산하면 600GB 작업하려면 6시간 넘게 걸린다는 건데 db 작업이라는 게 만만하지가 않은 거 같다.
테이블을 생성했으면 원래 테이블의 이름을 변경한다.
MariaDB [BigDB]> rename table TEST to TEST_bak;
Query OK, 0 rows affected (0.002 sec)
그리고 새로 만든 테이블의 이름을 원래 테이블 이름으로 변경한다. 혹시 원래 테이블에 인덱스가 있었으면 생성 작업을 해준다.
MariaDB [BigDB]> rename table TEST_TMP to TEST;
Query OK, 0 rows affected (0.002 sec)
백업한 테이블을 drop 한다.
MariaDB [BigDB]> drop table TEST_bak;
Query OK, 0 rows affected (0.004 sec)
이제 데이터베이스에는 새롭게 만든 테이블만 남게되어서 디스크 용량도 그만큼 줄어든다.
[root@localhost BigDB]# ls -lh | grep TEST
-rw-rw----. 1 mysql mysql 4.5K Jan 22 23:04 TEST.frm
-rw-rw----. 1 mysql mysql 588M Jan 22 23:04 TEST.ibd
눈치챘겠지만 이 방법은 디스크에 신규 테이블을 생성할 정도의 여력이 있을 때 쓸 수 있다는 한계가 있다.
주의 사항
- DB 작업을 할 땐 연결되어 있는 서비스는 종료해야 된다.
- 오래 걸릴 수 있다는 걸 미리 감안해야 한다. 당장 이거 예시 만들려고 1.2GB 데이터 insert하는 것만도 5분씩 걸리는 마당에 TB 단위 데이터 작업하려면 시간 각오는 해야된다. 이래서 truncate 생각이 절로 날 수밖에 없다.
- 작업 전 CPU와 메모리 상태가 문제 없는지 체크한다.
- 되도록 디스크 여유가 있을 때 미리 미리 작업한다.