쿼리 저장소를 사용하여 Azure Database for MySQL 성능 모니터링Monitor Azure Database for MySQL performance with Query Store

적용 대상: Azure Database for MySQL 5.7, 8.0Applies to: Azure Database for MySQL 5.7, 8.0

Azure Database for MySQL의 쿼리 저장소 기능은 시간 경과에 따라 쿼리 성능을 추적하는 방법을 제공합니다.The Query Store feature in Azure Database for MySQL provides a way to track query performance over time. 쿼리 저장소는 가장 오래 실행되고 리소스를 가장 많이 사용하는 쿼리를 신속하게 찾도록 지원하여 성능 문제 해결을 단순화합니다.Query Store simplifies performance troubleshooting by helping you quickly find the longest running and most resource-intensive queries. 쿼리 저장소는 쿼리 및 런타임 통계의 기록을 자동으로 캡처하고 검토를 위해 보존합니다.Query Store automatically captures a history of queries and runtime statistics, and it retains them for your review. 데이터베이스 사용량 패턴을 볼 수 있도록 데이터를 기간별로 구분합니다.It separates data by time windows so that you can see database usage patterns. 모든 사용자, 데이터베이스 및 쿼리에 대한 데이터는 Azure Database for MySQL 인스턴스의 mysql 스키마 데이터베이스에 저장됩니다.Data for all users, databases, and queries is stored in the mysql schema database in the Azure Database for MySQL instance.

쿼리 저장소 사용에 대한 일반적인 시나리오Common scenarios for using Query Store

쿼리 저장소는 다음을 비롯한 여러 가지 시나리오에 사용할 수 있습니다.Query store can be used in a number of scenarios, including the following:

  • 재발된 쿼리를 검색하는 경우Detecting regressed queries
  • 지정된 기간에 쿼리가 실행된 횟수를 확인하는 경우Determining the number of times a query was executed in a given time window
  • 전체 기간에 대한 쿼리의 평균 실행 시간을 비교하여 큰 델타를 확인하는 경우Comparing the average execution time of a query across time windows to see large deltas

쿼리 저장소 사용Enabling Query Store

쿼리 저장소는 옵트인 기능이므로 서버에서 기본적으로 활성화되지 않습니다.Query Store is an opt-in feature, so it isn't active by default on a server. 쿼리 저장소는 지정된 서버의 모든 데이터베이스에 대해 전역으로 사용하거나 사용하지 않도록 설정되며 데이터베이스별로 켜거나 끌 수 없습니다.The query store is enabled or disabled globally for all the databases on a given server and cannot be turned on or off per database.

Azure Portal을 통해 쿼리 저장소 사용Enable Query Store using the Azure portal

  1. Azure Portal에 로그인하고 Azure Database for MySQL 서버를 선택합니다.Sign in to the Azure portal and select your Azure Database for MySQL server.
  2. 메뉴의 설정 섹션에서 서버 매개 변수를 선택합니다.Select Server Parameters in the Settings section of the menu.
  3. query_store_capture_mode 매개 변수를 검색합니다.Search for the query_store_capture_mode parameter.
  4. 값을 모두로 설정하고 저장합니다.Set the value to ALL and Save.

쿼리 저장소에서 대기 통계를 사용하도록 설정하려면 다음과 같이 합니다.To enable wait statistics in your Query Store:

  1. query_store_wait_sampling_capture_mode 매개 변수를 검색합니다.Search for the query_store_wait_sampling_capture_mode parameter.
  2. 값을 모두로 설정하고 저장합니다.Set the value to ALL and Save.

데이터의 첫 번째 배치가 mysql 데이터베이스에서 지속되는 데 최대 20분이 걸립니다.Allow up to 20 minutes for the first batch of data to persist in the mysql database.

쿼리 저장소의 정보Information in Query Store

쿼리 저장소에는 다음과 같은 두 개의 저장소가 있습니다.Query Store has two stores:

  • 쿼리 실행 통계 정보를 지속하기 위한 런타임 통계 저장소A runtime statistics store for persisting the query execution statistics information.
  • 대기 통계 정보를 지속하기 위한 대기 통계 스토리지A wait statistics store for persisting wait statistics information.

