Amazon Redshift
数据库开发人员指南 (API 版本 2012-12-01)
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 Amazon AWS 入门

Amazon Redshift Advisor 建议

Amazon Redshift Advisor 提供了有关如何优化 Amazon Redshift 集群以提高性能和节省运营成本的建议。您可以在控制台中找到每条建议的说明,如前所示。您可以在以下章节中找到有关这些建议的更多详细信息。

压缩表数据

Amazon Redshift 经过优化,通过使用压缩编码来减少存储占用空间和提高查询性能。当您不使用压缩时,数据将消耗额外的空间并需要额外的磁盘 I/O。将压缩应用于大型未压缩列可能对集群产生很大的影响。

分析

Advisor 中的压缩分析将跟踪分配给永久用户表的未压缩存储。它将审查与不是排序键列的大型未压缩列关联的存储元数据。当未压缩存储的总量超过总存储空间的 15% 或者达到以下特定于节点的阈值时,Advisor 将提供利用未压缩列重建表的建议。

集群大小 阈值
DC2.LARGE 480 GB
DC2.8XLARGE 2.56 TB
DS2.XLARGE 4 TB
DS2.8XLAGE 16 TB

建议

解决单个表的未压缩存储是一次性优化,这需要重建该表。我们建议重建包含大型和经常访问的未压缩列的任何表。要确定哪些表包含最多的未压缩存储,请以超级用户身份运行以下 SQL 命令。

SELECT ti.schema||'.'||ti."table" tablename, raw_size.size uncompressed_mb, ti.size total_mb FROM svv_table_info ti LEFT JOIN ( SELECT tbl table_id, COUNT(*) size FROM stv_blocklist WHERE (tbl,col) IN ( SELECT attrelid, attnum-1 FROM pg_attribute WHERE attencodingtype IN (0,128) AND attnum>0 AND attsortkeyord != 1) GROUP BY tbl) raw_size USING (table_id) WHERE raw_size.size IS NOT NULL ORDER BY raw_size.size DESC;

uncompressed_mb 列中返回的数据表示表中所有列的未压缩 1-MB 数据块的总数。

在重建表时,请使用 ENCODE 参数来显式设置列压缩。

实施提示

  • 保留作为未压缩复合排序键中的第一列的任何列。Advisor 分析未计入这些列占用的存储。

  • 压缩大型列对性能和存储的影响高于压缩小型列。

  • 如果您不确定哪个压缩是最佳的,请使用 ANALYZE COMPRESSION 命令建议一个压缩。

  • 要为现有表生成数据定义语言 (DDL) 语句,您可以使用在 GitHub 上找到的 AWS 生成表 DDL 实用工具。

  • 要简化压缩建议和重建表的过程,您在可以使用在 GitHub 上找到的 Amazon Redshift 列编码实用工具

压缩由 COPY 加载的 Amazon S3 文件对象

COPY 命令利用 Amazon Redshift 中的大规模并行处理 (MPP) 架构并行读取和加载数据。它可以从 Amazon S3、DynamoDB 表以及来自一个或多个远程主机的文本输出读取文件。

在加载大量数据时,强烈建议使用 COPY 命令从 S3 加载压缩数据文件。压缩大型数据集可节省将文件上传到 S3 的时间。COPY 还将在读取文件时解压缩文件,以加快加载过程的速度。

分析

加载大型未压缩数据集的耗时的 COPY 命令通常有机会获得相当大的性能改进。Advisor 分析将识别用于加载大型未压缩数据集的 COPY 命令。在这种情况下,Advisor 将生成对 S3 中的源文件实施压缩的建议。

建议

确保所有加载大量数据或运行很长时间的 COPY 都从 S3 中提取未压缩的数据对象。您可以通过以超级用户身份运行以下 SQL 命令来识别从 S3 加载大量未压缩数据集的 COPY 命令。

