PostgreSQL을 사용한 임시 파일 관리 - Amazon Aurora

PostgreSQL을 사용한 임시 파일 관리

PostgreSQL에서 정렬 및 해시 작업을 수행하는 쿼리는 인스턴스 메모리를 사용하여 work_mem 파라미터에 지정된 값까지 결과를 저장합니다. 인스턴스 메모리가 충분하지 않은 경우 결과를 저장하기 위한 임시 파일이 생성됩니다. 이러한 임시 파일은 쿼리 실행을 완료하기 위해 디스크에 기록됩니다. 나중에 이러한 파일은 쿼리가 완료된 후 자동으로 제거됩니다. Aurora PostgreSQL에서 이러한 파일은 로컬 스토리지를 다른 로그 파일과 함께 공유합니다. FreeLocalStorage용 Amazon CloudWatch 지표를 관찰하면 Aurora PostgreSQL DB 클러스터의 로컬 스토리지 공간을 모니터링할 수 있습니다. 자세한 내용은 로컬 스토리지 문제 해결을 참조하세요.

다음과 같은 파라미터와 함수를 사용하여 인스턴스의 임시 파일을 관리할 수 있습니다.

  • temp_file_limit - 이 파라미터는 temp_files 크기(KB)를 초과하는 모든 쿼리를 취소합니다. 이러한 한도는 쿼리가 무한으로 실행되어 임시 파일이 디스크 공간을 사용하는 현상을 방지합니다. log_temp_files 파라미터의 결과를 사용하여 값을 추정할 수 있습니다. 가장 좋은 방법은 워크로드 동작을 검사하고 추정치에 따라 한도를 설정하는 것입니다. 다음 예는 한도를 초과했을 때 쿼리가 취소되는 방식을 보여줍니다.

    postgres=> select * from pgbench_accounts, pg_class, big_table;
    ERROR: temporary file size exceeds temp_file_limit (64kB)
  • log_temp_files - 이 파라미터는 세션의 임시 파일이 제거될 경우 postgresql.log로 메시지를 보냅니다. 이 파라미터는 쿼리가 성공적으로 완료된 후 로그를 생성합니다. 따라서 활성 상태의 장기 실행 쿼리의 문제를 해결하는 데에는 도움이 되지 않을 수도 있습니다.

    다음 예시에서는 쿼리가 성공적으로 완료되었을 때 임시 파일이 정리되는 동안 항목이 postgresql.log 파일에 기록되는 것을 보여줍니다.

    2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.5", size 140353536 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10; 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.4", size 180428800 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10;
  • pg_ls_tmpdir - RDS for PostgreSQL 13 이상에서 제공되는 이 함수를 사용하면 현재 임시 파일 사용량을 확인할 수 있습니다. 완료된 쿼리는 함수 결과에 나타나지 않습니다. 다음 예제에서는 이 함수의 결과를 볼 수 있습니다.

    postgres=> select * from pg_ls_tmpdir();
    name | size | modification -----------------+------------+------------------------ pgsql_tmp8355.1 | 1072250880 | 2023-02-06 22:54:56+00 pgsql_tmp8351.0 | 1072250880 | 2023-02-06 22:54:43+00 pgsql_tmp8327.0 | 1072250880 | 2023-02-06 22:54:56+00 pgsql_tmp8351.1 | 703168512 | 2023-02-06 22:54:56+00 pgsql_tmp8355.0 | 1072250880 | 2023-02-06 22:54:00+00 pgsql_tmp8328.1 | 835031040 | 2023-02-06 22:54:56+00 pgsql_tmp8328.0 | 1072250880 | 2023-02-06 22:54:40+00 (7 rows)
    postgres=> select query from pg_stat_activity where pid = 8355; query ---------------------------------------------------------------------------------------- select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid (1 row)

    파일 이름에는 임시 파일을 생성한 세션의 처리 ID(PID)가 포함됩니다. 다음 예와 같은 고급 쿼리는 각 PID에 대한 임시 파일의 합계를 수행합니다.

    postgres=> select replace(left(name, strpos(name, '.')-1),'pgsql_tmp','') as pid, count(*), sum(size) from pg_ls_tmpdir() group by pid;
    pid | count | sum ------+------------------- 8355 | 2 | 2144501760 8351 | 2 | 2090770432 8327 | 1 | 1072250880 8328 | 2 | 2144501760 (4 rows)
  • pg_stat_statements - pg_stat_statements 파라미터를 활성화하면 호출당 평균 임시 파일 사용량을 볼 수 있습니다. 쿼리의 query_id를 식별하고 이를 사용하여 다음 예와 같이 임시 파일 사용량을 검사할 수 있습니다.

    postgres=> select queryid from pg_stat_statements where query like 'select a.aid from pgbench%';
    queryid ---------------------- -7170349228837045701 (1 row)
    postgres=> select queryid, substr(query,1,25), calls, temp_blks_read/calls temp_blks_read_per_call, temp_blks_written/calls temp_blks_written_per_call from pg_stat_statements where queryid = -7170349228837045701;
    queryid | substr | calls | temp_blks_read_per_call | temp_blks_written_per_call ----------------------+---------------------------+-------+-------------------------+---------------------------- -7170349228837045701 | select a.aid from pgbench | 50 | 239226 | 388678 (1 row)
  • Performance Insights - 성능 개선 도우미 대시보드에서 temp_bytestemp_files 메트릭을 켜서 임시 파일 사용량을 확인할 수 있습니다. 그런 다음, 이 두 지표의 평균을 확인하고 두 지표가 쿼리 워크로드에 어떻게 상응하는지 확인할 수 있습니다. 성능 개선 도우미 내부의 보기에는 임시 파일을 생성 중인 쿼리가 구체적으로 표시되지 않습니다. 그러나 성능 개선 도우미를 pg_ls_tmpdir에 대해 표시된 쿼리와 결합하면 쿼리 워크로드의 문제를 해결하고, 분석하고, 변경 사항을 확인할 수 있습니다.

    성능 개선 도우미를 사용하여 지표를 분석하고 쿼리를 분석하는 방법에 대한 자세한 내용은 성능 개선 도우미 대시보드를 사용한 지표 분석 섹션을 참조하세요.

    성능 개선 도우미를 사용하여 임시 파일 사용량을 보려면
    1. 성능 개선 도우미 대시보드에서 지표 관리를 선택합니다.

    2. 다음 이미지에 나와 있는 것처럼 데이터베이스 지표를 선택하고, temp_bytestemp_files를 선택합니다.

      지표가 그래프로 표시됩니다.
    3. 상위 SQL 탭에서 기본 설정 아이콘을 선택합니다.

    4. 기본 설정 창에서 상위 SQL 탭에 표시할 다음 통계를 켜고 계속을 선택합니다.

      • 임시 쓰기/초

      • 임시 읽기/초

      • 임시 대량 쓰기/호출

      • 임시 대량 읽기/호출

    5. 다음 예제에 나온 것처럼, 임시 파일은 pg_ls_tmpdir에 대해 표시된 쿼리와 함께 통합될 때 분할됩니다.

      임시 파일 사용량을 표시하는 쿼리.

