加快mydumper與myloader匯出匯入

加快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匯出時不會匯出預存程序，函數，觸發器等。

本文永久更新連結地址：