写点什么

手把手教你调校 AWS PB 级数据仓库

  • 2019-11-20
  • 本文字数:8347 字

    阅读完需:约 27 分钟

手把手教你调校AWS PB级数据仓库

什么是一个好的数据仓库?


Redshift 是 AWS 云计算中的一个完全托管的,PB 级别规模的数据仓库服务。即使在数据量非常小的时候(比如几百个 GB 的数据)你就可以开始使用 Redshift,Redshift 集群可以随着你数据的增加而不断扩容,甚至达到 PB 级。云计算中数据仓库的优势非常明显,不需要 license,不需要预先配置非常大的数据仓库集群,扩容简单,仅仅需要为你实际所使用的数据仓库付费。


Redshift 作为一个企业级数据仓库完全支持 SQL 语法,无学习成本,支持很多种客户端连接,包括各种市场上的 BI 工具,报表以及数据分析工具。


Redshift 的概览


Redshift 通过支持大规模并行处理(MPP),列式存储,对不同列数据使用不同数据压缩算法,关系型数据仓库(SQL),灵活的扩容管理等众多优点,兼顾了数仓性能,同时也考虑学习成本及使用成本。


Redshift 系统架构及要点


图 1,Redshift 系统架构图



  • 主节点负责客户端与计算节点之间的所有通讯,编译代码并负责将编译好的代码分发给各个计算节点处理,负责分配数据到不同的计算节点,主节点对客户不可见的,无需客户管理主节点的压力,更重要的是主节点免费。

  • 计算节点是具体的干活的,并处理好的任务送给主节点进行合并后返回给客户端应用程序。每个计算节点都有自己独立的 CPU,内存以及直连存储。Redshift 集群规模大小通常就是指计算节点的个数以及计算节点机器类型。

  • 节点分片是指将计算节点被分成若干的分片,根据计算节点类型不同,每个节点包含的分片数量不同,通常 1 个 vCPU 对应一个分片,ds2 的机型除外。每个分片都会分配独立的内存及存储资源,接受来自主节点分配的任务。分片跟另外一个重要概念 Dist Key 紧密相关, 这里先提一下,接下来会具体介绍 Dist Key。

  • 排序键(Sort Key)是一个顺序键,即 Redshift 会根据这个键来将数据按顺序存储在硬盘上。Redshift 的查询优化程序(只要理解有这么个东西存在就好,客户不需要任何维护,对客户也是透明的)也会根据这个排序来进行执行查询优化计划。这是 Redshift 性能调优的一个非常重要的参数。

  • 分配键(Distribution Key)是控制加载到表的数据如何分布在各个计算节点的一个键,有好几种分布的风格,接下来会重点讲到,这是 Redshift 调优的非常重要的另外一个参数。


Redshift 的几个常用最佳实践


选择最佳排序键


  • 如果最近使用的数据查询频率最高,则指定时间戳列作为排序键的第一列;

  • 如果您经常对某列进行范围筛选或相等性筛选,则指定该列作为排序键;

  • 如果您频繁联接表,则指定联接列作为排序键和分配键;


熟悉 Redshift 的朋友可能知道可以指定多列作为排序键,而且排序键还有两种方式,组合式和交叉式。限于篇幅的原因,在接下来的调优测试中我们采用的是某一列作为排序键,如果有对其他排序键风格感兴趣的朋友,可以单独联系我们进行讨论。


选择最佳分配键


选择表分配方式的目的是通过在执行查询前将数据放在需要的位置来最大程度地减小重新分配步骤的影响,最好这个查询不需要二次移动数据。


分配键有三种风格,均匀分布(Even),键分布(Key),全分布(All),默认是均匀分布。


  • 根据共同列分配事实数据表和一个维度表;


事实数据表只能有一个分配键。任何通过其他键联接的表都不能与事实数据表并置。根据联接频率和联接行的大小选择一个要并置的维度。将维度表的主键和事实数据表对应的外键指定为 DISTKEY。


  • 根据筛选的数据集的大小选择最大的维度;


只有用于联接的行需要分配,因此需要考虑筛选后的数据集的大小,而不是表的大小。


  • 在筛选结果集中选择基数高的列;


例如,如果您在日期列上分配了一个销售表,您可能获得非常均匀的数据分配,除非您的大多数销售都是季节性的。但是,如果您通常使用范围受限谓词进行筛选以缩小日期期间的范围,则大多数筛选行将位于有限的一组切片上并且查询工作负载将偏斜。


  • 将一些维度表改为使用 ALL 分配;


如果一个维度表不能与事实数据表或其他重要的联接表并置,您可以通过将整个表分配到所有节点来大大提高查询性能。使用 ALL 分配会使存储空间需求成倍增长,并且会增加加载时间和维护操作,所以在选择 ALL 分配前应权衡所有因素。


优化 COPY,提高数据加载速度


当你将要数据加载到 Redshift 的某个表时,不要让单个输入文件过大,最好是将这些输入文件切成多份,具体数量最好是跟分片数量匹配,这样可以充分利用所有分片,配合分配键能达到最佳效果。


图 2,COPY 输入的最优方式


让 COPY 选择自动压缩


作为数据仓库,Redshift 通常会需要大量导入数据,这时使用做多的,效率最好的是 COPY 命令。在使用 COPY 时建议将 COMPUPDATE 参数设置为 ON,这样数据在加载进库时是自动压缩的,好处是可以节省存储空间,提高查询的速度,不过这会增加数据加载进表的时间,这个可以根据你的业务需求,再具体衡量。


Redshift 调优实战


测试结论


  1. 选择合适的排序键,分配键,及自动压缩对表的查询速度,存储效率很大提升。本次测试中,优化后查询速度有高达 75%的提升,存储空间节省 50%。

  2. 相同节点类型情况下,多节点性能比单节点性能提升明显。本次测试中,采用了 4 节点与单节点对比,4 节点查询速度比单节点提升 75%。

  3. 节点数量相同的情况下,dc 系列节点的查询速度比 ds 系列节点的查询速度要快。本次测试中,采用了 dc1.large 和 ds1.xlarge 两种节点类型进行对比,dc 系列节点的查询速度比 ds 系列快 20% 。

  4. 使用 JOIN 与不使用 JOIN 查询速度无明显差别。本次测试中,三个不同的查询及对应的 JOIN 查询,在查询速度上的差别非常小。这部分的详细测试结果,请参见附录一。

  5. 查询速度达到一定值时,再增加节点对查询优化的效果有限。本次测试中,在相同环境中,将节点数量从 8 个 dc1.large 节点增加到 12 个 dc1.large 节点,三个查询只有一个查询的速度有一定提升,其他 2 个查询速度基本没有太大变化。这部分的详细测试结果,请参见附录二。


图 3,调优前后性能对比图



备注:性能对比图从三个方面进行了对比,数据加载速度表存储空间查询的速度。本次测试的原始数据放在 AWS Oregon S3,Redshift 也在 Oregon 区域。


测试场景


表 1,本次测试中用到的表及表的大小



图 4,本次测试中表之间的关系



测试步骤


注意:本次测试步骤已假设 Redshift 集群已启动,且用户知道如何通过 JDBC 方式连接 Redshift 集群。


Before(不做任何优化):


  1. 创建表(不指定排序键和分配键);

  2. 加载数据(不进行自动压缩);

  3. 查询 Redshift 中各个表的存储空间;

  4. 执行三种不同查询,均取第 2 次查询所耗时间;

  5. 相同条件,使用 JOIN 查询所耗时间;


After(指定排序键和分配键,加载数据时进行了自动压缩):


  1. 删除表;

  2. 创建表(指定排序键和分配键);

  3. 加载数据(根据不同数据类型选择合适的压缩算法);

  4. 查询 Redshift 中各个表的存储空间;

  5. 执行三种不同查询,均取第 2 次查询所耗时间;

  6. 相同条件,使用 JOIN 查询所耗时间;


测试截图


图 5,单个节点(ds1.xlarge)的数据加载时间(优化前)



图 6,单个节点(ds1.xlarge)的数据加载时间(优化后)



图 7,单个节点(ds1.xlarge)的数据存储空间(优化前)



图 8,单个节点(ds1.xlarge)的数据存储空间(优化后)



图 9,单个节点(ds1.xlarge)的查询时间(优化前)



图 10,单个节点(ds1.xlarge)的查询时间(优化后)



图 11,4 个节点(ds1.xlarge)的数据加载时间(优化前)



图 12,4 个节点(ds1.xlarge)的数据加载时间(优化后)



图 13,4 个节点(ds1.xlarge)的数据存储空间(优化前)



图 14,4 个节点(ds1.xlarge)的数据存储空间(优化后)



图 15,4 个节点(ds1.xlarge)的查询时间 (优化前)



图 16,4 个节点(ds1.xlarge)的查询时间 (优化后)



图 17,4 个节点(dc1.large)的数据加载时间 (优化前)



图 18,4 个节点(dc1.large)的数据加载时间 (优化后)



图 19,4 个节点(dc1.large)的数据存储空间 (优化前)



图 20,4 个节点(dc1.large)的数据存储空间 (优化后)



图 21,4 个节点(dc1.large)的查询时间 (优化前)



图 22,4 个节点(dc1.large)的查询时间 (优化后)



本次测试中用到的命令参数


Before (优化前)


CREATE TABLE part


(


p_partkey INTEGER NOT NULL,


p_name VARCHAR(22) NOT NULL,


p_mfgr VARCHAR(6) NOT NULL,


p_category VARCHAR(7) NOT NULL,


p_brand1 VARCHAR(9) NOT NULL,


p_color VARCHAR(11) NOT NULL,


p_type VARCHAR(25) NOT NULL,


p_size INTEGER NOT NULL,


p_container VARCHAR(10) NOT NULL


);


CREATE TABLE supplier


(


s_suppkey INTEGER NOT NULL,


s_name VARCHAR(25) NOT NULL,


s_address VARCHAR(25) NOT NULL,


s_city VARCHAR(10) NOT NULL,


s_nation VARCHAR(15) NOT NULL,


s_region VARCHAR(12) NOT NULL,


s_phone VARCHAR(15) NOT NULL


);


CREATE TABLE customer


(


c_custkey INTEGER NOT NULL,


c_name VARCHAR(25) NOT NULL,


c_address VARCHAR(25) NOT NULL,


c_city VARCHAR(10) NOT NULL,


c_nation VARCHAR(15) NOT NULL,


c_region VARCHAR(12) NOT NULL,


c_phone VARCHAR(15) NOT NULL,


c_mktsegment VARCHAR(10) NOT NULL


);


CREATE TABLE dwdate


(


d_datekey INTEGER NOT NULL,


d_date VARCHAR(19) NOT NULL,


d_dayofweek VARCHAR(10) NOT NULL,


d_month VARCHAR(10) NOT NULL,


d_year INTEGER NOT NULL,


d_yearmonthnum INTEGER NOT NULL,


d_yearmonth VARCHAR(8) NOT NULL,


d_daynuminweek INTEGER NOT NULL,


d_daynuminmonth INTEGER NOT NULL,


d_daynuminyear INTEGER NOT NULL,


d_monthnuminyear INTEGER NOT NULL,


d_weeknuminyear INTEGER NOT NULL,


d_sellingseason VARCHAR(13) NOT NULL,


d_lastdayinweekfl VARCHAR(1) NOT NULL,


d_lastdayinmonthfl VARCHAR(1) NOT NULL,


d_holidayfl VARCHAR(1) NOT NULL,


d_weekdayfl VARCHAR(1) NOT NULL


);


CREATE TABLE lineorder


(


lo_orderkey INTEGER NOT NULL,


lo_linenumber INTEGER NOT NULL,


lo_custkey INTEGER NOT NULL,


lo_partkey INTEGER NOT NULL,


lo_suppkey INTEGER NOT NULL,


lo_orderdate INTEGER NOT NULL,


lo_orderpriority VARCHAR(15) NOT NULL,


lo_shippriority VARCHAR(1) NOT NULL,


lo_quantity INTEGER NOT NULL,


lo_extendedprice INTEGER NOT NULL,


lo_ordertotalprice INTEGER NOT NULL,


lo_discount INTEGER NOT NULL,


lo_revenue INTEGER NOT NULL,


lo_supplycost INTEGER NOT NULL,


lo_tax INTEGER NOT NULL,


lo_commitdate INTEGER NOT NULL,


lo_shipmode VARCHAR(10) NOT NULL


);


