如何對 Amazon Redshift 中的高 CPU 用量進行疑難排解？

簡短說明

Amazon Redshift 旨在在執行查詢時利用所有可用資源。這意味著您可以預期在 Redshift 叢集中查看 CPU 用量峰值。CPU 使用率的增加取決於叢集工作負載、偏態和未排序的資料或領導節點任務等因素。

但是，如果您的 CPU 用量影響查詢時間，請考慮下列方法：

• 檢閱您的 Redshift 叢集工作負載。
• 保持資料衛生。
• 更新資料表設計。
• 檢查維護更新。
• 檢查領導節點 CPU 用量中的峰值。
• 使用 Amazon CloudWatch 監控 CPU 使用率中的峰值。

解決方法

檢閱 Redshift 叢集工作負載

下列因素可能會影響 Redshift 叢集上的 CPU 使用率：

• 工作負載增加 (由於執行更多查詢)。工作負載增加會增加資料庫連線的數量，從而導致查詢並行性較高。
• 並行查詢數量較多會影響資源爭用、鎖定等待時間和工作負載管理 (WLM) 佇列等待時間。
• 更多資料庫連線。這可能是叢集中存在閒置工作階段的結果。閒置工作階段可能會造成額外的鎖定爭用問題。

查詢正在執行時，擷取鎖定資訊。若要識別長時間執行的工作階段，請使用下列 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>

然後，執行 PG_TERMINATE_BACKEND 以停止任何長時間執行的交易。若要防止這些工作階段保持開啟狀態，請確保已關閉所有交易。例如，請確保以 BEGIN 陳述式開頭的所有交易都附有 END 或 COMMIT 陳述式。

然後，執行下列 SQL 查詢以識別耗用高 CPU 的查詢：

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;

若要分析每個查詢的區段和切片層級執行步驟，請執行下列查詢：

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;

如需有關調校這些查詢的詳細資訊，請參閱 Amazon Redshift 的 10 大效能調校技術。

您也可以使用 wlm_query_trend_hourly 視圖來檢閱 Redshift 叢集工作負載模式。然後，判斷下列哪一種方法可協助您縮短佇列等待時間：

• 減少每個佇列的查詢並行，以便為每個查詢插槽提供更多記憶體。這種減少有助於需要更多記憶體的查詢更有效地執行。
• 啟用短時間查詢加速 (SQA)，以將短時間執行的查詢的優先順序排定為優先於長時間執行的查詢。
• 擴展 Redshift 叢集以適應增加的工作負載。擴展叢集可提供更多記憶體和運算能力，這有助於更快地執行查詢。如需詳細資訊，請參閱如何調整 Redshift 叢集的大小？

保持資料衛生

資料衛生狀況根據資料表中過時統計資料和未排序資料列的百分比來衡量。兩者的百分比較高可能會導致查詢優化工具產生執行計畫，其中查詢在參考資料表時執行效率不佳。未排序的資料可能會導致查詢掃描不必要的資料區塊，這需要額外的 I/O 操作。效能不佳的查詢會對 Redshift 叢集的 CPU 用量產生負面影響。

使用 SVV_TABLE_INFO 系統視圖來擷取資料表的 stats_off 和 unsorted 百分比資料。這些百分比應保持接近 0。如果百分比很高，請從 AWS 實驗室 GitHub 儲存庫執行 Analyze & Vacuum 結構描述公用程式，以更新您的資料表。

更新資料表設計

資料表設計由指定的排序索引鍵、分佈樣式和分佈索引鍵管控。分佈索引鍵和分佈樣式決定了資料在節點之間的分佈方式。

不適當的分佈索引鍵或分佈樣式可能會導致節點之間的分佈偏態。若要減少資料分佈偏態，請根據查詢模式和述詞選擇分佈樣式和排序索引鍵。分佈索引鍵應支援查詢中的聯結條件以及具有高基數的資料欄。適當的分佈索引鍵選擇可以協助查詢執行合併聯結而不是雜湊或巢狀迴圈聯結，這最終會影響查詢執行的時長。

若要識別具有偏態分佈的資料表，請使用 table_inspector.sql 指令碼。然後，使用 Amazon Redshift 資料表設計手冊，為您的資料表選擇最合適的排序索引鍵、分佈索引鍵和分佈樣式。

檢查維護更新

Amazon Redshift 會快取編譯的程式碼，允許查詢重複使用之前執行的區段的程式碼。然後，在任何維護更新期間，快取都會清除。因此，修補程式更新之後首次執行的查詢將花費一些時間進行編譯。此編譯額外負荷可能會增加 Redshift 叢集的 CPU 用量。

使用下列 SQL 查詢來檢查每小時編譯的區段數量：

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

檢查領導節點 CPU 用量中的峰值

諸如剖析和優化查詢、產生編譯的程式碼，以及從運算節點彙總結果等領導節點任務會耗用 CPU 資源。此耗用會導致領導節點 CPU 用量增加。如果查詢大量參考系統型錄資料表或執行領導節點專用函數，則領導節點 CPU 用量也會增加。

如果 CPU 用量峰值是因領導節點造成的，請在 Amazon Redshift 主控台中的事件下進行檢查。請確認您的 Redshift 叢集是否已進行任何維護。使用檢查維護更新中提供的 SQL 查詢，確認是否正在編譯比平常更多的區段。

使用 CloudWatch 監控 CPU 使用率中的峰值

使用 CloudWatch 指標來比較 CPU 使用率和資料庫連線之間的峰值。透過檢查「工作負載執行明細」圖表，來分析工作負載效能。「工作負載執行明細」圖表會顯示查詢花費最多時間的階段。

若要識別在指定時間內耗用最多 CPU 的前 100 個查詢，請使用下列查詢：

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;

若要擷取 CPU 達到 100% 時耗用最多資源的查詢清單，請使用下列查詢：

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;

若要檢查每個節點處理的資料量，請執行下列查詢：

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;

您可以使用查詢監控規則 (QMR) 來識別和記錄任何設計不佳的查詢。例如，QMR 規則可以定義為記錄耗用高 CPU 用量或延長執行階段的查詢。

---