[MySQL - Temporary Tablespace]

메모리 부족 이슈 발생시 볼만하다는 소리를 듣고 찾아보게 되었습니다.

temporary tablespace는 사용자가 임의적으로 create ~문 등을 사용해 임시 테이블을 생성할 때 undo 로그가 저장된다고 합니다.

 

 

■ Temp tablespace

ibtmp1  : global temporary tablespace 공간은 사용자가 생성한 임시 테이블의 롤백 세그먼트를 저장합니다.

innodb_temp_tablespace_dir : SESSION LEVEL로 ON-DISK 내부 임시테이블이 생성되는 공간 [사용자가 만든 임시 테이블 & 옵티마이저가 사용하는 임시 테이블 ]

MariaDB [(none)]> show variables like '%temp%';
+---------------------------------+-----------------------+
| Variable_name                   | Value                 |
+---------------------------------+-----------------------+
| aria_used_for_temp_tables       | ON                    |
| connect_use_tempfile            | AUTO                  |
| innodb_encrypt_temporary_tables | OFF                   |
| innodb_temp_data_file_path      | ibtmp1:12M:autoextend |                     => 12MB씩 자동 증가된다.
| mysql56_temporal_format         | ON                    |
+---------------------------------+-----------------------+




## 데이터 파일 디렉토리에 저장된다.
[root@kovea-db1 data]# ll
total 373284
-rw-r----- 1 mysql mysql  14262272 Oct 24 11:04 aria_log.00000001
-rw-r----- 1 mysql mysql        52 Oct 24 11:04 aria_log_control
-rw-rw---- 1 mysql mysql   7199789 Feb 23  2022 ib_buffer_pool
-rw-r----- 1 mysql mysql 348127232 Oct 24 11:04 ibdata1
-rw-rw---- 1 mysql mysql  12582912 Feb 23  2022 ibtmp1
drwx------ 2 mysql mysql      4096 Jun 15 08:31 mysql
drwx------ 2 mysql mysql        20 Jan 11  2022 performance_schema
drwx------ 2 mysql mysql       114 Jan 11  2022 test
-rw-r----- 1 mysql mysql       625 Jan 11  2022 xtrabackup_info



[root@jhtest data]# du -sh ./ibtmp1
12M    ./ibtmp1




mysql> show variables like '%innodb_temp_tablespaces_dir%';
+-----------------------------+-----------------+
| Variable_name               | Value           |
+-----------------------------+-----------------+
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
+-----------------------------+-----------------+

 

 

■ ibtmp1 ( global temporary tablespace )

GLOBAL TEMPORARY TABLESPACE로 사용자가 생성한 임시 테이블이 생성된다.

temporary tablespace의 데이터파일 사이즈는 12M로 시작하여 필요시 계속 늘어난다. 

사용하는만큼 늘어나며 이후 사용하지 않는 상황에서도 해당 공간을 반환하지 않고 점유합니다. 반환을 위해서는 Restart가 필요합니다.

 

 

## Temporary 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
1 row in set (0.00 sec)

 

 

## Max 제한 설정

temp table 을 생성을 유발하는 long query 로 인해 기하급수적으로 사이즈가 늘게 되면 Disk Full 장애를 유발할 수 있습니다.

쿼리가 수행 도중에 MAX 에 도달하게 되면 table is full 에러를 내면서 쿼리는 실패됩니다. 하지만 무제한으로 tablespace 를 제공할 수는 없기 때문에 적절한 사이즈를 정해야 합니다.

뒤에 max값을 적절한 수치로 설정하여 disk full장애를 예방할 수 있습니다.

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

 

 

 

 

참고

 

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=sory1008&logNo=221381987533

[MySQL Internals] Temporary Tablespace