Aurora PostgreSQL 실행 계획 유지 관리 - Amazon Aurora
계획 성능 평가계획 검증pg_hint_plan을 사용하여 계획 수정계획 삭제계획 내보내기/가져오기

Aurora PostgreSQL 실행 계획 유지 관리

쿼리 계획 관리는 실행 계획을, 추가, 유지 관리 및 개선할 수 있는 기술과 함수를 제공합니다.

계획 성능 평가

최적화 프로그램이 계획을 미승인 계획으로 캡처한 후 apg_plan_mgmt.evolve_plan_baselines 함수를 사용하여 실제 성능에 따라 계획을 비교합니다. 성능 실험의 결과에 따라 승인되지 않음에서 승인됨 또는 거부됨으로 계획의 상태를 변경할 수 있습니다. 대신에 apg_plan_mgmt.evolve_plan_baselines 함수를 사용하여 요구 사항을 충족하지 않을 경우 계획을 일시적으로 비활성화하도록 결정할 수 있습니다.

더 나은 계획 승인

다음 예제에서는 apg_plan_mgmt.evolve_plan_baselines 함수를 사용하여 관리형 계획의 상태를 승인됨으로 변경하는 방법을 보여줍니다.

SELECT apg_plan_mgmt.evolve_plan_baselines (
   sql_hash, 
   plan_hash, 
   min_speedup_factor := 1.0, 
   action := 'approve'
) 
FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';
NOTICE:     rangequery (1,10000)
NOTICE:     Baseline   [ Planning time 0.761 ms, Execution time 13.261 ms]
NOTICE:     Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms]
NOTICE:     Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms
NOTICE:     Unapproved -> Approved
evolve_plan_baselines 
-----------------------
0
(1 row)

출력에 파라미터 바인딩이 1과 10,000인 rangequery 문에 대한 성능 보고서가 표시됩니다. 새로운 미승인 계획(Baseline+1)이 이전에 승인된 계획 최상의 계획(Baseline)보다 더 낫습니다. 새 계획이 Approved 상태인지 확인하려면 apg_plan_mgmt.dba_plans 보기를 확인합니다.

SELECT sql_hash, plan_hash, status, enabled, stmt_name 
FROM apg_plan_mgmt.dba_plans;
sql_hash  | plan_hash |  status  | enabled | stmt_name  
------------+-----------+----------+---------+------------
1984047223 | 512153379 | Approved | t       | rangequery
1984047223 | 512284451 | Approved | t       | rangequery
(2 rows)

이제 관리형 계획에 설명문의 계획 기준인 승인된 계획 두 개가 포함됩니다. 또한 apg_plan_mgmt.set_plan_status 함수를 호출하여 계획의 상태 필드를 'Approved', 'Rejected', 'Unapproved' 또는 'Preferred'로 직접 설정할 수도 있습니다.

느린 계획 거부 또는 비활성화

계획을 거부하거나 비활성화하려면 'reject' 또는 'disable' 을 apg_plan_mgmt.evolve_plan_baselines 함수에 작업 파라미터로 전달합니다. 이 예제에서는 해당 문에 대한 최상의 Unapproved 계획보다 최소 10% 더 느린 캡처된 Approved 계획을 비활성화합니다.

SELECT apg_plan_mgmt.evolve_plan_baselines(
sql_hash,  -- The managed statement ID
plan_hash, -- The plan ID
1.1,       -- number of times faster the plan must be 
'disable'  -- The action to take. This sets the enabled field to false.
)
FROM apg_plan_mgmt.dba_plans
WHERE status = 'Unapproved' AND   -- plan is Unapproved
origin = 'Automatic';       -- plan was auto-captured

또한 계획을 거부됨 또는 비활성화됨으로 즉시 설정할 수도 있습니다. 계획의 활성화 필드를 true 또는 false로 즉시 설정하려면 apg_plan_mgmt.set_plan_enabled 함수를 호출합니다. 계획의 상태 필드를 'Approved', 'Rejected', 'Unapproved' 또는 'Preferred'로 즉시 설정하려면 apg_plan_mgmt.set_plan_status 함수를 호출합니다.

