Amazon Aurora
Aurora 사용 설명서 (API 버전 2014-10-31)

Aurora PostgreSQL용 쿼리 실행 계획 관리

PostgreSQL과 호환되는 Amazon Aurora용 쿼리 계획 관리를 사용하면 쿼리 실행 계획을 변경하는 방식과 변경해야 하는 경우를 제어할 수 있습니다. 쿼리 계획 관리의 주요 목표는 두 가지입니다.

  • 데이터베이스 시스템 변경 시 계획 회귀 방지

  • 쿼리 최적화 프로그램에서 새 계획을 사용할 수 있는 시점 제어

쿼리 최적화의 품질 및 일관성은 관계형 데이터베이스 관리 시스템(RDBMS)의 성능 및 안정성에 큰 영향을 미칩니다. 쿼리 최적화 프로그램은 특정 시점에 SQL 문에 대한 쿼리 실행 계획을 생성합니다. 조건이 바뀜에 따라 최적화 프로그램은 성능을 저하하는 다른 계획을 선택할 수도 있습니다. 다수의 변경 사항으로 인해 쿼리 최적화 프로그램이 다른 계획을 선택하게 되고 결국 성능 역행으로 이어질 수 있습니다. 이러한 변경 사항으로는 통계, 제한 사항, 환경 설정, 쿼리 파라미터 바인딩, 소프트웨어 업그레이드에 대한 변경 사항을 들 수 있습니다. 성능 역행은 고성능 애플리케이션에 중요한 문제입니다.

쿼리 계획 관리 기능을 사용하면 관리하려는 설명문 세트에 대한 실행 계획을 제어할 수 있습니다. 다음을 수행할 수 있습니다.

  • 강제로 최적화 프로그램이 소수의 알려진 정상 계획 중 하나를 선택하도록 하여 계획의 안정성을 높일 수 있습니다.

  • 계획을 중앙에서 최적화한 다음, 최고의 계획을 전역에 배포할 수 있습니다.

  • 사용되지 않는 인덱스를 식별하고 인덱스 생성 및 삭제의 영향을 평가할 수 있습니다.

  • 최적화 프로그램에서 발견한 새로운 최소 비용 계획을 자동으로 감지할 수 있습니다.

  • 성능을 개선하는 계획 변경 사항만 승인하도록 선택할 수 있어 위험이 더 적은 새로운 최적화 프로그램 기능을 사용해 볼 수 있습니다.

Aurora PostgreSQL용 쿼리 계획 관리 활성화

쿼리 계획 관리는 Amazon Aurora PostgreSQL 버전 2.1.0 이상에서 사용할 수 있습니다.

쿼리 계획 관리를 활성화하려면

  1. https://console.aws.amazon.com/rds/에서 Amazon RDS 콘솔을 엽니다.

  2. 쿼리 계획 관리에 사용할 새 인스턴스 수준의 파라미터 그룹을 생성합니다. 자세한 내용은 DB 파라미터 그룹 생성 단원을 참조하십시오.

  3. 쿼리 계획 관리에 사용할 새 클러스터 수준의 파라미터 그룹을 생성합니다. 자세한 내용은 DB 클러스터 파라미터 그룹 만들기 단원을 참조하십시오.

  4. 클러스터 수준의 파라미터 그룹을 열고 rds.enable_plan_management 파라미터를 1로 설정합니다. 자세한 내용은 DB 클러스터 파라미터 그룹의 파라미터 수정 단원을 참조하십시오.

  5. DB 인스턴스를 다시 시작하여 이 새로운 설정을 활성화합니다.

  6. psql과 같은 SQL 클라이언트를 사용하여 DB 인스턴스에 연결합니다.

  7. DB 인스턴스용 apg_plan_mgmt 확장을 생성합니다. 다음은 그 한 예입니다.

    psql my-database my-database=> CREATE EXTENSION apg_plan_mgmt;

    template1 기본 데이터베이스에서 apg_plan_mgmt 확장을 생성할 경우 생성하는 각각의 새 데이터베이스에서 쿼리 계획 관리 확장을 사용할 수 있습니다.

    참고

    apg_plan_mgmt 확장을 생성하려면 rds_superuser 역할이 필요합니다. apg_plan_mgmt 확장을 생성하면 apg_plan_mgmt 역할이 생성됩니다. 사용자가 apg_plan_mgmt 확장을 관리하려면 apg_plan_mgmt 역할을 부여받아야 합니다.

쿼리 계획 관리 업그레이드

쿼리 계획 관리 버전 1.0을 설치한 경우 버전 1.0.1로 업그레이드할 것을 적극 권장합니다.

업그레이드하려면 클러스터 또는 DB 인스턴스 수준에서 다음 명령을 실행하십시오.

ALTER EXTENSION apg_plan_mgmt UPDATE TO '1.0.1'; SELECT apg_plan_mgmt.validate_plans('update_plan_hash'); SELECT apg_plan_mgmt.reload();

쿼리 계획 관리의 기본 사항

