Azure Database for PostgreSQL의 인덱스 튜닝 - 유연한 서버

적용 대상: Azure Database for PostgreSQL - 유연한 서버

인덱스 튜닝은 추적한 쿼리를 분석하고 인덱스 추천을 제공하여 워크로드의 성능을 자동으로 개선하는 Azure Database for PostgreSQL 유연한 서버의 기능입니다.

Azure Database for PostgreSQL 유연한 서버의 기본 제공 제품으로, 쿼리 저장소 기능을 사용하여 모니터 성능을 기반으로 합니다. 인덱스 튜닝은 쿼리 저장소에서 추적하는 워크로드를 분석하고 인덱스 권장 사항을 생성하여 분석된 워크로드의 성능을 향상시키거나 중복되거나 사용되지 않는 인덱스를 삭제합니다.

• 인덱스 튜닝 세션 중에 분석된 쿼리를 크게 향상시킬 수 있으므로 만드는 데 유용한 인덱스를 식별합니다.
• 정확히 중복되고 제거할 수 있는 인덱스를 식별하여 존재 및 유지 관리가 시스템의 전반적인 성능에 미치는 성능 영향을 줄입니다.
• 제거할 후보가 될 수 있는 구성 가능한 기간 사용되지 않는 인덱스를 식별합니다.

인덱스 튜닝 알고리즘에 대한 일반적인 설명

index_tuning.mode 서버 매개 변수가 report(으)로 구성되면 서버 매개 변수 index_tuning.analysis_interval(으)로 구성된 빈도(분)로 튜닝 세션이 자동으로 시작됩니다.

첫 번째 단계에서 튜닝 세션은 생성할 수 있는 권장 사항이 시스템의 전반적인 성능에 크게 영향을 미칠 수 있다고 생각하는 데이터베이스 목록을 검색합니다. 이렇게 하려면 이 튜닝 세션이 집중하는 조회 간격 내에 실행이 캡처된 쿼리 저장소에서 기록한 모든 쿼리를 수집합니다. 조회 간격은 현재 튜닝 세션의 시작 시간부터 지난 index_tuning.analysis_interval분까지 지속됩니다.

실행이 쿼리 저장소에 기록되고 런타임 통계가 다시 설정되지 않은 모든 사용자 시작 쿼리의 경우 시스템은 집계된 총 실행 시간을 기준으로 순위를 지정합니다. 기간에 따라 가장 눈에 띄는 쿼리에 중점을 둡니다.

다음 쿼리는 해당 목록에서 제외됩니다.

• 시스템 시작 쿼리입니다. (즉, azuresu 역할에서 실행되는 쿼리)
• 시스템 데이터베이스(azure_sys, template0, template1 및 azure_maintenance)의 컨텍스트에서 실행되는 쿼리입니다.

이 알고리즘은 대상 데이터베이스를 반복하여 분석된 워크로드의 성능을 향상시킬 수 있는 가능한 인덱스를 검색합니다. 또한 중복으로 식별되거나 구성 가능한 기간에 사용되지 않았으므로 제거할 수 있는 인덱스를 검색합니다.

CREATE INDEX 권장 사항

인덱스 권장 사항을 생성하기 위해 분석할 후보로 식별된 각 데이터베이스에 대해 조회 간격 및 해당 특정 데이터베이스의 컨텍스트에서 실행되는 모든 SELECT, UPDATE, INSERT 및 DELETE 쿼리가 고려됩니다.

결과 쿼리 집합은 집계된 총 실행 시간에 따라 순위가 매겨지고 상위 index_tuning.max_queries_per_database이(가) 가능한 인덱스 권장 사항을 분석합니다.

잠재적 권장 사항은 다음 유형의 쿼리의 성능을 향상시키는 것을 목표로 합니다.

• 필터가 있는 쿼리(즉, WHERE 절에 조건자가 있는 쿼리),
• 조인이 JOIN 절로 표현되는 구문을 따르는지 또는 조인 조건자가 WHERE 절에 표현되는지 여부에 관계없이 여러 관계를 조인하는 쿼리입니다.
• 필터와 조인 조건자를 결합하는 쿼리입니다.
• 그룹화가 있는 쿼리(GROUP BY 절이 있는 쿼리).
• 필터와 그룹화가 결합된 쿼리입니다.
• 정렬이 있는 쿼리(ORDER BY 절이 있는 쿼리).
• 필터와 정렬을 결합한 쿼리입니다.

쿼리가 테이블의 한 열을 참조하고 해당 테이블에 통계가 없는 경우 전체 쿼리를 건너뛰고 실행을 개선하기 위한 인덱스 권장 사항을 생성하지 않습니다.

통계를 수집하는 데 필요한 분석은 ANALYZE 명령을 사용하여 수동으로 또는 자동 진공 디먼에 의해 자동으로 트리거될 수 있습니다.

index_tuning.max_indexes_per_table 튜닝 세션 동안 쿼리 수에서 참조하는 단일 테이블에 대해 테이블에 이미 있을 수 있는 인덱스를 제외하고 권장할 수 있는 인덱스 수를 지정합니다.

