Aurora PostgreSQL QPM

Aurora PostgreSQL e' l'implementazione ottimizzata per il Cloud Amazon, disponibile come servizio RDS, del database Open Source PostgreSQL. In questa paginetta vediamo il query plan management (QPM): una funzionalita' molto particolare di Aurora PostgreSQL che consente di gestire gli execution plan delle query SQL. Si tratta di una funzionalita' molto avanzata, disponibile in alcuni altri database (eg. Oracle SQL Plan Management) ma non presente nelle versioni base di PostgreSQL.

Questo documento presenta diversi aspetti di PostgreSQL: Introduzione, Configurazione, Utilizzo, Workflow, ...

Introduzione

L'ottimizzatore SQL ha il compito di scegliere l'execution path migliore per ottenere il risultato di una query. In PostgreSQL l'ottimizzatore utilizza un sofisticato algoritmo cost-based ovvero si basa sulle dimensioni degli oggetti acceduti e sulla selettivita' delle condizioni delle query per valutare il costo di ogni execution path.
Per una corretta valutazione dei costi sono fondamentali sia la presenza di indici adegutati che la raccolta di statistiche aggiornate. Per tale ragione PostgreSQL analizza automaticamente gli oggetti della base dati ad ogni modifica significativa [NdA lo stesso fa anche il DBA con il comando di VACUUM ANALIZE]. Questo fa si che l'ottimizzatore abbia sempre le informazioni piu' aggiornate per scegliere il percorso piu' efficiente per eseguire una query. La nota clausola SQL EXPLAIN consente di verificare il piano d'esecuzione scelto ed il suo costo.

In qualche raro caso pero' capita che l'ottimizzatore scelga una nuova strada meno efficiente di quelle utilizzate in precedenza provocando una regressione nelle prestazioni.
Il QPM e' stato introdotto per evitare regressioni nelle prestazioni consentendo al DBA di gestire gli execution plan come vedremo in questa paginetta.

Configurazione

La configurazione del QPM e' molto semplice: richiede l'impostazione di alcuni parametri e del riavvio dell'istanza.
Sintetizzando molto basta impostare sul cluster rds.enable_plan_management, impostare sul DB apg_plan_mgmt.capture_plan_baselines, apg_plan_mgmt.use_plan_baselines e riavviare l'istanza. Ma vediamo in dettaglio come fare.

In Aurora PostgreSQL sono presenti due gruppi di parametri configurabili da interfaccia grafica: quelli a livello di cluster e quelli a livello di istanza [NdA con PostgreSQL RDS vi e' un solo gruppo di parametri e su un'installazione tradizionale di PostgreSQL i parametri sono gestiti nel file postgresql.conf].
Iniziamo a creare un nuovo DB Cluster Parameter Group:

Va impostato il parametro rds.enable_plan_management parameter a 1:

Creiamo ora un nuovo DB Parameter Group:

Vanno configurati i parametri apg_plan_mgmt.capture_plan_baselines='automatic' ed apg_plan_mgmt.use_plan_baselines='true'. Con le prime versioni di QPM era anche opportuno aumentare il valore di apg_plan_mgmt.max_plans ma nelle versioni piu' recenti il valore di default e' sufficiente alla maggioranza dei casi d'uso:

Ora che le configurazioni sono pronte assegnamo il DB Cluster Parameter Group al nostro cluster:

Modifichiamo senza effettuare immediatamente il riavvio [NdA scegliamo di effettuare il riavvio alla prossima manutenzione]:

Assegnamo i DB Parameter Group alla nostra istanza e questa volta eseguiamo immediatamente effettuando un riavvio:

I passi di configurazione su AWS sono terminati, quando la base dati e' nuovamente disponibile dopo il riavvio dobbiamo creare l'estensione PostgreSQL:

CREATE EXTENSION apg_plan_mgmt;

L'estensione QPM e' disponibile sulla nostra istanza Aurora Postegres e possiamo controllarne la versione nella tabella pg_extension:

Gia' fatto!

Utilizzo

Con la configurazione effettuata la gestione degli execution Plan e' gia' attiva. Poiche' l'impostazione e' su automatic i nuovi piani di esecuzione vengono registrati ed automaticamente approvati. Per verificarlo basta eseguire la stessa query o un EXPLAIN PLAN due volte ed interrogare la tabella del dizionario:

SELECT sql_hash, 
       plan_hash, 
       status, 
       enabled, 
       estimated_total_cost,
       sql_text 
FROM   apg_plan_mgmt.dba_plans;

Quello che normalmente avviene e' che la base dati viene utilizzata per un certo periodo in modalita' automatic per consentire la raccolta dei piani d'esecuzione durante un normale utilizza. Tutti i piani raccolti vengono posti automaticamente in stato Approved.
Per fare in modo che non vengano introdotti nuovi piani d'esecuzione in sostituzione di quelli gia' raccolti si deve impostare apg_plan_mgmt.capture_plan_baselines='manual'. In questo modo i nuovi piani vengono comunque raccolti ma sono posti in stato Unpproved e, sopratutto, l'ottimizzatore utilizzera' solo i piani Approved quando presenti [NdA perche' e' impostato apg_plan_mgmt.use_plan_baselines].

