[mysql] Temporary Tablespace 관리

개요

압축되지 않고 사용자가 생성한 임시 테이블과 디스크에 생성되는 내부적인 임시 테이블들이 shared temporary tablespace 에 생성된다. innodb_temp_data_file_path 옵션으로 상대 경로, 이름, 사이즈, 데이터파일의 속성을 설정할 수 있다. 아무것도 설정하지 않으면 기본적으로 innodb_data_home_dir 경로에 ibtmp1:12M:authextend 속성으로 생성된다.

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+

 

Temporary Tablespace 의 데이터파일 관리

Temporary Tablespace 의 데이터파일 사이즈는 기본적으로 생성된 12M에서 시작하여 필요한 만큼의 Tablespace 만큼 계속 늘어난다. 다 쓰고 제거된 temp 공간도 테이블스페이스 내에서 재사용될 뿐 물리적으로 차지하는 공간은 줄어들지 않는다.

 

사용중인 용량 확인 방법

INFORMATION_SCHEMA.FILES 을 통해서 Temporary Tablespace의 용량을 확인한다.

 SELECT FILE_NAME, 
        TABLESPACE_NAME, 
        ENGINE, 
        INITIAL_SIZE, 
        TOTAL_EXTENTS*EXTENT_SIZE AS TotalSizeBytes, 
       	DATA_FREE, 
       	MAXIMUM_SIZE 
FROM 	INFORMATION_SCHEMA.FILES 
WHERE 	TABLESPACE_NAME = 'innodb_temporary';

temp table을 용량을 차지하는 long query로 인해 기하급수적으로 사이즈가 늘게 되면 Disk Full 장애가 발생한다.

 

Temporary Tablespace 의 줄이기

Temporary Tablespace 를 줄이기 위한 방법은 Mysql 서버 재시작하여 기본 설정(  innodb_temp_data_file_path )으로 Tablespace를 재생성하도록 하는 방법 뿐이다. 

Temporary Tablespace  용량에 최대값을 설정하지 않으면 무한정 증가하기 때문에 적절한 값으로 설정해야 한다.

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

퀴리 실행 중에 temporary tablespace 의 크기가 최대값에 도달하는 table is full가 발생하며 쿼리 실행이 실패한다.