Ottimizzazione automatica in Database di Azure per PostgreSQL - Server flessibile

L'ottimizzazione autonoma è una funzionalità di Database di Azure per PostgreSQL server flessibile che analizza le query registrate dal carico di lavoro e fornisce raccomandazioni per migliorare le prestazioni di tali query.

Si tratta di una funzionalità integrata in Database di Azure per PostgreSQL - Server flessibile che si basa sulla funzionalità di Query Store. 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.

• Identificare quali indici sono utili per creare perché potrebbero migliorare significativamente le query analizzate durante una sessione di ottimizzazione autonoma.
• Identificare gli indici che sono duplicati esatti e che possono essere eliminati.
• Identificare gli indici non utilizzati in un periodo configurabile e che potrebbero essere candidati all’eliminazione.
• Identificare gli indici contrassegnati come non validi che devono essere reindicizzare per trasformarli in quelli validi.
• Identificare le tabelle che non contengono statistiche correnti da analizzare.
• Identificare le tabelle che risultano gonfie e che devono essere sottoposte a vacuum

Descrizione generale dell'algoritmo di ottimizzazione autonoma

Quando si configura il index_tuning.mode parametro su report, il sistema avvia automaticamente l'ottimizzazione delle sessioni alla frequenza configurata nel index_tuning.analysis_interval parametro, espresso in minuti.

Nella prima fase, la sessione di ottimizzazione cerca l'elenco dei database in cui le raccomandazioni 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 è possibile eliminare perché sono duplicati o non usati per un periodo di tempo configurabile. Identifica anche le tabelle prive di statistiche aggiornate o eccessivamente grandi.

Raccomandazioni relative alla CREAZIONE DI INDICI

Per ogni database identificato come candidato da analizzare, il processo considera tutte le query SELECT, UPDATE, INSERT e DELETE eseguite durante l'intervallo di ricerca e nel contesto di tale database specifico.

Il processo classifica il set di query risultante in base al tempo di esecuzione totale aggregato e analizza la parte superiore index_tuning.max_queries_per_database per individuare 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.

Se una query fa riferimento a una colonna di una tabella e tale tabella non contiene statistiche, il processo non produce 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.

Analogamente, il processo controlla tutte le raccomandazioni sugli indici per assicurarsi che non introducano la regressione in alcuna singola query in tale carico di lavoro di un fattore specificato con index_tuning.max_regression_factor.

Tutti i parametri indicati nei paragrafi precedenti, i relativi 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, il processo avvia una nuova sessione. Al termine della fase di raccomandazione CREATE INDEX , è consigliabile 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

Le raccomandazioni per eliminare gli indici duplicati iniziano identificando gli indici con duplicati.

I duplicati vengono classificati in base a funzioni diverse che è possibile attribuire all'indice e in base alle dimensioni stimate.

Il processo consiglia infine di eliminare tutti i duplicati con una classificazione inferiore rispetto al relativo leader di riferimento e descrive il motivo per cui ogni duplicato è stato classificato come era.

Affinché due indici vengano considerati duplicati, devono:

• Vengano creati nella stessa tabella.
• Siano un indice dello stesso tipo.
• Fai corrispondere le relative colonne chiave e, per le chiavi di indice multicolonna, rispetta l'ordine in cui sono referenziate.
• Corrispondano all’albero delle espressioni del predicato. Questa condizione si applica solo agli indici parziali.
• Corrispondano all’albero delle espressioni di tutti i riferimenti a colonne nonsimple. Questa condizione si applica 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 l'eliminazione di 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 singolo valore che è possibile ignorare per il momento.

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, il processo avvia una sessione che mira a produrre raccomandazioni a livello di tabella. Queste raccomandazioni consigliano di eseguire ANALYZE o VACUUM sulle tabelle a cui accedono le query analizzate. Il motore di ottimizzazione considera che l'esecuzione di questi comandi potrebbe migliorare le prestazioni del carico di lavoro.

Raccomandazioni sulla tabella ANALYZE

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

• Sono referenziate in una query, in cui una colonna di tale tabella è usata in uno dei relativi predicati (WHERE, JOIN, ORDER BY, GROUP BY), e soddisfano anche una delle due condizioni seguenti:
  • Non vengono mai analizzati.
  • 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 lo svuotamento di una tabella identificano le tabelle sovraccariche. Il processo produce queste raccomandazioni solo quando autovacuum_enabled non è impostato su off a livello di server quando il carico di lavoro viene analizzato.

Configurare l'ottimizzazione autonoma

È possibile abilitare, disabilitare e configurare l'ottimizzazione autonoma tramite un set di parametri che ne controllano il comportamento.

Quando si abilita l'ottimizzazione autonoma, viene riattivata a una frequenza configurata nel index_tuning.analysis_interval parametro (che per impostazione predefinita è 720 minuti o 12 ore) e inizia ad analizzare il carico di lavoro registrato dall'archivio query durante tale periodo.

