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


SQL Server 임시 테이블 특성

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

임시 테이블은 tempdb 데이터베이스에 저장되었다가 더 이상 사용되지 않을 때 자동으로 삭제 된다. 그 외 기능은 일반 테이블과 비슷하다. 임시 테이블에는 전역과 로컬 두 유형이 있으며 로컬은 # 기호로 시작하며 전역은 ## 기호로 시작한다.

 

로컬 임시테이블은 현재 사용자의 연결에만 표시되고 사용자가 SQL Server 인스턴스와의 연결을 종료하면 삭제 된다.

 

전역은 테이블 생성 후 모든 사용자에게 표시되고 테이블을 참조하는 모든 사용자가 SQL Server인스턴스와 연결을 종료하면 삭제 된다. 즉 테이블을 생성한 사용자가 SQL Server와 연결을 끊어도 다른 사용자가 해당 테이블을 사용하고 있으면 다른 사용자의 사용이 종료될 때까지 기다렸다가 삭제된다.

 

실습을 통해서 전역 임시테이블의 특성에 대해서 살펴보자. 전역 임시 테이블은 테이블 이름 앞에 ## 기호로 식별되며 tempdb 데이터베이스에 저장된다.

CREATE TABLE ##tmpTest

(

    Value INT

)

 

 

테이블 생성 후 전역 임시 테이블은 모든 사용자와 모든 연결에서 볼 수 있다. 전역 임시테이블들은 수동으로 drop table 명령으로도 삭제 할 수 있다. 작성자 세션이 종료되어도 다른 세션에서 해당 테이블이 활성세션에 참조 되어 있는 경우 SQL Server 기준에서 마지막 T-SQL 문이 완료 될 때까지 삭제되지 않으며 참조 세션 문을 실행하는 동안 테이블은 새 연결에 사용할 수 없다.

 

SSMS에서 새쿼리 창을 실행하여 아래 스크립트(쿼리1)를 실행한다. 스크립트는 임시 테이블을 생성하고 데이터를 삽입한다.

--Query 1

 

IF (OBJECT_ID('tempdb..##tmpTest') IS NOT NULL)

    DROP TABLE ##tmpTest

 

CREATE TABLE ##tmpTest

(

    Value INT

)

 

DECLARE @i INT =1

 

WHILE (@i <= 1000000) --Value 1000000 is used to facilitate testing process, please be careful in choosing this value for your server to avoid overloading the server

BEGIN

 

    INSERT INTO ##tmpTest(Value) VALUES(@i)

 

    SET @i=@i+1

END

 

새 쿼리 창을 열고 아래 스크립트(쿼리2)를 실행 한다. 정상적으로 값이 조회되는 것을 확인할 수 있다.

--Query 2

 

SELECT TOP 1 * FROM ##tmpTest

 

 

새 쿼리창을 실행하여 아래 스크립트(쿼리3)를 실행 한다. 그리고 쿼리1의 세션을 종료 한다.

--Query 3

 

SELECT * FROM ##tmpTest

 

 

쿼리3이 실행되는 동안 쿼리2를 조회해 보면 정상적으로 값이 조회되는 것을 확인할 수 있다. 하지만 쿼리3의 작업이 완료되면 전역 임시테이블은 삭제되어 쿼리2에서 오류가 발생하는 것을 확인할 수 있다.

 

반복문의 경우 어떻게 작동하는지 알아본다. 쿼리 1을 실행하고 아래 스크립트(쿼리4)를 실행한다. 쿼리4가 진행 되는 동안 쿼리1의 세션을 종료 한다.

DECLARE @i INT =0,

        @val INT =0

 

WHILE (@i <= 1000000)

BEGIN

 

    SELECT @val=Value FROM ##tmpTest WHERE Value=@i

 

    SET @i=@i+1

 

    SELECT @val

END

 

 

쿼리4의 경우 하나의 T-SQL 명령문을 사용하지만 While 구문을 실행하면서 반복해서 테이블을 참조하는데 임시테이블이 삭제 후 참조하려고 했기 때문에 오류가 발생하였다.

 

이처럼 로컬 임시 테이블과 전역 임시 테이블은 비슷하지만 다른 특성이 있어 잘 파악하고 사용하여야 하며 쿼리를 개발 할 때 While 같은 경우 한 세션에서 실행되지만 임시 테이블이 삭제된 경우 반복 과정에서 오류가 날 수 있다는 점을 주의해야 한다.

 

 

[참고 자료]

 

 

2015-09-30 / 강성욱 / http://sqlmvp.kr

 

 

MS SQL, SQL Server, 임시 테이블, Temp table, 전역 임시 테이블, 로컬 임시 테이블, temp table, Global temporary table

+ Recent posts