关于data:实现更高性能一起探索Amazon-Redshift高级查询加速器

47次阅读

共计 16429 个字符，预计需要花费 42 分钟才能阅读完成。

AQUA（高级查问加速器）是什么？

AQUA 是一款功能强大的硬件查问加速器，将配合 RA3 节点（ra3.4xl 或 ra3.16xl）与 S3 托管存储独特起效。

上面来看亚马逊云科技官网博文中的相干形容：

这套存储系统采纳多种提醒机制（包含数据块热度、数据阻塞与工作负载模式）治理缓存，借以实现更高性能。

除了缓存机制之外，AQUA 还充分发挥 Amazon Nitro System 与定制化 FPGA 减速计划的劣势，在更靠近数据的地位解决规约及聚合查问对应的计算负载。这种形式可能缩小网络流量，减弱 RA3 节点中 CPU 的工作累赘，由此将查问性能晋升达 10 倍。更重要的是，AQUA 不产生任何额定费用，也无需更改代码内容。AQUA 还采纳 Amazon Simple Storage Service (S3)提供的疾速、高带宽连贯资源。

利用快照创立 AQUA 集群

这里，咱们将尝试通过快照还原性能创立 AQUA 集群。您能够抉择 ra3.4xlarge 或 ra3.16xlarge 节点类型。如果您曾经领有采纳这些节点的集群，则其中的 AQUA 会被默认配置为“Automatic”。要开始应用 AQUA，请抉择 [Actions]-[Configure AQUA] 并将以下对话框中的  Automatic  配置调整为Turn On。

这里，咱们应用默认配置 Automatic 创立一套集群。在配置 AQUA 的过程中，您能够灵便调整以下选项：

  • Automatic (默认)
  • Redshift 确定是否应用 AQUA。
  • 截至目前，AQUA（高级查问加速器）应用状态仍为：尚未激活 AQUA，但状况随时可能有所变动（“Currently, AQUA isn’t activated with this option, but this behavior is subject to change”）。在变动之前，此状态依然等效于 Turn Off；代表 AQUA 不会被激活。
  • Turn On
  • 您将抉择始终应用 AQUA。AQUA 仅可在某些亚马逊云科技区域以及 ra3.4xlarge 与 ra3.16xlarge 节点类型当中激活。
  • Turn Off
  • 您抉择不应用 AQUA。

期待约 5 分钟后，AQUA 即转为 Available 可用状态。能够看到，本文中的示例集群采纳 AQUA“Automatic”配置进行启动。

创立测试数据

AQUA 在 LIKE 及 SIMILAR TO 等操作中的减速成果尤其杰出，这里咱们筹备了约 3 亿条数据。

