加快MySQL数据import速度

背景:

生产服务器每天凌晨都会定时备份生产数据库,备份完成之后,通过脚本拉回本地,然后再导入本地的数据库。为何要导入本地数据库,这是为了能够在内网进行一些数据的处理和查询,而这些处理和查询比较耗时,所以一般都不会在生产环境进行操作。

先来看下本地服务器的配置:

在数据量不大的情况下,SQL 的压缩文件大约500M, 导入时间也大约花到2个小时。平时都是定时任务在凌晨跑,所以具体耗费多长时间并不影响使用。

随着业务数据的增长, 备份的 SQL 压缩文件也增加到了1.4G,数据涨了接近2倍。所以导入的速度越来越慢,而且非常明显,从凌晨的05:50, 一直到下午的13点左右,大约7个小时。这样的速度是无法接受的,必须要进行优化

数据库配置(my.cnf)作以下调整

各参数说明:

– innodb_buffer_pool_size : 缓存访问频繁的数据大小

– innodb_log_buffer_size : 增加缓冲区可以减少写日志I/O次数

– innodb_log_file_size : 增加日志文件大小可以减少写I/O

– innodb_write_io_threads : 用于写.ibd数据文件的线程数,默认为4,每个线程最多可以向操作系统发起256个I/O请求,此参数最大可以调整到64;

– innodb_flush_log_at_trx_commit:在发生异常崩溃的情况下,0和2都会丢失大概1秒的数据。一种折衷的方案是0和2都可以提高写的性能。设置为0,是因为InnoDB Log Buffer会每秒都会刷新到Transaction Logs (ib_logfile0, ib_logfile1),设置为2,只有事务提交时,才会把InnoDB Log Buffer刷新到Transaction Logs

修改好配置文件后这样重启数据库:

重启完成后,即可执行导入数据脚本。具体效果如何,可以通过atop命令查看,参考如下:

重点关注磁盘I/O 方面的情况,上面显示的是数据写入速度为 18.57 MB/s。

另外也可以参考InnoDB的状态

导入完数据后再重启下数据库

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181101G153HX00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

同媒体快讯

扫码关注云+社区

领取腾讯云代金券