Effect of Extended-insert on mysqldump and its import performance

1. Environmental description

SuSE One-SP1 x86_64 + MySQL 5.5.37

Blog Address: http://blog.csdn.net/hw_libo/article/details/39583247

The test table Order_line has 320 million data, approximately 37G in size:

ndsc02:/data/mysql/mysql3306/data/tpcc1000 # DU-SHL ORDER_LINE.*12KORDER_LINE.FRM37GORDER_LINE.IBD

Mysql> Show Table status like ' Order_line ', +------------+--------+---------+------------+-----------+----------- -----+-------------+-----------------+--------------+-----------+--| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | A +------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--- -----------+-----------+--| Order_line |      InnoDB | 10 | Compact |             328191117 | 84 |               27771404288 |  0 |   10846420992 |  6291456 | +------------+--------+---------+------------+-----------+----------------+-------------+-----------------+---- ----------+-----------+--1 row in Set (0.09 sec)

MY.CNF configuration for MySQL:

# InnoDB Variablesinnodb_data_file_path           = ibdata1:1g:autoextendinnodb_buffer_pool_size         = 35ginnodb_file_ per_table =           1innodb_thread_concurrency       = innodb_flush_log_at_trx_commit  = 1innodb_log_buffer_size          = 16minnodb_log_file_size =            256minnodb_log_files_in_group =       3innodb_max_dirty_pages_pct      = 50innodb_lock_wait_timeout        = 120innodb_rollback_on_timeoutinnodb_status_file              = 1transaction_isolation           = read-committed

Bulk_insert_buffer_size<span style= "White-space:pre" ></span>= 64M

2. Export the table using mysqldump

Parameter description:

-E,--extended-insert, long insert, multiple row in batch insert, improve import efficiency, and do not turn on-e backup import time is at least 3, 4 times times, by default, turn off with--extended-insert=false. It is strongly recommended to open, and the following test comparison will understand why.

(1) The default way to export, that is--extended-insert=true

# time Mysqldump-s/tmp/mysql.sock-uroot-proot--single-transaction-b tpcc1000--tables order_line >/tpcc1000_ord Er_line1.sqlreal    7m38.824suser    6m44.777ssys     0m50.627s

Ndsc02:/opt/mysql/backup # ls-l tpcc1000_order_line1.sql-rw-r--r--1 root root 24703941064 09-26 16:39 tpcc1000_order_li Ne1.sqlndsc02:/opt/mysql/backup # du-sh Tpcc1000_order_line1.sql 24G Tpcc1000_order_line1.sql

(2) Close--extended-insert, also known as--extended-insert=false

# time Mysqldump-s/tmp/mysql.sock-uroot-proot--single-transaction--extended-insert=false-b tpcc1000--tables order_ Line >/tpcc1000_order_line2.sqlreal    9m36.340suser    8m18.219ssys     1m12.241s

Ndsc02:/opt/mysql/backup # ls-l tpcc1000_order_line2.sql-rw-r--r--1 root root 35094700366 09-26 16:49 tpcc1000_order_li Ne2.sqlndsc02:/opt/mysql/backup # du-sh Tpcc1000_order_line2.sql 33G Tpcc1000_order_line2.sql

Visible, by default (--extended-insert=true), Export 37G table, takes 7 minutes 38 seconds, export file is 24G, if close--extended-insert=false, the same table, export time is 9 minutes 36 seconds, And the export file is 33G.

I tested it two times, basically the same. When you can export a file, it is necessary to turn on--extended-insert=true, so that the export file is small and time consuming is less.

3. Impact of Import

Here's the performance impact of the exported file with--extended-insert=false exported by default (--extended-insert=true) when it is imported.

Description: innodb_flush_log_at_trx_commit=2

The test table orders are used here, the size of the table is 2.6GB, the number of rows is 31493000 rows, and the following is the export file:

# du-sh Tpcc1000_orders*1.4gtpcc1000_orders1.sql      # # uses the exported file by default (--extended-insert=true) 2.3gtpcc1000_ Orders2.sql      # # files exported using--extended-insert=false

(1) Import the exported table by default (--extended-insert=true)

# time Mysql-f-s/tmp/mysql.sock-uroot-proot test <./tpcc1000_orders1.sqlreal 12m2.184suser 0m28.538ssys 0m1.460s

(2) Import a table exported using--extended-insert=false

# time Mysql-f-s/tmp/mysql3308.sock-uroot-proot Bosco2 </tpcc1000_orders2.sqlreal    276m39.231s  # ~ 4.6 hours User    8m13.391ssys     6m20.120s

The comparison above shows that the import speed is very different.

Is it useless to use--extended-insert=false to export the table?

Not so. For example, the table in the database already has a large amount of data, then import data into the table, if there is a primary key data conflict duplicate key error, will cause the import operation failed, but at this time if you are using--extended-insert=false export table, Primary key conflict on Import error duplicate key error, but non-conflicting data will still be imported normally.

Blog Address: http://blog.csdn.net/hw_libo/article/details/39583247

--Bosco qq:375612082

----END----

Effect of Extended-insert on mysqldump and its import performance