Requêtes de diagnostics pour l’ajustement des requêtes

Utilisez les requêtes suivantes pour identifier les problèmes rencontrés par les requêtes ou les tables sous-jacentes susceptibles d’affecter les performances des requêtes. Nous recommandons d’utiliser ces requêtes conjointement aux processus d’ajustement des requêtes expliqués dans la section Analyse et amélioration des requêtes.

Identification des requêtes particulièrement indiquées pour un ajustement

La requête suivante identifie les 50 instructions les plus chronophages exécutées au cours des 7 derniers jours. Vous pouvez utiliser les résultats pour identifier les requêtes inhabituellement longues. Vous pouvez également identifier les requêtes exécutées fréquemment (celles qui apparaissent plusieurs fois dans l’ensemble de résultats). Ces requêtes nécessitent souvent un ajustement en vue d’améliorer les performances du système.

Cette requête fournit également un nombre d’événements d’alertes associés à chaque requête identifiée. Ces alertes fournissent des détails utiles pour améliorer les performances de la requête. Pour de plus amples informations, veuillez consulter Révision des alertes de requêtes.

select trim(database) as db, count(query) as n_qry, 
max(substring (qrytext,1,80)) as qrytext, 
min(run_minutes) as "min" , 
max(run_minutes) as "max", 
avg(run_minutes) as "avg", sum(run_minutes) as total,  
max(query) as max_query_id, 
max(starttime)::date as last_run, 
sum(alerts) as alerts, aborted
from (select userid, label, stl_query.query, 
trim(database) as database, 
trim(querytxt) as qrytext, 
md5(trim(querytxt)) as qry_md5, 
starttime, endtime, 
(datediff(seconds, starttime,endtime)::numeric(12,2))/60 as run_minutes,     
alrt.num_events as alerts, aborted 
from stl_query 
left outer join 
(select query, 1 as num_events from stl_alert_event_log group by query ) as alrt 
on alrt.query = stl_query.query
where userid <> 1 and starttime >=  dateadd(day, -7, current_date)) 
group by database, label, qry_md5, aborted
order by total desc limit 50;

Identification des tables comportant une asymétrie des données ou des lignes non triées

La requête suivante identifie les tables comportant une distribution irrégulière de données (asymétrie des données) ou un pourcentage élevé de lignes non triées.

Un valeur de skew faible indique que les données de la table sont correctement distribuées. Si une table comporte une valeur de skew de 4,00 ou plus, envisagez de modifier le style de distribution de données. Pour de plus amples informations, veuillez consulter Distribution des données sous-optimales.

Si une table comporte une valeur de pct_unsorted supérieure à 20 pour cent, envisagez d’exécuter la commande VACUUM. Pour de plus amples informations, veuillez consulter Lignes non triées ou mal triées.

Vous devez également examiner les valeurs de mbytes et de pct_of_total pour chaque table. Ces colonnes identifient la taille de la table et le pourcentage d’espace sur le disque brut que la table consomme. L’espace disque brut comprend l’espace qui est réservé par Amazon Redshift pour un usage interne, il est donc plus grand que la capacité nominale du disque, qui est la quantité d’espace disque disponible pour l’utilisateur. Ces informations vous permettront de vérifier que l’espace disque est égal à au moins 2,5 fois la taille de votre table la plus volumineuse. Cet espace disponible permet au système d’écrire des résultats intermédiaires sur le disque lors du traitement de requêtes complexes.

