--1. tmp란
스토리지 엔진으로부터 받아온 레코드를 order by 나 group by 할 때
혹은 alter 테이블 명령어 수행을 위해 내부적인 임시 테이블을 사용함.
일반적으로 임시 테이블은 처음엔 메모리에 생성됐다가 설정값 (max_heap_table_size 혹은 tmp_table_size 값)을 초과하는 경우 디스크로 옮겨짐.
이와 같은 내부적인 임시테이블은 쿼리 수행이 완료되면 자동으로 삭제됨
Oracle의 PGA와 같은 역할
--2. 관련 설정값
-. max_heap_table_size
-. tmp_table_size
-. tmp_dir
--3. 이슈사항
180119 16:14:48 [Warning] mysqld: Disk is full writing '/temp001/masvc01/TEST/#sql_5a37_3.MAD' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
180119 16:14:48 [Warning] mysqld: Retry in 60 secs. Message reprinted in 600 secs
180119 16:14:49 [ERROR] mysqld: Disk full (/temp001/masvc01/TEST/#sql_5a37_18.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
180119 16:14:55 [Warning] mysqld: Disk is full writing '/temp001/masvc01/TEST/#sql_5a37_3.MAD' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
180119 16:14:55 [Warning] mysqld: Retry in 60 secs. Message reprinted in 600 secs
180119 16:14:55 [Warning] Sort aborted, host: 10.185.219.196, user: lms_app, thread: 2439718, query: SELECT a.menu_id
, a.site_code
위 로그 처럼 tmp disk가 full 발생하면 ,
temp 영역을 사용하는 쿼리는 hang 상태가 되기 때문에 temp 영역을 증설하거나
temp를 많이 사용하는 쿼리를 정리해야함
--4. 해결방법
-. temp 디스크 사용하는 thread 확인 후 정리
MariaDB [(none)]> show processlist;
+--------+--------+---------------------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+--------+---------------------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 638645 | | localhost | | Query | 0 | NULL | show processlist |
| 652552 | | localhost | | Query | 29 | Copying to tmp disk | NULL |
| 652676 | | localhost | | Query | 1 | Copying to tmp table |
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 임시 테이블 생성
copying to tmp table 상태인 쿼리를 확인하고 kill 조치
kill '652576';
-. temp 영역 여러개 지정
my.cnf에
[mysqld] port=3310 socket=/engn001/masvc01/OLDDB/mysql/mysql.sock basedir=/engn001/masvc01/OLDDB/mysql datadir=/data001/masvc01/OLDDB #tmpdir=/data001/masvc01/OLDDB/tmp tmpdir=/temp001/masvc01/OLDDB:/data001/masvc01/OLDDB
처럼 tmpdir 설정에 ' : ' 로 구분하여 복수로 지정가능.
위와 같이 지정시 지정된 tmpdir를 round-robin 식으로 사용하게 됨
-. temp 파일시스템 증설
MariaDB [(none)]> show variables like 'tmpdir';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| tmpdir | /temp001/masvc01/TEST |
+---------------+------------------------+
DB에서 사용중인 temp 영역을 조회 및 OS상에서 파일시스템 조회하여 해당 영역 증설