Gestione dei piani di esecuzione di Aurora PostgreSQL

La gestione del piano di query fornisce tecniche e funzioni per aggiungere, gestire e migliorare i piani di esecuzione.

Valutazione delle prestazioni del piano

Dopo che l'ottimizzatore acquisisce i piani come non approvati, utilizza la funzione apg_plan_mgmt.evolve_plan_baselines per confrontare i piani in base alle prestazioni effettive. A seconda dell'esito degli esperimenti sulle prestazioni, puoi modificare lo stato di un piano da non approvato ad approvato o rifiutato. Puoi invece decidere di utilizzare la funzione apg_plan_mgmt.evolve_plan_baselines per disabilitare temporaneamente un piano se non soddisfa i requisiti.

Approvazione dei piani migliori

Nell'esempio seguente viene illustrato come modificare lo stato dei piani gestiti per l'approvazione utilizzando la funzione apg_plan_mgmt.evolve_plan_baselines.

SELECT apg_plan_mgmt.evolve_plan_baselines (
   sql_hash, 
   plan_hash, 
   min_speedup_factor := 1.0, 
   action := 'approve'
) 
FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';

NOTICE:     rangequery (1,10000)
NOTICE:     Baseline   [ Planning time 0.761 ms, Execution time 13.261 ms]
NOTICE:     Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms]
NOTICE:     Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms
NOTICE:     Unapproved -> Approved
evolve_plan_baselines 
-----------------------
0
(1 row)     

L'output mostra un rapporto delle prestazioni per l'istruzione rangequery con i binding di parametri 1 e 10.000. Il nuovo piano non approvato (Baseline+1) è migliore del piano migliore approvato in precedenza (Baseline). Per confermare che il nuovo piano è ora Approved, controlla la visualizzazione apg_plan_mgmt.dba_plans.

SELECT sql_hash, plan_hash, status, enabled, stmt_name 
FROM apg_plan_mgmt.dba_plans;

sql_hash  | plan_hash |  status  | enabled | stmt_name  
------------+-----------+----------+---------+------------
1984047223 | 512153379 | Approved | t       | rangequery
1984047223 | 512284451 | Approved | t       | rangequery
(2 rows)

Il piano gestito ora include due piani approvati che rappresentano la baseline del piano dell'istruzione. Puoi anche chiamare la funzione apg_plan_mgmt.set_plan_status per impostare direttamente il campo di stato di un piano su 'Approved', 'Rejected', 'Unapproved' o 'Preferred'.

Rifiuto o disabilitazione dei piani più lenti

Per rifiutare o disabilitare i piani, passa 'reject' o 'disable' come parametro di operazione alla funzione apg_plan_mgmt.evolve_plan_baselines. Questo esempio disabilita qualsiasi piano Unapproved acquisito che è più lento per almeno il 10 percento del miglior piano Approved per l'istruzione.

SELECT apg_plan_mgmt.evolve_plan_baselines(
sql_hash,  -- The managed statement ID
plan_hash, -- The plan ID
1.1,       -- number of times faster the plan must be 
'disable'  -- The action to take. This sets the enabled field to false.
)
FROM apg_plan_mgmt.dba_plans
WHERE status = 'Unapproved' AND   -- plan is Unapproved
origin = 'Automatic';       -- plan was auto-captured

Puoi anche impostare direttamente un piano su rifiutato o disabilitato. Per impostare direttamente il campo abilitato di un piano su true o false, chiama la funzione apg_plan_mgmt.set_plan_enabled. Per impostare direttamente il campo di stato di un piano su 'Approved', 'Rejected', 'Unapproved' o 'Preferred', chiama la funzione apg_plan_mgmt.set_plan_status.

Convalida dei piani

Usa la funzione apg_plan_mgmt.validate_plans per eliminare o disabilitare i piani non validi.

I piani possono diventare non validi (obsoleti) quando gli oggetti da cui dipendono vengono rimossi, ad esempio un indice o una tabella. Tuttavia, un piano potrebbe diventare non valido solo temporaneamente se l'oggetto rimosso viene poi ricreato. Se un piano non valido può diventare successivamente valido, potresti preferire di disabilitare un piano non valido o non fare nulla anziché eliminarlo.

Per trovare ed eliminare tutti i piani che non sono validi e non sono stati utilizzati nell'ultima settimana, utilizza la funzione apg_plan_mgmt.validate_plans come segue.

SELECT apg_plan_mgmt.validate_plans(sql_hash, plan_hash, 'delete') 
FROM apg_plan_mgmt.dba_plans
WHERE last_used < (current_date - interval '7 days');

Per abilitare o disabilitare un piano direttamente, utilizza la funzione apg_plan_mgmt.set_plan_enabled.

