TempTable 진단: 메모리와 디스크 내부 임시 테이블이 발생하는 원인
MySQL 내부 임시 테이블이 메모리에서 디스크로 전환되는 조건과 상태 지표, Performance Schema 기반 진단 절차를 정리한다.
GROUP BY, DISTINCT, ORDER BY, UNION, 파생 테이블과 CTE가 느려졌을 때 실행 계획에는 흔히 Using temporary가 나타난다. 운영자는 이어서 Created_tmp_disk_tables를 확인하고, 값이 크면 tmp_table_size를 올리는 방식으로 대응하기 쉽다. 그러나 이 접근은 내부 임시 테이블의 생성 원인, MySQL 8.0의 TempTable 엔진, 개별 테이블 한도와 인스턴스 전체 한도, 디스크 저장 방식의 차이를 한 지표로 뭉뚱그린다.
MySQL 8.0에서 “MEMORY 임시 테이블”이라는 표현은 특히 주의해야 한다. 기본 내부 메모리 임시 테이블 엔진은 일반적으로 MEMORY 스토리지 엔진이 아니라 TempTable이다. 또한 TempTable이 RAM 한도를 넘은 뒤 memory-mapped file을 사용하더라도 Created_tmp_disk_tables만으로 그 사용량을 완전하게 관찰하지 못할 수 있다. 따라서 정확한 진단은 어떤 SQL 연산이 내부 임시 테이블을 요구했는지, 어느 메모리·디스크 경로를 사용했는지, 어떤 쿼리가 누적 비용을 만들었는지를 단계별로 연결해야 한다.
이 글은 MySQL 8.0 이상을 기준으로 내부 임시 테이블의 실행 경로와 진단 절차를 설명한다. 사용자가 CREATE TEMPORARY TABLE로 만드는 명시적 임시 테이블과 MySQL 실행기가 만드는 내부 임시 테이블은 별개의 대상이다.
1. 먼저 구분해야 할 세 가지 임시 객체
운영 대화에서 “temp table”은 다음 세 대상을 섞어 가리키기 쉽다.
| 구분 | 생성 주체 | 대표 목적 | 주요 관측점 |
|---|---|---|---|
| 내부 임시 테이블 | MySQL optimizer/executor | 집계, 중복 제거, materialization, 정렬 보조 | EXPLAIN, status counter, statement digest, TempTable memory instrument |
| 명시적 임시 테이블 | 사용자 세션 | CREATE TEMPORARY TABLE로 중간 데이터 보관 |
SQL 정의, 세션 수명, 선택한 storage engine |
| 정렬용 임시 파일 | filesort 실행 경로 | 인덱스로 해결하지 못한 정렬 | Using filesort, sort status, 임시 공간 I/O |
Created_tmp_tables와 Created_tmp_disk_tables는 내부 임시 테이블 진단의 출발점이다. 사용자가 만든 CREATE TEMPORARY TABLE 수를 그대로 세는 카운터라고 해석하면 안 된다. Using filesort도 내부 임시 테이블과 같은 뜻이 아니다. 한 쿼리에서 두 작업이 함께 발생할 수 있지만, 정렬과 중간 행 저장은 서로 다른 실행 단계다.
따라서 이 글에서 말하는 “메모리 임시 테이블”은 특별한 설명이 없는 한 서버가 실행 중 자동으로 만든 내부 임시 테이블을 뜻한다.
2. 내부 임시 테이블이 필요한 이유
MySQL은 스토리지 엔진에서 읽은 행을 그대로 반환할 수 없을 때 중간 상태를 저장해야 한다. 대표적인 원인은 다음과 같다.
- 인덱스 순서로 처리할 수 없는
GROUP BY와 집계 상태 유지 DISTINCT또는UNION DISTINCT의 중복 제거- 파생 테이블, CTE, view의 materialization
- semi-join 전략의 duplicate weedout
- window function 계산을 위한 중간 결과
- 요청한 정렬 순서와 입력 순서가 달라 정렬 전후 행을 보관해야 하는 경우
ORDER BY와GROUP BY기준이 서로 달라 한 번의 인덱스 순서로 해결할 수 없는 경우
내부 임시 테이블은 그 자체가 오류가 아니다. 중간 결과를 저장하는 것이 가장 합리적인 실행 계획일 수 있다. 문제는 입력 행이 과도하게 많거나, 행 폭이 넓거나, 동시 실행 수가 많거나, 디스크 전환 후 임시 저장 공간이 병목이 될 때 발생한다.
flowchart TD
Q[SQL 실행] --> O[Optimizer가 실행 계획 선택]
O --> N{중간 결과 저장이 필요한가}
N -->|아니요| R[행을 직접 반환]
N -->|예| T[내부 임시 테이블 생성]
T --> E{internal_tmp_mem_storage_engine}
E -->|TempTable| M[TempTable 메모리 경로]
E -->|MEMORY| H[MEMORY 엔진 경로]
M --> P{개별·전역 한도와 행 특성}
H --> P
P -->|메모리 유지 가능| I[메모리에서 처리]
P -->|한도 초과 또는 메모리 부적합| D[디스크 저장 경로]
D --> F{TempTable 설정과 버전}
F -->|mmap 허용 범위| MM[memory-mapped file]
F -->|mmap 한도 초과·비활성| IN[InnoDB on-disk temp table]
I --> R
MM --> R
IN --> R
이 흐름에서 중요한 점은 “메모리 아니면 InnoDB 디스크”라는 이분법만으로는 부족하다는 것이다. TempTable은 인스턴스의 RAM을 사용하다가 설정에 따라 memory-mapped file을 거칠 수 있으며, 이후 InnoDB 기반 내부 디스크 임시 테이블을 사용할 수 있다.
3. TempTable과 MEMORY를 같은 말로 쓰면 안 되는 이유
3.1 기본 내부 메모리 엔진은 TempTable이다
MySQL 8.0에서는 internal_tmp_mem_storage_engine으로 내부 메모리 임시 테이블 엔진을 선택한다. 일반적인 기본값은 TempTable이다. MEMORY는 별도의 선택지이며, 사용자가 직접 만든 ENGINE=MEMORY 테이블과도 구분해야 한다.
TempTable은 내부 임시 작업에 맞게 설계되어 가변 길이 컬럼과 BLOB/TEXT 계열을 다루는 능력이 MEMORY 엔진보다 유연하다. 따라서 오래된 문서의 “TEXT나 BLOB이 있으면 무조건 디스크 임시 테이블”이라는 규칙을 MySQL 8.0 TempTable의 보편 규칙으로 그대로 적용하면 안 된다. 선택한 내부 엔진, 정확한 MySQL minor version, 결과 행 구조를 함께 확인해야 한다.
3.2 개별 임시 테이블 한도와 전체 TempTable 한도는 다르다
MySQL 8.0의 TempTable 경로에서는 적어도 다음 세 축을 분리해야 한다.
tmp_table_size: 최신 MySQL 8.0에서는 개별 내부 TempTable의 메모리 크기를 제한하는 핵심 값이다.temptable_max_ram: 인스턴스에서 TempTable이 사용할 수 있는 RAM 영역의 전역 한도다.temptable_max_mmap: TempTable이 memory-mapped file로 사용할 수 있는 전역 한도다.0이면 해당 경로를 사용하지 않도록 구성할 수 있다.
max_heap_table_size는 internal_tmp_mem_storage_engine=MEMORY일 때 tmp_table_size와 함께 작은 값이 실질적인 상한으로 작용하는 전통적인 해석이 중요하다. 그러나 TempTable을 사용하는 최신 기본 구성에서 max_heap_table_size 하나를 보고 내부 TempTable 한도를 결론 내리면 안 된다.
또한 전역 RAM 한도는 세션별 예약량이 아니다. TempTable은 필요할 때 메모리를 사용하며 여러 세션의 내부 임시 테이블이 같은 전역 한도를 경쟁한다. 개별 쿼리는 작더라도 동시에 수백 개가 실행되면 인스턴스 전체 경로가 memory-mapped file 또는 InnoDB 디스크 경로로 밀릴 수 있다.
3.3 mmap 사용은 상태 카운터 해석을 어렵게 한다
Created_tmp_disk_tables는 중요한 지표지만 TempTable의 모든 디스크 활동을 완전하게 표현한다고 가정해서는 안 된다. 특히 memory-mapped file에 저장된 TempTable 데이터를 이 카운터가 InnoDB on-disk 임시 테이블과 동일하게 세지 않을 수 있다.
따라서 다음 결론은 위험하다.
Created_tmp_disk_tables가 낮으므로 내부 임시 테이블이 디스크를 거의 사용하지 않는다.
올바른 판단에는 TempTable memory instrument, 임시 디렉터리와 로컬 저장 공간의 사용량, statement digest, 쿼리 지연을 함께 봐야 한다.
4. 현재 설정과 전역 카운터 확인
다음 SQL은 MySQL 버전, 내부 임시 테이블 엔진, 관련 한도와 누적 카운터를 한 번에 확인한다. performance_schema.global_variables와 global_status를 사용하므로 정렬과 컬럼 선택이 명확하다.
SELECT VERSION() AS mysql_version;
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
'internal_tmp_mem_storage_engine',
'tmp_table_size',
'max_heap_table_size',
'temptable_max_ram',
'temptable_max_mmap'
)
ORDER BY VARIABLE_NAME;
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
'Created_tmp_tables',
'Created_tmp_disk_tables'
)
ORDER BY VARIABLE_NAME;
실행 결과(MySQL 8.0.x):
mysql> SELECT VERSION() AS mysql_version;
+---------------+
| mysql_version |
+---------------+
| 8.0.46 |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
-> FROM performance_schema.global_variables
-> WHERE VARIABLE_NAME IN (
-> 'internal_tmp_mem_storage_engine',
-> 'tmp_table_size',
-> 'max_heap_table_size',
-> 'temptable_max_ram',
-> 'temptable_max_mmap'
-> )
-> ORDER BY VARIABLE_NAME;
+---------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------------------------+----------------+
| internal_tmp_mem_storage_engine | TempTable |
| max_heap_table_size | 16777216 |
| temptable_max_mmap | 1073741824 |
| temptable_max_ram | 1073741824 |
| tmp_table_size | 16777216 |
+---------------------------------+----------------+
5 rows in set (0.00 sec)
mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
-> FROM performance_schema.global_status
-> WHERE VARIABLE_NAME IN (
-> 'Created_tmp_tables',
-> 'Created_tmp_disk_tables'
-> )
-> ORDER BY VARIABLE_NAME;
+-------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------+----------------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
+-------------------------+----------------+
2 rows in set (0.00 sec)
이 결과를 읽을 때는 바이트 값을 즉시 “쿼리 하나가 예약하는 메모리”로 환산하지 않는다. 변수는 한도이고, status는 서버 시작 또는 초기화 이후의 누적 사건 수다. 실제 점유량과 발생 속도는 별도로 측정해야 한다.
카운터 비율을 단독 KPI로 쓰면 안 된다
다음 비율은 추세를 보는 보조 지표로는 사용할 수 있다.
Created_tmp_disk_tables / Created_tmp_tables
그러나 이 값을 임시 테이블 효율의 단일 KPI로 사용하면 다음 문제가 생긴다.
- 트래픽 증가와 SQL 구성 변화가 분모·분자에 동시에 영향을 준다.
- 작은 디스크 임시 테이블 수천 개와 매우 큰 디스크 임시 테이블 한 개의 비용을 구분하지 못한다.
- TempTable의 mmap 사용을 온전히 반영하지 못할 수 있다.
- 어느 digest가 증가량을 만들었는지 알려주지 않는다.
- 서버 재시작, failover, 수동 통계 초기화 뒤 비교 기준이 끊긴다.
따라서 절대 누적값보다 일정 구간의 delta, 초당 발생률, 쿼리 호출 수 대비 비율, 로컬 임시 공간과 지연 시간의 동시 변화를 관찰한다.
5. 실행 계획으로 생성 원인 찾기
다음 예제는 동일한 테이블에서 인덱스가 없는 식으로 그룹화하는 쿼리와 복합 인덱스 순서를 활용하는 쿼리를 비교한다. 소규모 검증 데이터이므로 지연 시간 자체가 아니라 Extra와 접근 경로의 차이를 본다.
SET NAMES utf8mb4;
DROP TABLE IF EXISTS tmp_diag_demo;
CREATE TABLE tmp_diag_demo (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
tenant_id BIGINT UNSIGNED NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL,
amount DECIMAL(12,2) NOT NULL,
note VARCHAR(200) NOT NULL,
PRIMARY KEY (id),
KEY ix_tenant_status (tenant_id, status)
) ENGINE=InnoDB;
INSERT INTO tmp_diag_demo
(tenant_id, status, created_at, amount, note)
VALUES
(10, 'READY', '2026-08-20 09:00:00', 120.00, 'alpha'),
(10, 'READY', '2026-08-20 10:00:00', 180.00, 'bravo'),
(10, 'DONE', '2026-08-21 09:00:00', 210.00, 'charlie'),
(20, 'READY', '2026-08-21 10:00:00', 130.00, 'delta'),
(20, 'RUNNING', '2026-08-22 09:00:00', 160.00, 'echo'),
(20, 'DONE', '2026-08-22 10:00:00', 190.00, 'foxtrot'),
(30, 'READY', '2026-08-23 09:00:00', 140.00, 'golf'),
(30, 'READY', '2026-08-23 10:00:00', 170.00, 'hotel'),
(30, 'DONE', '2026-08-24 09:00:00', 220.00, 'india'),
(40, 'READY', '2026-08-24 10:00:00', 150.00, 'juliet'),
(40, 'RUNNING', '2026-08-25 09:00:00', 200.00, 'kilo'),
(40, 'DONE', '2026-08-25 10:00:00', 240.00, 'lima');
ANALYZE TABLE tmp_diag_demo;
EXPLAIN
SELECT DATE(created_at) AS order_date,
SUM(amount) AS total_amount
FROM tmp_diag_demo
GROUP BY DATE(created_at)
ORDER BY total_amount DESC;
EXPLAIN
SELECT tenant_id, status, COUNT(*) AS row_count
FROM tmp_diag_demo
GROUP BY tenant_id, status;
실행 결과(MySQL 8.0.x):
준비 DDL/DML과 두 EXPLAIN 결과에서 핵심 열을 발췌했다.
mysql> CREATE TABLE tmp_diag_demo (... KEY ix_tenant_status (tenant_id, status)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tmp_diag_demo ...;
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE tmp_diag_demo;
+-------------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------------------+---------+----------+----------+
| mysql_tech_note.tmp_diag_demo | analyze | status | OK |
+-------------------------------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT DATE(created_at), SUM(amount)
-> FROM tmp_diag_demo
-> GROUP BY DATE(created_at)
-> ORDER BY SUM(amount) DESC;
+---------------+------+-------+---------------------------------+
| table | type | key | Extra |
+---------------+------+-------+---------------------------------+
| tmp_diag_demo | ALL | NULL | Using temporary; Using filesort |
+---------------+------+-------+---------------------------------+
mysql> EXPLAIN SELECT tenant_id, status, COUNT(*)
-> FROM tmp_diag_demo
-> GROUP BY tenant_id, status;
+---------------+-------+------------------+-------------+
| table | type | key | Extra |
+---------------+-------+------------------+-------------+
| tmp_diag_demo | index | ix_tenant_status | Using index |
+---------------+-------+------------------+-------------+
첫 번째 쿼리는 인덱스에 저장된 순서가 아닌 DATE(created_at) 식으로 그룹화하고 집계 결과로 다시 정렬한다. Using temporary와 Using filesort가 함께 나타날 수 있다. 두 번째 쿼리는 (tenant_id, status) 인덱스 순서로 읽으며 그룹화할 수 있다. 소규모 데이터에서 optimizer 선택은 달라질 수 있으므로 type, key, Extra를 실제 환경에서 확인한다.
여기서 중요한 진단 원칙은 다음과 같다.
Using temporary는 내부 임시 테이블 필요성을 보여주지만 메모리인지 디스크인지 직접 알려주지는 않는다.Using filesort는 인덱스 순서만으로 정렬을 끝내지 못했다는 뜻이며, 반드시 디스크 파일을 사용했다는 뜻은 아니다.Using temporary가 사라졌더라도 다른 materialization 단계가 TREE/JSON 실행 계획에 나타날 수 있다.- 실행 계획의
rows는 추정치다. 안전한 환경에서는EXPLAIN ANALYZE로 실제 처리 행 수와 loops를 확인한다.
임시 테이블을 자주 만드는 SQL 형태
실행 계획을 검토할 때 다음 형태를 우선 찾는다.
- 함수나 표현식으로 가공한 컬럼을
GROUP BY또는DISTINCT에 사용한다. ORDER BY가GROUP BY키 또는 인덱스 순서와 호환되지 않는다.- 넓은 파생 테이블이나 CTE가 merge되지 않고 materialize된다.
UNION DISTINCT가 필요 이상으로 중복 제거를 수행한다.- window function 이전 단계에서 불필요하게 많은 행과 컬럼을 유지한다.
SELECT *로 긴 문자열·JSON·LOB 컬럼을 중간 결과에 포함한다.- 필터가 늦게 적용되어 큰 중간 집합을 만든다.
튜닝의 첫 번째 목표는 임시 테이블 수치를 0으로 만드는 것이 아니라 중간 결과의 행 수와 행 폭, 생성 빈도를 줄이는 것이다.
6. 세션 delta로 특정 쿼리의 생성 여부 확인
전역 카운터는 모든 세션이 공유하므로 특정 SQL의 영향을 분리하기 어렵다. 재현 환경에서는 동일한 세션에서 실행 전후의 Created_tmp_tables와 Created_tmp_disk_tables를 비교할 수 있다.
SELECT CAST(VARIABLE_VALUE AS UNSIGNED)
INTO @tmp_before
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Created_tmp_tables';
SELECT CAST(VARIABLE_VALUE AS UNSIGNED)
INTO @disk_before
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Created_tmp_disk_tables';
SELECT DATE(created_at) AS order_date,
SUM(amount) AS total_amount
FROM tmp_diag_demo
GROUP BY DATE(created_at)
ORDER BY total_amount DESC;
SELECT CAST(VARIABLE_VALUE AS UNSIGNED)
INTO @tmp_after
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Created_tmp_tables';
SELECT CAST(VARIABLE_VALUE AS UNSIGNED)
INTO @disk_after
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Created_tmp_disk_tables';
SELECT @tmp_after - @tmp_before AS created_tmp_tables_delta,
@disk_after - @disk_before AS created_tmp_disk_tables_delta;
실행 결과(MySQL 8.0.x):
상태값을 변수에 저장하는 준비 출력은 줄이고, 집계 결과와 delta를 발췌했다.
mysql> SELECT DATE(created_at) AS order_date,
-> SUM(amount) AS total_amount
-> FROM tmp_diag_demo
-> GROUP BY DATE(created_at)
-> ORDER BY total_amount DESC;
+------------+--------------+
| order_date | total_amount |
+------------+--------------+
| 2026-08-25 | 440.00 |
| 2026-08-24 | 370.00 |
| 2026-08-22 | 350.00 |
| 2026-08-21 | 340.00 |
| 2026-08-23 | 310.00 |
| 2026-08-20 | 300.00 |
+------------+--------------+
6 rows in set (0.00 sec)
mysql> SELECT @tmp_after - @tmp_before AS created_tmp_tables_delta,
-> @disk_after - @disk_before AS created_tmp_disk_tables_delta;
+--------------------------+-------------------------------+
| created_tmp_tables_delta | created_tmp_disk_tables_delta |
+--------------------------+-------------------------------+
| 1 | 0 |
+--------------------------+-------------------------------+
1 row in set (0.00 sec)
이 방법은 테스트와 제한된 진단 세션에 적합하다. 운영 애플리케이션 세션에서는 connection pool이 세션을 재사용하고, 같은 세션에서 다른 문장이 끼어들 수 있어 순수 delta를 보장하기 어렵다. 또한 delta가 1, 0이라면 이번 실행에서 내부 임시 테이블이 만들어졌지만 InnoDB on-disk 전환 카운터는 늘지 않았다는 뜻이지, TempTable이 로컬 파일을 전혀 사용하지 않았음을 보증하는 것은 아니다.
실제 디스크 전환을 재현하려고 tmp_table_size를 극단적으로 낮추고 wide row와 GROUP BY를 조합하면 버전, row width, TempTable 내부 구현에 따라 결과가 불안정해질 수 있다. 운영 문서와 자동 검증에서는 무리한 spill 재현보다 설정, 실행 계획, session delta, TempTable memory instrument를 결합하는 안정적인 방법을 우선한다.
7. statement digest에서 비용을 만든 SQL 찾기
Performance Schema의 events_statements_summary_by_digest는 digest별 내부 임시 테이블 생성 횟수를 누적한다. 다음 쿼리는 현재 스키마에서 디스크 임시 테이블이 많거나 전체 임시 테이블 생성이 많은 digest를 우선순위로 보여준다.
SELECT SCHEMA_NAME,
DIGEST_TEXT,
COUNT_STAR,
SUM_CREATED_TMP_TABLES,
SUM_CREATED_TMP_DISK_TABLES,
SUM_TIMER_WAIT,
SUM_ROWS_EXAMINED,
SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
AND SUM_CREATED_TMP_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC,
SUM_CREATED_TMP_TABLES DESC,
SUM_TIMER_WAIT DESC
LIMIT 10;
실행 결과(MySQL 8.0.x):
긴 digest 텍스트와 timer 값 가운데 진단에 필요한 열만 발췌했다.
mysql> SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR,
-> SUM_CREATED_TMP_TABLES, SUM_CREATED_TMP_DISK_TABLES, ...
-> FROM performance_schema.events_statements_summary_by_digest
-> WHERE SCHEMA_NAME = DATABASE()
-> AND SUM_CREATED_TMP_TABLES > 0
-> ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC,
-> SUM_CREATED_TMP_TABLES DESC,
-> SUM_TIMER_WAIT DESC
-> LIMIT 10;
+-----------------+------------------------------------------------------+------------+------------------------+-----------------------------+
| SCHEMA_NAME | DIGEST_TEXT | COUNT_STAR | SUM_CREATED_TMP_TABLES | SUM_CREATED_TMP_DISK_TABLES |
+-----------------+------------------------------------------------------+------------+------------------------+-----------------------------+
| mysql_tech_note | SELECT DATE (`created_at`), SUM (`amount`) ... | 1 | 1 | 0 |
+-----------------+------------------------------------------------------+------------+------------------------+-----------------------------+
운영에서는 SUM_CREATED_TMP_DISK_TABLES만 정렬하지 않는다. 다음 유형을 나눠 본다.
- 호출당 임시 테이블 수가 높은 digest: 한 번 실행할 때 materialization 단계가 여러 개인지 확인한다.
- 호출 수가 매우 높은 digest: 개별 비용은 작아도 총 CPU와 메모리 할당 비용이 커질 수 있다.
SUM_ROWS_EXAMINED가 큰 digest: 임시 테이블 생성 전에 필터링할 가능성을 검토한다.- 총 지연 시간이 큰 digest: 실행 빈도와 1회 지연을 분리하고 서비스 영향으로 우선순위를 정한다.
- disk counter는 낮지만 지연과 로컬 임시 공간이 함께 증가하는 digest: TempTable mmap 사용과 다른 정렬·임시 파일 경로를 의심한다.
누적 digest 표도 재시작과 TRUNCATE의 영향을 받는다. 수집 시스템에서는 snapshot 시각, 서버 UUID, uptime, instance role을 함께 저장하고 구간 delta를 계산하는 편이 안전하다.
8. TempTable 메모리 instrument로 RAM과 파일 경로 관찰
Performance Schema memory instrumentation이 활성화되어 있다면 TempTable의 현재·최고 사용량을 확인할 수 있다.
SELECT EVENT_NAME,
CURRENT_COUNT_USED,
CURRENT_NUMBER_OF_BYTES_USED,
HIGH_COUNT_USED,
HIGH_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/temptable/%'
ORDER BY EVENT_NAME;
실행 결과(MySQL 8.0.x):
mysql> SELECT EVENT_NAME,
-> CURRENT_COUNT_USED,
-> CURRENT_NUMBER_OF_BYTES_USED,
-> HIGH_COUNT_USED,
-> HIGH_NUMBER_OF_BYTES_USED
-> FROM performance_schema.memory_summary_global_by_event_name
-> WHERE EVENT_NAME LIKE 'memory/temptable/%'
-> ORDER BY EVENT_NAME;
+--------------------------------+--------------------+------------------------------+-----------------+---------------------------+
| EVENT_NAME | CURRENT_COUNT_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_COUNT_USED | HIGH_NUMBER_OF_BYTES_USED |
+--------------------------------+--------------------+------------------------------+-----------------+---------------------------+
| memory/temptable/physical_disk | 0 | 0 | 0 | 0 |
| memory/temptable/physical_ram | 0 | 0 | 1 | 1048608 |
+--------------------------------+--------------------+------------------------------+-----------------+---------------------------+
2 rows in set (0.00 sec)
일반적으로 memory/temptable/physical_ram과 memory/temptable/physical_disk 계열 행을 확인할 수 있다. 정확한 event 이름과 노출 여부는 MySQL minor version과 instrumentation 설정에 따라 달라질 수 있으므로, 결과가 비어 있으면 곧바로 “TempTable 메모리를 사용하지 않는다”고 결론 내리지 않는다. 먼저 다음을 확인한다.
performance_schema가 활성화되어 있는가- 관련 memory instrument가
ENABLED=YES인가 - 관측 시점에 TempTable 할당이 실제로 존재하는가
- 서버 버전에서 같은 event 이름을 제공하는가
- 현재값은 0이지만 high-water mark가 남아 있는가
physical_disk라는 이름도 단순 물리 디스크 write bytes와 같다고 해석해서는 안 된다. 이것은 TempTable allocator 관점의 계측이다. 실제 저장 장치 병목 판단에는 운영체제 또는 관리형 서비스의 로컬 스토리지 사용량, IOPS, throughput, latency를 함께 본다.
9. 메모리에서 디스크로 이동하는 대표 원인
9.1 개별 내부 임시 테이블이 tmp_table_size를 넘는다
그룹 수가 많거나 중간 행 폭이 넓으면 한 개의 내부 TempTable이 빠르게 커진다. COUNT(*)만 유지하는 집계와 SELECT *, 긴 JSON, 큰 문자열을 포함한 materialization은 같은 행 수여도 메모리 요구량이 다르다.
개선 순서는 다음과 같다.
- 임시 테이블에 들어가기 전에
WHERE와 join 조건으로 행을 줄인다. - 중간 단계에서 필요 없는 컬럼을 projection에서 제외한다.
- 적절한 복합 인덱스로 그룹·정렬 순서를 제공할 수 있는지 확인한다.
- CTE/derived table이 merge 가능한 구조인지 검토한다.
- 그 뒤에만
tmp_table_size변경을 검토한다.
9.2 여러 세션이 temptable_max_ram을 경쟁한다
개별 TempTable이 작아도 동시에 많은 세션이 생성하면 전역 RAM 사용 한도에 도달할 수 있다. 이 상황은 단일 SQL 부하 테스트에서는 잘 보이지 않고, 배치 시작·리포트 집중·failover 뒤 재시도 폭주에서 나타난다.
tmp_table_size를 올렸는데도 디스크 경로가 줄지 않는다면 개별 한도보다 전역 TempTable RAM 경쟁이 원인일 수 있다. 반대로 temptable_max_ram을 과도하게 올리면 임시 테이블 수치는 좋아 보여도 mysqld 전체 메모리 여유가 줄어 OOM 위험이 커질 수 있다.
9.3 선택한 내부 엔진이 행 구조를 메모리에 담지 못한다
internal_tmp_mem_storage_engine=MEMORY를 사용하는 환경은 TempTable 기본 환경보다 컬럼 형식 제약을 더 크게 받을 수 있다. 오래된 버전과의 호환성 때문에 MEMORY를 선택했다면 BLOB/TEXT, 가변 길이 컬럼, 문자셋에 따른 최대 행 폭을 점검한다.
여기서 해결책은 무조건 TempTable로 바꾸거나 LOB를 제거하는 것이 아니다. 버전 호환성, 쿼리 의미, 메모리 상한, 회귀 테스트를 거쳐 변경해야 한다.
9.4 materialization 범위가 예상보다 크다
CTE를 여러 번 참조하거나 derived table을 merge할 수 없거나 window function 단계가 겹치면 눈에 보이는 GROUP BY 하나보다 많은 내부 임시 객체가 만들어질 수 있다. Traditional EXPLAIN만으로 충분하지 않다면 EXPLAIN FORMAT=TREE 또는 EXPLAIN FORMAT=JSON에서 Materialize, temporary table, sort iterator를 확인한다.
9.5 쿼리량이 늘어 누적 지표만 커진다
쿼리 한 건의 임시 테이블 사용 방식은 그대로인데 트래픽이 두 배가 되면 Created_tmp_tables 증가량도 커질 수 있다. 이 경우 설정 변경보다 호출량, cache 정책, 중복 요청, 배치 주기 조정이 더 직접적인 해결책일 수 있다.
10. 운영 진단 순서
단계 1: 관측 기준을 고정한다
MySQL 버전, uptime, 최근 restart/failover 시각, parameter 변경 시각을 기록한다. 동일하지 않은 생존 기간의 누적 카운터를 서로 비교하지 않는다.
단계 2: 실행 계획에서 생성 원인을 찾는다
상위 digest의 대표 SQL에 대해 EXPLAIN을 확인한다. Using temporary, Using filesort, derived/CTE materialization, window 단계와 예상 rows를 구분한다.
단계 3: 구간 delta와 쿼리 처리량을 연결한다
1분 또는 5분 단위로 다음 값을 함께 저장한다.
Created_tmp_tables증가량Created_tmp_disk_tables증가량- 실행 문장 수 또는 요청 수
- TempTable RAM/disk memory instrument
- 로컬 임시 저장 공간 사용량과 I/O latency
- CPU, 전체 메모리 여유, swap/OOM 징후
단계 4: digest별 비용을 정렬한다
호출 횟수, 총 지연, examined rows, tmp table 수를 함께 보고 전체 DB time에 가장 큰 digest부터 분석한다. disk 비율만 높은 저빈도 관리 쿼리보다, 매초 수천 번 실행되며 메모리 임시 테이블을 만드는 API가 더 큰 문제일 수 있다.
단계 5: SQL과 인덱스를 먼저 개선한다
필터 조기 적용, projection 축소, 불필요한 DISTINCT 제거, UNION ALL 가능 여부, GROUP/ORDER 키와 인덱스 정렬 호환성, materialization 방지 가능성을 검토한다. 의미가 달라지는 rewrite는 반드시 결과 집합 동등성을 검증한다.
단계 6: 한도를 소폭 조정하고 동시성을 시험한다
설정 변경이 필요하면 한 번에 하나의 축만 소폭 변경한다. 단일 쿼리 지연뿐 아니라 동시 실행 시 mysqld RSS, TempTable high-water mark, 로컬 저장 공간, p95/p99 latency를 비교한다.
11. Aurora MySQL에서의 해석 차이
Aurora MySQL도 호환 버전의 optimizer와 TempTable 경로를 사용하지만 운영 관측 단위가 다르다.
- 인스턴스 로컬 자원: 내부 임시 테이블의 메모리와 임시 파일은 DB 인스턴스 자원 압력과 연결된다. Aurora의 분산 cluster volume이 모든 TempTable spill 비용을 대신 처리한다고 가정하면 안 된다.
- writer와 reader 분리: writer와 각 Aurora Replica는 별도의 메모리, Performance Schema 누적값, 로컬 임시 공간을 가진다. reader endpoint 뒤의 부하는 여러 인스턴스로 나뉠 수 있다.
- failover: 역할 전환 뒤에는 기존 writer의 status와 새 writer의 status를 연속된 하나의 누적값처럼 비교하지 않는다. instance identifier와 role을 함께 저장한다.
- 파라미터 적용 범위: DB cluster parameter group과 DB parameter group, dynamic/static 적용 여부를 확인한다. 특정 reader의 분석 부하 때문에 전체 cluster에 공격적인 TempTable 한도를 적용하지 않는다.
- 서비스 지표 결합: Performance Insights 또는 Database Insights의 상위 SQL·DB load와 CloudWatch의
FreeLocalStorage,FreeableMemory, CPU, replica lag를 함께 본다. 제공 지표 이름과 의미는 엔진 버전 및 서비스 설정에서 다시 확인한다.
Aurora에서 임시 저장 공간 부족은 단순 쿼리 지연을 넘어 쿼리 실패나 인스턴스 안정성 문제로 이어질 수 있다. 분석 쿼리를 reader로 분리하더라도 동시성 제한과 쿼리별 중간 결과 크기 관리는 필요하다.
12. 흔한 오해와 실패 방식
“Using temporary가 보이면 디스크를 쓴다”
Using temporary는 내부 임시 테이블을 사용한다는 신호이지 저장 위치를 말하지 않는다. status delta와 TempTable memory instrument를 추가로 확인한다.
“Created_tmp_disk_tables가 0이면 디스크 활동도 0이다”
TempTable의 memory-mapped file 경로와 filesort 임시 파일을 빠뜨릴 수 있다. 로컬 저장 공간과 I/O 지표를 함께 본다.
“disk ratio가 높으므로 tmp_table_size를 크게 올린다”
원인이 wide intermediate row, 과도한 materialization, 낮은 선택도, 동시성이라면 한도 증설은 문제를 메모리 압박으로 옮길 뿐이다.
“max_heap_table_size만 올리면 TempTable이 커진다”
최신 기본 TempTable 구성과 MEMORY 엔진 구성의 한도 규칙을 혼동한 판단이다. 먼저 internal_tmp_mem_storage_engine과 버전을 확인한다.
“전역 카운터로 특정 쿼리를 찾을 수 있다”
전역 값에는 모든 세션이 섞인다. digest별 누적값, session delta, query tagging 또는 별도 재현을 사용한다.
“메모리에만 남으면 항상 더 빠르고 안전하다”
RAM은 공유 자원이다. 큰 내부 임시 테이블을 메모리에 오래 유지하면 Buffer Pool 이외 영역이 팽창하고 OOM 위험이 커질 수 있다. 디스크 전환은 비용인 동시에 메모리 상한을 지키는 안전장치다.
“설정 변경 뒤 즉시 성공으로 판단한다”
단일 실행의 응답 시간만 좋아져도 피크 동시성에서는 더 나빠질 수 있다. 정상 트래픽 주기와 배치 주기를 포함해 high-water mark와 tail latency를 확인한다.
13. DBA 점검표
개념과 설정
- 내부 임시 테이블과
CREATE TEMPORARY TABLE - MySQL 정확한 버전과
internal_tmp_mem_storage_engine - TempTable의
tmp_table_size,temptable_max_ram,temptable_max_mmap - MEMORY 엔진을 사용한다면
max_heap_table_size
원인 분석
- 상위 digest에서
GROUP BY,DISTINCT,UNION -
Using temporary와Using filesort -
Created_tmp_disk_tables
변경과 검증
14. 테스트 객체 정리
앞의 재현 예제를 실행했다면 테스트 테이블을 제거한다.
DROP TABLE tmp_diag_demo;
실행 결과(MySQL 8.0.x):
mysql> DROP TABLE tmp_diag_demo;
Query OK, 0 rows affected (0.00 sec)
결론
MySQL 내부 임시 테이블 진단의 핵심은 Created_tmp_disk_tables 숫자를 낮추는 것이 아니다. 먼저 SQL 실행 경로에서 왜 중간 결과가 필요한지 찾고, 그 결과의 행 수와 폭, 실행 빈도와 동시성을 줄여야 한다. 그다음 TempTable의 개별 한도, 전역 RAM 한도, mmap과 InnoDB 디스크 경로를 구분해 관찰해야 한다.
안전한 운영 절차는 실행 계획으로 생성 원인 확인 → session·global delta 측정 → digest별 비용 분리 → TempTable RAM·disk와 로컬 저장 공간 관찰 → SQL·인덱스 개선 → 제한적인 설정 조정 순서다. 이 원칙을 지키면 디스크 임시 테이블을 단순히 메모리로 밀어 올리는 대신, 중간 결과 자체를 줄이고 서버의 메모리 안정성과 쿼리 지연을 함께 개선할 수 있다.
다음 단계에서는 TempTable 진단을 statement digest의 호출량·지연 시간과 결합하여, 임시 테이블을 가장 많이 만드는 SQL을 대시보드와 경보 기준으로 운영하는 방법을 다룰 수 있다.