Monitorare le prestazioni con Query StoreMonitor performance with the Query Store

Si applica a: Database di Azure per PostgreSQL - Versioni a server singolo 9.6, 10, 11Azure Database for PostgreSQL - Single Server versions 9.6, 10, 11Applies to: Azure Database for PostgreSQL - Single Server versions 9.6, 10, 11

La funzionalità Query Store di Database di Azure per PostgreSQL offre la possibilità di tenere traccia delle prestazioni delle query nel tempo.The Query Store feature in Azure Database for PostgreSQL provides a way to track query performance over time. Query Store semplifica la risoluzione dei problemi di prestazioni consentendo di trovare rapidamente le query con il tempo di esecuzione più lungo e il più elevato utilizzo di risorse.Query Store simplifies performance troubleshooting by helping you quickly find the longest running and most resource-intensive queries. Query Store acquisisce automaticamente una cronologia delle query e le statistiche di runtime e le conserva a scopo di verifica.Query Store automatically captures a history of queries and runtime statistics, and it retains them for your review. I dati vengono separati per intervalli di tempo per consentire l'individuazione dei modelli di utilizzo dei database.It separates data by time windows so that you can see database usage patterns. I dati relativi a tutti gli utenti, tutti i database e tutte le query vengono archiviati in un database denominato azure_sys nell'istanza di Database di Azure per PostgreSQL.Data for all users, databases, and queries is stored in a database named azure_sys in the Azure Database for PostgreSQL instance.

Importante

Non modificare il database azure_sys o i relativi schemi.Do not modify the azure_sys database or its schemas. Tale modifica impedirebbe il corretto funzionamento di Query Store e delle funzionalità per le prestazioni correlate.Doing so will prevent Query Store and related performance features from functioning correctly.

Abilitazione di Query StoreEnabling Query Store

Query Store è una funzionalità con consenso esplicito e non è quindi attivo per impostazione predefinita in un server.Query Store is an opt-in feature, so it isn't active by default on a server. Viene abilitato o disabilitato a livello globale per tutti i database in un determinato server e non può essere attivato o disattivato in base al database.The store is enabled or disabled globally for all the databases on a given server and cannot be turned on or off per database.

Abilitare Query Store con il portale di AzureEnable Query Store using the Azure portal

  1. Accedere al portale di Azure e selezionare il server di Database di Azure per PostgreSQL.Sign in to the Azure portal and select your Azure Database for PostgreSQL server.
  2. Selezionare Parametri del server nella sezione Impostazioni del menu.Select Server Parameters in the Settings section of the menu.
  3. Cercare il pg_qs.query_capture_mode parametro.Search for the pg_qs.query_capture_mode parameter.
  4. Impostare il TOP valore su e Salva.Set the value to TOP and Save.

Per abilitare le statistiche di attesa nell'archivio query:To enable wait statistics in your Query Store:

  1. Cercare il pgms_wait_sampling.query_capture_mode parametro.Search for the pgms_wait_sampling.query_capture_mode parameter.
  2. Impostare il ALL valore su e Salva.Set the value to ALL and Save.

In alternativa, è possibile impostare questi parametri usando l'interfaccia della riga di comando di Azure.Alternatively you can set these parameters using the Azure CLI.Alternatively you can set these parameters using the Azure CLI.

az postgres server configuration set --name pg_qs.query_capture_mode --resource-group myresourcegroup --server mydemoserver --value TOP
az postgres server configuration set --name pgms_wait_sampling.query_capture_mode --resource-group myresourcegroup --server mydemoserver --value ALL

Per il salvataggio permanente del primo batch di dati nel database azure_sys possono essere necessari fino a 20 minuti.Allow up to 20 minutes for the first batch of data to persist in the azure_sys database.

Informazioni in Query StoreInformation in Query Store

Query Store include due archivi:Query Store has two stores:

  • Un archivio delle statistiche di runtime per il salvataggio permanente delle informazioni delle statistiche di esecuzione delle query.A runtime stats store for persisting the query execution statistics information.
  • Un archivio delle statistiche di attesa per il salvataggio permanente delle informazioni delle statistiche di attesa.A wait stats store for persisting wait statistics information.

