Condividi tramite

Monitorare le prestazioni con Query Store

  • Articolo

SI APPLICA A: Database di Azure per PostgreSQL - Server flessibile

Query Store è una funzionalità di Database di Azure per PostgreSQL server flessibile che consente di tenere traccia delle prestazioni delle query nel tempo. Query Store semplifica la risoluzione dei problemi di prestazioni consentendo di trovare rapidamente le query con esecuzione più lunga e a elevato utilizzo di risorse. Query Store acquisisce automaticamente una cronologia di query e statistiche di runtime e le mantiene per la revisione. Seziona i dati in base al tempo in modo che sia possibile visualizzare i modelli di utilizzo temporali. I dati per tutti gli utenti, i database e le query vengono archiviati in un database denominato azure_sys nell'istanza del server flessibile Database di Azure per PostgreSQL.

Abilitare l'archivio query

Query Store è disponibile per l'uso senza costi aggiuntivi. Si tratta di una funzionalità di consenso esplicito, quindi non è abilitata per impostazione predefinita in un server. È possibile abilitare o disabilitare Query Store a livello globale per tutti i database in un determinato server e non può essere attivato o disattivato in base al database.

Importante

Non abilitare Query Store nel piano tariffario Burstable perché causerebbe un impatto sulle prestazioni.

Abilitare l'archivio query in portale di Azure

  1. Accedere al portale di Azure e selezionare il Database di Azure per il server flessibile PostgreSQL.
  2. Selezionare Parametri del server nella sezione Impostazioni del menu.
  3. Cercare il pg_qs.query_capture_mode parametro.
  4. Impostare il valore su top o all, a seconda che si desideri tenere traccia delle query di primo livello o anche delle query annidate (quelle eseguite all'interno di una funzione o di una routine) e selezionare Salva. Attendere fino a 20 minuti prima che il primo batch di dati sia persistente nel azure_sys database.

Abilitare il campionamento di attesa dell'archivio query

  1. Cercare il pgms_wait_sampling.query_capture_mode parametro.
  2. Impostare il valore su all e selezionare Salva.

Informazioni nell'archivio query

Query Store è costituito da due archivi:

  1. Un archivio delle statistiche di runtime per il salvataggio permanente delle informazioni delle statistiche di esecuzione delle query.
  2. Un archivio delle statistiche di attesa per il salvataggio permanente delle informazioni delle statistiche di attesa.

Gli scenari comuni per l'uso di Query Store includono:

  • Determinazione del numero di volte in cui una query è stata eseguita in un determinato intervallo di tempo.
  • Confronto tra il tempo medio di esecuzione di una query nelle finestre temporali per visualizzare variazioni di grandi dimensioni.
  • Identificazione delle query con il tempo di esecuzione più lungo nelle ultime ore.
  • Identificazione delle prime N query in attesa delle risorse.
  • Informazioni sulla natura delle attese per una determinata 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. È possibile eseguire query sulle informazioni contenute in questi archivi usando le viste.

Accedere alle informazioni sull'archivio query

I dati dell'archivio query vengono archiviati nel database nell'istanza azure_sys del server flessibile Database di Azure per PostgreSQL. La query seguente restituisce informazioni sulle query registrate nell'archivio query:

SELECT * FROM  query_store.qs_view;

Questa query restituisce informazioni sulle statistiche di attesa:

SELECT * FROM  query_store.pgms_wait_sampling_view;

Ricercare query in relazione all'attesa

I tipi di eventi di attesa combinano diversi eventi di attesa in bucket in base alla somiglianza. Query Store fornisce il tipo di evento di attesa, il nome dell'evento di attesa specifico e la query in questione. 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.

Ecco alcuni esempi di come ottenere altre informazioni dettagliate sul carico di lavoro usando le statistiche di attesa in Query Store:

Osservazione Azione
Attese di blocco elevate Controllare il testo delle query interessate e identificare le entità di destinazione. Cercare nell'archivio query altre query che vengono eseguite di frequente e/o hanno una durata elevata e stanno modificando la stessa entità. 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.
Attese di I/O con buffer elevato Trovare le query con un numero elevato di letture fisiche nell'archivio query. Se corrispondono alle query con attese di I/O elevate, è consigliabile abilitare la funzionalità di ottimizzazione automatica degli indici per verificare se è possibile creare alcuni indici che potrebbero ridurre il numero di letture fisiche per tali query.
Attese di memoria elevate Trovare le prime query che utilizzano la memoria nell'archivio query. Queste query probabilmente ritardano l'avanzamento delle query interessate.

Opzioni di configurazione

Quando query store è abilitato, salva i dati nelle finestre di aggregazione di lunghezza determinate dal parametro server pg_qs.interval_length_minutes (il valore predefinito è 15 minuti). Per ogni finestra, archivia fino a 500 query distinte per finestra. Gli attributi che distinguono l'univocità di ogni query sono user_id (identificatore dell'utente che esegue la query), db_id (identificatore del database nel cui contesto viene eseguita la query) e query_id (un valore intero che identifica in modo univoco la query eseguita). Se il numero di query distinte raggiunge il 500 durante l'intervallo configurato, il 5% di quelli registrati viene deallocato per fare più spazio. Quelli deallocati per primi sono quelli che sono stati eseguiti il minor numero di volte.