select trim(pgn.nspname) as schema, 
trim(a.name) as table, id as tableid, 
decode(pgc.reldiststyle,0, 'even',1,det.distkey ,8,'all') as distkey, dist_ratio.ratio::decimal(10,4) as skew, 
det.head_sort as "sortkey", 
det.n_sortkeys as "#sks", b.mbytes,  
decode(b.mbytes,0,0,((b.mbytes/part.total::decimal)*100)::decimal(5,2)) as pct_of_total, 
decode(det.max_enc,0,'n','y') as enc, a.rows, 
decode( det.n_sortkeys, 0, null, a.unsorted_rows ) as unsorted_rows , 
decode( det.n_sortkeys, 0, null, decode( a.rows,0,0, (a.unsorted_rows::decimal(32)/a.rows)*100) )::decimal(5,2) as pct_unsorted 
from (select db_id, id, name, sum(rows) as rows, 
sum(rows)-sum(sorted_rows) as unsorted_rows 
from stv_tbl_perm a 
group by db_id, id, name) as a 
join pg_class as pgc on pgc.oid = a.id
join pg_namespace as pgn on pgn.oid = pgc.relnamespace
left outer join (select tbl, count(*) as mbytes 
from stv_blocklist group by tbl) b on a.id=b.tbl
inner join (select attrelid, 
min(case attisdistkey when 't' then attname else null end) as "distkey",
min(case attsortkeyord when 1 then attname  else null end ) as head_sort , 
max(attsortkeyord) as n_sortkeys, 
max(attencodingtype) as max_enc 
from pg_attribute group by 1) as det 
on det.attrelid = a.id
inner join ( select tbl, max(mbytes)::decimal(32)/min(mbytes) as ratio 
from (select tbl, trim(name) as name, slice, count(*) as mbytes
from svv_diskusage group by tbl, name, slice ) 
group by tbl, name ) as dist_ratio on a.id = dist_ratio.tbl
join ( select sum(capacity) as  total
from stv_partitions where part_begin=0 ) as part on 1=1
where mbytes is not null 
order by  mbytes desc;

Identification des requêtes avec des boucles imbriquées

La requête suivante identifie les requêtes ayant des événements d’alertes consignés pour des boucles imbriquées. Pour plus d’informations sur la façon de corriger la condition de boucle imbriquée, consultez Boucle imbriquée.

select query, trim(querytxt) as SQL, starttime 
from stl_query 
where query in (
select distinct query 
from stl_alert_event_log 
where event like 'Nested Loop Join in the query plan%') 
order by starttime desc;

Vérification des temps d’attente des requêtes dans les files d’attente

La requête suivante affiche combien de temps les requêtes récentes ont attendu avant qu’un emplacement s’ouvre dans une file d’attente avant leur exécution. Si vous voyez une tendance de temps d’attente élevés, vous pouvez modifier la configuration de votre file d’attente de requête pour obtenir un débit plus élevé. Pour de plus amples informations, veuillez consulter Implémentation de la gestion manuelle de la charge de travail.

select trim(database) as DB , w.query, 
substring(q.querytxt, 1, 100) as querytxt,  w.queue_start_time, 
w.service_class as class, w.slot_count as slots, 
w.total_queue_time/1000000 as queue_seconds, 
w.total_exec_time/1000000 exec_seconds, (w.total_queue_time+w.total_Exec_time)/1000000 as total_seconds 
from stl_wlm_query w 
left join stl_query q on q.query = w.query and q.userid = w.userid 
where w.queue_start_Time >= dateadd(day, -7, current_Date) 
and w.total_queue_Time > 0  and w.userid >1   
and q.starttime >= dateadd(day, -7, current_Date) 
order by w.total_queue_time desc, w.queue_start_time desc limit 35;

Vérification des alertes de requêtes par table

La requête suivante identifie les tables pour lesquelles des événements d’alertes de requêtes ont été consignés, ainsi que les types d’alertes rencontrés le plus souvent.

Si la valeur de minutes pour une ligne avec une table identifiée est élevée, vérifiez cette table pour savoir si elle nécessite des opérations de maintenance habituelles, comme l’exécution de ANALYSE ou de VACUUM.

Si la valeur de count est élevée pour une ligne, mais que la valeur de table est null, exécutez une requête sur STL_ALERT_EVENT_LOG pour la valeur de event associée afin d’étudier pourquoi cette alerte est déclenchée si souvent.

select trim(s.perm_table_name) as table, 
(sum(abs(datediff(seconds, s.starttime, s.endtime)))/60)::numeric(24,0) as minutes, trim(split_part(l.event,':',1)) as event,  trim(l.solution) as solution, 
max(l.query) as sample_query, count(*) 
from stl_alert_event_log as l 
left join stl_scan as s on s.query = l.query and s.slice = l.slice 
and s.segment = l.segment and s.step = l.step
where l.event_time >=  dateadd(day, -7, current_Date) 
group by 1,3,4 
order by 2 desc,6 desc;

Identification des tables avec des statistiques manquantes

La requête suivante fournit un nombre de requêtes que vous exécutez sur les tables pour lesquelles des statistiques sont manquantes. Si cette requête renvoie toutes les lignes, examinez la valeur de plannode afin de déterminer la table concernée et exécutez ANALYSE sur celle-ci.

select substring(trim(plannode),1,100) as plannode, count(*) 
from stl_explain 
where plannode like '%missing statistics%' 
group by plannode 
order by 2 desc;