Maintenance des plans d'exécution d'Aurora PostgreSQL

La gestion des plans de requêtes propose des techniques et des fonctions pour ajouter, gérer et améliorer les plans d'exécution.

Évaluation des performances des plans

Une fois que l'optimiseur a capturé des plans en tant que non approuvés, utilisez la fonction apg_plan_mgmt.evolve_plan_baselines pour comparer les plans sur la base de leurs performances réelles. En fonction des résultats de vos analyses des performances, vous pouvez modifier le statut d'un plan de non approuvé en approuvé ou rejeté. Vous pouvez également décider d'utiliser la fonction apg_plan_mgmt.evolve_plan_baselines pour désactiver temporairement un plan s'il ne répond pas à vos exigences.

Approbation de plans plus performants

L'exemple suivant montre comment modifier le statut de plans gérés en approuvé à l'aide de la fonction 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)     

La sortie montre un rapport de performances pour l'instruction rangequery avec des liaisons de paramètres de 1 et 10 000. Le nouveau plan non approuvé (Baseline+1) est plus performant que le meilleur plan précédent approuvé (Baseline). Pour confirmer que le nouveau plan est désormais Approved, vérifiez la vue 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)

Le plan géré inclut désormais deux plans approuvés, qui constituent la référence de plans de l'instruction. Vous pouvez également appeler la fonction apg_plan_mgmt.set_plan_status afin de définir directement le champ de statut d'un plan sur 'Approved', 'Rejected', 'Unapproved' ou 'Preferred'.

Rejet ou désactivation de plans plus lents

Pour rejeter ou désactiver des plans, transférez 'reject' ou 'disable' en tant que paramètre d'action à la fonction apg_plan_mgmt.evolve_plan_baselines. Cet exemple désactive tout plan Unapproved capturé qui est plus lent d'au moins 10 % que le meilleur plan Approved pour l'instruction.

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

Vous pouvez également définir directement un plan sur rejeté ou désactivé. Pour définir directement le champ activé du plan sur true ou false, appelez la fonction apg_plan_mgmt.set_plan_enabled. Pour définir directement le champ de statut d'un plan sur 'Approved', 'Rejected', 'Unapproved' ou 'Preferred', appelez la fonction apg_plan_mgmt.set_plan_status.

Validation des plans

Utilisez la fonction apg_plan_mgmt.validate_plans pour supprimer ou désactiver les plans non valides.

Des plans peuvent devenir non valides ou obsolètes en cas de suppression d'objets dont ils dépendent, tels qu'un index ou une table. Cependant, un plan peut devenir non valide de manière temporaire seulement si l'objet supprimé est recréé. Si un plan non valide est susceptible de redevenir valide plus tard, vous préférerez peut-être le désactiver ou ne rien faire plutôt que le supprimer.

Pour retrouver et supprimer tous les plans qui sont non valides et qui n'ont pas été utilisés au cours de la semaine écoulée, utilisez la fonction apg_plan_mgmt.validate_plans comme suit.

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');

Pour activer ou désactiver directement un plan, utilisez la fonction apg_plan_mgmt.set_plan_enabled.

Correction de plans à l'aide de pg_hint_plan

L'optimiseur de requêtes est conçu pour rechercher un plan optimal pour toutes les instructions et, dans la plupart des cas, il trouve un très bon plan. Il peut toutefois arriver que vous sachiez qu'un plan plus performant que celui généré par l'optimiseur existe. Pour amener l'optimiseur à générer le plan souhaité, deux méthodes sont recommandées : utiliser l'extension pg_hint_plan ou définir des variables Grand Unified Configuration (GUC) dans PostgreSQL :

• Extension pg_hint_plan – Spécifiez un « indicateur » pour modifier le fonctionnement du planificateur à l'aide de l'extension pg_hint_plan de PostgreSQL. Pour installer l'extension pg_hint_plan et en savoir plus sur son utilisation, veuillez consulter la documentation de pg_hint_plan.

• Variables GUC – Remplacez un ou plusieurs paramètres du modèle de coûts ou d'autres paramètres de l'optimiseur, tels que from_collapse_limit ou GEQO_threshold.

Lorsque vous utilisez une de ces techniques pour forcer l'optimiseur de requêtes à utiliser un plan, vous pouvez également utiliser la gestion des plans de requêtes pour capturer et imposer l'utilisation du nouveau plan.

Vous pouvez utiliser l'extension pg_hint_plan pour modifier l'ordre des jointures, les méthodes de jointure ou les chemins d'accès d'une instruction SQL. Utilisez un commentaire SQL avec une syntaxe pg_hint_plan spéciale pour modifier la manière dont l'optimiseur crée un plan. Par exemple, partons de l'hypothèse que l'instruction SQL possède une jointure bidirectionnelle.

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

Supposons ensuite que l'optimiseur choisisse d'utiliser l'ordre des jointures (t1, t2), alors que nous savons que l'ordre (t2, t1) est plus rapide. L'indicateur suivant oblige l'optimiseur à utiliser l'ordre des jointures plus rapide (t2, t1). Incluez EXPLAIN pour que l'optimiseur génère un plan pour l'instruction SQL mais n'exécute pas celle-ci. (Sortie non illustrée.)

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

