IO:BufFileRead 및 IO:BufFileWrite

IO:BufFileRead 및 IO:BufFileWrite 이벤트는 RDS for PostgreSQL이 임시 파일을 만들 때 발생합니다. 연산에 현재 정의된 작업 메모리 파라미터보다 많은 메모리가 필요한 경우 임시 데이터를 영구 스토리지에 씁니다. 이 작업을 '디스크로 유출'이라고도 합니다.

지원되는 엔진 버전

이 대기 이벤트 정보는 모든 RDS for PostgreSQL 버전에서 지원됩니다.

컨텍스트

IO:BufFileRead와 IO:BufFileWrite는 작업 메모리 영역 및 유지 관리 작업 메모리 영역과 관련이 있습니다. 이러한 로컬 메모리 영역에 대한 자세한 내용은 PostgreSQL 설명서에서 리소스 소비를 참조하세요.

기본값은 work_mem 또는 4MB입니다. 한 세션이 병렬로 연산을 수행하는 경우 병렬 처리를 처리하는 각 작업자는 4MB의 메모리를 사용합니다. 이런 이유로, work_mem을 신중하게 설정하세요. 값을 너무 많이 늘리면 많은 세션을 실행하는 데이터베이스가 너무 많은 메모리를 소비할 수 있습니다. 값을 너무 낮게 설정하면 RDS for PostgreSQL이 로컬 스토리지에 임시 파일을 만듭니다. 이러한 임시 파일의 디스크 I/O는 성능을 저하시킬 수 있습니다.

다음과 같은 일련의 이벤트를 관찰하면 데이터베이스에서 임시 파일이 생성될 수 있습니다.

1. 갑작스럽고 급격한 가용성 감소

2. 여유 공간을 위한 신속한 복구

'체인톱' 패턴도 볼 수 있습니다. 이 패턴은 데이터베이스에서 지속적으로 작은 파일을 생성한다는 것을 의미할 수 있습니다.

대기 증가의 가능한 원인

일반적으로 이러한 대기 이벤트는 work_mem 또는 maintenance_work_mem 파라미터가 할당하는 것보다 더 많은 메모리를 소모하는 연산에 의해 발생합니다. 보정을 위해 연산은 임시 파일에 기록합니다. IO:BufFileRead와 IO:BufFileWrite 이벤트의 일반적인 원인에는 다음이 포함됩니다.

작업 메모리 영역에 존재하는 것보다 많은 메모리가 필요한 쿼리

다음 특성을 가진 쿼리는 작업 메모리 영역을 사용합니다.

• 해시 조인.

• ORDER BY 절

• GROUP BY 절

• DISTINCT

• 윈도 함수

• CREATE TABLE AS SELECT

• 구체화 뷰 새로고침

작업 메모리 영역에 존재하는 것보다 많은 메모리가 필요한 명령문

다음 명령문에서는 유지 관리 작업 메모리 영역을 사용합니다.

• CREATE INDEX

• CLUSTER

작업

대기 이벤트의 원인에 따라 다른 작업을 권장합니다.

주제

• 문제 식별
• 조인 쿼리 검토
• ORDER BY와 GROUP BY 쿼리를 검토합니다.
• DISTINCT 연산 사용을 피하세요.
• GROUP BY 함수 대신 윈도 함수를 사용하는 것이 좋습니다.
• 구체화된 뷰 및 CTAS 명령문 조사
• 인덱스를 다시 구축할 때 pg_repack 사용
• 테이블을 클러스터링할 때 maintenance_work_mem 증가
• 메모리를 조정하여 IO:BufFileRead 및 IO:BufFileWrite 방지

문제 식별

성능 개선 도우미가 켜져 있지 않고 IO:BufFileRead와 IO:BufFileWrite가 정상보다 더 자주 발생한다고 의심되는 상황을 가정합니다. 문제의 원인을 식별하기 위해 지정한 임계값 KB를 초과하는 임시 파일을 생성하는 모든 쿼리를 로깅하도록 log_temp_files 파라미터를 설정할 수 있습니다. 기본적으로 log_temp_files는 -1로 설정되어 있어 이 로깅 기능을 끕니다. 이 파라미터를 0로 설정하면 RDS for PostgreSQL이 모든 임시 파일을 로깅합니다. 1024으로 설정하면 RDS for PostgreSQL은 1MB보다 큰 임시 파일을 생성하는 모든 쿼리를 로깅합니다. log_temp_files에 관한 자세한 내용은 PostgreSQL 문서의 오류 보고 및 로깅을 참조하세요.

