Comment puis-je corréler le plan de requête avec le rapport de requête dans Amazon Redshift ?

Dernière mise à jour : 28/10/2020

J' essaie de mettre en corrélation le plan de requête avec le rapport de requête dans mon cluster Amazon Redshift. Comment dois-je procéder ?

Brève description

Pour déterminer l'utilisation requise pour exécuter une requête dans Amazon Redshift, utilisez la commande EXPLAIN. La commande EXPLAIN affiche le plan d'exécution d'une instruction de requête sans réellement exécuter la requête. Le plan d'exécution décrit les étapes de planification et d'exécution de la requête impliquées.

Ensuite, utilisez la vue système SVL_QUERY_REPORT pour afficher les informations de la requête au niveau d'une tranche de cluster. Vous pouvez utiliser les informations au niveau de la tranche pour détecter une distribution inégale des données dans le cluster, ce qui a un impact sur les performances de la requête.

Remarque : Dans SVL_QUERY_REPORT, la colonne enregistrements indique le nombre d’enregistrements qui sont traitées par tranche de cluster. La colonne rows_pre_filter indique le nombre total d’enregistrements émis avant le filtrage des enregistrements marqués pour la suppression.

Amazon Redshift traite le plan de requête et le traduit en étapes, segments et flux. Pour plus d'informations, consultez Workflow d'exécution et de planification de requête.

Résolution

Création d'une table et récupération du plan d'explication et du rapport de requête SVL pour la requête

1.    Créez deux tables avec des clés de tri et des clés de distribution différentes.

2.    Exécutez la requête suivante où la jointure n'a pas été effectuée sur une clé de distribution :

select eventname, sum (pricepaid) from sales, event where sales.eventid = event.eventid group by eventname order by 2 desc;

Cette requête distribue la table intérieure à tous les nœuds de calcul.

3.    Récupérez le plan de la requête :

EXPLAIN <query>;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1002815368414.24..1002815368415.67 rows=571 width=27)
   Merge Key: sum(sales.pricepaid)
   ->  XN Network  (cost=1002815368414.24..1002815368415.67 rows=571 width=27)
         Send to leader
         ->  XN Sort  (cost=1002815368414.24..1002815368415.67 rows=571 width=27)
               Sort Key: sum(sales.pricepaid)
               ->  XN HashAggregate  (cost=2815368386.67..2815368388.10 rows=571 width=27)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=109.98..2815367496.05 rows=178125 width=27)
                           Hash Cond: ("outer".eventid = "inner".eventid)
                           ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=14)
                           ->  XN Hash  (cost=87.98..87.98 rows=8798 width=21)
                                 ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=21)
(12 rows)

4.    Exécutez la requête suivante :

select * from svl_query_report where query = <query_id> order by segment, step, elapsed_time, rows;

Mappage du plan de requête avec le rapport de requête

1.    Exécutez la requête suivante pour obtenir le svl_query_report :

select query,slice,segment,step,start_time,end_time,elapsed_time,rows,bytes,label from svl_query_report where query = 938787 and segment = 0 order by segment, step, elapsed_time, rows;

EXPLAIN <query>;
->  XN Hash  (cost=87.98..87.98 rows=8798 width=21)
   ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=21)

Voici un exemple de sortie :

 query  | slice | segment | step |         start_time         |         end_time          | elapsed_time | rows | bytes  |            label              
--------+-------+---------+------+----------------------------+---------------------------+--------------+------+--------+------------------------------
 938787 |     0 |       0 |    0 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4383 | 128626 | scan   tbl=278788 name=event 
 938787 |     1 |       0 |    0 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4415 | 128918 | scan   tbl=278788 name=event 
 938787 |     0 |       0 |    1 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4383 |      0 | project                      
 938787 |     1 |       0 |    1 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4415 |      0 | project                      
 938787 |     0 |       0 |    2 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4383 | 126660 | bcast                        
 938787 |     1 |       0 |    2 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4415 | 126920 | bcast                        

 (6 rows)

Cette sortie indique que lorsque la valeur du segment est 0, Amazon Redshift effectue une opération de parcours séquentiel pour analyser le tableau des événements.

2.    Exécutez la requête suivante pour obtenir le rapport de requête du segment 1 :

select query,slice,segment,step,start_time,end_time,elapsed_time,rows,bytes,label from svl_query_report where query = 938787 and segment = 1 order by segment, step, elapsed_time, rows;

Voici un exemple de sortie :

query  | slice | segment | step |       start_time          |          end_time          | elapsed_time | rows | bytes  |     label           
--------+-------+---------+------+----------------------------+----------------------------+--------------+------+--------+-------------------------------------------
938787 |     1 |       1 |    0 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.830037 |         3173 |    0 |      0 | scan   tbl=376297 name=Internal Worktable 
 38787 |     0 |       1 |    0 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.831142 |         4278 | 8798 | 253580 | scan   tbl=376297 name=Internal Worktable 