index_tuning.max_index_count 튜닝 세션 중에 분석된 데이터베이스의 모든 테이블에 대해 생성되는 인덱스 권장 사항 수를 지정합니다.

인덱스 권장 사항을 내보내려면 튜닝 엔진은 분석된 워크로드에서 index_tuning.min_improvement_factor(으)로 지정된 요소에 의해 하나 이상의 쿼리가 향상된다는 것을 예측해야 합니다.

마찬가지로 모든 인덱스 권장 사항은 index_tuning.max_regression_factor(으)로 지정된 요소의 해당 워크로드에서 단일 쿼리에 회귀를 도입하지 않도록 확인됩니다.

이전 단락에 언급된 모든 매개 변수, 기본값 및 유효한 범위는 구성 옵션에 설명되어 있습니다.

인덱스를 만드는 권장 사항과 함께 생성된 스크립트는 다음 패턴을 따릅니다.

create index concurrently {indexName} on {schema}.{table}({column_name}[, ...])

여기에는 concurrently절이 포함됩니다. 이 절의 효과에 대한 자세한 내용은 CREATE INDEX PostgreSQL 공식 설명서를 참조하세요.

인덱스 튜닝은 일반적으로 "_"(밑줄)로 구분되고 상수 "_idx" 접미사를 사용하여 서로 다른 키 열의 이름으로 구성된 권장 인덱스의 이름을 자동으로 생성합니다. 이름의 총 길이가 PostgreSQL 제한을 초과하거나 기존 관계와 충돌하는 경우 이름은 약간 다릅니다. 잘려서 이름 끝에 숫자를 추가할 수 있습니다.

CREATE INDEX 권장 사항의 영향 계산

인덱스 권장 사항을 만들 때의 영향은 IndexSize(메가바이트) 및 QueryCostImprovement(백분율)에서 측정됩니다.

IndexSize는 테이블의 현재 카디널리티와 권장 인덱스가 참조하는 열의 크기를 고려하여 인덱스의 예상 크기를 나타내는 단일 값입니다.

QueryCostImprovement는 값 배열로 구성되며, 각 요소는 이 인덱스가 존재하는 경우 계획의 비용이 개선될 것으로 예상되는 각 쿼리에 대한 계획의 비용 향상을 나타냅니다. 각 요소는 쿼리의 식별자(쿼리됨)와 권장 사항이 구현된 경우 계획 비용이 향상되는 비율(차원)을 보여 줍니다.

DROP INDEX 및 REINDEX 권장 사항

인덱스 튜닝 기능이 결정되는 각 데이터베이스에 대해 새 세션을 시작하고 CREATE INDEX 권장 사항 단계가 완료되면 다음 조건에 따라 기존 인덱스를 삭제하거나 다시 인덱싱하는 것이 좋습니다.

• 다른 사용자와 중복된 것으로 간주되는 경우 삭제합니다.
• 구성 가능한 시간 동안 사용되지 않으면 삭제합니다.
• 잘못된 것으로 표시된 인덱스를 다시 인덱싱합니다.

중복 인덱스 삭제

중복 인덱스를 삭제하기 위한 권장 사항: 먼저 중복된 인덱스를 식별합니다.

중복은 인덱스에 기인할 수 있는 다양한 함수와 예상 크기에 따라 순위가 매겨집니다.

마지막으로 참조 리더보다 순위가 낮은 모든 중복 항목을 삭제하고 각 중복 항목이 순위가 지정된 이유를 설명합니다.

두 인덱스를 중복으로 간주하려면 다음을 수행해야 합니다.

• 동일한 테이블을 통해 만들 수 있습니다.
• 정확히 동일한 형식의 인덱스여야 합니다.
• 키 열을 일치시키고 다중 열 인덱스 키의 경우 참조되는 순서와 일치합니다.
• 조건자의 식 트리와 일치합니다. 부분 인덱스에만 적용됩니다.
• 모든 비임플 열 참조의 식 트리와 일치합니다. 식에서 만든 인덱스에만 적용됩니다.
• 키에서 참조되는 각 열의 데이터 정렬과 일치합니다.

사용되지 않는 인덱스 삭제

사용하지 않는 인덱스를 삭제하기 위한 권장 사항은 다음 인덱스를 식별합니다.

• 최소 index_tuning.unused_min_period일 동안 사용되지 않습니다.
• 인덱스가 만들어지는 테이블에 최소(일일 평균) DML 수를 index_tuning.unused_dml_per_table 표시합니다.
• 인덱스가 만들어지는 테이블에 최소(일일 평균) 읽기 수를 index_tuning.unused_reads_per_table 표시합니다.

잘못된 인덱스 다시 인덱스

기존 인덱스를 다시 인덱싱하기 위한 권장 사항은 잘못된 것으로 표시된 인덱스를 식별합니다. 인덱스가 잘못된 것으로 표시되는 이유와 시기에 대한 자세한 내용은 PostgreSQL의 REINDEX 공식 설명서를 참조하세요.