설명문의 복잡성과 관계없이 쿼리 계획 관리를 사용하여 SELECT, INSERT, UPDATE 또는 DELETE 문을 관리할 수 있습니다. 준비된 SQL 문, 동적 SQL 문, 포함된 SQL 문 및 즉시 모드 SQL 문이 모두 지원됩니다. 파티셔닝된 테이블, 상속, 행 수준의 보안 및 recursive 공통 테이블 표현식(CTE)을 비롯하여 모든 PostgreSQL 언어 기능을 사용할 수 있습니다.

참고

현재는 PL/pgSQL 함수 내부의 설명문에 대한 계획은 캡처할 수 없습니다.

수동 계획 캡처 수행

특정 설명문에 대한 계획을 캡처하려면 다음 예와 같이 수동 캡처 모드를 사용합니다.

/* Turn on manual capture */ SET apg_plan_mgmt.capture_plan_baselines = manual; EXPLAIN SELECT COUNT(*) from pg_class; -- capture the plan baseline SET apg_plan_mgmt.capture_plan_baselines = off; -- turn off capture SET apg_plan_mgmt.use_plan_baselines = true; -- turn on plan usage

SELECT, INSERT, UPDATE 또는 DELETE 문을 실행하거나, 위와 같이 EXPLAIN 문을 포함할 수 있습니다. EXPLAIN을 사용하여 설명문 실행에 따른 오버헤드나 잠재적인 부작용 없이 계획을 캡처할 수 있습니다. 수동 캡처에 대한 자세한 내용은 특정 SQL 문에 대해 계획을 수동으로 캡처 단원을 참조하십시오.

캡처된 계획 보기

앞의 예시에서 EXPLAIN SELECT가 실행될 때 최적화 프로그램이 계획을 저장합니다. 이를 위해 최적화 프로그램은 apg_plan_mgmt.dba_plans 보기에 행을 삽입하고 자율적인 트랜잭션에서 계획을 커밋합니다. apg_plan_mgmt 역할이 부여된 경우 apg_plan_mgmt.dba_plans 보기의 내용을 볼 수 있습니다. 다음 쿼리는 dba_plans 보기의 중요한 몇 가지 열을 표시합니다.

SELECT sql_hash, plan_hash, status, enabled, plan_outline, sql_text::varchar(40) FROM apg_plan_mgmt.dba_plans ORDER BY sql_text, plan_created;

표시된 각 행은 관리형 계획을 나타냅니다. 앞의 예는 다음 정보를 보여 줍니다.

  • sql_hash – 계획 관련 관리형 설명문의 ID입니다.

  • plan_hash – 관리형 계획의 ID입니다.

  • status – 계획의 상태입니다. 최적화 프로그램은 승인된 계획을 실행할 수 있습니다.

  • enabled – 계획이 활성화되어 사용할 수 있는지 아니면 비활성화되어 사용할 수 없는지를 나타내는 값입니다.

  • plan_outline – 관리형 계획의 세부 정보입니다.

apg_plan_mgmt.dba_plans 보기에 대한 자세한 내용은 apg_plan_mgmt.dba_plans 보기에서 계획 검토 단원을 참조하십시오.

관리형 설명문 및 SQL 해시를 이용한 작업

관리형 설명문은 쿼리 계획 관리 상태에서 최적화 프로그램에서 캡처한 SQL 문입니다. 수동 또는 자동 캡처를 사용하여 관리형 설명문으로 캡처할 SQL 문을 지정합니다.

  • 수동 캡처의 경우 이전 예에서처럼 최적화 프로그램에 특정 설명문을 제공합니다.

  • 자동 캡처의 경우 최적화 프로그램은 여러 번 실행되는 설명문에 대한 계획을 캡처합니다. 자동 캡처는 뒤에 소개하는 예에 나와 있습니다.

apg_plan_mgmt.dba_plans 보기에서는 SQL 해시 값을 사용하여 관리형 설명문을 식별할 수 있습니다. SQL 해시는 차이(예: 리터럴 값)를 없앤 SQL 문의 정규화된 표현에서 계산됩니다. 정규화 사용이란 여러 SQL 문이 리터럴 또는 파라미터 값만 다를 경우 apg_plan_mgmt.dba_plans 보기에서 동일 SQL 해시로 표현됨을 뜻합니다. 따라서 각 계획이 서로 다른 조건에서 최적 상태인 동일한 SQL 해시에 대해 여러 계획이 있을 수 있습니다.

최적화 프로그램은 SQL 문을 처리할 때 다음 규칙을 사용하여 정규화된 SQL 문을 생성합니다.

  • 선행 블록 설명을 제거합니다.

  • EXPLAIN 키워드 및 EXPLAIN 옵션을 제거합니다(있는 경우).

  • 후행 공백을 제거합니다.

  • 모든 리터럴을 제거합니다.

  • 가독성을 위해 공백과 대/소문자를 유지합니다.

예를 들면 다음 설명문을 사용합니다.

/*Leading comment*/ EXPLAIN SELECT /* Query 1 */ * FROM t WHERE x > 7 AND y = 1;

최적화 프로그램은 다음과 같이 이 설명문을 정규화합니다.