Gli scenari comuni per l'uso di Query Store includono:Common scenarios for using Query Store include:

  • Determinazione del numero di volte in cui una query è stata eseguita in un determinato intervallo di tempoDetermining the number of times a query was executed in a given time window
  • Confronto del tempo di esecuzione di una query nei diversi intervalli di tempo per identificare differenze significativeComparing the average execution time of a query across time windows to see large deltas
  • Identificazione delle query con il tempo di esecuzione più lungo nelle ultime X oreIdentifying longest running queries in the past X hours
  • Identificazione delle prime N query in attesa delle risorseIdentifying top N queries that are waiting on resources
  • Comprensione della natura dell'attesa per una determinata queryUnderstanding wait nature for a particular query

Per ridurre al minimo l'utilizzo di spazio, le statistiche di esecuzione di runtime nell'archivio delle statistiche di runtime vengono aggregate per un intervallo di tempo fisso configurabile.To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed, configurable time window. Le informazioni negli archivi sono visibili eseguendo query sulle viste di Query Store.The information in these stores is visible by querying the query store views.

Accedere alle informazioni dell'archivio queryAccess Query Store information

I dati di Query Store vengono archiviati nel database azure_sys sul server Postgres.Query Store data is stored in the azure_sys database on your Postgres server.

La query seguente restituisce informazioni sulle query in Query Store:The following query returns information about queries in Query Store:

SELECT * FROM query_store.qs_view; 

In alternativa, usare questa query per le statistiche di attesa:Or this query for wait stats:

SELECT * FROM query_store.pgms_wait_sampling_view;

È anche possibile generare dati dell'archivio query nei log di Monitoraggio di Azure per l'analisi e gli avvisi, gli hub eventi per lo streaming e Archiviazione di Azure per l'archiviazione.You can also emit Query Store data to Azure Monitor Logs for analytics and alerting, Event Hubs for streaming, and Azure Storage for archiving. Le categorie di log da configurare sono QueryStoreRuntimeStatistics e QueryStoreWaitStatistics.The log categories to configure are QueryStoreRuntimeStatistics and QueryStoreWaitStatistics. Per altre informazioni sull'installazione, vedere l'articolo Sulle impostazioni di diagnostica di Monitoraggio di Azure.To learn about setup, visit the Azure Monitor diagnostic settings article.To learn about setup, visit the Azure Monitor diagnostic settings article.

Ricerca di query in relazione all'attesaFinding wait queries

I tipi di eventi di attesa combinano diversi eventi di attesa in bucket in base alla somiglianza.Wait event types combine different wait events into buckets by similarity. Query Store indica il tipo di evento di attesa, il nome dello specifico evento di attesa e la query in questione.Query Store provides the wait event type, specific wait event name, and the query in question. La possibilità di correlare queste informazioni sulle attese alle statistiche di runtime delle query consente di comprendere in modo più approfondito ciò che contribuisce alle caratteristiche di prestazioni delle query.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.

Di seguito sono riportati alcuni esempi di come è possibile ottenere informazioni dettagliate sul carico di lavoro usando le statistiche di attesa in Query Store:Here are some examples of how you can gain more insights into your workload using the wait statistics in Query Store:

OsservazioneObservation AzioneAction
Attese di blocco elevateHigh Lock waits Controllare il testo delle query interessate e identificare le entità di destinazione.Check the query texts for the affected queries and identify the target entities. Cercare in Query Store altre query che modificano la stessa entità e che vengono eseguite spesso e/o hanno durata elevata.Look in Query Store for other queries modifying the same entity, which is executed frequently and/or have high duration. Dopo aver identificato tali query, valutare la possibilità di modificare la logica dell'applicazione per migliorare la concorrenza o usare un livello di isolamento meno restrittivo.After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
Attese di I/O del buffer elevateHigh Buffer IO waits Trovare le query con un numero elevato di letture fisiche in Query Store.Find the queries with a high number of physical reads in Query Store. Se corrispondono alle query con attese di I/O elevate, valutare la possibilità di introdurre un indice sull'entità sottostante in modo da eseguire ricerche anziché analisi.If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans. Questo ridurrebbe al minimo il sovraccarico di I/O delle query.This would minimize the IO overhead of the queries. Controllare le raccomandazioni per le prestazioni relative al server nel portale per verificare se sono presenti raccomandazioni sugli indici per il server che ottimizzerebbero le query.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.
Attese di memoria elevateHigh Memory waits Trovare le query con il maggiore utilizzo di memoria in Query Store.Find the top memory consuming queries in Query Store. Queste query probabilmente ritardano l'avanzamento delle query interessate.These queries are probably delaying further progress of the affected queries. Controllare le raccomandazioni per le prestazioni relative al server nel portale per verificare se sono presenti raccomandazioni sugli indici che ottimizzerebbero queste query.Check the Performance Recommendations for your server in the portal to see if there are index recommendations that would optimize these queries.

