Comment résoudre les problèmes d'utilisation élevée de l'UC dans Amazon Redshift ?

Brève description

Amazon Redshift est conçu pour utiliser toutes les ressources disponibles lors de l'exécution de requêtes. Cela signifie que vous pouvez vous attendre à des pics d'utilisation de l’UC dans votre cluster Redshift. Une augmentation de l'utilisation de l'UC peut être due à des facteurs tels que la charge de travail du cluster, des données asymétriques et non triées ou les tâches de nœud principal.

Toutefois, si l'utilisation de l'UC a un impact sur le temps d’exécution des requêtes, veuillez envisager les approches suivantes :

• Examinez votre charge de travail de cluster Amazon Redshift
• Entretenez l'intégrité de vos données.
• Mettez à jour la conception de votre table.
• Vérifiez les mises à jour de maintenance.
• Vérifiez les pics d'utilisation de l'UC de nœud principal.
• Utilisez Amazon CloudWatch pour surveiller les pics d'utilisation de l'UC.

Solution

Examiner votre charge de travail de cluster Redshift

Les facteurs suivants peuvent avoir un impact sur l'utilisation de l'UC sur votre cluster Redshift :

• Une charge de travail accrue (due à un plus grand nombre de requêtes exécutées). L'augmentation de la charge de travail entraîne l'augmentation du nombre de connexions à la base de données, entraînant ainsi une simultanéité accrue des requêtes.
• Le nombre plus élevé de requêtes simultanées affecte le conflit des ressources, le temps d'attente de verrouillage et le temps d'attente de la gestion de la charge de travail (WLM).
• Plus de connexions aux bases de données. Cela peut être dû à des sessions inactives présentes dans le cluster. Les séances inactives peuvent entraîner des problèmes de conflit de verrouillage supplémentaires.

Pendant que les requêtes sont en cours d'exécution, récupérez les informations de verrouillage. Pour identifier les séances de longue durée, utilisez la requête SQL suivante :

select *,datediff(s,txn_start,getdate())/86400||' days '||datediff(s,txn_start,getdate())%86400/3600||' hrs '||datediff(s,txn_start,getdate())%3600/60||' mins '||datediff(s,txn_start,getdate())%60||' secs' as "duration"
from svv_transactions where lockable_object_type='transactionid' and pid<>pg_backend_pid() order by 3;<br>

Ensuite, exécutez PG_TERMINATE_BACKEND pour arrêter les transactions de longue durée. Pour empêcher ces séances de rester ouvertes, assurez-vous que toutes les transactions sont fermées. Par exemple, assurez-vous que toutes les transactions commençant par une instruction BEGIN sont accompagnées d'une instruction END ou COMMIT.

Ensuite, exécutez la requête SQL suivante pour identifier les requêtes qui sont à l'origine de la consommation élevée de l'UC :

select stq.userid, stq.query, trim(stq.label) as label, stq.xid, stq.pid, svq.service_class,
query_cpu_usage_percent as "cpu_%",starttime, endtime, datediff(s,starttime, endtime) as duration_s,
substring(stq.querytxt,1,100) as querytext from stl_query stq
join svl_query_metrics svq on stq.query=svq.query 
where query_cpu_usage_percent is not null and starttime > sysdate - 1
order by query_cpu_usage_percent desc;

Pour analyser les étapes d'exécution au niveau des segments et des tranches pour chaque requête, exécutez la requête suivante :

select query, segment, step, label ,is_rrscan as rrS, is_diskbased as disk, is_delayed_scan as DelayS, min(start_time) as starttime, max(end_time) as endtime, datediff(ms, min(start_time), max(end_time)) as "elapsed_msecs", sum(rows) as row_s , sum(rows_pre_filter) as rows_pf, CASE WHEN sum(rows_pre_filter) = 0 THEN 100 ELSE sum(rows)::float/sum(rows_pre_filter)::float*100 END as pct_filter, SUM(workmem)/1024/1024 as "Memory(MB)", SUM(bytes)/1024/1024 as "MB_produced" from svl_query_report where query in (<query_ids>) group by query, segment, step, label , is_rrscan, is_diskbased , is_delayed_scan order by query, segment, step, label;

