加快mydumper与myloader导出导入

    mydumper与myloader是一个优秀的第三方mysql数据库逻辑备份恢复工具,使用多线程的导出与导入。弥补了mysqldump单线程的不足。本文描述的是如何加快mydumper与myloader的导出与导入供大家参考。

    有关mydumper与myloader其他事项可以参考: mydumper安装及安装故障汇总 mydumper备份mysql数据库示例 myloader恢复mysql数据库示例

1、基于MyIsam引擎导出导入 a、表不分块导出及导入 [root@GZAPP tmp]# mydumper -u inno -p xxx -B bsom -T tb_access_log -o /backup/tmp/ [root@GZAPP tmp]# ls -hltr   ###导出的数据文件为单个文件,大小在2.6GB total 2.6G -rw-r--r-- 1 root root 1.6K Jul 24 08:51 bsom.tb_access_log-schema.sql -rw-r--r-- 1 root root  214 Jul 24 08:52 metadata -rw-r--r-- 1 root root 2.6G Jul 24 08:52 bsom.tb_access_log.sql

###基于缺省线程数导入,且设定每个事务查询数为10000,此参数此时其实作用不大,因为表为myisam引擎 [root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -d /backup/tmp -v 3 -q 10000 [root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -d /backup/tmp -v 3 -q 10000 ** Message: 4 threads created ** Message: Creating table `tempdb`.`tb_access_log` ** Message: Thread 4 shutting down ** Message: Thread 1 restoring `bsom`.`tb_access_log` part 0 ** Message: Thread 3 shutting down ** Message: Thread 2 shutting down

