Condividi tramite

Ottimizzazione autonoma

Il tuning autonomo è una funzionalità nel tuo server flessibile di Azure Database per PostgreSQL che analizza le query tracciate dal tuo carico di lavoro e fornisce raccomandazioni per migliorare le prestazioni di tali query.

Si tratta di una funzionalità integrata nella tua istanza di Azure Database per server flessibile PostgreSQL, basata sulla funzionalità dell'archivio query. L'ottimizzazione autonoma analizza il carico di lavoro rilevato dall'archivio query e produce raccomandazioni sugli indici o sulle tabelle per migliorare le prestazioni del carico di lavoro analizzato. Può produrre raccomandazioni per creare nuovi indici, eliminare indici duplicati o inutilizzati, analizzare tabelle che non dispongono di statistiche o statistiche obsolete o tabelle a vuoto.

Descrizione generale dell'algoritmo di ottimizzazione autonoma

Quando il parametro index_tuning.mode del server è configurato su report, le sessioni di ottimizzazione vengono avviate automaticamente con la frequenza configurata nel parametro index_tuning.analysis_interval del server, espressa in minuti.

Nella prima fase, la sessione di ottimizzazione cerca l'elenco dei database in cui considera che le raccomandazioni che potrebbero produrre potrebbero influire significativamente sulle prestazioni complessive del sistema. A tale scopo, raccoglie tutte le query registrate da Query Store le cui esecuzioni sono state acquisite nell'intervallo di ricerca su cui si concentra la sessione di ottimizzazione. Al momento, l'intervallo di ricerca si estende agli ultimi index_tuning.analysis_interval minuti, a partire dall'ora di inizio della sessione di ottimizzazione.

Per tutte le query avviate dall'utente con esecuzioni registrate in Query Store e le cui statistiche di runtime non vengono reimpostate, il sistema le classifica in base al tempo di esecuzione totale aggregato. Si incentra sulle query più importanti, in base alla loro durata.

Le query seguenti vengono escluse da tale elenco:

  • Query avviate dal sistema, ovvero le query eseguite dal ruolo azuresu.
  • Query eseguite nel contesto di qualsiasi database di sistema (azure_sys, template0, template1 e azure_maintenance).

L'algoritmo esegue l'iterazione sui database di destinazione, cercando possibili indici eventualmente in grado di migliorare le prestazioni dei carichi di lavoro analizzati. Cerca anche gli indici che possono essere eliminati perché sono duplicati o non usati per un periodo di tempo configurabile. Identifica anche le tabelle che non contengono statistiche correnti o che sono sovraccariche.

Raccomandazioni relative alla CREAZIONE DI INDICI

Per ogni database identificato come candidato per l'analisi, tutte le query SELECT, UPDATE, INSERT e DELETE eseguite durante l'intervallo di ricerca e nel contesto di tale database specifico vengono considerate come fattori.

Il set di query risultante viene classificato in base al tempo di esecuzione totale aggregato e il primo index_tuning.max_queries_per_database viene analizzato per individuare le possibili raccomandazioni sugli indici.

Le potenziali raccomandazioni mirano a migliorare le prestazioni di questi tipi di query:

  • Query con filtri, ovvero query con predicati nella clausola WHERE.
  • Le query che si uniscono a più relazioni, indipendentemente dal fatto che seguano la sintassi in cui i join vengono espressi con la clausola JOIN o se i predicati di join sono espressi nella clausola WHERE.
  • Query che combinano filtri e predicati di join.
  • Query con raggruppamento (query con una clausola GROUP BY).
  • Query che combinano filtri e raggruppamento.
  • Query con ordinamento (query con una clausola ORDER BY).
  • Query che combinano filtri e ordinamento.

Annotazioni

L'unico tipo di indici attualmente consigliato dal sistema è B-Tree.

Se una query fa riferimento a una colonna di una tabella e tale tabella non contiene statistiche, non genera raccomandazioni sugli indici per migliorarne l'esecuzione. Tuttavia, genera un consiglio per analizzare la tabella.

index_tuning.max_indexes_per_table specifica il numero di indici che possono essere consigliati, escludendo gli indici eventualmente già esistenti sulla tabella per ogni singola tabella a cui si fa riferimento da un numero qualsiasi di query durante una sessione di ottimizzazione.