SELECT wq.userid, query, exec_start_time AS starttime, COUNT(*) num_files, ROUND(MAX(wq.total_exec_time/1000000.0),2) execution_secs, ROUND(SUM(transfer_size)/(1024.0*1024.0),2) total_mb, SUBSTRING(querytxt,1,60) copy_sql FROM stl_s3client s JOIN stl_query q USING (query) JOIN stl_wlm_query wq USING (query) WHERE s.userid>1 AND http_method = 'GET' AND POSITION('COPY ANALYZE' IN querytxt) = 0 AND aborted = 0 AND final_state='Completed' GROUP BY 1, 2, 3, 7 HAVING SUM(transfer_size) = SUM(data_size) AND SUM(transfer_size)/(1024*1024) >= 5 ORDER BY 6 DESC, 5 DESC;

如果暂存的数据在您加载后保留在 S3 中(通常位于数据湖架构中),以压缩形式存储此数据可以降低存储成本。

实施提示

  • 在压缩后,理想的对象大小在 1 MB – 128 MB 之间。

  • 您可以使用 gzip、lzop 或 bzip2 格式压缩文件。

隔离多个活动数据库

作为最佳实践,我们建议将 Amazon Redshift 中的数据库与其他数据库隔离。查询在特定数据库中运行且无法访问集群上的任何其他数据库中的数据。但是,您在集群的所有数据库中运行的查询共用同一基础集群存储空间和计算资源。当单个集群包含多个活动数据库时,这些数据库的工作负载通常不相关。

分析

Advisor 分析将审查集群上的所有数据库中是否有在同一时间运行的活动工作负载。如果存在在同一时间运行的活动工作负载,Advisor 将生成考虑将数据库迁移到独立的 Amazon Redshift 集群的建议。

建议

考虑将每个主动查询的数据库移动到独立的专用集群。使用独立的集群可减少资源争用和提高查询性能。之所以如此,是因为它使您能够为每个集群设置大小以满足每个工作负载的存储、成本和性能需求。此外,不相关的工作负载经常受益于不同的工作负载管理配置。

要识别哪些数据库是主动使用的,您可以作为超级用户运行此 SQL 命令。

SELECT database, COUNT(*) as num_queries, AVG(DATEDIFF(sec,starttime,endtime)) avg_duration, MIN(starttime) as oldest_ts, MAX(endtime) as latest_ts FROM stl_query WHERE userid > 1 GROUP BY database;

实施提示

  • 由于用户必须专门连接到各个数据库,并且查询只能访问单个数据库,因此将数据库移动到独立集群对用户的影响最小。

  • 移动数据库一个方式是执行以下步骤:

    1. 将当前集群的快照暂时还原到相同大小的集群。

    2. 从新集群中删除除目标数据库之外的所有数据库。

    3. 将集群的大小调整为合适的节点类型并针对数据库的工作负载进行计数。

重新分配工作负载管理 (WLM) 内存

Amazon Redshift 会将用户查询路由到定义查询队列以进行处理。工作负载管理 (WLM) 定义这些查询路由至队列的方式。Amazon Redshift 会为每个队列分配一部分集群可用内存。队列的内存在队列的查询槽间分配。

当某个队列所配置的插槽数多于工作负载需要的插槽数时,分配给这些未使用的插槽的内存将得不到充分利用。通过将配置的插槽数减少得与峰值工作负载需求匹配,您可以将未充分利用的内存重新分配到活动插槽,并可以提高查询性能。

分析

Advisor 分析将审查工作负载并发需求以识别具有未使用的插槽的查询队列。当发现以下内容时,Advisor 将生成一个减少队列中的插槽数的建议:

  • 在整个分析过程中具有并非完全不活动的插槽的队列

  • 在整个分析过程中具有超过四个插槽(其中至少有两个为非活动插槽)的队列

建议

通过将配置的插槽数减少得与峰值工作负载需求匹配,您可以将未充分利用的内存重新分配到活动插槽。请考虑为插槽从未得到充分利用的队列减少配置的插槽数。要识别这些队列,您可以通过作为超级用户运行以下 SQL 命令来比较每个队列的峰值每小时插槽需求。

