MySQL TempTable 디스크 전환 확인법: tmp_table_size별 카운터 비교와 진단 순서
운영 중인 MySQL 서버는 사내 DPM 모니터링 툴로 5초 간격 수집을 하고 있습니다. 이 툴에서는 쿼리별로 소요 시간과 함께 템프 공간이 얼마나 늘었는지가 보이기 때문에, 느린 집계 쿼리를 볼 때 “실행 시간이 긴가”와 “템프를 얼마나 키우는가”를 같이 놓고 판단합니다. 그런데 화면에서 템프 증가가 보인 쿼리를 EXPLAIN으로 다시 보면 Extra에는 늘 똑같이 Using temporary만 찍혀 있습니다. 이 표시만으로는 임시 테이블이 메모리 안에서 끝났는지, 디스크 내부 임시 테이블로 넘어갔는지를 구분할 수 없습니다.
그래서 MySQL 8.4.11 로컬 환경에서 같은 집계 쿼리를 두 번 돌려 봤습니다. 바꾼 것은 세션의 tmp_table_size 하나뿐이고, 결과는 아래와 같았습니다.
| 세션 tmp_table_size | EXPLAIN Extra | Created_tmp_tables 증가 | Created_tmp_disk_tables 증가 |
|---|---|---|---|
| 2MB (2,097,152바이트) | Using temporary; Using filesort | 1 | 1 |
| 161MB (기본값) | Using temporary; Using filesort | 1 | 0 |
실행계획은 두 번 모두 같았고, 디스크로 넘어갔는지는 상태 카운터의 증가량에서만 갈렸습니다. 아래는 이 결과를 얻기까지 순서대로 던진 질문과 확인 방법입니다.
지금 서버의 임시 테이블 한도는 몇인가
한도를 이야기하기 전에 실제 값부터 봤습니다. 버전, TempTable 관련 전역 값, 현재 세션의 tmp_table_size, 메모리 내부 임시 테이블 엔진을 한 번에 조회합니다.
SELECT
VERSION() AS mysql_version,
ROUND(@@GLOBAL.temptable_max_ram / 1024 / 1024)
AS temptable_max_ram_mb,
ROUND(@@GLOBAL.temptable_max_mmap / 1024 / 1024)
AS temptable_max_mmap_mb,
ROUND(@@SESSION.tmp_table_size / 1024 / 1024)
AS tmp_table_size_mb,
@@SESSION.internal_tmp_mem_storage_engine
AS internal_tmp_mem_storage_engine;
temptable_max_ram은 1,024MB, temptable_max_mmap은 0, 세션 tmp_table_size는 약 161MB, 엔진은 TempTable입니다. 테스트 테이블은 dba_lab.ahi_lookup_test로 약 100만 행이 들어 있습니다. 이번 확인에서 전역 값은 하나도 바꾸지 않았고, 현재 연결의 tmp_table_size만 내렸다가 되돌렸습니다.
여기서 temptable_max_mmap이 0이라는 점은 뒤에서 카운터를 믿어도 되는 근거가 됩니다. 이 값이 0보다 크면 TempTable이 RAM 한도를 넘었을 때 메모리 매핑 파일을 먼저 쓰는데, MySQL 문서 기준으로 그 경로는 Created_tmp_disk_tables에 잡히지 않습니다. 운영 서버에서 같은 방법을 쓰기 전에 이 값부터 확인해야 하는 이유입니다.
세 가지 메모리 설정은 각각 무엇을 막는가
디스크 전환이 보이면 “임시 테이블 메모리를 올리자”로 바로 가기 쉬운데, 관련 설정은 범위가 서로 다릅니다.
| 설정 | 적용 범위 | 넘었을 때 |
|---|---|---|
| tmp_table_size | 임시 테이블 하나 | 그 테이블만 디스크 내부 임시 테이블(InnoDB)로 넘어감 |
| temptable_max_ram | TempTable 엔진 전체가 쓰는 RAM 합계 | 메모리 매핑 파일 또는 디스크 내부 임시 테이블 사용 |
| temptable_max_mmap | 메모리 매핑 파일 전체 크기 | 0이면 이 단계를 건너뛰고 바로 디스크 내부 임시 테이블 사용 |
쿼리 하나가 큰 중간 결과를 만들어 넘어가는 경우는 tmp_table_size 쪽 문제이고, 여러 세션이 동시에 임시 테이블을 쓰다가 합계가 넘는 경우는 temptable_max_ram 쪽 문제입니다. 이번에 재현한 것은 전자입니다. temptable_max_ram은 1,024MB 그대로 두고 개별 한도만 2MB로 낮췄습니다.
예전 튜닝 글에서 함께 올리라고 하는 max_heap_table_size는 내부 임시 테이블 엔진이 MEMORY일 때 tmp_table_size와 같이 적용되는 값입니다. 엔진이 TempTable이면 개별 한도는 tmp_table_size가 정하므로, 두 값을 같이 손대기 전에 internal_tmp_mem_storage_engine부터 확인하는 편이 맞습니다.
어떤 쿼리가 임시 테이블을 크게 만드는가
재현에 쓴 쿼리는 100만 행을 MOD(user_no, 100000) 기준 10만 개 그룹으로 나누고, 그룹별 건수와 payload 문자열의 최댓값을 구합니다.
SELECT
MOD(user_no, 100000) AS group_no,
COUNT(*) AS row_count,
MAX(payload) AS sample_payload
FROM dba_lab.ahi_lookup_test
GROUP BY MOD(user_no, 100000)
ORDER BY group_no
LIMIT 10;
EXPLAIN 결과는 type=ALL, possible_keys에 uk_tenant_user가 보이지만 key=NULL, 추정 rows=989,896, Extra=Using temporary; Using filesort입니다. 임시 테이블이 커질 수밖에 없는 조건이 세 가지 겹쳐 있습니다.
첫째, 그룹 기준이 계산식이라 인덱스 순서로 묶을 수 없습니다. 전체 행을 읽으면서 그룹별 중간값을 임시 테이블에 쌓아야 합니다. 둘째, 그룹이 10만 개이고 각 행에 MAX(payload) 문자열이 붙어 다닙니다. 임시 테이블 크기는 그룹 수와 행 길이를 곱한 만큼 커지므로 2MB 같은 한도는 금방 넘습니다. 셋째, LIMIT 10은 도움이 되지 않습니다. 앞의 10개를 고르려면 10만 개 그룹을 모두 만들고 정렬까지 끝내야 합니다.
디스크로 넘어갔는지는 어떻게 확인하는가
Created_tmp_tables와 Created_tmp_disk_tables는 세션이 열린 뒤부터 쌓이는 누적값입니다. 숫자 하나만 보면 이번 쿼리 때문인지 알 수 없으므로, 같은 연결에서 쿼리 직전 값을 사용자 변수에 담아 두고 직후 값과 뺐습니다.
먼저 세션 한도를 2MB로 내리고 실행 전 값을 저장합니다.
SET SESSION tmp_table_size = 2097152; -- 2MB
SELECT
MAX(CASE WHEN VARIABLE_NAME = 'Created_tmp_tables'
THEN CAST(VARIABLE_VALUE AS UNSIGNED) END),
MAX(CASE WHEN VARIABLE_NAME = 'Created_tmp_disk_tables'
THEN CAST(VARIABLE_VALUE AS UNSIGNED) END)
INTO @tmp_tables_before, @tmp_disk_tables_before
FROM performance_schema.session_status
WHERE VARIABLE_NAME IN (
'Created_tmp_tables',
'Created_tmp_disk_tables'
);이어서 위의 집계 쿼리를 같은 연결에서 실행하고, 바로 다음 조회로 전후 값과 증가량을 계산합니다.
SELECT
@@SESSION.tmp_table_size AS tmp_table_size_bytes,
@tmp_tables_before AS tmp_tables_before,
MAX(CASE
WHEN VARIABLE_NAME = 'Created_tmp_tables'
THEN CAST(VARIABLE_VALUE AS UNSIGNED)
END) AS tmp_tables_after,
MAX(CASE
WHEN VARIABLE_NAME = 'Created_tmp_tables'
THEN CAST(VARIABLE_VALUE AS UNSIGNED)
END) - @tmp_tables_before
AS tmp_tables_delta,
@tmp_disk_tables_before AS disk_tables_before,
MAX(CASE
WHEN VARIABLE_NAME = 'Created_tmp_disk_tables'
THEN CAST(VARIABLE_VALUE AS UNSIGNED)
END) AS disk_tables_after,
MAX(CASE
WHEN VARIABLE_NAME = 'Created_tmp_disk_tables'
THEN CAST(VARIABLE_VALUE AS UNSIGNED)
END) - @tmp_disk_tables_before
AS disk_tables_delta
FROM performance_schema.session_status
WHERE VARIABLE_NAME IN (
'Created_tmp_tables',
'Created_tmp_disk_tables'
);
tmp_table_size 2,097,152바이트에서 내부 임시 테이블은 19에서 20으로, 디스크 내부 임시 테이블은 0에서 1로 늘었습니다. 이 쿼리가 만든 임시 테이블 1개가 2MB를 넘으면서 디스크 내부 임시 테이블로 바뀐 것입니다.
다음은 SET SESSION tmp_table_size = DEFAULT;로 세션 값을 전역 값(약 161MB)으로 돌린 뒤 같은 순서를 반복했습니다. 이번에는 이전 변수와 섞이지 않도록 @normal_tmp_before, @normal_disk_before라는 이름으로 실행 전 값을 저장했고, 쿼리 앞뒤 시각 차이를 @normal_elapsed_ms에 담아 실행 시간도 같이 출력했습니다.
SELECT
ROUND(@@SESSION.tmp_table_size / 1024 / 1024)
AS tmp_table_size_mb,
@normal_elapsed_ms AS elapsed_ms,
MAX(CASE WHEN VARIABLE_NAME = 'Created_tmp_tables'
THEN CAST(VARIABLE_VALUE AS UNSIGNED) END)
- @normal_tmp_before AS tmp_tables_delta,
MAX(CASE WHEN VARIABLE_NAME = 'Created_tmp_disk_tables'
THEN CAST(VARIABLE_VALUE AS UNSIGNED) END)
- @normal_disk_before AS disk_tables_delta
FROM performance_schema.session_status
WHERE VARIABLE_NAME IN (
'Created_tmp_tables',
'Created_tmp_disk_tables'
);
161MB에서는 내부 임시 테이블이 똑같이 1개 만들어졌지만 디스크 내부 임시 테이블 증가량은 0입니다. 같은 쿼리, 같은 데이터, 같은 실행계획에서 한도 하나 때문에 저장 위치가 달라진 것을 카운터로 확인할 수 있었습니다.
실행 시간은 약 2,502ms가 나왔는데, 이 숫자로 “디스크 전환 때문에 몇 % 느려졌다”고 말하지는 않겠습니다. 2MB 쪽은 같은 방식으로 시간을 재지 않았고, 161MB 쪽은 두 번째 실행이라 버퍼 풀에 데이터가 올라온 상태였을 수 있습니다. 시간을 비교하려면 같은 조건에서 각각 여러 번 재야 합니다. 그리고 디스크 내부 임시 테이블도 InnoDB 버퍼 풀을 거치기 때문에, 디스크 임시 테이블이 생겼다는 사실이 곧 스토리지 I/O 병목이라는 뜻도 아닙니다.
운영에서는 어디서부터 좁혀 가는가
실제 운영에서 출발점은 DPM 화면입니다. 5초 간격으로 쿼리별 소요 시간과 템프 공간 증가가 함께 수집되기 때문에, 오래 걸리면서 템프를 크게 키우는 쿼리가 먼저 눈에 들어오고 그 근거로 대상을 정합니다. 위에서 정리한 세션 카운터 비교는 그다음 단계에서 씁니다. 모니터링 화면이 “이 쿼리가 템프를 키운다”를 알려 준다면, 세션 카운터는 “그게 디스크 내부 임시 테이블로 넘어간 것인지”와 “고친 뒤에 정말 안 넘어가는지”를 쿼리 단위로 확인해 줍니다.
모니터링 툴이 없는 환경이라면 MySQL 자체 통계로 같은 흐름을 만들 수 있습니다. 서버 전체 추세는 아래처럼 봅니다.
SHOW GLOBAL STATUS LIKE 'Created_tmp%';일정 간격으로 찍어서 Created_tmp_disk_tables가 Created_tmp_tables에 비해 꾸준히 늘고 있는지를 봅니다. 다만 서버 누적값으로는 어떤 SQL인지 알 수 없으므로, 원인 SQL은 다이제스트 요약 테이블에서 찾습니다.
SELECT
LEFT(DIGEST_TEXT, 120) AS sql_pattern,
COUNT_STAR AS exec_count,
SUM_CREATED_TMP_TABLES AS tmp_tables,
SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_DISK_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10;exec_count와 tmp_disk_tables는 같이 봐야 합니다. 자주 도는 가벼운 SQL이 가끔 넘어가는 경우와, 하루 몇 번 도는 무거운 배치 SQL이 매번 넘어가는 경우는 손대는 방향이 다릅니다.
개별 쿼리가 아니라 동시 실행 합계가 의심될 때는 TempTable 엔진의 메모리 계측값을 봅니다.
SELECT
EVENT_NAME,
CURRENT_NUMBER_OF_BYTES_USED,
HIGH_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/temptable/%';physical_ram 항목의 최고 사용량이 temptable_max_ram에 가깝다면 쿼리 하나보다 동시 부하를 먼저 의심합니다. 이 조회는 이번에 결과를 캡처하지 않았고, 진단할 때 같이 볼 지표로만 적어 둡니다. 참고로 쿼리가 끝난 뒤에도 이 수치가 바로 내려가지 않을 수 있습니다. TempTable은 스레드 단위로 메모리 블록을 잡아 두는 구조라, 연결을 오래 유지하는 커넥션 풀 환경에서는 정상 동작일 수 있습니다. 누수인지 판단하려면 같은 부하에서 계속 오르기만 하는지 장기 추이를 봐야 합니다.
tmp_table_size를 올리면 끝나는가
이번 결과만 보면 한도를 올리는 것이 가장 빠른 해결처럼 보입니다. 하지만 tmp_table_size는 연결마다 적용되는 값이라, 전역으로 크게 올리면 동시에 도는 쿼리들이 쓸 수 있는 메모리 상한도 같이 커집니다. 그래서 순서를 바꿔 쿼리가 만드는 중간 결과부터 줄이는 쪽을 먼저 봅니다.
읽는 행을 먼저 줄입니다. 기간이나 상태 조건으로 집계 대상 자체를 줄일 수 있는지 확인합니다.
집계 단계에서 긴 컬럼을 빼 봅니다. 이번 쿼리의 MAX(payload)처럼 긴 문자열을 그룹마다 들고 다니면 임시 테이블 행이 길어집니다. 필요한 그룹만 먼저 고르고 상세 값은 나중에 따로 가져오면 행 길이가 줄어듭니다.
그룹 기준을 인덱스로 쓸 수 있게 만듭니다. 계산식 대신 생성 컬럼이나 함수 기반 인덱스를 두면 인덱스 순서로 그룹을 처리해 임시 테이블이 아예 필요 없어질 수 있습니다. 이 방법은 이번에 검증하지 않았으므로 적용 전에 EXPLAIN으로 Using temporary가 사라지는지 확인해야 합니다.
한도는 마지막에, 필요한 곳에만 올립니다. 특정 배치만 문제라면 그 세션에서만 SET SESSION으로 올리는 방법을 먼저 검토하고, 전역 값은 동시 실행 수와 서버 메모리 여유를 계산한 뒤 정합니다.
어떤 방법을 쓰든 효과 확인은 앞에서 쓴 전후 카운터 비교로 합니다. 고친 쿼리를 같은 세션에서 다시 돌려 disk_tables_delta가 0으로 나오는지 보고, 운영에서는 DPM 화면에서 해당 쿼리의 템프 증가와 소요 시간이 줄었는지를 이어서 봅니다.
카운터가 0이면 디스크를 안 쓴 것인가
꼭 그렇지는 않습니다. 앞에서 말한 대로 temptable_max_mmap이 0보다 큰 서버에서는 메모리 매핑 파일로 넘어간 임시 테이블이 Created_tmp_disk_tables에 잡히지 않습니다. 또 filesort가 정렬 버퍼를 넘어 임시 파일을 쓰는 것은 임시 테이블과 별개라서 Sort_merge_passes 같은 다른 카운터로 봐야 합니다. 이번 환경은 temptable_max_mmap=0이었기 때문에 카운터 증가량을 그대로 디스크 전환으로 읽을 수 있었습니다.
점검할 때 확인한 항목을 순서대로 묶어 두면 다음과 같습니다.
| 확인할 것 | 볼 곳 |
|---|---|
| 템프를 크게 키우는 쿼리가 무엇인가 | 모니터링 툴(쿼리별 소요 시간, 템프 증가), events_statements_summary_by_digest |
| 카운터를 믿을 수 있는 환경인가 | temptable_max_mmap, internal_tmp_mem_storage_engine |
| 왜 임시 테이블이 커지는가 | EXPLAIN, 그룹 수, 집계에 포함된 문자열 컬럼 길이 |
| 개별 한도인가, 전체 한도인가 | tmp_table_size, temptable_max_ram, memory/temptable 계측값 |
| 고친 뒤 디스크 전환이 사라졌는가 | 같은 세션 전후 Created_tmp_disk_tables 증가량 |
댓글 0
첫 댓글을 남겨보세요.