Per la configurazione dei parametri di Query Store sono disponibili le opzioni seguenti:

Parametro Descrizione Default Intervallo
pg_qs.interval_length_minutes (*) Intervallo di acquisizione in minuti per Query Store. Definisce la frequenza di persistenza dei dati. 15 1 - 30
pg_qs.is_enabled_fs Solo uso interno: questo parametro viene usato come opzione di override di una funzionalità. Se viene visualizzato come disattivato, Query Store è disabilitato, nonostante il valore impostato per pg_qs.query_capture_mode. on on, off
pg_qs.max_plan_size Numero massimo di byte salvati dal testo del piano di query dall'archivio query; piani più lunghi vengono troncati. 7500 100 - 10000
pg_qs.max_query_text_length Lunghezza massima della query che può essere salvata; le query più lunghe vengono troncate. 6000 100 - 10000
pg_qs.parameters_capture_mode Indica se e quando acquisire parametri posizionali della query. capture_parameterless_only capture_parameterless_only, capture_first_sample
pg_qs.query_capture_mode Istruzioni da tenere traccia. none none, top, all
pg_qs.retention_period_in_days Periodo di conservazione in giorni per Query Store. I dati meno recenti vengono eliminati automaticamente. 7 1 - 30
pg_qs.store_query_plans Indica se i piani di query devono essere salvati in Query Store. off on, off
pg_qs.track_utility Indica se l'archivio query deve tenere traccia dei comandi dell'utilità. on on, off

(*) Parametro del server statico che richiede un riavvio del server per rendere effettiva una modifica del relativo valore.

Le opzioni seguenti si applicano specificamente alle statistiche di attesa:

Parametro Descrizione Default Intervallo
pgms_wait_sampling.history_period Frequenza, in millisecondi, in cui vengono campionati gli eventi di attesa. 100 1 - 600000
pgms_wait_sampling.is_enabled_fs Solo uso interno: questo parametro viene usato come opzione di override di una funzionalità. Se viene visualizzato come off, il campionamento di attesa è disabilitato nonostante il valore impostato per pgms_wait_sampling.query_capture_mode. on on, off
pgms_wait_sampling.query_capture_mode Quali istruzioni devono tenere traccia dell'estensione pgms_wait_sampling . none none, all

Nota

pg_qs.query_capture_modepgms_wait_sampling.query_capture_modesostituisce . Se pg_qs.query_capture_mode è none, l'impostazione pgms_wait_sampling.query_capture_mode non ha alcun effetto.

Usare il portale di Azure per ottenere o impostare un valore diverso per un parametro.

Viste e funzioni

È possibile eseguire query sulle informazioni registrate dall'archivio query e eliminarle usando alcune viste e funzioni disponibili nello query_store schema del azure_sys database. Chiunque nel ruolo pubblico PostgreSQL può usare queste viste per visualizzare i dati nell'archivio query. e sono disponibili solo nel database azure_sys.

Le query vengono normalizzate esaminando la struttura e ignorando qualsiasi elemento non semanticamente significativo, ad esempio valori letterali, costanti, alias o differenze nella combinazione di maiuscole e minuscole.

Se due query sono semanticamente identiche, anche se usano alias diversi per le stesse colonne e tabelle a cui si fa riferimento, vengono identificate con lo stesso query_id. Se due query differiscono solo nei valori letterali usati, vengono identificate anche con lo stesso query_id. Per le query identificate con lo stesso query_id, il relativo sql_query_text è quello della query eseguita per la prima volta dall'avvio dell'attività di registrazione dell'archivio query o dall'ultima volta che i dati persistenti sono stati eliminati perché è stata eseguita la funzione query_store.qs_reset .