copy customer from ‘s3://lyz/redshift/customer’


credentials ‘aws_access_key_id= your-key;aws_secret_access_key=your-secret-key’


gzip compupdate off region ‘us-west-2’;


copy dwdate from ‘s3://lyz/redshift/dwdate’


credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘


gzip compupdate off region ‘us-west-2’;


copy lineorder from ‘s3://lyz/redshift/lineorder’


credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘


gzip compupdate off region ‘us-west-2’;


copy part from ‘s3://lyz/redshift/part’


credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘


gzip compupdate off region ‘us-west-2’;


copy supplier from ‘s3://lyz/redshift/supplier’


credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘


gzip compupdate off region ‘us-west-2’;


select count(*) from LINEORDER;


select count(*) from PART;


select count(*) from CUSTOMER;


select count(*) from SUPPLIER;


select count(*) from DWDATE;


select stv_tbl_perm.name as table, count(*) as mb


from stv_blocklist, stv_tbl_perm


where stv_blocklist.tbl = stv_tbl_perm.id


and stv_blocklist.slice = stv_tbl_perm.slice


and stv_tbl_perm.name in (‘lineorder’,’part’,’customer’,’dwdate’,’supplier’)


group by stv_tbl_perm.name


order by 1 asc;


— Query 1


— Restrictions on only one dimension.


select sum(lo_extendedprice*lo_discount) as revenue


from lineorder, dwdate


where lo_orderdate = d_datekey


and d_year = 1997


and lo_discount between 1 and 3


and lo_quantity < 24;


— Query 2


— Restrictions on two dimensions


select sum(lo_revenue), d_year, p_brand1


from lineorder, dwdate, part, supplier


where lo_orderdate = d_datekey


and lo_partkey = p_partkey


and lo_suppkey = s_suppkey


and p_category = ‘MFGR#12’


and s_region = ‘AMERICA’


group by d_year, p_brand1


order by d_year, p_brand1;


— Query 3


— Drill down in time to just one month


select c_city, s_city, d_year, sum(lo_revenue) as revenue


from customer, lineorder, supplier, dwdate


where lo_custkey = c_custkey


and lo_suppkey = s_suppkey


and lo_orderdate = d_datekey


and (c_city=’UNITED KI1′ or


c_city=’UNITED KI5′)


and (s_city=’UNITED KI1′ or


s_city=’UNITED KI5′)


and d_yearmonth = ‘Dec1997’


group by c_city, s_city, d_year


order by d_year asc, revenue desc;


After(优化后):


drop table part cascade;


drop table supplier cascade;


drop table customer cascade;


drop table dwdate cascade;


drop table lineorder cascade;


CREATE TABLE part (


p_partkey integer not null sortkey distkey,


p_name varchar(22) not null,


p_mfgr varchar(6) not null,


p_category varchar(7) not null,


p_brand1 varchar(9) not null,


p_color varchar(11) not null,


p_type varchar(25) not null,


p_size integer not null,


p_container varchar(10) not null


);


CREATE TABLE supplier (


s_suppkey integer not null sortkey,


s_name varchar(25) not null,


s_address varchar(25) not null,


s_city varchar(10) not null,


s_nation varchar(15) not null,


s_region varchar(12) not null,


s_phone varchar(15) not null)


diststyle all;


CREATE TABLE customer (


c_custkey integer not null sortkey,


c_name varchar(25) not null,


c_address varchar(25) not null,


c_city varchar(10) not null,


c_nation varchar(15) not null,


c_region varchar(12) not null,


c_phone varchar(15) not null,


c_mktsegment varchar(10) not null)


diststyle all;