DROP INDEX 권장 사항의 영향 계산

드롭 인덱스 권장 사항의 영향은 혜택(백분율) 및 IndexSize(메가바이트)의 두 가지 차원에서 측정됩니다.

이점은 지금은 무시할 수 있는 단일 값입니다.

IndexSize는 테이블의 현재 카디널리티와 권장 인덱스가 참조하는 열의 크기를 고려하여 인덱스의 예상 크기를 나타내는 단일 값입니다.

인덱스 튜닝 구성

인덱스 튜닝은 튜닝 세션이 실행되는 빈도와 같이 동작을 제어하는 매개 변수 집합을 통해 활성화, 비활성화 및 구성할 수 있습니다.

인덱스 튜닝을 사용하도록 설정, 사용 안 함 및 구성하는 방법에 대한 올바른 인덱스 튜닝 구성에 대한 모든 세부 정보를 살펴보세요.

인덱스 튜닝으로 생성된 정보

인덱스 튜닝에서 생성된 권장 사항을 읽고 해석하고 사용하는 방법은 인덱스 튜닝에서 생성된 권장 사항을 가져오고 사용하는 방법을 자세히 설명합니다.

제한 사항 및 지원 가능성

다음은 인덱스 튜닝에 대한 제한 사항 및 지원 가능성 범위 목록입니다.

지원되는 컴퓨팅 계층 및 SKU

인덱스 튜닝은 버스트 가능, 범용 및 메모리 최적화와 현재 사용 가능한 모든 계층에서 지원되며, vCore가 4개 이상인 현재 지원되는 모든 컴퓨팅 SKU가 지원됩니다.

지원되는 PostgreSQL 버전

인덱스 튜닝은 Azure Database for PostgreSQL 유연한 서버의 주 버전 12 이상에서 지원됩니다.

search_path 사용

인덱스 튜닝은 query_store.qs_view 열 search_path 에 유지되는 값을 사용하므로 각 쿼리를 분석할 때 쿼리가 원래 실행되었을 때 설정된 값과 동일한 값이 가능한 권장 사항을 분석하도록 설정된 값 search_path 입니다.

매개 변수가 있는 쿼리

PREPARE를 사용하거나 확장 쿼리 프로토콜을 사용하여 만든 매개 변수가 있는 쿼리는 구문 분석되고 분석되어 인덱스 권장 사항을 생성합니다.

매개 변수가 있는 쿼리를 분석하려면 인덱스 튜닝을 수행하려면 쿼리 저장소가 쿼리 실행을 캡처할 때 pg_qs.parameters_capture_mode를 설정해야 capture_first_sample 합니다. 또한 쿼리가 실행될 때 쿼리 저장소에서 매개 변수를 올바르게 캡처해야 합니다. 즉, 분석 중인 쿼리의 경우 query_store.qs_view 의 열 parameters_capture_status 이 .로 succeeded설정되어 있어야 합니다.

읽기 전용 모드 및 읽기 복제본

인덱스 튜닝은 읽기 복제본에서 지원되지 않거나 인스턴스가 읽기 전용 모드인 경우 쿼리 저장소를 사용하므로 읽기 복제본 또는 읽기 전용 모드인 인스턴스에서는 지원하지 않습니다.

읽기 복제본에서 볼 수 있는 모든 권장 사항은 주 복제본에서 실행된 워크로드만 분석한 후 주 복제본에서 생성되었습니다.

컴퓨팅 규모 축소

서버에서 인덱스 튜닝을 사용하도록 설정하고 해당 서버의 컴퓨팅을 필요한 최소 vCore 수보다 작게 축소하는 경우 이 기능은 계속 사용하도록 설정됩니다. 이 기능은 vCore가 4개 미만인 서버에서 지원되지 않으므로 컴퓨팅을 축소할 때로 설정된 경우에도 index_tuning.mode 워크로드를 분석하고 권장 사항을 생성하기 위해 ON 실행되지 않습니다. 서버가 최소 요구 사항을 충족하지는 않지만 모든 index_tuning.* 서버 매개 변수에 액세스할 수 없습니다. 최소 요구 사항을 충족하는 컴퓨팅으로 서버를 다시 확장할 때마다 요구 사항을 index_tuning.mode 충족하지 않는 컴퓨팅으로 축소하기 전에 설정된 값으로 구성됩니다.

고가용성 및 읽기 복제본

서버에 고가 용성 또는 읽기 복제본 이 구성된 경우 권장 인덱스를 구현할 때 주 서버에서 쓰기 집약적 워크로드를 생성하는 것과 관련된 영향을 알고 있어야 합니다. 크기가 큰 인덱스를 만들 때는 특히 주의해야 합니다.

관련 콘텐츠

• 쿼리 저장소를 사용하여 성능 모니터링
• 인덱스 튜닝 사용, 사용 안 함 및 구성
• 인덱스 튜닝으로 생성된 권장 사항 읽기, 해석 및 사용