快速将大数据(超过1T)导入mysql库

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_41910261/article/details/85222548

1、源数据超过1T,且都是sql文件,文件大小不等,有的文件大于100G

2、硬件环境:一台liunx服务器,8核32G+5块1T的磁盘(a,b,c,d,e单块磁盘最大写入速度30M/S)

3、mysql版本为5.6

4、要求:24小时内将数据全部导入

问题分析:

      考虑到导入过程中还会生成操作日志和索引等类容,数据导入后所需的存储空间将远大于1T, 单独一块磁盘肯定无法满足需求,这里有两个方案:

      方案1,将2块1T的磁盘合成一块2T的磁盘,优点全库的数据都可以放在合并后的逻辑分区,操作起来简单,缺点读写性能对比单块磁盘略有下降,并不是想象中的1+1=2而是1+1<1,鉴于要求是快速导入数据,所以该方案不可取

      方案2,mysql的databasedir为a盘,启动一个mysql实例,创建5个数据库,将数据存放目录分别指向每块磁盘,优点:能最大化利用所有磁盘的IO资源,缺点:数据分散到5个库,不便于关联查询,考虑到本次需求不涉及关联查询,不影响实际使用。

实施方案2:

      第一步,优化mysql参数配置,考虑到本次需求的特性,为了获得最好的性能,对my.conf做如下优化,数据库引擎为INNODB

datadir=/a/mysql_data

1、innodb_buffer_pool_size=22G

2、innodb_log_buffer_size = 32M

3、innodb_log_file_size=4G

4、innodb_flush_log_at_trx_commit = 2

5、sync_binlog=500

6、thread_cache_size=64

7、innodb_write_io_threads = 8

8、innodb_read_io_threads = 8

9、innodb_thread_concurrency = 0

10、innodb_log_files_in_group = 3

第二部,mysql数据库存放在不同磁盘,这样同时向多个库导入数据时,每个库独享一块磁盘的IO资源,能最大化利用硬件资源

1、创建数据库database2,这时在/a/mysql_data目录下会创建一个目录database2,并且下面有一个文件db.opt

2、重名名database2 mv /a/mysql_data/database2 /a/mysql_data/database2_bak

3、创建目录 mkdir /b/mysql_extdata/database2

4、授权 chown -R mysql:mysql /b/mysql_extdata/database2

5、ln -s /b/mysql_extdata/database2 /a/mysql_data/database2

6、chown -R mysql:mysql /a/mysql_data/database2

7、mv /a/mysql_data/database2_bak/db.opt /a/mysql_data/database2

这样就实现了将数据库存放到非datadir指定的目录

后记:笔者测试结果为,每个库的导入数据能稳定在15G/h,总速度为75G/h,理论上14个小时可以完成任务,但实际耗时24小时,主要是因为导大表(单个sql文件大于100G)的效率较低,还在寻找其他优化方法。

有一种优化思路但未实际操作,记在此处与大家分享:

采用分库分表的方式,将大于100Gsql文件,平均拆分成5个文件,分表导入5个库中,整体效率可以提升20倍。

 

展开阅读全文

没有更多推荐了,返回首页