Come posso risolvere i problemi relativi all'utilizzo elevato della CPU in Amazon Redshift?

Breve descrizione

Amazon Redshift è progettato per utilizzare tutte le risorse disponibili durante l'esecuzione delle query. Ciò significa che puoi aspettarti di vedere picchi nell'utilizzo della CPU nel tuo cluster Redshift. Un aumento dell'utilizzo della CPU può dipendere da fattori quali il carico di lavoro del cluster, i dati distorti e non ordinati o le attività del nodo leader.

Tuttavia, se l'utilizzo della CPU influisce sul tempo della query, prendi in considerazione i seguenti approcci:

• Controlla il carico di lavoro del tuo cluster Redshift.
• Mantieni l'igiene dei tuoi dati.
• Aggiorna il design delle tabelle.
• Verifica la presenza di aggiornamenti di manutenzione.
• Verifica la presenza di picchi nell'utilizzo della CPU del tuo nodo leader.
• Usa Amazon CloudWatch per monitorare i picchi nell'utilizzo della CPU.

Soluzione

Verifica il carico di lavoro del cluster Redshift

I seguenti fattori possono influire sull'utilizzo della CPU sul cluster Redshift:

• Un aumento del carico di lavoro (a causa di un maggior numero di query in esecuzione). L'aumento del carico di lavoro fa aumentare il numero di connessioni al database, con conseguente maggiore concorrenza delle query.
• Il maggior numero di query simultanee influisce sulla contesa delle risorse, sul tempo di attesa del blocco e sul tempo di attesa delle code di gestione del carico di lavoro (WLM).
• Altre connessioni al database. Questo può essere il risultato di sessioni inattive presenti nel cluster. Le sessioni inattive possono causare ulteriori problemi di contesa tra blocchi.

Mentre le query sono in esecuzione, recupera le informazioni di blocco. Per identificare le sessioni di lunga esecuzione, utilizza la seguente query SQL:

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>

Quindi, esegui PG\ _TERMINATE\ _BACKEND per interrompere qualsiasi transazione di lunga esecuzione. Per evitare che queste sessioni rimangano aperte, assicurati che tutte le transazioni siano chiuse. Ad esempio, assicurati che tutte le transazioni che iniziano con un’istruzione BEGIN siano accompagnate da un'istruzione END o COMMIT.

Quindi, esegui la seguente query SQL per identificare le query che consumano una CPU elevata:

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;

Per analizzare i passaggi di esecuzione a livello di segmento e sezione per ogni query, esegui la seguente query:

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;

Per ulteriori informazioni sull'ottimizzazione di queste query, consulta Le 10 migliori tecniche di ottimizzazione delle prestazioni per Amazon Redshift.

Puoi anche utilizzare la visualizzazione wlm_query_trend_hourly per analizzare lo schema del carico di lavoro del cluster Redshift. Quindi, stabilisci quale dei seguenti approcci può aiutarti a ridurre i tempi di attesa in coda:

• Riduci la concorrenza delle query per coda per fornire più memoria a ogni slot di query. Questa riduzione consente di eseguire in modo più efficiente le query che richiedono più memoria.
• Abilita l'accelerazione delle query brevi (SQA) per dare priorità alle query di breve esecuzione rispetto a quelle di lunga esecuzione.
• Dimensiona il cluster Redshift per adattarlo all'aumento del carico di lavoro. La scalabilità di un cluster fornisce più memoria e potenza di calcolo, il che può aiutare le query a essere eseguite più rapidamente. Per ulteriori informazioni, consulta Come faccio a dimensionare un cluster Redshift?

Mantieni l'igiene dei tuoi dati

L'igiene dei dati si misura in base alla percentuale di statistiche obsolete e di righe non ordinate presenti in una tabella. Un'alta percentuale di entrambi può far sì che l'ottimizzatore delle query generi un piano di esecuzione in cui le query vengono eseguite in modo inefficiente quando si fa riferimento alle tabelle. I dati non ordinati possono far sì che le query eseguano la scansione di blocchi di dati non necessari, che richiedono operazioni di I/O aggiuntive. Una query con prestazioni scadenti influisce negativamente sull'utilizzo della CPU del cluster Redshift.