공간 사용량을 최소화하기 위해 런타임 통계 스토리지의 런타임 실행 통계가 고정된 구성 가능 기간을 통해 집계됩니다.To minimize space usage, the runtime execution statistics in the runtime statistics store are aggregated over a fixed, configurable time window. 이러한 저장소의 정보는 쿼리 저장소 보기를 쿼리하여 표시됩니다.The information in these stores is visible by querying the query store views.

다음 쿼리는 쿼리 저장소의 쿼리에 대한 정보를 반환합니다.The following query returns information about queries in Query Store:

SELECT * FROM mysql.query_store;

또는 대기 통계에 대한 이 쿼리는 다음을 수행합니다.Or this query for wait statistics:

SELECT * FROM mysql.query_store_wait_stats;

대기 쿼리 찾기Finding wait queries

참고

대기 통계는 최고 워크로드 동안 사용하도록 설정하거나 중요한 워크로드에 대해 무기한으로 켜두면 안 됩니다.Wait statistics should not be enabled during peak workload hours or be turned on indefinitely for sensitive workloads.
높은 CPU 사용률 또는 더 낮은 vCores로 구성된 서버에서 실행되는 워크로드의 경우 대기 통계를 사용하도록 설정할 때는 주의해야 합니다.For workloads running with high CPU utilization or on servers configured with lower vCores, use caution when enabling wait statistics. 무기한으로 켜두면 안 됩니다.It should not be turned on indefinitely.

대기 이벤트 유형은 유사성을 기준으로 서로 다른 대기 이벤트를 버킷으로 결합합니다.Wait event types combine different wait events into buckets by similarity. 쿼리 저장소는 대기 이벤트 유형, 특정 대기 이벤트 이름 및 해당하는 쿼리를 제공합니다.Query Store provides the wait event type, specific wait event name, and the query in question. 이 대기 정보를 쿼리 런타임 통계와 상호 연관시킬 수 있다는 것은 쿼리 성능 특성에 영향을 미치는 내용을 더 잘 이해할 수 있음을 의미합니다.Being able to correlate this wait information with the query runtime statistics means you can gain a deeper understanding of what contributes to query performance characteristics.

다음은 쿼리 저장소의 대기 통계를 사용하여 워크로드에 대한 더 많은 통찰력을 얻을 수 있는 방법의 몇 가지 예입니다.Here are some examples of how you can gain more insights into your workload using the wait statistics in Query Store:

관찰Observation 동작Action
최고 잠금 대기High Lock waits 영향을 받는 쿼리에 대한 쿼리 텍스트를 확인하고 대상 엔터티를 식별합니다.Check the query texts for the affected queries and identify the target entities. 쿼리 저장소에서 자주 실행되거나 오래 실행되는 동일한 엔터티를 수정하는 다른 쿼리를 확인합니다.Look in Query Store for other queries modifying the same entity, which is executed frequently and/or have high duration. 이러한 쿼리를 식별한 후 애플리케이션 논리를 변경하여 동시성을 개선하거나 덜 제한적인 격리 수준을 사용하는 것이 좋습니다.After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
높은 버퍼 IO 대기High Buffer IO waits 쿼리 저장소에서 물리적 읽기 횟수가 많은 쿼리를 찾습니다.Find the queries with a high number of physical reads in Query Store. 해당 쿼리가 IO 대기가 많은 쿼리와 일치하는 경우 검사 대신 검색을 수행하기 위해 기본 엔터티에 인덱스를 도입하는 것이 좋습니다.If they match the queries with high IO waits, consider introducing an index on the underlying entity, to do seeks instead of scans. 이렇게 하면 쿼리의 IO 오버헤드가 최소화됩니다.This would minimize the IO overhead of the queries. 포털에서 서버에 대한 성능 권장 사항을 확인하여 쿼리를 최적화하는 이 서버에 대한 인덱스 권장 사항이 있는지 확인합니다.Check the Performance Recommendations for your server in the portal to see if there are index recommendations for this server that would optimize the queries.
높은 메모리 대기High Memory waits 쿼리 저장소에서 메모리 사용량이 많은 상위 쿼리를 찾습니다.Find the top memory consuming queries in Query Store. 이러한 쿼리는 영향을 받는 쿼리의 추가 진행을 지연시킬 수 있습니다.These queries are probably delaying further progress of the affected queries. 포털에서 서버에 대한 성능 권장 사항을 확인하여 이러한 쿼리를 최적화하는 인덱스 권장 사항이 있는지 확인합니다.Check the Performance Recommendations for your server in the portal to see if there are index recommendations that would optimize these queries.

