PostgreSQL Temp file 관련 정리 (Temp tablespace)

Temp file?

 Postgresql에서 Temporary table/Sort/Hash Operation 등의 작업 처리 시에 temp_buffers, work_mem에 허용된 사이즈 만큼 메모리를 사용한다.
 이를 초과하는 작업에 대해서 임시로 디스크에 temp file을 생성하여 처리한다. (like 오라클 temp segment)
이 때, temp file을 읽고 쓰기 위해 디스크 I/O가 발생되며 디스크 I/O는 메모리 처리보다 느리기 때문에.. In-Memory 작업 대비 속도 저하, 추가된 Temp file 의 파일시스템 사용량 등을 고려해야한다.

• 언제 생성이 될까?
  • 위에 정리했듯이 CREATE TEMPORARY TABLE 처리 중 temp_buffers 설정 값을 초과하거나, QUERY 수행 중 발생하는 SORT/HASH 작업이 work_mem 설정 값을 초과 할 때 생성된다. (temp_buffers와 work_mem 은 아래 설명)
  • 일반적으로 order by, distinct나 merge/hash join 간 발생하는 정렬 작업 시 생성된다고 보면 된다.
• 어디에 생성이 될까?
  • default는 default_tablespace에 저장된다. 보통 default_tablespace 설정을 하지 않고 운영하므로, $PGDATA 쪽에 저장된다.
• $PGDATA 꽉차면 어떡하지? DATA 영역과 분리 할 수는 없을까?
  • temp_tablespaces 파라미터로 조정이 가능하다. 아래 변경방법 참고.

-- 적당한 마운트 포인트 설정
mkdir /postgres_temp
-- TEMP TABLESPACE 설정
create tablespace temp_tbs location '/postgres_temp';
alter system set temp_tablespaces = 'temp_tbs';
select pg_reload_conf();

 

• temp_file 최대 사이즈를 걸 수 없을까?
  
  • 최대 사이즈는 temp_file_limit 파라미터로 조정가능하다.
  • 대용량 sorting 시 파일 시스템 부족 현상이 생길 수 있으니 적절히 제한을 둘 필요가 있다.
• 로깅 방법
  • log_temp_files 파라미터로 temp 파일을 쓰는 쿼리에 대해 로깅이 가능하다.
  • 비정상적으로 temp 파일을 많이 생성하는 쿼리를 식별하기 위해 켜둘 필요가 있다.
• 오라클의 temp extent 와 차이점
  • PostgreSQL은 temp file 사용 공간을 커넥션 종료 시 OS에 반납한다. 반면, 오라클은 쿼리 수행이 끝나도 temp 영역을 OS에 반환 하지 않고 계속 유지함

Temp 관련 파라미터 설명

위 그림의 Backend process는 커넥션 발생 시에 생성되는 프로세스로, 각 커넥션별로 temp_buffers, work_mem, maintenance_work_mem 이 할당 될 수 있다. (실제 수행 전까지는 점유 X)

• temp_buffers (integer)
  • CREATE TEMPORARY TABLE 생성 시 각 세션 별 제한주는 파라미터다.
  • default : 1024 (단위가 8KB이므로, 8MB)
• work_mem (integer)
  • 중요!
  • SORT/HASH OPERATION 활용되는 각 세션의 오퍼레이션 별 메모리 사이즈를 제한주는 파라미터
  • default : 4096 (단위가 1KB이므로, 4MB)
  • 운영 시엔 Physical mem / (max_connections * 2) 정도로 설정
  • ex) explain analyze 에 "Sort Method: external merge Disk: 7528kB" 해당 플랜이 있다면 work_mem=8MB 적어도 8MB가 있어야 IN-MEMORY 작업가능하다고 보면됨.
  • 세션레벨에서 조정가능하니 튜닝할때도 사용되는 파라미터
• temp_file_limit (integer)
  • 디스크에 생성되는 temp file 전체 사이즈를 제한하는 파라미터
  • default : -1. 제한없음
  • 운영 환경마다 다르니 파일시스템 여유량과 log_temp_files 를 설정하여 모니터링을 해서 적정값 설정하여 사용

요약

• 운영계라면 DATA영역 FULL을 방지하기 위해 temp_file_limit은 적당하게 꼭 걸어두자.
• temp_file_limit에 걸려 temp file 생성이 필요한 다른 트랜잭션이 실패할 수 있으니, 비효율/비정상 쿼리인지 log_temp_file 로그를 확인해 튜닝하자.
• DATA 영역과 temp tablespace를 분리하여 파일시스템 FULL로 인한 엔진 장애를 사전에 방지 할 수 있어보인다.

---

참고 게시물

1. Architecture and Tuning of Memory in PostgreSQL Databases.
2. About temp_tablespaces in PostgreSQL