[MySQL - 임시테이블(tmpdir) FULL 이슈]

■ 발생 이슈

※ 최근 운영 중인 DB(Maria)서버에서 tmpdir 디스크가 100% 되어 DB가 내려갔습니다.

[root@jhtest ~]# df -h
Filesystem                        Size  Used Avail Use% Mounted on devtmpfs                           32G     0   32G   0% /dev
32G  4.0K   32G   1% /dev/shm
tmpfs      
tmpfs                              32G  3.1G   29G  10% /run
tmpfs                              32G     0   32G   0% /sys/
/dev/xvda                          48G    0G   48G 100% /


[root@jhtest tmpdir]# ll
total 23461140
-rw-rw---- 1 mysql mysql     9322496 Oct 13 16:10 #sql_a4a_11.MAD
-rw-rw---- 1 mysql mysql      245760 Oct 13 16:10 #sql_a4a_11.MAI
-rw-rw---- 1 mysql mysql 11635564544 Oct 13 15:22 #sql_a4a_14.MAD
-rw-rw---- 1 mysql mysql        8192 Oct 13 14:57 #sql_a4a_14.MAI
-rw-rw---- 1 mysql mysql  5817786368 Oct 13 15:22 #sql_a4a_15.MAD
-rw-rw---- 1 mysql mysql        8192 Oct 13 14:57 #sql_a4a_15.MAI
-rw-rw---- 1 mysql mysql  5817786368 Oct 13 15:26 #sql_a4a_16.MAD
-rw-rw---- 1 mysql mysql   761577472 Oct 13 16:10 #sql_a4a_16.MAI
-rw-rw---- 1 mysql mysql    12836864 Oct 13 16:10 #sql_a4a_19.MAD
-rw-rw---- 1 mysql mysql        8192 Oct 13 16:10 #sql_a4a_19.MAI


[root@jhtest tmpdir]# du -sh
23G    .


## error-log
2022-10-13 14:56:02 139858034087680 [Warning] Warning: Enabling keys got errno 188 on .CTE, retrying
2022-10-13 15:27:39 139858540619520 [ERROR] mysqld: Disk full
(/sw/app/log/tmpdir/#sql_a4a_2.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
2022-10-13 15:27:39 139859084064512 [ERROR] mysqld: Disk full
(/sw/app/log/tmpdir/#sql_a4a_0.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
2022-10-13 15:27:39 139859086792448 [ERROR] mysqld: Disk full
(/sw/app/log/tmpdir/#sql_a4a_21.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
(생략)
2022-10-13 16:11:25 140542464739136 [ERROR] mysqld: Can't create/write to file '/tmpdir/ibUlFcEm' (Errcode: 28 "No space left on device")
2022-10-13 16:11:25 140542464739136 [ERROR] InnoDB: Unable to create temporary file; errno: 28
2022-10-13 16:11:25 140542464739136 [ERROR] mysqld: Can't create/write to file '/tmpdir/ibFFGWRk' (Errcode: 28 "No space left on device")
2022-10-13 16:11:25 140542464739136 [ERROR] InnoDB: Unable to create temporary file; errno: 28 2022-10-13 16:11:25 140542464739136 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2022-10-13 16:11:25 140542464739136 [Note] InnoDB: Starting shutdown...
2022-10-13 16:11:25 140542464739136 [ERROR] Plugin 'InnoDB' init function returned error.
2022-10-13 16:11:25 140542464739136 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022-10-13 16:11:25 140542464739136 [Note] Plugin 'FEEDBACK' is disabled. 221013 16:11:25 server_audit: MariaDB Audit Plugin version 1.4.3 STARTED.
2022-10-13 16:11:25 140542464739136 [ERROR] Unknown/unsupported storage engine: InnoDB 2022-10-13 16:11:25 140542464739136 [ERROR] Aborting

 

■ 발생 이유

아래의 이유로 tmpdir에 임시 테이블을 사용하는데 해당 경로(디스크)가 full차서 더이상 쿼리 실행이 불가하여 DB가 down되었다.

tmpdir - mysql 쿼리 실행 중 join 이나 union 등 테이블을 합쳐지거나 스토리지 엔진으로부터 받아온 레코드를 order by 나  group by 할 때 혹은 alter 테이블 명령어 수행을 위해 내부적인 임시 테이블을 사용합니다.

작업을 해야하는 경우가 있는데 데이터가 많거나 해서 메모리에 다 올려서 작업을 못할 경우 아래 설정된 임시 디스크를 사용하게 됩니다.

일반적으로 임시 테이블은 처음엔 메모리에 생성됐다가 설정값 (max_heap_table_size 혹은 tmp_table_size 값)을 초과하는 경우 디스크로 옮겨지며 이와 같은 내부적인 임시테이블은 쿼리 수행이 완료되면 자동으로 삭제됩니다.

 

■ 조치 방법

1. show processlist 시 temp 디스크 사용하는 thread 확인 후 정리

1) copying to tmp table

정렬하는 데이터 크기가 tmp_table_size, max_heap_table_size 보다 작아서 memory 내에 tmp 임시테이블 생성

2) copying to tmp disk

정렬하는 데이터 크기가 tmp_table_size, max_heap_table_size 보다 커져서 memory가 아닌 디스크에 tmp 임시 테이블 생성

 

2.temp 영역을 사용하는 쿼리는 hang 상태가 되기 때문에 temp 영역을 증설하거나 temp를 많이 사용하는 쿼리(slow query)를 정리해야합니다.

 

3.temp 영역 여러개 지정

tmpdir 설정에  ' : ' 로 구분하여 복수로 지정가능합니다.

[root@localhost ~]# vi /etc/my.cnf

[mysqld]
datadir=/data/data
socket=/tmp/mysql.sock
lower_case_table_names=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
performance_schema = ON


tmpdir=/tmp:/data/data



MariaDB [(none)]> show variables like 'tmpdir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tmpdir        | /tmp:/data/data |
+---------------+-----------------+
1 row in set (0.00 sec)

위와 같이 지정시 지정된 tmpdir를 round-robin 식으로 사용할 수 있습니다.

 

4.파라미터 수정

메모리의 여유를 고려하여 max_heap_table_size,  tmp_table_size 파라미터 수정

내부 메모리 임시 테이블에 MEMORY 스토리지 엔진을 사용할 때 실제 크기 제한은 tmp_table_size와 max_heap_table_size 중 더 작은 크기입니다.

고급 GROUP BY 쿼리를 많이 수행하고 메모리가 많은 경우 tmp_table_size(내부 메모리 임시 테이블에 MEMORY 스토리지 엔진을 사용할 경우 필요한 경우 max_heap_table_size) 값을 늘립니다.

 

 

참고

https://sarc.io/index.php/mariadb/1058-mariadb-tmp-full

MariaDB tmp full 발생 시 조치방법