앞서 살펴본 WAL File, Data File에 이어, 이번에는 쿼리 수행 중 일시적으로 생성 및 삭제 되는 Temp File에 대해 알아보도록 하겠습니다.
I/O 단원의 마지막 주제이기도 한 Temp File이 어떤 상황에서 생성되는지를 먼저 살펴보고, Temp File I/O를 확인하는 방법과 Temp File로 인한 I/O 부하가 어떤 Wait Event로 나타나는지를 중심으로 설명하겠습니다.
Temp File
Temp File은 Sort, Hash 등 대용량 데이터 작업을 수행하는 과정에서 주어진 메모리가 부족할 때 임시로 생성되는 파일입니다.
즉, 할당된 Local Memory보다 Backend Process가 필요로 하는 메모리가 더 큰 작업이 수행될 때, 부족한 메모리를 보완하기 위해 디스크에 Temp File을 생성하여 사용합니다. 이 과정에서 Temp File에 대한 Read/Write I/O가 발생합니다.
Local Memory는 각 프로세스에 독립적으로 할당되는 메모리 공간으로, 작업 유형에 따라 다양한 세부 영역이 존재합니다.
Temp File 생성 과정
Backend Process 요청에 의해 Temp File이 생성되는 과정을 간략히 표현하면 다음과 같습니다.
① SQL 쿼리 수행
사용자가 SQL 쿼리 수행을 요청하면, Backend Process는 SQL 실행을 시작합니다.
② Work Memory 연산
Sort/Hash/Group by와 같은 메모리 연산이 필요한 경우, Backend Process는 Local Memory 내 Work Memory를 사용하여 SQL 연산을 수행합니다.Work Memory 크기는 work_mem 파라미터를 통해 설정할 수 있습니다.
③ Temp File 생성
SQL 연산 수행 중 필요한 메모리 사용량이 Work Memory 크기를 초과하면, Backend Process는 메모리 내에서 모든 연산을 처리할 수 없게 됩니다.
이 경우 Backend Process는 메모리에서 처리하던 중간 결과를 Temp File을 생성하여 디스크에 기록합니다.
Temp File의 파일명(pgsql_tmp1295764.0)은 아래와 같은 포맷으로 구성됩니다.
- base/
pgsql_tmp: Temp File 생성 디렉토리
1295764: Temp File을 생성한 PostgreSQL 프로세스의 ID(PID)
.0: Temp File의 인덱스로, 하나의 프로세스에 대해 여러 개의 Temp File이 생성된 경우 인덱스를 추가하여 Temp File 생성
④ Temp File 사용
쿼리 실행 과정에서, 메모리에서 처리 가능한 데이터는 Work Memory에서 처리하고, 초과된 데이터는 Temp File을 통해 Read/Write를 반복하며 연산이 계속 진행됩니다.
예를 들어 정렬 작업의 경우, 메모리에서 처리할 수 없는 데이터는 일부 정렬된 상태로 Temp File에 Write합니다. 이후 이를 다시 Read해 병합함으로써 최종 결과를 완성합니다.
이와 같이 Temp File에 저장되는 방식과 단위는, Backend Process가 수행하는 작업의 종류에 따라 달라집니다.
⑤ Temp File 삭제
쿼리 종료 시 Temp File은 자동으로 삭제됩니다.
Wait Event
Temp File을 생성하여 사용하는 과정에서는 Temp File에 대한 Read/Write 작업으로 인해 IO:BufFileRead와 IO:BufFileWrite와 같은 Wait Event가 발생할 수 있습니다.
IO:BufFileRead
Backend Process가 Temp File로부터 데이터를 읽어오는 작업을 대기할 때 발생하는 Wait Event입니다.
이 Wait Event는 Temp File에 대한 디스크 Read I/O가 완료될 때까지 Backend Process가 대기하고 있음을 의미합니다.
IO:BufFileWrite
Backend Process가 Temp File에 데이터를 저장하는 작업을 대기할 때 발생하는 Wait Event입니다.
이 Wait Event는 Temp File에 대한 디스크 Write I/O가 완료될 때까지 Backend Process가 대기하고 있음을 의미합니다.
두 Wait Event는 모두, Temp File 사용이 필요한 작업에서 발생하는 I/O 대기라는 공통된 원인을 가집니다.
이어서 이들 Wait Event가 주로 발생하는 원인과 이에 대한 해결 방안을 살펴보겠습니다.
주요 발생 원인
- 대량의 데이터를 대상으로 Sort 또는 Hash 기반의 연산이 수행될 경우, Backend Process는 Sort 결과나 Hash Table을 메모리에 유지하려고 시도합니다. 이때 처리 대상 데이터가 크거나 동시에 수행되는 작업이 많아지면 각 작업에 할당된 Work Memory를 초과하게 되어 Temp File을 사용하게 되며, 그 결과 IO:BufFileRead와 IO:BufFileWrite와 같은 Wait Event가 발생합니다.
- Sort :
ORDER BY,GROUP BY,DISTINCT,UNION - Hash :
GROUP BY,JOIN,DISTINCT
- Workload 대비 충분한 메모리가 할당되지 않았다면 Temp File 사용이 빈번해지면서 관련 Wait Event 발생이 증가할 수 있습니다.
- 주로 Work Memory가 부족하여 Sort/Hash 작업이 디스크 기반 Temp File 처리로 전환되어 발생합니다.
- 드물게
VACUUM,ANALYZE과 같은 유지 관리 작업에 사용되는 Maintenance Work Memory가 부족한 경우에도 Temp File이 사용됩니다.
해결 방안
- Sort 또는 Hash 기반의 연산이 포함된 쿼리의 최적화가 도움이 될 수 있습니다.
- 불필요한
ORDER BY,GROUP BY제거하여 정렬 및 집계 연산을 최소화합니다. - 필터 조건을 추가하여 처리할 데이터 양을 줄이면 Sort/Hash 연산 부담이 감소하여 Temp File 없이 Work Memory 내에서 처리될 가능성이 높아집니다.
- 적절한 인덱스 생성하여 이를 활용함으로써 정렬을 회피하거나 대규모 Hash 연산을 줄일 수 있습니다.
- 쿼리 최적화를 통해 문제가 해결되지 않는다면 할당된 Work Memory 크기를 점검해서 쿼리 특성과 동시성을 고려하여 work_mem 파라미터를 통해 Work Memory 크기를 조정합니다.
- Work Memory는 Local Memory 영역으로 각 프로세스마다 개별적으로 할당됩니다. 따라서 동시 Connection 수가 많은 환경에서 work_mem 값을 과도하게 높게 설정하면, 오히려 전체 메모리 사용량이 급증해 성능 저하가 발생할 수 있으므로 동시 Connection 수를 제한하는 max_connections 파라미터 설정도 함께 고려해야 합니다.
- work_mem는 Backend Process(세션) 레벨에서 조정할 수 있으므로, 글로벌 설정은 보수적으로 유지하고 대량 Sort / Hash 연산이 필요한 쿼리를 수행하는 프로세스에만 일시적으로 값을 증가시키는 방식이 효과적입니다. 이를 통해 불필요한 Temp File 생성을 줄이면서도 시스템 전체 메모리 안정성을 유지할 수 있습니다.
Work Memory 크기에 따른 Temp File I/O 발생 비교
테스트를 통해 Work Memory 크기(work_mem)를 변화시키며 Temp File에 대한 I/O 발생량을 확인해보겠습니다.
위와 같이 정렬(Sort) 작업을 포함하는 쿼리를 실행하여 서로 다른 Work Memory 설정에서 어떤 차이를 보이는지 살펴보겠습니다.
[CASE1] work_mem=1MB
- 총 소요 시간 :
62389.719ms
- Sort 방식 :
external merge→ Temp File 사용
- Disk 사용량 :
Disk: 293568kB(≈ 287MB)
- Temp I/O 통계 :
temp read=146777 written=147515
Temp I/O 통계는 pg_stat_statements 뷰를 통해서도 확인할 수도 있습니다.
- Scan 방식 :
Seq Scan on io_temp_test
pg_wait_sampling_profile을 조회하여 쿼리 수행 과정에서 발생한 Temp File 관련 Wait Event 발생량을 확인할 수 있습니다.
- Temp File 사용으로 IO:BufFileWrite와 IO:BufFileRead Wait Event가 발생한 것을 볼 수 있습니다.
[CASE2] work_mem=4MB
CASE1 보다 더 큰 값(
4MB)으로 Work Memory를 조정한 후 동일한 쿼리를 수행하여 결과를 확인해보겠습니다.- 총 소요 시간 :
57705.340ms
→ 약 4,684 ms 단축(≈ 7.5% 개선)
- Sort 방식 :
external merge
→ work_mem=
4MB로 조정해도 정렬 대상(1천만 row)을 메모리에서 작업하기엔 여전히 부족하여 Temp File 사용- Disk 사용량 :
Disk: 293616kB(≈ 287MB)
→ 정렬 대상 규모는 같기 때문에 사용량은 거의 동일
- Temp I/O 통계 :
temp read=73401 written=73580→ work_mem이 커지면 한 번에 더 많은 데이터를 처리할 수 있어 병합 단계가 줄어들고, 그 결과 누적 Temp I/O가 크게 감소
- Scan 방식 :
Seq Scan on io_temp_test
항목 | work_mem=1MB | work_mem=4MB |
총 소요 시간 | ~62,389 ms | ~57,705 ms |
Sort Method | external merge | external merge |
Disk (Sort) | 293,568 kB (≈ 287 MB) | 293,616 kB (≈ 287 MB) |
temp written | 147,515 blocks (≈ 1,152 MB) | 73,580 blocks (≈ 575 MB) |
temp read | 146,777 blocks (≈ 1,146 MB) | 73,401 blocks (≈ 574 MB) |
Scan 방식 | Seq Scan | Seq Scan |
[CASE3] work_mem=1MB+인덱스 생성
이번에는 CASE1과 Work Memory 크기는 동일하게 유지한 상태에서 해당 테이블에 인덱스를 생성하여 쿼리 수행 결과를 확인해보겠습니다.
컬럼 d를 기준으로 검색 및 정렬 성능 향상을 위해 모든 컬럼을 포함한 복합 인덱스를 생성하였습니다.
- 총 소요 시간 :
27199.740ms
→ 약 35,189 ms 단축(≈56% 개선)
- Sort 방식 : 미표시
→ Temp File 기반 Sort가 제거됨
- Disk 사용량 : 미표시 → Temp File 사용 안함
- Temp I/O 통계 : 미표시 → Temp I/O=0
- Scan 방식 :
Index Only Scan Backward
→ ORDER BY d DESC를 위해 인덱스를 역방향으로 스캔하여 정렬을 회피
CASE3은 ORDER BY d DESC를 별도의 Sort작업 없이, 인덱스를 역순으로 읽어 해결하였으며,
Heap Fetches: 0이므로, 인덱스에 모든 컬럼을 포함시켜 쿼리를 인덱스 만으로 충족했고, 동시에 Index Only Scan 조건도 만족했습니다.항목 | work_mem=1MB (인덱스 없음) | work_mem=1MB + 인덱스 생성 |
총 소요 시간 | ~62,389 ms | ~27,199 ms |
Sort Method | external merge | 없음 |
Disk (Sort) | 293,568 kB (≈ 287 MB) | 없음 |
temp written | 147,515 blocks (≈ 1,152 MB) | 없음 |
temp read | 146,777 blocks (≈ 1,146 MB) | 없음 |
Scan 방식 | Seq Scan | Index Only Scan Backward |
CASE1~3을 통해 다음과 같은 사실을 확인할 수 있습니다.
- work_mem 값을 늘리면 Temp File에 대한 I/O 부하가 감소한다.
- 인덱스 사용과 같은 최적화를 통해 Work Memory 사용량을 최소화한다면 Temp File 관련 I/O를 크게 줄이거나 생성 자체를 안 할 수 있다.
따라서 Temp File I/O를 줄이기 위해서는 먼저 쿼리 최적화를 통해 Work Memory 작업량을 최소화합니다. 쿼리 최적화를 통한 개선이 어렵거나 작업량 대비 할당된 Work Memory가 부족하다면 work_mem 파라미터 조정을 통해 Work Memory를 늘리는 방안을 검토합니다.
마무리
- Sort 또는 Hash 기반의 연산 작업을 포함한 쿼리를 수행하는 과정에서 Work Memory가 부족하면 Temp File을 생성하여 사용한다.
- Temp File을 사용하게 되면 Temp File에 데이터를 저장하고 읽어오는 과정에서 디스크 I/O가 발생하며, 그 결과 IO:BufFileRead와 IO:BufFileWrite와 같은 Wait Event가 발생한다.
- IO:BufFileRead와 IO:BufFileWrite 발생을 줄이기 위해서는 쿼리 최적화를 통한 Work Memory 사용량 감소를 최우선으로 한다.
- 쿼리 최적화 이후에도 Work Memory를 초과하여 Temp File을 자주 사용하게 된다면, 전체 시스템 메모리 사용량과 동시 Connection 수를 고려하여 Work Memory 크기를 신중하게 조정한다.
함께 보면 좋은 아티클