index_tuning.max_index_count specifica il numero di raccomandazioni sugli indici prodotte per tutte le tabelle di ogni singolo database analizzato durante una sessione di ottimizzazione.

Affinché venga generata una raccomandazione sull'indice, il motore di ottimizzazione deve stimare che questa migliori almeno una query nel carico di lavoro analizzato da un fattore specificato con index_tuning.min_improvement_factor.

Allo stesso modo, vengono controllate tutte le raccomandazioni sugli indici per far sì che non introducano regressioni in nessuna delle singole query presenti in tale carico di lavoro di un fattore specificato con index_tuning.max_regression_factor.

Annotazioni

index_tuning.min_improvement_factor e index_tuning.max_regression_factor fanno entrambi riferimento al costo dei piani di query, non alla durata o alle risorse utilizzate durante l'esecuzione.

Tutti i parametri indicati nei paragrafi precedenti, i valori predefiniti e gli intervalli validi sono descritti nelle opzioni di configurazione.

Lo script prodotto insieme alla raccomandazione per creare un indice segue questo modello:

CREATE INDEX CONCURRENTLY {indexName} ON {schema}.{table}({column_name}[, ...])

Include la clausola CONCURRENTLY. Per altre informazioni sugli effetti di questa clausola, vedere la documentazione ufficiale di PostgreSQL per CREATE INDEX.

L'ottimizzazione autonoma genera automaticamente i nomi degli indici consigliati, che in genere sono costituiti dai nomi delle diverse colonne chiave separate da "_" (caratteri di sottolineatura) e con un suffisso "_idx" costante. Se la lunghezza totale del nome supera i limiti di PostgreSQL o se si scontra con eventuali relazioni esistenti, il nome è leggermente diverso. Potrebbe essere troncato e alla fine del nome potrebbe essere aggiunto un numero.

Calcolare l'impatto di una raccomandazione CREATE INDEX

L'impatto della creazione di una raccomandazione sull'indice viene misurato su IndexSize (in megabyte) e QueryCostImprovement (in percentuale).

IndexSize è un valore singolo che rappresenta le dimensioni stimate dell'indice, considerando la cardinalità corrente della tabella e le dimensioni delle colonne a cui fa riferimento l'indice consigliato.

QueryCostImprovement è costituito da una matrice di valori, in cui ciascun elemento rappresenta il miglioramento del costo del piano per ogni query il cui costo del piano si stima migliorerebbe se esistesse tale indice. Ogni elemento mostra l'identificatore della query (sottoposto a query) e la percentuale in base alla quale il costo del piano migliorerebbe se la raccomandazione venisse implementata (dimensionale).

Raccomandazioni relative a DROP INDEX e REINDEX

Per ogni database identificato come candidato, deve avviare una nuova sessione e dopo il completamento della fase di raccomandazione CREATE INDEX, consiglia di eliminare o reindicizzare gli indici esistenti, in base ai criteri seguenti:

  • Eliminare se è considerato duplicato di altri.
  • Eliminare se non viene usato per un periodo di tempo configurabile.
  • Reindicizzare gli indici contrassegnati come non validi.

Eliminare indici duplicati

Raccomandazioni per l'eliminazione di indici duplicati: identificare prima di tutto quali indici hanno duplicati.

I duplicati vengono classificati in base a funzioni diverse che possono essere attribuite all'indice e in base alle dimensioni stimate.

Infine, il consiglio è di eliminare tutti i duplicati con una classificazione inferiore rispetto al relativo leader di riferimento, descrivendo il motivo per cui ogni duplicato è stato così classificato.

Affinché due indici vengano considerati duplicati, è necessario che:

  • Vengano creati nella stessa tabella.
  • Siano un indice dello stesso tipo.
  • Le colonne chiave corrispondano e, per le chiavi indice a più colonne, corrispondano all'ordine in cui sono referenziate.
  • Corrispondano all’albero delle espressioni del predicato. Applicabile solo agli indici parziali.
  • Corrispondano all’albero delle espressioni di tutti i riferimenti a colonne nonsimple. Applicabile solo agli indici creati nelle espressioni.
  • Corrispondano alle regole di confronto di ogni colonna a cui si fa riferimento nella chiave.

Eliminare gli indici inutilizzati

