Accelerate mydumper and myloader Export and Import

Accelerate mydumper and myloader Export and Import

Mydumper and myloader are excellent third-party mysql database logical backup and recovery tools that use multi-thread export and import. Makes up for the insufficiency of mysqldump single thread. This article describes how to accelerate the export and import of mydumper and myloader for your reference.

For more information about mydumper and myloader, see:

• Mydumper installation and installation fault Summary
• Example of mydumper backing up mysql database
• Example of myloader restoring mysql database

1. Export and Import Based on MyIsam Engine
A. tables are not exported and imported in parts.
[Root @ GZAPP tmp] # mydumper-u inno-p xxx-B bsom-T tb_access_log-o/backup/tmp/
[Root @ GZAPP tmp] # ls-hltr ### the exported data file is a single file with a size of 2.6 GB.
Total 2.6 GB
-Rw-r -- 1 root 1.6 K Jul 24 bsom. tb_access_log-schema. SQL
-Rw-r -- 1 root 214 Jul 24 08:52 metadata
-Rw-r -- 1 root 2.6G Jul 24 08:52 bsom. tb_access_log. SQL

### Import Based on the default number of threads and set the number of queries for each transaction to 10000. This parameter does not actually work because the table is the myisam engine.
[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 |
+ --------- + ---------- + -------- + --------- + -------------------------------------------------- +
### From the preceding thread count, we can see that only one single thread is executing the insert operation.

B. Partition Export and Import of tables
### Use MB for chunking in the following example
[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.6 GB
-Rw-r -- 1 root 1.6 K Jul 24 bsom. tb_access_log-schema. SQL
-Rw-r -- 1 root 478 M Jul 24 08:21 bsom. tb_access_log.20.1. SQL
-Rw-r -- 1 root 478 M Jul 24 08:21 bsom. tb_access_log.00002. SQL
-Rw-r -- 1 root 478 M Jul 24 08:21 bsom. tb_access_log.20.3. SQL
-Rw-r -- 1 root 478 M Jul 24 08:21 bsom. tb_access_log.00004. SQL
-Rw-r -- 1 root 478 M Jul 24 08:22 bsom. tb_access_log.20.5. SQL
-Rw-r -- 1 root 214 Jul 24 metadata
-Rw-r -- 1 root 241 M Jul 24 08:22 bsom. tb_access_log.00006. SQL
### As shown in the preceding figure, the large table tb_access_log is divided into multiple files by nearly MB.

[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

# The following processlist shows that table-Level Lock wait exists.
+ --------- + ------- + ----------- + --------- + -------- + ----------------------------- + ------------------------------------------------ +
| 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. Adjust myisam parameters and import them
[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. 903 s
User 0m6. 008 s
Sys 0m1. 681 s

### Adjust the following parameters and try to import them again,
Change concurrent_insert AUTO to ALWAYS
Bulk_insert_buffer_size 8388608 to 256 M
Change myisam_sort_buffer_size to 67108864 MB.

[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. 460 s ### the import time is not significantly reduced at this time
User 0m5. 924 s
Sys 0m1. 637 s

2. Export and Import Based on the innodb Engine
A. the table is not exported in blocks. The data file size is 3.9 GB.
[Root @ GZAPP tmp] # ls-hltr
Total 3.9 GB
-Rw-r -- 1 root 1.8 K Jul 24 bscom. tb_message-schema. SQL
-Rw-r -- 1 root 3.9G Jul 24 00:25 bscom. tb_message. SQL
-Rw-r -- 1 root 215 Jul 24 metadata

### The following six threads are used for import. As you can see, only one thread is working, because there is only one data file
[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. Partition export of tables
[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.9 GB
-Rw-r -- 1 root 1.8 K Jul 24 bscom. tb_message-schema. SQL
-Rw-r -- 1 root 478 M Jul 24 bscom. tb_message.20.1. SQL
-Rw-r -- 1 root 478 M Jul 24 bscom. tb_message.20.2. SQL
-Rw-r -- 1 root 478 M Jul 24 bscom. tb_message.20.3. SQL
-Rw-r -- 1 root 478 M Jul 24 bscom. tb_message.00004. SQL
-Rw-r -- 1 root 478 M Jul 24 bscom. tb_message.20.5. SQL
-Rw-r -- 1 root 478 M Jul 24 bscom. tb_message.20.6. SQL
-Rw-r -- 1 root 478 M Jul 24 bscom. tb_message.20.7. SQL
-Rw-r -- 1 root 481 M Jul 24 bscom. tb_message.20.8. SQL
-Rw-r -- 1 root 135 Jul 24 metadata
-Rw-r -- 1 root 93 M Jul 24 09:55 bscom. tb_message.20.9. SQL

### Try to use six threads for import below. We can see that six threads are being imported concurrently.
[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. Summary
A. mydumper can reasonably set the number of threads based on available resources on the server during export.
B. When exporting mydumper, specify the chunk-filesize or rows parameter as much as possible to export data in parts.
C. When myloader is targeting the myisam engine, it is recommended to adjust relevant parameters to a reasonable value to improve performance, mainly because of table-level locks.
D. When myloader is targeting the innodb engine, it is recommended to adjust the parameters to a reasonable value to improve performance, such as the following parameters:
Innodb_buffer_pool_size
Innodb_flush_log_at_trx_commit
Innodb_log_buffer_size
E. By using multipart export and import, You can significantly use concurrency to accelerate inndbo table import.
F. Note that mydumper does not export stored procedures, functions, and triggers during export.

This article permanently updates the link address: