哪些mysql设置会影响LOAD DATA INFILE的速度? [英] What mysql settings affect the speed of LOAD DATA INFILE?
问题描述
让我来设置情况.我们正在尝试将一定数量的行(每天大约10-20M)插入适度宽的MyISAM表中:
Let me set up the situation. We are trying to insert a modestly high number of rows (roughly 10-20M a day) into a MyISAM table that is modestly wide:
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| blah1 | varchar(255) | NO | PRI | | |
| blah2 | varchar(255) | NO | PRI | | |
| blah3 | varchar(5) | NO | PRI | | |
| blah4 | varchar(5) | NO | PRI | | |
| blah5 | varchar(2) | NO | PRI | | |
| blah6 | varchar(2) | NO | PRI | | |
| blah7 | date | NO | PRI | | |
| blah8 | smallint(6) | NO | PRI | | |
| blah9 | varchar(255) | NO | PRI | | |
| blah10 | bigint(20) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
除了巨大的主键外，唯一的索引位于blah7(日期字段)上.我们正在使用LOAD DATA INFILE，并在大约2个小时的加载时间里看到了令我印象深刻的性能.导致我相信LOAD DATA INFILE比这快几个数量级.
The only index besides that whopping primary key is on the blah7, the date field. We are using LOAD DATA INFILE and seeing what strikes me as pretty awful performance, around 2 hours to load the data. I was led to believe that LOAD DATA INFILE was orders of magnitude faster than that.
有趣的是，我们的胖表(5-6个字段)少了一些，我们也使用LOAD DATA INFILE将数据批处理到其中，我们看到的性能要好得多.记录的数量要少得多，这使我认为加载大表时我们正在达到缓冲区大小的限制，并且必须转至磁盘(实际上，除了转至磁盘外，还要进行其他操作)解释这么慢的加载时间?).
Interestingly, we have some less fat tables (5-6 fields) that we also use LOAD DATA INFILE to batch data into and we see much better performance on those. The number of records is quite a bit smaller, which leads me to think that we are running up against a buffer size limit when we load the large table, and are having to go to disk (and really, what else but going to disk would explain such slow load times?).
...这使我想到了这个问题.处理LOAD DATA INFILE命令时，哪些my.cnf设置最重要?
...which brings me to my question. What my.cnf settings are most important when dealing with LOAD DATA INFILE commands?
推荐答案
一般情况下，插入索引会降低性能.您最好在插入数据之前先删除索引，然后在插入后重新建立索引.
Inserting into indexes in general kills performance. You may be better off removing the index before inserting data and re-indexing after insertion.
来自 http://forum.percona.com/s/m/983/:
通常，MySQL加载速度相当快 MyISAM表中的数据，但是有 异常，这是当它不能 按排序重建索引，但建立 他们一行一行地代替.有可能 由于错误的配置而发生 (即太小 myisam_max_sort_file_size或 myisam_max_extra_sort_file_size)或它 可能只是缺乏优化，如果 你有大(不适合 内存)PRIMARY或UNIQUE索引.
Normally MySQL is rather fast loading data in MyISAM table, but there is exception, which is when it can't rebuild indexes by sort but builds them row by row instead. It can be happening due to wrong configuration (ie too small myisam_max_sort_file_size or myisam_max_extra_sort_file_size) or it could be just lack of optimization, if you're having large (does not fit in memory) PRIMARY or UNIQUE indexes.
也请查看 http: //www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/和 查看全文
