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 TABLEdev=> 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 | 7428384l_partkey | 9121341l_suppkey | 621360l_linenumber | 4l_quantity | 23.0000l_extendedprice | 31323.4700l_discount | 0.0900l_tax | 0.0500l_returnflag | Rl_linestatus | Fl_shipdate | 1992-01-02l_commitdate | 1992-03-22l_receiptdate | 1992-01-03l_shipinstruct | DELIVER IN PERSONl_shipmode | FOBl_comment | haggle carefully about the furiously irAQUA性能测试
这里，咱们通过显式更改Turn On/Off进行性能差别比照。要更新AQUA配置，您能够点击[Actions]-[Configure AQUA]。
备注：
在对话框中更改Turn ON/Off并点击[Save changes]之后，集群将立刻重新启动以利用变更。
测试查问
在测试中，咱们执行以下 SIMILAR TO 与 LIKE 示例查问，并别离记录其响应工夫。
- SIMILAR TO示例查问
-- explainselect 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示例查问
-- explainselect 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 lineitemdev-> wheredev-> l_comment similar to 'slyly %' ordev-> l_comment similar to 'plant %' ordev-> l_comment similar to 'fina %' ordev-> l_comment similar to 'quick %' ordev-> l_comment similar to 'slyly %' ordev-> l_comment similar to 'quickly %' ordev-> l_comment similar to ' %about%' ordev-> l_comment similar to ' final%' ordev-> l_comment similar to ' %final%' ordev-> l_comment similar to ' breach%' ordev-> l_comment similar to ' egular%' ordev-> l_comment similar to ' %closely%' ordev-> l_comment similar to ' closely%' ordev-> l_comment similar to ' %idea%' ordev-> l_comment similar to ' idea%' ; sum | count------------------+--------- 1440371216714447 | 9496106(1 row)Time: 215896.819 msselect sum(l_orderkey), count(*)from lineitemwhere 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=> explainselect sum(l_orderkey), count(*)from lineitemwhere 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 msAQUA 已激活 (Turn On)
dev=> select sum(l_orderkey), count(*)dev-> from lineitemdev-> wheredev-> l_comment similar to 'slyly %' ordev-> l_comment similar to 'plant %' ordev-> l_comment similar to 'fina %' ordev-> l_comment similar to 'quick %' ordev-> l_comment similar to 'slyly %' ordev-> l_comment similar to 'quickly %' ordev-> l_comment similar to ' %about%' ordev-> l_comment similar to ' final%' ordev-> l_comment similar to ' %final%' ordev-> l_comment similar to ' breach%' ordev-> l_comment similar to ' egular%' ordev-> l_comment similar to ' %closely%' ordev-> l_comment similar to ' closely%' ordev-> l_comment similar to ' %idea%' ordev-> l_comment similar to ' idea%' ; sum | count------------------+--------- 1440371216714447 | 9496106(1 row)Time: 29191.625 msdev=> select sum(l_orderkey), count(*)dev-> from lineitemdev-> wheredev-> l_comment similar to 'slyly %' ordev-> l_comment similar to 'plant %' ordev-> l_comment similar to 'fina %' ordev-> l_comment similar to 'quick %' ordev-> l_comment similar to 'slyly %' ordev-> l_comment similar to 'quickly %' ordev-> l_comment similar to ' %about%' ordev-> l_comment similar to ' final%' ordev-> l_comment similar to ' %final%' ordev-> l_comment similar to ' breach%' ordev-> l_comment similar to ' egular%' ordev-> l_comment similar to ' %closely%' ordev-> l_comment similar to ' closely%' ordev-> l_comment similar to ' %idea%' ordev-> l_comment similar to ' idea%' ; sum | count------------------+--------- 1440371216714447 | 9496106(1 row)Time: 7512.982 ms以下为执行打算。
dev=> explainselect sum(l_orderkey), count(*)from lineitemwhere 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 lineitemdev-> wheredev-> l_comment like 'slyly %' ordev-> l_comment like 'plant %' ordev-> l_comment like 'fina %' ordev-> l_comment like 'quick %' ordev-> l_comment like 'slyly %' ordev-> l_comment like 'quickly %' ordev-> l_comment like ' %about%' ordev-> l_comment like ' final%' ordev-> l_comment like ' %final%' ordev-> l_comment like ' breach%' ordev-> l_comment like ' egular%' ordev-> l_comment like ' %closely%' ordev-> l_comment like ' closely%' ordev-> l_comment like ' %idea%' ordev-> l_comment like ' idea%' ; sum | count------------------+--------- 1440371216714447 | 9496106(1 row)Time: 10276.394 msdev=>dev=>dev=> select sum(l_orderkey), count(*)from lineitemwhere 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=> explainselect sum(l_orderkey), count(*)from lineitemwhere 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 msAQUA已激活 (Turn On)
dev=> select sum(l_orderkey), count(*)dev-> from lineitemdev-> wheredev-> l_comment like 'slyly %' ordev-> l_comment like 'plant %' ordev-> l_comment like 'fina %' ordev-> l_comment like 'quick %' ordev-> l_comment like 'slyly %' ordev-> l_comment like 'quickly %' ordev-> l_comment like ' %about%' ordev-> l_comment like ' final%' ordev-> l_comment like ' %final%' ordev-> l_comment like ' breach%' ordev-> l_comment like ' egular%' ordev-> l_comment like ' %closely%' ordev-> l_comment like ' closely%' ordev-> l_comment like ' %idea%' ordev-> l_comment like ' idea%' ; sum | count------------------+--------- 1440371216714447 | 9496106(1 row)Time: 11116.387 msdev=>dev=> select sum(l_orderkey), count(*)from lineitemwhere 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=> explainselect sum(l_orderkey), count(*)from lineitemwhere 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十分重要，同时也无望破除云数据仓库复杂度越来越高、咱们越来越难以了解其运作行为的窘境。和大家一样，我也殷切期待着这样一套无需了解底层工作原理、就能始终保持良好状态的业务体系。