938787 |     1 |       1 |    1 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.830037 |         3173 |    0 |      0 | project                                   
938787 |     0 |       1 |    1 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.831142 |         4278 | 8798 |      0 | project                                   
938787 |     1 |       1 |    2 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.830037 |         3173 |    0 |      0 | hash   tbl=439                            
938787 |     0 |       1 |    2 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.831142 |         4278 | 8798 | 359156 | hash   tbl=439                            
(6 rows)

La requête continue à s'exécuter jusqu'à ce que la valeur du segment soit 1 et qu'une opération de table de hachage soit effectuée sur la table intérieure de la jointure.

3.    Exécutez la requête suivante pour obtenir le SVL_QUERY_REPORT pour une requête dont la valeur de segment est 2 :

EXPLAIN <query>;
->  XN Hash Join DS_BCAST_INNER  (cost=109.98..2815367496.05 rows=178125 width=27)
                           Hash Cond: ("outer".eventid = "inner".eventid)
   ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=14)

4.    Exécutez la requête suivante :

select query,slice,segment,step,start_time,end_time,elapsed_time,rows,bytes,label from svl_query_report where query = 938787 and segment = 2 order by segment, step, elapsed_time, rows;

Voici un exemple de sortie :

  query  | slice | segment | step |         start_time         |          end_time          | elapsed_time | rows  |  bytes  |            label             
--------+-------+---------+------+----------------------------+----------------------------+--------------+-------+---------+------------------------------
 938787 |     1 |       2 |    0 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 | 1730380 | scan   tbl=278792 name=sales 
 938787 |     0 |       2 |    0 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 | 1718740 | scan   tbl=278792 name=sales 
 938787 |     1 |       2 |    1 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 |       0 | project                      
 938787 |     0 |       2 |    1 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 |       0 | project                      
 938787 |     1 |       2 |    2 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 |       0 | project                      
 938787 |     0 |       2 |    2 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 |       0 | project                      
 938787 |     1 |       2 |    3 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 |       0 | hjoin  tbl=439               
 938787 |     0 |       2 |    3 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 |       0 | hjoin  tbl=439               
 938787 |     1 |       2 |    4 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 |       0 | project                      
 938787 |     0 |       2 |    4 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 |       0 | project                      
 938787 |     1 |       2 |    5 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 |       0 | project                      
 938787 |     0 |       2 |    5 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 |       0 | project                      
 938787 |     1 |       2 |    6 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 |   576 |   34916 | aggr   tbl=448               
 938787 |     0 |       2 |    6 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 |   576 |   34916 | aggr   tbl=448               
 938787 |     1 |       2 |    7 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 |   576 |   17728 | dist                         
 938787 |     0 |       2 |    7 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 |   576 |   17768 | dist                         
(16 rows)   

Dans cet exemple de sortie, la requête est exécutée lorsque la valeur du segment est 2 et effectue une opération de parcours séquentiel pour analyser la table des ventes. Dans le même segment, une opération d'agrégation est effectuée pour agréger les résultats et une opération de jointure par hachage est effectuée pour joindre les tables. Les colonnes de jointure d'une des tables n'est pas une clé de distribution ou une clé de tri. Par conséquent, la table intérieure est distribuée à tous les nœuds de calcul en tant que DS_BCAST_INNER, ce qui peut être dans le plan EXPLAIN. 

5.    Exécutez la requête suivante pour obtenir le SVL_QUERY_REPORT pour une requête dont la valeur de segment est 3 :

select query,slice,segment,step,start_time,end_time,elapsed_time,rows,bytes,label from svl_query_report where query = 938787 and segment = 3 order by segment, step, elapsed_time, rows;

->  XN Sort  (cost=1002815368414.24..1002815368415.67 rows=571 width=27)
               Sort Key: sum(sales.pricepaid)
   ->  XN HashAggregate  (cost=2815368386.67..2815368388.10 rows=571 width=27)

Voici un exemple de sortie :

 query  | slice | segment | step |         start_time         |          end_time          | elapsed_time | rows | bytes |                   label                   