구성 옵션Configuration options

쿼리 저장소를 사용하도록 설정하면 데이터가 15분 집계 창에 저장되고 각 창에는 최대 500개의 고유 쿼리가 포함됩니다.When Query Store is enabled it saves data in 15-minute aggregation windows, up to 500 distinct queries per window.

다음 옵션은 쿼리 저장소 매개 변수를 구성하는 데 사용할 수 있습니다.The following options are available for configuring Query Store parameters.

매개 변수Parameter 설명Description 기본값Default RangeRange
query_store_capture_modequery_store_capture_mode 값에 따라 쿼리 저장소 기능을 설정/해제합니다.Turn the query store feature ON/OFF based on the value. 참고: performance_schema가 꺼진 경우 query_store_capture_mode를 켜면 performance_schema와 이 기능에 필요한 성능 스키마 계측의 하위 집합이 켜집니다.Note: If performance_schema is OFF, turning on query_store_capture_mode will turn on performance_schema and a subset of performance schema instruments required for this feature. ALLALL NONE, ALLNONE, ALL
query_store_capture_intervalquery_store_capture_interval 쿼리 저장소 캡처 간격(분)입니다.The query store capture interval in minutes. 쿼리 메트릭이 집계되는 간격을 지정할 수 있습니다.Allows specifying the interval in which the query metrics are aggregated 1515 5 - 605 - 60
query_store_capture_utility_queriesquery_store_capture_utility_queries 시스템에서 실행되는 모든 유틸리티 쿼리의 캡처를 켜거나 끕니다.Turning ON or OFF to capture all the utility queries that is executing in the system. 아니요NO 예, 아니요YES, NO
query_store_retention_period_in_daysquery_store_retention_period_in_days 쿼리 저장소에 데이터를 유지할 기간(일)입니다.Time window in days to retain the data in the query store. 77 1 - 301 - 30

다음 옵션은 특히 대기 통계에 적용됩니다.The following options apply specifically to wait statistics.

매개 변수Parameter 설명Description 기본값Default RangeRange
query_store_wait_sampling_capture_modequery_store_wait_sampling_capture_mode 대기 통계를 켜고 끌 수 있습니다.Allows turning ON / OFF the wait statistics. 없음NONE NONE, ALLNONE, ALL
query_store_wait_sampling_frequencyquery_store_wait_sampling_frequency 대기 샘플링 빈도(초)를 변경합니다.Alters frequency of wait-sampling in seconds. 5~300초5 to 300 seconds. 3030 5-3005-300

참고

현재 query_store_capture_mode는 이 구성을 대체합니다. 즉, query_store_capture_modequery_store_wait_sampling_capture_mode를 모두 사용하도록 설정하여 대기 통계가 작동하도록 해야 합니다.Currently query_store_capture_mode supersedes this configuration, meaning both query_store_capture_mode and query_store_wait_sampling_capture_mode have to be enabled to ALL for wait statistics to work. query_store_capture_mode가 꺼져 있는 경우, 대기 통계가 performance_schema enabled 및 쿼리 저장소에 의해 캡처된 query_text를 활용하므로 대기 통계도 켜집니다.If query_store_capture_mode is turned off, then wait statistics is turned off as well since wait statistics utilizes the performance_schema enabled, and the query_text captured by query store.

 Azure Portal  또는  Azure CLI 를 사용하여 매개 변수에 대한 다른 값을 가져오거나 설정합니다.Use the Azure portal or Azure CLI to get or set a different value for a parameter.

보기 및 함수Views and functions

다음 보기 및 함수를 사용하여 쿼리 저장소를 보고 관리합니다.View and manage Query Store using the following views and functions. 일부 권한 공용 역할의 사용자는 이러한 보기를 사용하여 쿼리 저장소의 데이터를 볼 수 있습니다.Anyone in the select privilege public role can use these views to see the data in Query Store. 이러한 보기는 mysql 데이터베이스에서만 사용할 수 있습니다.These views are only available in the mysql database.