Les étapes suivantes montrent comment utiliser pg_hint_plan.

Pour modifier le plan généré de l'optimiseur et le capturer à l'aide de pg_hint_plan

1. Activez le mode de capture manuelle.

   SET apg_plan_mgmt.capture_plan_baselines = manual;

2. Spécifiez un indicateur pour l'instruction SQL qui vous intéresse.

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

   Après l'exécution de celle-ci, l'optimiseur capture le plan dans la vue apg_plan_mgmt.dba_plans. Le plan capturé n'inclut pas la syntaxe de commentaire pg_hint_plan spéciale car la gestion des plans de requêtes normalise l'instruction en supprimant les commentaires de début.

3. Visualisez les plans gérés à l'aide de la vue apg_plan_mgmt.dba_plans.

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

4. Définissez le statut du plan sur Preferred. En procédant ainsi, vous garantissez que l'optimiseur choisit d'exécuter ce plan au lieu d'en sélectionner un parmi l'ensemble de plans approuvés lorsque le plan à coût minimal n'a pas encore le statut Approved ou Preferred.

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

5. Désactivez la capture manuelle des plans et imposez l'utilisation de plans gérés.

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

   Désormais, lorsque l'instruction SQL initiale s'exécutera, l'optimiseur choisira un plan Approved ou Preferred. Si le plan à coût minimal n'est ni Approved ni Preferred, l'optimiseur choisira le plan Preferred.

Suppression de plans

Les plans sont automatiquement supprimés s'ils n'ont pas été utilisés depuis plus d'un mois, plus précisément 32 jours. Il s'agit de la valeur par défaut du paramètre apg_plan_mgmt.plan_retention_period. Vous pouvez modifier la période de conservation du plan en la prolongeant ou en la raccourcissant à partir de la valeur 1. La détermination du nombre de jours depuis qu'un plan a été utilisé est calculée en soustrayant la date last_used de la date actuelle. La date last_used correspond à la date la plus récente à laquelle l'optimiseur a choisi un plan en tant que plan à coût minimal ou à laquelle le plan a été exécuté. La date est enregistrée pour le plan dans la vue apg_plan_mgmt.dba_plans.

Nous vous recommandons de supprimer des plans qui n'ont pas été utilisés depuis longtemps ou qui ne sont pas utiles. Chaque plan possède une date last_used que l'optimiseur met à jour chaque fois qu'il exécute un plan ou le choisit en tant que plan à coût minimal pour une instruction. Vérifiez les dernières dates last_used pour identifier les plans que vous pouvez supprimer en toute sécurité.

La requête suivante renvoie une table à trois colonnes indiquant le nombre total de plans, les plans qui n'ont pas pu être supprimés et les plans supprimés avec succès. Elle comprend une requête imbriquée qui est un exemple d'utilisation de la fonction apg_plan_mgmt.delete_plan pour supprimer tous les plans qui n'ont pas été choisis en tant que plan à coût minimal au cours des 31 derniers jours et son statut n'est pas 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

Pour de plus amples informations, veuillez consulter apg_plan_mgmt.delete_plan.

Pour supprimer des plans qui ne sont pas valides et dont vous pensez qu'ils le resteront, utilisez la fonction apg_plan_mgmt.validate_plans. Cette fonction vous permet de supprimer ou de désactiver des plans non valides. Pour de plus amples informations, veuillez consulter Validation des plans.

Important

Si vous ne supprimez pas les plans superflus, vous risquez de tomber à court de mémoire partagée mise de côté pour la gestion des plans de requêtes. Pour contrôler la quantité de mémoire disponible pour les plans gérés, utilisez le paramètre apg_plan_mgmt.max_plans. Définissez ce paramètre dans votre groupe de paramètres de votre base de données personnalisés, puis réinitialisez votre instance de base de données pour appliquer les modifications. Pour plus d'informations, veuillez consulter le paramètre apg_plan_mgmt.max_plans.

Exportation et importation de plans

Vous pouvez exporter vos plans gérés et les exporter dans une autre instance de base de données.

Pour exporter des plans gérés

Un utilisateur autorisé peut copier tout sous-ensemble de la table apg_plan_mgmt.plans dans une autre table et l'enregistrer à l'aide de la commande pg_dump. Voici un exemple de.

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;

Pour importer des plans gérés

1. Copiez le fichier .tar des plans gérés exportés dans le système dans lequel vous voulez restaurer les plans.

2. Utilisez la commande pg_restore pour copier le fichier .tar dans une nouvelle table.

   % pg_restore --dbname mytargetdatabase -Ft plans_copy.tar

3. Fusionnez la table plans_copy avec la table apg_plan_mgmt.plans, comme montré dans l'exemple suivant.

   Note

   Dans certains cas, il se peut que vous procédiez à un vidage depuis une version de l'extension apg_plan_mgmt et que vous la restauriez dans une autre version. Dans ces cas-là, il se peut que les colonnes de la table des plans soient différentes. Dans ce cas, nommez les colonnes explicitement au lieu d'utiliser 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. Rechargez les plans gérés dans la mémoire partagée et supprimez la table temporaire des plans.

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