Le raccomandazioni per eliminare gli indici inutilizzati identificano gli indici che:

  • Non vengono usati per almeno index_tuning.unused_min_period giorni.
  • Evidenzino un numero minimo (media giornaliera) di DML index_tuning.unused_dml_per_table nella tabella in cui viene creato l'indice.
  • Evidenzino un numero minimo (media giornaliera) di letture index_tuning.unused_reads_per_table nella tabella in cui viene creato l'indice.

Reindicizzare gli indici non validi

Le raccomandazioni per la reindicizzazione degli indici esistenti identificano gli indici contrassegnati come non validi. Per altre informazioni sui motivi e sui casi in cui gli indici sono contrassegnati come non validi, vedere la documentazione ufficiale di REINDEX in PostgreSQL.

Calcolare l'impatto di una raccomandazione DROP INDEX

L'impatto di una raccomandazione drop index viene misurato su due dimensioni: Benefit (in percentuale) e IndexSize (in megabyte).

Il vantaggio è un valore singolo che al momento può essere ignorato.

IndexSize è un valore singolo che rappresenta le dimensioni stimate dell'indice, considerando la cardinalità corrente della tabella e le dimensioni delle colonne a cui fa riferimento l'indice consigliato.

Raccomandazioni per le tabelle

Per ogni database identificato come candidato da analizzare, avvia una sessione che mira a produrre raccomandazioni a livello di tabella. Tali raccomandazioni invitano a eseguire ANALYZE o VACUUM nelle tabelle a cui si accede dalle query esaminate, per cui il motore di ottimizzazione considera l'esecuzione di tali comandi potrebbe migliorare le prestazioni del carico di lavoro.

Raccomandazioni per le tabelle ANALYZE

Raccomandazioni per l'analisi di una tabella identificano le tabelle che:

  • Sono fatti riferimento in una query e una colonna di tale tabella viene utilizzata in uno dei relativi predicati (WHERE, JOIN, ORDER BY, GROUP BY) e soddisfa anche una delle due condizioni seguenti:
    • Non sono mai state analizzate.
    • Sono state analizzate a un certo punto, ma ora mancano statistiche (in genere perché il server si è arrestato in modo anomalo prima che le statistiche siano state mantenute su disco).

Raccomandazioni per la tabella VACUUM

Le raccomandazioni per la pulizia delle tabelle identificano quelle sovraccaricate. Queste raccomandazioni vengono generate solo quando autovacuum_enabled non è impostato su off a livello di server quando il carico di lavoro viene analizzato.

Configurare l'ottimizzazione autonoma

L'ottimizzazione autonoma può essere abilitata, disabilitata e configurata tramite un set di parametri che ne controllano il comportamento.

Quando l'ottimizzazione autonoma è abilitata, viene riattivata con una frequenza configurata nel index_tuning.analysis_interval parametro del server (il valore predefinito è 720 minuti o 12 ore) e inizia ad analizzare il carico di lavoro registrato dall'archivio query durante tale periodo.

Si noti che se si modifica il valore per index_tuning.analysis_interval, viene osservato solo dopo il completamento dell'esecuzione pianificata successiva. Ad esempio, se si abilita l'ottimizzazione autonoma un giorno alle 10:00, poiché il valore predefinito per index_tuning.analysis_interval è 720 minuti, la prima esecuzione viene pianificata per l'avvio alle 10:00 dello stesso giorno. Le modifiche apportate al valore di index_tuning.analysis_interval tra le 10:00 e le 22:00 non hanno alcun effetto su quella pianificazione iniziale. Solo al termine dell'esecuzione pianificata, leggerà il valore corrente impostato per index_tuning.analysis_interval e pianifica l'esecuzione successiva in base a tale valore.

Per la configurazione dei parametri di ottimizzazione autonoma sono disponibili le opzioni seguenti:

Parameter Descrizione Predefinita Intervallo Units
index_tuning.analysis_interval Imposta la frequenza con cui viene attivata ogni sessione di ottimizzazione degli indici quando index_tuning.mode è impostato su REPORT. 720 60 - 10080 minutes
index_tuning.max_columns_per_index Numero massimo di colonne che possono far parte della chiave di indice per qualsiasi indice consigliato. 2 1 - 10
index_tuning.max_index_count Numero massimo di indici consigliati per ogni database durante una sessione di ottimizzazione. 10 1 - 25
index_tuning.max_indexes_per_table Numero massimo di indici che possono essere consigliati per ogni tabella. 10 1 - 25
index_tuning.max_queries_per_database Numero di query più lente per ogni database per cui è possibile consigliare gli indici. 25 5 - 100
index_tuning.max_regression_factor Regressione accettabile introdotta da un indice consigliato in una delle query analizzate durante una sessione di ottimizzazione. 0.1 0.05 - 0.2 percentuale
index_tuning.max_total_size_factor Dimensione totale massima, in percentuale dello spazio su disco totale, che tutti gli indici consigliati per qualsiasi database specificato possono usare. 0.1 0 - 1 percentuale
index_tuning.min_improvement_factor Miglioramento dei costi che un indice consigliato deve fornire ad almeno una delle query analizzate durante una sessione di ottimizzazione. 0.2 0 - 20 percentuale
index_tuning.mode Configura l'ottimizzazione degli indici come disabilitata (OFF) o abilitata per generare solo raccomandazioni. Richiede l'abilitazione di Query Store impostando pg_qs.query_capture_mode su TOP o ALL. OFF OFF, REPORT
index_tuning.unused_dml_per_table Numero minimo di operazioni DML medie giornaliere che interessano la tabella, pertanto gli indici inutilizzati vengono considerati per l'eliminazione. 1000 0 - 9999999
index_tuning.unused_min_period Numero minimo di giorni in cui l'indice non è stato usato, in base alle statistiche di sistema, quindi viene considerato per l'eliminazione. 35 30 - 70
index_tuning.unused_reads_per_table Numero minimo di operazioni di lettura medie giornaliere che interessano la tabella in modo che gli indici inutilizzati vengano considerati per l'eliminazione. 1000 0 - 9999999

Se si usano i az postgres flexible-server autonomous-tuning show-settings comandi dell'interfaccia della riga di comando e az postgres flexible-server autonomous-tuning set-settings per visualizzare o modificare una delle impostazioni di ottimizzazione autonoma, i valori accettati come argomenti per il --name parametro sono quelli visualizzati nella colonna Parameter della tabella precedente, ma senza includere il prefisso index_tuning..

Informazioni prodotte dall'ottimizzazione autonoma

Usare le raccomandazioni di ottimizzazione autonoma descrive in dettaglio come ottenere e usare le raccomandazioni prodotte dall'ottimizzazione autonoma.

Limitazioni e supporto

Di seguito è riportato l'elenco delle limitazioni e dell'ambito di supporto per l'ottimizzazione autonoma.

Eliminazione automatica delle raccomandazioni

Le raccomandazioni vengono eliminate automaticamente 35 giorni dopo l'ultima produzione. Per il funzionamento di questo meccanismo di eliminazione automatica, è necessario abilitare l'ottimizzazione autonoma.

Dipendenza dall'estensione ipopg

Per la regolazione automatica per produrre raccomandazioni CREATE INDEX, usa l'estensione hypopg.

Se l'estensione esiste già all'inizio di una sessione di ottimizzazione, viene usata nello schema in cui è stato creato. Al termine della sessione di ottimizzazione, l'estensione non viene eliminata. Un'eccezione a questa regola è se l'estensione è stata creata nello pg_catalog schema. In questo caso, l'ottimizzazione autonoma esclude l'estensione.

Se l'estensione non esiste in primo luogo o è stata eliminata perché è stata creata nello pg_catalog schema, l'ottimizzazione autonoma lo crea in uno schema denominato ms_temp_recommendations709253 e, al termine della sessione di ottimizzazione, elimina l'estensione e rimuove lo schema.

Gli utenti membri del azure_pg_admin ruolo possono eliminare l'estensione ipopg in qualsiasi momento, anche quando viene creata dalla funzionalità di ottimizzazione autonoma. Tuttavia, l'eliminazione mentre è in esecuzione una sessione di ottimizzazione autonoma potrebbe causare l'esito negativo della sessione e non produrre raccomandazioni.

SKU e livelli di calcolo supportati

L'ottimizzazione autonoma è supportata in tutti i livelli attualmente disponibili: burstable, per utilizzo generico e ottimizzato per la memoria e in qualsiasi SKU di calcolo attualmente supportato con almeno 4 vCore.

Versioni supportate di PostgreSQL

L'ottimizzazione autonoma è supportata nelle versioni principali12 o successive delle istanze del server flessibile di Database di Azure per PostgreSQL.

Uso di search_path