Opzioni di configurazioneConfiguration options

Quando è abilitato, Query Store salva i dati in intervalli di aggregazione di 15 minuti, con un massimo di 500 query distinte per intervallo.When Query Store is enabled it saves data in 15-minute aggregation windows, up to 500 distinct queries per window.

Per la configurazione dei parametri di Query Store sono disponibili le opzioni seguenti.The following options are available for configuring Query Store parameters.

ParametroParameter DescrizioneDescription PredefinitoDefault GammaRange
pg_qs.query_capture_modepg_qs.query_capture_mode Imposta le istruzioni di cui verrà tenuta traccia.Sets which statements are tracked. nonenone none, top, allnone, top, all
pg_qs.max_query_text_lengthpg_qs.max_query_text_length Imposta la lunghezza massima di query che è possibile salvare.Sets the maximum query length that can be saved. Le query più lunghe verranno troncate.Longer queries will be truncated. 60006000 100-10000100 - 10K
pg_qs.retention_period_in_dayspg_qs.retention_period_in_days Imposta il periodo di conservazione.Sets the retention period. 77 1-301 - 30
pg_qs.track_utilitypg_qs.track_utility Imposta se deve essere tenuta traccia dei comandi dell'utilità.Sets whether utility commands are tracked onon on, offon, off

Le opzioni seguenti si applicano specificamente alle statistiche di attesa.The following options apply specifically to wait statistics.

ParametroParameter DescrizioneDescription PredefinitoDefault GammaRange
pgms_wait_sampling.query_capture_modepgms_wait_sampling.query_capture_mode Imposta le istruzioni di cui verrà tenuta traccia per le statistiche di attesa.Sets which statements are tracked for wait stats. nonenone none, allnone, all
Pgms_wait_sampling.history_periodPgms_wait_sampling.history_period Imposta la frequenza di campionamento degli eventi di attesa, in millisecondi.Set the frequency, in milliseconds, at which wait events are sampled. 100100 1-6000001-600000

Nota

pg_qs.query_capture_mode prevale su pgms_wait_sampling.query_capture_mode.pg_qs.query_capture_mode supersedes pgms_wait_sampling.query_capture_mode. Se il valore di pg_qs.query_capture_mode è NONE, l'impostazione di pgms_wait_sampling.query_capture_mode non ha alcun effetto.If pg_qs.query_capture_mode is NONE, the pgms_wait_sampling.query_capture_mode setting has no effect.

Per ottenere o impostare un diverso valore per un parametro, usare il portale di Azure o l'interfaccia della riga di comando di Azure.Use the Azure portal or Azure CLI to get or set a different value for a parameter.

Viste e funzioniViews and functions

Visualizzare e gestire Query Store usando le viste e le funzioni seguenti.View and manage Query Store using the following views and functions. Queste viste possono essere usate da qualsiasi membro del ruolo pubblico di PostgreSQL per visualizzare i dati in Query StoreAnyone in the PostgreSQL public role can use these views to see the data in Query Store. e sono disponibili solo nel database azure_sys.These views are only available in the azure_sys database.

Le query vengono normalizzate esaminandone la struttura dopo la rimozione di valori letterali e costanti.Queries are normalized by looking at their structure after removing literals and constants. Due query identiche tranne per i valori letterali avranno lo stesso hash.If two queries are identical except for literal values, they will have the same hash.

query_store.qs_viewquery_store.qs_view

