Kettle性能调优汇总

　　性能调优在整个工程中是非常重要的，也是非常有必要的。但有的时候我们往往都不知道如何对性能进行调优。其实性能调优主要分两个方面：一方面是硬件调优，一方面是软件调优。本章主要是介绍Kettle的性能优化及效率提升。一、Kettle调优
　　1、调整JVM大小进行性能优化
　　修改Kettle定时任务中的Kitchen或Pan或Spoon脚本。Kettle是基于Java的，这方面调优和其它Java程序是一样的。
　　修改脚本代码片段
　　setOPTXmx512mcpCLASSPATHDjava。library。pathlibswtwin32DKETTLEHOMEKETTLEHOMEDKETTLEREPOSITORYKETTLEREPOSITORYDKETTLEUSERKETTLEUSERDKETTLEPASSWORDKETTLEPASSWORDDKETTLEPLUGINPACKAGESKETTLEPLUGINPACKAGESDKETTLELOGSIZELIMITKETTLELOGSIZELIMIT参数参考：
　　Xmx1024m：设置JVM最大可用内存为1024M。
　　Xms512m：设置JVM促使内存为512m。此值可以设置与Xmx相同，以避免每次垃圾回收完成后JVM重新分配内存。
　　Xmn2g：设置年轻代大小为2G。整个JVM内存大小年轻代大小年老代大小持久代大小。持久代一般固定大小为64m，所以增大年轻代后，将会减小年老代大小。此值对系统性能影响较大，Sun官方推荐配置为整个堆的38。
　　Xss128k：设置每个线程的堆栈大小。JDK5。0以后每个线程堆栈大小为1M，以前每个线程堆栈大小为256K。更具应用的线程所需内存大小进行调整。在相同物理内存下，减小这个值能生成更多的线程。但是操作系统对一个进程内的线程数还是有限制的，不能无限生成，经验值在30005000左右。
　　样例：OPTXmx1024mXms512m
　　2、调整提交（Commit）记录数大小进行优化
　　如修改RotKangTest01中的表输出组件中的提交记录数量参数进行优化，Kettle默认Commit数量为：1000，可以根据数据量大小来设置Commitsize：100050000。
　　Commit数量调整
　　3、调整记录集合里的记录数
　　RowSet是两个步骤之间的缓存。
　　性能调优的关键是如何找到性能瓶颈：一个重要的方法就是观察RowSet。如下图所示，当左边的in大于右边的out的位置时，很可能就是性能瓶颈的位置。（也可以通过单个执行最长的步骤来确定性能瓶颈）
　　kettle步骤度量Priinout
　　通过点击转换空白处，可以调整RowSet的大小：
　　RowSet调整
　　调整之后，执行效果如下：
　　kettle步骤度量Priinout
　　4、调整转换动作的并发处理数（改变开始复制的数量）
　　注意：此种方式要用在适合并发操作的场景，比如查询类，要注意死锁问题。
　　当调整RowSet大小之后，性能效果仍不明显的话，可以尝试调整转换动作的并发处理数，比如以下转换操作在数据库查询处出现性能瓶颈。
　　数据转换
　　调整并发处理数（一般设置成28个），如下：
　　复制数量调整
　　执行情况如下图所示，速度明显提高了很多：
　　5、InsertUpdate增加错误处理步骤分离Insert和Update
　　Kettle的原作者在他的博客中提到过，尽量不要使用InsertUpdate组件，因为这个组件慢的他都受不了，正常情况下在几百条每秒（对比TableInsert几万的速度）。如果必须使用这个组件的时候，那么可以在InsertUpdate中勾选Dontperformanyupdates（不做任何更新操作），然后把错误的数据指向一具数据库更新的操作，这要就把添加和更新分离了开来。根据官网描述，在少量更新大量插入的时候性能可以提高到原来的3倍左右，实测时达不到，可能和数据集有关。
　　6、数据库分组和排序优于ETL分组和排序
　　在ETL中减少排序和分组的操作，尽量使用数据库完成排序和分组。在KTR中，数据是使用流的方式在不同的步骤间传递数据，使用排序和分组的操作会在这一步阻塞KTR的执行，直到接收到前面所有步骤传过来的数据为止，导致ETL的运行时间增长，占用的内存增大。
　　使用BlockingStep也会将流阻塞到这一步，和以上情况类似。
　　7、延迟转化
　　很多字段在读入到最后输出，实际上都没有被操作过，开启延迟转化可以让kettle在必要的时候再进行转化。这里的转化是指从二进制到字符串之间的转化，在输入和输出都是文本的时候更为明显。事实上，SelectValues在转化的效率上也高于读取时直接转化。
　　8、尽量减少步骤的数量
　　步骤的数量会影响ktr的执行效率，包含并行处理时复制的数量。ktr中步骤的数量为机器核心总数的34倍最佳，如果超过这个范围，可以考虑通过减少步骤数量的方式以提高ktr的执行效率。
　　9、不要在SelectValues的步骤删除某个字段
　　如果在SelectValues的步骤删除某个字段，kettle会需要调整现有的存储结构，在可以不删除的时候尽量不要删除字段。
　　10、其他调优手段
　　（1）。使用集群，尤其是对于查询类，运算类，排序等；
　　（2）。更换其他实现方式，如js使用java类或插件；
　　（3）。注意日志级别（Rowlevel日志的性能会严重下降，是Basic的110）；
　　（4）。注意死锁问题：数据库死锁（读写同一张表）和转换本身死锁；
　　（5）。尽量使用数据库连接池；
　　使用数据库连接池，可以在一定程度上提高速度。如何查看是否使用了数据库连接池？（这个在详细日志中可以看到，使用了连接池）。
　　（6）。尽量使用缓存，缓存尽量大一些（主要是文本文件和数据流），比如排序；
　　（7）。合适的使用数据库索引，尤其对于数据库查询类。具体可以参考〔索引的正确使用〕；
　　（8）。可以使用sql来做的一些操作尽量用sql；
　　Group，merge，streamlookup，splitfield这些操作都是比较慢的，想办法避免他们，能用sql就用sql；
　　（9）。插入大量数据的时候尽量把索引删掉；
　　（10）。尽量避免使用update，delete操作，尤其是update，如果可以把update变成先delete，后insert；
　　（11）。能使用truncatetable的时候，就不要使用deleteallrow这种类似sql合理的分区，如果删除操作是基于某一个分区的，就不要使用deleterow这种方式（不管是deletesql还是delete步骤），直接把分区drop掉，再重新创建；
　　（12）。尽量缩小输入的数据集的大小（增量更新也是为了这个目的）；
　　（13）。尽量使用数据库原生的方式装载文本文件（Oracle的sqlloader，mysql的bulkloader步骤）；
　　（14）。尽量不要用kettle的calculate计算步骤，能用数据库本身的sql就用sql，不能用sql就尽量想办法用procedure，实在不行才是calculate步骤；
　　（15）。远程数据库用文件FTP的方式来传数据，文件要压缩。（只要不是局域网都可以认为是远程连接）；
　　（16）。使用Carte管理kjb和ktr减小内存消耗；
　　（17）。在确保结果输出正确的情况下，能使用并行处理的就不要使用串行处理；
　　（18）。要知道你的性能瓶颈在哪，可能有时候你使用了不恰当的方式，导致整个操作都变慢，观察kettlelog生成的方式来了解你的ETL操作最慢的地方。
　　如果我们想要优化一个ETL（ktr或者kjb）的性能，我们首先需要知道的就是它的瓶颈在哪里。而这些信息一般只能在ETL运行的步骤度量中看到，并且是不会持久化的。如果你希望把一些数据记录下来，帮助以后进行查阅，那么可以开启数据库日志和性能监控。
　　开启日志：
　　作业EditSettingsLog
　　转化EditSettingsLogging
　　开启日志后，还要设置性能监控：
　　EditSettingsMonitoring
　　勾选Enablestepperformancemonitoring（开启性能监控），下面的两个选项分别是：Stepperformancemeasurementinterval（ms）（对每一步进行性能监测的度量间隔）：这一个选项的大小会影响你在数据库记录的详细程度，一般以运行总时长的十分之一左右的数值即可，这样对于每一步可以记录10组左右的数据，足够做一些基本的分析，注意单位是毫秒。Maximumnumberofsnapshotsinmemory（在内存中保存的最大的快照数量）：这一个选项在我们系统的内存不是很足够时可以使用，但是太小可能会导致无法分析出来，和上面的选项搭配使用。
　　转换的错误日志输出：
　　我们在运行过程中会输出大量日志，这样我们在定位问题的时候需要去日志里找出错的位置在哪里。Kettle中可以对ktr单独配置日志，如果我们把ktr的错误日志直接输出出来，那么在定位问题的时候就会非常方便，设置的方法如下：在Job中，选择需要输出错误ahrefhttps：www。bs178。comrizhitargetblankclassinfotextkey日志a的步骤（经常出错或者可能出错的步骤），编辑，选择Logging，勾选SpecfifyLogfile（指定ahrefhttps：www。bs178。comrizhitargetblankclassinfotextkey日志a文件），选择路径、后缀，ahrefhttps：www。bs178。comrizhitargetblankclassinfotextkey日志a级别选择错误ahrefhttps：www。bs178。comrizhitargetblankclassinfotextkey日志a（调试时可以选详细或者行级）。后面的选项：Appendlogfile（追加到ahrefhttps：www。bs178。comrizhitargetblankclassinfotextkey日志a文件）：该选项是指ahrefhttps：www。bs178。comrizhitargetblankclassinfotextkey日志a输出到已有文件中。Createparentfolder（创建父文件夹）：该选项是指如果给定的路径中有不存在的文件夹会自动创建。Includedateinlogfile：ahrefhttps：www。bs178。comrizhitargetblankclassinfotextkey日志a中包含日期。Includetimeinlogfile：ahrefhttps：www。bs178。comrizhitargetblankclassinfotextkey日志a中包含时间。二、索引的正确使用
　　在ETL过程中的索引需要遵循以下使用原则：
　　1、当插入的数据为数据表中的记录数量10以上时，首先需要删除该表的索引来提高数据的插入效率，当数据全部插入后再建立索引。
　　2、避免在索引列上使用函数或计算，在where子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。例如：低效：selectfromdeptwhereSAL1225000；高效：selectfromdeptwhereSAL2500012；
　　3、避免在索引列上使用NOT和！，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到NOT和！时，就会停止使用索引转而执行全表扫描。
　　4、索引列上用替代高效：selectfromtempwheredeptno4低效：selectfromtempwheredeptno3
　　两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO3的记录并且向前扫描到第一个DEPT大于3的记录。三、数据抽取的SQL优化
　　1、Where子句中的连接顺序：
　　比如ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
　　例如：
　　（低效，执行时间156。3秒）SELECTFROMEMPEWHERESAL50000ANDJOBMANAGERAND25（SELECTCOUNT（）FROMEMPWHEREMGRE。EMPNO）；
　　（高效，执行时间10。6秒）SELECTFROMEMPEWHERE25（SELECTCOUNT（）FROMEMPWHEREMGRE。EMPNO）ANDSAL50000ANDJOBMANAGER；
　　2、删除全表是用TRUNCATE替代DELETE：
　　因为TRUNCATE是DDL语句，它执行时不会产生UNDO信息，因此要更快。尤其是当数据量比较大的时候。但注意的是TRUNCATE只针对删除全表数据。delete关键字：deletefrom表名truncate关键字：truncate表名
　　3、尽量多使用COMMIT：
　　mysql默认是开启Commit，而对于Oracle也尽量多使用Commit。
　　只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少：
　　COMMIT所释放的资源包括：
　　a。回滚段上用于恢复数据的信息；
　　b。被程序语句获得的锁；
　　c。redologbuffer中的空间；
　　d。ORACLE为管理上述3种资源中的内部花费。
　　ETL中同一个过程的数据操作步骤很多，数据仓库采用的是数据抽取后分析模型重算的原理，所以对数据的COMMIT不像业务系统为保证数据的完整和一致性而需要某个操作过程全部完成才能进行，只要有可能就在程序中对每个deleteinsert和update操作尽量多使用COMMIT，这样系统性能会因为COMMIT所释放的资源而大大提高。
　　4、建议用EXISTS替代IN：
　　写sql时，最好用exists来代替in，因为in不走索引，所以用exists的sql性能较好。当然这不是绝对的，毕竟in的可读性好，如果in的访问量比较小或者in的参数为数值列表就比较合适。
　　总之，建议尽可能使用exists方式，避免使用子查询。
　　5、用NOTEXISTS替代NOTIN：
　　在SQL中，我们经常会习惯性的使用notin来实现一张表有而另外一张表没有的数据，在访问量比较小的时候是可以的，但是一旦数据量大了，NOTIN就是最低效的，因为它对子查询中的表执行了一个全表遍历，所以我们推荐使用notexists或者外连接来代替：selectfromtabletwheret。idnotin（selectidfromtable2）可以替换成以下两种方式selectfromtableawherenotexists（select1fromtable2bwherea。idb。id）；selecta。fromtable1aleftjointable2bona。idb。idwhereb。idisnull；
　　总结：EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
　　6、优化GROUPBY：
　　groupby使用了临时表和排序：
　　Explain分析GroupBy语句Extra这个字段的Usingtemporary表示在执行分组的时候使用了临时表Extra这个字段的Usingfilesort表示使用了排序
　　groupby使用不当，很容易就会产生慢SQL问题。因为它既用到临时表，又默认用到排序，有时候还可能用到磁盘临时表。
　　如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmptablesize），会把内存临时表转成磁盘临时表。如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间和磁盘IO。
　　主要是这些导致了慢SQL的因素，所以GroupBy的优化很重要。
　　从哪些方向去优化呢？方向1：既然它默认会排序，我们不给它排是不是就行啦。方向2：既然临时表是影响groupby性能的X因素，我们是不是可以不用临时表？
　　我们一起来想下，执行groupby语句为什么需要临时表呢？groupby的语义逻辑，就是统计不同的值出现的个数。如果这个这些值一开始就是有序的，我们是不是直接往下扫描统计就好了，就不用临时表来记录并统计结果啦？所以我们优化的方式主要是以下方面：groupby后面的字段加索引orderbynull不用排序尽量只使用内存临时表使用SQLBIGRESULT
　　提高GruopBy语句的效率，可以通过将不需要的记录在GROUPBY之前过滤掉。低效：selectJOB，AVG（SAL）FROMEMPGROUPBYJOBHAVINGJOBPRESIDENTORJOBMANAGER高效：selectJOB，AVG（SAL）FROMEMPWHEREJOBPRESIDENTORJOBMANAGERGROUPBYJOB
　　7、有条件的使用UNIONALL替换UNION：
　　ETL过程针对多表连接操作的情况很多，有条件的使用unionALL替换union的前提是：所连接的各个表中无主关键字相同的记录，因为uniionALL将重复输出两个结果集全中相同记录。
　　当SQL语句需要union两个查询结果集合时，这两个结果集合会以uniionALL的方式被合并，然后在输出最终结果前进行排序。如果unionALL替代union，这样排序就不是必要的了，效率就会因此提高35倍。
　　8、分离表和索引：
　　主要针对ORACLE，总是将你的表和索引建立在不同的表空间内（TABLESPACES），决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。同时，确保数据表空间和索引表空间置于不同的硬盘上。