Extended-insert impact on mysqldump and import performance _ MySQL

1. environment description SuSE11sp1x86_6443; MySQL5537blog address: http: bytes Test table order_line has 3.2 billion data records, the size is about 37G: NDSC02: datamysqlmysql3306 1. environment description

SuSE 11 sp1 x86_64 + MySQL 5.5.37

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

The test table order_line has 0.32 billion data records, which is about 37 GB 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)

MySQL my. cnf configuration:

# InnoDB variablesinnodb_data_file_path           = ibdata1:1G:autoextendinnodb_buffer_pool_size         = 35Ginnodb_file_per_table           = 1innodb_thread_concurrency       = 20 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= 64M

2. use mysqldump to export the table

Parameter description:

-E, -- extended-insert, long INSERT, and batch INSERT with multiple rows improve the import efficiency, which is at least three or four times different from the backup import time when-e is not enabled, it is enabled by default. -- extended-insert = false is used to disable it. We strongly recommend that you enable it. you can see why by comparing the following tests.

(1) export by default, that is, -- extended-insert = true

# time mysqldump -S /tmp/mysql.sock -uroot -proot --single-transaction -B tpcc1000 --tables order_line > ./tpcc1000_order_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_line1.sqlNDSC02:/opt/mysql/backup # du -sh tpcc1000_order_line1.sql 24G tpcc1000_order_line1.sql

(2) disable -- extended-insert, that is, -- 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_line2.sqlNDSC02:/opt/mysql/backup # du -sh tpcc1000_order_line2.sql 33G tpcc1000_order_line2.sql

It can be seen that, by default (-- extended-insert = true), 37 GB tables are exported at 7 minutes 38 seconds, and the exported file is 24 GB. if -- extended-insert = false is disabled, for the same table, the export time is 9 minutes 36 seconds and the export file is 33 GB.

I tested it twice, basically the same. When you can export files, it is required to enable -- extended-insert = true. in this way, the exported files are small and consume less time.

3. import impact

Here we will talk about the performance impact of files exported by default (-- extended-insert = true) and files exported by using -- extended-insert = false during import.

Note: innodb_flush_log_at_trx_commit = 2

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

# Du-sh tpcc1000_orders * 1.4Gtpcc1000 _ orders1. SQL # use default (-- extended-insert = true) exported File 2.3Gtpcc1000 _ orders2. SQL # use -- extended-insert = false to export the file

(1) import tables exported 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 tables exported using -- extended-insert = false

# Time mysql-f-S/tmp/mysql3308.sock-uroot-proot bosco2 <./tpcc1000_orders2.sqlreal 276m39. 231 s # About 4.6 hours user 8m13. 391 ssys 6m20. 120 s

After the comparison above, we found that the import speed varies a lot.

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

This is not the case. For example, if a large amount of data already exists in the database table, when importing data to the table, if the primary key conflicts with the Duplicate key error, the import operation will fail, however, if you use -- extended-insert = false to export the table, the Duplicate key error will be reported if the primary key conflicts during the import, but the non-conflicting data can still be imported normally.

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

-- Bosco QQ: 375612082

---- END ----