mysql并发导入数据 效果如何?
原创
背景
备份时使用的mysqldump备份了数据库, 约100GB, (主要是某张表很大). 现在要使用该dump文件恢复数据.
传统的做法就是 mysql < xxx.sql 但, 这100GB太大了. 之前导入时间超过1天(IO也有瓶颈).(导出不到半小时).
比较急, 等不了那么多时间....
分析
导出是顺序IO, 速度肯定快. 导入是随机IO还有binlog等IO.
单个文件导入比较慢, 所以我们可以拆分下.sql文件. 把DDL单独出来, 剩下的insert再均匀分给多个文件, 让多个进程去并发导入.
(时间关系, 来不及拆分索引了. 最后建索引,速度还会更快, 约束等也是同理)
导入的时候, 可以先关闭会话级Binlog写入. 还可以设置下隔离级别, 双1之类的参数,
我这里演示的时候就不去整那么多了. 本次演示数据量就20GB就行. 多了我也懒得去造...
导入过程
导出过程:
mysqldump -h127.0.0.1 -p123456 --single-transaction --set-gtid-purged=OFF --databases db1 > t20231202.sql我这里是把.sql文件拆分为多个文件后再导入的. 如果空间不允许的话, 可以不用保存下来, 直接并发写入数据库即可.
拆分SQL文件
第一个参数是要拆分的sql文件, 第二个参数是拆分为sql文件的数量.
time python3 split_mysqlduml_sql.py t20231202.sql 32
导入DDL
查看下ddl是否有问题, 没得问题就可以导入了
[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来做就行
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 文件的时间戳来确认. 导入的数据库表的最新时间就是导入完成的时间. (前提是导入完成).
-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 */;
nohup time mysql -h127.0.0.1 -p123456 --init-command='set sql_log_bin=off' < t20231202.sql &时间也差不多15分钟, 没想到吧, 我也没想到..... (本次环境是cvm SSD centos7.9 x86)
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之类的来实现, 效率差不了多少的)
#!/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()
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。