root@localhost[tempdb]> show processlist; +---------+---------+----------+--------+---------+--------+---------+------------------------------------------------+ | Id      | User    | Host     | db     | Command | Time   | State   | Info                                           | +---------+---------+----------+--------+---------+--------+---------+------------------------------------------------+ | 4452079 | root    | localhost| tempdb | Query   |      0 | init    | show processlist                               | | 4453793 | inno    | localhost| tempdb | Sleep   |    420 |         | NULL                                           | | 4453794 | inno    | localhost| tempdb | Query   |      4 | update  | INSERT INTO `tb_access_log` VALUES (506873,"325| +---------+---------+----------+--------+---------+--------+---------+------------------------------------------------+ ###从上面的线程数可以看出只有一个单线程在执行insert操作

b、表分块导出及导入 ###下面的示例中使用500MB进行分块 [root@GZAPP tmp]# mydumper -u inno -p xxx -B bsom -T tb_access_log -F 500 -o /backup/tmp/ [root@GZAPP tmp]# ls -hltr total 2.6G      -rw-r--r-- 1 root root 1.6K Jul 24 08:21 bsom.tb_access_log-schema.sql -rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00001.sql -rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00002.sql -rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00003.sql -rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00004.sql -rw-r--r-- 1 root root 478M Jul 24 08:22 bsom.tb_access_log.00005.sql -rw-r--r-- 1 root root  214 Jul 24 08:22 metadata -rw-r--r-- 1 root root 241M Jul 24 08:22 bsom.tb_access_log.00006.sql ###由上可知,大表tb_access_log按接近500M被分割成了多个文件

[root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -t 6 -d /backup/tmp -v 3 ** Message: 6 threads created ** Message: Creating database `tempdb` ** Message: Creating table `tempdb`.`tb_access_log` ** Message: Thread 1 restoring `bsom`.`tb_access_log` part 3 ** Message: Thread 2 restoring `bsom`.`tb_access_log` part 5 ** Message: Thread 5 restoring `bsom`.`tb_access_log` part 4 ** Message: Thread 3 restoring `bsom`.`tb_access_log` part 6 ** Message: Thread 4 restoring `bsom`.`tb_access_log` part 1 ** Message: Thread 6 restoring `bsom`.`tb_access_log` part 2

#在下面的processlist可以看到,存在表级锁等待 +---------+-------+-----------+---------+---------+--------+-----------------------------+------------------------------------------------+ | Id      | User  | Host      | db      | Command | Time   | State                       | Info                                           | +---------+-------+-----------+---------+---------+--------+-----------------------------+------------------------------------------------+ | 4452079 | root  | localhost | bsom    | Query   |      0 | init                        | show processlist                               | | 4452167 | inno  | localhost | tempdb  | Sleep   |    769 |                             | NULL                                           | | 4452168 | inno  | localhost | tempdb  | Query   |     36 | update                      | INSERT INTO `tb_access_log` VALUES (6367402,"0,| | 4452169 | inno  | localhost | tempdb  | Query   |     21 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (12593865," | | 4452170 | inno  | localhost | tempdb  | Query   |     26 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (15643029,""| | 4452171 | inno  | localhost | tempdb  | Query   |      6 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (173947,"70 | | 4452172 | inno  | localhost | tempdb  | Query   |     15 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (9490507,"7 | | 4452173 | inno  | localhost | tempdb  | Query   |     30 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (3271602,"4 | +---------+---------+-----------+---------+---------+--------+-----------------------------+----------------------------------------------+

c、调整myisam有关参数后导入 [root@GZ-APP-BAK01 tmp]# time myloader -u innobk -p InnoBK -B tempdb -t 6 -d /backup/tmp -v 3 ** Message: 6 threads created ** Message: Creating table `tempdb`.`tb_mobile_access_log` ** Message: Thread 1 restoring `blossom`.`tb_mobile_access_log` part 3 ** Message: Thread 6 restoring `blossom`.`tb_mobile_access_log` part 6 ** Message: Thread 2 restoring `blossom`.`tb_mobile_access_log` part 5 ** Message: Thread 3 restoring `blossom`.`tb_mobile_access_log` part 4 ** Message: Thread 4 restoring `blossom`.`tb_mobile_access_log` part 1 ** Message: Thread 5 restoring `blossom`.`tb_mobile_access_log` part 2 ** Message: Thread 6 shutting down ** Message: Thread 5 shutting down ** Message: Thread 1 shutting down ** Message: Thread 2 shutting down ** Message: Thread 4 shutting down ** Message: Thread 3 shutting down

real    266m28.903s user    0m6.008s sys     0m1.681s

###调整以下相关参数,后尝试再次导入, concurrent_insert  AUTO 改成 ALWAYS bulk_insert_buffer_size 8388608 改成 256M myisam_sort_buffer_size 67108864 改成 128M

[root@GZ-APP-BAK01 tmp]# time myloader -u innobk -p InnoBK -B tempdb -t 6 -o -d /backup/tmp -v 3 ** Message: 6 threads created ** Message: Dropping table (if exists) `tempdb`.`tb_mobile_access_log` ** Message: Creating table `tempdb`.`tb_mobile_access_log` ** Message: Thread 1 restoring `blossom`.`tb_mobile_access_log` part 3 ** Message: Thread 2 restoring `blossom`.`tb_mobile_access_log` part 6 ** Message: Thread 3 restoring `blossom`.`tb_mobile_access_log` part 5 ** Message: Thread 4 restoring `blossom`.`tb_mobile_access_log` part 4 ** Message: Thread 6 restoring `blossom`.`tb_mobile_access_log` part 1 ** Message: Thread 5 restoring `blossom`.`tb_mobile_access_log` part 2 ** Message: Thread 2 shutting down ** Message: Thread 1 shutting down ** Message: Thread 6 shutting down ** Message: Thread 5 shutting down ** Message: Thread 3 shutting down ** Message: Thread 4 shutting down

real    253m42.460s   ###此时导入时间并无明显减少 user    0m5.924s sys     0m1.637s

2、基于innodb引擎的导出导入 a、表未分块导出,数据文件大小为3.9GB [root@GZAPP tmp]# ls -hltr total 3.9G -rw-r--r-- 1 root root 1.8K Jul 24 00:09 bscom.tb_message-schema.sql -rw-r--r-- 1 root root 3.9G Jul 24 00:25 bscom.tb_message.sql -rw-r--r-- 1 root root  215 Jul 24 09:14 metadata

###下面使用6个线程导入,实际上可以看到,只有1个线程在工作,因为数据文件只有1个 [root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -t 6 -d /backup/tmp -v 3 ** Message: 6 threads created ** Message: Creating table `tempdb`.`tb_message` ** Message: Thread 1 restoring `bscom`.`tb_message` part 0 ** Message: Thread 5 shutting down ** Message: Thread 2 shutting down ** Message: Thread 6 shutting down ** Message: Thread 3 shutting down ** Message: Thread 4 shutting down

b、表分块导出 [root@GZAPP tmp]# mydumper -u inno -p xxx -B bscom -T tb_message -F 500 -o /backup/tmp/ [root@GZAPP tmp]# ls -hltr total 3.9G -rw-r--r-- 1 root root 1.8K Jul 24 09:55 bscom.tb_message-schema.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00001.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00002.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00003.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00004.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00005.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00006.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00007.sql -rw-r--r-- 1 root root 481M Jul 24 09:55 bscom.tb_message.00008.sql -rw-r--r-- 1 root root  135 Jul 24 09:55 metadata -rw-r--r-- 1 root root  93M Jul 24 09:55 bscom.tb_message.00009.sql

###下面尝试使用6线程导入,可以看到有6个线程在并发导入 [root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -t 6 -d /backup/tmp/ -v 3 ** Message: 6 threads created ** Message: Creating database `tempdb` ** Message: Creating table `tempdb`.`tb_message` ** Message: Thread 2 restoring `bscom`.`tb_message` part 5 ** Message: Thread 1 restoring `bscom`.`tb_message` part 9 ** Message: Thread 3 restoring `bscom`.`tb_message` part 1 ** Message: Thread 4 restoring `bscom`.`tb_message` part 8 ** Message: Thread 5 restoring `bscom`.`tb_message` part 4 ** Message: Thread 6 restoring `bscom`.`tb_message` part 6 ** Message: Thread 1 restoring `bscom`.`tb_message` part 7 ** Message: Thread 6 restoring `bscom`.`tb_message` part 3 ** Message: Thread 2 restoring `bscom`.`tb_message` part 2 ** Message: Thread 3 shutting down ** Message: Thread 5 shutting down ** Message: Thread 4 shutting down ** Message: Thread 1 shutting down ** Message: Thread 2 shutting down ** Message: Thread 6 shutting down

3、小结 a、mydumper在导出的时候可以根据服务器可用资源来合理地设置线程数。 b、mydumper在导出的时候尽可能地指定chunk-filesize或者rows参数以分块导出。 c、myloader在针对myisam引擎时建议调整相关参数至合理值以提高无法提高性能,主要是表级锁的问题。 d、myloader在针对innodb引擎时建议调整参数至合理值以提高性能,如以下参数等:   innodb_buffer_pool_size   innodb_flush_log_at_trx_commit   innodb_log_buffer_size e、通过使用分块导出与导入可以显著利用并发来加快inndbo表导入。   f、注意mydumper导出时不会导出存储过程,函数,触发器等。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏散尽浮华

mysql操作命令梳理(5)-执行sql语句查询即mysql状态说明

在日常mysql运维中,经常要查询当前mysql下正在执行的sql语句及其他在跑的mysql相关线程,这就用到mysql processlist这个命令了。 m...

2426
来自专栏Porschev[钟慰]的专栏

Nodejs学习笔记(十五)--- Node.js + Koa2 构建网站简单示例

前言   前面一有写到一篇Node.js+Express构建网站简单示例https://cloud.tencent.com/developer/article/...

2.2K9
来自专栏抠抠空间

MySQL 之 视图、触发器、存储过程、函数、事物与数据库锁

浏览目录: 1.视图 2.触发器 3.存储过程 4.函数 5.事物 6.数据库锁 7.数据库备份 1.视图 视图:是一个虚拟表,其内容由查询定义。同真实的表...

3917
来自专栏GIS讲堂

gps实时位置的展示

很多时候,我们有这样的使用场景:外业人员在外作业,我们需要知道人员的当前的实时位置和人员信息,如何实现呢?本文将为大家简单的说明该场景下我们应该实现。

1964
来自专栏码字搬砖

hive动态分区

hive分区可以方便快速定位,查找( 设置分区,可以直接定位到hdfs上相应的文件目录下,避免全表扫描)。 hive分区可以分为静态分区、动态分区,另外静动...

1.1K3
来自专栏杨建荣的学习笔记

一条关于swap争用的报警邮件分析(一)(r7笔记第28天)

最近这些天有一台服务器总是会收到剩余swap过低的告警。 邮件内容大体如下: ############ ZABBIX-监控系统: --------------...

3664
来自专栏地方网络工作室的专栏

mysql 命令行补全工具 mycli

mysql 命令行补全工具 mycli 前言 我们在连接mysql数据库的时候,大多数情况下是使用gui图形界面的工具的。但是,有时候连接数据库还是命令行方便,...

2777
来自专栏用户2442861的专栏

cmake教程二,变量以及flow control

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/haluoluo211/article/d...

1012
来自专栏c#开发者

分析Oracle数据库日志文件(1)

分析Oracle数据库日志文件(1) 一、如何分析即LogMiner解释 从目前来看,分析Oracle日志的唯一方法就是使用Oracle公司提供的LogMin...

4095
来自专栏沃趣科技

ASM 翻译系列第十五弹:ASM Internal ASM File Directory

原作者:Bane Radulovic 译者: 郭旭瑞 审核: 魏兴华 DBGeeK社群联合出品 ASM File Directory 本篇主要介绍A...

3574

扫码关注云+社区

领取腾讯云代金券