쿼리 저장소를 사용하여 성능 모니터링
적용 대상:
Azure Database for PostgreSQL - 유연한 서버
Azure Database for PostgreSQL의 쿼리 저장소 기능은 시간 경과에 따라 쿼리 성능을 추적하는 방법을 제공합니다. 쿼리 저장소는 가장 오래 실행되고 리소스를 가장 많이 사용하는 쿼리를 신속하게 찾도록 지원하여 성능 문제 해결을 단순화합니다. 쿼리 저장소는 쿼리 및 런타임 통계의 기록을 자동으로 캡처하고 검토를 위해 보존합니다. 시간별 사용 패턴을 볼 수 있도록 데이터를 시간별로 분할합니다. 모든 사용자, 데이터베이스 및 쿼리에 대한 데이터는 Azure Database for PostgreSQL 인스턴스의 azure_sys라는 데이터베이스에 저장됩니다.
중요
azure_sys 데이터베이스 또는 해당 스키마를 수정하지 마세요. 이렇게 하면 쿼리 저장소 및 관련 성능 기능이 제대로 작동하지 않습니다.
쿼리 저장소 사용
쿼리 저장소는 옵트인 기능이므로 서버에서 기본적으로 사용하도록 설정되지 않습니다. 쿼리 저장소는 지정된 서버의 모든 데이터베이스에 대해 전역으로 사용하거나 사용하지 않도록 설정되며 데이터베이스별로 켜거나 끌 수 없습니다.
중요
성능에 영향을 줄 수 있으므로 버스트 가능한 가격 책정 계층에서 쿼리 저장소 사용하도록 설정하지 마세요.
쿼리 저장소를 사용하도록 설정
- Azure Portal에 로그인하고 Azure Database for PostgreSQL 서버를 선택합니다.
- 메뉴의 설정 섹션에서 서버 매개 변수를 선택합니다.
pg_qs.query_capture_mode매개 변수를 검색합니다.- 값을
TOP또는ALL로 설정하고 저장합니다. 데이터의 첫 번째 배치가 azure_sys 데이터베이스에서 지속되는 데 최대 20분이 걸립니다.
쿼리 저장소 대기 샘플링 사용
pgms_wait_sampling.query_capture_mode매개 변수를 검색합니다.- 값을
ALL로 설정하고 저장합니다.
쿼리 저장소의 정보
쿼리 저장소에는 다음과 같은 두 개의 저장소가 있습니다.
- 쿼리 실행 통계 정보를 지속하기 위한 런타임 통계 저장소.
- 대기 통계 정보를 지속하기 위한 대기 통계 저장소.
쿼리 저장소 사용에 대한 일반적인 시나리오는 다음과 같습니다.
- 지정된 기간에 쿼리가 실행된 횟수를 확인하는 경우
- 전체 기간에 대한 쿼리의 평균 실행 시간을 비교하여 큰 델타를 확인하는 경우
- 지난 몇 시간 동안 가장 오래 실행되는 쿼리를 식별하는 경우
- 리소스에서 대기 중인 최상위 N개 쿼리를 식별하는 경우
- 특정 쿼리에 대한 대기 특성을 이해하여 공간 사용량을 최소화하기 위해 런타임 통계 저장소의 런타임 실행 통계는 구성 가능한 고정된 기간 동안 집계됩니다. 이러한 저장소의 정보는 보기를 사용하여 쿼리할 수 있습니다.
쿼리 저장소 정보 액세스
쿼리 저장소 데이터는 Postgres 서버의 azure_sys 데이터베이스에 저장됩니다. 다음 쿼리는 쿼리 저장소의 쿼리에 대한 정보를 반환합니다.
SELECT * FROM query_store.qs_view;
또는 대기 통계에 대한 이 쿼리는 다음을 수행합니다.
SELECT * FROM query_store.pgms_wait_sampling_view;
대기 쿼리 찾기
대기 이벤트 유형은 유사성을 기준으로 서로 다른 대기 이벤트를 버킷으로 결합합니다. 쿼리 저장소는 대기 이벤트 유형, 특정 대기 이벤트 이름 및 해당하는 쿼리를 제공합니다. 이 대기 정보를 쿼리 런타임 통계와 상호 연관시킬 수 있다는 것은 쿼리 성능 특성에 영향을 미치는 내용을 더 잘 이해할 수 있음을 의미합니다.
다음은 쿼리 저장소의 대기 통계를 사용하여 워크로드에 대한 더 많은 통찰력을 얻을 수 있는 방법의 몇 가지 예입니다.
| 관찰 | 동작 |
|---|---|
| 최고 잠금 대기 | 영향을 받는 쿼리에 대한 쿼리 텍스트를 확인하고 대상 엔터티를 식별합니다. 쿼리 저장소에서 자주 실행되거나 오래 실행되는 동일한 엔터티를 수정하는 다른 쿼리를 확인합니다. 이러한 쿼리를 식별한 후 애플리케이션 논리를 변경하여 동시성을 개선하거나 덜 제한적인 격리 수준을 사용하는 것이 좋습니다. |
| 높은 버퍼 IO 대기 | 쿼리 저장소에서 물리적 읽기 횟수가 많은 쿼리를 찾습니다. 해당 쿼리가 IO 대기가 많은 쿼리와 일치하는 경우 검사 대신 검색을 수행하기 위해 기본 엔터티에 인덱스를 도입하는 것이 좋습니다. 이렇게 하면 쿼리의 IO 오버헤드가 최소화됩니다. 포털에서 서버에 대한 성능 권장 사항을 확인하여 쿼리를 최적화하는 이 서버에 대한 인덱스 권장 사항이 있는지 확인합니다. |
| 높은 메모리 대기 | 쿼리 저장소에서 메모리 사용량이 많은 상위 쿼리를 찾습니다. 이러한 쿼리는 영향을 받는 쿼리의 추가 진행을 지연시킬 수 있습니다. 포털에서 서버에 대한 성능 권장 사항을 확인하여 이러한 쿼리를 최적화하는 인덱스 권장 사항이 있는지 확인합니다. |
구성 옵션
쿼리 저장소를 사용하도록 설정하면 데이터가 15분 집계 창에 저장되고 각 창에는 최대 500개의 고유 쿼리가 포함됩니다. 다음 옵션은 쿼리 저장소 매개 변수를 구성하는 데 사용할 수 있습니다.
| 매개 변수 | 설명 | 기본값 | Range |
|---|---|---|---|
| pg_qs.query_capture_mode | 추적되는 문을 설정합니다. | 없음 | none, top, all |
| pg_qs.store_query_plans | pg_qs에 대한 쿼리 계획 저장 설정 또는 해제 | 끄기 | on, off |
| pg_qs.max_plan_size | pg_qs에 대한 쿼리 계획 텍스트에 대해 저장할 최대 바이트 수를 설정합니다. 더 긴 계획은 잘립니다. | 7,500 | 100 - 10k |
| pg_qs.max_query_text_length | 저장할 수 있는 최대 쿼리 길이를 설정합니다. 더 긴 쿼리는 잘립니다. | 6000 | 100 - 10K |
| pg_qs.retention_period_in_days | 보존 기간을 설정합니다. | 7 | 1 - 30 |
| pg_qs.index_generation_interval | 쿼리 저장소를 사용할 때 모든 데이터베이스에 대한 빈도를 생성하는 인덱스 권장 사항을 설정합니다. | 15 | 15 - 10080 |
| pg_qs.track_utility | 유틸리티 명령을 추적할지 여부를 설정합니다. | On | on, off |
다음 옵션은 특히 대기 통계에 적용됩니다.
| 매개 변수 | 설명 | 기본값 | Range |
|---|---|---|---|
| pgms_wait_sampling.query_capture_mode | 대기 통계가 추적되는 문을 설정합니다. | 없음 | none, all |
| Pgms_wait_sampling.history_period | 대기 이벤트가 샘플링되는 빈도(밀리초)를 설정합니다. | 100 | 1-600000 |
[!참고] pg_qs.query_capture_mode는 pgms_wait_sampling.query_capture_mode를 대체합니다. pg_qs.query_capture_mode가 NONE인 경우 pgms_wait_sampling.query_capture_mode 설정은 영향을 미치지 않습니다.
Azure Portal 을 사용하여 매개 변수에 대한 다른 값을 가져오거나 설정합니다.
보기 및 함수
다음 보기 및 함수를 사용하여 쿼리 저장소를 보고 관리합니다. PostgreSQL 공용 역할의 사용자는 이러한 보기를 사용하여 쿼리 저장소의 데이터를 볼 수 있습니다. 이러한 보기는 azure_sys 데이터베이스에서만 사용 가능합니다. 쿼리는 리터럴 및 상수를 제거한 후 구조를 확인하여 정규화됩니다. 리터럴 값을 제외하고 두 쿼리가 동일한 경우에는 동일한 queryId를 사용하게 됩니다.
query_store.qs_view
이 보기는 쿼리 저장소의 모든 데이터를 반환합니다. 각 고유 데이터베이스 ID, 사용자 ID 및 쿼리 ID에 대한 하나의 행이 있습니다.
| 이름 | 형식 | 참조 | 설명 |
|---|---|---|---|
| runtime_stats_entry_id | bigint | runtime_stats_entries 테이블의 ID | |
| user_id | oid | pg_authid.oid | 문을 실행한 사용자의 OID |
| db_id | oid | pg_database.oid | 문이 실행된 데이터베이스의 OID |
| query_id | bigint | 문의 구문 분석 트리에서 계산된 내부 해시 코드 | |
| query_sql_text | Varchar(10000) | 대표 문의 텍스트. 동일한 구조의 서로 다른 쿼리가 함께 클러스터되고, 이 텍스트는 클러스터에 있는 첫 번째 쿼리의 텍스트입니다. | |
| plan_id | bigint | 이 쿼리에 해당하는 계획의 ID | |
| start_time | timestamp | 쿼리는 시간 버킷별로 집계되며 버킷의 시간 범위는 기본적으로 15분입니다. 이는 이 항목의 시간 버킷에 해당하는 시작 시간. | |
| end_time | timestamp | 이 항목의 시간 버킷에 해당하는 종료 시간. | |
| calls | bigint | 쿼리 실행 횟수 | |
| total_time | double precision | 총 쿼리 실행 시간(밀리초) | |
| min_time | double precision | 최소 쿼리 실행 시간(밀리초) | |
| max_time | double precision | 최대 쿼리 실행 시간(밀리초) | |
| mean_time | double precision | 평균 쿼리 실행 시간(밀리초) | |
| stddev_time | double precision | 쿼리 실행 시간(밀리초)의 표준 편차 | |
| rows | bigint | 문이 영향을 미치거나 검색하는 총 행 수 | |
| shared_blks_hit | bigint | 문을 통해 공유되는 총 블록 캐시 적중 수 | |
| shared_blks_read | bigint | 문이 읽은 총 공유 블록 수 | |
| shared_blks_dirtied | bigint | 문에 의해 변경된 총 공유 블록 수 | |
| shared_blks_written | bigint | 문이 쓴 총 공유 블록 수 | |
| local_blks_hit | bigint | 문에 의한 총 로컬 블록 캐시 적중 수 | |
| local_blks_read | bigint | 문이 읽은 총 로컬 블록 수 | |
| local_blks_dirtied | bigint | 문에 의해 변경된 총 로컬 블록 수 | |
| local_blks_written | bigint | 문이 쓴 총 로컬 블록 수 | |
| temp_blks_read | bigint | 문이 읽은 총 임시 블록 수 | |
| temp_blks_written | bigint | 문이 쓴 총 임시 블록 수 | |
| blk_read_time | double precision | 문이 블록을 읽는 데 사용한 총 시간(밀리초)(track_io_timing이 사용하도록 설정된 경우, 이외의 경우에는 0) | |
| blk_write_time | double precision | 문이 블록을 쓰는 데 사용한 총 시간(밀리초)(track_io_timing이 사용하도록 설정된 경우, 이외의 경우에는 0) |
query_store.query_texts_view
이 보기는 쿼리 저장소의 쿼리 텍스트 데이터를 반환합니다. 각 고유 query_text에 대한 하나의 행이 있습니다.
| 이름 | 형식 | 설명 |
|---|---|---|
| query_text_id | bigint | query_texts 테이블의 ID |
| query_sql_text | Varchar(10000) | 대표 문의 텍스트. 동일한 구조의 서로 다른 쿼리가 함께 클러스터되고, 이 텍스트는 클러스터에 있는 첫 번째 쿼리의 텍스트입니다. |
query_store.pgms_wait_sampling_view
이 보기는 쿼리 저장소의 대기 이벤트 데이터를 반환합니다. 각 고유 데이터베이스 ID, 사용자 ID, 쿼리 ID 및 이벤트에 대한 하나의 행이 있습니다.
| 이름 | 형식 | 참조 | 설명 |
|---|---|---|---|
| user_id | oid | pg_authid.oid | 문을 실행한 사용자의 OID |
| db_id | oid | pg_database.oid | 문이 실행된 데이터베이스의 OID |
| query_id | bigint | 문의 구문 분석 트리에서 계산된 내부 해시 코드 | |
| event_type | text | 백 엔드가 대기 중인 이벤트 유형 | |
| event | text | 백 엔드가 현재 대기 중인 경우 대기 이벤트 이름 | |
| calls | 정수 | 캡처된 동일한 이벤트 수 |
query_store.query_plans_view
이 보기는 쿼리를 실행하는 데 사용된 쿼리 계획을 반환합니다. 각 고유 데이터베이스 ID 및 쿼리 ID당 하나의 행이 있습니다. 유틸리티가 아닌 쿼리에 대한 쿼리 계획만 저장합니다.
| plan_id | db_id | query_id | plan_text |
|---|---|---|---|
| plan_id | bigint | query_text의 해시 값 | |
| db_id | oid | pg_database.oid | 문이 실행된 데이터베이스의 OID |
| query_id | bigint | 문의 구문 분석 트리에서 계산된 내부 해시 코드 | |
| plan_text | varchar(10000) | cost=false, buffers=false 및 format=false가 지정된 문의 실행 계획입니다. 이는 EXPLAIN에서 제공한 것과 동일한 출력입니다. |
Functions
qs_reset은 쿼리 저장소가 지금까지 수집한 모든 통계를 무시합니다. 이 함수는 서버 관리자 역할만 실행할 수 있습니다.
staging_data_reset은 쿼리 저장소가 메모리에서 수집한 모든 통계(즉, 아직 데이터베이스로 플러시되지 않은 메모리의 데이터)를 무시합니다. 이 함수는 서버 관리자 역할만 실행할 수 있습니다.
제한 사항 및 알려진 문제
- PostgreSQL 서버에 매개 변수
default_transaction_read_only가 있는 경우 쿼리 저장소 데이터를 캡처하지 않습니다.
다음 단계
- 쿼리 저장소가 특히 유용할 수 있는 시나리오에 대해 자세히 알아보세요.
- 쿼리 저장소 사용 모범 사례에 대해 자세히 알아보세요.