dev=> create table lineitem (
dev(>   l_orderkey bigint not null,
dev(>   l_partkey bigint,
dev(>   l_suppkey bigint,
dev(>   l_linenumber integer not null,
dev(>   l_quantity decimal(18,4),
dev(>   l_extendedprice decimal(18,4),
dev(>   l_discount decimal(18,4),
dev(>   l_tax decimal(18,4),
dev(>   l_returnflag varchar(1),
dev(>   l_linestatus varchar(1),
dev(>   l_shipdate date,
dev(>   l_commitdate date,
dev(>   l_receiptdate date,
dev(>   l_shipinstruct varchar(25),
dev(>   l_shipmode varchar(10),
dev(>   l_comment varchar(44))
dev-> distkey (l_orderkey)
dev-> sortkey (l_receiptdate);
CREATE TABLE
dev=> copy lineitem from 's3://cm-bucket/redshift-immersionday-labs/data/lineitem-part/'
dev-> iam_role 'arn:aws:iam::123456789012:role/AmazonRedshiftRole'
dev-> region 'ap-northeast-1' gzip delimiter '|' compupdate preset;

INFO:  Load into table 'lineitem' completed, 303008217 record(s) loaded successfully.
COPY

dev=> select * from lineitem limit 1;
-[RECORD 1]---+----------------------------------------
l_orderkey      | 7428384
l_partkey       | 9121341
l_suppkey       | 621360
l_linenumber    | 4
l_quantity      | 23.0000
l_extendedprice | 31323.4700
l_discount      | 0.0900
l_tax           | 0.0500
l_returnflag    | R
l_linestatus    | F
l_shipdate      | 1992-01-02
l_commitdate    | 1992-03-22
l_receiptdate   | 1992-01-03
l_shipinstruct  | DELIVER IN PERSON
l_shipmode      | FOB
l_comment       | haggle carefully about the furiously ir

AQUA 性能测试

这里，咱们通过显式更改 Turn On/Off 进行性能差别比照。要更新 AQUA 配置，您能够点击[Actions]-[Configure AQUA]。

备注：
在对话框中更改 Turn ON/Off 并点击 [Save changes] 之后，集群将立刻重新启动以利用变更。

测试查问

在测试中，咱们执行以下 SIMILAR TO 与  LIKE  示例查问，并别离记录其响应工夫。

  • SIMILAR TO 示例查问
-- explain
select sum(l_orderkey), count(*) 
from lineitem 
where
  l_comment similar to 'slyly %' or
  l_comment similar to 'plant %' or
  l_comment similar to 'fina %' or
  l_comment similar to 'quick %' or
  l_comment similar to 'slyly %' or
  l_comment similar to 'quickly %' or
  l_comment similar to '%about%' or
  l_comment similar to 'final%' or
  l_comment similar to '%final%' or
  l_comment similar to 'breach%' or
  l_comment similar to 'egular%' or
  l_comment similar to '%closely%' or
  l_comment similar to 'closely%' or
  l_comment similar to '%idea%' or
  l_comment similar to 'idea%' ;

LIKE 示例查问

-- explain
select sum(l_orderkey), count(*) 
from lineitem 
where
  l_comment like 'slyly %' or
  l_comment like 'plant %' or
  l_comment like 'fina %' or
  l_comment like 'quick %' or
  l_comment like 'slyly %' or
  l_comment like 'quickly %' or
  l_comment like '%about%' or
  l_comment like 'final%' or
  l_comment like '%final%' or
  l_comment like 'breach%' or
  l_comment like 'egular%' or
  l_comment like '%closely%' or
  l_comment like 'closely%' or
  l_comment like '%idea%' or
  l_comment like 'idea%' ;

咱们在这里搜寻了蕴含“l_comment”的字符串，并汇总相干记录。我晓得间接用 or 连贯 SIMILAR TO  与 LIKE 的作法不太迷信，但咱们应该置信 AQUA 的能力，对吧？

咱们禁用了后果缓存以精确掂量解决时长，以下为最终后果。

set enable_result_cache_for_session to off;
  • SIMILAR TO 性能比拟
  • AQUA 未激活 (Turn Off)
dev=> select sum(l_orderkey), count(*)
dev-> from lineitem
dev-> where
dev->   l_comment similar to 'slyly %' or
dev->   l_comment similar to 'plant %' or
dev->   l_comment similar to 'fina %' or
dev->   l_comment similar to 'quick %' or
dev->   l_comment similar to 'slyly %' or
dev->   l_comment similar to 'quickly %' or
dev->   l_comment similar to '%about%' or
dev->   l_comment similar to 'final%' or
dev->   l_comment similar to '%final%' or
dev->   l_comment similar to 'breach%' or
dev->   l_comment similar to 'egular%' or
dev->   l_comment similar to '%closely%' or
dev->   l_comment similar to 'closely%' or
dev->   l_comment similar to '%idea%' or
dev->   l_comment similar to 'idea%' ;

       sum        |  count
------------------+---------
 1440371216714447 | 9496106
(1 row)
Time: 215896.819 ms

select sum(l_orderkey), count(*)
from lineitem
where
  l_comment similar to 'slyly %' or
  l_comment similar to 'plant %' or
  l_comment similar to 'fina %' or
  l_comment similar to 'quick %' or
  l_comment similar to 'slyly %' or
  l_comment similar to 'quickly %' or
  l_comment similar to '%about%' or
  l_comment similar to 'final%' or
  l_comment similar to '%final%' or
  l_comment similar to 'breach%' or
  l_comment similar to 'egular%' or
  l_comment similar to '%closely%' or
  l_comment similar to 'closely%' or
  l_comment similar to '%idea%' or
  l_comment similar to 'idea%' ;
       sum        |  count
------------------+---------
 1440371216714447 | 9496106
(1 row)
Time: 211313.374 ms

以下为执行打算。

dev=> explain
select sum(l_orderkey), count(*)
from lineitem
where
  l_comment similar to 'slyly %' or
  l_comment similar to 'plant %' or
  l_comment similar to 'fina %' or
  l_comment similar to 'quick %' or
  l_comment similar to 'slyly %' or
  l_comment similar to 'quickly %' or
  l_comment similar to '%about%' or
  l_comment similar to 'final%' or
  l_comment similar to '%final%' or
  l_comment similar to 'breach%' or
  l_comment similar to 'egular%' or
  l_comment similar to '%closely%' or
  l_comment similar to 'closely%' or
  l_comment similar to '%idea%' or
  l_comment similar to 'idea%' ;
                                                                                                                                                                                                                                                                                                                                           QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 XN Aggregate  (cost=13830214.62..13830214.62 rows=1 width=8)
   ->  XN Seq Scan on lineitem  (cost=0.00..13635370.08 rows=38968908 width=8)
         Filter: (((l_comment)::text ~ '^(.*idea.*)$'::text) OR ((l_comment)::text ~ '^(idea.*)$'::text) OR ((l_comment)::text ~ '^(fina .*)$'::text) OR ((l_comment)::text ~ '^(.*about.*)$'::text) OR ((l_comment)::text ~ '^(.*final.*)$'::text) OR ((l_comment)::text ~ '^(final.*)$'::text) OR ((l_comment)::text ~ '^(plant .*)$'::text) OR ((l_comment)::text ~ '^(quick .*)$'::text) OR ((l_comment)::text ~ '^(slyly .*)$'::text) OR ((l_comment)::text ~ '^(breach.*)$'::text) OR ((l_comment)::text ~ '^(egular.*)$'::text) OR ((l_comment)::text ~ '^(.*closely.*)$'::text) OR ((l_comment)::text ~ '^(closely.*)$'::text) OR ((l_comment)::text ~ '^(quickly .*)$'::text))
(3 rows)

Time: 8.506 ms

AQUA 已激活 (Turn On)

dev=> select sum(l_orderkey), count(*)
dev-> from lineitem
dev-> where
dev->   l_comment similar to 'slyly %' or
dev->   l_comment similar to 'plant %' or
dev->   l_comment similar to 'fina %' or
dev->   l_comment similar to 'quick %' or
dev->   l_comment similar to 'slyly %' or
dev->   l_comment similar to 'quickly %' or
dev->   l_comment similar to '%about%' or
dev->   l_comment similar to 'final%' or
dev->   l_comment similar to '%final%' or
dev->   l_comment similar to 'breach%' or
dev->   l_comment similar to 'egular%' or
dev->   l_comment similar to '%closely%' or
dev->   l_comment similar to 'closely%' or
dev->   l_comment similar to '%idea%' or
dev->   l_comment similar to 'idea%' ;
       sum        |  count
------------------+---------
 1440371216714447 | 9496106
(1 row)
Time: 29191.625 ms

dev=> select sum(l_orderkey), count(*)
dev-> from lineitem
dev-> where
dev->   l_comment similar to 'slyly %' or
dev->   l_comment similar to 'plant %' or
dev->   l_comment similar to 'fina %' or
dev->   l_comment similar to 'quick %' or
dev->   l_comment similar to 'slyly %' or
dev->   l_comment similar to 'quickly %' or
dev->   l_comment similar to '%about%' or
dev->   l_comment similar to 'final%' or
dev->   l_comment similar to '%final%' or
dev->   l_comment similar to 'breach%' or
dev->   l_comment similar to 'egular%' or
dev->   l_comment similar to '%closely%' or
dev->   l_comment similar to 'closely%' or
dev->   l_comment similar to '%idea%' or
dev->   l_comment similar to 'idea%' ;
       sum        |  count
------------------+---------
 1440371216714447 | 9496106
(1 row)
Time: 7512.982 ms

以下为执行打算。

dev=> explain
select sum(l_orderkey), count(*)
from lineitem
where
  l_comment similar to 'slyly %' or
  l_comment similar to 'plant %' or
  l_comment similar to 'fina %' or
  l_comment similar to 'quick %' or
  l_comment similar to 'slyly %' or
  l_comment similar to 'quickly %' or
  l_comment similar to '%about%' or
  l_comment similar to 'final%' or
  l_comment similar to '%final%' or
  l_comment similar to 'breach%' or
  l_comment similar to 'egular%' or
  l_comment similar to '%closely%' or
  l_comment similar to 'closely%' or
  l_comment similar to '%idea%' or
  l_comment similar to 'idea%' ;
                                                                                                                                                                                                                                                                                                                                           QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 XN Aggregate  (cost=13830214.62..13830214.62 rows=1 width=8)
   ->  XN Seq Scan on lineitem  (cost=0.00..13635370.08 rows=38968908 width=8)
         Filter: (((l_comment)::text ~ '^(.*idea.*)$'::text) OR ((l_comment)::text ~ '^(idea.*)$'::text) OR ((l_comment)::text ~ '^(fina .*)$'::text) OR ((l_comment)::text ~ '^(.*about.*)$'::text) OR ((l_comment)::text ~ '^(.*final.*)$'::text) OR ((l_comment)::text ~ '^(final.*)$'::text) OR ((l_comment)::text ~ '^(plant .*)$'::text) OR ((l_comment)::text ~ '^(quick .*)$'::text) OR ((l_comment)::text ~ '^(slyly .*)$'::text) OR ((l_comment)::text ~ '^(breach.*)$'::text) OR ((l_comment)::text ~ '^(egular.*)$'::text) OR ((l_comment)::text ~ '^(.*closely.*)$'::text) OR ((l_comment)::text ~ '^(closely.*)$'::text) OR ((l_comment)::text ~ '^(quickly .*)$'::text))
(3 rows)

Time: 8.683 ms

测试后果

在 AQUA 已激活的状况下，SIMILAR TO 查问性能失去显著晋升：第一轮测试中晋升 7.4 倍，第二及后续轮次中晋升 28.1 倍。具体查问打算与 AQUA 无关，其中的状态均转换为正则表达式。（下表中的时长单位为秒）

  • LIKE 性能比拟
  • AQUA 未激活 (Turn Off)
dev=> select sum(l_orderkey), count(*)
dev-> from lineitem
dev-> where
dev->   l_comment like 'slyly %' or
dev->   l_comment like 'plant %' or
dev->   l_comment like 'fina %' or
dev->   l_comment like 'quick %' or
dev->   l_comment like 'slyly %' or
dev->   l_comment like 'quickly %' or
dev->   l_comment like '%about%' or
dev->   l_comment like 'final%' or
dev->   l_comment like '%final%' or
dev->   l_comment like 'breach%' or
dev->   l_comment like 'egular%' or
dev->   l_comment like '%closely%' or
dev->   l_comment like 'closely%' or
dev->   l_comment like '%idea%' or
dev->   l_comment like 'idea%' ;

       sum        |  count
------------------+---------
 1440371216714447 | 9496106
(1 row)

Time: 10276.394 ms
dev=>
dev=>
dev=> select sum(l_orderkey), count(*)
from lineitem
where
  l_comment like 'slyly %' or
  l_comment like 'plant %' or
  l_comment like 'fina %' or
  l_comment like 'quick %' or
  l_comment like 'slyly %' or
  l_comment like 'quickly %' or
  l_comment like '%about%' or
  l_comment like 'final%' or
  l_comment like '%final%' or
  l_comment like 'breach%' or
  l_comment like 'egular%' or
  l_comment like '%closely%' or
  l_comment like 'closely%' or
  l_comment like '%idea%' or
  l_comment like 'idea%' ;
       sum        |  count
------------------+---------
 1440371216714447 | 9496106
(1 row)

Time: 6921.963 ms

以下为执行打算。

dev=> explain
select sum(l_orderkey), count(*)
from lineitem
where
  l_comment like 'slyly %' or
  l_comment like 'plant %' or
  l_comment like 'fina %' or
  l_comment like 'quick %' or
  l_comment like 'slyly %' or
  l_comment like 'quickly %' or
  l_comment like '%about%' or
  l_comment like 'final%' or
  l_comment like '%final%' or
  l_comment like 'breach%' or
  l_comment like 'egular%' or
  l_comment like '%closely%' or
  l_comment like 'closely%' or
  l_comment like '%idea%' or
  l_comment like 'idea%' ;
                                                                                                                                                                                                                                                                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 XN Aggregate  (cost=13688958.11..13688958.11 rows=1 width=8)
   ->  XN Seq Scan on lineitem  (cost=0.00..13635370.08 rows=10717605 width=8)
         Filter: (((l_comment)::text ~~ '%idea%'::text) OR ((l_comment)::text ~~ '%about%'::text) OR ((l_comment)::text ~~ '%final%'::text) OR ((l_comment)::text ~~ '%closely%'::text) OR ((l_comment)::text ~~ 'breach%'::text) OR ((l_comment)::text ~~ 'closely%'::text) OR ((l_comment)::text ~~ 'egular%'::text) OR ((l_comment)::text ~~ 'final%'::text) OR ((l_comment)::text ~~ 'idea%'::text) OR ((l_comment)::text ~~ 'fina %'::text) OR ((l_comment)::text ~~ 'plant %'::text) OR ((l_comment)::text ~~ 'quick %'::text) OR ((l_comment)::text ~~ 'quickly %'::text) OR ((l_comment)::text ~~ 'slyly %'::text))
(3 rows)

Time: 7.985 ms

AQUA 已激活 (Turn On)

dev=> select sum(l_orderkey), count(*)
dev-> from lineitem
dev-> where
dev->   l_comment like 'slyly %' or
dev->   l_comment like 'plant %' or
dev->   l_comment like 'fina %' or
dev->   l_comment like 'quick %' or
dev->   l_comment like 'slyly %' or
dev->   l_comment like 'quickly %' or
dev->   l_comment like '%about%' or
dev->   l_comment like 'final%' or
dev->   l_comment like '%final%' or
dev->   l_comment like 'breach%' or
dev->   l_comment like 'egular%' or
dev->   l_comment like '%closely%' or
dev->   l_comment like 'closely%' or
dev->   l_comment like '%idea%' or
dev->   l_comment like 'idea%' ;

       sum        |  count
------------------+---------
 1440371216714447 | 9496106
(1 row)

Time: 11116.387 ms
dev=>
dev=> select sum(l_orderkey), count(*)
from lineitem
where
  l_comment like 'slyly %' or
  l_comment like 'plant %' or
  l_comment like 'fina %' or
  l_comment like 'quick %' or
  l_comment like 'slyly %' or
  l_comment like 'quickly %' or
  l_comment like '%about%' or
  l_comment like 'final%' or
  l_comment like '%final%' or
  l_comment like 'breach%' or
  l_comment like 'egular%' or
  l_comment like '%closely%' or
  l_comment like 'closely%' or
  l_comment like '%idea%' or
  l_comment like 'idea%' ;
       sum        |  count
------------------+---------
 1440371216714447 | 9496106
(1 row)

Time: 7526.141 ms

以下为执行打算。

dev=> explain
select sum(l_orderkey), count(*)
from lineitem
where
  l_comment like 'slyly %' or
  l_comment like 'plant %' or
  l_comment like 'fina %' or
  l_comment like 'quick %' or
  l_comment like 'slyly %' or
  l_comment like 'quickly %' or
  l_comment like '%about%' or
  l_comment like 'final%' or
  l_comment like '%final%' or
  l_comment like 'breach%' or
  l_comment like 'egular%' or
  l_comment like '%closely%' or
  l_comment like 'closely%' or
  l_comment like '%idea%' or
  l_comment like 'idea%' ;
                                                                                                                                                                                                                                                                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 XN Aggregate  (cost=13688958.11..13688958.11 rows=1 width=8)
   ->  XN Seq Scan on lineitem  (cost=0.00..13635370.08 rows=10717605 width=8)
         Filter: (((l_comment)::text ~~ '%idea%'::text) OR ((l_comment)::text ~~ '%about%'::text) OR ((l_comment)::text ~~ '%final%'::text) OR ((l_comment)::text ~~ '%closely%'::text) OR ((l_comment)::text ~~ 'breach%'::text) OR ((l_comment)::text ~~ 'closely%'::text) OR ((l_comment)::text ~~ 'egular%'::text) OR ((l_comment)::text ~~ 'final%'::text) OR ((l_comment)::text ~~ 'idea%'::text) OR ((l_comment)::text ~~ 'fina %'::text) OR ((l_comment)::text ~~ 'plant %'::text) OR ((l_comment)::text ~~ 'quick %'::text) OR ((l_comment)::text ~~ 'quickly %'::text) OR ((l_comment)::text ~~ 'slyly %'::text))
(3 rows)

Time: 8.096 ms

测试后果

在激活 AQUA 之后，LIKE 查问的性能略有降落：第一轮及之后轮次中的性能约为未激活时的 0.9 倍。（下表中的时长单位为秒）

探讨

通过应用 AQUA，SIMILAR TO过滤查问的性能达到 7 至 28 倍的晋升，但 LIKE 查问的执行速度反而有所降落。能够看到，AQUA 会造成肯定水平的资源开销。

在本次测试中，只管咱们禁用了后果缓存，第一轮查问与后续轮次当中依然存在解决工夫上的差别。造成这种状况的起因可能包含：

在未激活 AQUA 的状况下，首轮查问当中蕴含记录编译、以及将数据从托管 S3 加载至本地存储内的工夫。这两项操作的后果都被纳入缓存，可供第二轮查问间接应用。另一方面，激活 AQUA 时的首轮查问当中蕴含记录编译以及将所需数据由托管 S3 加载至 AQUA 的工夫。加载至 AQUA 的数据可能也会被纳入缓存，但目前还没有对于 AQUA 缓存的正式条件形容或阐明，因而咱们无奈对具体缓存量做出精确估算。

AQUA 费率规范

完全免费，零老本！！！

总结

通过这些测试，咱们证实激活 AQUA 之后，SIMILAR TO 过滤查问的性能可晋升 7 至 28 倍。

在这里，咱们显式开启 / 敞开 AQUA，并配合不同数据进行了全面测试。依据查问与工作负载的不同，AQUA 有时候反而会拉低执行性能。我心愿默认设置“Automatic”能快点起效，由 Redshift 自主判断是否须要应用 AQUA。

测试后果通知咱们，并不是所有工作负载都适宜 AQUA；因而本文倡议大家依据理论用例决定是否激活 AQUA。至多在应用受反对节点类型的集群当中，AQUA 默认设置为“Automatic”。如果后续亚马逊云科技凋谢 AQUA 主动激活等性能，那么即便不刻意调整，咱们也能充沛享受 AQUA 带来的性能加强。请急躁期待，独特期待 RedShift 与 AQUA 的协同倒退。

如果能想方法将由托管 S3 加载至本地存储的数据缓存与编译缓存有效化，咱们的测试应该会更为精密，并帮忙咱们思考更多现有问题、挑战与解决办法。这一点不仅对 Redshift 十分重要，同时也无望破除云数据仓库复杂度越来越高、咱们越来越难以了解其运作行为的窘境。和大家一样，我也殷切期待着这样一套无需了解底层工作原理、就能始终保持良好状态的业务体系。

正文完
data
发表至： data
2021-12-20
 0
关于data:身兼数职的Amazon-DocumentDB还有什么不为人知的功能
关于data:如何使用流程-中的-DataObject-并为流程设置租户
关于data:为Amazon-DMS数据库迁移任务建立自动化监控机制
关于data:手把手教你使用-Timestream-实现物联网时序数据存储和分析