Funzionamento della normalizzazione delle query

Di seguito sono riportati alcuni esempi per provare a illustrare il funzionamento di questa normalizzazione:

Si supponga di creare una tabella con l'istruzione seguente:

create table tableOne (columnOne int, columnTwo int);

È possibile abilitare la raccolta dati di Query Store e permettere a uno o più utenti di eseguire le query seguenti, in questo ordine esatto:

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";

Tutte le query precedenti condividono lo stesso query_id. Il testo mantenuto da Query Store è quello della prima query eseguita dopo l'abilitazione della raccolta dati. Pertanto, sarebbe select * from tableOne;.

Il set di query seguente, una volta normalizzato, non corrisponde al set di query precedente perché la clausola WHERE le rende semanticamente diverse:

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;

Tuttavia, tutte le query in questo ultimo set condividono lo stesso query_id e il testo usato per identificarli tutti è quello della prima query nel batch select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;.

Infine, trovare di seguito alcune query che non corrispondono al query_id di quelle del batch precedente e il motivo per cui non corrispondono:

Query:

select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;

Motivo della mancata corrispondenza: l'elenco di colonne fa riferimento alle stesse due colonne (columnOne e ColumnTwo), ma l'ordine in cui viene fatto riferimento viene invertito, dal columnOne, ColumnTwo batch precedente a ColumnTwo, columnOne in questa query.

Query:

select * from tableOne where columnTwo = 25 and columnOne = 25;

Motivo della mancata corrispondenza: l'ordine in cui viene fatto riferimento alle espressioni valutate nella clausola WHERE viene invertito da columnOne = ? and ColumnTwo = ? nel batch precedente a ColumnTwo = ? and columnOne = ? in questa query.

Query:

select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;

Motivo della mancata corrispondenza: la prima espressione nell'elenco di colonne non columnOne è più, ma la funzione abs valutata su columnOne (abs(columnOne)), che non è semanticamente equivalente.

Query:

select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;

Motivo della mancata corrispondenza: la prima espressione nella clausola WHERE non valuta più l'uguaglianza di columnOne con un valore letterale, ma con il risultato della funzione ceiling valutata su un valore letterale, che non è semanticamente equivalente.

Visualizzazioni

query_store.qs_view

Questa vista restituisce tutti i dati salvati in modo permanente nelle tabelle di supporto dell'archivio query. I dati che registrano ancora in memoria per l'intervallo di tempo attualmente attivo, non sono visibili fino a quando l'intervallo di tempo non termina e i relativi dati volatili in memoria vengono raccolti e salvati in modo permanente nelle tabelle archiviate su disco. Questa vista restituisce una riga diversa per ogni database (db_id), utente (user_id) e query (query_id) distinti.

