PowerPoint Presentation

Size: px

Start display at page:

Download "PowerPoint Presentation"

骑治黎
5 years ago
Views:

1 Amazon Redshift 最佳实践基于 SQL 的列式数据仓库管理系统韩思捷,AWS 技术支持经理 Sijie Han, Technical Account Manager, Amazon Web Services

2 此在线研讨会的学习内容 Amazon Redshift 服务概述客户示例迁移到 Amazon Redshift 的最佳实践 Amazon Redshift schema 设计的最佳实践 Amazon Redshift 数据加载的最佳实践 Amazon Redshift 性能调优的最佳实践 Q & A

3 Amazon Redshift 服务概述

4 关系型数据仓库快速简单便宜大规模并行处理 ; 基于 PB 级的扩容 Amazon Redshift 完全托管包含 HDD 和 SSD 两种平台每年 $1,000/TB; 从 $0.25/ 小时开始计费

5 使用 Amazon Redshift 的部分客户清单

6 Amazon Redshift 的系统架构主节点计算节点执行 SQL 语句的入口存放元数据协调查询语句的执行基于本地以及列式存储并行执行查询通过 Amazon S3 加载, 备份和恢复数据 ; 从 Amazon DynamoDB,Amazon EMR 或者 SSH 进行数据加载 10 GigE (HPC) JDBC/ODBC 两种硬件平台针对数据处理进行了优化 DS2: HDD; 可以从 2 TB 扩展到 2 PB DC1: SSD; 可以从 160 GB 扩展到 326 TB Ingestion Backup Restore

7 计算节点架构每个节点包含多个分片 DS2 XL 上有 2 个分片, 8 XL 上有 16 个分片 DC1 L 上有 2 个分片, 8 XL 上有 32 个分片 Leader Node 每个分片都会分配 CPU 并管理表数据每个分片并行的处理部分工作负载

Amazon Redshift 极大的减少了 I/O ID Age State Amount 数据压缩区域映射 123 20 CA 500 345 25 WA 250

8 Amazon Redshift 极大的减少了 I/O ID Age State Amount 数据压缩区域映射 CA WA FL WA 375 ID Age State Amount 行式存储中计算 SUM(Amount) : 需要扫描所有列的数据不必要的 I/O

9 Amazon Redshift 极大的减少了 I/O ID Age State Amount 数据压缩区域映射 CA WA FL WA 375 ID Age State Amount 行式存储中计算 SUM(Amount) : 只需要扫描必要的数据块

10 Amazon Redshift 极大的减少了 I/O 列式存储数据压缩区域映射 analyze compression orders; Table Column Encoding orders id mostly32 orders age mostly32 orders state lzo orders amount mostly32 ID Age State Amount 基于列的压缩高效减少存储空间减少 I/O

11 Amazon Redshift 极大的减少了 I/O 列式存储数据压缩区域映射位于内存里的数据块元数据记录了每个数据块里的最小值和最大值对于查询语句来说, 有效的去除了不会包含请求数据的数据块最小化不必要的 I/O 请求基于排序键继续宁过滤的时候最为高效 ID Age State Amount

12 用户示例

13 分析 Twitter 的数据

14 分析 Twitter 的数据 Amazon Glacier $0.010/GB-Mo Amazon Redshift Starts at $0.25/hour S3 $0.030/GB-Mo Amazon Kinesis $0.015/shard 1MB/s in; 2MB/out $0.028/million puts EC2 Starts at $0.02/hour

15 分析 Twitter 的数据所需要的成本 500MM tweets/day = ~ 5,800 tweets/sec 2k/tweet is ~12MB/sec (~1TB/day) $0.015/hour per shard, $0.028/million PUTS Amazon Kinesis 的成本为 $0.765/hour Amazon Redshift 的成本为 $0.850/hour ( 使用 2TB 的计算节点 ) S3 的成本为 $1.28/hour ( 非压缩 ) 总共 : $2.895/hour

16 NTT Docomo Mobile 使用分析 S3 很多手机基站会产生 PB 级别的数据 Data Source ET State Management Forwarder Loader 很难扩展, 并且昂贵 Client Direct Connect Redshift Sandbox 需要一个安全的, 可伸缩的系统, 并且可以在本地数据中心里运行

17 Sushiro 实时数据流分析使用 Amazon Kinesis 进行实时数据的捕获, 并在 Amazon Redshift 里进行分析 380 家门店的实时数据 Amazon 近实时的处理库存量以及消费用量预测门店的需求量, 使得食物的浪费最少, 并且提高效率

18 euclid 零售和 POS 系统分析 - 每小时处理 10TB 的数据, 而之前需要 2 星期 - 成本下降 80-90%

19 迁移到 Amazon Redshift 的最佳实践

20 迁移到 Amazon Redshift 之前的重点考虑内容并发查询进程数量应该控制在 8-12 个, 最多为 50 个表结构设计非常重要, 不能使用索引 DISTKEY 和 SORTKEY 对性能影响很大可以使用 PRIMARY KEY, FOREIGN KEY, UNIQUE 这些约束注意 : 这些约束并不是强制的压缩可以提高查询性能

