extended-insert對mysqldump及導入性能的影響

extended-insert對mysqldump及導入性能的影響

1. 環境描述

SuSE 11 sp1 x86_64 + MySQL 5.5.37

blog地址：http://blog.csdn.net/hw_libo/article/details/39583247

測試表order_line有3.2億數據，大小約37G：

NDSC02:/data/mysql/mysql3306/data/tpcc1000 # du -shl order_line.*
12K	order_line.frm
37G	order_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配置： 

# InnoDB variables
innodb_data_file_path           = ibdata1:1G:autoextend
innodb_buffer_pool_size         = 35G
innodb_file_per_table           = 1
innodb_thread_concurrency       = 20 
innodb_flush_log_at_trx_commit  = 1
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 256M
innodb_log_files_in_group       = 3
innodb_max_dirty_pages_pct      = 50
innodb_lock_wait_timeout        = 120
innodb_rollback_on_timeout
innodb_status_file              = 1
transaction_isolation           = READ-COMMITTED

bulk_insert_buffer_size<span style="white-space:pre">		</span>= 64M

2. 使用mysqldump導出該表

參數說明：

-e, --extended-insert，長INSERT，多row在一起批量INSERT，提高導入效率，和沒有開啟 -e 的備份導入耗時至少相差3、4倍，默認開啟；用--extended-insert=false關閉。強烈建議開啟，通過下面的測試比較就會明白為什麼了。

（1）默認方式導出，也即--extended-insert=true

# time mysqldump -S /tmp/mysql.sock -uroot -proot --single-transaction -B tpcc1000 --tables order_line > ./tpcc1000_order_line1.sql
real    7m38.824s
user    6m44.777s
sys     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.sql
NDSC02:/opt/mysql/backup # du -sh tpcc1000_order_line1.sql 
24G tpcc1000_order_line1.sql

（2）關閉--extended-insert，也即--extended-insert=false

# time mysqldump -S /tmp/mysql.sock -uroot -proot --single-transaction --extended-insert=false -B tpcc1000 --tables order_line > ./tpcc1000_order_line2.sql
real    9m36.340s
user    8m18.219s
sys     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.sql
NDSC02:/opt/mysql/backup # du -sh tpcc1000_order_line2.sql 
33G tpcc1000_order_line2.sql

可見，默認情況下（--extended-insert=true），導出37G的表，耗時7分38秒，導出文件為24G，如果關閉--extended-insert=false，同樣的表，導出時耗時9分36秒，且導出文件為33G。

我測試過兩次，基本一樣。可以導出文件時，開啟--extended-insert=true是必須的，這樣導出文件小，耗時也比較少。 

3. 導入的影響

這裡說說默認情況下（--extended-insert=true）導出的文件與使用--extended-insert=false導出的文件在導入時的性能影響。

說明：innodb_flush_log_at_trx_commit=2

這裡使用了測試表orders，表的大小為2.6GB，行數為31493000行，下面是導出文件：

# du -sh tpcc1000_orders*
1.4G	tpcc1000_orders1.sql      ## 使用默認情況下（--extended-insert=true）導出的文件
2.3G	tpcc1000_orders2.sql      ## 使用--extended-insert=false導出的文件

（1）導入默認情況下（--extended-insert=true）導出的表 

# time mysql -f -S /tmp/mysql.sock -uroot -proot test < ./tpcc1000_orders1.sql

real    12m2.184s
user    0m28.538s
sys     0m1.460s

（2）導入使用--extended-insert=false導出的表

# time mysql -f -S /tmp/mysql3308.sock -uroot -proot bosco2 < ./tpcc1000_orders2.sql

real    276m39.231s  ## 約4.6小時
user    8m13.391s
sys     6m20.120s

經過上面的一比較，發現導入速度相差非常多。

那麼使用--extended-insert=false導出表是不是一無是處呢？

並非如此。比如數據庫中表中已經存在大量數據，那麼再往表中導入數據時，如果出現主鍵數據沖突Duplicate key error，將會導致導入操作失敗，但此時如果是使用--extended-insert=false導出表，導入時主鍵沖突的會報錯Duplicate key error，但不沖突的數據仍然能正常導入。

blog地址：http://blog.csdn.net/hw_libo/article/details/39583247

-- Bosco QQ：375612082

---- END ----