摘要:所以满足均为小事务即可。怀疑官方文档有误。所以,这里证明了确实可用于拆分备份文件的大小的。结论默认设置下导出的备份文件,符合导入需求,不会造成大事务。
作者:陈俊聪背景
有人问mysqldump出来的insert语句,是否可以按每 10 row 一条insert语句的形式组织。
思考1:参数--extended-insert回忆过去所学:
我只知道有一对参数
--extended-insert(默认值)
表示使用长 INSERT ,多 row 在合并一起批量 INSERT,提高导入效率
--skip-extended-insert
一行一个的短INSERT
均不满足群友需求,无法控制按每 10 row 一条 insert 语句的形式组织。
之前一直没有考虑过这个问题。这个问题的提出,相信主要是为了“避免大事务”。所以满足 insert 均为小事务即可。
下面,我们来探讨一下以下问题:
什么是大事务?
那么 mysqldump 出来的 insert 语句可能是大事务吗?
什么是大事务?定义:运行时间比较长,操作的数据比较多的事务我们称之为大事务。
大事务风险:
∘ 锁定太多的数据,造成大量的阻塞和锁超时,回滚所需要的时间比较长。
∘ 执行时间长,容易造成主从延迟。
∘ undo log膨胀
避免大事务:我这里按公司实际场景,规定了,每次操作/获取数据量应该少于5000条,结果集应该小于2M
mysqldump出来的SQL文件有大事务吗?前提,MySQL 默认是自提交的,所以如果没有明确地开启事务,一条 SQL 语句就是一条事务。在 mysqldump 里,就是一条 SQL 语句为一条事务。
按照我的“避免大事务”自定义规定,答案是没有的。
原来,mysqldump 会按照参数--net-buffer-length,来自动切分 SQL 语句。默认值是 1M。按照我们前面定义的标准,没有达到我们的 2M 的大事务标准。
--net-buffer-length 最大可设置为 16777216,人手设置大于这个值,会自动调整为 16777216,即 16M。设置 16M,可以提升导出导入性能。如果为了避免大事务,那就不建议调整这个参数,使用默认值即可。
[root@192-168-199-198 ~]# mysqldump --net-buffer-length=104652800 -uroot -proot -P3306 -h192.168.199.198 test t >16M.sql mysqldump: [Warning] option "net_buffer_length": unsigned value 104652800 adjusted to 16777216设置大于16M,参数被自动调整为16M
注意,指的是 mysqldump 的参数,而不是 mysqld 的参数。官方文档提到: If you increase this variable, ensure that the MySQL server net_buffer_length system variable has a value at least this large.
意思是 mysqldump 增大这个值,mysqld 也得增大这个值,测试结论是不需要的。怀疑官方文档有误。
不过,在导入的时候,受到服务器参数 max_allowed_packet 影响,它控制了服务器能接受的数据包的最大大小,默认值是 4194304,即 4M。所以导入数据库时需要调整参数 max_allowed_packet 的值。
set global max_allowed_packet=16*1024*1024*1024;
不调整的话,会出现以下报错:
[root@192-168-199-198 ~]# mysql -uroot -proot -P3306 -h192.168.199.198 test <16M.sql mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2006 (HY000) at line 46: MySQL server has gone away相关测试
最后,我放出我的相关测试步骤
mysql> select version(); +------------+ | version() | +------------+ | 5.7.26-log | +------------+ 1 row in set (0.00 sec)
造100万行数据
create database test; use test; CREATE TABLE `t` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into t values (1,1,"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyztuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"); insert into t select * from t; #重复执行20次 # 直到出现Records: 524288 Duplicates: 0 Warnings: 0 # 说明数据量达到100多万条了。 mysql> select count(*) from t; +----------+ | count(*) | +----------+ | 1048576 | +----------+ 1 row in set (1.04 sec)
数据大小如下,有 284MB
[root@192-168-199-198 test]# pwd /data/mysql/mysql3306/data/test [root@192-168-199-198 test]# du -sh t.ibd 284M t.ibd --net-buffer-length=1M [root@192-168-199-198 ~]# mysqldump -uroot -proot -S /tmp/mysql3306.sock test t >1M.sql [root@192-168-199-198 ~]# du -sh 1M.sql 225M 1M.sql [root@192-168-199-198 ~]# cat 1M.sql |grep -i insert |wc -l 226
默认 --net-buffer-length=1M 的情况下,225M 的SQL文件里有 226 条 insert ,平均下来确实就是每条 insert 的 SQL 大小为 1M。
--net-buffer-length=16M
[root@192-168-199-198 ~]# mysqldump --net-buffer-length=16M -uroot -proot -S /tmp/mysql3306.sock test t >16M.sql [root@192-168-199-198 ~]# du -sh 16M.sql 225M 16M.sql [root@192-168-199-198 ~]# cat 16M.sql |grep -i insert |wc -l 15
默认--net-buffer-length=16M 的情况下,225M 的 SQL 文件里有 15 条 insert,平均下来确实就是每条 insert 的 SQL 大小为 16M。
所以,这里证明了 --net-buffer-length 确实可用于拆分 mysqldump 备份文件的SQL大小的。
insert 次数越多,交互次数就越多,性能越低。 但鉴于上面例子的 insert 数量差距不大,只有 16 倍,性能差距不会很大(实际测试也是如此)。我们直接对比 --net-buffer-length=16K 和 --net-buffer-length=16M 的情况,他们insert次数相差了 1024 倍。
[root@192-168-199-198 ~]# time mysql -uroot -proot -S /tmp/mysql3306.sock test <16K.sql mysql: [Warning] Using a password on the command line interface can be insecure. real 0m10.911s #11秒 user 0m1.273s sys 0m0.677s [root@192-168-199-198 ~]# mysql -uroot -proot -S /tmp/mysql3306.sock -e "reset master"; mysql: [Warning] Using a password on the command line interface can be insecure. [root@192-168-199-198 ~]# time mysql -uroot -proot -S /tmp/mysql3306.sock test <16M.sql mysql: [Warning] Using a password on the command line interface can be insecure. real 0m8.083s #8秒 user 0m1.669s sys 0m0.066s
结果明显。--net-buffer-length 设置越大,客户端与数据库交互次数越少,导入越快。
结论mysqldump 默认设置下导出的备份文件,符合导入需求,不会造成大事务。性能方面也符合要求,不需要调整参数。
参考链接:
https://dev.mysql.com/doc/ref...
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/110722.html
摘要:肖鹏微博数据库那些事儿肖鹏,微博研发中心技术经理,主要负责微博数据库相关的业务保障性能优化架构设计,以及周边的自动化系统建设。经历了微博数据库各个阶段的架构改造,包括服务保障及体系建设微博多机房部署微博平台化改造等项目。 showImg(https://segmentfault.com/img/bV24Gs?w=900&h=385); 对于手握数据库的开发人员来说,没有误删过库的人生是...
摘要:肖鹏微博数据库那些事儿肖鹏,微博研发中心技术经理,主要负责微博数据库相关的业务保障性能优化架构设计,以及周边的自动化系统建设。经历了微博数据库各个阶段的架构改造,包括服务保障及体系建设微博多机房部署微博平台化改造等项目。 showImg(https://segmentfault.com/img/bV24Gs?w=900&h=385); 对于手握数据库的开发人员来说,没有误删过库的人生是...
摘要:肖鹏微博数据库那些事儿肖鹏,微博研发中心技术经理,主要负责微博数据库相关的业务保障性能优化架构设计,以及周边的自动化系统建设。经历了微博数据库各个阶段的架构改造,包括服务保障及体系建设微博多机房部署微博平台化改造等项目。 showImg(https://segmentfault.com/img/bV24Gs?w=900&h=385); 对于手握数据库的开发人员来说,没有误删过库的人生是...
摘要:说明本文绝大部分内容来源技术内幕存储引擎一书,部分图片来源网络。脏页存储于,表示缓冲池中的页与磁盘页不一致,等待被调度刷新。脏页数量太多,比如占据缓冲池比例大于时,强制进行刷新,比例可调。 说明 本文绝大部分内容来源《MySQL技术内幕:InnoDB存储引擎》一书,部分图片来源网络。#我是搬运工# InnoDB 体系结构 后台线程 InnoDB存储引擎是多线程模型,其后台有多个不同的后...
摘要:要定期做备份,备份的周期要充分考虑系统可以承受的恢复时间。基于时间点恢复的操作步骤如果是上午点发生了误操作,可以用以下语句用备份和将数据恢复到故障前跳过故障时的时间点,继续执行后面的,完成恢复。 一、 备份恢复策略 进行备份或恢复操作时需要考虑一些因素: 确定要备份的表的存储引擎是事务型还是非事务型,两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。 确定使用全备份还是增量...
阅读 1563·2021-10-09 09:44
阅读 2640·2021-09-27 13:36
阅读 1249·2021-09-22 15:33
阅读 1096·2021-09-22 15:23
阅读 1008·2021-09-06 15:02
阅读 1544·2019-08-29 16:14
阅读 2775·2019-08-29 15:26
阅读 2279·2019-08-28 18:08