21 迁移到 Amazon Redshift 之前的重点考虑内容对于大的写操作进行优化如果数据是基于时间进行生成, 则对不同时间范围的数据创建不同的表, 并通过 UNION ALL 视图进行合并查询

22 Amazon Redshift schema 设计的最佳实践

23 数据分布方式均匀的分布数据, 以便进行并行处理在计算节点之间, 最小化数据的传递相邻分片之间的数据关联在本地进行汇总计算 Distribution key 相同的 key 值的数据放在同一个分片上 All 在每个节点的第一个分片上都会有完整的表的数据 Even 轮询方式进行数据分布 Slice 1 Slice 2 Slice 3 Slice 4 Slice 1 Slice 2 Slice 3 Slice 4 Slice 1 Slice 2 Slice 3 Slice 4 Node 1 Node 2 Node 1 Node 2 Node 1 Node 2

24 选择合适的分布方式 Key Even 大型的事实表在关联中使用经常变化的表在聚合运算中进行本地化操作的列表不经常进行关联或汇总操作大表同时其中的列不会用于 where 条件 All 表的数据不太变化相对较合理的大小尺寸 ( 比如几百万行但是不超过一亿条数据 ) 在关联操作中没有经常使用的关联字段典型的使用场景 : 对维度表进行关联, 但是在关联中没有统一的关联列

25 分布不均衡表的定义 CREATE TABLE test (id INT, customer CHAR(255) DISTKEY, createdate TIMESTAMP ); 如何解决修改分布键, 使其为一个分布更加均匀的字段或者数据分布方式修改为 EVEN 重新分布 vs 数据分布不均匀当前状态 10 亿条数据 90% 的记录中 customer 字段的值为 abc.com 导致 90% 的数据都落在同一个节点上该特定的分片会比其他节点上的分片都要忙可能会导致 OOM 和 Disk Full 错误

26 检查表是如何在分片里进行分布的 select trim(name) as table, stv_blocklist.slice, stv_tbl_perm.rows from stv_blocklist,stv_tbl_perm where stv_blocklist.tbl=stv_tbl_perm.id and stv_tbl_perm.slice=stv_blocklist.slice and stv_blocklist.id > and name not like '%#m%' and name not like 'systable%' group by name, stv_blocklist.slice, stv_tbl_perm.rows order by 3 desc;

27 数据排序数据行在表的数据块里以有序方式进行保存数据进入分片时, 按照指定的排序键进行数据排序表会同时包含排序的区域和非排序的区域通过利用区域映射功能来跳过一些不需要扫描的数据块对于经常用来过滤的字段定义排序键如果未排序的区域不断增加, 会导致性能下降当数据加载到一个空的表的时候, 会进行排序定义 SORTKEY 的时候需要考虑 : 查询模式数据格式业务需求 VACUUM [SORT ONLY]

28 选择 SORTKEY 主要参考出现在 where 条件里的字段 ( 日期, ID, ) 或者, 选择一个经常用于聚合运算的字段或者, 选择与分布键同样的字段 COMPOUND 最常见定义良好的过滤条件基于时间的数据 INTERLEAVED 大表 ( 超过 10 亿条记录 ) 没有固定的过滤条件没有基于时间的数据

29 Compound 类型的排序键 cust_id prod_id other columns blocks cust_id prod_id [1,1] [1,2] [1,3] [1,4] 2 [2,1] [2,2] [2,3] [2,4] 3 [3,1] [3,2] [3,3] [3,4] 4 [4,1] [4,2] [4,3] [4,4] Select sum(amt) From big_tab Where cust_id = (1234); Select sum(amt) From big_tab Where prod_id = (5678); 数据行保存在 Amazon Redshift 的数据块里假设一个 block 里放了四条记录对于一个特定的 cust_id 值的数据行, 全都放在一个数据块里不过对于一个特定的 prod_id 的数据行, 则分布在四个数据块里

Interleaved 类型的排序键 cust_id prod_id other columns blocks 1 1 2 2 1 1 2 2 3 1 2 1 2 3 4 3 4 1 cust_id prod_id 1 2 3 4 1 [1,1] [1,2] [1,3] [1,4] 2 [2,1] [2,2] [2,3] [2,4] 3

30 Interleaved 类型的排序键 cust_id prod_id other columns blocks cust_id prod_id [1,1] [1,2] [1,3] [1,4] 2 [2,1] [2,2] [2,3] [2,4] 3 [3,1] [3,2] [3,3] [3,4] 对于同一个特定的 cust_id 值的数据行, 分布在两个数据块里对于同一个特定的 prod_id 值的数据行, 分布在两个数据块里 [4,1] [4,2] [4,3] [4,4] 对于这两个 key 值来说, 使用相同的方式进行排序

31 压缩数据在加载数据到一个空的表的时候,COPY 命令会自动分析并压缩数据 ANALYZE COMPRESSION 命令检查已经存在的表, 并未每个字段提供一个建议的压缩算法如果修改了某个字段的压缩算法, 则需要重建该表

