mysql并发导入数据 效果如何?

2023-12-02 15:32:13 浏览数 (1)

背景

备份时使用的mysqldump备份了数据库, 约100GB, (主要是某张表很大). 现在要使用该dump文件恢复数据.

传统的做法就是 mysql < xxx.sql 但, 这100GB太大了. 之前导入时间超过1天(IO也有瓶颈).(导出不到半小时).

比较急, 等不了那么多时间....

分析

导出是顺序IO, 速度肯定快. 导入是随机IO还有binlog等IO.

单个文件导入比较慢, 所以我们可以拆分下.sql文件. 把DDL单独出来, 剩下的insert再均匀分给多个文件, 让多个进程去并发导入.

(时间关系, 来不及拆分索引了. 最后建索引,速度还会更快, 约束等也是同理)

导入的时候, 可以先关闭会话级Binlog写入. 还可以设置下隔离级别, 双1之类的参数,

我这里演示的时候就不去整那么多了. 本次演示数据量就20GB就行. 多了我也懒得去造...

导入过程

导出过程:

代码语言：javascript复制
mysqldump -h127.0.0.1 -p123456 --single-transaction --set-gtid-purged=OFF --databases db1 > t20231202.sql

我这里是把.sql文件拆分为多个文件后再导入的. 如果空间不允许的话, 可以不用保存下来, 直接并发写入数据库即可.

拆分SQL文件

第一个参数是要拆分的sql文件, 第二个参数是拆分为sql文件的数量.

代码语言：javascript复制
time python3 split_mysqlduml_sql.py t20231202.sql 32
20GB不到一分钟就拆分完了20GB不到一分钟就拆分完了

导入DDL

查看下ddl是否有问题, 没得问题就可以导入了

注意gtid, 我测试导出的时候去掉了gtid的注意gtid, 我测试导出的时候去掉了gtid的
代码语言：javascript复制
[root@VM-32-21-centos t20231202]# time mysql -h127.0.0.1 -p123456 < t20231202.sql.ddl.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

real	0m6.355s
user	0m0.009s
sys	0m0.004s
[root@VM-32-21-centos t20231202]#

导入DDL还是挺快的.就6秒.

并发导入数据

现在该并发导入了, 这里我们使用shell来做就行

建议专门给一个目录(如果要落盘的话)建议专门给一个目录(如果要落盘的话)
代码语言：javascript复制
for filename in ls t20231202.sql.dml_*.sql;do
	nohup mysql -h127.0.0.1 -p123456 --init-command='set sql_log_bin=off' <  ${filename} &
done

现在就等慢慢导入了

速度还是能接受的.速度还是能接受的.

结束时间可以通过 ibd 文件的时间戳来确认. 导入的数据库表的最新时间就是导入完成的时间. (前提是导入完成).

代码语言：javascript复制
-rw-r----- 1 mysql mysql  25G Dec  2 14:57 sbtest1.ibd

算下来时间就是15分钟, 也就是15分钟导入完20GB的.sql文件. 这速度还是不错的. 100GB的话就是75分钟, 属于能接受的范围. 当然优化空间还很大

验证

登录数据库,验证数据是否正常即可. (本次测试的话, 可以使用 checksum table xxx 的方式校验SQL)

比较

和直接导入比较(直接导入有disbale key, 上面拆分sql的时候并没有disable key)

也就是少了这两

LOCK TABLES `sbtest1` WRITE;

/*!40000 ALTER TABLE `sbtest1` DISABLE KEYS */;

代码语言：javascript复制
nohup time mysql -h127.0.0.1 -p123456  --init-command='set sql_log_bin=off' < t20231202.sql &

时间也差不多15分钟, 没想到吧, 我也没想到..... (本次环境是cvm SSD centos7.9 x86)

代码语言：javascript复制
149.88user 11.91system 15:33.72elapsed 17%CPU (0avgtext 0avgdata 39816maxresident)k
39965328inputs 8outputs (13major 9988minor)pagefaults 0swaps

总结和建议

1. 尽量不要使用mysqldump备份数据量较大的数据库, 备份倒是快. 但恢复就慢了. 可以使用xtrbackup之类的工具备份.

2. 导入的时候尽量放后台运行. 不然窗口断开了, 导入进程就挂了...

3. 注意gtid问题. 如果不是搭建主从之类的话, 可以直接去掉gtid信息.

4. 如果表大小差不多的话, 建议按照表来拆分. 可以1个进程一张表的方式导入(后面有空了再去写吧).

20GB的.sql文件导入时间

单进程导入

32进程导入

15分钟

15分钟

TODO:

索引,约束等, 最后建.

加进度条.

一张表一个.sql文件

附脚本

python3写的, 无依赖. 直接执行就行. (也可以换成shell之类的来实现, 效率差不了多少的)

代码语言：javascript复制
#!/usr/bin/env python3
#split mysqldump file by ddcw @https://github.com/ddcw
import os,sys
try:
	filename = sys.argv[1]
except Exception as e:
	print(e)
	sys.exit(1)

#忽略空行和注释. 返回一条SQL(DDL/DML)
def read_gen(fd):
	while True:
		data = fd.readline()
		if data == 'n' or data[:3] == '--n' or data[:3] == '-- ' or data[:3] == '/*!' or data[:4] == 'SET ' or data[:12] == 'LOCK TABLES ' or data[:13] == 'UNLOCK TABLES':
			continue
		else:
			yield data

parallel = int(sys.argv[2]) if len(sys.argv) > 2 else 8
fddl = open(f'{filename}.ddl.sql','w')
fdml = [ open(f'{filename}.dml_{x}.sql','w') for x in range(parallel) ]

with open(filename,'r') as f:
	gen_data = read_gen(f)
	n = 0
	ddl = ''
	while True:
		data = next(gen_data)
		if data == '':
			break
		elif data[:12] == 'INSERT INTO ':
			fdml[n%parallel].write(data)
			n  = 1
		elif data[:4] == "USE ":
			#每个Use操作都要写入每个文件
			fddl.write(data)
			for x in range(parallel):
				fdml[x].write(data)
		elif data[-2:] == ";n":
			ddl  = data
			fddl.write(ddl)
			ddl = ''
		else:
			ddl  = data

fddl.close()
for x in range(parallel):
	fdml[x].close()
mysql

0 人点赞