0


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,大概二十多亿,数值够大,自增长主键实际使用问题不大~**

– 建库测试库

createdatabaseifnotexists test defaultcharacterset utf8 collate utf8_general_ci;use test;

– 创建测试表demo_info

use test;createtable test.demo_info(
    id int(7)primarykeynotnullauto_increment,
    name varchar(255)notnull,
    sex char(1)notnull,
    age int(3))ENGINE=InnoDBDEFAULTCHARSET=utf8 COLLATE=utf8_bin;altertable test.demo_info addindex index_name(name);altertable test.demo_info addindex index_age(age);altertable test.demo_info addindex index_name_age(name,age);altertable test.demo_info comment'测试表';altertable test.demo_info modifycolumn id int(7)notnullauto_incrementcomment'ID';altertable test.demo_info modifycolumn name varchar(255)notnullcomment'姓名';altertable test.demo_info modifycolumn sex char(1)notnullcomment'性别:1-男,0-女';altertable test.demo_info modifycolumn age int(3)comment'年龄';

– 创建测试表demo_info2

createtable test.demo_info2(
    id int(7)primarykeynotnullauto_increment,
    name varchar(255)notnull,
    sex char(1)notnull,
    age int(3))ENGINE=InnoDBDEFAULTCHARSET=utf8 COLLATE=utf8_bin;altertable test.demo_info2 addindex index_name(name);altertable test.demo_info2 addindex index_age(age);altertable test.demo_info2 addindex index_name_age(name,age);altertable test.demo_info2 comment'测试表2';altertable test.demo_info2 modifycolumn id int(7)notnullauto_incrementcomment'ID';altertable test.demo_info2 modifycolumn name varchar(255)notnullcomment'姓名';altertable test.demo_info2 modifycolumn sex char(1)notnullcomment'性别:1-男,0-女';altertable test.demo_info2 modifycolumn 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脚本及性能优化的实战演练吧~

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

二、Kettle使用

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下使用

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;deletefrom test.demo_info;altertable test.demo_info auto_increment1;deletefrom test.demo_info2;altertable test.demo_info2 auto_increment1;

重新来测试测试下通过作业关联两个转换的多表的数据迁移( 数据量比较大的话,建议先配置每个转换的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也需要进行动态的维护索引,比较消耗性能,因此在做数据迁移时建议先临时删除索引,特别是百千万级大表,如果存在字符串组合索引,在做数据迁移插入数据时效率非常低~

– 查看指定表的索引

showindexfrom test.demo_info;

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

– 临时删除非主键索引(数据迁移前)

altertable test.demo_info dropindex index_name;altertable test.demo_info dropindex index_age;altertable test.demo_info dropindex index_name_age;altertable test.demo_info2 dropindex index_name;altertable test.demo_info2 dropindex index_age;altertable test.demo_info2 dropindex index_name_age;

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

altertable test.demo_info addindex index_name(name);altertable test.demo_info addindex index_age(age);altertable test.demo_info addindex index_name_age(name,age);altertable test.demo_info2 addindex index_name(name);altertable test.demo_info2 addindex index_age(age);altertable test.demo_info2 addindex 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本地数据:

deletefrom test.demo_info;deletefrom test.demo_info2;altertable test.demo auto_increment1;altertable test.demo2 auto_increment1;

同样,清理Linux远程数据:

deletefrom test.demo_info;deletefrom test.demo_info2;altertable test.demo auto_increment1;altertable test.demo2 auto_increment1;

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"];thenPENTAHO_DI_JAVA_OPTIONS="-Xms128m -Xmx128m"fi

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

3g

3072m

:**

if[ -z "$PENTAHO_DI_JAVA_OPTIONS"];thenPENTAHO_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版本exportJAVA_HOME=/usr/java/jdk1.8.0.221
exportPATH=$JAVA_HOME/bin:$PATHexportCLASSPATH=.:$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

语句文件,统计所有数据表的数据量,内容如:**

selectsum(selectCount.ct)as totalRows from(selectcount(*)as ct from test.demo_info
  unionallselectcount(*)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

语句文件,统计所有新库,数据表的数据量,内容如:**

selectsum(selectCount.ct)as totalRows from(selectcount(*)as ct from test.demo_info
  unionallselectcount(*)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/JustinQin/article/details/120043364
版权归原作者 吾日三省贾斯汀 所有, 如有侵权,请联系我们删除。

“Kettle--MySQL生产数据库千万、亿级数据量迁移方案及性能优化”的评论:

还没有评论