In questo modo una significativa variazione sui dati (eg. un TRUNCATE TABLE seguita da un ANALYZE), la creazione di nuovi indici, un cambio di minor o major version, ... o comunque una qualsiasi variazione che porti l'ottimizzatore alla scelta di un differente piano di esecuzione non generera' differenze: Aurora Pg continuera' ad utilizzare solo i piani di esecuzione approvati!

Naturalmente quando non c'e' nessun piano approvato, ad esempio perche' viene utilizzata una query diversa, verra' utilizzato l'execution path selezionato dall'ottimizzatore: non vi sono alternative e tutte le query vengono comunque eseguite, il QPM agisce solo nella scelta dell'execution path.

Execution Plan Workflow

Oltre al semplice utilizzo presentato nel paragrafo precedente il QPM in realta' consente di gestire in modo completo i plan ed i loro stati.

Innanzi tutto e' possibile cancellare un plan con la funzione apg_plan_mgmt.delete_plan(sql_hash, plan_hash). I piani d'esecuzione raccolti ma non utilizzati per un lungo periodo [NdA il default e' un mese] vengono automaticamente cancellati.

Per far approvare tutti i piani che hanno un costo minore almeno del 10% si utilizza la query:

SELECT apg_plan_mgmt.Evolve_plan_baselines (sql_hash, plan_hash, 1.1, 'approve')
  FROM apg_plan_mgmt.dba_plans
 WHERE status = 'Unapproved';

Un piano puo' essere attivo o meno inoltre gli stati possibili di un plan sono: 'Approved', 'Rejected', 'Unapproved' e 'Preferred'. E' possibile modificare lo stato di ogni singolo piano con le funzioni apg_plan_mgmt.set_plan_enabled e apg_plan_mgmt.set_plan_status.

I piani d'esecuzione sono mantenuti nella tabella apg_plan_mgmt.plans. Si tratta di una normale tabella che puo' essere esportata con pg_dump, copiata con INSERT AS SELECT, ... Questo consente di "migrare" i piani d'esecuzione anche tra istanze differenti. E' cosi' possibile gestire ambienti, versioni, ... differenti tra loro utilizzando non solo le stesse applicazioni e le stesse query ma anche gli stessi piani di esecuzione.

In generale e' il DBA (il superuser o meglio, l'rds_superuser) che gestisce i piani d'esecuzione. E' tuttavia disponibile un ruolo specifico per la la gestione apg_plan_mgmt che puo' essere concesso ad utenti differenti.

Varie ed eventuali

Un corretto disegno logico e fisico della base dati e' fondamentale per ottenere buone prestazioni. Nessun tuning, ottimizzazione o complessa funzionalita' di gestione degli execution plan potranno mai risolvere un problema di disegno della base dati.

Nel 95% dei casi con PostgreSQL e con Aurora PostgreSQL non e' necessario fare nulla: basta creare gli indici corretti, l'autoanalyze colleziona le statistiche corrette e l'ottimizzatore sceglie sempre gli execution path migliori.
Nel 99% dei casi effettuando un tuning dell'autovacuum e dell'autoanalyze oppure, piu' semplicemente, lanciando un VACUUM ANALYZE sulle tabelle piu' utilizzate le statistiche ed i costi vengono valutati correttamente.
In qualche raro caso e' utile in PostgreSQL modificare alcuni parametri che influenzano l'ottimizzatore (eg. SET work_mem = '16MB'; SET random_page_cost = 2.0;) prima di eseguire una query. Con Aurora PostgreSQL e' difficile sia utile eseguire un tuning di questo tipo perche' molti importanti valori di default dipendono dalla DB instance class scelta e non sono minimali come avviene invece con PostgreSQL community.
Siete sfortunati e ricadete nei pochi casi rimasti in cui si presentano comunque regressioni prestazionali? Beh, ora sapete come configurare ed utilizzare il QPM per risolvere il problema!

Molto utili con il apg_plan_mgmt descritto in questa pagina sono anche le estensioni pg_stat_statements e pg_hint_plan.

Il piani sono influenzati dalle eventuali impostazioni (eg. set work_mem) utilizzate per ottimizzare le query e con il QPM e' possibile distinguere tra loro. E' cosi' possibile utilizzare piani ottimizzati per query specifiche senza richiedere modifiche alle applicazioni che eseguono le query.
Questo e' particolarmente utile quando non e' possibile agire sul codice sorgente inserendo le opportune impostazioni prima di eseguire una query perche' l'applicazione e' di una terza parte.

Maggiori dettagli sul QPM si trovano nella documentazione ufficiale.

Il QPM e' disponibile su Aurora PostgreSQL dalla versione 2.1.0 [NdA la versione 2.0 di Aurora corrisponde alla versione 10 Community di PostgreSQL].
Seguite i link per verificare gli aggiornamenti delle release PostgreSQL e dei servizi Amazon RDS di cui Aurora PostgreSQL fa parte.

Titolo: Aurora PostgreSQL QPM
Livello: Avanzato (3/5)
Data: 14 Febbraio 2020 ❤️
Versione: 1.0.0 - 14 Febbraio 2021
Autori: mail [AT] meo.bogliolo.name, Ivan Brocchetti