Correzione dei piani mediante pg_hint_plan

L'ottimizzatore di query è progettato per trovare un piano ottimale per tutte le istruzioni e nella maggior parte dei casi trova un buon piano. Tuttavia, occasionalmente potresti realizzare che esiste un piano molto migliore di quello generato dall'ottimizzatore. Due modi consigliati per ottenere che l'ottimizzatore generi un piano desiderato sono l'uso dell'estensione pg_hint_plan o l'impostazione delle variabili GUC (Grand Unified Configuration) in PostgreSQL:

• Estensione pg_hint_plan – Specifica un "hint" per modificare il funzionamento del pianificatore utilizzando l'estensione pg_hint_plan di PostgreSQL. Per installare e ottenere ulteriori informazioni su come utilizzare l'estensione pg_hint_plan, consulta la documentazione di pg_hint_plan.

• Variabili GUC – Sostituisci uno o più parametri del modello di costo o altri parametri dell'ottimizzatore, come ad esempio from_collapse_limit o GEQO_threshold.

Quando utilizzi una di queste tecniche per imporre all'ottimizzatore di query di utilizzare un piano, puoi utilizzare anche la gestione del piano di query per acquisire e imporre l'utilizzo del nuovo piano.

È possibile utilizzare l'estensione pg_hint_plan per modificare l'ordine di join, i metodi di join oi percorsi di accesso per un'istruzione SQL. Utilizza un commento SQL con la speciale sintassi pg_hint_plan per modificare il modo in cui l'ottimizzatore crea un piano. Ad esempio, supponiamo che l'istruzione SQL del problema abbia un join bidirezionale.

SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;            

Quindi, supponiamo che l'ottimizzatore scelga l'ordine di join (t1, t2), ma si sa che l'ordine di join (t2, t1) è più veloce. Il seguente hint forza l'ottimizzatore a utilizzare l'ordine di join più veloce, (t2, t1). Includi EXPLAIN in modo che l'ottimizzatore generi un piano per l'istruzione SQL ma senza eseguire l'istruzione (output non mostrato).

/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id; 

I passaggi seguenti illustrano come utilizzare pg_hint_plan.

Per modificare il piano generato dall'ottimizzatore e acquisire il piano utilizzando pg_hint_plan

1. Attivare la modalità di acquisizione manuale.

   SET apg_plan_mgmt.capture_plan_baselines = manual;