쿼리는 리터럴 및 상수를 제거한 후 구조를 확인하여 정규화됩니다.Queries are normalized by looking at their structure after removing literals and constants. 리터럴 값을 제외하고 두 쿼리가 동일한 경우에는 동일한 해시를 포함합니다.If two queries are identical except for literal values, they will have the same hash.

mysql.query_storemysql.query_store

이 보기는 쿼리 저장소의 모든 데이터를 반환합니다.This view returns all the data in Query Store. 각 고유 데이터베이스 ID, 사용자 ID 및 쿼리 ID에 대한 하나의 행이 있습니다.There is one row for each distinct database ID, user ID, and query ID.

이름Name 데이터 형식Data Type IS_NULLABLEIS_NULLABLE 설명Description
schema_name varchar(64)varchar(64) 아니요NO 스키마의 이름입니다.Name of the schema
query_id bigint(20)bigint(20) 아니요NO 특정 쿼리에 대해 생성되는 고유 ID로 같은 쿼리가 다른 스키마에서 실행되는 경우 새 ID가 생성됩니다.Unique ID generated for the specific query, if the same query executes in different schema, a new ID will be generated
timestamp_id timestamptimestamp 아니요NO 쿼리가 실행되는 타임스탬프입니다.Timestamp in which the query is executed. query_store_interval 구성을 기반으로 합니다.This is based on the query_store_interval configuration
query_digest_text longtextlongtext 아니요NO 모든 리터럴을 제거한 후 정규화된 쿼리 텍스트입니다.The normalized query text after removing all the literals
query_sample_text longtextlongtext 아니요NO 리터럴을 사용한 실제 쿼리의 첫 번째 모양입니다.First appearance of the actual query with literals
query_digest_truncated bitbit YESYES 쿼리 텍스트가 잘렸는지의 여부입니다.Whether the query text has been truncated. 쿼리가 1KB 보다 길면 값은 예입니다.Value will be Yes if the query is longer than 1 KB
execution_count bigint(20)bigint(20) 아니요NO 이 타임스탬프 ID/구성된 시간 간격 동안 쿼리를 실행했던 횟수입니다.The number of times the query got executed for this timestamp ID / during the configured interval period
warning_count bigint(20)bigint(20) 아니요NO 시간 간격 동안 이 쿼리가 생성된 경고의 수입니다.Number of warnings this query generated during the internal
error_count bigint(20)bigint(20) 아니요NO 시간 간격 동안 이 쿼리가 생성된 오류의 수입니다.Number of errors this query generated during the interval
sum_timer_wait doubledouble YESYES 시간 간격 동안 이 쿼리의 전체 실행 시간입니다.Total execution time of this query during the interval
avg_timer_wait doubledouble YESYES 시간 간격 동안 이 쿼리에 대한 평균 실행 시간입니다.Average execution time for this query during the interval
min_timer_wait doubledouble YESYES 이 쿼리에 대한 최소 실행 시간입니다.Minimum execution time for this query
max_timer_wait doubledouble YESYES 최대 실행 시간입니다.Maximum execution time
sum_lock_time bigint(20)bigint(20) 아니요NO 이 기간 동안 이 쿼리 실행에 대한 모든 잠금에 소요된 총시간입니다.Total amount of time spent for all the locks for this query execution during this time window
sum_rows_affected bigint(20)bigint(20) 아니요NO 행 수가 영향을 받음Number of rows affected
sum_rows_sent bigint(20)bigint(20) 아니요NO 클라이언트로 전송된 행 수입니다.Number of rows sent to client
sum_rows_examined bigint(20)bigint(20) 아니요NO 검사된 행 수Number of rows examined
sum_select_full_join bigint(20)bigint(20) 아니요NO 전체 조인 수입니다.Number of full joins
sum_select_scan bigint(20)bigint(20) 아니요NO 선택된 검색 수입니다.Number of select scans
sum_sort_rows bigint(20)bigint(20) 아니요NO 정렬된 행 수입니다.Number of rows sorted
sum_no_index_used bigint(20)bigint(20) 아니요NO 쿼리에서 어떤 인덱스도 사용하지 않은 횟수입니다.Number of times when the query did not use any indexes
sum_no_good_index_used bigint(20)bigint(20) 아니요NO 쿼리 실행 엔진이 양호한 인덱스를 사용하지 않은 횟수입니다.Number of times when the query execution engine did not use any good indexes
sum_created_tmp_tables bigint(20)bigint(20) 아니요NO 만든 임시 테이블의 총 수입니다.Total number of temp tables created
sum_created_tmp_disk_tables bigint(20)bigint(20) 아니요NO 디스크에 생성된 임시 테이블의 총 수입니다(I/O 생성).Total number of temp tables created in disk (generates I/O)
first_seen timestamptimestamp 아니요NO 집계 기간 중 쿼리의 첫 번째 발생입니다(UTC).The first occurrence (UTC) of the query during the aggregation window
last_seen timestamptimestamp 아니요NO 이 집계 기간 중 쿼리의 마지막 발생입니다(UTC).The last occurrence (UTC) of the query during this aggregation window