WITH generate_dt_series AS (select sysdate - (n * interval '5 second') as dt from (select row_number() over () as n from stl_scan limit 17280)), apex AS ( SELECT iq.dt, iq.service_class, iq.num_query_tasks, count(iq.slot_count) as service_class_queries, sum(iq.slot_count) as service_class_slots FROM (select gds.dt, wq.service_class, wscc.num_query_tasks, wq.slot_count FROM stl_wlm_query wq JOIN stv_wlm_service_class_config wscc ON (wscc.service_class = wq.service_class AND wscc.service_class > 5) JOIN generate_dt_series gds ON (wq.service_class_start_time <= gds.dt AND wq.service_class_end_time > gds.dt) WHERE wq.userid > 1 AND wq.service_class > 5) iq GROUP BY iq.dt, iq.service_class, iq.num_query_tasks), maxes as (SELECT apex.service_class, trunc(apex.dt) as d, date_part(h,apex.dt) as dt_h, max(service_class_slots) max_service_class_slots from apex group by apex.service_class, apex.dt, date_part(h,apex.dt)) SELECT apex.service_class - 5 AS queue, apex.service_class, apex.num_query_tasks AS max_wlm_concurrency, maxes.d AS day, maxes.dt_h || ':00 - ' || maxes.dt_h || ':59' as hour, MAX(apex.service_class_slots) as max_service_class_slots FROM apex JOIN maxes ON (apex.service_class = maxes.service_class AND apex.service_class_slots = maxes.max_service_class_slots) GROUP BY apex.service_class, apex.num_query_tasks, maxes.d, maxes.dt_h ORDER BY apex.service_class, maxes.d, maxes.dt_h;

max_service_class_slots 列表示该小时内查询队列中的 WLM 查询插槽的最大数量。如果存在未充分利用的队列,请通过修改参数组来实施插槽减少优化,如 Amazon Redshift Cluster Management Guide 中所述。

实施提示

  • 如果工作负载在卷中高度可变,请确保分析捕获了峰值利用率期间。如果没有,请重复运行上述 SQL 以监控峰值并发需求。

  • 有关解释来自上述 SQL 代码的查询结果的更多详细信息,请参阅 GitHub 上的 wlm_apex_hourly.sql 脚本

在 COPY 期间跳过压缩分析

当您将数据加载到具有使用 COPY 命令声明的压缩编码的空表中时,Amazon Redshift 将应用存储压缩。此优化确保了即使在由最终用户加载时,集群中的数据也能被高效存储。应用压缩所需的分析可能需要大量时间。

分析

Advisor 分析将检查是否有被自动压缩分析延迟的 COPY 操作。该分析通过对加载中的数据进行采样来确定压缩编码。此采样类似于由 ANALYZE COMPRESSION 命令执行的采样。

当您将数据作为结构化流程的一部分加载时(例如在夜间提取、转换、加载 (ETL) 批处理中),您可以预先定义压缩。您还可以优化表定义以永久跳过此阶段而不会造成任何负面影响。

建议

要通过跳过压缩分析阶段来提高 COPY 响应能力,请实施以下两个选项之一:

  • 在创建您要使用 COPY 命令加载的任何表时使用列 ENCODE 参数。

  • 通过在 COPY 命令中应用 COMPUPDATE OFF 参数来完全禁用压缩。

最佳解决方案通常是在表创建期间使用列编码,因为此方法还保留了在磁盘上存储压缩数据的好处。您可以使用 ANALYZE COMPRESSION 命令建议压缩编码,但您必须重新创建表以应用这些编码。要自动执行此流程,您可以使用在 GitHub 上找到的 AWS ColumnEncodingUtility

要识别触发了自动压缩分析的最新 COPY 操作,请运行以下 SQL 命令。

