一、Kettle环境搭建
一、Windows主要用于可视化创建数据迁移用到的ktr转换脚本、kjb作业脚本,以及脚本需要配置的DB2信息、读写性能优化信息等,也可直接在客户端完成不同数据库之间的数据迁移。
(1)创建测试库表
分别在Windows(模拟旧库环境)及Linux(模拟新库环境),创建一个测试库test以及两张测试库表test.demo_info
、test.demo_info2
,这里为了方便测试,我两张表除了表名不一样,其他字段都一样,测试表的主键为ID,使用了auto_increment设置主键从1开始自增长,MySQL中int类型占用4个byte字节,即最大数值是(2^31)-1即2147483647,大概二十多亿,数值够大,自增长主键实际使用问题不大~
– 建库测试库
create database if not exists test default character set utf8 collate utf8_general_ci;
use test;
– 创建测试表demo_info
use test;
create table test.demo_info(
id int(7) primary key not null auto_increment,
name varchar(255) not null,
sex char(1) not null,
age int(3)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
alter table test.demo_info add index index_name(name);
alter table test.demo_info add index index_age(age);
alter table test.demo_info add index index_name_age(name,age);
alter table test.demo_info comment '测试表';
alter table test.demo_info modify column id int(7) not null auto_increment comment 'ID';
alter table test.demo_info modify column name varchar(255) not null comment '姓名';
alter table test.demo_info modify column sex char(1) not null comment '性别:1-男,0-女';
alter table test.demo_info modify column age int(3) comment '年龄';
– 创建测试表demo_info2
create table test.demo_info2(
id int(7) primary key not null auto_increment,
name varchar(255) not null,
sex char(1) not null,
age int(3)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
alter table test.demo_info2 add index index_name(name);
alter table test.demo_info2 add index index_age(age);
alter table test.demo_info2 add index index_name_age(name,age);
alter table test.demo_info2 comment '测试表2';
alter table test.demo_info2 modify column id int(7) not null auto_increment comment 'ID';
alter table test.demo_info2 modify column name varchar(255) not null comment '姓名';
alter table test.demo_info2 modify column sex char(1) not null comment '性别:1-男,0-女';
alter table test.demo_info2 modify column age int(3) comment '年龄';
(2)构建测试数据
只需要插入Windows(模拟旧库环境)数据表的测试数据,Linux(模拟新库环境)不需要,我们的目的是要使用Kettle迁移数据到Linux(模拟新库环境)数据表的~
这里构建测试数据很简单,因为两张数据表的主键ID设置了自增长,直接新建文本,编辑文本另存为后缀.del文件,再使用文本编辑器(notepad++ yyds 仅个人推荐,非广告,哈哈哈!!!)编辑内容,这里先直接复制个不含ID的10w
条数据(注意字段数据间的特殊分隔符是0x0f,如下图特殊符号所示)到文件中~
通过MySQL的load
data infile语法指定字段快速插入数据,用这些数据,先来简单的进行数据迁移的测试,后面性能优化之后再用100w
、1000w
更多的数据量来进行数据迁移测试,当然实际环境的话最好按你们实际数据库大概有多少数据量,去构建多少的测试数据~
这里提供下,Kettle数据迁移10w、100w、1000w 测试del数据文件,也可以可直接下载~
csdn 下载1~
mpan 下载2~ 提取码:jj6l
说明:想学习和了解MySQL的load
data infile导出数据的语法和使用技巧的话,可以先看下我的这篇文章学习下:
MySQL如何使用load data infile、into outfile高效导入导出数据…
先执行这两行命令,导入10w条数据(性能优化前测试使用):
load data infile 'C:/Users/Administrator/Desktop/10w.del' into table test.demo_info character set utf8 fields terminated by 0x0f (name,sex,age);
load data infile 'C:/Users/Administrator/Desktop/10w.del' into table test.demo_info2 character set utf8 fields terminated by 0x0f (name,sex,age);
通过load data infile分别导入两张表的数据,还挺快的,单表不到1秒完成10w条数据导入~
导入100w条数据(性能优化后测试再使用):
load data infile 'C:/Users/Administrator/Desktop/100w.del' into table test.demo_info character set utf8 fields terminated by 0x0f (name,sex,age);
load data infile 'C:/Users/Administrator/Desktop/100w.del' into table test.demo_info2 character set utf8 fields terminated by 0x0f (name,sex,age);
导入100w条数据(性能优化后测试再使用):
load data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info character set utf8 fields terminated by 0x0f (name,sex,age);
load data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info2 character set utf8 fields terminated by 0x0f (name,sex,age);
测试库表及数据构建完成,接下来进入正文学习如何制作Kettle脚本及性能优化的实战演练吧~
推荐内容
1.1 Kettle下载及安装
Kettle安装包官方下载 https://sourceforge.net/projects/pentaho/files/
我这里以最新版本pdi-ce-
9.2.0.0-
290.zip
为例,直接下载zip压缩包后解压得到目录data-integration即可~
这里通过双击Spoon.bat
即可~
不过,在运行之前,还是要先装好相关相关数据库的连接驱动包
,不然无法连接到数据库~
1.2 驱动包下载
Kettle是基于纯Java实现的,通过JDBC与数据库建立连接,连接过程需要依赖数据库连接驱动jar包,这里提供几种常用的驱动包的官方下载地址如下:
官方Oracle Java驱动包下载: https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html
官方DB2 Java驱动包下载:https://www.ibm.com/support/pages/node/382667
官方MySQL Java驱动包下载: https://dev.mysql.com/downloads/connector/j/
我这里下载MySQL的驱动包,选择哪个版本呢?~
1.3 驱动包版本
关于驱动包的版本选择,这个要根据实际相关数据库的版本去选择对应驱动包版本下载,这里我以MySQL数据库驱动包及版本下载为例~
进入上面给的MySQL官方下载地址,可以看到最新的驱动是8.
0.
26的驱动(下拉选Platform Independent
):
这里说明一下,只要你的MySQL是5.
6及以后的数据库版本
,直接下载8.
0+
的最新驱动包mysql-connector-java-
8.
0.
26即可,不一定非要MySQL 5.7就下5.7的驱动包,而且MySQL官方文档也说明建议MySQL 5.6以后的升级驱动包为8.0+的,如下:
即MySQL驱动包8.
0+
版本能够兼容MySQL5.
6及之后的所有版本的数据库连接~
如果你使用的MySQL低于 5.
6版本的,比如MySQL
5.
5,驱动包8.
0+
是不兼容的,无法完成数据库的连接~
这时,只需要去历史版本Archives下载对应的5.
1.+
版本的驱动包即可~
解压下载得到的zip压缩包,得到官方MySQL连接驱动jar包~
1.4 驱动包安装
将下载得到的MySQL数据库连接驱动包mysql-connector-java-
8.
0.
26.jar
放到kettle如下目录:
data-integration\lib
如果你觉得不够稳妥的话,比如放到Linux会不会加载不到驱动,那么以下这几个目录,都放驱动包,妥妥的:
\data-integration\libswt
\data-integration\libswt\osx64
\data-integration\libswt\win64
2.1 Windows
Windows下,主要通过运行Spoon.bat
,打开Kettle客户端可视化操作页面,创建数据迁移用到的ktr转换脚本、kjb作业脚本(转换
和作业
后面会介绍),以及相关数据库连接配置、读写性能优化配置等,配置完成后最后一般都是上传kettle脚本到Linux上执行,当然也可直接在Windows下Kettle客户端可视化页面直接执行转换或作业脚本,完成数据库之间的数据迁移,但是生产一般是在Linux,而且Windows执行数据迁移的效率一般要比Linux慢一些~
2.1.1 数据库连接测试
双击\
data-integration\Spoont.bat脚本运行~
视图主对象树
-> 转换
->右键新建 -> 直接快捷键Ctrl + S
另存为test.ktr
(自定义后缀,这里建议使用.ktr)
选中DB连接
,按如下图,操作验证相关数据库是否能正确连接,这里以MySQL数据库为例~
注意:两个DB连接,都要右键,设置为共享!!!
本地数据库连接可以使用自己的IP,方便后面Linux中模拟两台IP服务器之间测试:
几种导致数据库连接报错的情况:
…
(1)缺少驱动或者无法正确加载到驱动都会报错,提示:
Driver class org
.gjt.mm.mysql.Driver
could not be found…**
…
(2)远程连接MySQL的用户(root),没有被放开远程连接的权限,,则会报错如下:
Access denied for user ‘root’@’119.
168.xxx.xxx
’ (using password: YES)
…
(3)配置连接参数不对,会报错如下:
Connection failed. Verify all connection parameters and confirm that the appropriate driver is installed.
…
缺少驱动的具体就不说了,直接下载数据库驱动包放到\
data-integration\lib目录下即可~
…
MySQL放开用户的远程连接(flush刷新权限生效)方式如下:
mysql> select
user,host
from mysql.
user;
mysql> update mysql.
user
set host=
'%'
where
user =
'root';
mysql> flush privileges
;
…
同样如果需要在Kettle中建立Oracle、DB2连接时,如果没有驱动也会报同样类似的错误,这时需要下载数据库连接驱动包并正确安装驱动包即可~
2.1.2 Kettle作业和转换
使用Kettle进行数据迁移的过程中,主要有两个专业名词即转换(Kettle Transformation)
和作业(Kettle
Job)
~
名词 | 说明 |
转换 | 一般文件后缀命名为.ktr ,单表迁移数据,构建表输入(读取数据),表输出(写入数据),Linux下使用 |
作业 | 文件后缀为.kjb,可以关联多个.ktr执行,实现多表(串行/并行)迁移数据,Linux下使用 |
2.1.2.1 创建Kettle转换
在前面数据库连接测试
时,新建的test.ktr
就是一个Kettle转换
文件,只不过前面只是测试了DB连接
是否正常,至于表输入
和表输出
的相关配置,实现数据表数据迁移的相关配置还没作具体说明~
选项 | 说明 |
表输入 | 简单理解为从指定的数据表(旧库)读取数据的过程~ |
表输出 | 简单理解为插入数据到指定的数据表(新库)的过程~ |
(1)同前面创建test.ktr
方式,创建demo_info数据表对应的转换文件demo_info.ktr
(2)双击表输入
进行编辑,如下图:
(3)双击表输出
进行编辑,如下图:
(4)同样创建测试表demo_i
nfo2对应的转换文件demo_i
nfo2.ktr
2.1.2.2 创建Kettle作业
(1)完成kjb作业的创建,并关联demo_info表的ktr转换,作业执行过程为 Start -> 转换 -> 成功
,如下图:
注意这里Transformation一般建议使用相对路径,移植性比较高,如:
${Internal.Entry.Current.Directory}/demo_info.ktr
表示kjb会加载跟自己同目录的ktr文件,建议不要写死ktr的绝对路径,容易出错~
(2)同样将测试表demo_info2的转换也添加到作业中~
最后,快捷键Ctrl + S
或者点击左上角保存,保存得到kjb作业文件(跟ktr转换放同一个目录下)~
2.1.3 数据迁移测试(未优化)
前面已通过在Windows下可视化来创建好Kettle数据迁移需要用到的作业
和转换
了,测试环境可以直接在Kettle客户端点击执行转换(单表)或作业(多表)的运行按钮,完成数据的迁移即可~
2.1.3.1 执行转换测试(单表)
建议在测试每个表的转换时,先少量数据,验证每个转换的正确性,最后执行作业时,关联所有转换完成多个数据表的全部数据迁移~
我这里先正常用前面插入的10w条记录,测试看看Kettle不做任何性能优化,单表demo_info数据迁移的效率如何~
通过转换单表数据迁移的过程…
最后看到,Windows环境下,Kettle默认不做任何优化,单表数据迁移10w条数据大概100条左右/秒
,总耗时16分
左右,汗!!!虽然也跟我Linux服务器(目标数据库)配置有关,就单核1G,但主要还是没做性能优化~
同样,测试下demo_info2表的转换是否正确,不过这里就不再测10w条记录了,太慢了,双击修改下demo_info2表输入,select查询语句中加个limit
0,
100测试100条数据迁移,验证转换正确性即可~
验证转换demo_info2.ktr没问题~
也可以检查下Linux(模拟新库环境)测试表demo_info2的数据是否是100条~
2.1.3.2 执行作业测试(多表)
把前面通过转换迁移到Linux(模拟新库环境)数据表demo_info和demo_info2的数据先清了,并重置ID从1开始自增~
- - Linux下mysql命令行执行:
use test;
delete from test.demo_info;
alter table test.demo_info auto_increment 1;
delete from test.demo_info2;
alter table test.demo_info2 auto_increment 1;
重新来测试测试下通过作业关联两个转换的多表的数据迁移( 数据量比较大的话,建议先配置每个转换的SQL语句查询少量数据迁移,先验证完整作业执行的正确性)…
这里不用测试也知道,因为都是没做任何性能优化,因此跟前面通过转换demo_info.ktr迁移demo_info单表10w条数据的耗时没有任何区别,只不过是串行的加了一张表,耗时增加了一倍多一些~~
这里有时间也跑一下吧,花了37分钟迁移20w条数据,还是大概90条记录/秒
,汗!!!前面16分钟10w条数据,100条左右/秒
~
虽然也跟个人电脑及服务器的CPU性能以及网络传输能力有关,但这些硬件条件有时候没法改变,接下来会从软件层面来进行优化,相信会有飞一样的感觉!!!~~
2.1.4 数据迁移(性能优化)
通过前面不做任何性能优化的Kettle,在做数据迁移时,效率真的是非常低下,接下来对Kettle数据迁移优化,做完优化,整体迁移效率至少提升5 ~ 10倍以上的效率,主要从以下几个方面的优化:
- 优化Kettle
作业
- 优化Kettle
转换
- 化Kettle连接DB
参数
- 优化
JVM
内存大小
- 优化目标表的字段
索引
(临时删除)
2.1.4.1 优化Kettle作业
(1)优化Kettle作业
第一步:统计数据量比较大的表
统计数据量比较大的表,将这些表作为并发执行的第一张表,其他小数据量的表平均放在后面即可,这里假设我们构建的两张数据表demo_info和demo_info都是两张大表~
第二步:重新改造作业为并发执行转换~
把原来串联执行的demo_info和demo_info2改成并发执行~
第三步:Kettle -> kjb作业文件 -> 选中"Start" -> “右键” -> Run next Entries in parallel -> 确定(I understand),完成并发生效,如图即为并发执行:
2.1.4.2 优化Kettle转换
(1)加大提交记录数
加大每一个表对应的ktr转换的表输出,每次的提交记录数~
加大步骤:Kettle -> ktr转换文件 -> 双击表输出 -> 提交记录数量: 10000 (默认1000,范围1000~50000,百千万级大表,建议最大设置为50000即可,最大不要超过65535!!)
2.1.4.3 优化Kettle连接DB参数
(1)使用连接池
配置步骤:kettle -> ktr转换文件 -> DB连接 -> 双击数据库连接 ->连接池~
比如:
设置连接池初始化大小:50 最大空闲:151 以及勾选关键的几个参数并赋值~
连接池的相关参数要配置多少合适呢?
这个可以按你们工程jdbc.properties数据库配置中的参数值来填写以及根据自己数据库当前配置的max_connections最大连接数来确定~
注意:关于max_connections最大连接数,MySQL 5.7版本默认151
,最大可设置为2的14次方=``16384
可以查看下自己的MySQL服务配置的最大连接数是多少:
mysql > show
variables like
'%max_connections%';
当然有些小伙伴说,数据库最大连接数多少咱也不懂,咱也不敢改啊,OK,没问题,如果你不敢改,那就使用数据库默认的最大连接数,起码默认最大连接数也有151,只不过可能会慢一些~
如果你有权限,想让自己生产的数据迁移效率高一些,还是建议申请修改一下最大连接数,默认的太小了,可以通过修改MySQL的配置my.ini(Linux是my.cnf),加大MySQL的最大连接数,在[mysqld]组下添加或者修改以下内容:
max_connections=
2000
Windows下直接net stop mysql/ net start mysql重启生效MySQL服务~
Linux下可能会不生效,若不生效则需要配置Linux操作系统的limits.
conf配置文件~vi
/etc/security/limits.conf
在最后加入以下MySQL服务配置信息:
mysql hard nofile 65535
mysql soft nofile 65535
查看Linux最大连接显示是否设置成功~
ulimit -n
重启MySQL服务后,查看Linux下最大连接数是否设置成功~
(2)优化DB读写参数
– 表输入(读)核心参数优化
双击DB连接(读库),对选项下的相关参数进行配置:
- - 参数说明
参数及赋值 | 参数说明 |
defaultFetchSize=10000 | 每次与数据库交互,读多少条数据加入内存中缓存,不设置默认把所有数据读取出来,容易内存溢出(OOM),我这里设置10000,大表CPU性能高建议设置最大50000,不能超过65535 |
useServerPrepStmts | 是否在使用服务端的预编译语句,true表示以占位符的方式发送SQL到服务端进行拼接 |
cachePrepStmts | 是否客户端缓存预处理语句 |
useCursorFetch | 是否允许部分数据到客户端就进行处理,如果为false表示所有数据到达客户端后,才进行处理 |
参数不在于多,在于使用几个简单高效的参数即可,其他参数可以查看MySQL性能优化官方文档:
https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html
– 表输出(写)核心参数优化
双击DB连接(写库),对选项下的相关参数进行配置:
- - 参数说明
参数及赋值 | 参数说明 |
defaultFetchSize=5000 | 每次与数据库交互,从内存中读取多少条数据写入数据表,不设置默认把所有数据写入, rewriteBatchedStatements设置true,该参数会失效 |
rewriteBatchedStatements | 是否开启批量写入,true表示开启,原多条insert变成单条insert执行 |
useServerPreStmts | 是否使用服务端预编译,设置为false,表示在客户端编译好 |
useCompression | 是否使用压缩,使用压缩优化客户端与服务端传输效率 |
useCursorFetch | 是否允许部分数据到客户端就进行处理,如果为false表示所有数据到达客户端后,才进行处理 |
2.1.4.4 优化JVM内存大小
修改Kettle安装目录data-integration/Spoon.bat
(Linux是Spoon.sh
)文件中的PENTAHO_DI_JAV
A_OPTIONS参数中的JVM相关参数值~
默认如下:
PENTAHO_DI_JAVA_OPTIONS=
"-Xms1024m"
"-Xmx2048m"
– JVM内存优化说明
参数 | 说明 |
-Xms | 堆区最小内存,默认物理内存的1/64,建议-Xms、-Xmx设置成一样,避免每次GC后调整堆区大小 |
-Xmx | 堆区最大内存,默认物理内存的1/4 ,建议最大值设置为实际可用物理内存的3/4即可,不要超过80%,不过有个度,32位操作系统有限制,Winows 32限制2-3G,Linux限制2-3G~ ,64位操作系统不限制,取决于实际可用的物理内存~ |
-Xmn | 新生代内存大小,JDK1.8 新生代:年老代=1:3 ,官方建议设置为最大堆区内存值的3/8,不知道这里不配置也行~ |
-XX:MaxPermSize | 永久代最大内存,默认64m,JDK1.8取消了永久代,这里该参数就不要配置了,配置了也会失效,JDK1.7及之前版本,默认64m,建议加大一些,不然64m随时发生OutOfMemoryError内存溢出~ |
(1)Windows配置JVM参数
查看物理内存~
理论上建议设置的JVM最大堆区内存值(8g * 3/4 = 6g即 6 * 1024m = 6144m):
PENTAHO_DI_JAVA_OPTIONS=
"-Xms6144m"
"-Xmx6144m"
不过我本地电脑实际物理内存就30%(2g)左右了,配置再大也没用,再稍微优化下,勉强配置个3g=3072m最大堆区内存吧:
PENTAHO_DI_JAVA_OPTIONS=
"-Xms3072m"
"-Xmx3072m"
(2)Linux配置JVM参数
查看Linux的内存大小(默认单位KB):
cat /proc/meminfo |grep 'MemTotal'
cat /proc/meminfo |grep 'MemFree'
或(更简单的)
free -m
可以看到我的"高性能"单核
1g Linux服务器
,就剩余83M空闲内存!!!!对写数据效率影响大!
不过一般生产的机器基本标配都是4G或8G起步了吧,像我当时做数据迁移时,UAT环境就MySQL服务器16G,生产环境MySQL服务器的物理内存是32G(32768M,33554432KB,三千多万KB,我们老板,有钱!!!),按堆区最大内存设置为物理内存的1/4,年轻代为最大堆区的3/8的话,可以设置的Kettle的JVM内存对应配置为:-Xms24g -Xmx24g -Xmn9g -XX:
MaxPermSize=1024m
不过单个作业执行,肯定用不到那么大内存,而且要考虑可能并发执行多个作业,建议单个作业最大配置个3g就OK了~
2.1.4.5 优化目标表的字段索引(临时删除)
在目标库建立库表时,先不要创建表的相关非主键字段索引,如果已经创建的,建议先临时删除相关表(新库)的字段索引,完成数据迁移之后,再重新建立字段索引~
MySQL建立字段索引,用得好会给查询的速度有大大的提升,但是进行插入、修改数据时,MySQL也需要进行动态的维护索引,比较消耗性能,因此在做数据迁移时建议先临时删除索引,特别是百千万级大表,如果存在字符串组合索引,在做数据迁移插入数据时效率非常低~
– 查看指定表的索引
show index from test.demo_info;
前面我们创建测试表的时候,也建立了索引,因此需要先删除索引~
– 临时删除非主键索引(数据迁移前)
alter table test.demo_info drop index index_name;
alter table test.demo_info drop index index_age;
alter table test.demo_info drop index index_name_age;
alter table test.demo_info2 drop index index_name;
alter table test.demo_info2 drop index index_age;
alter table test.demo_info2 drop index index_name_age;
– 重新建立非主键索引(数据迁移完成后)
alter table test.demo_info add index index_name(name);
alter table test.demo_info add index index_age(age);
alter table test.demo_info add index index_name_age(name,age);
alter table test.demo_info2 add index index_name(name);
alter table test.demo_info2 add index index_age(age);
alter table test.demo_info2 add index index_name_age(name,age);
2.1.5 重做数据迁移(性能优化后)
完成关键的几个方面的性能优化后,接下来就是见证奇迹的时候了,来迁移下20w条数据,看看效率如何~
(1)测试迁移两张表20w条数据
执行作业~
16秒!!!!两张表20w条数据,大概12500条/秒~
前面我们没做任何优化前,单表10w条数据,要16分钟,100条/秒左右,两张表20w条数据,要37分钟,90条/秒左右~
性能直接飙升120倍以上?? 当然实际肯定不会提高那么高倍数的,只是数据量太少了,接下来再测一下200w条数据量迁移效率如何~
(2)测试迁移两张表200w条数据~
清理Windows本地数据:
delete from test.demo_info;
delete from test.demo_info2;
alter table test.demo auto_increment 1;
alter table test.demo2 auto_increment 1;
同样,清理Linux远程数据:
delete from test.demo_info;
delete from test.demo_info2;
alter table test.demo auto_increment 1;
alter table test.demo2 auto_increment 1;
Windows本地两张表分别导入100w条数据
load data infile 'C:/Users/Administrator/Desktop/100w.del' into table test.demo_info character set utf8 fields terminated by 0x0f (name,sex,age);
load data infile 'C:/Users/Administrator/Desktop/100w.del' into table test.demo_info2 character set utf8 fields terminated by 0x0f (name,sex,age);
load data infile导入数据还是挺快的,单表100w条数据,4秒左右~
完成本地库两张表数据导入,执行重新执行作业,看看还是不是有120倍,如下所示:
耗时2分48秒,两张表200w条数据,11904条/秒左右,不错了,不过比我预期的低一些,毕竟我本地Windows配置和Linux远程服务器的性能比较一般!!!
(3)测试迁移两张表2000w条数据~
同样,来测试下2000w,数据迁移效率如何~
Windows本地两张表分别导入1000w条数据:
load data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info character set utf8 fields terminated by 0x0f (name,sex,age);
load data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info2 character set utf8 fields terminated by 0x0f (name,sex,age);
两张表都load data infile导入数据,单表导入1000w条数据耗时45 ~ 50秒左右~
执行作业
耗时30分钟左右,两张表2000w条数据,大概11111条/秒的速度,个人觉得挺不错了,最辣鸡的配置了,生产好一些的配置,连接池配大一些,JVM参数再加大一些,单个作业能达到了25000+条/秒以上,Linux可以分成4个作业,并发执行,即便每个脚本下降到2w条/秒左右,但总的还是能达到8w条/秒,1分钟 480w,10分钟4800w,30分钟内还是能轻松实现亿级数据量的高效迁移的~
3.1 Kettle环境搭建
3.1.1 检查JDK
Kettle基于纯java编写,基于JDK环境运行,因此需要检查下环境~
java -version
如没有配置好JDK,需先配置好JDK环境~
3.1.2 目录规划
目录位置及命名可以根据实际需要定义,这里建议创建这四个目录:
mkdir -p $HOME/kettle
mkdir -p $HOME/kettle/kettle_file
mkdir -p $HOME/kettle/kettle_sh
mkdir -p $HOME/kettle/kettle_log
mkdir -p $HOME/kettle/sql
注意:Linux下$HOME表示当前操作用户的主目录,如我都是用mysql用户操作的,主目录/home/mysql
– 目录说明
目录 | 说明 |
kettle_file | 存放数据迁移准备好的kettle的转换ktr和作业kjb文件 |
kettle_sh | 存放Shell脚本,脚本内容是多表,通过kitchen.sh调用作业kjb,单表的话,通过pan.sh调用转换ktr~ |
kettle_log | 存放执行脚本以及调用Kettle作业和转换进行数据迁移打印的日志文件~ |
sql | 存放统计数据量的SQL语句文件~ |
先上传kettle作业和转换文件到kettle_file目录下~
3.1.3 kettle安装包
提前在Windows下往安装包pdi-ce-9.0.0.0-423.zip的lib目录中放好数据库驱动包~
上传压缩包到MySQL服务器,如:
$HOME/kettle/
解压完成安装:
cd $HOME/kettle/
unzip pdi-ce-9.0.0.0-423.zip
3.1.4 验证kettle
执行测试kettle命令:
cd $HOME/kettle/data-integration
./kitchen.sh --version
./pan.sh --version
分别出现关键信息如下,表明kettle解压安装成功!
...
Kitchen - Start of run.
...
Pan - Start of run.
...
如果服务器内存不足,执行测试可能会报错如下:
关键报错:
Native memory allocation (mmap) failed to map
715849728
bytes for committing reserved memory.
即最低至少要分配715849728 bytes约等于699M的内存,才能运行kitchen.sh脚本测试~
查看了我的单核1gLinux服务器,总物理内存才990M左右,剩余内存就211M了,汗!!!
通过top命令查看,光是mysqld服务进程就占用了44%,大概占用435M了,基本没内存了~
一般如果生产需要在Linux执行kettle脚本,内存肯定不会这么小的~
这种情况,我这里为了方便在Linux简单测试,只能先修改data-integration/spoon.
sh中的JVM内存小一点了,改成128m
吧,如下(搜索PENTAHO_DI_JAV
A_OPTIONS定位):
if [ -z "$PENTAHO_DI_JAVA_OPTIONS" ]; then
PENTAHO_DI_JAVA_OPTIONS="-Xms128m -Xmx128m"
fi
实际CPU性能好一些的测试环境或者生产环境建议改为3g
即3072m
:
if [ -z "$PENTAHO_DI_JAVA_OPTIONS" ]; then
PENTAHO_DI_JAVA_OPTIONS="-Xms3072m -Xmx3072m"
fi
再次测试下
cd $HOME/kettle/data-integration
./kitchen.sh --version
./pan.sh --version
3.2 检查驱动及网络连接
kettle安装成功不代表两台服务器之间能开始进行数据迁移了,还需要检查相关数据库驱动
以及新旧数据库两台服务器之间的网络是否连通
~
(1)检查驱动
检查kettle安装目录lib目录下表是否存在相关数据库的连接驱动,如MySQL数据库连接驱动包
data-integration/lib/mysql-connector-java-
8.
0.
26.jar
(2)检查网络及连接
在新数据库服务器检查与旧数据库服务器的网络连通性
以及连接权限指定端口
~
– 检查与旧数据库服务器的网络是否连通
ping 旧数据库服务器的IP
– 是否有权限连接到指定端口(如MySQL的3306端口)
telnet 就数据库服务器的IP 3306
当然如果要在Linux执行telnet Windows的3306端口,需要做以下几件件事:
第一:配置防火墙入站规则天机3306端口
防火墙未开启,可以忽略这步~
第二:勾选telnet服务端
第三:开启telnet服务
3.3 再次性能优化
这一步主要是,准备Shell脚本!!!
(1)主要是针对亿级以上的数据量,再次进行性能优化的方案,数据量比较小的可以忽略~
(2)前面通过Windows下的可视化操作等对Kettle进行了优化,可以直接在Kettle客户端执行作作业或者转换,完成数据迁移,也可以直接上传脚本到Linux服务器执行~
(3)实际的测试环境或生产环境,大多一般都是两台远程Linux服务器之间的数据迁移,如果库表数据量达到亿级以上,这种情况就可以根据每一张大表(如1000w以上)创建一个Shell脚本,每个脚本的内容是执行指定的作业~
(4)如果存在多张大表,则对应创建多个Shell脚本,这样可以在实际投产时并发的执行Shell脚本,最大化的利用CPU的性能,并发数据迁移,再次成倍数的提高数据迁移效率,进一步节省投产的时间成本~~
亿级以上数据量,Linux再次优化大概思路:
(1)假设有100张表,大概1亿左右的数据量,统计超过1000w的表有5张~
(2)在Windows下给每张大表(如1000w以上)建一个对应的Kettle作业,这个作业除了关联那张大表转换之外,还要再平均关联其他小表的转换(比如平均再关联20个小表)~
(3)不过虽然这里有5张表,但是建议总作业数不要超过4个,每个作业一般分配3g内存左右,4个作业总需物理内存就是12g左右内存,如果你生产内存远远大于12g,那没啥问题,建5个作业,对应5个Shell脚本~~
(4)每一个作业的创建按照前面Windows下提的几个方面优化,特别是并发执行,验证每个作业的正确性
(5)创建每一个Shell脚本,如demo_info.sh、demo_info2.sh,Shell脚本内容是通过span.sh脚本去调用指定的作业
(6)准备好这几张大表为主的Shell脚本后,就可以在Linux同时执行这几个Shell脚本并发数据迁移~
为什么不直接一个作业,然后这个作业并发执行多个大表转换即可?
因为Linux环境下,通过kettle的kitchen.sh脚本执行作业时,只会开启一个Java进程,JVM的内存是有上限的,作业并发再多转换,内存也就那么多,所以还是建议并行执行多个Shell脚本(对应多个作业)执行,意味着内存的叠加使用,可以最大化的利用生产比较好的CPU配置,更高效的完成数据的迁移~
当然,如果不想麻烦,就想一个脚本,一个作业,关联所有转换也可以,不过需要给Kettle配置JVM最大内存调到最优,比如一个作业整这么多转换~
本文为方便测试,这里就一个作业,创建一个数据迁移的Shell脚本test.
sh进行测试,内容如下:
#!/bin/sh
#一、使用自定义的JDK版本
export JAVA_HOME=/usr/java/jdk1.8.0.221
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
#二、执行kettle脚本,作业通过 kitchen.sh执行,转换通过pan.sh执行
#作业(多表)
$HOME/kettle/data-integration/kitchen.sh -file=$HOME/kettle/kettle_file/test.kjb > $HOME/kettle/kettle_log/transTest.log 2>&1 &
#转换(单表),这里先注释,如果数据迁移数据总量不一致,排查出哪张表数据不一致,需要单独迁移这张表,可以把这个放开,前面的作业注释
#$HOME/kettle/data-integration/pan.sh -file=$HOME/kettle/kettle_file/demo_info.kjb > $HOME/kettle/kettle_log/demo_info.log 2>&1 &
注意JDK等相关目录改成自己的~
将test.
sh脚本上传到kettle_sh目录下:
$HOME/kettle/kettle_sh
往下进行数据迁移~
3.4 数据迁移
实际做数据迁移的时间成本,不只是kettle作业脚本的执行时间,你还需要花一些时间进行其他相关操作,比如统计数据迁移前(旧库)和数据迁移后(新库)的数据量总量~
(1)先统计旧库
的总数据量
提前准备一个selectCount.
sql语句文件,统计所有数据表的数据量,内容如:
select sum(selectCount.ct) as totalRows from (
select count(*) as ct from test.demo_info
union all
select count(*) as ct from test.demo_info2
) selectCount;
上传selectCount.
sql语句文件到sql目录下:
$HOME/kettle/sql/
执行SQL语句文件,查看数据量(旧库)~
mysql > source C:
/Users/Administrator
/Desktop/selectCount.sql;
(2)执行数据迁移Shell脚本
/bin/sh $HOME/kettle/kettle_sh/test.sh
可以查看Shell脚本调用的Kettle作业的执行进程~
ps -ef|grep kjb
测试时,可以可以通过top命令,查看CPU性能消耗及内存使用情况~
(3)实时查看数据迁移日志
Shell脚本test.sh中,有指定日志文件存放路径~
tail -f $HOME/kettle/kettle_log/transTest.log
(4)最后统计新库的总数据量
提前准备一个selectCount.
sql语句文件,统计所有新库,数据表的数据量,内容如:
select sum(selectCount.ct) as totalRows from (
select count(*) as ct from test.demo_info
union all
select count(*) as ct from test.demo_info2
) selectCount;
执行SQL语句文件,查看数据量~
mysql > source
/home/mysql
/kettle/sql/selectCount.sql;
(4)数据量不一致需查看日志
vi /home/mysql/kettle/kettle_log
日志中会记录每张表的表输入记录、表输出记录,根据这个排查总数据量不一致,是哪张表导致的~
来源地址:https://blog.csdn.net/eagle89/article/details/128565346