쿼리 저장소를 사용하여 성능 모니터링 하기
적용 대상: 
Azure Database for PostgreSQL - 유연한 서버
Azure Database for PostgreSQL 유연한 서버의 쿼리 저장소 기능은 시간 경과에 따른 쿼리 성능을 추적하는 방법을 제공합니다. 쿼리 저장소는 가장 오래 실행되고 리소스를 가장 많이 사용하는 쿼리를 신속하게 찾도록 지원하여 성능 문제 해결을 단순화합니다. 쿼리 저장소는 쿼리 및 런타임 통계의 기록을 자동으로 캡처하고 검토를 위해 보존합니다. 시간별 사용 패턴을 볼 수 있도록 데이터를 시간별로 분할합니다. 모든 사용자, 데이터베이스 및 쿼리에 대한 데이터는 Azure Database for PostgreSQL 유연한 서버 인스턴스의 azure_sys라는 데이터베이스에 저장됩니다.
Important
azure_sys 데이터베이스 또는 해당 스키마를 수정하지 마세요. 이렇게 하면 쿼리 저장소 및 관련 성능 기능이 제대로 작동하지 않습니다.
쿼리 저장소를 사용하도록 설정
쿼리 저장소는 추가 요금 없이 모든 지역에서 사용할 수 있습니다. 이는 옵트인 기능이므로 서버에서는 기본적으로 사용하도록 설정되지 않습니다. 쿼리 저장소는 지정된 서버의 모든 데이터베이스에 대해 전역적으로 사용하거나 사용하지 않도록 설정할 수 있으며 데이터베이스별로 사용하거나 사용하지 않도록 설정할 수 없습니다.
Important
성능에 영향을 줄 수 있으므로 버스트 가능한 가격 책정 계층에서 쿼리 저장소를 사용하도록 설정하지 마세요.
Azure Portal에서 쿼리 저장소 사용
- Azure Portal에 로그인하고 Azure Database for PostgreSQL 유연한 서버 인스턴스를 선택합니다.
- 메뉴의 설정 섹션에서 서버 매개 변수를 선택합니다.
pg_qs.query_capture_mode매개 변수를 검색합니다.- 최상위 쿼리 또는 중첩 쿼리(함수 또는 절차 내에서 실행되는 쿼리)를 추적할지 여부에 따라 값을
TOP또는ALL로 설정하고 저장을 클릭합니다. 데이터의 첫 번째 배치가 azure_sys 데이터베이스에서 지속되는 데 최대 20분이 걸립니다.
쿼리 저장소 대기 샘플링 사용
pgms_wait_sampling.query_capture_mode매개 변수를 검색합니다.- 값을
ALL로 설정하고 저장합니다.
쿼리 저장소의 정보
쿼리 저장소는 두 개의 저장소로 구성됩니다.
- 쿼리 실행 통계 정보를 지속하기 위한 런타임 통계 저장소.
- 대기 통계 정보를 지속하기 위한 대기 통계 저장소.
쿼리 저장소 사용에 대한 일반적인 시나리오는 다음과 같습니다.
- 지정된 기간에 쿼리가 실행된 횟수를 확인하는 경우
- 전체 기간에 대한 쿼리의 평균 실행 시간을 비교하여 큰 델타를 확인하는 경우
- 지난 몇 시간 동안 가장 오래 실행되는 쿼리를 식별하는 경우.
- 리소스에서 대기 중인 최상위 N개 쿼리를 식별하는 경우.
- 특정 쿼리에 대한 대기 특성을 이해합니다.
공간 사용량을 최소화하기 위해 런타임 통계 저장소의 런타임 실행 통계가 고정된 구성 가능 기간을 통해 집계됩니다. 이러한 저장소의 정보는 보기를 사용하여 쿼리할 수 있습니다.
쿼리 저장소 정보 액세스
쿼리 저장소 데이터는 Azure Database for PostgreSQL 유연한 서버 인스턴스의 azure_sys 데이터베이스에 저장됩니다. 다음 쿼리는 쿼리 저장소의 쿼리에 대한 정보를 반환합니다.
SELECT * FROM query_store.qs_view;
또는 대기 통계에 대한 이 쿼리는 다음을 수행합니다.
SELECT * FROM query_store.pgms_wait_sampling_view;
대기 쿼리 찾기
대기 이벤트 유형은 유사성을 기준으로 서로 다른 대기 이벤트를 버킷으로 결합합니다. 쿼리 저장소는 대기 이벤트 유형, 특정 대기 이벤트 이름 및 해당하는 쿼리를 제공합니다. 이 대기 정보를 쿼리 런타임 통계와 상호 연관시킬 수 있다는 것은 쿼리 성능 특성에 영향을 미치는 내용을 더 잘 이해할 수 있음을 의미합니다.
다음은 쿼리 저장소의 대기 통계를 사용하여 워크로드에 대한 더 많은 통찰력을 얻을 수 있는 방법의 몇 가지 예입니다.
| 관찰 | 작업 |
|---|---|
| 최고 잠금 대기 | 영향을 받는 쿼리에 대한 쿼리 텍스트를 확인하고 대상 엔터티를 식별합니다. 쿼리 저장소에서 자주 실행되거나 오래 실행되는 동일한 엔터티를 수정하는 다른 쿼리를 확인합니다. 이러한 쿼리를 식별한 후 애플리케이션 논리를 변경하여 동시성을 개선하거나 덜 제한적인 격리 수준을 사용하는 것이 좋습니다. |
| 높은 버퍼 IO 대기 | 쿼리 저장소에서 물리적 읽기 횟수가 많은 쿼리를 찾습니다. 해당 쿼리가 IO 대기가 많은 쿼리와 일치하는 경우 검사 대신 검색을 수행하기 위해 기본 엔터티에 인덱스를 도입하는 것이 좋습니다. 이렇게 하면 쿼리의 IO 오버헤드가 최소화됩니다. 포털에서 서버에 대한 성능 권장 사항을 확인하여 쿼리를 최적화하는 이 서버에 대한 인덱스 권장 사항이 있는지 확인합니다. |
| 높은 메모리 대기 | 쿼리 저장소에서 메모리 사용량이 많은 상위 쿼리를 찾습니다. 이러한 쿼리는 영향을 받는 쿼리의 추가 진행을 지연시킬 수 있습니다. 포털에서 서버에 대한 성능 권장 사항을 확인하여 이러한 쿼리를 최적화하는 인덱스 권장 사항이 있는지 확인합니다. |
구성 옵션
쿼리 저장소가 사용하도록 설정되면 pg_qs.interval_length_minutes 서버 매개 변수에 의해 결정된 길이의 집계 창에 데이터가 저장됩니다(기본값은 15분). 각 창에 대해 창당 최대 500개의 고유 쿼리(고유 userid, dbid 및 queryid 포함)를 저장합니다. 간격 동안 고유 쿼리 수가 500에 도달하면 사용량이 적은 5%가 할당 취소되어 공간을 더 확보합니다.
다음 옵션은 쿼리 저장소 매개 변수를 구성하는 데 사용할 수 있습니다.
| 매개 변수 | 설명 | 기본값 | 범위 |
|---|---|---|---|
| pg_qs.query_capture_mode | 추적되는 문을 설정합니다. | 없음 | none, top, all |
| pg_qs.interval_length_minutes (*) | pg_qs에 대한 query_store 캡처 간격을 분 단위로 설정합니다. 이는 데이터 지속 빈도입니다. | 15 | 1 - 30 |
| pg_qs.store_query_plans | pg_qs에 대한 쿼리 계획 저장 설정 또는 해제합니다. | off | 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 | pg_qs의 보존 기간을 일 단위로 설정합니다. 이 시간 이후에는 데이터가 삭제됩니다. | 7 | 1 - 30 |
| pg_qs.track_utility | pg_qs가 유틸리티 명령을 추적할지 여부를 설정합니다. | On | on, off |
(*) 값 변경 내용을 적용하려면 서버를 다시 시작해야 하는 정적 서버 매개 변수입니다.
다음 옵션은 특히 대기 통계에 적용됩니다.
| 매개 변수 | 설명 | 기본값 | 범위 |
|---|---|---|---|
| pgms_wait_sampling.query_capture_mode | pgms_wait_sampling 확장으로 추적할 문을 선택합니다. | 없음 | 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 데이터베이스에서만 사용 가능합니다.
쿼리는 구조를 확인하고 리터럴, 상수, 별칭 또는 대/소문자 차이와 같이 의미 체계상 중요하지 않은 항목을 무시하여 정규화됩니다.
두 쿼리가 의미 체계상 동일한 경우 동일한 참조 열과 테이블에 대해 서로 다른 별칭을 사용하더라도 동일한 query_id로 식별됩니다. 두 쿼리가 사용된 리터럴 값만 다른 경우에도 동일한 query_id로 식별됩니다. 동일한 query_id로 식별된 모든 쿼리에 대해 해당 sql_query_text는 쿼리 저장소가 활동 기록을 시작한 이후 처음으로 실행된 쿼리의 텍스트이거나 query_store.qs_reset 함수가 실행되어 지속형 데이터가 마지막으로 삭제된 이후에 실행된 쿼리의 텍스트입니다.
쿼리 정규화가 작동하는 방식
다음은 이 정규화가 어떻게 작동하는지 설명하기 위한 몇 가지 예입니다.
다음 문을 사용하여 테이블을 만든다고 가정해 보겠습니다.
create table tableOne (columnOne int, columnTwo int);
쿼리 저장소 데이터 수집을 사용하도록 설정하면 단일 또는 여러 사용자가 정확한 순서에 따라 다음 쿼리를 실행합니다.
select * from tableOne;
select columnOne, columnTwo from tableOne;
select columnOne as c1, columnTwo as c2 from tableOne as t1;
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one";
이전 쿼리는 모두 동일한 query_id를 공유합니다. 그리고 쿼리 저장소가 보관하는 텍스트는 데이터 수집을 사용하도록 설정한 후 실행된 첫 번째 쿼리의 텍스트입니다. 따라서 select * from tableOne;이 됩니다.
다음 쿼리 집합은 정규화되면 WHERE 절로 인해 의미 체계상 다르기 때문에 이전 쿼리 집합과 일치하지 않습니다.
select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
select * from tableOne where columnOne = -3 and columnTwo = -3;
select columnOne, columnTwo from tableOne where columnOne = '5' and columnTwo = '5';
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = 7 and columnTwo = 7;
그러나 이 마지막 집합의 모든 쿼리는 동일한 query_id를 공유하며 이를 식별하는 데 사용되는 텍스트는 모두 일괄 처리 select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;의 첫 번째 쿼리의 텍스트입니다.
마지막으로 이전 일괄 처리의 query_id와 일치하지 않는 일부 쿼리와 일치하지 않는 이유를 아래에서 찾아보세요.
쿼리:
select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
일치하지 않는 이유: 열 목록이 동일한 두 열(columnOne 및 ColumnTwo)을 참조하지만 참조되는 순서는 이전 일괄 처리의 columnOne, ColumnTwo에서 이 쿼리의 ColumnTwo, columnOne로 반전됩니다.
쿼리:
select * from tableOne where columnTwo = 25 and columnOne = 25;
일치하지 않는 이유: WHERE 절에서 평가된 식이 참조되는 순서가 이전 일괄 처리의 columnOne = ? and ColumnTwo = ?에서 이 쿼리의 ColumnTwo = ? and columnOne = ?으로 반전되었습니다.
쿼리:
select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;
일치하지 않는 이유: 열 목록의 첫 번째 식은 더 이상 columnOne이 아니지만 abs 함수는 의미 체계상 동등하지 않은 columnOne(abs(columnOne))에 대해 평가되었습니다.
쿼리:
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;
일치하지 않는 이유: WHERE 절의 첫 번째 표현식은 더 이상 리터럴과 columnOne의 동일성을 평가하지 않지만 리터럴에 대해 평가되는 함수 ceiling의 결과를 사용하므로 의미상 동일하지 않습니다.
뷰
query_store.qs_view
이 뷰는 쿼리 저장소의 지원 테이블에 이미 유지된 모든 데이터를 반환합니다. 현재 활성 시간 동안 메모리에 기록되는 데이터는 해당 기간이 끝날 때까지 표시되지 않으며, 메모리 내 휘발성 데이터가 수집되어 디스크에 저장된 테이블에 지속됩니다. 이 보기는 각 개별 데이터베이스(db_id), 사용자(user_id) 및 쿼리(query_id)에 대해 서로 다른 행을 반환합니다.
| 이름 | Type | 참조 | 설명 |
|---|---|---|---|
| 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) | 대표 문의 텍스트. 동일한 구조의 서로 다른 쿼리가 함께 클러스터되고, 이 텍스트는 클러스터에 있는 첫 번째 쿼리의 텍스트입니다. 최대 쿼리 텍스트 길이의 기본값은 6000이며 쿼리 저장소 매개 변수 pg_qs.max_query_text_length를 사용하여 수정할 수 있습니다. 쿼리 텍스트가 이 최댓값을 초과하면 처음 pg_qs.max_query_text_length자까지 잘립니다. |
|
| plan_id | bigint | 이 쿼리에 해당하는 계획의 ID입니다. | |
| start_time | timestamp | 쿼리는 서버 매개 변수 pg_qs.interval_length_minutes(기본값은 15분)에 의해 시간 범위가 정의되는 기간별로 집계됩니다. 이는 이 항목의 기간에 해당하는 시작 시간입니다. |
|
| end_time | timestamp | 이 항목의 기간에 해당하는 종료 시간입니다. | |
| 호출 | bigint | 이 기간 동안 쿼리가 실행된 횟수입니다. 병렬 쿼리의 경우 각 시작에 대한 호출 수는 쿼리 시작을 구동하는 백 엔드 프로세스에 대한 1에 해당하고 시작 트리의 병렬 분기를 공동으로 시작하기 위해 시작된 각 백 엔드 작업자 프로세스에 대한 다른 단위 수에 해당합니다. | |
| 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)입니다. | |
| is_system_query | 부울 값 | 슈퍼 사용자 권한이 있고 제어판 작업을 수행하는 데 사용되는 user_id = 10(azuresu)인 역할로 쿼리가 실행되었는지 여부를 확인합니다. 이 서비스는 관리되는 PaaS 서비스이므로 Microsoft만 슈퍼 사용자 역할에 속합니다. | |
| query_type | text | 쿼리가 나타내는 작업 형식입니다. 가능한 값은 unknown, select, update, insert, delete, merge, utility, nothing, undefined입니다. |
query_store.query_texts_view
이 보기는 쿼리 저장소의 쿼리 텍스트 데이터를 반환합니다. 각각의 고유한 query_sql_text에 대해 하나의 행이 있습니다.
| 이름 | 타입 | 설명 |
|---|---|---|
| query_text_id | bigint | query_texts 테이블의 ID |
| query_sql_text | varchar(10000) | 대표 문의 텍스트. 동일한 구조의 서로 다른 쿼리가 함께 클러스터되고, 이 텍스트는 클러스터에 있는 첫 번째 쿼리의 텍스트입니다. |
| query_type | smallint | 쿼리가 나타내는 작업 형식입니다. PostgreSQL 버전 <= 14에서 가능한 값은 0(알 수 없음), 1(선택), 2(업데이트), 3(삽입), 4(삭제), 5(유틸리티), 6(없음)입니다. PostgreSQL 버전 >= 15에서 가능한 값은 0(알 수 없음), 1(선택), 2(업데이트), 3(삽입), 4(삭제), 5(병합), 6(유틸리티), 7(없음)입니다. |
query_store.pgms_wait_sampling_view
이 보기는 쿼리 저장소의 대기 이벤트 데이터를 반환합니다. 이 보기는 각 개별 데이터베이스(db_id), 사용자(user_id), 쿼리(query_id) 및 이벤트(event)에 대해 서로 다른 행을 반환합니다.
| 이름 | Type | 참조 | 설명 |
|---|---|---|---|
| start_time | timestamp | 쿼리는 서버 매개 변수 pg_qs.interval_length_minutes(기본값은 15분)에 의해 시간 범위가 정의되는 기간별로 집계됩니다. 이는 이 항목의 기간에 해당하는 시작 시간입니다. |
|
| end_time | timestamp | 이 항목의 기간에 해당하는 종료 시간입니다. | |
| user_id | oid | pg_authid.oid | 문을 실행한 사용자의 OID입니다. |
| db_id | oid | pg_database.oid | 문이 실행된 데이터베이스의 OID입니다. |
| query_id | bigint | 문의 구문 분석 트리에서 계산된 내부 해시 코드입니다. | |
| event_type | text | 백 엔드가 대기 중인 이벤트 유형입니다. | |
| 이벤트 | text | 백 엔드가 현재 대기 중인 경우 대기 이벤트 이름입니다. | |
| 호출 | 정수 | 동일한 이벤트가 캡처된 횟수입니다. |
참고 항목
query_store.pgms_wait_sampling_view 뷰의 event_type 및 event 열에 가능한 값 목록은 pg_stat_activity의 공식 문서를 참조하여 동일한 이름을 가진 열을 참조하는 정보를 찾아보세요.
query_store.query_plans_view
이 보기는 쿼리를 실행하는 데 사용된 쿼리 계획을 반환합니다. 각각의 개별 데이터베이스 ID 및 쿼리 ID당 하나의 행이 있습니다. 이는 유틸리티가 아닌 쿼리에 대한 쿼리 계획만 저장합니다.
| plan_id | db_id | query_id | plan_text |
|---|---|---|---|
| plan_id | bigint | EXPLAIN에 의해 생성된 정규화된 쿼리 계획의 해시 값입니다. 계획 노드의 예상 비용과 버퍼 사용량을 제외하므로 정규화된 것으로 간주됩니다. | |
| db_id | oid | pg_database.oid | 문이 실행된 데이터베이스의 OID입니다. |
| query_id | bigint | 문의 구문 분석 트리에서 계산된 내부 해시 코드입니다. | |
| plan_text | varchar(10000) | cost=false, buffers=false 및 format=text가 지정된 문의 실행 계획입니다. 이는 EXPLAIN에서 제공한 것과 동일한 출력입니다. |
함수
query_store.qs_reset
이 함수는 쿼리 저장소에서 지금까지 수집한 모든 통계를 삭제합니다. 이미 닫힌 시간에 대한 통계(디스크 테이블에 유지됨)와 현재 시간에 대한 통계(아직 메모리에 보관됨)를 모두 삭제합니다. 이 함수는 서버 관리자 역할(azure_pg_admin)에 의해서만 실행될 수 있습니다.
query_store.staging_data_reset
이 함수는 쿼리 저장소에 의해 메모리 내에서 수집된 모든 통계(즉, 쿼리 저장소에 대해 수집된 데이터의 지속성을 지원하는 디스크 테이블에 아직 플러시되지 않은 메모리의 데이터)를 삭제합니다. 이 함수는 서버 관리자 역할(azure_pg_admin)에 의해서만 실행될 수 있습니다.
읽기 전용 모드
Azure Database for PostgreSQL - 유연한 서버 인스턴스가 읽기 전용 모드인 경우(예: default_transaction_read_only 매개 변수가 on으로 설정된 경우) 또는 읽기 전용 모드가 저장 용량 도달로 인해 자동으로 사용하도록 설정된 경우 쿼리 저장소는 데이터를 캡처하지 않습니다.