mysql.query_store_wait_statsmysql.query_store_wait_stats

이 보기는 쿼리 저장소의 대기 이벤트 데이터를 반환합니다.This view returns wait events data in Query Store. 각 고유 데이터베이스 ID, 사용자 ID, 쿼리 ID 및 이벤트에 대한 하나의 행이 있습니다.There is one row for each distinct database ID, user ID, query ID, and event.

이름Name 데이터 형식Data Type IS_NULLABLEIS_NULLABLE 설명Description
interval_start timestamptimestamp 아니요NO 간격의 시작입니다(15분 증분).Start of the interval (15-minute increment)
interval_end timestamptimestamp 아니요NO 간격의 끝입니다(15분 증분).End of the interval (15-minute increment)
query_id bigint(20)bigint(20) 아니요NO 쿼리 저장소에서 정규화된 쿼리에 대해 생성된 고유 ID입니다.Generated unique ID on the normalized query (from query store)
query_digest_id varchar(32)varchar(32) 아니요NO 쿼리 저장소에서 모든 리터럴을 제거한 후의 정규화된 쿼리 텍스트입니다.The normalized query text after removing all the literals (from query store)
query_digest_text longtextlongtext 아니요NO 쿼리 저장소에서 리터럴을 사용한 실제 쿼리의 첫 번째 모양입니다.First appearance of the actual query with literals (from query store)
event_type varchar(32)varchar(32) 아니요NO 대기 이벤트의 범주입니다.Category of the wait event
event_name varchar(128)varchar(128) 아니요NO 대기 이벤트의 이름입니다.Name of the wait event
count_star bigint(20)bigint(20) 아니요NO 쿼리의 간격 중 샘플링된 대기 이벤트 수입니다.Number of wait events sampled during the interval for the query
sum_timer_wait_ms doubledouble 아니요NO 시간 간격 중에 이 쿼리의 총 대기 시간(밀리초)입니다.Total wait time (in milliseconds) of this query during the interval

FunctionsFunctions

이름Name 설명Description
mysql.az_purge_querystore_data(TIMESTAMP) 지정된 타임스탬프 이전의 모든 쿼리 저장소 데이터를 제거합니다.Purges all query store data before the given time stamp
mysql.az_procedure_purge_querystore_event(TIMESTAMP) 지정된 타임스탬프 이전의 모든 대기 이벤트 데이터를 제거합니다.Purges all wait event data before the given time stamp
mysql.az_procedure_purge_recommendation(TIMESTAMP) 만료 날짜가 지정된 타임스탬프 이전인 권장 사항을 제거합니다.Purges recommendations whose expiration is before the given time stamp

제한 사항 및 알려진 문제Limitations and known issues

  • MySQL 서버에 default_transaction_read_only 매개 변수가 있으면 쿼리 저장소는 데이터를 캡처할 수 없습니다.If a MySQL server has the parameter default_transaction_read_only on, Query Store cannot capture data.
  • 긴 유니코드 쿼리(>= 6000바이트)가 발견되면 쿼리 저장소 기능이 중단될 수 있습니다.Query Store functionality can be interrupted if it encounters long Unicode queries (>= 6000 bytes).
  • 대기 통계의 보존 기간은 24시간입니다.The retention period for wait statistics is 24 hours.
  • 대기 통계는 샘플을 사용하여 이벤트의 비율을 캡처합니다.Wait statistics uses sample to capture a fraction of events. query_store_wait_sampling_frequency 매개 변수를 사용하여 빈도를 수정할 수 있습니다.The frequency can be modified using the parameter query_store_wait_sampling_frequency.

다음 단계Next steps