今天本是晴天,到了下午天一下子暗起来了,不久便下起了小雨,没办法外出,就在家鼓捣起了MySQL数据库服务。
前一段时间做内训,演示数据库复制服务,通过数据库客户端工具Navicat Premium导入样例数据库时,只导入了数据库结构,数据却一条都没导入,当时就觉得纳闷,好在不影响演示,打算有空了查一下。今天恰好有点时间,从下午四点多,一直鼓捣到晚上,差不多搞明白了原因,同时也尝试了通过mysql数据库服务的参数调优来提高大数据量的导入性能。
01 实验环境
1、本地win10,需要安装并配置好mysql,怎么安装百度查;
2、安装客户端工具Navicat Premium,怎么安装百度查;
3、华为云&天翼云上开通mysql数据库服务,我选择最低配1c2g,SAS盘,并配置了2M的EIP;
4、下载mysql示例数据库,大概有近400万条数据,具体下载地址忘了,如果需要可以@我。
02 Navicat 导入数据失败
先从日志里查找原因,提示是语法错误,不断的去尝试,发现在Navicat工具的mysql命令行窗口,仅仅输入source命令居然也报错,百思不得其解,各种百度,可能的原因是source命令是mysql客户端的工具集,Navicat可能不支持,要导入还是要直接在命令行窗口执行。
03 本地批量数据导入
1、以系统管理员的权限打开cmd窗口;
2、运行 net start mysql,显示“服务已经启动成功”,如下图:
3、进入到employees样例数据库文件夹,执行导入数据库命令:
mysql -h 125.124.74.17 -P 8635 -uroot -pTest@1234 < employees.sql
“<”符号表示导入数据库文件,执行结果如下:
图中导入数据成功,耗时4分42秒,通过Navicat工具能够直观的看到导入的数据库、表、视图及数据等,如下图:
04 参数调优
通过不断地尝试,针对大数据量的批量导入,在华为云&天翼云服务端可以调优的参数有2个,分别是
innodb_flush_log_at_trx_commit和bulk_insert_buffer_size,我们可以在华为云&天翼云mysql服务的参数组界面修改参数,如下图:
innodb_flush_log_at_trx_commit
该参数控制重做日志写入磁盘的过程,系统默认为1,表示事务提交时,会将重做日志缓冲写入磁盘,并且立即刷新.也即是说一旦事务完成,数据立即写盘,以确保数据不会丢失。但对于批量导入大量数据的情况,会导致频繁写盘,影响导入性能。我们这里将该参数设置为0,表示每间隔1秒再写盘。这里我们把参数从1改为0,如图:
bulk_insert_buffer_size
用来缓存批量插入数据的时候临时缓存写入数据,大数据的sql文件能达到几百M乃至上G,默认8M,确实有点小,这里我们改到120M。
修改参数后保险期间需重启数据库实例。
05 性能优化效果
先是修改了
innodb_flush_log_at_trx_commit参数,导入数据耗时00:04:14,快了28秒;
然后再修改bulk_insert_buffer_size参数,耗时00:03:42,此次调优提升了32秒;
相比没有调优之前,整体快了60秒,相当于性能提升了了27%!
06 总结
通常用户在云端开了RDS服务后,需要将本地私有云的数据批量导入到云端,对数据量达到千万级的情况,需要综合来考虑,比如数据可以分批导入,为提升导入性能,可以调整RDS的相关参数,以加快数据的导入速度,使客户的业务中断时间降低到最小。当然除了手动导入,也可以通过购买数据库复制服务来实现在线的数据导入,好处是能够避免人工导入带来的疏忽,并能实现业务的平滑迁移。
最后附上导入的数据库的相关表的数据条数:
select count(*) from salaries 2844047 select count(*) from employees 300024 select count(*) from titles 443308 select count(*) from dept_emp 331603
2024泰安大学生有哪些资助政策 泰安学生资助管理中心
泰安高等教育阶段可以享受的资助政策有国家奖学金、省政府奖学金、国家励志奖学金、省政府励志奖学金、国家助学金等,详见正文(0)人阅读时间:2024-09-192024肥城公交免费乘车活动 肥城公交价格
9月22日是“世界无车日”,为鼓励更多的市民选择乘坐公共交通出行,减少城市空气污染,肥城公交邀您免费乘车。(0)人阅读时间:2024-09-192024泰安学前教育阶段学生资助政策有哪些?
2024泰安市学前教育阶段学生资助政策包括免保教费和政府助学金,详细资助标准和资助对象见正文(0)人阅读时间:2024-09-192024泰安义务教育阶段学生资助政策有哪些?
泰安义务教育阶段学生资助政策是生活补助,资助对象为全日制正式学籍的家庭经济困难在校生(0)人阅读时间:2024-09-19如何申请多多买菜门店(如何申请多多买菜门店怎么提成)
适用:华为Nova8SE&&iPhone12,华为EMUI11.0.0&&苹果IOS14.1,微信7.0.21。加盟多多买菜需要注意,多多买菜加盟的团长门店是本身就有经营场..2024-09-20大熊猫的特点是什么 大熊猫的特点是什么三年级
大熊猫体型肥硕似熊、丰腴富态,头圆尾短,饲养的熊猫略重,一般雄性个体稍大于雌性。头部和身体毛色黑白相间分明,但黑非纯黑,白也不是纯白,..2024-09-20飘窗安全吗(晚上睡飘窗安全吗)
现在买房,90%的房子户型都是有飘窗的。有些业主就疑惑,飘窗的设计安全吗?会不会存在着安全隐患,尤其是高层住宅,飘窗就更危险了?接下来,且听..2024-09-20基金加仓和补仓是什么意思 加仓和补仓的区别基金什么时候加仓?什么时候...
基金加仓和补仓本质上都是投资者之前已经持有该基金,然后再次购买该基金。但是一般是把现在盈利再买该基金叫做加仓;而如果现在亏损再..2024-09-20