조인 쿼리 검토

쿼리에서 조인이 사용될 가능성이 높습니다. 예를 들어 다음 쿼리는 네 개의 테이블을 조인합니다.

SELECT * 
       FROM "order" 
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = order.customer_id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

임시 파일 사용량 스파이크의 원인은 쿼리 자체의 문제입니다. 예를 들어, 끊어진 절은 조인을 제대로 필터링하지 않을 수 있습니다. 다음 예에서 두 번째 내부 조인을 고려해 보겠습니다.

SELECT * 
       FROM "order"
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = customer.id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

위의 쿼리가 실수로 customer.id를 customer.id에 조인하여 모든 고객과 모든 주문 사이에 데카르트 프로덕트를 생성합니다. 이러한 유형의 우발적인 조인은 큰 임시 파일을 생성합니다. 테이블의 크기에 따라 테카르트 쿼리(Cartesian query)는 스토리지를 채울 수도 있습니다. 다음 조건이 충족되면 애플리케이션에 데카르트 조인(Cartesian join)이 있을 수 있습니다.

• 스토리지 가용성이 급격히, 크게 줄어들고 복구가 빨라집니다.

• 인덱스가 생성되지 않습니다.

• CREATE TABLE FROM SELECT 문이 발행되고 있지 않습니다.

• 구체화 뷰가 새로 고침이 되지 않습니다.

적절한 키를 사용하여 테이블이 조인되고 있는지 확인하려면 쿼리 및 객체 관계형 매핑 지시어를 검사합니다. 애플리케이션의 특정 쿼리가 항상 호출되는 것은 아니며 일부 쿼리는 동적으로 생성됩니다.

ORDER BY와 GROUP BY 쿼리를 검토합니다.

경우에 따라 ORDER BY 절에 임시 파일이 과도하게 발생할 수 있습니다. 다음 지침을 참고하세요.

• 정렬이 필요할 때 ORDER BY 절의 열만 포함합니다. 이 지침은 ORDER BY 절에서 수천 개의 행을 반환하고 많은 열을 지정하는 쿼리에 특히 중요합니다.

• 오름차순 또는 내림차순이 동일한 열과 일치할 때 ORDER BY 절을 가속하기 위해 인덱스를 생성하는 것이 좋습니다. 부분 인덱스는 작기 때문에 선호됩니다. 작은 인덱스를 더 빠르게 읽고 탐색할 수 있습니다.

• null 값을 허용할 수 있는 열에 대한 인덱스를 만드는 경우 null 값을 인덱스의 끝에 저장할지 아니면 인덱스의 시작 부분에 저장할지 고려해야 합니다.

  가능한 경우 결과 집합을 필터링하여 정렬해야 하는 행 수를 줄입니다. WITH 절의 명령문 또는 하위 쿼리를 사용할 경우, 내부 쿼리가 결과 집합을 생성하여 외부 쿼리로 전달한다는 것을 기억하세요. 쿼리가 필터링할 수 있는 행이 많을수록 쿼리 정렬이 할 일이 줄어듭니다.

• 전체 결과 집합을 가져올 필요가 없는 경우 LIMIT 절을 사용하세요. 예를 들어 상위 5개 행만 원하는 경우 LIMIT 절을 사용하는 쿼리는 결과를 계속 생성하지 않습니다. 이렇게 하면 쿼리에 메모리와 임시 파일이 더 적게 필요합니다.

GROUP BY 절을 사용하는 쿼리는 임시 파일이 필요할 수도 있습니다. GROUP BY 쿼리는 다음과 같은 함수를 사용하여 값을 요약합니다.

• COUNT

• AVG

• MIN

• MAX

• SUM

• STDDEV

GROUP BY 쿼리를 튜닝하려면 ORDER BY 쿼리의 권장 사항을 따르세요.

DISTINCT 연산 사용을 피하세요.

가능하면 중복된 행을 제거하기 위해 DISTINCT 연산을 사용하지 마세요. 쿼리가 반환하는 불필요하고 중복된 행이 많을수록 DISTINCT 연산에 시간이 오래 걸리게 됩니다. 가능하면 WHERE 절에 필터를 추가합니다. 다른 테이블에 동일한 필터를 사용하는 경우에도 마찬가지입니다. 쿼리를 필터링하고 조인하면 성능이 향상되고 리소스 사용이 줄어듭니다. 또한 잘못된 보고와 결과를 방지할 수 있습니다.

DISTINCT를 동일 테이블의 여러 행에서 사용해야 하는 경우 복합 인덱스를 만드는 것이 좋습니다. 인덱스에서 여러 열을 그룹화하면 고유한 행을 평가하는 시간을 단축할 수 있습니다. 또한 RDS for PostgreSQL 버전 10 이상을 사용하는 경우, CREATE STATISTICS 명령을 통해 다중 열의 통계를 상호 연관시킬 수 있습니다.

GROUP BY 함수 대신 윈도 함수를 사용하는 것이 좋습니다.

GROUP BY를 사용하여 결과 집합을 변경한 다음 집계된 결과를 검색합니다. 윈도 함수를 사용하면 결과 집합을 변경하지 않고 데이터를 집계할 수 있습니다. 윈도 함수는 OVER 절을 통해 다른 행과 상호 연관시키는 쿼리에 의해 정의된 집합에서 계산을 수행합니다. 윈도 함수의 모든 GROUP BY 함수를 사용할 수 있으며, 다음과 같은 함수도 사용할 수 있습니다.

• RANK

• ARRAY_AGG

• ROW_NUMBER

• LAG

• LEAD

윈도 함수에 의해 생성된 임시 파일 수를 최소화하려면 두 개의 별개의 집계가 필요할 때 동일한 결과 집합에 대한 중복을 제거합니다. 다음과 같은 쿼리를 가정하겠습니다.