L'ottimizzazione autonoma consuma il valore salvato in modo permanente nella colonna search_path di query_store.qs_view, in modo che, quando ogni query viene analizzata, lo stesso valore di search_path impostato quando la query viene eseguita originariamente sia quello utilizzato per analizzare le possibili raccomandazioni.

Query con parametri

Le query con parametri create con PREPARE o usando il protocollo di query esteso vengono analizzate per produrre raccomandazioni sugli indici.

Per l'analisi delle query con parametri, l'ottimizzazione autonoma richiede che pg_qs.parameters_capture_mode sia impostato su capture_first_sample quando Query Store acquisisce l'esecuzione della query. È inoltre necessario che i parametri vengano acquisiti correttamente da Query Store quando viene eseguita la query. In altre parole, per la query da analizzare, query_store.qs_view deve avere la colonna parameters_capture_status impostata su succeeded.

Modalità di sola lettura e repliche in lettura

Poiché l'ottimizzazione autonoma si basa sui dati che l'archivio delle query mantiene localmente nel database, e poiché non è supportato nelle repliche di lettura o quando un'istanza è in modalità di sola lettura, non la supportiamo nelle repliche di lettura o nelle istanze in modalità di sola lettura.

Tutte le raccomandazioni visualizzate su una replica in lettura sono state generate nella replica primaria dopo l'analisi esclusiva del carico di lavoro eseguito nella replica primaria.

Riduzione delle prestazioni di calcolo

Se l'ottimizzazione autonoma è abilitata in un server e si riduce il calcolo del server a meno del numero minimo di vCore necessari, la funzionalità rimane abilitata. Poiché la funzionalità non è supportata nei server con meno di 4 vCore, non viene eseguita per analizzare il carico di lavoro e produrre raccomandazioni, anche se index_tuning.mode è stata impostata ON su quando il calcolo è stato ridotto. Poiché il server non soddisfa i requisiti minimi, tutti i parametri del server index_tuning.* sono inaccessibili. Ogni volta che si esegue il backup del server in un ambiente di calcolo che soddisfa i requisiti minimi, index_tuning.mode viene configurato con qualsiasi valore impostato prima di ridimensionarlo a un ambiente di calcolo che non soddisfa i requisiti.

Disponibilità elevata e repliche in lettura

Se nel server sono configurate repliche a disponibilità elevata o in lettura, tenere presente le implicazioni associate alla produzione di carichi di lavoro a elevato utilizzo di scrittura nel server primario quando vengono implementati gli indici consigliati. Prestare particolare attenzione quando si creano indici le cui dimensioni sono stimate di grandi dimensioni.

Motivi per cui l'ottimizzazione autonoma potrebbe non produrre raccomandazioni per la creazione di indici per determinate query

Di seguito è riportato un elenco di tipi di query per i quali l'ottimizzazione autonoma non genera raccomandazioni CREATE INDEX. Query che:

  • Si verifica un errore quando il motore di ottimizzazione autonoma tenta di ottenere l'output EXPLAIN durante la fase di analisi.
  • Tabelle di riferimento che non dispongono di statistiche sul relativo contenuto nel catalogo di sistema pg_statistic. Eseguire ANALYZE su tali tabelle in modo che il motore di ottimizzazione possa prendere in considerazione queste query in futuro.
  • Presentano il testo della query troncato nell'archivio query. È il caso in cui la lunghezza del testo della query supera il valore configurato in pg_qs.max_query_text_length.
  • Fanno riferimento a oggetti eliminati o rinominati prima che si verifichi l'analisi. Queste query potrebbero comunque essere sintatticamente valide, ma non semanticamente valide.
  • Accedono a tabelle o indici temporanei nelle tabelle temporanee.
  • Accedono alle visualizzazioni o alle viste materializzate.
  • Accedono alle tabelle partizionate.
  • Vengono identificate come istruzioni di utilità. Le istruzioni di utilità o i comandi di utilità sono, fondamentalmente, qualsiasi istruzione non considerata SELECT, INSERT, UPDATE, DELETE o MERGE e alcuni comandi contenenti una di queste istruzioni.
  • Non sono tra gli ultimi index_tuning.max_queries_per_database più lenti per il database e il periodo analizzati.
  • Sono state eseguite nel contesto di un database specifico, quando nessuna di queste query è stata identificata come la più lenta a livello di server.

Contenuti correlati

  • Last updated on