Temporary table 및 Temporary tablespace 개념 및 관리

해당 페이지에서는 MySQL 및 MariaDB 에서 사용하는 Temporary table 및 Temporary tablespace 에 대하여 다룬다.

 

해당 내용은 아래의 공식 문서를 참조하여 기재한다.

 

https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html#:~:text=The%20temporary%20tablespace%20receives%20a,if%20the%20server%20halts%20unexpectedly.

 

https://www.percona.com/blog/2019/07/17/mysql-disk-space-exhaustion-for-implicit-temporary-tables/

 

또한 이전 게시글중에서 아래의 개시글을 먼저 읽고 오면 좋다.

 

2021.03.30 - [MySQL/아키텍처 및 내부 구조] - MySQL / MariaDB 기본 아키텍처 (Data - InnoDB)

 

 

 Temporary table 및 Temporary tablespace 사용의 의의

 

 

Temporary table (임시 태이블) 은 사용자가 프로시저 등에 내부적으로 임시 구성 테이블등을 만들기 위해서 쓰는 것 이외에 엔진 내부적으로 작업을 수행할때도 쓰인다. 이러한 작업에 쓰이는 테이블을 Implicit temporary tables (묵시적 임시 테이블) 이라고 한다. 이러한 묵시적 임시 테이블과 연관되는 대표적인 작업은 아래와 같다.

 

- Group by

- Order by

- Distinct

-  merge 를 사용 할 수 없는 테이블들 간의 Union

- 다수 테이블을 일괄 update

 

이렇게 임시 테이블은 다양한 내부작업에서 쓰이는데, 이 때, 임시 테이블들 또한 엔진 파라미터에 근거하여 지정된 임시테이블 공간을 구성하여 사용하게 된다. 이 때, 이 작업에서 쓰이는 파라미터는 tmp_table_size (유저가 임의로 만든 임시 테이블 이외의 테이블들에  할당하는 임시 테이블 크기) max_heap_table_size (유저가 임의로 만든 임시 테이블에 할당하는 임시 테이블 크기) 다.

 

만일 운영 하는 데이터베이스에서 위의 작업들이 유난히 느리게 처리 될 시, 위의 파라미터들을 조절하여 작업속도 향상을 고려 해 볼 수 있다. 물론 이 값은 할당된 메모리 자원이 한정되어 있기에 무한적으로 늘릴수는 없다.

 

즉, 메모리로 처리되는 임시테이블 작업이외에 디스크에서도 임시 테이블을 만들어서 사용하게 된다.

이 때, 디스크에서 임시테이블이 만들어져서 처리 시, 각 임시테이블 마다 임의의 파일이 만들어 지며 이 파일들은

Temporary Tablespace 에 저장되게 된다.

 

 

Temporary tablespace 관리

 

 

보통 Temporary tablespace 는 아래와 같은 파라미터 설정으로 구성 및 크기 또한 조절이 된다.

 

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

 

위의 기본 파라미터를 짐작 할 수 있다면, Temporary tablespace 는 autoextend 로 인하여 상한선 없이 무제한으로 증가하게 되는 것을 알 수 있다. 그렇기에 이러한 증가가 지속되면 실제 데이터를 보관할 공간이 부족해지면서 스토리지가 full 이 되는 문제가 생기게 된다. 이 때, 실제 Temp tablespace 가 얼마나 사용되는지는 아래의 쿼리로 확인 해 볼 수 있다.

 

mysql> 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'\G
*************************** 1. row ***************************
      FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 12582912
 TotalSizeBytes: 12582912
      DATA_FREE: 6291456
   MAXIMUM_SIZE: NULL

 

이 때, 이를 조절 할 수 있는 방법은 두가지가 있다.

 

- 파라미터 조절

 

아래와 같이 max 로 temp 영역이 쓰일 수 있는 공간을 조절 할 수 있다. 하지만 이는 임시 테이블에 대한 부하가 있는 데이터베이스라면 문제를 일으 킬 수 있다. 또한 다른 게시글에서 언급하겠지만, AWS RDS 및 Aurora 에서는 이를 조절 할 수가 없다.

 

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

 

- 데이터베이스 재기동

 

MySQL 및 MariaDB 는 데이터베이스를 재기동 함으로써 매우 크게늘어난 Temp tablespace 를 0으로 돌릴 수 있다.

AWS RDS 및 Aurora 에서는 위의 파라미터 조절이 안되기 때문에 Temp tablespace 가 계속 증가되는 문제를 해결 할 수 있는 유일한 방법은 재기동 뿐이다.

 

또한, 재기동 이후에 임시 테이블을 사용하는 작업이 다시 시작되면 Temp tablespace 는 위의 파라미터 설정에 따라 돌아가게 되고 이에 따라 다시 스토리지가 부족해지는 문제가 발생 할 수 있다.

 

이러한 문제가 지속된다면 사용자 측면에서 사용자가 임의로 만드는 임시테이블 사용을 줄이는 등으로 임시 테이블들을 사용하는 작업들을 가능한 줄이는 방향을 고려 해 봐야한다.