| |
Mydumper with myloader is an excellent third-party mysql database logical backup and recovery tool, use the export and import multi-threaded. To make up for the shortcomings of single-threaded mysqldump. This article describes how to accelerate the export and import mydumper and myloader for your reference.
1, based on MyIsam engine Export Import
a, non-blocking Export and import of table
[Root @ GZAPP tmp] # mydumper -u inno -p xxx -B bsom -T tb_access_log -o / backup / tmp /
[Root @ GZAPP tmp] # ls -hltr ### exported data files into a single file, the size of 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
### Based on the default number of threads to import, and set each transaction queries to 10000, this parameter has little effect at this fact, because the table is 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 number of threads that can be seen above, only a single-threaded insert operation at execution
b, Export and import block table
### The following example uses 500MB is divided into blocks
[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
### From the foregoing, according to a large table tb_access_log approaching 500M split into multiple files
[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
# In the following processlist can see, there is a table-level lock wait
+ --------- + ------- + ----------- + --------- + --------- + -------- + ---------- + ---------- -------------------------------------- +
| 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, after adjusting the relevant parameters Import 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
### Adjust the following parameters, and then try again to import,
concurrent_insert AUTO into ALWAYS
bulk_insert_buffer_size 8388608 change 256M
myisam_sort_buffer_size 67108864 changed 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
There is no significant reduction in real 253m42.460s ### at this time of import
user 0m5.924s
sys 0m1.637s
2. Export innodb engine based on import
a, table does not block export data file size is 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
### Use the following six threads import can actually see that only one thread at work, because 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
Export b, block table
[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
Try to use 6 ### thread imported, you can see that there are six threads in concurrent imports
[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 in export when you can be reasonably arranged according to the number of threads available to the server resources.
b, mydumper specify as much as possible in the export when chunk-filesize or rows parameter to block the export.
c, myloader recommendation to adjust the parameters for reasonable values when myisam engine to improve performance can not be improved, mainly table-level locking problems.
d, myloader recommended when adjusted for innodb engine parameters to reasonable values 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 a block export and import can significantly accelerate inndbo to use concurrent table import.
f, note mydumper not exported stored procedures, functions, triggers, etc. export. |
|
|
|