文章图片


文章图片

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. COPYdev=> 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 msselect 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 msdev=> 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非常重要，同时也有望破除云数据仓库复杂度越来越高、我们越来越难以理解其运作行为的困境。和大家一样，我也殷切期待着这样一套无需理解底层工作原理、就能始终保持良好状态的业务体系。
【实现更高性能，一起探索Amazon Redshift高级查询加速器】

推荐阅读

• 

  高三复读生该如何减压？身心健康才是成功教育！
• 

  ps褶皱文字怎么做 photoshop制作褶皱文字效果的操作步骤
• 

  昆仑山在哪 昆仑山的位置
• 

  日异月殊的意思 日异月殊的出处
• 

  高效能慢生活践行第626天1103-2020
• 

  等额本息和等额本金哪个划算 等额本息和等额本金哪个划算30年
• 

  浴盐多久用一次好
• 

  板鞋五个洞系鞋带的方法 板鞋五个洞系鞋带的方法图解
• 

  血见愁的功效与作用 血见愁
• 

  烫头发的药水
• 

  适合旅游的时候用的相机
• 

  天魔劫 天劫
• 

  盈滨海岸 一米海岸
• 

  用完脱毛仪之后可以用身体乳么
• 

  苦苣菜的做法和功效
• 

  电视坏了可以改成什么样的，电视坏了可以改成什么样的电视
• 

  虎牙直播一直正在加载，虎牙直播一直正在加载怎么回事
• 

  建安文学对后代文学有何积极影响
• 

  2022年5月希腊移民入境人数激增
• 

  想回农村养兔子最少得养多少？

• SQL|SQL基本功（五）--函数、谓词、CASE表达式
• python|深度盘点（一文详解数据分析中100个常用指标和术语）
• 专注于最有价值的事情！——亚马逊云科技首席科学家工作心得分享
• Data|单链表的增删查改
• C|生成一个整数集合的所有子集
• 身兼数职的Amazon DocumentDB，还有什么不为人知的功能（）
• Big|Anaconda 换源
• Data|拜占庭将军问题中的签名算法SM，以及有关证明。
• 大数据|HBase导出CSV格式数据的方法
• big|zookeeper集群启动时报Connection refused错误