Archivio di query in Database di Azure per PostgreSQL - Server flessibile

Query Store è una funzionalità di un server flessibile Database di Azure per PostgreSQL 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 più a 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 per tutti gli utenti, i database e le query vengono archiviati in un database denominato azure_sys nel server flessibile Database di Azure per PostgreSQL.

Abilitare Query Store

Query Store è disponibile senza costi aggiuntivi. Si tratta di una funzionalità di consenso esplicito, quindi non è abilitata per impostazione predefinita in un server. È possibile abilitare o disabilitare l'archivio query a livello globale per tutti i database in un determinato server. Non è possibile attivarlo o disattivarlo per ogni database.

Importante

Non abilitare Query Store nel piano tariffario Burstable perché causa problemi di prestazioni.

Abilitare Query Store nel portale di Azure

  1. Accedere al portale di Azure e selezionare il server flessibile Database di Azure per PostgreSQL.
  2. Selezionare Parametri 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. 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 in questi archivi usando le visualizzazioni.

Accedere alle informazioni di Query Store

Il server flessibile di Database di Azure per PostgreSQL archivia i dati di Query Store nel database azure_sys. La query seguente restituisce informazioni sulle query registrate dall'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 raggruppano diversi eventi di attesa in categorie in base alla loro somiglianza. Query Store fornisce il tipo di evento di attesa, il nome dell'evento di attesa specifico e la query in questione. Quando si correlano queste informazioni di attesa con le statistiche di runtime delle query, si ottiene una conoscenza più approfondita degli elementi che contribuiscono alle caratteristiche delle 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 Action
Attese di blocco elevate Controllare il testo delle query interessate e identificare le entità di destinazione. Cerca nel Query Store altre query eseguite frequentemente che hanno una durata elevata e 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 Trova 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 autonoma 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 ulteriormente lo stato di avanzamento delle query interessate.

Opzioni di configurazione

Quando si abilita Query Store, i dati vengono salvati nelle finestre di aggregazione. La lunghezza di queste finestre è determinata dal parametro pg_qs.interval_length_minutes , che per impostazione predefinita è 15 minuti. Per ogni finestra, Query Store archivia fino a 500 query distinte. 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 (valore intero che identifica in modo univoco la query eseguita). Se il numero di query distinte raggiunge 500 durante l'intervallo configurato, Query Store libera il 5% delle query registrate per liberare spazio per altre. Le query deallocate per prime sono quelle eseguite il minor numero di volte.

Per configurare Query Store parametri, usare le opzioni seguenti:

Parameter 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.max_captured_queries Numero massimo di query che l'Archivio query conserva tra tutte le query registrate in ogni intervallo di acquisizione. 500 100 - 500
pg_qs.max_plan_size Numero massimo di byte che Query Store salva dal testo del piano di esecuzione della query. I piani più lunghi vengono troncati. 7500 100 - 10000
pg_qs.max_query_text_length Lunghezza massima della query che il Query Store può salvare. 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 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 Specifica se l'archivio query deve salvare i piani di esecuzione delle query. off on, off
pg_qs.track_utility Indica se Query Store deve tenere traccia dei comandi dell'utilità. on on, off

Annotazioni

Se si modifica il valore per il pg_qs.max_query_text_length parametro , il testo di tutte le query acquisite dall'archivio query prima di apportare la modifica continua a usare lo stesso query_id e sql_query_text. Questo comportamento potrebbe dare l'impressione che il nuovo valore non abbia effetto, ma, per le query che l'archivio query non ha registrato prima, si noterà che il testo della query usa la lunghezza massima appena configurata. Questo comportamento è per progettazione ed è illustrato in Viste e funzioni. Se si esegue query_store.qs_reset, vengono rimosse tutte le informazioni registrate dall'archivio query fino ad ora, incluso il testo acquisito per ogni ID di query. 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:

Parameter Descrizione Predefinita Intervallo
pgms_wait_sampling.history_period Frequenza di campionamento degli eventi di attesa, in millisecondi. 100 1 - 600000
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 dall'archivio query ed eliminarle usando viste e funzioni disponibili nello query_store schema del azure_sys database. 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

Gli esempi seguenti illustrano il funzionamento della normalizzazione delle query:

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

create table tableOne (columnOne int, columnTwo int);

Si abilita la raccolta dati di Query Store e uno o più utenti eseguono 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 ID di query. Query Store mantiene il testo della prima query eseguita dopo l'abilitazione della raccolta dati. Pertanto, il testo è 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 ID di query. Il testo che li identifica tutti è il testo della prima query nel batch: select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;.

Infine, le query seguenti non corrispondono all'ID delle query del batch precedente. Il motivo per cui non corrispondono è illustrato nell'elenco seguente:

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 viene invertito. L'ordine passa 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 vengono valutate le espressioni nella clausola WHERE viene invertito. L'ordine passa 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.

Views

query_store.qs_view

Questa vista restituisce tutti i dati mantenuti dall'archivio query nelle tabelle di supporto. I dati che l'archivio query sta ancora registrando in memoria per l'intervallo di tempo attualmente attivo non sono visibili fino al termine dell'intervallo di tempo e i 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 References 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. Le query diverse con la stessa struttura vengono raggruppate tra loro. Questo testo è il testo per la prima delle query nel cluster. Il valore predefinito per la lunghezza massima del testo della query è 6.000 ed è possibile modificarlo usando il parametro pg_qs.max_query_text_lengthquery store . 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 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. 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 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. Per le query parallele, il numero di righe per ogni esecuzione corrisponde al numero di righe restituite al client dal processo backend che gestisce l'esecuzione della query, più la somma di tutte le righe che ciascun processo worker backend, avviato per collaborare all'esecuzione dei rami paralleli dell'albero di esecuzione, restituisce al processo backend che gestisce 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 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. Le query diverse con la stessa struttura vengono raggruppate tra loro. Questo testo è il testo per la prima delle query nel cluster.
query_type SMALLINT Tipo di operazione rappresentata dalla query. Nelle versioni di PostgreSQL <= 14, i valori possibili sono 0 (sconosciuto), 1 (select), 2 (update), 3 (insert), 4 (delete), 5 (utility), 6 (nothing). Nelle versioni di PostgreSQL >= 15, i valori possibili sono 0 (sconosciuto), 1 (select), 2 (update), 3 (insert), 4 (delete), 5 (merge), 6 (utility), 7 (nothing).

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 References Descrizione
start_time timestamp Le query vengono aggregate in base all'intervallo di tempo. Il parametro pg_qs.interval_length_minutes 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 References 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.

Functions

query_store.qs_reset

Questa funzione elimina tutte le statistiche raccolte dall'archivio query. 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 dall'archivio query. Questi dati non vengono ancora 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 del Query Store in un server con repliche in lettura non abilita automaticamente il Query Store su nessuna delle repliche di lettura. Anche se lo si abilita in una qualsiasi delle repliche di lettura, Query Store non registra le query eseguite su una qualsiasi replica di lettura. Le repliche di lettura funzionano in modalità di sola lettura fino a quando non vengono alzate di livello a primario.

Contenuti correlati

  • Last updated on