手把手教你调校 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/