Beschleunigen Sie den Export und Import von Mydumper und Myloader
- 黄舟Original
- 2017-02-13 11:04:181665Durchsuche
Mydumper und Myloader sind hervorragende Tools zur logischen Sicherung und Wiederherstellung von MySQL-Datenbanken von Drittanbietern, die Multithread-Export und -Import verwenden. Es gleicht die Mängel des einzelnen Threads von mysqldump aus. In diesem Artikel wird als Referenz beschrieben, wie Sie den Export und Import von mydumper und myloader beschleunigen können.
Weitere Informationen zu Mydumper und Myloader finden Sie unter:
Mydumper-Installation und Installationsfehlerzusammenfassung
Mydumper-Backup-MySQL-Datenbank-Beispiel
Myloader-Restore-MySQL-Datenbank-Beispiel
Export und Import basierend auf der MyIsam-Enginea und in Blöcken importiert[root@GZAPP tmp]# mydumper -u inno -p xxx -B bsom -T tb_access_log -o /backup/tmp/
[root@GZAPP tmp]# ls -hltr ### Die exportierte Datendatei ist eine einzelne Datei mit einer Größe von 2,6 GB
insgesamt 2,6 GB
-rw-r--r-- 1 Root Root 1,6 KB 24. Juli 08:51 bsom.tb_access_log-schema.sql
-rw-r-- r-- 1 Root Root 214 24. Juli 08:52 Metadaten
-rw-r--r-- 1 Root Root 2.6G 24. Juli 08:52 bsom.tb_access_log.sql
[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
]> Prozessliste;+------ ---+---------+----------+--------+---- -----+------ --+---------+------------------------- ----------------------+| Benutzer-ID | +---------+---- -----+----------+--------+--------- +--------+----- ----+----------------------------- ----------------------------------- |. localhost |. NULL |. 4453794 |. INSERT INTO `tb_access_log` -----+----------+-- --------+--------+---------+--- -----+---------+--- ---------------- -----------------+
### Aus der Anzahl der Threads oben ist ersichtlich, dass es nur einen einzigen Thread gibt, der den Einfügevorgang ausführt
###Im folgenden Beispiel werden 500 MB für das Chunking verwendet
###Wie oben zu sehen ist, wurden in der großen Tabelle tb_access_log fast 500 MB in mehrere Dateien aufgeteilt
[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
#在下面的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导出时不会导出存储过程，函数，触发器等。
以上就是加快mydumper与myloader导出导入的内容，更多相关内容请关注PHP中文网（www.php.cn）！