(function() { var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true; ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s); })();

理解SSIS中的Pipeline

Categories: SQL, SSIS, 未分类
Tags: No Tags
Comments: No Comments
Published on: 2011 年 01 月 13 日

自从SQL 2000以后在SQL Server上SSIS就成了一个非常重要的产品。相比DTS,最大的变化不是在那些转换的控件上,而是运行体系,清晰的运行方式让开发人员可以更加深入的理解任务和数据的关系,能够将整个数据集成的项目清晰的分割成不同的阶段和任务。新架构下产生了Data Flow和Control Flow的概念。这次就重点聊聊Data Flow,希望读者们能慢慢理解其中之精髓,不明白的可以留言。(通常情况下我太愿意贴图,因为之前就没学会怎么搞图片)

Data Flow中文就是数据流,实际上Data Flow数据Control Flow中的一个任务。一个SSIS包中可以没有Data Flow但是不能没有Control Flow,例如SQL Server中的维护任务就可以是一个SSIS的包,用来备份数据或者维护索引,这种包就没有Data Flow。Data Flow主要适用于处理数据,在Data Flow中,数据从源读取写入目标,中间可以根据数据要求进行转换(结构,内容或类型)。数据从源到目的过程中,完全都是在内存中进行转换,也就是说一旦数据进到了Data Flow,在硬件上就只与内存、CPU缓存及CPU打交道了。Data Flow是依照Pipeline模式进行设计的,也就是说数据操作可以理解为逐行处理。当然数据读取和写入还是和这些数据源或目标有关系,可能也会有批的操作,但是转换上只能在内存里面逐条进行(部分操作除外,后面会提到)。如果是Pipeline模式,硬件上就需要有更快的CPU及内存的频率,这些对于性能来说很重要。但是如果不做特殊的设计和处理,系统很容易达到一个瓶颈,因为每一个pipeline只能够理由一个线程来操作,无论你的服务器有多少个核,只能发挥1个核的作用。当你发现其他的子系统压力也不大,但是SSIS转换的速率却不高的时候,基本上都是这种问题所致。

另外在由于数据通过pipeline中进行流动,如果有一个转换将所有的数据堵在内存里将极大的影响SSIS运行的效率,而且如果数据量非常大的时候可能会把内存耗尽,就像北京堵车一样,没有事故还可以,有了事故就全线飘红。SSIS可没有TempDB,物理内存没了就是没了。这种情况基本上都是在聚合组件和排序组件上,所以要尽量避免使用他们,除非万不得已,否则小心使用。

这个问题我觉得对于一般的开发人员来说不是很好解决,如果你对数据库及SSIS没有很深理解的话,基本上很难,而且这个问题是无法通过升级硬件来有效解决的(目前CPU的发展并不是高主频,而是更多核)。这个问题只能通过设计来解决,但是这可是一门学问,涉及的领域就是并行处理。并行处理需要考虑很多问题,包括数据源和数据目标的设计,控制流设计等。通过这些巧妙的设计,让我们的包能够通过更多的线程来运行更多的pipeline,当然这个更多2字并不是代表无穷多,因为当线程数量达到一定规模的时候,整个系统的瓶颈可能就会出现在其他的子系统上了,多数情况可能在网络IO或者磁盘IO上。

设计并行的SSIS包比较复杂,这里我给大家介绍一下RRD(Round-Robin Distributor)。这个组件目前还没有发布,但是可以讲讲这个组件的实现方式。实际上这个组件就是将输入pipeline根据你的data flow下层引出的执行线程数量动态的分发数据,这个功能类似于多播,只不过多播里面分发的数据是相同的,对同一条数据复制了多份并产生多个执行的线程,RRD上分发的数据是不同的。这样如果结合上适当的数据源就可以在data flow中实现并行。如果哪位在项目中需要使用可以联系我。

最近在和SSIS产品组交流的时候,我也提了一些建议,主要都是和性能相关,他们也应该与时俱进一下,让用户能够更好的利用一下多核。

TechEd 2010-如何构建企业级ETL 后续补充

Categories: SQL, SSIS
Tags: ,
Comments: 1 Comment
Published on: 2010 年 12 月 16 日

在TechEd 2010上讲了一门如何构建企业级ETL的Session,可能是因为技术方面的东西太多,没有什么太炫的Slides和Demo,听众反映一般,8.08分不太高,这里在把东西解释解释。

当时主要是因为工作比较忙,准备PPT和Demo就用了1天半的时间,确实有点仓促了,但是内容上感觉还是比较丰满的,主要目的就是给听众一个理念上的引导。

目前在企业里面用SSIS的客户还是比较多,但是大多数人还仅仅停留在如何使用SSIS进行转换,很多学习的教程也都是写那些控件的使用方法。实际上工程项目中遇到的一些实际问题是不能通过简单的控件来解决的,必须要有巧妙的设计,这个道理就像写程序一样,否则也就不会有设计模式这个领域了。