--------+-------+---------+------+----------------------------+----------------------------+--------------+------+-------+-------------------------------------------
 938787 |     1 |       3 |    0 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |  546 | 28792 | scan   tbl=376298 name=Internal Worktable 
 938787 |     0 |       3 |    0 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |  606 | 31824 | scan   tbl=376298 name=Internal Worktable 
 938787 |     1 |       3 |    1 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |  273 | 16580 | aggr   tbl=451                            
 938787 |     0 |       3 |    1 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |  303 | 18336 | aggr   tbl=451                            
 938787 |     1 |       3 |    2 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |  273 |     0 | project                                   
 938787 |     0 |       3 |    2 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |  303 |     0 | project                                   
 938787 |     1 |       3 |    3 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |  273 | 14396 | sort   tbl=453                            
 938787 |     0 |       3 |    3 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |  303 | 15912 | sort   tbl=453                            
 938787 |     1 |       3 |    4 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |    0 |     0 | merge                                     
 938787 |     0 |       3 |    4 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |    0 |     0 | merge                                     
 938787 |     1 |       3 |    5 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |    0 |     0 | aggr   tbl=456                            
 938787 |     0 |       3 |    5 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |    0 |     0 | aggr   tbl=456                            
 938787 |     1 |       3 |    6 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |    0 |     0 | project                                   
 938787 |     0 |       3 |    6 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |    0 |     0 | project                                   
(14 rows)

La requête continue à s'exécuter jusqu'à ce que la valeur du segment soit 3 et qu'une opération d'agrégation de hachage et une opération de tri soient effectuées. Une opération d'agrégation de hachage est effectuée sur des fonctions d'agrégation groupées non triées. L'opération de tri est effectuée pour évaluer la clause ORDER BY.

6.    Exécutez la requête suivante pour obtenir le SVL_QUERY_REPORT pour une requête dont la valeur de segment est 4 et 5 : 

select query,slice,segment,step,start_time,end_time,elapsed_time,rows,bytes,label from svl_query_report where query = 938787 and segment = 4  order by segment, step, elapsed_time, rows;

 query  | slice | segment | step |         start_time         |          end_time          | elapsed_time | rows | bytes |                 label                 
--------+-------+---------+------+----------------------------+----------------------------+--------------+------+-------+----------------------------------------
 938787 |     1 |       4 |    0 | 2020-05-22 11:11:48.915148 | 2020-05-22 11:11:48.915215 |           67 |  273 | 14396 | scan   tbl=453 name=Internal Worktable
 938787 |     0 |       4 |    0 | 2020-05-22 11:11:48.915148 | 2020-05-22 11:11:48.915424 |          276 |  303 | 15912 | scan   tbl=453 name=Internal Worktable
 938787 |     1 |       4 |    1 | 2020-05-22 11:11:48.915148 | 2020-05-22 11:11:48.915215 |           67 |  273 |  8888 | return                                 
 938787 |     0 |       4 |    1 | 2020-05-22 11:11:48.915148 | 2020-05-22 11:11:48.915424 |          276 |  303 |  8864 | return                                 
 938787 |  6411 |       5 |    1 | 2020-05-22 11:11:48.914501 | 2020-05-22 11:11:48.916049 |         1548 |  576 |     0 | project
 938787 |  6411 |       5 |    2 | 2020-05-22 11:11:48.914501 | 2020-05-22 11:11:48.916049 |         1548 |  576 | 18360 | return
 938787 |  6411 |       5 |    0 | 2020-05-22 11:11:48.914501 | 2020-05-22 11:11:48.916049 |         1548 |    0 |     0 | merge
 (7 rows)

Une fois tous les segments utilisés, la requête exécute une opération réseau sur les segments 4 et 5 pour envoyer des résultats intermédiaires au nœud principal. Les résultats sont envoyés au nœud principal pour traitement supplémentaire.

Une fois la requête exécutée, utilisez la requête suivante pour vérifier le temps d'exécution de la requête en millisecondes :

select datediff (ms, exec_start_time, exec_end_time) from stl_wlm_query where query= 938787;

date_diff 
-----------
 101
(1 row)

Optimisation de votre requête

Pour optimiser votre requête lors de l'analyse du plan de requête, effectuez les opérations suivantes :

1.    Identifiez les étapes ayant le coût le plus élevé.

2.    Vérifiez s'il existe des opérations de tri très coûteuses. Notez que les performances d'une requête dépendent de la méthode de distribution des données ainsi que des données analysées par la requête. Veillez à sélectionner le style de distribution approprié pour une table afin de minimiser l'impact de l'étape de redistribution. En outre, utilisez une clé de tri pour les colonnes appropriées afin d'améliorer la vitesse des requêtes et de réduire le nombre de blocs à analyser. Pour plus d'informations sur la manière de choisir les clés de distribution et de tri, consultez le playbook sur la conception avancée des tables d'Amazon Redshift : styles de distribution et clés de distribution.

Les exemples suivants utilisent la table STL_ALERT_EVENT_LOG pour identifier et corriger les problèmes potentiels de performances de requête :