Pour plus d'informations sur le réglage de ces requêtes, consultez les 10 meilleures techniques de réglage des performances d'Amazon Redshift.

Vous pouvez également utiliser la vue wlm_query_trend_hourly pour consulter le modèle de charge de travail de cluster Redshift. Ensuite, déterminez laquelle des approches suivantes peut vous aider à réduire le temps d'attente de la file d'attente :

• Réduire la simultanéité des requêtes par file d'attente pour fournir plus de mémoire à chaque emplacement de requête. Cela aide les requêtes qui nécessitent plus de mémoire à s'exécuter plus efficacement.
• Activer l'accélération des requêtes courtes (SQA) pour prioriser les requêtes à exécution courte par rapport aux requêtes à exécution longue.
• Mettre à l'échelle le cluster Redshift de façon à ce qu'il s'adapte à l'augmentation de la charge de travail. La mise à l'échelle d'un cluster fournit plus de mémoire et de puissance de calcul, ce qui peut aider les requêtes à s'exécuter plus rapidement. Pour plus d'informations, consultez Comment redimensionner un cluster Redshift ?

Entretenir l'intégrité des données

L'intégrité des données est mesurée par le pourcentage de statistiques obsolètes et de lignes non triées présentes dans une table. Lorsque ces deux pourcentages sont élevés, le système d'optimisation des requêtes peut générer un plan d'exécution dans lequel les requêtes s'exécutent de manière inefficace lors du référencement de tables. Les données non triées peuvent entraîner une analyse inutile de blocs de données, qui nécessite des opérations d'I/O supplémentaires Les requêtes peu performantes affectent de façon négative l'utilisation de l'UC de votre cluster Redshift.

Utilisez la vue système SVV_TABLE_INFO pour récupérer les données stats_off et les données de pourcentage unsorted d'une table. Ces pourcentages doivent rester proches de 0. S'ils sont élevés, exécutez l'utilitaire de schéma Analyser & Vacuum à partir du référentiel GitHub AWS Labs pour mettre à jour vos tables.

Mettre à jour la conception de table

La conception de table est régie par les clés de tri, le style de distribution et la clé de distribution désignées. La clé de distribution et le style de distribution déterminent la façon dont les données sont distribuées entre les nœuds.

Une clé de distribution ou un style de distribution inapproprié peut induire une asymétrie de distribution entre les nœuds. Pour réduire l'asymétrie de distribution des données, choisissez un style de distributionet uneclé de tri en fonction des modèles de requête et des prédicats. La clé de distribution doit prendre en charge les conditions de jointure de vos requêtes et colonnes ayant une cardinalité élevée. Une clé de distribution bien choisie peut aider les requêtes à effectuer des jointures de fusion au lieu de jointures de hachage ou de jointures de boucles imbriquées, affectant ainsi la durée d'exécution des requêtes.

Pour identifier les tables ayant une distribution asymétrique, utilisez le script table_inspector.sql. Ensuite, utilisez le playbook de conception de table Amazon Redshift pour choisir les clés de tri, les clés de distribution et les styles de distribution les plus appropriés pour votre table.

Vérifier les mises à jour de maintenance

Amazon Redshift met en cache le code compilé, permettant ainsi aux requêtes de réutiliser le code pour les segments précédemment exécutés. Le cache est ensuite effacé lors des mises à jour de maintenance. Par conséquent, les requêtes qui sont exécutées pour la première fois après une mise à jour du correctif passent un certain temps à la compilation. Cette surcharge de compilation peut augmenter l'utilisation de l'UC d'un cluster Redshift.

Utilisez la requête SQL suivante pour vérifier le nombre de segments compilés chaque heure :

