头图

实现更高性能,一起探索Amazon Redshift高级查询加速器

亚马逊云开发者

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非常重要,同时也有望破除云数据仓库复杂度越来越高、我们越来越难以理解其运作行为的困境。和大家一样,我也殷切期待着这样一套无需理解底层工作原理、就能始终保持良好状态的业务体系。

阅读 426

亚马逊云开发者
亚马逊云开发者社区是面向开发者交流与互动的平台。在这里,你可以分享和获取有关云计算、人工智能、IoT...

亚马逊云开发者社区是面向开发者交流与互动的平台。在这里,你可以分享和获取有关云计算、人工智能、IoT...

2.9k 声望
9.5k 粉丝
0 条评论

亚马逊云开发者社区是面向开发者交流与互动的平台。在这里,你可以分享和获取有关云计算、人工智能、IoT...

2.9k 声望
9.5k 粉丝
文章目录
宣传栏