MySQL/MariaDB Temp table and Temp file 모니터링

MySQL/MariaDB Temp table and Temp file 모니터링

 

·      Version : MySQL 5.7, Docker

 

MySQL/MariaDB에서 Temp table 및 Temp file 모니터링하는 방법에 대해서 알아본다. 데이터베이스 서브 시스템에서 상대적으로 느린 부분이 디스크이다. 임시 테이블 및 임시 파일이 디스크에 기록될때 데이터베이스의 성능 저하가 발생할 수 있기기 때문에 지속적인 모니터링을 통해서 디스크에 생성되는 임시 테이블 및 파일이 높지 않은지 살펴보아야 한다.

아래 스크립트는 temp table 및 temp file의 지표를 나타낸다.

Show status where variable_name like ‘%tmp %’

 

 

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

 

위 지표에서 성능 저하가 발생하는 부분은 Created_tmp_disk_tables 항목이다. 이 항목의 수치가 높다면 현재 쿼리를 처리하기 위해 디스크를 많이 사용하고 있다는 뜻이다. 일반적으로 아래와 같은 쿼리는 임시 테이블을 생성한다.

·       OEDER BY 및 GROUP BY에 명시된 컬림이 다른 쿼리

·       한 개의 쿼리에 DISTINCT와 ORDER BY가 동시에 존재하는 경우

·       DISTINCT가 인데스러 처리되지 못하는 쿼리

·       UNION ALL이 사용된 쿼리

·       실행계획에 select_type이 UNION RESILT가 나타나는 경우

·       실행계획에 select_type이 DERIVDE 인 쿼리

 

이 외에도 임시 테이블에 저장되는 레코드의 크기가 tmp_table_size 또는 max_heap_table_size보다 큰 경우 디스크에 임시 테이블을 생성하여 저장한다. 디스크 사용을 줄이기 위해 tmp_table_size 및 max_heap_table_size의 시스템 변수 값을 증가시키면 MySQL 서버가 사용할 수 있는 메모리 공간이 줄어들기 때문에 주의해야 한다. 아래 스크립트는 tmp_table_size 및 max_heap_table_size를 확인하는 스크립트 이다.

show variables where variable_name in (‘tmp_table_size’, ‘max_heap_table_size’)

 

 

SELECT 절에서 사용되는 컬럼에 따라서도 디스크에 임시 테이블이 생성되는데, TEXT, BLOB 형식의 컬럼이 포함되는 경우 MEMORY 스토리지 엔진이 이 데이터 타입을 지원하지 않아 디스크에 임시 테이블을 생성한다.

 

[참고자료]

·       https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Created_tmp_disk_tables

 

 

2018-05-02 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, temp table, temp file 모니터링, 임시 테이블, 임시 파일 모니터링, tmp_table_size, max_heap_table_size, created_tmp_disk_tables, created_tmp_files, created_tmp_tables