계획 검증

apg_plan_mgmt.validate_plans 함수를 사용하여 유효하지 않은 계획을 삭제하거나 비활성화할 수 있습니다.

인덱스나 테이블 같은 종속된 객체가 제거되면 계획이 유효하지 않게 되거나 기한 경과 상태가 될 수 있습니다. 하지만 제거된 객체가 다시 생성되는 경우에는 계획이 실시적으로 잘못될 수 있습니다. 유효하지 않은 계획이 나중에 유효 상태가 될 수 있는 경우 유효하지 않은 계획을 삭제하기 보다는 비활성화하거나 아무 작업도 하지 않는 것이 더 나을 수 있습니다.

유효하지 않고 지난 주에 사용한 적이 없는 모든 계획을 찾아서 삭제하려면 다음과 같이 apg_plan_mgmt.validate_plans 함수를 사용합니다.

SELECT apg_plan_mgmt.validate_plans(sql_hash, plan_hash, 'delete') 
FROM apg_plan_mgmt.dba_plans
WHERE last_used < (current_date - interval '7 days');

계획을 직접 활성화하거나 비활성화하려면 apg_plan_mgmt.set_plan_enabled 함수를 사용합니다.

pg_hint_plan을 사용하여 계획 수정

쿼리 최적화 프로그램은 모든 설명문에 대한 최적 계획을 찾도록 설계되었으며, 대부분의 경우 최적화 프로그램은 좋은 계획을 찾아냅니다. 하지만 경우에 따라 최적화 프로그램에서 생성된 것보다 훨씬 더 나은 계획이 존재한다는 것을 알게 될 수도 있습니다. 최적화 프로그램을 통해 원하는 계획을 생성하기 위한 두 가지 권장 방법은 PostgreSQL에서 pg_hint_plan 확장을 사용하거나 Grand Unified Configuration(GUC) 변수를 설정하는 것입니다.

  • pg_hint_plan 확장 – PostgreSQL의 pg_hint_plan 확장을 사용하여 플래너의 작동 방식을 수정하려면 "힌트(hint)"를 지정합니다. pg_hint_plan 확장을 설치하고 사용하는 방법은 pg_hint_plan 설명서를 참조하십시오.

  • GUC 변수 – 하나 이상의 비용 모델 파라미터 또는 다른 최적화 프로그램 파라미터(예: from_collapse_limit 또는 GEQO_threshold)를 재정의합니다.

이러한 기술 중 하나를 사용하여 쿼리 최적화 프로그램에서 계획을 사용하도록 지정할 경우 쿼리 계획 관리를 사용하여 새 계획을 캡처하고 사용하도록 설정할 수도 있습니다.

pg_hint_plan 확장을 사용하여 SQL 문의 조인 순서, 조인 방법 또는 액세스 경로를 변경할 수 있습니다. 특수 pg_hint_plan 구문과 함께 SQL 설명을 사용하여 최적화 프로그램에서 계획을 생성하는 방식을 수정할 수 있습니다. 예를 들어 문제의 SQL 문에 양방향 조인이 있다고 가정해 보겠습니다.

SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;

또한 최적화 프로그램에서는 조인 순서(t1, t2)를 선택하지만 조인 순서(t2, t1)가 더 빠름을 알고 있다고 가정해 보겠습니다. 다음 힌트는 최적화 프로그램에서 더 빠른 조인 순서(t2, t1)를 사용하도록 지정합니다. 최적화 프로그램에서 SQL 문에 대한 계획을 생성하되 해당 문을 실행하지 않도록 EXPLAIN을 포함합니다. (출력이 표시되지 않음)

/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;

다음 단계에서는 pg_hint_plan을 사용하는 방법을 보여줍니다.

