资讯详情

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

学习目录

  • 测试库表和数据
  • 一、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 数据迁移

测试库表和数据

create database if not exists test default character set utf8 collate utf8_general_ci; use test; 

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 '年龄'; 

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_incremnt 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 '年龄';

csdn 下载1~ mpan 下载2~ 提取码：jj6l

MySQL如何使用load data infile、into outfile高效导入导出数据…

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 '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 '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下载及安装

https://sourceforge.net/projects/pentaho/files/

1.2 驱动包下载

https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html https://www.ibm.com/support/pages/node/382667 https://dev.mysql.com/downloads/connector/j/

1.3 驱动包版本

1.4 驱动包安装

data-integrationlib


data-integrationlibswt
data-integrationlibswtosx64
data-integrationlibswtwin64

二、Kettle使用

2.1 Windows

2.1.1 数据库连接测试

自定义后缀，这里建议使用.ktr）

… Driver class org.gjt.mm.mysql.Driver could not be found…** … Access denied for user ‘root’@’119.168.xxx.xxx’ (using password: YES) … Connection failed. Verify all connection parameters and confirm that the appropriate driver is installed. … … mysql> select user,host from mysql.user; mysql> update mysql.user set host='%' where user = 'root'; mysql> flush privileges; …

2.1.2 Kettle作业和转换

名词

说明

转换

一般文件后缀命名为.ktr ，单表迁移数据，构建表输入（读取数据），表输出（写入数据），Linux下使用kitchen.sh脚本调用执行

作业

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

2.1.2.1 创建Kettle转换

选项

说明

表输入

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

表输出

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

2.1.2.2 创建Kettle作业

${Internal.Entry.Current.Directory}/demo_info.ktr

2.1.3 数据迁移测试（未优化）

2.1.3.1 执行转换测试（单表）

2.1.3.2 执行作业测试（多表）

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;

2.1.4 数据迁移（性能优化）

  • 优化Kettle作业
  • 优化Kettle转换
  • 化Kettle连接DB参数
  • 优化JVM内存大小
  • 优化目标表的字段索引（临时删除）

2.1.4.1 优化Kettle作业

2.1.4.2 优化Kettle转换

2.1.4.3 优化Kettle连接DB参数

mysql > show variables like '%max_connections%';

max_connections=2000

Linux下可能会不生效，若不生效则需要配置Linux操作系统的limits.conf配置文件~ vi /etc/security/limits.conf 

mysql hard nofile 65535
mysql soft nofile 65535


ulimit -n

参数及赋值

参数说明

defaultFetchSize=10000

每次与数据库交互，读多少条数据加入内存中缓存，不设置默认把所有数据读取出来，容易内存溢出（OOM），我这里设置10000，大表CPU性能高建议设置最大50000，不能超过65535

useServerPrepStmts=true

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

cachePrepStmts=true

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

useCursorFetch=true

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

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

参数及赋值

参数说明

defaultFetchSize=5000

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

rewriteBatchedStatements=true

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

useServerPreStmts=false

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

useCompression=true

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

useCursorFetch=true

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

2.1.4.4 优化JVM内存大小

PENTAHO_DI_JAVA_OPTIONS="-Xms1024m" "-Xmx2048m"

参数

说明

-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内存溢出~

PENTAHO_DI_JAVA_OPTIONS="-Xms6144m" "-Xmx6144m"

PENTAHO_DI_JAVA_OPTIONS="-Xms3072m" "-Xmx3072m" 

cat /proc/meminfo |grep 'MemTotal'

cat /proc/meminfo |grep 'MemFree'


free -m

-Xms24g -Xmx24g -Xmn9g -XX:MaxPermSize=1024m

2.1.4.5 优化目标表的字段索引（临时删除）

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 重做数据迁移（性能优化后）

delete from test.demo_info;
delete from test.demo_info2;
alter table test.demo auto_increment 1;
alter table test.demo2 auto_increment 1;


delete from test.demo_info;
delete from test.demo_info2;
alter table test.demo auto_increment 1;
alter table test.demo2 auto_increment 1;


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

三、Linux

3.1 Kettle环境搭建

3.1.1 检查JDK

java -version

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

目录

说明

kettle_file

存放数据迁移准备好的kettle的转换ktr和作业kjb文件

kettle_sh

存放Shell脚本，脚本内容是多表，通过kitchen.sh调用作业kjb，单表的话，通过pan.sh调用转换ktr~

kettle_log

存放执行脚本以及调用Kettle作业和转换进行数据迁移打印的日志文件~

sql

存放统计数据量的SQL语句文件~

3.1.3 kettle安装包

$HOME/kettle/


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

3.1.4 验证kettle

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


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

Native memory allocation (mmap) failed to map 715849728 bytes for committing reserved memory.

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


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 检查驱动及网络连接

data-integration/lib/mysql-connector-java-8.0.26.jar

ping 旧数据库服务器的IP


telnet 就数据库服务器的IP 3306

3.3 再次性能优化

#!/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 &


$HOME/kettle/kettle_sh

3.4 数据迁移

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;


$HOME/kettle/sql/

mysql > source C:/Users/Administrator/Desktop/selectCount.sql; 

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


ps -ef|grep kjb


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


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;

mysql > source /home/mysql/kettle/sql/selectCount.sql; 

vi /home/mysql/kettle/kettle_log

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

标签： ktr自动复位电阻尺ktr微型自恢复直线位移传感器

锐单商城拥有海量元器件数据手册、 IC替代型号，打造 电子元器件IC百科大全！

锐单商城 - 一站式电子元器件采购平台