SELECT /* Query 1 */ * FROM t WHERE x > CONST AND y = CONST;

자동 계획 캡처 작업

애플리케이션의 모든 SQL 문에 대한 계획을 캡처하려는 경우 또는 수동 캡처를 사용할 수 없는 경우’ 자동 계획 캡처를 사용합니다. 자동 계획 캡처를 사용하는 경우 기본적으로 최적화 프로그램은 두 번 이상 실행되는 설명문에 대해 계획을 캡처합니다. 자동 계획 캡처를 사용하려면 다음을 수행합니다.

  1. DB 인스턴스에 대한 파라미터 그룹에서 apg_plan_mgmt.capture_plan_baselinesautomatic으로 설정하여 자동 계획 캡처를 켭니다. 자세한 내용은 DB 파라미터 그룹의 파라미터 수정 단원을 참조하십시오.

  2. DB 인스턴스를 다시 시작합니다.

애플리케이션이 실행될 때 최적화 프로그램은 두 번 이상 실행되는 모든 설명문에 대해 계획을 캡처합니다. 최적화 프로그램은 관리형 설명문의 캡처된 첫 번째 계획의 상태를 항상 approved로 설정합니다. 승인된 계획의 관리형 설명문 세트를 계획 기준이라고 합니다.

애플리케이션이 계속 실행됨에 따라 최적화 프로그램은 관리형 설명문에 대한 추가 계획을 찾을 수 있습니다. 최적화 프로그램은 캡처한 추가 계획을 unapproved 상태로 설정합니다.

관리형 설명문에 대한 캡처된 모든 계획 세트를 계획 기록이라고 합니다. 나중에 apg_plan_mgmt.evolve_plan_baselines 함수 또는 apg_plan_mgmt.set_plan_status 함수를 사용하여 unapproved 계획이 잘 수행되는지 여부를 판단하고 이러한 계획을 approved, rejected 또는 preferred로 변경할 수 있습니다.

자동 계획 캡처를 끄려면 DB 인스턴스에 대한 파라미터 그룹에서 apg_plan_mgmt.capture_plan_baselinesoff으로 설정하십시오. 그런 다음 설정이 적용되도록 데이터베이스를 다시 시적합니다.

계획 캡처에 대한 자세한 내용은 실행 계획 캡처 단원을 참조하십시오.

계획 검증

관리형 계획은 계획이 종속된 객체(예: 인덱스)가 제거되면 무효("기한 경과") 상태가 될 수 있습니다. 기한 경과 상태인 모든 계획을 찾아서 삭제하려면 apg_plan_mgmt.validate_plans 함수를 사용합니다.

SELECT apg_plan_mgmt.validate_plans('delete');

자세한 내용은 계획 검증 단원을 참조하십시오.

성능을 개선하는 새 계획 승인

관리형 계획을 사용하는 경우 최적화 프로그램에서 검색된 더 새롭고 저렴한 비용의 계획이 계획 기준에 이미 포함된 최소 비용 계획보다 더 빠른지 확인할 수 있습니다. 성능 비교를 수행하고 필요에 따라 더 빠른 계획을 승인하려면 apg_plan_mgmt.evolve_plan_baselines 함수를 호출합니다.

다음 예제에서는 계획 기준의 최소 비용 계획보다 최소 10% 더 빠르며 활성화되어 있는 미승인 계획을 모두 자동으로 승인합니다.

SELECT apg_plan_mgmt.evolve_plan_baselines( sql_hash, plan_hash, 1.1, 'approve' ) FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved' AND enabled = true;

apg_plan_mgmt.evolve_plan_baselines 함수는 실행 시 성능 통계를 수집하고 planning_time_ms, execution_time_ms, cardinality_error, total_time_benefit_msexecution_time_benefit_ms 열의 apg_plan_mgmt.dba_plans 보기에 이러한 통계를 저장합니다. 또한 apg_plan_mgmt.evolve_plan_baselines 함수는 last_verified 또는 last_validated timestamps 열을 업데이트하므로 성능 통계가 수집된 가장 최근 시간을 볼 수 있습니다.

SELECT sql_hash, plan_hash, status, last_verified, sql_text::varchar(40) FROM apg_plan_mgmt.dba_plans ORDER BY last_verified DESC; -- value updated by evolve_plan_baselines()

계획 확인에 대한 자세한 내용은 계획 성능 평가 단원을 참조하십시오.

계획 삭제

최적화 프로그램은 계획을 계획 보존 기간 동안 최소 비용 계획으로 선택했거나 실행하지 않은 경우 해당 계획을 자동으로 삭제합니다. 기본적으로 계획 보존 기간은 32일입니다. 계획 보존 기간을 변경하려면 apg_plan_mgmt.plan_retention_period 파라미터를 설정합니다.

또한 apg_plan_mgmt.dba_plans 보기의 내용을 검토하고 apg_plan_mgmt.delete_plan 함수를 사용하여 원하지 않는 계획을 모두 삭제할 수도 있습니다. 자세한 내용은 계획 삭제 단원을 참조하십시오.