MySQL 단편화 Fragmentation 의 이해와 해결

단편화, Fragmentation

단편화, fragmentation 는 기억 장치의 빈 공간 또는 자료가 여러 개의 조각으로 나뉘는 현상을 말한다.
이 현상은 기억장치의 사용 가능한 공간을 줄이거나, 읽기와 쓰기의 수행속도를 늦추는 문제점을 야기한다.
https://ko.wikipedia.org/wiki/%EB%8B%A8%ED%8E%B8%ED%99%94

 

 

MySQL InnoDB 에서는 PK 혹은 Clustering Index 를 통해 각 레코드를 관리하고 있습니다.

Secondary Index와 Clustering Index 는 B+Tree 의 구조를 가지며,

Clustering Index는 리프노드에 직접 레코드를 가지고 있고

Secondary Index 는 Clustering Index 의 주소를 갖고 있습니다.

 

이러한 Tree 구조의 인덱스는 검색 성능 향상 및 범위 검색을 용이하게 합니다.

 

다만, Root -> Branch -> Leaf 로 따라가야 하는 구성이나

Tree인 만큼 Left, Right Node 를 지정해야 하는 구조나

Balance 가 무너지지 않게 해야 하는 특징이 존재합니다.

 

 

id	name
1	test
2	test
3	test

위와 같은 테이블이 존재했다는 가정에서

2번의 레코드를 삭제한다면 테이블에서는 3번의 레코드를 위로 올려 1 -> 3 의 구조를 유지해야 할 것입니다.

 

하지만 InnoDB 에서는 바로 구조를 수정하지 않습니다.

삭제된 레코드는 해당 레코드에 대해 논리적인 삭제를 위해 삭제됐다는 marking 만 하게 됩니다.

InnoDB는 marking 된 것을 보고 '이 공간은 사용하면 안되겠다.'라는 판단을 하게 됩니다.

따라서 데이터가 삭제되며 구조를 다시 업데이트 해야되는 비용을 줄인 것입니다.

 

 

 

https://www.cubrid.com/blog/3822789

레코드를 삭제하게 되면 위의 그림과 같이 레코드 헤더에 삭제됐다는 표시를 통해 차후 이 영역을 재사용하게 합니다.

다만, 이 공간은 즉시 재사용되지 않고 optimize 이후 재사용 가능한 공간이 됩니다.

따라서 마킹된 공간이 많으면 많을수록 단편화 문제가 심화될 수 있습니다.

 

 

 

https://www.cubrid.com/blog/3822789

또한 MySQL 에서는 slot directory 와 비슷하게 page directory 라는 것을 사용하는데,

주황색으로 표시된 영역이 레코드, 회색으로 표시된 영역이 page directory 라고 한다면

두 영역이 맞닿을 때 꽉 찼다고 판단하고 새로운 페이지를 할당받게 됩니다.

 

그러므로 페이지 내에 모든 데이터를 삭제하고 어떤 데이터를 다시 삽입했을 때

어떤 페이지는 마킹만 되어있는 레코드들의 집합으로 이뤄질 수도 있고

이는 단편화 문제가 발생했다고 볼 수 있습니다.

 

 

 

단편화 확인

select 
	TABLE_SCHEMA as DATABASE,
	table_name as TABLE,
	round((data_length + index_length) / (1024 * 1024), 2) as total,
	round((data_free) / (1024 * 1024 ), 2) as datafree
from 
	information_schema.TABLES
where 
	TABLE_SCHEMA = 'DATABASE' 
and 
	TABLE_NAME = 'TABLE';

위와 같은 쿼리를 통해 단편화를 확인할 수 있습니다.

datafree 를 통해 free size 가 얼마나 되는지 확인할 수 있고,

혹은 OS 의 ibd 파일과 total 의 사이즈를 비교하여 간극만큼을 단편화로 판단할 수 있습니다.

 

 

 

테이블 재구성

단편화를 해결하기 위해 테이블을 재구성해야 합니다.

앞서 살펴보았던 공간들에 대해 마치 '디스크 조각 모음' 처럼 가용하게 바꿔줘야 합니다.

 

Optimize Table

OPTIMIZE TABLE 명령어는 테이블의 물리적인 저장 공간과 인덱스 데이터를 재구성하여 저장 공간을 줄이고 테이블 접근 시 I/O 효율성을 향상시킵니다. InnoDB 테이블의 경우, 이 명령어는 ALTER TABLE ... FORCE 로 매핑되며, 이는 테이블을 재구성하여 인덱스 통계를 업데이트하고 클러스터 인덱스의 미사용 공간을 반환합니다.

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

Optimize Table 은 테이블 재구성을 위한 명령어로 Online DML 또한 지원하고 있습니다.

그러나 Optimize Table 은 I/O 비용이 높고 테이블에 대한 잠금이 필요하므로 서비스 중에 사용하기는 어렵습니다.

따라서 서비스 중에 실행하는 것을 피하거나 pt-osc 와 같은 tool 을 사용할 수 있겠습니다.

 

끝!