msyqldump / import 速度

測試環境
mysql 5.7
google cloud platform:  8Core 16G-ram SSD linux 主機


據說修改 bulk_insert_buffer_size  可加快匯入速度

default 8MB

mysql> SHOW VARIABLES LIKE '%bulk%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
+-------------------------+---------+
1 row in set (0.04 sec)

mysql> SET GLOBAL bulk_insert_buffer_size =1024*1024*256;


或是 my.cnf 設定
bulk_insert_buffer_size = 256M


開始測試


測試的檔案
data.sql  
400MB  ，267 萬筆資料 (20 tables)
MyISAM 格式
匯出方式
$ mysqldump XXX > data.sql -p -uroot --skip-extended-insert
 

也就是匯出格式像這樣
INSERT INTO `tbl_photo` VALUES (26839,182,'p182_11867397_86123.jpg');
INSERT INTO `tbl_photo` VALUES (26838,182,'p182_11867397_17990.jpg');
INSERT INTO `tbl_photo` VALUES (26836,182,'p182_11867397_94778.jpg');
::  ::  ::

#1 bulk_insert_buffer_size = 8M

$ time mysql t1 < data.sql -p -uroot
Enter password:

real    71m22.827s
user    1m5.643s
sys     2m10.735s



#2 bulk_insert_buffer_size = 256M

$ time mysql t2 < data.sql -p -uroot
Enter password:

real    71m38.760s
user    1m5.431s
sys     2m10.599s




測試的檔案
data2.sql  
480MB  ，267 萬筆資料 (20 tables)
MyISAM 格式
匯出方式
$ mysqldump XXX > data2.sql -p -uroot
 

也就是匯出格式像這樣
INSERT INTO `tbl_photo` VALUES (26839,182,'p182_11867397_86123.jpg'),(26838,182,'p182_11867397_17990.jpg'),(26836,182,
'p182_11867397_94778.jpg') ....

$ time mysql t3 < data2.sql -p -uroot
Enter password:

real    1m8.070s
user    0m3.983s
sys    0m0.718s


結論
影響 import 速度最大的差別就是 sql 匯出的格式