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 ----


郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。