亚马逊AWS官方博客

手把手教你调校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云端高可用架构有着深刻的理解,以及对企业级应用如何迁移到云端的架构设计有实战方面的经验。