Nome Type Riferimenti Descrizione
runtime_stats_entry_id bigint ID nella tabella runtime_stats_entries.
user_id oid pg_authid.oid OID dell'utente che ha eseguito l'istruzione.
db_id oid pg_database.oid OID del database in cui l'istruzione è stata eseguita.
query_id bigint Codice hash interno, calcolato dall'albero di analisi dell'istruzione.
query_sql_text varchar(10000) Testo di un'istruzione rappresentativa. Query diverse con la stessa struttura vengono raggruppate e questo è il testo per la prima query del gruppo. Il valore predefinito per la lunghezza massima del testo della query è 6000 e può essere modificato usando il parametro dell'archivio query pg_qs.max_query_text_length. Se il testo della query supera questo valore massimo, viene troncato ai primi pg_qs.max_query_text_length caratteri.
plan_id bigint ID del piano corrispondente alla query.
start_time timestamp Le query vengono aggregate in base alle finestre temporali. Il parametro pg_qs.interval_length_minutes del server definisce l'intervallo di tempo di tali finestre (il valore predefinito è 15 minuti). Questa colonna corrisponde all'ora di inizio della finestra in cui è stata registrata questa voce.
end_time timestamp Ora di fine corrispondente all'intervallo di tempo per questa voce.
calls bigint Numero di volte in cui la query viene eseguita in questo intervallo di tempo. Si noti che per le query parallele, il numero di chiamate per ogni esecuzione corrisponde a 1 per il processo back-end che determina l'esecuzione della query, oltre a molte altre unità per ogni processo di lavoro back-end che avvia per collaborare all'esecuzione dei rami paralleli dell'albero di esecuzione.
total_time double precision Tempo totale di esecuzione della query, in millisecondi.
min_time double precision Tempo minimo di esecuzione della query, in millisecondi.
max_time double precision Tempo massimo di esecuzione della query, in millisecondi.
mean_time double precision Tempo medio di esecuzione della query, in millisecondi.
stddev_time double precision Deviazione standard del tempo di esecuzione della query, in millisecondi.
rows bigint Numero totale di righe recuperate o interessate dall'istruzione. Si noti che per le query parallele, il numero di righe per ogni esecuzione corrisponde al numero di righe restituite al client dal processo back-end che determina l'esecuzione della query, più la somma di tutte le righe avviate per collaborare all'esecuzione dei rami paralleli dell'albero di esecuzione, restituisce al processo back-end che determina l'esecuzione della query.
shared_blks_hit bigint Numero totale di riscontri nella cache dei blocchi condivisi ottenuto dall'istruzione.
shared_blks_read bigint Numero totale dei blocchi condivisi letti dall'istruzione.
shared_blks_dirtied bigint Numero totale dei blocchi condivisi modificati ma non salvati dall'istruzione.
shared_blks_written bigint Numero totale di blocchi condivisi scritti dall'istruzione.
local_blks_hit bigint Numero totale di riscontri nella cache dei blocchi locali ottenuto dall'istruzione.
local_blks_read bigint Numero totale dei blocchi locali letti dall'istruzione.
local_blks_dirtied bigint Numero totale dei blocchi locali modificati ma non salvati dall'istruzione.
local_blks_written bigint Numero totale di blocchi locali scritti dall'istruzione.
temp_blks_read bigint Numero totale dei blocchi temporanei letti dall'istruzione.
temp_blks_written bigint Numero totale dei blocchi temporanei scritti dall'istruzione.
blk_read_time double precision Tempo totale impiegato dall'istruzione per la lettura dei blocchi, in millisecondi (se il parametro track_io_timing è abilitato, in caso contrario è zero).
blk_write_time double precision Tempo totale impiegato dall'istruzione per la scrittura dei blocchi, in millisecondi (se il parametro track_io_timing è abilitato, in caso contrario è zero).
is_system_query boolean Determina se il ruolo con user_id = 10 (azuresu) ha eseguito la query. L'utente dispone di privilegi avanzati e viene usato per eseguire operazioni del piano di controllo. Poiché questo è un servizio PaaS gestito, solo Microsoft fa parte del ruolo utente con privilegi avanzati.
query_type Testo Tipo di operazione rappresentata dalla query. I valori possibili sono i seguenti: unknown, select, update, insert, delete, merge, utility, nothing, undefined.
search_path Testo Valore di search_path impostato al momento dell'acquisizione della query.
query_parameters Testo Rappresentazione testuale di un oggetto JSON con i valori passati ai parametri posizionali di una query con parametri. Questa colonna popola solo il valore in due casi: 1) per le query non con parametri. 2) Per le query con parametri, quando pg_qs.parameters_capture_mode è impostato su capture_first_samplee se Query Store può recuperare i valori per i parametri della query in fase di esecuzione.
parameters_capture_status Testo Tipo di operazione rappresentata dalla query. I valori possibili sono succeeded (la query non è stata parametrizzata o era una query con parametri e i valori sono stati acquisiti correttamente), disabled (la query è stata parametrizzata ma i parametri non sono stati acquisiti perché pg_qs.parameters_capture_mode è stato impostato su capture_parameterless_only), too_long_to_capture (la query è stata parametrizzata, ma i parametri non sono stati acquisiti perché la lunghezza del json risultante che verrebbe visualizzata nella query_parameters colonna di questa visualizzazione, è stata considerata eccessivamente lunga per la persistenza dell'archivio query), too_many_to_capture (la query è stata parametrizzata, ma i parametri non sono stati acquisiti perché il numero totale di parametri, sono stati considerati eccessivi per la persistenza dell'archivio query), serialization_failed (la query è stata parametrizzata, ma almeno uno dei valori passati come parametro non poteva essere serializzato in testo).

query_store.query_texts_view

Questa vista restituisce i dati del testo delle query in Query Store. È presente una riga per ogni query_sql_text distinto.

