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을 사용하여 계획을 캡처하려면
-
수동 캡처 모드를 켭니다.
SET apg_plan_mgmt.capture_plan_baselines = manual;
-
관심 SQL 문에 대한 힌트를 지정합니다.
/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
이 실행 후 최적화 프로그램이
apg_plan_mgmt.dba_plans
보기에서 해당 계획을 캡처합니다. 캡처한 계획은 특수pg_hint_plan
설명 구문을 포함하지 않습니다. 쿼리 계획 관리 기능이 선행 설명을 제거하여 설명문을 정규화하기 때문입니다. -
apg_plan_mgmt.dba_plans
보기를 사용하여 관리형 계획을 확인합니다.SELECT sql_hash, plan_hash, status, sql_text, plan_outline FROM apg_plan_mgmt.dba_plans;
계획의 상태를
Preferred
로 설정합니다. 그러면 최적화 프로그램에서 최소 비용 계획이 아직Approved
또는Preferred
가 아닐 때 승인된 계획 세트에서 선택하는 대신 해당 계획을 실행합니다.SELECT apg_plan_mgmt.set_plan_status(
sql-hash
,plan-hash
, 'preferred' );-
수동 계획 캡처를 끄고 관리형 계획을 사용하도록 설정합니다.
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;
관리형 계획을 가져오려면
-
내보낸 관리형 계획의 .tar 파일을 계획이 복원되어야 할 시스템으로 복사합니다.
-
pg_restore
명령을 사용하여 tar 파일을 새 테이블로 복사합니다.%
pg_restore --dbname mytargetdatabase -Ft plans_copy.tar -
다음 예제와 같이
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;
-
관리형 계획을 공유 메모리에 다시 로드하고 임시 계획 테이블을 제거합니다.
SELECT apg_plan_mgmt.reload();
-- refresh shared memory
DROP TABLE plans_copy;