Condividi tramite

Ottimizzazione degli indici in Database di Azure per PostgreSQL - Server flessibile (anteprima)

  • Articolo

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

L'ottimizzazione degli indici è una funzionalità del server flessibile di Database di Azure per PostgreSQL che migliora automaticamente le prestazioni del carico di lavoro analizzando le query rilevate e fornendo raccomandazioni sugli indici.

Si tratta di un'offerta integrata nel server flessibile di Database di Azure per PostgreSQL, che si basa sulla funzionalità Monitoraggio delle prestazioni con Query Store. L'ottimizzazione dell'indice analizza il carico di lavoro rilevato da Query Store e produce raccomandazioni sugli indici per migliorare le prestazioni del carico di lavoro analizzato o per eliminare indici duplicati o inutilizzati.

Descrizione generale dell'algoritmo di ottimizzazione dell'indice

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 ritiene che qualsiasi raccomandazione possa produrre un impatto significativo 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é vengono identificati come duplicati o come non usati per un periodo di tempo configurabile.

Raccomandazioni relative alla CREAZIONE DI INDICI

Per ogni database identificato come candidato all'analisi per la generazione di raccomandazioni sugli indici, tutte le query SELECT eseguite durante l'intervallo di ricerca e nel contesto di tale database specifico vengono fattorizzate.

Nota

L'ottimizzazione degli indici attualmente non analizza le istruzioni DML (UPDATE, INSERT, DELETE e MERGE) o consiglia indici che ne migliorano le prestazioni. Inoltre, non è stato ancora considerato l'impatto degli indici raccomandati per migliorare le prestazioni delle query SELECT sulle istruzioni DML.

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.

Nota

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

Se una query fa riferimento a una colonna di una tabella e tale tabella non dispone di statistiche, l'intera query viene ignorata e non viene restituita alcuna raccomandazione sull'indice per migliorarne l'esecuzione.

L'analisi necessaria per raccogliere statistiche può essere attivata manualmente usando il comando ANALYZE o automaticamente dal daemon autovacuum.

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.

Nota

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 dell'indice genera automaticamente i nomi degli indici consigliati, che in genere sono costituiti dai nomi delle diverse colonne chiave separate da "_" (underscore) 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 all'ELIMINAZIONE DI INDICI

Per ogni database per il quale è stata determinata la funzionalità di ottimizzazione dell'indice, deve avviare una nuova sessione e, al termine della fase di raccomandazione CREATE INDEX, consiglia di eliminare gli indici per due possibili motivi:

  • Perché sono considerati duplicati di altri.
  • Perché non vengono utilizzati per un periodo di tempo configurabile.

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 una quantità minima (media giornaliera) di DML index_tuning.unused_dml_per_table nella tabella in cui viene creato l'indice.
  • Evidenzino una quantità minima (media giornaliera) di letture index_tuning.unused_reads_per_table nella tabella in cui viene creato l'indice.

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.

Configurazione dell'ottimizzazione degli indici

L'ottimizzazione dell'indice può essere abilitata, disabilitata e configurata tramite un set di parametri che ne controllano il comportamento, come la frequenza con la quale la sessione di ottimizzazione viene eseguita.

Esplorare tutti i dettagli sulla configurazione corretta della funzionalità di ottimizzazione degli indici in come abilitare, disabilitare e configurare l'ottimizzazione dell'indice.

Informazioni generate dall'ottimizzazione dell'indice

Come leggere, interpretare e utilizzare le raccomandazioni prodotte dall'ottimizzazione dell'indice descrive in dettaglio come ottenere e utilizzare le raccomandazioni generate dall'ottimizzazione dell'indice.

Limitazioni e supporto

L'ottimizzazione degli indici in Database di Azure per PostgreSQL - Server flessibile presenta le limitazioni seguenti:

  • L'ottimizzazione dell'indice è attualmente in fase di anteprima e potrebbe presentare alcune limitazioni o restrizioni.
  • La funzionalità è disponibile in aree specifiche. L'elenco completo è disponibile nella sezione Aree supportate.
  • L'ottimizzazione degli indici è supportata in tutti i livelli attualmente disponibili (con possibilità di burst, per utilizzo generico e ottimizzato per la memoria) e in qualsiasi SKU di calcolo attualmente supportato con almeno 4 vCore.
  • La funzionalità è supportata nelle versioni principali 14 o successive del server flessibile di Database di Azure per PostgreSQL.
  • Le istruzioni preparate non vengono analizzate per produrre raccomandazioni.
  • Nelle repliche in lettura o quando un'istanza è in modalità di sola lettura, l'ottimizzazione dell'indice non è supportata.
  • È importante considerare le implicazioni della creazione di indici consigliati nei server con disponibilità elevata o repliche in lettura, soprattutto quando si stima che le dimensioni degli indici siano elevate.

Per altre informazioni sull'ottimizzazione degli indici e sugli articoli correlati, vedere i collegamenti alla documentazione forniti in Contenuti correlati.

Aree geografiche supportate

Non esiste alcuna limitazione a livello di area per la funzionalità di ottimizzazione degli indici. È disponibile in tutte le aree in cui è disponibile il server flessibile di Database di Azure per PostgreSQL.

Livelli e SKU supportati

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

Importante

Se un server dispone dell'ottimizzazione degli indici abilitata e viene ridotto a un calcolo con un numero inferiore rispetto a quello minimo di vCore necessari, la funzionalità rimarrà abilitata. Poiché la funzionalità non è supportata nei server con meno di 4 vCore, se si prevede di abilitarla in un server con meno di 4 vCore oppure si prevede di ridurre l'istanza a meno di 4 vCore, assicurarsi di disabilitare prima l'ottimizzazione dell'indice, impostando index_tuning.mode su OFF.

Versioni supportate di PostgreSQL

L'ottimizzazione degli indici è supportata nelle versioni principali 14 o successive del server flessibile di Database di Azure per PostgreSQL.

Importante

Sebbene sia possibile abilitare la funzionalità nelle istanze che eseguono versioni inferiori alla 14, tale operazione non è prevista perché la funzionalità non è supportata in tali versioni.

Istruzioni preparate

Attualmente, l'ottimizzazione dell'indice non analizza le istruzioni preparate.

Modalità di sola lettura e repliche in lettura

Quando un'istanza del server flessibile di Database di Azure per PostgreSQL è in modalità di sola lettura, ad esempio quando il parametro default_transaction_read_only è impostato su on, o se la modalità di sola lettura viene abilitata automaticamente a causa del raggiungimento della capacità di archiviazione, Query Store non acquisisce dati.

Inoltre, l'ottimizzazione dell'indice non è attualmente supportata nelle repliche in lettura. Tutte le raccomandazioni visualizzate su una replica in lettura sono state prodotte sulla replica primaria dopo aver analizzato il carico di lavoro in essa registrato.

Considerazioni importanti

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 gli indici consigliati vengono implementati. Prestare particolare attenzione quando si creano indici le cui dimensioni sono stimate come molto grandi.

Contenuto correlato