What mysql settings affect LOAD DATA INFILE speed?

Question

Let me tune the situation. We are trying to insert a modestly large number of rows into the MyISAM table (approximately 10-20 M per day):

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

The only index besides this colossal primary key is in the blah7 date field. We use LOAD DATA INFILE and seeing what amazes me as a pretty terrible performance, about 2 hours to load the data. I was convinced that LOAD DATA INFILE is an order of magnitude faster than that.

, (5-6 ), LOAD DATA INFILE , . , , , ( , , , ?).

... . my.cnf LOAD DATA INFILE?

Answer 1

. , .

http://forum.percona.com/s/m/983/:

MySQL MyISAM, , , . - (.. myisam_max_sort_file_size myisam_max_extra_sort_file_size) , ( ) .

http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/ http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/insert-speed.html.

Answer 2

, , .

Answer 3

- MyISam, , bulk_insert_buffer_size

MyISAM LOAD DATA INFILE . BULK_INSERT_BUFFER_SIZE . 0 . - 8 . - 4 .

, bulk_insert_buffer_size . , , , 10 . . , , , .

, , :

• MYISAM_SORT_BUFFER_SIZE
• KEY_BUFFER_SIZE

alter table:

alter table t disable keys;