原创

MySQL source导入sql文件过慢,解决办法

一、sql文件过慢,解决办法

最近公司某个项目服务出现异常,需要从原来数据库导出数据和表结构,重新导入到其他服务器的数据库中。

在执行过程中发现数据导入非常慢,耗时很长时间。8G左右的数据,耗时4天左右,这个明显感觉到异常,经验告诉我不可能需要如此多的时间。

但是因考虑到项目数据库存储的都是二进制blob的数据,可能服务器CPU,内存等原因而慢的。那就只能等待了。

经过一段时间后,异常问题还是没解决,旧服务还在运行,会产生部分增量数据,需要同步到新的数据库。这次查询了大量资料,采用另外一种方案执行。

数据库采用自建mysql5.6。

mysql -u root -p

use 项目数据库;

set global innodb_flush_log_at_trx_commit=0;

set global max_allowed_packet=1024*1024*20;

set global bulk_insert_buffer_size=32*1024*1024;

source /home/user/20210107/BUNDLE.sql

上面设置的参数只在 当前连接和新连接上生效,永久生效请修改my.cnf。实际使用只会在连接上设置,这些参数在生产环境不适合使用,所以导入成功后请 重启使这些设置失效。

不同版本的数据库可能还需要设置 set global innodb_buffer_pool_size=3210241024;

innodb_buffer_pool_size 该参数又可能是只读属性,需要在my.cnf 配置。

innodb_buffer_pool_size默认大小为128M。最大值取决于CPU的架构。

============================================================

提交事务的时候将 redo 日志写入磁盘中,所谓的 redo 日志,就是记录下来你对数据做了什么修改,比如对 “id=10 这行记录修改了 name 字段的值为 xxx”,这就是一个日志。如果我们想要提交一个事务了,此时就会根据一定的策略把 redo 日志从 redo log buffer 里刷入到磁盘文件里去。此时这个策略是通过 innodb_flush_log_at_trx_commit 来配置的,他有几个选项。

innodb_flush_log_at_trx_commit参数:

值为0 : 提交事务的时候,不立即把 redo log buffer 里的数据刷入磁盘文件的,而是依靠 InnoDB 的主线程main_thread每秒执行一次刷新到磁盘(调用文件系统的sync操作)。此时可能你提交事务了,结果 mysql 宕机了,然后此时内存里的数据全部丢失。

值为1 : 提交事务的时候,就必须把 redo log 从内存刷入到磁盘文件里去,只要事务提交成功,那么 redo log 就必然在磁盘里了(调用文件系统的sync操作)。注意,因为操作系统的“延迟写”特性,此时的刷入只是写到了操作系统的缓冲区中,因此执行同步操作才能保证一定持久化到了硬盘中。

值为2 : 提交事务的时候,把 redo 日志写入磁盘文件对应的 os cache 缓存里去,而不是直接进入磁盘文件,可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去(由存储引擎的main_thread 每秒将日志刷新到磁盘)。

可以看到,只有1才能真正地保证事务的持久性,但是由于刷新操作 fsync() 是阻塞的,直到完成后才返回,我们知道写磁盘的速度是很慢的,因此 MySQL 的性能会明显地下降。如果不在乎事务丢失,0和2能获得更高的性能。

注意事项

当设置为0,该模式速度最快,但不太安全,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

当设置为1,该模式是最安全的,但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下,binary log 只有可能丢失最多一个语句或者一个事务。但是因为每次提交都写入磁盘,IO的性能较差。

当设置为2,该模式速度较快,也比0安全,只有在操作系统崩溃或者系统断电的情况下,上一秒钟所有事务数据才可能丢失。

============================================================

max_allowed_packet参数:

MySQL根据配置文件会限制Server接受的数据包大小。

============================================================

bulk_insert_buffer_size参数:

增加bulk_insert_buffer_size(默认8M)

============================================================

二、source 命令乱码问题解决方法

第1种方案:在进入mysql时就设置编码值

mysql -u root -p --default-character-set=utf8

第2种方案:登录mysql后设置参数

use 项目数据库;  

set names utf-8 ;

source /home/user/20210107/BUNDLE.sql
正文到此结束
该篇文章的评论功能已被站长关闭