网络知识 娱乐 Kettle--MySQL生产数据库千万、亿级数据量迁移方案及性能优化

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性能并发执行脚本,在单个脚本执行性能瓶颈的基础上再次成倍数提高数据迁移效率,更高效完成旧库数据迁移到新库,节省实际投产时的时间成本~

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


学习目录

  • 测试库表及数据
  • 一、Kettle环境搭建
    • 1.1 Kettle下载及安装
    • 1.2 驱动包下载
    • 1.3 驱动包版本
    • 1.4 驱动包安装
  • 二、Kettle使用
    • 2.1 Windows
      • 2.1.1 数据库连接测试
      • 2.1.2 Kettle作业和转换
        • 2.1.2.1 创建Kettle转换
        • 2.1.2.2 创建Kettle作业
      • 2.1.3 数据迁移测试(未优化)
        • 2.1.3.1 执行转换测试(单表)
        • 2.1.3.2 执行作业测试(多表)
      • 2.1.4 数据迁移(性能优化)
        • 2.1.4.1 优化Kettle作业
        • 2.1.4.2 优化Kettle转换
        • 2.1.4.3 优化Kettle连接DB参数
        • 2.1.4.4 优化JVM内存大小
        • 2.1.4.5 优化目标表的字段索引(临时删除)
      • 2.1.5 重做数据迁移(性能优化后)
  • 三、Linux
    • 3.1 Kettle环境搭建
      • 3.1.1 检查JDK
      • 3.1.2 目录规划
      • 3.1.3 kettle安装包
      • 3.1.4 验证kettle
    • 3.2 检查驱动及网络连接
    • 3.3 再次性能优化
    • 3.4 数据迁移

测试库表及数据

(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(模拟新库环境)数据表的~
这里构建测试数据很简单,因为两张数据表的主键ID设置了自增长,直接新建文本,编辑文本另存为后缀.del文件,再使用文本编辑器(notepad++ yyds 仅个人推荐,非广告,哈哈哈!!!)编辑内容,这里先直接复制个不含ID的10w条数据(注意字段数据间的特殊分隔符是0x0f,如下图特殊符号所示)到文件中~

在这里插入图片描述
通过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条左右/秒~
17:46:56 ~
虽然也跟个人电脑及服务器的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 以及勾选关键的几个参数并赋值~
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_J