• 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_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;

    • 1
    • 2

    – 创建测试表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 '年龄';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    – 创建测试表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 '年龄';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    （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);

    • 1
    • 2
    • 3

    通过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);

    • 1
    • 2
    • 3

    导入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);

    • 1
    • 2
    • 3

    测试库表及数据构建完成，接下来进入正文学习如何制作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

    • 1

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

    data-integrationlibswt
data-integrationlibswtosx64
data-integrationlibswtwin64

    • 1
    • 2
    • 3

    二、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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    重新来测试测试下通过作业关联两个转换的多表的数据迁移（ 数据量比较大的话，建议先配置每个转换的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

    • 1
    • 2

    查看Linux最大连接显示是否设置成功~

    ulimit -n

    • 1

    重启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'

    • 1
    • 2
    • 3

    或（更简单的）

    free -m

    • 1

    可以看到我的"高性能"单核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;

    • 1

    前面我们创建测试表的时候，也建立了索引，因此需要先删除索引~

    – 临时删除非主键索引（数据迁移前）

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    – 重新建立非主键索引（数据迁移完成后）

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    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;

    • 1
    • 2
    • 3
    • 4

    同样，清理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;

    • 1
    • 2
    • 3
    • 4

    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);

    • 1
    • 2

    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);

    • 1
    • 2
    • 3

    两张表都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

    • 1

    如没有配置好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

    • 1
    • 2
    • 3
    • 4
    • 5

    注意：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/

    • 1

    解压完成安装：

    cd $HOME/kettle/
unzip pdi-ce-9.0.0.0-423.zip

    • 1
    • 2

    3.1.4 验证kettle

    执行测试kettle命令：

    cd $HOME/kettle/data-integration
./kitchen.sh --version
./pan.sh --version

    • 1
    • 2
    • 3

    分别出现关键信息如下，表明kettle解压安装成功！

    ...
Kitchen - Start of run.
...
Pan - Start of run.
...

    • 1
    • 2
    • 3
    • 4
    • 5

    如果服务器内存不足，执行测试可能会报错如下：
    在这里插入图片描述
    关键报错：
    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

    • 1
    • 2
    • 3

    实际CPU性能好一些的测试环境或者生产环境建议改为3g即3072m：

    if [ -z "$PENTAHO_DI_JAVA_OPTIONS" ]; then
    PENTAHO_DI_JAVA_OPTIONS="-Xms3072m -Xmx3072m"
fi

    • 1
    • 2
    • 3

    再次测试下

    cd $HOME/kettle/data-integration
./kitchen.sh --version
./pan.sh --version

    • 1
    • 2
    • 3

    3.2 检查驱动及网络连接

    kettle安装成功不代表两台服务器之间能开始进行数据迁移了，还需要检查相关数据库驱动以及新旧数据库两台服务器之间的网络是否连通~
    （1）检查驱动
    检查kettle安装目录lib目录下表是否存在相关数据库的连接驱动，如MySQL数据库连接驱动包
    data-integration/lib/mysql-connector-java-8.0.26.jar

    （2）检查网络及连接
    在新数据库服务器检查与旧数据库服务器的网络连通性以及连接权限指定端口~

    – 检查与旧数据库服务器的网络是否连通

    ping 旧数据库服务器的IP

    • 1

    – 是否有权限连接到指定端口（如MySQL的3306端口）

    telnet 就数据库服务器的IP 3306

    • 1

    当然如果要在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 &

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    注意JDK等相关目录改成自己的~
    将test.sh脚本上传到kettle_sh目录下：

    $HOME/kettle/kettle_sh

    • 1

    往下进行数据迁移~

    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;

    • 1
    • 2
    • 3
    • 4
    • 5

    上传selectCount.sql语句文件到sql目录下：

    $HOME/kettle/sql/

    • 1

    执行SQL语句文件，查看数据量（旧库）~
    mysql > source C:/Users/Administrator/Desktop/selectCount.sql;
    在这里插入图片描述

    （2）执行数据迁移Shell脚本

    /bin/sh $HOME/kettle/kettle_sh/test.sh

    • 1

    可以查看Shell脚本调用的Kettle作业的执行进程~

    ps -ef|grep kjb

    • 1

    在这里插入图片描述

    测试时，可以可以通过top命令，查看CPU性能消耗及内存使用情况~
    在这里插入图片描述

    （3）实时查看数据迁移日志
    Shell脚本test.sh中，有指定日志文件存放路径~

    tail -f $HOME/kettle/kettle_log/transTest.log

    • 1

    （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;

    • 1
    • 2
    • 3
    • 4
    • 5

    执行SQL语句文件，查看数据量~
    mysql > source /home/mysql/kettle/sql/selectCount.sql;
    在这里插入图片描述
    （4）数据量不一致需查看日志

    vi /home/mysql/kettle/kettle_log

    • 1

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

    原创不易，觉得有用的小伙伴来个三连（点赞+收藏+评论 ）+关注支持一下，非常感谢~
    在这里插入图片描述

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

  • 相关阅读:
    Buildroot 开发
    供应链平台 采购信息监控更新提醒
    node.js 1 开发环境搭建
    【kubernetes篇】使用Nfs实现kubernetes持久化存储
    三、PL/SQL中的SQL
    13_cgi
    牛客网：NC55 最长公共前缀
    从港口煤炭工人，到国企大数据负责人：曾经的网瘾少年是怎么做到的？
    Spring——AOP(Aspect Oriented Programming)
    TouchGFX之画布控件
  • 原文地址：https://blog.csdn.net/m0_67401761/article/details/126061434