kettle优化之提高MySQL读写速度

kettle优化之提高MySQL读写速度

       背景：在往mysql数据库中进行抽取清洗转换数据的时候，发现表的数据量大的时候，对表的读、写操作的速度很慢，并且是达到一定数据量以后速度会特别慢，数据量小的时候读的速度是很快。

一、优化前测试效果

读写数据速度测试：5000条数据读写速度测试：8min24s 即 504s

20000条数据读写速度测试: 16min32s 即 992s

二、优化方案

2.1、修改数据库连接参数

命名参数	值	参数说明
useCompression	TRUE	在传输时开启数据压缩  ，提高传输效率
rewriteBatchedStatements	TRUE	让数据库重排Insert语句，合并多条插入语句成为一条，提交插入效率
useServerPrepStmts	FALSE	让数据库重排Insert语句，合并多条插入语句成为一条，提交插入效率

改变后速度：

5000条数据的读写结果：3.1s

200000条数据读写速度测试: 2min18s 即138s

2.2、修改spoon.bat内存大小

参数参考：

 -Xmx1024m：设置JVM最大可用内存为1024M。 -Xms512m：设置JVM初始内存为512m。此值可以设置与-Xmx相同，      以避免每次垃圾回收完成后JVM重新分配内存。

必须注意：-Xmx必须小于等于系统内存的1/4，要不然会报错，-Xms不大于-Xmx。当系统内存为4G时-Xmx不能大于1G,当系统内存为8G时或更大时，java(JDK)版本必须是64位的才能识别出来,此时-Xmx可以为2G或更高。个人建议内存设置不要超出整个服务器内存的2/3。

2.3、适当的提高数据集的大小

设置方法：右击转换空白处，选择转换设置，在杂项中进行设置。

2、提交记录数量更改为10000，即每次提交10000条记录(前提是没有并发的设置，或错误后被插入/更新执行)

2.4、设置并发处理

方法：start设置多个并发任务，鼠标右击start,选RUN Next Entries in Parallel

2.5、开启多线程

需要添加右键，然后填写数量为2,enter即可。

      特别注意:在多线程”insert/update”场景中，如果更新的key并非唯一，则有可能产生死锁(多个线程一次更新同一行的数据)，因此设置并发的时候最好关键字是主键或唯一字段，插入更新的提交数量建议设置为1。

2.6、点击“确定”比较卡

输入表的数据库中参数配置如下：

1、增加读的操作

useServerPrepStmts:true

cachePrepStmts:true    

2、读取缓存，设置过高消耗内存也会高

defaultFetchSize:10000

useCursorFetch:true

3、压缩数据传入，与mysql服务端进行通信时采用压缩

useCompression:true

具体配置参考如下：

三、总结

使用kettle时必须要注意点：

1、检查连接数据库的驱动版本，要做到jar包的版本和数据库版本接近；

2、创建数据库连接的url中必须加上以下两个参数：

?autoReconnect=true&failOverReadOnly=false

3、创建数据库连接的参数中必须加上以下三个参数：（用于增加数据写入速度）

useServerPrepStmts=falserewriteBatchedStatements=trueuseCompression=true

4、创建数据库连接，如果使用的连接池，必须设置maxIdl值较大：

5、必须根据kettle软件所在的硬件，修改spoon.bat(window)或spoon.sh(linux)的内存大小：

更多优秀文章，请扫码关注个人微信公众号或搜索“程序猿小杨”添加。