Condividi tramite

Query Store

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

Query Store è una funzionalità di Database di Azure per PostgreSQL - Server flessibile che offre la possibilità 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 il tempo di esecuzione più lungo e il più elevato utilizzo di risorse. Query Store acquisisce automaticamente una cronologia delle query e le statistiche di runtime e le conserva a scopo di verifica. Seziona i dati in base al tempo in modo che sia possibile visualizzare i modelli di utilizzo temporali. 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 - Server flessibile.

Abilitare Query Store

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 con possibilità di burst perché causerebbe un impatto sulle prestazioni.

Abilitare Query Store nel 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), quindi selezionare Salva. Per il salvataggio permanente del primo batch di dati nel database azure_sys possono essere necessari fino a 20 minuti.

Abilitare il campionamento di attesa di Query Store

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

Informazioni in Query Store

Query Store è costituito da due archivi:

  • Un archivio delle statistiche di runtime per il salvataggio permanente delle informazioni delle statistiche di esecuzione delle query.
  • 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 del tempo di esecuzione di una query nei diversi intervalli di tempo per identificare variazioni significative.
  • Identificazione delle query con il tempo di esecuzione più lungo nelle ultime ore.
  • Identificazione delle prime N query in attesa delle risorse.
  • Comprendere la 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 di Query Store

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

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 indica il tipo di evento di attesa, il nome dello specifico evento di attesa 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.

Di seguito sono riportati alcuni esempi di come è possibile ottenere 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. In Query Store cercare altre query che vengono eseguite frequentemente e/o hanno una durata elevata e che modificano 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 del buffer elevate Trovare le query con un numero elevato di letture fisiche in Query Store. 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 query con il maggiore utilizzo di memoria in Query Store. Queste query probabilmente ritardano l'avanzamento delle query interessate.

Opzioni di configurazione

Quando Query Store è abilitato, salva i dati nelle finestre di aggregazione con una durata determinata dal parametro del 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 quelle registrate viene deallocato per fare più spazio per altre. 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 Predefinita 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 tramite Query Store; 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 verranno 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 di cui 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 Query Store 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.

Annotazioni

Se si modifica il valore per il parametro pg_qs.max_query_text_length, il testo di tutte le query acquisite prima di apportare la modifica continua a usare lo stesso query_id e sql_query_text. Potrebbe dare l'impressione che il nuovo valore non sia effettivo, ma, per le query che non sono state registrate in Query Store prima, si noterà che il testo della query usa la lunghezza massima appena configurata. Si tratta di una procedura di progettazione e viene illustrata in Viste e funzioni. Se si esegue query_store.qs_reset, vengono rimosse tutte le informazioni registrate da Query Store fino ad ora, incluso il testo acquisito per ogni ID di query e, se una di queste query viene eseguita di nuovo, la lunghezza massima appena configurata viene applicata al testo acquisito.

Le opzioni seguenti si applicano specificamente alle statistiche di attesa:

Parametro Descrizione Predefinita Intervallo
pgms_wait_sampling.history_period Frequenza di campionamento degli eventi di attesa, in millisecondi. 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 Di quali istruzioni l'estensione pgms_wait_sampling deve tenere traccia. none none, all

Annotazioni

pg_qs.query_capture_mode sostituisce pgms_wait_sampling.query_capture_mode. 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 da Query Store e eliminarle usando alcune viste e funzioni disponibili nello schema query_store del database azure_sys. Queste viste possono essere usate da qualsiasi membro del ruolo pubblico di PostgreSQL per visualizzare i dati in Query Store. Le viste 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 di Query Store o dall'ultima volta che i dati persistenti sono stati eliminati perché la funzione query_store.qs_reset è stata eseguita.

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 loro riferimento viene invertito, da columnOne, ColumnTwo nel 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 è più columnOne, 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.

Viste

query_store.qs_view

Questa vista restituisce tutti i dati salvati in modo permanente nelle tabelle di supporto di Query Store. I dati ancora in corso di registrazione in memoria per l'intervallo di tempo attualmente attivo non sono visibili fino alla fine del suddetto intervallo 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 Tipo 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 byte.
plan_id bigint ID del piano corrispondente alla query.
start_time timestamp Le query vengono aggregate in base all'intervallo di tempo. 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 guida l'esecuzione della query, più molte altre unità per ogni processo di lavoro back-end che viene avviato per collaborare all'esecuzione dei rami paralleli dell'albero di esecuzione.
total_time doppia precisione Tempo totale di esecuzione della query, in millisecondi.
min_time doppia precisione Tempo minimo di esecuzione della query, in millisecondi.
max_time doppia precisione Tempo massimo di esecuzione della query, in millisecondi.
mean_time doppia precisione Tempo medio di esecuzione della query, in millisecondi.
stddev_time doppia precisione 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 di back-end che gestisce l'esecuzione della query, più la somma di tutte le righe che ogni processo di lavoro back-end, avviato per collaborare all'esecuzione dei rami paralleli dell'albero di esecuzione, restituisce al processo di back-end che attiva 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 doppia precisione 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 doppia precisione 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 text Tipo di operazione rappresentata dalla query. I valori possibili sono i seguenti: unknown, select, update, insert, delete, merge, utility, nothing, undefined.
search_path text Valore di search_path impostato al momento dell'acquisizione della query.
query_parameters text Rappresentazione testuale di un oggetto JSON con i valori passati ai parametri posizionali di una query parametrizzate. Questa colonna viene popolata in due casi: 1) per le query non parametrizzate. 2) Per le query parametrizzate, 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 text Tipo di operazione rappresentata dalla query. I valori possibili sono succeeded (la query non è stata parametrizzata o era una query parametrizzata 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 era 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 colonna query_parameters di questa vista, è stata considerata eccessivamente lunga per la persistenza di Query Store), too_many_to_capture (la query è stata parametrizzata, ma i parametri non sono stati acquisiti perché il numero totale di parametri è stato considerato eccessivo per la persistenza di Query Store), serialization_failed (la query è stata parametrizzata, ma almeno uno dei valori passati come parametro non ha potuto 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 Tipo Riferimenti Descrizione
start_time timestamp Le query vengono aggregate in base all'intervallo di tempo. 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 text Tipo di evento atteso dal back-end.
event text Nome dell'evento di attesa, se il back-end è attualmente in attesa.
calls integer Numero di volte in cui è stato acquisito lo stesso evento.

Annotazioni

Per un elenco dei valori possibili nelle colonne event_type e event della vista query_store.pgms_wait_sampling_view, 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 Tipo 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 da Query Store. Elimina le statistiche per gli intervalli di tempo già chiusi, 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 da Query Store (ovvero i dati in memoria che non sono ancora stati scaricati nelle tabelle su 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 server flessibile di Database di Azure per PostgreSQL è 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 di Query Store in un server con repliche in lettura non abilita automaticamente Query Store in alcuna replica di lettura. Anche se lo si abilita in una qualsiasi 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.

Contenuti correlati