如何解决 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 集群的大小？

保持数据健康

数据的健康情况是根据表中的陈旧统计信息和未排序行的百分比来衡量的。如果这两者的百分比较高，就可能会导致查询优化程序生成一个执行计划；在这个执行计划中，查询在引用表时运行效率低下。未排序的数据可能导致查询扫描不必要的数据块，而这需要额外的输入/输出操作。效果不佳的查询会对 Redshift 集群的 CPU 利用率产生负面影响。

使用 SVV_TABLE_INFO 系统视图检索表中 stats_off 和 unsorted 数据的百分比。二者的百分比应保持接近 0。如果百分比较高，请从 AWS Labs GitHub 存储库运行分析和清理架构实用程序，来更新表。

更新表设计

表设计由指定的排序键、分配方式和分配键控制。分配键和分配方式决定了如何在节点之间分配数据。

不适当的分配键或分配方式会导致节点之间分配偏斜。要减少数据分配偏斜，请根据查询模式和谓词选择分配方式和排序键。分配键应支持基数高的查询和列中的连接条件。选择合适的分配键可以帮助查询执行合并连接（而非哈希或嵌套循环连接），这最终会影响查询运行的时间。

要识别采用偏斜分配的表，请使用 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 控制台中的 Events（事件）下查看。验证是否在 Redshift 集群上进行了任何维护。使用检查维护更新中提供的 SQL 查询来验证正在编译的数据段是否比平时多。

使用 CloudWatch 监控 CPU 利用率的峰值

使用 CloudWatch 指标比较 CPUutilization (CPU 利用率) 和 Database Connections (数据库连接) 之间的峰值。请查看 Workload Execution Breakdown 图表来分析工作负载性能。Workload Execution Breakdown 图表显示查询花费时间最多的段。

要确定在指定时间内消耗 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 较高或延长运行时间的查询。

---