Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Import and export myloader accelerate mydumper     - Let you Fun Ubuntu desktop eleven weapon (Linux)

- Install Ubuntu text editor KKEdit 0.2.10 (Linux)

- Python 2 Chinese garbage problem solved (Linux)

- Ubuntu compiler installation R Full Record (Linux)

- Linux Network Programming - libnet Guide (Programming)

- Implement firewall function on a closed Linux machine (Linux)

- Firewall settings oracle remote connection in Linux systems (Linux)

- Two alert log ORA Errors (Database)

- Oracle table Access Control (Database)

- Extended VMware Ubuntu root partition size (Linux)

- Java generate two-dimensional code by Zxing (Programming)

- Linux PXE unattended installation PXE-E32: TFTP OPen timeout the solution (Linux)

- How to convert images, audio and video formats on Ubuntu (Linux)

- Python Multithreaded Programming (Programming)

- Java call by value and by reference (Programming)

- XenServer Virtual Machine Installation --- first ISO image file storage expansion (Linux)

- Apache POI Excel Document Processing (Linux)

- Linux shell script to adjust the Task Scheduler (Linux)

- Transplant spider to MySQL 5.6 (Database)

- Difference LVS three scheduling modes (Server)

 
         
  Import and export myloader accelerate mydumper
     
  Add Date : 2017-01-08      
         
         
         
  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.
     
         
         
         
  More:      
 
- Oracle View Object Information (Database)
- Graphics of Java Tools (Programming)
- Linux system commands to view hardware information (Linux)
- Linux performance monitoring - CPU, Memory, IO, Network (Linux)
- Shilpa Nair interview experience sharing RedHat Linux package management (Linux)
- Ubuntu method for single-card dual-IP (Linux)
- Python Socket Network Programming (Programming)
- Preview function to confirm the stop resource Oracle 12c new feature crsctl (Database)
- Running the open-source Swift under Linux platform (Linux)
- GNU / Linux system, how to clean up memory space (Linux)
- Linux kernel panic (because glibc result) Repair (Linux)
- The Linux kernel and AVL tree in red-black tree (Programming)
- How to install PlayOnLinux 4.2.5 under Ubuntu 14.04 / 12.04 (Linux)
- Installation Strongswan: on a Linux IPsec-based VPN tool (Linux)
- Solve ORA-01012: not logged on (Database)
- Linux environment variable configuration and save places (Linux)
- Android WebView use layman (Programming)
- Linux account related documents Interpretation (Linux)
- CentOS7 install JDK (Linux)
- The free command in Linux (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.