select query, btrim(event) as event, btrim(solution) as solution from stl_alert_event_log where query = 940313;

 query  |              event               |          solution          
--------+----------------------------------+-------------------------
 940313 | Missing query planner statistics | Run the ANALYZE command
 (1 row)

Dans cet exemple de sortie, la requête de la commande ANALYZE peut être utilisée pour améliorer les performances de la requête car les statistiques de la requête sont obsolètes.

Vous pouvez également utiliser le plan EXPLAIN pour voir si des alertes sont générées pour la requête :

explain select eventname, sum (pricepaid) from sales, event where sales.eventid = event.eventid group by eventname order by 2 desc;
                                                QUERY PLAN                   
                                     
-----------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1029210993681.72..1029210993682.22 rows=200 width=330)
   Merge Key: sum(sales.pricepaid)
   ->  XN Network  (cost=1029210993681.72..1029210993682.22 rows=200 width=330)
         Send to leader
         ->  XN Sort  (cost=1029210993681.72..1029210993682.22 rows=200 width=330)
               Sort Key: sum(sales.pricepaid)
               ->  XN HashAggregate  (cost=29210993673.57..29210993674.07 rows=200 width=330)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=109.98..29210955741.88 rows=7586340 width=330)
                           Hash Cond: ("outer".eventid = "inner".eventid)
                           ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=16)
                           ->  XN Hash  (cost=87.98..87.98 rows=8798 width=322)
                                 ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=322)
 ----- Tables missing statistics: event, sales -----
 ----- Update statistics by running the ANALYZE command on these tables -----

3.    Vérifiez les types de jointure.

Remarque : Une boucle imbriquée est la jointure la moins optimale car elle est principalement utilisée pour les jointures croisées et certaines jointures d'inégalité.

L'exemple suivant montre une jointure croisée entre deux tables. Une jointure par boucles imbriquées est utilisée et la première valeur de coût est 0.00. Cette valeur de coût est le coût relatif du retour du premier enregistrement de l'opération de jointure croisée. La deuxième valeur (3901467082.32) indique le coût relatif de la réalisation de l'opération de jointure croisée. Notez la différence de coût entre le premier et le dernier enregistrement. Les boucles imbriquées ont un impact négatif sur les performances de votre cluster en surchargeant la file d'attente de requêtes longues :

explain select * from sales cross join event;
                                             QUERY PLAN                   
                                    
---------------------------------------------------------------------------------------------------------
 XN Nested Loop DS_BCAST_INNER  (cost=0.00..3901467082.32 rows=1517267888 width=88)
   ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=53)
   ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=35)
 ----- Nested Loop Join in the query plan - review the join predicates to avoid Cartesian products -----
(4 rows)

Remarque : Amazon Redshift sélectionne un opérateur de jointure en fonction du style de distribution de la table et de l'emplacement des données requises.

Pour optimiser les performances de la requête, la clé de tri et la clé de distribution ont été modifiées en « eventid » pour les deux tables. Dans l'exemple suivant, la jointure de fusion est utilisée à la place d'une jointure par hachage :

explain select eventname, sum (pricepaid) from sales, event where sales.eventid = event.eventid group by eventname order by 2 desc;
                                           QUERY PLAN                       
                           
------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000000004965.12..1000000004966.55 rows=571 width=27)
   Merge Key: sum(sales.pricepaid)
   ->  XN Network  (cost=1000000004965.12..1000000004966.55 rows=571 width=27)
         Send to leader
         ->  XN Sort  (cost=1000000004965.12..1000000004966.55 rows=571 width=27)
               Sort Key: sum(sales.pricepaid)
               ->  XN HashAggregate  (cost=4937.55..4938.98 rows=571 width=27)
                     ->  XN Merge Join DS_DIST_NONE  (cost=0.00..4046.93 rows=178125 width=27)
                           Merge Cond: ("outer".eventid = "inner".eventid)
                           ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=14)
                           ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=21)
(11 rows)

4.    Identifiez les opérateurs de diffusion dont les opérations sont très coûteuses.

Remarque : Pour les petites tables, les opérateurs de diffusion ne sont pas toujours considérés comme non optimaux car la redistribution des petites tables n'a pas autant d'impact relatif sur les performances des requêtes.

5.    Exécutez la requête suivante pour vérifier le temps d'exécution de la requête.

select datediff (ms, exec_start_time, exec_end_time) from stl_wlm_query where query= 940715;

 date_diff 
-----------
 34
 (1 row)

Une différence de temps d'exécution pour les deux requêtes confirme que le plan de requête est correctement corrélé avec le rapport de requête.


Cet article vous a-t-il été utile ?


Besoin d'aide pour une question technique ou de facturation ?