Questa vista restituisce tutti i dati in Query Store.This view returns all the data in Query Store. Contiene una riga per ogni specifico ID database, ID utente e ID query.There is one row for each distinct database ID, user ID, and query ID.

NomeName TipoType RiferimentiReferences DescrizioneDescription
runtime_stats_entry_idruntime_stats_entry_id bigintbigint ID nella tabella runtime_stats_entriesID from the runtime_stats_entries table
user_iduser_id oidoid pg_authid.oidpg_authid.oid OID dell'utente che ha eseguito l'istruzioneOID of user who executed the statement
db_iddb_id oidoid pg_database.oidpg_database.oid OID del database in cui l'istruzione è stata eseguitaOID of database in which the statement was executed
query_idquery_id bigintbigint   Codice hash interno, calcolato dall'albero di analisi dell'istruzioneInternal hash code, computed from the statement's parse tree
query_sql_textquery_sql_text Varchar(10000)Varchar(10000)   Testo di un'istruzione rappresentativa.Text of a representative statement. Query diverse con la stessa struttura vengono raggruppate e questo è il testo per la prima query del gruppo.Different queries with the same structure are clustered together; this text is the text for the first of the queries in the cluster.
plan_idplan_id bigintbigint ID del piano corrispondente alla query, non ancora disponibileID of the plan corresponding to this query, not available yet
start_timestart_time timestamptimestamp Le query vengono aggregate per intervalli di tempo. La durata di un intervallo è di 15 minuti per impostazione predefinita.Queries are aggregated by time buckets - the time span of a bucket is 15 minutes by default. Questo timestamp è l'ora di inizio corrispondente all'intervallo di tempo della voce.This is the start time corresponding to the time bucket for this entry.
end_timeend_time timestamptimestamp Ora di fine corrispondente all'intervallo di tempo della voceEnd time corresponding to the time bucket for this entry.
callscalls bigintbigint   Numero di volte in cui la query è stata eseguitaNumber of times the query executed
total_timetotal_time double precisiondouble precision   Tempo totale di esecuzione della query, in millisecondiTotal query execution time, in milliseconds
min_timemin_time double precisiondouble precision Tempo minimo di esecuzione della query, in millisecondiMinimum query execution time, in milliseconds
max_timemax_time double precisiondouble precision Tempo massimo di esecuzione della query, in millisecondiMaximum query execution time, in milliseconds
mean_timemean_time double precisiondouble precision Tempo medio di esecuzione della query, in millisecondiMean query execution time, in milliseconds
stddev_timestddev_time double precisiondouble precision Deviazione standard del tempo di esecuzione della query, in millisecondiStandard deviation of the query execution time, in milliseconds
rowsrows bigintbigint   Numero totale di righe recuperate o interessate dall'istruzioneTotal number of rows retrieved or affected by the statement
shared_blks_hitshared_blks_hit bigintbigint   Numero totale di riscontri nella cache dei blocchi condivisi ottenuto dall'istruzioneTotal number of shared block cache hits by the statement
shared_blks_readshared_blks_read bigintbigint Numero totale dei blocchi condivisi letti dall'istruzioneTotal number of shared blocks read by the statement
shared_blks_dirtiedshared_blks_dirtied bigintbigint   Numero totale dei blocchi condivisi modificati ma non salvati dall'istruzioneTotal number of shared blocks dirtied by the statement
shared_blks_writtenshared_blks_written bigintbigint   Numero totale dei blocchi condivisi scritti dall'istruzioneTotal number of shared blocks written by the statement
local_blks_hitlocal_blks_hit bigintbigint Numero totale di riscontri nella cache dei blocchi locali ottenuto dall'istruzioneTotal number of local block cache hits by the statement
local_blks_readlocal_blks_read bigintbigint   Numero totale dei blocchi locali letti dall'istruzioneTotal number of local blocks read by the statement
local_blks_dirtiedlocal_blks_dirtied bigintbigint   Numero totale dei blocchi locali modificati ma non salvati dall'istruzioneTotal number of local blocks dirtied by the statement
local_blks_writtenlocal_blks_written bigintbigint   Numero totale dei blocchi locali scritti dall'istruzioneTotal number of local blocks written by the statement
temp_blks_readtemp_blks_read bigintbigint   Numero totale dei blocchi temporanei letti dall'istruzioneTotal number of temp blocks read by the statement
temp_blks_writtentemp_blks_written bigintbigint   Numero totale dei blocchi temporanei scritti dall'istruzioneTotal number of temp blocks written by the statement
blk_read_timeblk_read_time double precisiondouble precision   Tempo totale impiegato dall'istruzione per la lettura dei blocchi, in millisecondi (se il parametro track_io_timing è abilitato, in caso contrario è zero)Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_timeblk_write_time double precisiondouble precision   Tempo totale impiegato dall'istruzione per la scrittura dei blocchi, in millisecondi (se il parametro track_io_timing è abilitato, in caso contrario è zero)Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