2. Specificare un hint per l'istruzione SQL desiderata.

   /*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
   FROM t1, t2 
   WHERE t1.id = t2.id; 

   Al termine dell'esecuzione, l'ottimizzatore acquisisce il piano nella visualizzazione apg_plan_mgmt.dba_plans. Il piano acquisito non include la speciale sintassi dei commenti pg_hint_plan perché la gestione del piano di query normalizza l'istruzione rimuovendo i commenti iniziali.

3. Visualizzare i piani gestiti utilizzando la visualizzazione apg_plan_mgmt.dba_plans.

   SELECT sql_hash, plan_hash, status, sql_text, plan_outline 
   FROM apg_plan_mgmt.dba_plans;

4. Impostare lo stato del piano su Preferred. In questo modo si assicura che l'ottimizzatore scelga di eseguirlo invece di selezionare un piano dall'insieme dei piani approvati quando il piano a costo minimo non è già Approved o Preferred.

   SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' ); 

5. Disattivare l'acquisizione del piano manuale e applicare l'utilizzo dei piani gestiti.

   SET apg_plan_mgmt.capture_plan_baselines = false;
   SET apg_plan_mgmt.use_plan_baselines = true;

   Ora, quando viene eseguita l'istruzione SQL originale, l'ottimizzatore sceglierà un piano Approved o Preferred. Se il piano a costo minimo non è Approved o Preferred, l'ottimizzatore sceglierà il piano Preferred.

Eliminazione dei piani

I piani vengono eliminati automaticamente se non vengono utilizzati da più di un mese, specificatamente, 32 giorni. Questa è l'impostazione di default per il parametro apg_plan_mgmt.plan_retention_period. È possibile modificare il periodo di conservazione del piano in un periodo di tempo più lungo o più breve a partire dal valore di 1. La determinazione del numero di giorni dall'ultimo utilizzo di un piano viene calcolata sottraendo la data last_used dalla data corrente. La data last_used si riferisce alla data più recente in cui l'ottimizzatore ha scelto un piano come il piano di costo minimo o alla data di esecuzione del piano. La data viene archiviata per il piano nella vista apg_plan_mgmt.dba_plans.

Ti consigliamo di eliminare i piani che non sono stati utilizzati per molto tempo o che non sono utili. Ad ogni piano è assegnata una data last_used che viene aggiornata dall'ottimizzatore ogni volta che esegue il piano o che sceglie il piano come il piano a costo minimo per un'istruzione. Controlla le date last_used più recenti per identificare i piani che è possibile eliminare in modo sicuro.

La seguente query restituisce una tabella a tre colonne con il numero totale di piani, i piani che non sono stati eliminati e i piani eliminati con successo. Include una query annidata che è un esempio di utilizzo della funzione apg_plan_mgmt.delete_plan per eliminare tutti i piani che non sono stati scelti come il piano a costo minimo negli ultimi 31 giorni e il cui stato non è Rejected.

SELECT (SELECT COUNT(*) from apg_plan_mgmt.dba_plans) total_plans,
       COUNT(*) FILTER (WHERE result = -1) failed_to_delete,
       COUNT(*) FILTER (WHERE result = 0) successfully_deleted
       FROM (
            SELECT apg_plan_mgmt.delete_plan(sql_hash, plan_hash) as result
            FROM apg_plan_mgmt.dba_plans
            WHERE last_used < (current_date - interval '31 days')
            AND status <> 'Rejected'
            ) as dba_plans ;   

 total_plans | failed_to_delete | successfully_deleted
-------------+------------------+----------------------
           3 |                0 |                    2

Per ulteriori informazioni, consulta apg_plan_mgmt.delete_plan.

Per eliminare i piani non validi e che si prevede rimangano non validi, utilizza la funzione apg_plan_mgmt.validate_plans. Questa funzione consente di eliminare o disabilitare i piani non validi. Per ulteriori informazioni, consulta Convalida dei piani.

Importante

Se i piani estranei non vengono eliminati, esiste il rischio di esaurimento della memoria condivisa dedicata alla gestione del piano di query. Per controllare la quantità di memoria disponibile per i piani gestiti, utilizzare il parametro apg_plan_mgmt.max_plans. Impostare questo parametro nel gruppo di parametri database personalizzati e riavviare l'istanza database per rendere effettive le modifiche. Per ulteriori informazioni, vedi il parametro apg_plan_mgmt.max_plans.

Esportazione e importazione dei piani

Puoi esportare i piani gestiti e importarli in un'altra istanza database.

Per esportare i piani gestiti

Un utente autorizzato può copiare qualsiasi sottoinsieme della tabella apg_plan_mgmt.plans in un'altra tabella e quindi salvarlo utilizzando il comando pg_dump. Di seguito è riportato un esempio.

CREATE TABLE plans_copy AS SELECT * 
FROM apg_plan_mgmt.plans [ WHERE predicates ] ;

% pg_dump --table apg_plan_mgmt.plans_copy -Ft mysourcedatabase > plans_copy.tar

DROP TABLE apg_plan_mgmt.plans_copy;

Per importare i piani gestiti

1. Copiare il file .tar dei piani gestiti esportati nel percorso di sistema in cui devono essere ripristinati i piani.

2. Usare il comando pg_restore per copiare il file tar in una nuova tabella.

   % pg_restore --dbname mytargetdatabase -Ft plans_copy.tar

3. Unire la tabella plans_copy alla tabella apg_plan_mgmt.plans, come mostrato nell'esempio seguente.

   Nota

   In alcuni casi, potresti dover eseguire il dump da una versione dell'estensione apg_plan_mgmt e ripristinarlo in una versione diversa. In questi casi, le colonne nella tabella dei piani potrebbero essere diverse. In tal caso, denominare le colonne esplicitamente anziché utilizzare SELECT *.

   INSERT INTO apg_plan_mgmt.plans SELECT * FROM plans_copy
    ON CONFLICT ON CONSTRAINT plans_pkey
    DO UPDATE SET
    status = EXCLUDED.status,
    enabled = EXCLUDED.enabled,
    -- Save the most recent last_used date 
    --
    last_used = CASE WHEN EXCLUDED.last_used > plans.last_used 
    THEN EXCLUDED.last_used ELSE plans.last_used END, 
    -- Save statistics gathered by evolve_plan_baselines, if it ran:
    --
    estimated_startup_cost = EXCLUDED.estimated_startup_cost,
    estimated_total_cost = EXCLUDED.estimated_total_cost,
    planning_time_ms = EXCLUDED.planning_time_ms,
    execution_time_ms = EXCLUDED.execution_time_ms,
    total_time_benefit_ms = EXCLUDED.total_time_benefit_ms, 
    execution_time_benefit_ms = EXCLUDED.execution_time_benefit_ms;

4. Ricaricare i piani gestiti nella memoria condivisa e rimuovere la tabella dei piani temporanei.

   SELECT apg_plan_mgmt.reload(); -- refresh shared memory
   DROP TABLE plans_copy;