WITH xids AS ( SELECT xid FROM stl_query WHERE userid>1 AND aborted=0 AND querytxt = 'analyze compression phase 1' GROUP BY xid INTERSECT SELECT xid FROM stl_commit_stats WHERE node=-1) SELECT a.userid, a.query, a.xid, a.starttime, b.complyze_sec, a.copy_sec, a.copy_sql FROM (SELECT q.userid, q.query, q.xid, date_trunc('s',q.starttime) starttime, substring(querytxt,1,100) as copy_sql, ROUND(datediff(ms,starttime,endtime)::numeric / 1000.0, 2) copy_sec FROM stl_query q JOIN xids USING (xid) WHERE (querytxt ilike 'copy %from%' OR querytxt ilike '% copy %from%') AND querytxt not like 'COPY ANALYZE %') a LEFT JOIN (SELECT xid, ROUND(sum(datediff(ms,starttime,endtime))::numeric / 1000.0,2) complyze_sec FROM stl_query q JOIN xids USING (xid) WHERE (querytxt like 'COPY ANALYZE %' OR querytxt like 'analyze compression phase %') GROUP BY xid ) b ON a.xid = b.xid WHERE b.complyze_sec IS NOT NULL ORDER BY a.copy_sql, a.starttime;

实施提示

  • 确保在 ETL 过程中创建的所有大尺寸表(例如,暂存表和临时表)声明了除第一个排序键之外的所有列。

  • 估计正在为由前面的 SQL 命令标识的每个 COPY 命令加载的表的预计生命周期长短。如果您确信该表仍然非常小,请禁用压缩和 COMPUPDATE OFF 参数。否则,请在使用 COPY 命令加载表之前使用显式压缩创建表。

拆分由 COPY 加载的 Amazon S3 对象

COPY 命令利用 Amazon Redshift 中的大规模并行处理 (MPP) 架构在 Amazon S3 上的文件中读取和加载数据。COPY 命令从多个文件并行加载数据,向集群中的节点划分工作负载。要实现最佳吞吐量,我们强烈建议您将数据拆分成多个文件,以便利用并行处理。

分析

Advisor 分析可识别用于加载在 S3 中暂存的少量文件中包含的大型数据集的 COPY 命令。加载来自若干文件的大型数据集的耗时的 COPY 命令通常有机会获得相当大的性能改进。当 Advisor 发现这些 COPY 命令需要大量时间时,它将通过将数据拆分为 S3 中的额外文件来创建提高并行度的建议。

建议

在这种情况下,我们建议执行以下操作(按优先顺序列出):

  1. 优化加载的文件数比集群节点数更少的 COPY 命令。

  2. 优化加载的文件数比集群切片数更少的 COPY 命令。

  3. 优化这样的 COPY 命令:其中的文件数不是集群切片数的倍数。

某些 COPY 命令加载大量数据或运行很长时间。对于这些命令,我们建议您在从 S3 加载大量数据对象时,其数量应等于集群中的切片数的倍数。要识别 COPY 命令已加载的 S3 对象数,请以超级用户身份运行以下 SQL 代码。

SELECT query, COUNT(*) num_files, ROUND(MAX(wq.total_exec_time/1000000.0),2) execution_secs, ROUND(SUM(transfer_size)/(1024.0*1024.0),2) total_mb, SUBSTRING(querytxt,1,60) copy_sql FROM stl_s3client s JOIN stl_query q USING (query) JOIN stl_wlm_query wq USING (query) WHERE s.userid>1 AND http_method = 'GET' AND POSITION('COPY ANALYZE' IN querytxt) = 0 AND aborted = 0 AND final_state='Completed' GROUP BY query, querytxt HAVING (SUM(transfer_size)/(1024*1024))/COUNT(*) >= 2 ORDER BY CASE WHEN COUNT(*) < (SELECT max(node)+1 FROM stv_slices) THEN 1 WHEN COUNT(*) < (SELECT COUNT(*) FROM stv_slices WHERE node=0) THEN 2 ELSE 2+((COUNT(*) % (SELECT COUNT(*) FROM stv_slices))/(SELECT COUNT(*)::DECIMAL FROM stv_slices)) END, (SUM(transfer_size)/(1024.0*1024.0))/COUNT(*) DESC;