Nome Tipo Descrizione
query_text_id bigint ID della tabella query_texts
query_sql_text varchar(10000) Testo di un'istruzione rappresentativa. Query diverse con la stessa struttura vengono raggruppate e questo è il testo per la prima query del gruppo.
query_type smallint Tipo di operazione rappresentata dalla query. Nella versione di PostgreSQL <= 14, i valori possibili sono 0 (sconosciuto), 1 (selezione), 2 (aggiornamento), 3 (inserimento), 4 (eliminazione), 5 (utilità), 6 (niente). Nella versione di PostgreSQL >= 15, i valori possibili sono 0 (sconosciuto), 1 (selezione), 2 (aggiornamento), 3 (inserimento), 4 (eliminazione), 5 (merge), 6 (utilità), 7 (niente).

query_store.pgms_wait_sampling_view

Questa vista restituisce i dati degli eventi di attesa in Query Store. Questa vista restituisce una riga diversa per ogni database (db_id), utente (user_id), query (query_id) ed evento (event) distinti.

Nome Type Riferimenti Descrizione
start_time timestamp Le query vengono aggregate in base alle finestre temporali. Il parametro pg_qs.interval_length_minutes del server definisce l'intervallo di tempo di tali finestre (il valore predefinito è 15 minuti). Questa colonna corrisponde all'ora di inizio della finestra in cui è stata registrata questa voce.
end_time timestamp Ora di fine corrispondente all'intervallo di tempo per questa voce.
user_id oid pg_authid.oid Identificatore di oggetto dell'utente che ha eseguito l'istruzione.
db_id oid pg_database.oid Identificatore di oggetto del database in cui è stata eseguita l'istruzione.
query_id bigint Codice hash interno, calcolato dall'albero di analisi dell'istruzione.
event_type Testo Tipo di evento atteso dal back-end.
event Testo Nome dell'evento di attesa, se il back-end è attualmente in attesa.
calls integer Numero di volte in cui è stato acquisito lo stesso evento.

Nota

Per un elenco dei valori possibili nelle event_type colonne e event della query_store.pgms_wait_sampling_view vista, vedere la documentazione ufficiale di pg_stat_activity e cercare le informazioni che fanno riferimento alle colonne con gli stessi nomi.

query_store.query_plans_view

Questa vista restituisce il piano di query utilizzato per eseguire una query. È presente una riga per ogni ID di database distinto e un ID di query. Query Store registra solo i piani di query per le query non di utilità.

Nome Type Riferimenti Descrizione
plan_id bigint Valore hash del piano di query normalizzato generato da EXPLAIN. È in formato normalizzato perché esclude i costi stimati dei nodi del piano e l'utilizzo dei buffer.
db_id oid pg_database.oid OID del database in cui l'istruzione è stata eseguita.
query_id bigint Codice hash interno, calcolato dall'albero di analisi dell'istruzione.
plan_text varchar(10000) Piano di esecuzione dell'istruzione given costs=false, buffers=false e format=text. Output identico a quello prodotto da EXPLAIN.

Funzioni

query_store.qs_reset

Questa funzione elimina tutte le statistiche raccolte finora dall'archivio query. Elimina le statistiche per le finestre temporali già chiuse, che sono già persistenti nelle tabelle su disco. Elimina anche le statistiche per l'intervallo di tempo corrente, che esistono solo in memoria. Solo i membri del ruolo di amministratore del server (azure_pg_admin) possono eseguire questa funzione.

query_store.staging_data_reset

Questa funzione elimina tutte le statistiche raccolte in memoria dall'archivio query, ovvero i dati in memoria non ancora scaricati nelle tabelle disco che supportano la persistenza dei dati raccolti per Query Store. Solo i membri del ruolo di amministratore del server (azure_pg_admin) possono eseguire questa funzione.

Modalità di sola lettura

Quando un'istanza di Database di Azure per PostgreSQL server flessibile è in modalità di sola lettura, ad esempio quando il default_transaction_read_only parametro è impostato su ono se la modalità di sola lettura viene abilitata automaticamente a causa del raggiungimento della capacità di archiviazione, Query Store non acquisisce dati.

L'abilitazione dell'archivio query in un server con repliche in lettura non abilita automaticamente query store in una delle repliche di lettura. Anche se la si abilita in una delle repliche in lettura, Query Store non registra le query eseguite su repliche di lettura, perché operano in modalità di sola lettura fino a quando non vengono alzate di livello a primario.

Contenuto correlato