Utilizza la visualizzazione del sistema SVV_TABLE_INFO per recuperare i dati percentuali stats_off e unsorted per una tabella. Queste percentuali dovrebbero rimanere vicine allo 0. Se le percentuali sono elevate, esegui l'utilità dello schema Analyze & Vacuum dall’archivio GitHub di AWS Labs per aggiornare le tabelle.

Aggiorna il design delle tabelle

Il design delle tabelle è regolato dalle chiavi di ordinamento, dallo stile di distribuzione e dalla chiave di distribuzione designati. La chiave e lo stile di distribuzione determinano il modo in cui i dati vengono distribuiti tra i nodi.

Una chiave o uno stile di distribuzione non appropriati possono causare un'inclinazione della distribuzione tra i nodi. Per ridurre l'inclinazione nella distribuzione dei dati, scegli lo stile di distribuzione e la chiave di ordinamento in base ai modelli di query e ai predicati. La chiave di distribuzione deve supportare le condizioni di join nelle query e nelle colonne con elevata cardinalità. Una corretta selezione della chiave di distribuzione può aiutare le query a eseguire unioni di join anziché loop join hash o annidati, il che influisce in ultima analisi sulla quantità di tempo di esecuzione delle query.

Per identificare le tabelle con distribuzione distorta, utilizza lo script table\ _inspector.sql. Quindi, utilizza il manuale di progettazione delle tabelle di Amazon Redshift per scegliere le chiavi di ordinamento, le chiavi di distribuzione e gli stili di distribuzione più appropriati per la tua tabella.

Verifica la presenza di aggiornamenti di manutenzione

Amazon Redshift memorizza nella cache il codice compilato, consentendo alle query di riutilizzare il codice per segmenti eseguiti in precedenza. La cache viene quindi cancellata durante eventuali aggiornamenti di manutenzione. Di conseguenza, le query eseguite per la prima volta dopo l'aggiornamento di una patch impiegheranno del tempo nella compilazione. Questo sovraccarico di compilazione può aumentare l'utilizzo della CPU di un cluster Redshift.

Utilizza la seguente query SQL per verificare quanti segmenti vengono compilati ogni ora:

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";

Verifica eventuali picchi nell'utilizzo della CPU del tuo nodo leader

Le attività dei nodi leader come l'analisi e l'ottimizzazione delle query, la generazione di codice compilato e l'aggregazione dei risultati dai nodi di elaborazione consumano risorse della CPU. Questo consumo comporta un aumento dell'utilizzo della CPU dei nodi leader. L'utilizzo della CPU del nodo leader può aumentare anche se le query fanno molto riferimento alle tabelle del catalogo di sistema o eseguono funzioni che riguardano solo i nodi leader.

Se il picco nell'utilizzo della CPU è causato da un nodo leader, controlla la sezione Eventi nella console Amazon Redshift. Verifica se è stata effettuata alcuna manutenzione sul tuo cluster Redshift. Utilizza la query SQL fornita in Verifica aggiornamenti di manutenzione per verificare se vengono compilati più segmenti del solito.

Usa CloudWatch per monitorare i picchi nell'utilizzo della CPU

Usa le metriche di CloudWatch per confrontare i picchi tra CPUutilization e Database Connections. Analizza le prestazioni del carico di lavoro controllando il grafico Ripartizione dell'esecuzione del carico di lavoro. Il grafico di Ripartizione dell'esecuzione del carico di lavoro mostra in quali fasi le query impiegano più tempo.

Per identificare le prime 100 query che consumano più CPU in un periodo di tempo specificato, utilizza la seguente query:

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;

Per recuperare un elenco di query che consumano più risorse quando la CPU raggiunge il 100%, utilizza la seguente query:

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;

Per verificare la quantità di dati elaborati da ciascun nodo, esegui la seguente query:

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;

È possibile utilizzare le regole di monitoraggio delle query (QMR) per identificare e registrare eventuali query mal progettate. Ad esempio, è possibile definire delle regole QMR per registrare le query che richiedono un elevato utilizzo della CPU o un runtime prolungato.

---