实施提示

  • 节点中的切片数取决于集群的节点大小。有关各种节点类型中的切片数的更多信息,请参阅 Amazon Redshift Cluster Management Guide 中的 Amazon Redshift 中的集群和节点

  • 您可以通过指定一个通用前缀(对于集合,则为前缀键),或通过在清单文件中明确列出文件,从而加载多个文件。有关加载文件的更多信息,请参阅将数据拆分成多个文件

  • Amazon Redshift 在拆分工作负载时不会考虑文件大小。拆分您的加载数据文件,使文件大小大约相等,压缩后的文件大小介于 1 MB 和 1 GB 之间。为了获得最佳并行度,在压缩后,理想的大小在 1 MB 和 125 MB 之间。

更新表统计数据

Amazon Redshift 使用基于成本的查询优化程序为查询选择最佳执行计划。成本估算基于使用 ANALYZE 命令收集的表统计数据。当统计数据过时或丢失时,数据库可能会选择一个效率较低的查询执行计划,尤其是对于复杂的查询。保存最新统计数据可帮助复杂的查询在尽可能短的时间内运行。

分析

Advisor 分析可跟踪其统计数据已过时或丢失的表。它将审查与复杂查询关联的表访问元数据。如果使用复杂模式频繁访问的表缺少统计数据,Advisor 将创建关键建议以运行 ANALYZE。如果使用复杂模式频繁访问的表具有过时的统计数据,Advisor 将创建启发式建议以运行 ANALYZE。

建议

每当表内容发生重大变化时,请使用 ANALYZE 更新统计数据。每当使用 COPY 或 INSERT 命令将大量新数据行加载到现有表中时,我们建议运行 ANALYZE。每当使用 UPDATE 或 DELETE 命令修改大量行时,我们也建议运行 ANALYZE。要识别具有缺失或过时的统计数据的表,请以超级用户身份运行以下 SQL 命令。结果将按表的大小顺序排列。

要识别具有缺失或过时的统计数据的表,请以超级用户身份运行以下 SQL 命令。结果将按表的大小顺序排列。

SELECT ti.schema||'.'||ti."table" tablename, ti.size table_size_mb, ti.stats_off statistics_accuracy FROM svv_table_info ti WHERE ti.stats_off > 5.00 ORDER BY ti.size DESC;

实施提示

默认 ANALYZE 阈值为 10%。此默认值意味着,如果自上次 ANALYZE 之后,给定表有不到 10% 的行发生了更改,则 ANALYZE 命令将跳过此表。因此,您可以选择在每个 ETL 流程结束时发出 ANALYZE 分析。采用此方法意味着经常会跳过 ANALYZE,但也确保了 ANALYZE 在需要时运行。

ANALYZE 统计数据对在联接中使用的列(例如,JOIN tbl_a ON col_b)或作为谓词的列(例如,WHERE col_b = 'xyz')影响最大。默认情况下,ANALYZE 将收集指定的表中的所有列的统计数据。如果需要,您可以通过仅对受 ANALYZE 影响最大的列运行 ANALYZE 来减少运行该命令所需的时间。您可以运行以下 SQL 命令来识别要用作谓词的列。您还可以通过指定 ANALYZE PREDICATE COLUMNS 来让 Amazon Redshift 选择要分析的列。

WITH predicate_column_info as ( SELECT ns.nspname AS schema_name, c.relname AS table_name, a.attnum as col_num, a.attname as col_name, CASE WHEN 10002 = s.stakind1 THEN array_to_string(stavalues1, '||') WHEN 10002 = s.stakind2 THEN array_to_string(stavalues2, '||') WHEN 10002 = s.stakind3 THEN array_to_string(stavalues3, '||') WHEN 10002 = s.stakind4 THEN array_to_string(stavalues4, '||') ELSE NULL::varchar END AS pred_ts FROM pg_statistic s JOIN pg_class c ON c.oid = s.starelid JOIN pg_namespace ns ON c.relnamespace = ns.oid JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum) SELECT schema_name, table_name, col_num, col_name, pred_ts NOT LIKE '2000-01-01%' AS is_predicate, CASE WHEN pred_ts NOT LIKE '2000-01-01%' THEN (split_part(pred_ts, '||',1))::timestamp ELSE NULL::timestamp END as first_predicate_use, CASE WHEN pred_ts NOT LIKE '%||2000-01-01%' THEN (split_part(pred_ts, '||',2))::timestamp ELSE NULL::timestamp END as last_analyze FROM predicate_column_info;

