实现更高性能，一起探索 Amazon Redshift 高级查询加速器
- 2021 年 12 月 20 日
本文字数：12835 字
阅读完需：约 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 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.COPY
dev=> 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 ir复制代码
AQUA 性能测试
这里，我们通过显式更改 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 ms
select 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 ms复制代码
AQUA 已激活 (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 ms
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: 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 ms复制代码
AQUA 已激活 (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 非常重要，同时也有望破除云数据仓库复杂度越来越高、我们越来越难以理解其运作行为的困境。和大家一样，我也殷切期待着这样一套无需理解底层工作原理、就能始终保持良好状态的业务体系。
划线
评论
复制
发布于: 21 小时前阅读数: 6
还未添加个人签名 2019.09.17 加入
还未添加个人简介
评论