SSIS中提供了一个扩展性很强的框架,Control Flow&Data Flow,具体概念我就不多说了,有人反映我讲课的时候经常重复这些东西,其实也不是重复,只是很多人没有深入的理解。我们的设计其实很多地方都会在Control Flow里面进行体现,所以要有好的设计,就必须先理解Control Flow。除了理解Control Flow外,对于Parameter和Configuration的灵活应用也非常重要,这些都是在进行控制的过程中必不可少的连接技术,没有这些东西也无法把不同功能的包和流程组织在一起。另外一个重要的功能就是Script,Script是整个包设计里面的灵魂,很多动态功能都要依赖于此,而且Script中写的不再是vbScript和VB.net,我们可以写C#了,功能上丰富多了,尤其是对象参数和C#的结合。我们可以设置任何Object对象参数,在Script里面就意味着我们可以传递更丰富的内容,而不仅仅局限在基本数据类型,例如int,string等,我们可以传递类似于array,list等更高级的参数类型,提供更灵活的调用。有了Parameter,Configuration,Script,我们可以借助于一些容器控件来实现更多功能,具体能实现什么,就看你想怎么设计了,至少SSIS已经提供了这个框架。

另外对于企业来说SSIS的性能也是需要特殊注意,因为SSIS操作过程中对于系统整体来说都会有短时的压力,需要在规定的时间窗口内完成ETL的过程,否则可能会影响其他业务系统的正常运行。大部分用户都会遇到性能问题,而多数性能问题都是由于下列原因引起的

  1. 数据源读取速度
  2. 目标写入速度
  3. 转换过程设计

数据读取速度

这个部分的原因要看数据源系统的情况。我们都希望对整个系统来说有一个有效的方式进行性能评估及性能优化,所以我建议这个数据源部分使用可以分割边界的系统,也就是说不要用可能过分依赖于网络,远程站点等异步的数据源系统。文件和本地局域网数据库都是比较好的选择,至少网络延时可以忽略不计,否则的话性能上的SLA很难评估,相比局域网数据库或文件系统性能的稳定性比远程HTTP或Web Service要好很多。有了稳定的数据源就要评估这种数据源的极限读取能力,也就是做Baseline,这个很重要,因为你的系统性能的极限和这个有很大关系,但是这个极限并不是不能突破,关键看设计,至少在没有特殊设计的情况下,极限性能就是对于数据源单纯的读取性能。如果数据源是关系型数据库,就需要对数据库或者查询进行优化。

目标写入速度

这方面我就不多说了,微软专门有相应的文章写了如何进行快速的数据装载,主要的问题就是No Log或者Mini Log的设计。这个过程也相对好进行评估。用BCP就能测试出来。

转换过程设计

这个部分相对来说可以做的事情比较多。SSIS的Data Flow就是Pipeline模式,你要做的事情就是让数据能够流动起来,别有堵塞。聚合、排序都会有阻塞,都要慎用。另外Merge Join也要注意,这个需要2个流入的数据源是排序的,这种排序最好考虑在数据源里面实现。如果数据源不支持,例如文件系统,可以通过SQL做一个Staging的数据库,临时排序使用,至少可以利用内存和tempdb排序,而SSIS都是在内存里面排序,你不一定有足够大的内存,至少我遇到在224G内存的服务器上出现内存不足的情况,当时的数据量是1TB。另外就是内存的占用和我们每行数据的长度及数据类型都有关系,要精确设计一下,不能用Select *这种方式,如果文件系统,需要在读取的时候转换一个准确的数据类型。

上面说了3中在Data Flow常见的设计问题和思路,在Control Flow里面的事情比较多,但是最常见的就是并行设计。很多情况下并行设计能大幅度提升你系统的性能。多数情况下,SSIS服务器并没有发挥出系统的整体性能,只要找到不同时期的性能瓶颈,逐步优化后都能有很好的性能提升,但是并行是大家经常忽略的一种情况。如果你发现你的IO,网络,内存都不存在瓶颈的时候,你要提升性能,就要注意看一下CPU了。对于CPU利用率来说,我们不能看整体,因为多核和多CPU情况下,我们必须要看每一个核的利用情况,很有可能一个核已经100%利用率,但是系统只能一个线程,这个时候必须要考虑多线程设计。SSIS设计上并没有直接提供对于线程的控制,真的没有做到与时俱进,目前很多服务器上都是4-6个核,4CPU的服务器也很多,通常每个核提供20MB/s以上的吞吐量,你可以评估一下,你的系统能够做到的性能极限是多少。我在一个测试过程中在96核的系统上可以做到大约1TB/s的数据吞吐,这还是我限制了40个线程。但是线程的设计比较复杂,需要考虑数据源的形式及特征以及Control Flow中流程的关系。基本上都是通过脚本结合流程控制组件来动态控制,目前除了这个没有其他的办法。微软目前可以提供一个Data Flow层面的转换组件RRD(Round Robin Distributor)在Data Flow里面实现多线程,目前这个组件还没有Release。如果大家在项目里面有需求,可以找我联系。

先写到这里,后面有时间在讲讲如何使用Control Flow。

page 1 of 1
Welcome , today is 星期一, 2017 年 06 月 26 日