Press "Enter" to skip to content

Kettle–MySQL生产数据库千万、亿级数据量迁移方案及性能优化

本站内容均来自兴趣收集,如不慎侵害的您的相关权益,请留言告知,我们将尽快删除.谢谢.

大家好,我是贾斯汀!

 

【实战前言】

 

(1)不管你是学生,还是已经工作了的小伙伴,可能你在过去、现在或者未来,会遇到这样的问题,公司/项目用的是 Oracle/DB2/MySQL 等关系型数据库,因公司发展需求,需要完成旧数据库数据安全迁移到新数据库的重要使命,新旧数据库可能是同一种类型的数据库,也可能是不同类型的数据库,相同类型数据库还好,比如都是MySQL数据库,那幺你主要只需要考虑如何将数据安全、高效的完成迁移就好,而不同类型的数据库,比如从DB2迁移数据到MySQL,这种情况就需要在进行数据迁移之前,先按照新的数据库MySQL的建表规范,正确完成数据表的重建工作~

 

(2)本文主要分享我个人在实际工作当中,==如何使用Kettle这款基于纯Java实现(意味着扩平台特性,也就是Windows/Linux等操作系统通用)的开源ETL数据挖掘工具,经过性能优化(性能是默认效率的5 ~ 10倍以上)之后,实现新旧数据库之间数据的安全高效迁移~

 

(3)我个人也是工作用到,一开始完成UAT测试环境模拟迁移DB2数据库一千万左右数据量到MySQL数据库的过程,到最后在实际生产环境安全高效完成五千万数据量从DB2迁移到MySQL的方案落实,不过由于个人学习环境限制,就不装DB2数据库了,本文将以 本地MySQL(模拟旧库) 迁移数据到 远程MySQL(模拟新库) ,进行实战演练,原理是一样的,要说区别主要在于数据库类型不同,在进行数据迁移之前,需要先按规范建立好新数据库的相关库表~

 

(4)Kettle脚本的制作、测试以及性能优化这部分的工作,主要在Windows下通过可视化界面来完成,实际的UAT测试环境以及生产环境数据库服务器大多都是在Linux的,因此Linux也需要搭建一套Kettle环境,并且将在Windows下性能优化好的Kettle脚本,放到Linux环境,同时如果数据量非常大的话( 亿级以上数据量 ),还可以根据大表制作多个Shell脚本来执行准备好的Kettle作业脚本,利用更良好的CPU性能并发执行脚本,在单个脚本执行性能瓶颈的基础上再次成倍数提高数据迁移效率,更高效完成旧库数据迁移到新库,节省实际投产时的时间成本~

 

前言废话有点多了,哈哈哈,进入正文吧~

 

学习目录

 

测试库表及数据

 

(1)创建测试库表

 

分别在Windows(模拟旧库环境)及Linux(模拟新库环境),创建一个测试库test以及两张测试库表 test.demo_infotest.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(模拟新库环境)数据表的~

10w

通过MySQL的 load data infile 语法指定字段快速插入数据,用这些数据,先来简单的进行数据迁移的测试,后面性能优化之后再用 100w1000w 更多的数据量来进行数据迁移测试,当然实际环境的话最好按你们实际数据库大概有多少数据量,去构建多少的测试数据~

 

这里提供下,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脚本及性能优化的实战演练吧~

 

一、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-integrationlib

 

如果你觉得不够稳妥的话,比如放到Linux会不会加载不到驱动,那幺以下这几个目录,都放驱动包,妥妥的:

 

data-integrationlibswt
data-integrationlibswtosx64
data-integrationlibswtwin64

 

二、Kettle使用

 

2.1 Windows

 

Windows下,主要通过运行 Spoon.bat ,打开Kettle客户端可视化操作页面,创建数据迁移用到的 ktr 转换脚本、 kjb 作业脚本( 转换作业 后面会介绍),以及相关数据库连接配置、读写性能优化配置等,配置完成后最后一般都是上传kettle脚本到Linux上执行,当然也可直接在Windows下Kettle客户端可视化页面直接执行转换或作业脚本,完成数据库之间的数据迁移,但是生产一般是在Linux,而且Windows执行数据迁移的效率一般要比Linux慢一些~

 