IO:BufFileReadIO:BufFileWrite 이벤트는 대개 워크로드의 상위 쿼리에서 임시 파일이 생성될 때 발생합니다. 데이터베이스 부하 및 상위 SQL 섹션의 AAS(상위 활성 세션)를 검토하여 IO:BufFileReadIO:BufFileWrite에서 대기 중인 상위 쿼리를 식별하는 데 성능 개선 도우미를 사용할 수 있습니다.

그래프에 IO:BufFileRead 및 IO:BufFileWrite가 표시되어 있습니다.

성능 개선 도우미를 사용하여 상위 쿼리와 대기 이벤트에서 부하를 분석하는 방법에 대한 자세한 내용은 상위 SQL(Top SQL) 탭 개요 단원을 참조하세요. 임시 파일 사용량 및 관련 대기 이벤트를 늘리는 쿼리를 식별하고 조정해야 합니다. 이러한 대기 이벤트 및 수정에 대한 자세한 내용은 IO:BufFileRead 및 IO:BufFileWrite를 참조하세요.

참고

work_mem 파라미터는 정렬 작업의 메모리가 부족하여 결과가 임시 파일에 기록되는 시점을 제어합니다. 이 파라미터의 설정을 기본값보다 높게 변경하면 모든 데이터베이스 세션에서 더 많은 메모리를 사용할 수 있으므로 변경하지 않는 것이 좋습니다. 또한 복잡한 조인 및 정렬을 수행하는 단일 세션에서는 각 작업이 메모리를 사용하는 병렬 작업을 수행할 수 있습니다.

여러 조인 및 정렬이 포함된 대용량 보고서가 있을 경우 SET work_mem 명령을 사용하여 세션 수준에서 이 파라미터를 설정하는 것이 가장 좋습니다. 그러면 변경 사항이 현재 세션에만 적용되고 값이 전체적으로 변경되지 않습니다.