MySQL的优化LOAD DATA INFILE
我看到到处程序员discuting的最快LOAD DATA INFILE
插入优化。但他们从来没有解释过很多他们的价值选择等,优化取决于环境和实际的实际需求。MySQL的优化LOAD DATA INFILE
所以,想上什么是在达到最快的插入可能我的MySQL配置文件的最佳值的一些解释相关,请。
我的配置,英特尔双核@ 3.30 GHz的,4GB DDR4 RAM(Windows7的说: “2.16Gb可用” 寿由于保留内存)。
我backup.csv文件明文约5亿的条目,所以它的一个巨大的500GB文件大小这样舍姆(但十六进制字符串,长度64):
"sdlfkjdlfkjslfjsdlfkjslrtrtykdjf";"dlksfjdrtyrylkfjlskjfssdlkfjslsdkjf"
在我的表,唯一的两个字段第一个是Unique索引。 行格式设置为固定节省空间的问题。出于同样的原因,字段类型被设置为BINARY(32)。
即时通讯使用MyISAM引擎。 (!因为InnoDB需要更多的空间)(MySQL版本5.1.41)
这里是我计划用现在的代码:
ALTER TABLE verification DISABLE KEYS;
LOCK TABLES verification WRITE;
LOAD DATA INFILE 'G:\\backup.csv'
IGNORE INTO TABLE verification
FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
(@myhash, @myverif) SET hash = UNHEX(@myhash), verif = UNHEX(@myverif);
UNLOCK TABLES;
ALTER TABLE verification ENABLE KEYS;
正如你所看到的,命令使用LOAD DATA INFILE
采用纯文本值,并将其转化为HEX(均为十六进制哈希finaly所以......)
我听说缓冲区大小等,并从MySQL配置文件中的所有这些值。我应该改变什么,请问最好的价值是什么?正如你所看到的,我锁定了桌子,并且还禁用了键以加快速度。
我也阅读文档:
myisamchk --keys-used=0 -rq /var/lib/mysql/dbName/tblName
在这之前插入会加速它也。但是什么是真的tblName
? (?因为我有一个.frm文件,一个.MYD和.MYI,所以我应该指向哪一个)
Here are the lasts short hints i did read about optimisation
编辑:忘了告诉,一切都为localhost。
因此,我finfinge设法插入我的500GB数据库超过30亿条,在5小时的东西。
我已经尝试了很多方法,并在重建Primary Index
时,我被卡住了这个错误ERROR 1034 (HY000): Duplicate key 1 for record at 2229897540 against new record at 533925080
。
我现在解释我如何实现来完成我的插入:
- 我整理我的
.csv
文件,GNU CoreUtils : sort.exe
(在Windows IM)记住这样做,你需要1.5倍的CSV文件的可用空间,用于临时文件。 (因此计数.csv文件,最终为2.5x) - 您可以使用索引和全部创建表。
- 执行
mysqladmin flush-tables -u a_db_user -p
- 执行
myisamchk --keys-used=0 -rq /var/lib/mysql/dbName/tblName
-
插入数据:(请勿使用
ALTER TABLE tblname DISABLE KEYS;
!!!)LOCK TABLES verification WRITE; LOAD DATA INFILE 'G:\\backup.csv' IGNORE INTO TABLE verification FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (@myhash, @myverif) SET hash = UNHEX(@myhash), verif = UNHEX(@myverif); UNLOCK TABLES;
插入数据时,你重建索引,通过执行
myisamchk --key_buffer_size=1024M --sort_buffer_size=1024M -rqq /var/lib/mysql/dbName/tblName
(注-rqq
,加倍q
将通过尝试修复它们而忽略可能的重复错误(而不是在几个小时的等待之后停止插入!)- 执行
mysqladmin flush-tables -u a_db_user -p
,我是完成了!
- 我注意到,在速度的巨大推动作用,如果
.csv
文件是另一个驱动器比对数据库,并同样在另一个驱动器的sort
操作,把临时文件。 (读/写速度在同一个地方不能同时DATAS)的这个
我敢肯定它是验证,而不是verification.MYD
或其他两个。 .MYD是数据,.MYI是索引,.frm是模式。
字符串有多长?是十六进制?如果是32位的十六进制数字,那么的输出是不是需要BINARY(16)
?
过程的长的部分可能会ENABLE KEYS
,是时候将建立索引时。在运行时执行SHOW PROCESSLIST;
- 如果它显示“使用keybuffer”,则杀死它,这将持续一段时间。如果有人说“修理修复”,那么它很好 - 它正在排序,然后有效地加载索引。
在开始进程之前,您可以通过设置myisam_data_pointer_size=5
来节省5GB的磁盘空间。似乎也有myisam_index_pointer_size
,但它可能默认为5,这对您的情况可能是正确的。 (我在第4版中遇到过这样的设置。大约在2004年0;但从来没有。)
我不认为key_buffer_size
将负载和索引期间 - 因为你真的不希望它使用key_buffer。不要将它设置得太高以至于RAM耗尽。交换是糟糕的表现。
我绝对没有所谓的文件'verification',我将尝试也无妨,可能myisamchk的' '一个人在做这项工作。数据是十六进制字符串是的,长度为64(所以BINARY(32);我忘了在我的问题上提到这一点)。我的版本是'mysql.exe Ver 14.14 Distrib 5.1.41,对于Win32(ia32)' – user3916429
哦,对于'myisam_data_pointer_size',默认为'6',所以它的好处,因为'5'只有五十亿。有一个500Gb数据库,我不会争取5Gb的诚实=),似乎我没有'myisam_index_pointer_size'注册。 – user3916429
你可能有3个文件'verification.MYD'等。大约64/32。 5.1变得古色古香;考虑尽快升级。 “6”(默认值,256TB限制)和“5”(1TB限制)是文件中“数据指针”中_bytes_的数量。 '4'(4GB限制)会太小。 –