query_store.query_texts_viewquery_store.query_texts_view

Questa vista restituisce i dati del testo delle query in Query Store.This view returns query text data in Query Store. Contiene una riga per ogni specifico query_text.There is one row for each distinct query_text.

NomeName TipoType DescrizioneDescription
query_text_idquery_text_id bigintbigint ID della tabella query_textsID for the query_texts table
query_sql_textquery_sql_text Varchar(10000)Varchar(10000)   Testo di un'istruzione rappresentativa.Text of a representative statement. Query diverse con la stessa struttura vengono raggruppate e questo è il testo per la prima query del gruppo.Different queries with the same structure are clustered together; this text is the text for the first of the queries in the cluster.

query_store.pgms_wait_sampling_viewquery_store.pgms_wait_sampling_view

Questa vista restituisce i dati degli eventi di attesa in Query Store.This view returns wait events data in Query Store. Contiene una riga per ogni specifico ID database, ID utente, ID query ed evento.There is one row for each distinct database ID, user ID, query ID, and event.

NomeName TipoType RiferimentiReferences DescrizioneDescription
user_iduser_id oidoid pg_authid.oidpg_authid.oid OID dell'utente che ha eseguito l'istruzioneOID of user who executed the statement
db_iddb_id oidoid pg_database.oidpg_database.oid OID del database in cui l'istruzione è stata eseguitaOID of database in which the statement was executed
query_idquery_id bigintbigint   Codice hash interno, calcolato dall'albero di analisi dell'istruzioneInternal hash code, computed from the statement's parse tree
event_typeevent_type texttext   Tipo di evento atteso dal back-endThe type of event for which the backend is waiting
eventevent texttext Nome dell'evento di attesa, se il back-end è attualmente in attesaThe wait event name if backend is currently waiting
callscalls IntegerInteger Numero dello stesso evento acquisitoNumber of the same event captured

FunzioniFunctions

Query_store.qs_reset() restituisce voidQuery_store.qs_reset() returns void

qs_reset  rimuove tutte le statistiche finora raccolte da Query Store.qs_reset discards all statistics gathered so far by Query Store. Questa funzione può essere eseguita solo dal ruolo di amministratore del server.This function can only be executed by the server admin role.

Query_store.staging_data_reset() restituisce voidQuery_store.staging_data_reset() returns void

staging_data_reset  rimuove tutte le statistiche raccolte in memoria da Query Store (ossia i dati in memoria che non sono ancora stati scaricati nel database).staging_data_reset discards all statistics gathered in memory by Query Store (that is, the data in memory that has not been flushed yet to the database). Questa funzione può essere eseguita solo dal ruolo di amministratore del server.This function can only be executed by the server admin role.

Limitazioni e problemi notiLimitations and known issues

  • Se un server PostgreSQL ha il parametro default_transaction_read_only on, Query Store non può acquisire i dati.If a PostgreSQL server has the parameter default_transaction_read_only on, Query Store cannot capture data.
  • La funzionalità Query Store può essere interrotta se si verificano query Unicode lunghe (> = 6000 byte).Query Store functionality can be interrupted if it encounters long Unicode queries (>= 6000 bytes).
  • Le repliche di lettura replicano i dati dell'archivio query dal server master.Read replicas replicate Query Store data from the master server. Ciò significa che l'archivio query di una replica di lettura non fornisce statistiche sulle query eseguite nella replica di lettura.This means that a read replica's Query Store does not provide statistics about queries run on the read replica.

Passaggi successiviNext steps