Monitorare le prestazioni con Query Store
SI APPLICA A:
Database di Azure per PostgreSQL - Server flessibile
La funzionalità Query Store in Database di Azure per PostgreSQL server flessibile consente di tenere traccia delle prestazioni delle query nel tempo. Query Store semplifica la risoluzione dei problemi relativi alle prestazioni consentendo di trovare rapidamente le query con esecuzione più lunga e 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 nell'istanza del server flessibile Database di Azure per PostgreSQL.
Importante
Non modificare il database azure_sys o il relativo schema. Tale modifica impedirebbe il corretto funzionamento di Query Store e delle funzionalità per le prestazioni correlate.
Abilitare Query Store
Query Store è disponibile in tutte le aree senza costi aggiuntivi. Si tratta di una funzionalità di consenso esplicito, quindi non è abilitata per impostazione predefinita in un server. L'archivio query può essere abilitato o disabilitato a livello globale per tutti i database in un determinato server e non può essere attivato o disattivato per ogni database.
Importante
Non abilitare Query Store nel piano tariffario burstable perché causerebbe un impatto sulle prestazioni.
Abilitare Query Store in portale di Azure
- Accedere al portale di Azure e selezionare l'istanza del server flessibile Database di Azure per PostgreSQL.
- Selezionare Parametri server nella sezione Impostazioni del menu.
- Cercare il
pg_qs.query_capture_modeparametro. - Impostare il valore su
TOPoALL, a seconda che si desideri tenere traccia delle query di primo livello o anche delle query annidate (eseguite all'interno di una funzione o di una routine) e fare clic su 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
- Cercare il
pgms_wait_sampling.query_capture_modeparametro. - Impostare il valore su
ALLe 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 esecuzioni di una query in un determinato intervallo di tempo.
- Confronto tra il tempo medio di esecuzione di una query nelle finestre temporali per visualizzare delta di grandi dimensioni.
- Identificazione delle query con esecuzione più lunga nelle ultime ore.
- Identificazione delle prime N query in attesa di risorse.
- Comprendere la natura delle attese per una query specifica.
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 Database di Azure per PostgreSQL. La query seguente restituisce informazioni sulle query in Query Store:
SELECT * FROM query_store.qs_view;
In alternativa, usare questa query per le statistiche di attesa:
SELECT * FROM query_store.pgms_wait_sampling_view;
Trovare query di 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. Cercare in Query Store altre query che modificano la stessa entità e che vengono eseguite spesso e/o hanno durata elevata. 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, valutare la possibilità di introdurre un indice sull'entità sottostante in modo da eseguire ricerche anziché analisi. Questo ridurrebbe al minimo il sovraccarico di I/O delle query. 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. |
| 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. Controllare le raccomandazioni per le prestazioni relative al server nel portale per verificare se sono presenti raccomandazioni sugli indici che ottimizzerebbero queste query. |
Opzioni di configurazione
Quando Query Store è abilitato, salva i dati nelle finestre di aggregazione di lunghezza determinata dal pg_qs.interval_length_minutes parametro del server (il valore predefinito è 15 minuti). Per ogni finestra, archivia le 500 query distinte per finestra.
Per la configurazione dei parametri di Query Store sono disponibili le opzioni seguenti:
| Parametro | Descrizione | Default | Intervallo |
|---|---|---|---|
| pg_qs.query_capture_mode | Imposta le istruzioni di cui verrà tenuta traccia. | Nessuno | none, top, all |
| pg_qs.interval_length_minutes (*) | Imposta l'intervallo di acquisizione query_store in minuti per pg_qs, ovvero la frequenza di persistenza dei dati. | 15 | 1-30 |
| pg_qs.store_query_plans | Attiva o disattiva i piani di query per pg_qs. | spento | on, off |
| pg_qs.max_plan_size | Imposta il numero massimo di byte che verranno salvati per il testo del piano di query per pg_qs; piani più lunghi verranno troncati. | 7500 | 100 - 10.000 |
| pg_qs.max_query_text_length | Imposta la lunghezza massima della query che può essere salvata; le query più lunghe verranno troncate. | 6000 | 100-10000 |
| pg_qs.retention_period_in_days | Imposta la finestra del periodo di conservazione in giorni per pg_qs: dopo questa volta i dati verranno eliminati. | 7 | 1-30 |
| pg_qs.index_generation_interval (*) | Imposta l'intervallo di generazione automatica dell'indice query_store in minuti per pg_qs. | 720 | 15 - 10080 |
| pg_qs.index_recommendations | Abilita o disabilita le raccomandazioni sugli indici. pg_qs.query_capture_mode deve essere anche 'TOP' o 'ALL'. | spento | off, consiglia |
| pg_qs.track_utility | Imposta un valore che indica se i comandi dell'utilità vengono rilevati da pg_qs. | 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 in modo specifico alle statistiche di attesa:
| Parametro | Descrizione | Default | Intervallo |
|---|---|---|---|
| pgms_wait_sampling.query_capture_mode | Seleziona le istruzioni rilevate dall'estensione pgms_wait_sampling. | Nessuno | none, all |
| Pgms_wait_sampling.history_period | Imposta la frequenza, espressa in millisecondi, in cui vengono campionati gli eventi di attesa. | 100 | 1-600000 |
Nota
pg_qs.query_capture_mode prevale su 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.
Usare il portale di Azure per ottenere o impostare un valore diverso per un parametro.
Viste e funzioni
Visualizzare e gestire Query Store usando le viste e le funzioni seguenti. Queste viste possono essere usate da qualsiasi membro del ruolo pubblico di PostgreSQL per visualizzare i dati in Query Store 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 la stessa query_id. Se due query differiscono solo nei valori letterali usati, vengono identificate anche con lo stesso query_id. Per tutte le query identificate con lo stesso query_id, il relativo sql_query_text sarà 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 un singolo 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 query_id. Il testo mantenuto da Query Store è quello della prima query eseguita dopo l'abilitazione della raccolta dati. Di conseguenza, 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 nel 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 le espressioni valutate nella clausola WHERE vengono invertite rispetto columnOne = ? and ColumnTwo = ? al 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 che sono già stati salvati in modo permanente nelle tabelle di supporto di Query Store. I dati registrati in memoria per l'intervallo di tempo attualmente attivo, non sono visibili fino alla fine dell'intervallo di tempo 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 distinto (db_id), utente (user_id) e query (query_id).
| Nome | Type | Riferimenti | Descrizione |
|---|---|---|---|
| runtime_stats_entry_id | bigint | ID della 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 è stata eseguita l'istruzione. |
| 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 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 caratteri. |
|
| plan_id | bigint | ID del piano corrispondente alla query. | |
| start_time | timestamp | Le query vengono aggregate in base alle finestre temporali, il cui intervallo di tempo è definito dal parametro pg_qs.interval_length_minutes del server (il valore predefinito è 15 minuti). Questa è l'ora di inizio corrispondente all'intervallo di tempo per 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, oltre a molte altre unità per ogni processo di lavoro back-end, avviata per collaborare all'esecuzione dei rami paralleli dell'albero di esecuzione. | |
| total_time | double precision | Tempo totale di esecuzione delle query, espresso in millisecondi. | |
| min_time | double precision | Tempo di esecuzione minimo della query, espresso in millisecondi. | |
| max_time | double precision | Tempo massimo di esecuzione delle query, espresso in millisecondi. | |
| mean_time | double precision | Tempo medio di esecuzione della query, espresso 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 guida l'esecuzione della query, oltre alla somma di tutte le righe avviate da ogni processo di lavoro back-end, avviate per collaborare all'esecuzione dei rami paralleli dell'albero di esecuzione, torna al processo back-end di guida. | |
| shared_blks_hit | bigint | Numero totale di riscontri nella cache dei blocchi condivisi dall'istruzione . | |
| shared_blks_read | bigint | Numero totale di blocchi condivisi letti dall'istruzione . | |
| shared_blks_dirtied | bigint | Numero totale di blocchi condivisi sporsi 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 dall'istruzione . | |
| local_blks_read | bigint | Numero totale di blocchi locali letti dall'istruzione . | |
| local_blks_dirtied | bigint | Numero totale di blocchi locali sporsi dall'istruzione . | |
| local_blks_written | bigint | Numero totale di blocchi locali scritti dall'istruzione . | |
| temp_blks_read | bigint | Numero totale di blocchi temporanei letti dall'istruzione . | |
| temp_blks_written | bigint | Numero totale di blocchi temporanei scritti dall'istruzione . | |
| blk_read_time | double precision | Tempo totale impiegato per la lettura dei blocchi, in millisecondi (se track_io_timing è abilitato, in caso contrario zero). | |
| blk_write_time | double precision | Tempo totale impiegato per la scrittura dei blocchi, espresso in millisecondi (se track_io_timing è abilitato, in caso contrario zero). | |
| is_system_query | boolean | Determina se la query è stata eseguita dal ruolo con user_id = 10 (azuresu), che dispone di privilegi avanzati e viene usata per eseguire operazioni del riquadro di controllo. Poiché questo servizio è un servizio PaaS gestito, solo Microsoft fa parte di tale ruolo di utente con privilegi avanzati. | |
| query_type | Testo | Tipo di operazione rappresentata dalla query. I valori possibili sono unknown, select, update, insert, mergedelete, utility, nothing. undefined |
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 (sconosciuti), 1 (select), 2 (update), (insert), 3 (delete), 45 (utility), 6 (nothing). Nella versione di PostgreSQL >= 15, i valori possibili sono 0 (sconosciuti), 1 (select), (update), 2 (insert), 3 (delete), 45 (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 distinto (db_id), utente (user_id), query (query_id) e evento (evento).
| Nome | Type | Riferimenti | Descrizione |
|---|---|---|---|
| start_time | timestamp | Le query vengono aggregate in base alle finestre temporali, il cui intervallo di tempo è definito dal parametro pg_qs.interval_length_minutes del server (il valore predefinito è 15 minuti). Questa è l'ora di inizio corrispondente all'intervallo di tempo per questa voce. |
|
| end_time | timestamp | Ora di fine corrispondente all'intervallo di tempo per questa voce. | |
| 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 è stata eseguita l'istruzione. |
| query_id | bigint | Codice hash interno, calcolato dall'albero di analisi dell'istruzione. | |
| event_type | Testo | Tipo di evento per il quale il back-end è in attesa. | |
| evento | 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 colonne event_type ed evento 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. In questo modo verranno archiviati solo i piani di query per le query non di utilità.
| plan_id | db_id | query_id | plan_text |
|---|---|---|---|
| plan_id | bigint | Valore hash del piano di query normalizzato prodotto da EXPLAIN. Viene considerato 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 è stata eseguita l'istruzione. |
| query_id | bigint | Codice hash interno, calcolato dall'albero di analisi dell'istruzione. | |
| plan_text | varchar(10000) | Piano di esecuzione dell'istruzione data cost=false, buffers=false e format=text. Si tratta dello stesso output fornito da EXPLAIN. |
Funzioni
query_store.qs_reset
Questa funzione elimina tutte le statistiche raccolte finora da Query Store. Elimina entrambe le statistiche per le finestre temporali già chiuse, che sono state rese persistenti in nelle tabelle disco, e quelle per l'intervallo di tempo corrente, che vengono ancora mantenute in memoria. Questa funzione può essere eseguita solo dal ruolo di amministratore del server (azure_pg_admin).
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. Questa funzione può essere eseguita solo dal ruolo di amministratore del server (azure_pg_admin).
Limitazioni e problemi noti
compatibilità Archiviazione di Azure e Query Store
A causa di problemi di compatibilità, non è possibile abilitare le estensioni di Archiviazione di Azure e Query Store contemporaneamente. Per garantire il corretto funzionamento ed evitare potenziali conflitti, abilitare solo una di queste estensioni alla volta.
Per usare Archiviazione di Azure:
- Disabilitare Query Store impostando il parametro
pg_qs.query_capture_modesuNONE. Questo parametro è dinamico, quindi non è necessario riavviare.
Per usare Query Store:
- Disabilitare l'estensione Archiviazione di Azure eseguendo
DROP EXTENSION azure_storage;. - Rimuovere Archiviazione di Azure da
shared_preload_libraries. - Riavviare il server di database.
Questi passaggi sono necessari per evitare conflitti e garantire che il sistema funzioni correttamente. Microsoft sta lavorando per risolvere questi problemi di compatibilità e ti manterrà informato degli eventuali aggiornamenti.
Modalità di sola lettura
Quando un'istanza del server flessibile 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.