mariadb copying to tmp table

Intro

마리아 DB 환경의 서비스를 운영하면서 copying to tmp table 오류(?) 를 직면하게 되어 처리한 내용을 정리합니다.

copy to tmp table??

스토리지 엔진으로부터 받아온 레코드를 order by 나 group by 할 때
혹은 alter 테이블 명령어 수행을 위해 내부적인 임시 테이블을 사용함.
일반적으로 임시 테이블은 처음엔 메모리에 생성됐다가 설정값 (max_heap_table_size 혹은 tmp_table_size 값)을 초과하는 경우 디스크로 옮겨짐.
이와 같은 내부적인 임시테이블은 쿼리 수행이 완료되면 자동으로 삭제됨
Oracle의 PGA와 같은 역할

1. copy to tmp table 확인

• 현재 수행중인 쿼리를 실행 하여 STATE 컬럼내용 중 확인한다.

SELECT * 
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND = 'Query';

2. EXPLAIN으로 실행계획 확인 및 튜닝

• 1)에서 조회된 쿼리 중 copy to tmp table 발생 쿼리를 확인하고 튜닝 함.

EXPLAIN
SELECT A.BK_NO, 
		 C.BK_STATUS, 
		 A.LST_GATE_IN_RESV_DT_FROM , 
		 A.LST_GATE_IN_RESV_DT_TO, 
		 A.LST_DOCK_IN_RESV_DT_FROM, 
		 A.LST_DOCK_IN_RESV_DT_TO, 
		 A.LST_UP_DOWN_RESV_DT_FROM, 
		 A.LST_UP_DOWN_RESV_DT_TO, 
		 A.LST_DOCK_OUT_RESV_DT_FROM, 
		 A.LST_DOCK_OUT_RESV_DT_TO
	FROM T_BOOKING_TASK A
	RIGHT JOIN T_BOOKING_DOCK B
      ON (B.CNTR_CD = A.CNTR_CD  
			 AND B.BK_NO = A.BK_NO 
			 AND B.DOCK_CD =  NAME_CONST('I_DOCK_CD',_utf8'1-11-1' 
			 COLLATE 'utf8_general_ci')
			)
	RIGHT JOIN T_BOOKING_TASK_PROC C
      ON (C.CNTR_CD = A.CNTR_CD  
			 AND C.BK_NO = A.BK_NO 
			 AND C.BK_STATUS in ('20','30','40','50','60') 
			)
	...(생략)

3. memory size 증가

• tmp table에 할당되어있는 memory size가 적을 경우 증가시켜 줍니다.
• root 접속 후 mariadb의 설정파일인 /etc/my.cnf 에서 [mysqld] 아래에 설정 정보 입력

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

4. 서비스 재기동

• 3)을 적용하기 위해서는 서비스를 재기동 해야 하며 아래와 같은 순서로 서비스 재기동을 수행 합니다 (DB로의 유입을 막기 위함).

1. Web 중지
2. WAS 중지
3. DB 중지
4. DB 기동
5. WAS 중지
6. Web 중지

5. memory size 증가 확인

# 확인 방법
SHOW VARIABLES LIKE '%_table_size';
=> tmp_table_size = 64M
max_heap_table_size = 64M 

6. 기타 관련 내용

• Variable Name Commets
• Created_tmp_disk_tables : 디스크에 생성된 temp table 횟수
• Created_tmp_files : 생성된 temp file 횟수
• Created_tmp_tables : 메모리와 디스크에 생성된 temp table 횟수

SHOW VARIABLES LIKE 'join_buffer_size';
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW STATUS WHERE variable_name LIKE '%tmp%'