有关更多信息,请参阅分析表

启用短查询加速

短查询加速 (SQA) 让选定的短时查询优先于长时查询。SQA 在专用空间中执行短时查询,因此 SQA 查询不会被迫排在队列中的长时查询后面等待。SQA 仅优先处理用户定义的队列中的短时查询。使用 SQA,短时查询会更快地开始运行,用户会更快地看到结果。

如果您启用 SQA,则可以减少或消除专用于运行短查询的工作负载管理 (WLM) 队列。此外,长时查询无需与短查询竞争队列中的插槽,因此您可以将 WLM 队列配置为使用较少的查询插槽。当您使用较低的并发度时,查询吞吐量会增加,而且大多数工作负载的总体系统性能会得到提高。有关更多信息,请参阅短查询加速

分析

Advisor 检查工作负载模式,并报告 SQA 可以减少延迟的最近查询的数量以及符合 SQA 条件的查询的每日队列时间。

建议

修改 WLM 配置以启用 SQA。Amazon Redshift 使用机器学习算法分析每个符合条件的查询。预测质量会随着 SQA 从您的查询模式中学习而改进。有关更多信息,请参阅配置工作负载管理

当您启用 SQA 时,默认情况下 WLM 会将短查询的最大运行时设置为动态。我们建议保留 SQA 最大运行时的动态设置。

实施提示

要检查是否启用了 SQA,请运行以下查询。如果查询返回一行内容,则说明 SQA 已启用。

select * from stv_wlm_service_class_config where service_class = 14;

有关更多信息,请参阅监控 SQA

替换单列交错排序键

有些表在单个列上使用交错排序键。通常,与在单个列上使用复合排序键的表相比,这样的表效率较低,并且消耗的资源更多。

在某些情况下,当不同查询使用多个列进行筛选时,交错排序可以提升性能。在单个列上使用交错排序键仅在特定情况下有效。在这种情况下,查询通常会筛选前 8 个字节中具有长的公共前缀的 CHAR 或 VARCHAR 列值。例如,URL 字符串的前缀通常为“https://”。对于单列键,在进行任何其他筛选操作时,复合排序优于交错排序。复合排序可加快联接、GROUP BY 和 ORDER BY 操作,以及在已排序列上使用 PARTITION BY 和 ORDER BY 的窗口函数。交错排序不会给这些操作带来好处。有关更多信息,请参阅选择排序键

使用复合排序可以显著降低维护开销。具有复合排序键的表不需要昂贵的 VACUUM REINDEX 操作,这些操作对于交错排序是必需的。在实践中,对于绝大多数 Amazon Redshift 工作负载,复合排序键比交错排序键更有效。

分析

Advisor 跟踪在单个列上使用交错排序键的表。

建议

如果表在单个列上使用了交错排序,请重新创建表以使用复合排序键。在创建新表时,对单列排序使用复合排序键。要查找使用单列排序键的交错表,请运行以下命令。

SELECT schema AS schemaname, "table" AS tablename FROM svv_table_info WHERE table_id IN ( SELECT attrelid FROM pg_attribute WHERE attrelid IN ( SELECT attrelid FROM pg_attribute WHERE attsortkeyord <> 0 GROUP BY attrelid HAVING MAX(attsortkeyord) = -1 ) AND NOT (atttypid IN (1042, 1043) AND atttypmod > 12) AND attsortkeyord = -1);

有关选择最佳排序样式的其他信息,请参阅 AWS 大数据博客文章 Amazon Redshift 工程的高级表设计行动手册:复合和交错排序键