select "hour", count(query) total_queries, count(case when is_compiled='Y' then 1 else null end ) as queries_compiled_count, sum(segements_count) total_segments_count, sum(segments_compiled_count) total_segments_compiled_count from
(
  select q.query, date_trunc('h',q.starttime) as "hour", q.starttime, q.endtime, q.aborted, (case when compiled_queries.segments_compiled_count = 0 then 'N' ELSE 'Y' end) is_compiled, compiled_queries.segements_count, compiled_queries.segments_compiled_count
  from stl_query q
  left join (select query, count(segment) segements_count, sum(compile) segments_compiled_count from svl_compile group by query) compiled_queries on q.query = compiled_queries.query
  where q.userid > 1
  and q.starttime > trunc(sysdate) -7
)
group by "hour"
order by "hour";

Vérifier les pics d'utilisation de l'UC de nœud principal

Les tâches de nœud principal, telles que l'analyse et l'optimisation des requêtes, la génération de code compilé et l'agrégation des résultats à partir de nœuds de calcul, consomment des ressources d'UC. Cette consommation entraîne une augmentation de l'utilisation de l'UC de nœud principal. L'utilisation de l'UC de nœud principal peut également augmenter si les requêtes référencent massivement les tables du catalogue système ou exécutent des fonctions de nœud principal uniquement.

Si le pic d'utilisation de l'UC est causé par un nœud principal, cochez la case Events (Événements) dans la console Amazon Redshift. Vérifiez si une maintenance a eu lieu sur votre cluster Redshift. Utilisez la requête SQL fournie dans Check for maintenance updates pour vérifier si plus de segments sont compilés que d'habitude.

Utiliser CloudWatch pour surveiller les pics d'utilisation de l'UC

Utilisez les métriques CloudWatch pour comparer les pics entre l'utilisation de l'UC et les connexions à la base de données. Analysez les performances de la charge de travail en consultant le graphique Workload Execution Breakdown. Le graphique Workload Execution Breakdown indique les étapes sur lesquelles les requêtes passent le plus de temps.

Pour identifier les 100 requêtes qui sont à l’origine de l’utilisation la plus élevée de l'UC pendant une période spécifiée, utilisez la requête suivante :

select qms.*, substring(q.querytxt,1,100) qtxt
from svl_query_metrics_summary qms
join stl_query q on q.query=qms.query
where q.starttime > sysdate - 1 
and q.userid>1
order by qms.query_cpu_time desc nulls last limit 100;

Pour récupérer une liste des requêtes qui consomment le plus de ressources lorsque l'UC atteint 100 %, utilisez la requête suivante :

select a.userid, service_class, a.query, b.pid, b.xid, a.starttime, slices, max_rows, max_blocks_read, max_blocks_to_disk, max_query_scan_size, segment, round(max_cpu_time/(max_run_time*1.0)*100,2) as max_cpu_pcnt, round(cpu_time/(run_time*1.0)*100,2) as cpu_pcnt, max_cpu_time, max_run_time, case when segment > -1 then 'segment' else 'query' end as metric_lvl, text from pg_catalog.stv_query_metrics a left join stv_inflight b using (query) where step_type=-1 order by query, segment;

Pour vérifier la quantité de données traitées par chaque nœud, exécutez la requête suivante :

select iq.day_d, sl.node, sum(iq.elapsed_ms) as elapsed, sum(iq.bytes) as bytes from (select start_time::date as day_d, slice,query,segment,datediff('ms',min(start_time),max(end_time)) as elapsed_ms, sum(bytes) as bytes from svl_query_report where end_time > start_time group by 1,2,3,4) iq join stv_slices as sl on (sl.slice = iq.slice) group by 1,2 order by 1 desc, 3 desc;

Vous pouvez utiliser les règles de surveillance de requête (QMR) pour identifier et journaliser toutes les requêtes mal conçues. Par exemple, des règles QMR peuvent être définies pour journaliser les requêtes qui sont à l'origine d'une consommation élevée de l'UC ou dont le temps d'exécution est long.

---