Se si modifica il valore per index_tuning.analysis_interval, il nuovo valore diventa effettivo 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 che apporti al valore di index_tuning.analysis_interval tra le 10:00 e le 22:00 non incidono sulla pianificazione iniziale. Solo quando l'esecuzione pianificata viene completata, legge il valore corrente impostato per index_tuning.analysis_interval e pianifica l'esecuzione successiva in base a tale valore.

Usare le opzioni seguenti per configurare i parametri di ottimizzazione autonoma:

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

L'elenco seguente descrive le limitazioni e l'ambito di supporto per l'ottimizzazione autonoma.

Eliminazione automatica delle raccomandazioni

Il sistema elimina automaticamente le raccomandazioni 35 giorni dopo l’ultima volta che le ha generate. Per consentire il funzionamento di questo meccanismo di eliminazione automatica, è necessario abilitare l'ottimizzazione autonoma.

Dipendenza dall'estensione ipopg

Per generare CREATE INDEX raccomandazioni, l'ottimizzazione autonoma usa l'estensione hypopg.

Se l'estensione esiste all'inizio di una sessione di ottimizzazione, il processo lo usa nello schema in cui è stato creato. Al termine della sessione di ottimizzazione, il processo non elimina l'estensione. 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 al primo posto o il processo lo elimina perché è stato creato nello pg_catalog schema, l'ottimizzazione autonoma lo crea in uno schema denominato ms_temp_recommendations709253. Al termine della sessione di ottimizzazione, il processo 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 la funzionalità di ottimizzazione autonoma lo ha creato. Tuttavia, un'eliminazione mentre è in esecuzione una sessione di tuning autonomo potrebbe impedire il completamento della sessione e non produrre alcuna raccomandazione.

SKU e livelli di calcolo supportati

Database di Azure per PostgreSQL - Server flessibile supporta l'ottimizzazione autonoma in tutti i livelli di servizio attualmente disponibili: Burstable, General Purpose e Memory Optimized. Supporta anche l'ottimizzazione autonoma per qualsiasi SKU di calcolo attualmente supportato con almeno 4 vCore.

Versioni supportate di PostgreSQL

Il Server flessibile di Database di Azure per PostgreSQL supporta l'ottimizzazione autonoma per le versioni principali12 o versioni successive.

Uso di search_path

L'ottimizzazione autonoma usa il valore della colonna search_path di query_store.qs_view. Quando analizza ogni query, usa lo stesso search_path valore impostato quando la query è stata eseguita originariamente per analizzare le possibili raccomandazioni.

Query con parametri

Le query con parametri create con PREPARE o tramite il protocollo di query esteso vengono sottoposte ad analisi sintattica e analizzate per generare 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 Query Store acquisisca correttamente i parametri quando viene eseguita la query. In altre parole, per la query da analizzare, la parameters_capture_status colonna in query_store.qs_view deve essere impostata su succeeded.

Modalità di sola lettura e repliche in lettura

Poiché l'ottimizzazione autonoma si basa sui dati che il Query Store rende persistenti localmente nel azure_sys database e non è supportata sulle repliche di lettura o quando un server è in modalità di sola lettura, la funzionalità non è supportata sulle repliche di lettura o sui server in modalità di sola lettura.

Tutte le raccomandazioni visualizzate in 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 si abilita l'ottimizzazione autonoma in un server e quindi si riduce il calcolo del server a un numero inferiore al 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 è stato impostato ON su quando è stato ridotto il calcolo. Anche se il server non soddisfa i requisiti minimi, tutti i index_tuning.* parametri non sono accessibili. 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 si configura disponibilità elevata o repliche di lettura sul server, tenere presenti le implicazioni associate all'esecuzione di carichi di lavoro a utilizzo intensivo di scrittura sul server primario quando si implementano 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

L'ottimizzazione autonoma non genera CREATE INDEX raccomandazioni per i tipi di query seguenti:

• Query che riscontrano un errore quando il motore di ottimizzazione autonoma tenta di ottenere l'output EXPLAIN durante la fase di analisi.
• Query che fanno riferimento a tabelle prive di statistiche sul loro 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.
• Query con testo di query troncato nell'archivio query. Questo troncamento si verifica quando la lunghezza del testo della query supera il valore configurato in pg_qs.max_query_text_length.
• Query che fanno riferimento a oggetti eliminati o rinominati prima che si verifichi l'analisi. Queste query possono comunque essere sintatticamente valide, ma non sono semanticamente valide.
• Query che accedono a tabelle temporanee o a indici su tabelle temporanee.
• Interrogazioni che accedono a viste o viste materializzate.
• Interrogazioni che accedono a tabelle partizionate.
• Query identificate come istruzioni di utilità. Le istruzioni di utilità o i comandi di utilità sono, fondamentalmente, qualsiasi istruzione non considerata SELECT, , INSERTUPDATEDELETE, o MERGEe determinati comandi contenenti una di queste istruzioni.
• Query che non rientrano tra le index_tuning.max_queries_per_database query più lente per il database e il periodo analizzati.
• Query eseguite nel contesto di un database specifico, quando nessuna di queste query viene identificata come la più lenta a livello di server.

Contenuti correlati

• Query Store.
• Configurare l'ottimizzazione autonoma.
• Usare raccomandazioni per l'ottimizzazione autonoma.