최적화 프로그램의 생성된 계획을 수정하고 pg_hint_plan을 사용하여 계획을 캡처하려면

  1. 수동 캡처 모드를 켭니다.

    SET apg_plan_mgmt.capture_plan_baselines = manual;

  2. 관심 SQL 문에 대한 힌트를 지정합니다.

    /*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;

    이 실행 후 최적화 프로그램이 apg_plan_mgmt.dba_plans 보기에서 해당 계획을 캡처합니다. 캡처한 계획은 특수 pg_hint_plan 설명 구문을 포함하지 않습니다. 쿼리 계획 관리 기능이 선행 설명을 제거하여 설명문을 정규화하기 때문입니다.

  3. apg_plan_mgmt.dba_plans 보기를 사용하여 관리형 계획을 확인합니다.

    SELECT sql_hash, plan_hash, status, sql_text, plan_outline 
FROM apg_plan_mgmt.dba_plans;

  4. 계획의 상태를 Preferred로 설정합니다. 그러면 최적화 프로그램에서 최소 비용 계획이 아직 Approved 또는 Preferred가 아닐 때 승인된 계획 세트에서 선택하는 대신 해당 계획을 실행합니다.

    SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' );

  5. 수동 계획 캡처를 끄고 관리형 계획을 사용하도록 설정합니다.

    SET apg_plan_mgmt.capture_plan_baselines = false;
SET apg_plan_mgmt.use_plan_baselines = true;

    이제는 원본 SQL 문이 실행될 때 최적화 프로그램이 Approved 또는 Preferred 계획을 선택합니다. 최소 비용 계획이 Approved 또는 Preferred가 아니면 최적화 프로그램은 Preferred 계획을 선택합니다.

계획 삭제

한 달 이상, 구체적으로 32일 동안 사용하지 않은 계획은 자동으로 삭제됩니다. 이것이 apg_plan_mgmt.plan_retention_period 파라미터의 기본 설정입니다. 값을 1부터 시작해서 계획 보존 기간을 더 긴 기간으로 변경하거나 더 짧은 기간으로 변경할 수 있습니다. 현재 날짜에서 last_used 날짜를 빼서 계획의 마지막으로 사용된 이후 일수를 계산합니다. last_used 날짜는 최적화 프로그램이 계획을 최소 비용 계획으로 선택했거나 계획이 실행된 가장 최근 날짜입니다. 계획의 날짜가 apg_plan_mgmt.dba_plans 보기에 저장됩니다.

장시간 사용하지 않았거나 유용하지 않은 계획을 삭제하는 것이 좋습니다. 최적화 프로그램이 계획을 실행하거나 해당 계획을 문에 대한 최소 비용 계획으로 선택할 때마다 최적화 프로그램이 업데이트하는 last_used 날짜가 각 계획에 지정되어 있습니다. 안전하게 삭제할 수 있는 계획을 확인하려면 마지막 last_used 날짜를 확인하세요.

다음 쿼리는 총 계획 수, 삭제에 실패한 계획 및 성공적으로 삭제된 계획이 포함된 3열 테이블을 반환합니다. 여기에는 apg_plan_mgmt.delete_plan 함수를 사용하여 지난 31일 동안 최소 비용 계획으로 선택되지 않았으며 Rejected 상태가 아닌 모든 계획을 삭제하는 방법의 예인 중첩 쿼리가 있습니다.

SELECT (SELECT COUNT(*) from apg_plan_mgmt.dba_plans) total_plans,
       COUNT(*) FILTER (WHERE result = -1) failed_to_delete,
       COUNT(*) FILTER (WHERE result = 0) successfully_deleted
       FROM (
            SELECT apg_plan_mgmt.delete_plan(sql_hash, plan_hash) as result
            FROM apg_plan_mgmt.dba_plans
            WHERE last_used < (current_date - interval '31 days')
            AND status <> 'Rejected'
            ) as dba_plans ;   


 total_plans | failed_to_delete | successfully_deleted
-------------+------------------+----------------------
           3 |                0 |                    2

자세한 내용은 apg_plan_mgmt.delete_plan 섹션을 참조하세요.

현재 유효하지 않고 앞으로도 유효하지 않을 것으로 예상되는 계획을 삭제하려면 apg_plan_mgmt.validate_plans 함수를 사용하세요. 이 함수를 사용하면 유효하지 않은 계획을 삭제하거나 비활성화할 수 있습니다. 자세한 내용은 계획 검증 섹션을 참조하세요.

중요

유효하지 않은 계획을 정리하지 않을 경우 결국에는 쿼리 계획 관리용으로 별도 보관되어 있는 공유 메모리가 부족해질 수 있습니다. 관리형 계획에 사용할 수 있는 메모리의 양을 제어하려면 apg_plan_mgmt.max_plans 파라미터를 사용합니다. 사용자 지정 DB 파라미터 그룹에서 이 파라미터를 설정하고 DB 인스턴스를 재부팅하여 변경 내용을 적용합니다. 자세한 내용은 apg_plan_mgmt.max_plans 파라미터를 참조하십시오.

계획 내보내기/가져오기

관리형 계획을 내보내고 다른 DB 인스턴스로 가져올 수 있습니다.

관리형 계획을 내보내려면

권한 있는 사용자는 apg_plan_mgmt.plans 테이블의 일부를 다른 테이블에 복사한 후, pg_dump 명령을 사용하여 저장할 수 있습니다. 다음은 예제입니다.

CREATE TABLE plans_copy AS SELECT * 
FROM apg_plan_mgmt.plans [ WHERE predicates ] ;
% pg_dump --table apg_plan_mgmt.plans_copy -Ft mysourcedatabase > plans_copy.tar
DROP TABLE apg_plan_mgmt.plans_copy;
관리형 계획을 가져오려면

  1. 내보낸 관리형 계획의 .tar 파일을 계획이 복원되어야 할 시스템으로 복사합니다.

  2. pg_restore 명령을 사용하여 tar 파일을 새 테이블로 복사합니다.

    % pg_restore --dbname mytargetdatabase -Ft plans_copy.tar

  3. 다음 예제와 같이 plans_copy 테이블과 apg_plan_mgmt.plans 테이블을 병합합니다.

    참고

    어떤 경우에는 한 버전의 apg_plan_mgmt 확장에서 덤프하여 다른 버전으로 복원할 수 있습니다. 이 경우 계획 테이블의 열은 다를 수 있습니다. 다른 경우에는 SELECT *를 사용하는 대신에 열에 명시적으로 이름을 지정합니다.

    INSERT INTO apg_plan_mgmt.plans SELECT * FROM plans_copy
 ON CONFLICT ON CONSTRAINT plans_pkey
 DO UPDATE SET
 status = EXCLUDED.status,
 enabled = EXCLUDED.enabled,
 -- Save the most recent last_used date 
 --
 last_used = CASE WHEN EXCLUDED.last_used > plans.last_used 
 THEN EXCLUDED.last_used ELSE plans.last_used END, 
 -- Save statistics gathered by evolve_plan_baselines, if it ran:
 --
 estimated_startup_cost = EXCLUDED.estimated_startup_cost,
 estimated_total_cost = EXCLUDED.estimated_total_cost,
 planning_time_ms = EXCLUDED.planning_time_ms,
 execution_time_ms = EXCLUDED.execution_time_ms,
 total_time_benefit_ms = EXCLUDED.total_time_benefit_ms, 
 execution_time_benefit_ms = EXCLUDED.execution_time_benefit_ms;

  4. 관리형 계획을 공유 메모리에 다시 로드하고 임시 계획 테이블을 제거합니다.

    SELECT apg_plan_mgmt.reload(); -- refresh shared memory
DROP TABLE plans_copy;