SELECT sum(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as sum_salary
     , avg(salary) OVER (PARTITION BY dept ORDER BY salary ASC) as avg_salary
  FROM empsalary;

WINDOW 절을 사용하여 다음과 같이 쿼리를 다시 작성할 수 있습니다.

SELECT sum(salary) OVER w as sum_salary
         , avg(salary) OVER w as_avg_salary
    FROM empsalary
  WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

기본적으로 RDS for PostgreSQL 실행 플래너는 유사한 노드를 통합하므로 연산을 복제하지 않습니다. 그러나 윈도 블록에 대한 명시적 선언을 사용하면 쿼리를 보다 쉽게 유지할 수 있습니다. 또한 중복을 방지하여 성능을 향상시킬 수 있습니다.

구체화된 뷰 및 CTAS 명령문 조사

구체화된 뷰가 새로 고쳐지면 쿼리가 실행됩니다. 이 쿼리에는 GROUP BY, ORDER BY, 또는 DISTINCT 같은 연산이 포함될 수 있습니다. 새로 고침을 하는 동안 많은 수의 임시 파일과 IO:BufFileWrite 및 IO:BufFileRead 대기 이벤트를 관찰할 수 있습니다. 마찬가지로, SELECT 문을 기반으로 테이블을 생성할 때도 마찬가지입니다. CREATE TABLE 문은 쿼리를 실행합니다. 필요한 임시 파일을 줄이려면 쿼리를 최적화하세요.

인덱스를 다시 구축할 때 pg_repack 사용

인덱스를 생성하면 엔진이 결과 세트를 정렬합니다. 테이블의 크기가 커지고 인덱싱된 열의 값이 다양해짐에 따라 임시 파일에 더 많은 공간이 필요합니다. 대부분의 경우 유지 관리 작업 메모리 영역을 수정하지 않으면 큰 테이블에 대한 임시 파일이 생성되지 않도록 할 수 없습니다. maintenance_work_mem에 대한 자세한 내용은 PostgreSQL 설명서의 https://www.postgresql.org/docs/current/runtime-config-resource.html을 참조하십시오.

큰 인덱스를 다시 생성할 때 가능한 해결 방법은 pg_repack 확장을 사용하는 것입니다. 자세한 내용은 pg_repack 문서의 최소한의 잠금으로 PostgreSQL 데이터베이스의 테이블 재구성을 참조하세요. RDS for PostgreSQL DB 인스턴스에서 확장을 설정하는 방법은 pg_repack 확장을 사용하여 테이블 및 인덱스에서 부풀림을 줄입니다. 섹션을 참조하세요.

테이블을 클러스터링할 때 maintenance_work_mem 증가

CLUSTER 명령은 index_name에 의해 지정된 기존 인덱스를 기반으로 하는 table_name에 의해 지정된 테이블을 클러스터링합니다. RDS for PostgreSQL은 지정된 인덱스의 순서와 일치하도록 테이블을 물리적으로 다시 만듭니다.

마그네틱 스토리지가 널리 보급되었을 때 스토리지 처리량이 제한적이었기 때문에 클러스터링이 일반적이었습니다. SSD 기반 스토리지가 일반적이므로 클러스터링은 인기가 덜합니다. 그러나 테이블을 클러스터링하는 경우에도 테이블 크기, 인덱스, 쿼리 등에 따라 성능이 약간 향상될 수 있습니다.

CLUSTER 명령을 실행하거나 IO:BufFileWrite 및 IO:BufFileRead 대기 이벤트를 관찰할 수 있는 경우, maintenance_work_mem을 조정합니다. 메모리 크기를 상당히 크게 늘립니다. 값이 높으면 엔진이 클러스터링 작업에 더 많은 메모리를 사용할 수 있음을 의미합니다.

메모리를 조정하여 IO:BufFileRead 및 IO:BufFileWrite 방지

일부 상황에서는 메모리를 조정해야 합니다. 목표는 다음과 같이 적절한 파라미터를 사용하여 다음 소비 영역에서 메모리 균형을 맞추는 것입니다.

• work_mem 값

• shared_buffers 값 디스카운트 후 남은 메모리

• 열리고 사용 중인 최대 연결은 max_connections로 제한됩니다.

메모리 조정에 대한 자세한 내용은 PostgreSQL 설명서에서 리소스 소비를 참조하세요.

작업 메모리 영역 크기 증가

경우에 따라 세션에서 사용하는 메모리를 늘리는 것이 유일한 방법입니다. 쿼리가 올바르게 작성되고 조인에 올바른 키를 사용하는 경우 work_mem 값을 증가시키는 것이 좋습니다.

쿼리가 생성하는 임시 파일 수를 확인하려면 log_temp_files를 0에 설정하세요. work_mem 값을 로그에서 식별된 최대값으로 늘리면 쿼리가 임시 파일을 생성하지 못하도록 합니다. 하지만 work_mem은 각 연결 또는 병렬 워커에 대해 계획 노드당 최대값을 설정합니다. 데이터베이스에 5,000개의 연결이 있고 각 연결이 256MiB 메모리를 사용하는 경우 엔진에 1.2TiB의 RAM이 필요합니다. 따라서 인스턴스의 메모리가 부족할 수 있습니다.

공유 버퍼 풀에 충분한 메모리 예약

데이터베이스는 작업 메모리 영역뿐만 아니라 공유 버퍼 풀과 같은 메모리 영역을 사용합니다. work_mem을 늘리기 전에 이러한 추가 메모리 영역의 요구 사항을 고려하는 것이 좋습니다.

예를 들어 RDS for PostgreSQL 인스턴스 클래스가 db.r5.2xlarge라고 가정합니다. 이 클래스에는 64GiB의 메모리가 있습니다. 기본적으로 메모리의 25%는 공유 버퍼 풀에 예약되어 있습니다. 공유 메모리 영역에 할당된 양을 빼면 16,384MB가 남아 있습니다. 운영 체제와 엔진에도 메모리가 필요하므로 나머지 메모리를 작업 메모리 영역에만 할당하지 마세요.

work_mem에 할당할 수 있는 메모리는 인스턴스 클래스에 따라 달라집니다. 더 큰 인스턴스 클래스를 사용하는 경우 더 많은 메모리를 사용할 수 있습니다. 하지만 앞의 예에서는 16GiB를 초과하여 사용할 수 없습니다. 그렇지 않으면 메모리가 부족할 때 인스턴스를 사용할 수 없게 됩니다. 인스턴스를 사용할 수 없는 상태에서 복구하기 위해 RDS for PostgreSQL 자동화 서비스가 자동으로 다시 시작됩니다.

연결 수 관리

데이터베이스 인스턴스에 5,000개의 동시 연결이 있다고 가정합니다. 각 연결은 최소 4MiB의 work_mem을 사용합니다. 연결의 메모리 소비량이 많으면 성능이 저하될 수 있습니다. 다음과 같은 옵션이 있습니다.

• vCPU가 더 많은 대규모 인스턴스 클래스로 업그레이드하세요.

• 연결 프록시 또는 풀러를 사용하여 동시 데이터베이스 연결 수를 줄이세요.

프록시의 경우 Amazon RDS 프록시, PGBouncer 또는 애플리케이션에 기반한 연결 풀러를 고려하세요. 이 솔루션은 CPU 부하를 완화합니다. 또한 모든 연결에 작업 메모리 영역이 필요할 때 위험을 줄일 수 있습니다. 데이터베이스 연결 수가 적으면 work_mem 값을 늘릴 수 있습니다. 이런 방법으로 IO:BufFileRead와 IO:BufFileWrite 대기 이벤트의 발생을 줄일 수 있습니다. 또한 작업 메모리 영역을 기다리는 쿼리의 속도가 크게 향상됩니다.