CREATE TABLE dwdate (


d_datekey integer not null sortkey,


d_date varchar(19) not null,


d_dayofweek varchar(10) not null,


d_month varchar(10) not null,


d_year integer not null,


d_yearmonthnum integer not null,


d_yearmonth varchar(8) not null,


d_daynuminweek integer not null,


d_daynuminmonth integer not null,


d_daynuminyear integer not null,


d_monthnuminyear integer not null,


d_weeknuminyear integer not null,


d_sellingseason varchar(13) not null,


d_lastdayinweekfl varchar(1) not null,


d_lastdayinmonthfl varchar(1) not null,


d_holidayfl varchar(1) not null,


d_weekdayfl varchar(1) not null)


diststyle all;


CREATE TABLE lineorder (


lo_orderkey integer not null,


lo_linenumber integer not null,


lo_custkey integer not null,


lo_partkey integer not null distkey,


lo_suppkey integer not null,


lo_orderdate integer not null sortkey,


lo_orderpriority varchar(15) not null,


lo_shippriority varchar(1) not null,


lo_quantity integer not null,


lo_extendedprice integer not null,


lo_ordertotalprice integer not null,


lo_discount integer not null,


lo_revenue integer not null,


lo_supplycost integer not null,


lo_tax integer not null,


lo_commitdate integer not null,


lo_shipmode varchar(10) not null


);


copy customer from ‘s3://lyz/redshift/customer’


credentials ‘aws_access_key_id=your-key;aws_secret_access_key=your-secret-key’


gzip region ‘us-west-2’;


copy dwdate from ‘s3://lyz/redshift/dwdate’


credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘


gzip region ‘us-west-2’;


copy lineorder from ‘s3://lyz/redshift/lineorder’


credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘


gzip region ‘us-west-2’;


copy part from ‘s3://lyz/redshift/part’


credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘


gzip region ‘us-west-2’;


copy supplier from ‘s3://lyz/redshift/supplier’


credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘


gzip region ‘us-west-2’;


select stv_tbl_perm.name as table, count(*) as mb


from stv_blocklist, stv_tbl_perm


where stv_blocklist.tbl = stv_tbl_perm.id


and stv_blocklist.slice = stv_tbl_perm.slice


and stv_tbl_perm.name in (‘lineorder’,’part’,’customer’,’dwdate’,’supplier’)


group by stv_tbl_perm.name


order by 1 asc;


— Query 1


— Restrictions on only one dimension.


select sum(lo_extendedprice*lo_discount) as revenue


from lineorder, dwdate


where lo_orderdate = d_datekey


and d_year = 1997


and lo_discount between 1 and 3


and lo_quantity < 24;


— Query 2


— Restrictions on two dimensions


select sum(lo_revenue), d_year, p_brand1


from lineorder, dwdate, part, supplier


where lo_orderdate = d_datekey


and lo_partkey = p_partkey


and lo_suppkey = s_suppkey


and p_category = ‘MFGR#12’


and s_region = ‘AMERICA’


group by d_year, p_brand1


order by d_year, p_brand1;


— Query 3


— Drill down in time to just one month


select c_city, s_city, d_year, sum(lo_revenue) as revenue


from customer, lineorder, supplier, dwdate


where lo_custkey = c_custkey


and lo_suppkey = s_suppkey


and lo_orderdate = d_datekey


and (c_city=’UNITED KI1′ or


c_city=’UNITED KI5′)


and (s_city=’UNITED KI1′ or


s_city=’UNITED KI5′)


and d_yearmonth = ‘Dec1997’


group by c_city, s_city, d_year


order by d_year asc, revenue desc;


附录一


图 23,查询 1 所耗时间,8 节点(dc1.large)



图 24,查询 1 使用 JOIN 所耗时间,8 节点(dc1.large)



图 25,查询 2 所耗时间,8 节点(dc1.large)



图 26,查询 2 使用 JOIN 所耗时间,8 节点(dc1.large)



图 27,查询 3 所耗时间,8 节点(dc1.large)



图 28,查询 3 使用 JOIN 所耗时间,8 节点(dc1.large)