2.1.1 数据库连接测试

 

双击 data-integrationSpoont.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-integrationlib 目录下即可~

 

 

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下使用 kitchen.sh 脚本调用执行

 

作业

 

文件后缀为 .kjb ,可以关联多个 .ktr 执行,实现多表(串行/并行)迁移数据,Linux下使用 pan.sh 脚本调用执行

 

2.1.2.1 创建Kettle转换

 

在前面 数据库连接测试 时,新建的 test.ktr 就是一个Kettle 转换 文件,只不过前面只是测试了 DB连接 是否正常,至于 表输入表输出 的相关配置,实现数据表数据迁移的相关配置还没作具体说明~

 

选项

 

说明

 

表输入

 

简单理解为从指定的数据表(旧库)读取数据的过程~

 

表输出

 

简单理解为插入数据到指定的数据表(新库)的过程~

 

(1)同前面创建 test.ktr 方式,创建 demo_info 数据表对应的转换文件 demo_info.ktr

 

(2)双击 表输入 进行编辑,如下图:

 

(3)双击 表输出 进行编辑,如下图:

 

(4)同样创建测试表 demo_info2 对应的转换文件 demo_info2.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倍以上的效率,主要从以下几个方面的优化:

 

作业
转换
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

 

是否在使用服务端的预编译语句,true表示以占位符的方式发送SQL到服务端进行拼接

 

cachePrepStmts=true

 

是否客户端缓存预处理语句

 

useCursorFetch=true

 

是否允许部分数据到客户端就进行处理,如果为false表示所有数据到达客户端后,才进行处理

 

参数不在于多,在于使用几个简单高效的参数即可,其他参数可以查看MySQL性能优化官方文档:

https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html

– 表输出(写)核心参数优化

 

双击DB连接(写库),对选项下的相关参数进行配置:

 

– – 参数说明

 

参数及赋值

 

参数说明

 

defaultFetchSize=5000

 

每次与数据库交互,从内存中读取多少条数据写入数据表,不设置默认把所有数据写入, rewriteBatchedStatements设置true,该参数会失效

 

rewriteBatchedStatements=true

 

是否开启批量写入,true表示开启,原多条insert变成单条insert执行

 

useServerPreStmts=false

 

是否使用服务端预编译,设置为false,表示在客户端编译好

 

useCompression=true

 

是否使用压缩,使用压缩优化客户端与服务端传输效率

 

useCursorFetch=true

 

是否允许部分数据到客户端就进行处理,如果为false表示所有数据到达客户端后,才进行处理

 

2.1.4.4 优化JVM内存大小

 

修改Kettle安装目录data-integration/ Spoon.bat (Linux是 Spoon.sh )文件中的 PENTAHO_DI_JAVA_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分钟内还是能轻松实现亿级数据量的高效迁移的~

 

三、Linux

 

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_JAVA_OPTIONS 定位):

 

if [ -z "$PENTAHO_DI_JAVA_OPTIONS" ]; then
    PENTAHO_DI_JAVA_OPTIONS="-Xms128m -Xmx128m"
fi

 

实际CPU性能好一些的测试环境或者生产环境建议改为 3g3072m

 

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

 

日志中会记录每张表的表输入记录、表输出记录,根据这个排查总数据量不一致,是哪张表导致的~

 

原创不易,觉得有用的小伙伴来个 三连(点赞+收藏+评论 )+关注 支持一下,非常感谢~

 

先自我介绍一下,小编13年上师交大毕业,曾经在小公司待过,去过华为OPPO等大厂,18年进入阿里,直到现在。深知大多数初中级java工程师,想要升技能,往往是需要自己摸索成长或是报班学习,但对于培训机构动则近万元的学费,着实压力不小。自己不成体系的自学效率很低又漫长,而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家,初衷也很简单,就是希望帮助到想自学又不知道该从何学起的朋友,同时减轻大家的负担。添加下方名片,即可获取全套学习资料哦

Be First to Comment

发表评论

您的电子邮箱地址不会被公开。