32 大部分情况下自动压缩都是一个推荐的做法如果你在一个定期运行的 ETL 流程, 并且你使用的是临时表或者暂存表, 则关闭自动压缩 : 使用 ANALYZE COMPRESSION 命令来确定正确的压缩方式在你的 DDL 里添加这些压缩方式使用 CREATE TABLE LIKE 命令重建表通过区域映射我们可以知道 : 每个数据块包含的数据范围在一个数据块里应该扫描哪些数据行

33 测试压缩效率获得建议的压缩方式 : ANALYZE COMPRESSION <table_name> 使用建议的压缩方式创建表 : CREATE TABLE encodingcolumns ( columnraw varchar(100) encode raw, columnbytedict varchar(100) encode bytedict, columnlzo varchar(100) encode lzo); 检查每个列所使用的数据块的个数 : select col, count(*) 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 = 'your_table_name' group by col order by col;

34 定义约束 Amazon Redshift 并不会强制约束用来作为生成执行计划的时候的提示信息比如查询执行器会在计算某些特定的统计信息的时候使用主键和外键约束假定加载的数据都没有违反约束的要求在加载数据到集群之前, 要先对数据进行约束方面的检查, 确保它们都没有违反约束的定义 NOT NULL 约束是强制的注意 : 如果加载到数据库的数据违反了约束的要求, 则可能会导致错误的查询结果

35 Amazon Redshift 数据加载的最佳实践

36 Amazon Redshift 加载数据概述 Source DBs AWS Direct Connect VPN Connection Amazon RDS Data volume Amazon Glacier Amazon EC2 or on-premises (using SSH) Amazon DynamoDB Amazon S3 logs/files Amazon S3 Multipart Upload Corporate data center AWS Import/ Export Amazon EMR Amazon Redshift AWS Cloud

37 并行加载文件每个分片一次可以加载一个文件 : 流式解压解析分布写入如果只有一个文件需要被加载, 则说明只有一个分片在工作这时只有 6.25% 的分片是活跃状态

38 使用多个文件来最大化吞吐量确保需要加载的文件个数至少和集群里的分片个数一致在具有 16 个加载文件的时候, 所有的分片都在工作, 因此你可以最大化吞吐量随着你添加新的计算节点到集群里, 在执行 COPY 命令的时候可以线性的扩展吞吐量

39 Amazon Redshift 性能调优的最佳实践

40 对查询进行优化定期检查你的表的状态定期执行 vacuum 和 analyze 命令检查 SVV_TABLE_INFO 表缺少统计信息表里的数据出现分布不均衡检查 skew_rows 列, 该值表示不同的分片之间所包含的最多的数据行数除以最少的数据行数后得到的值比如如果该字段的值为 2.0, 则表示一个分片里的数据包含的数据行数是另一个分片里的数据行数的 2 倍要解决该问题, 则需要使用另一个分布键来重建表, 或者修改分布方式未压缩的字段未排序的数据检查你的集群的状态 WLM 队列提交队列数据库锁

41 缺失统计信息 Amazon Redshift 查询优化器会依赖于最新的统计信息某个表上的统计信息只有在你访问这张表的时候才会被使用对下面的字段的统计信息尤其重要 : SORTKEY DISTKEY 在 where 条件里出现的字段定期运行命令 : analyze <tablename> (col1, col2)

42 表维护和状态表的数据分布不均匀工作负载不均衡只有当最慢的分片上的工作负载结束, 整个查询才能结束还可能导致出错 : 临时数据保存在单个节点上, 导致该节点的空间耗尽, 从而导致查询失败未排序的数据使用 VACUUM 或 DEEP COPY 命令把表的数据进行排序对于未排序的表进行扫描的时候, 仍然可以通过区域映射来提高效率

43 维护排序键借助 SVV_TABLE_INFO 表检查未排序的数据行占表的总记录数的百分比这个百分比越高, 则越需要运行 VACUUM 命令通常在加载完数据以后建议运行 VACUUM 命令, 除非被加载的数据已经按照排序键进行排序了, 则不需要运行 VACUUM 命令通过 skew_sortkey1 字段, 检查排序键是否过度压缩 : 导致性能问题只能通过 Deep Copy 进行调整

44 集群状态 : 提交和 WLM WLM 队列管理确定短时间运行和长时间运行的查询并进行优先级排列定义多个队列来让不同的查询语句使用缺省的并发度为 5 提交队列你的提交队列有多长? 确定不需要的事务把相关的语句组织为一个事务检查 STL_COMMIT_STATS

45 影响性能的各个因素节点个数以及分片的个数节点类型数据分布方式数据排序数据集的大小尺寸并发操作查询语句

46 Thank You!

水晶分析师

水晶分析师大数据时代的挑战产品定位体系架构功能特点大数据处理平台行业大数据应用 IT 基础设施数据源 Hadoop Yarn 终端统一管理和监控中心(Deploy,Configure,monitor,Manage) Master Servers TRS CRYSTAL MPP Flat Files Applications&DBs ETL&DI Products 技术指标 1 TRS