附录二


图 29,查询 1 所耗时间,12 节点(dc1.large)



图 30,查询 2 所耗时间,12 节点(dc1.large)



图 31,查询 3 所耗时间,12 节点(dc1.large)



作者介绍:



郑进佳


亚马逊 AWS 解决方案架构师,在加入 AWS 之前,在多家跨国公司有着超过 7 年的架构设计和项目管理的经验,对 AWS 云端高可用架构有着深刻的理解,以及对企业级应用如何迁移到云端的架构设计有实战方面的经验。


本文转载自 AWS 技术博客。


原文链接:


https://amazonaws-china.com/cn/blogs/china/dw-redshift/


2019-11-20 08:00804

评论

发布
暂无评论
发现更多内容

MySQL基础笔记

楠羽

#开源

十年沉浮,Web2 到 Web3 的转变之路

TinTinLand

区块链 开发者 就业 web3 hackers web3

Spark Optimizer 规则下的 BUG 排查与修复全记录

观远数据

saprk

招行架构师徐佳航:金融云原生与开源标准的共同生长

阿里巴巴云原生

阿里云 开源 容器 云原生 KubeVela

Java更改 PDF 页面大小

在下毛毛雨

MobTech MobLink Web端快速集成指南

MobTech袤博科技

JavaScript 集成 跳转访问网页

Python 教程之输入输出(10)—— 输出格式

海拥(haiyong.site)

Python 8月月更

腾讯云数据安全中台保护方案获“首届全国商用密码应用优秀案例”

腾讯安全云鼎实验室

云安全

Shuttle + Alluxio 加速内存Shuffle起飞

Alluxio

开源 OPPO Alluxio spark SQL 8月月更

浅聊一下邮件通知的批量推送

为自己带盐

dotnetcore 8月月更 邮件发送

C#/VB.NET:删除PDF文档中的页面

Geek_249eec

C# VB.NET 删除PDF页面

「Java」本地文件上传下载预览

价投小邱

文件 下载 文件上传 #java

东莞等保测评多少钱及格?哪里可以做等保测评?

行云管家

等保 等级保护 等保测评 安全等级保护

如果重新学计算机

价投小邱

Linux 计算机网络 操作系统

拒绝加班:巧用前端电子表格中构建公式树

葡萄城技术团队

《中国车联网TSP行业发展洞察2022》案例征集

易观分析

车联网 案例征集

ARM 内核寄存器 和 基本汇编语言讲解

矜辰所致

汇编语言 8月月更 ARM内核 内核寄存器

追一科技携手华为云助力中小企业数字化,Face虚拟数字人亮相828企业节

sofiya

Python自学笔记6-列表有哪些常用操作

和牛

Python 测试 8月月更

快照有哪几种意思?如何统一管理各云主机快照,实现快速配置与回滚?

行云管家

运维 云主机 快照 IT运维

元宇宙的未来商机和应用

智捷云

商派oneX新零售系统上架华为云云商店,首次参与“828企业节”

神奇视野

“中国建筑业竞争力百强”中化二建如何做知识管理?

sofiya

SPL:跑批有这么难么

华为云开发者联盟

Java 开发

在公司内部,做了一次 HTTP(S) 的分享

程序员小毕

程序员 面试 程序人生 https 计算机网络

高性能计算在处理和吞吐量方面的重大突破

Finovy Cloud

计算 高性能计算架构

TDengine3.0 新架构设计思路

TDengine

数据库 tdengine 时序数据库

KubeEdge:下一代云原生边缘设备管理标准DMI的设计与实现

华为云开发者联盟

云计算 云原生 后端

一次纯线上接口异常的排查过程

南城FE

node.js 前端

喜报 | 博睿数据两项发明专利获得国家知识产权局授权,累计发明专利11项

博睿数据

APM 可观测性 博睿数据 智能运维AIOps 发明专利

兆骑科创承办创业赛事活动,双创服务,创业服务平台

兆骑科创凤阁

手把手教你调校AWS PB级数据仓库_其他_亚马逊云科技 (Amazon Web Services)_InfoQ精选文章