Mysql load from infile stuck waiting on hard drive I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk loads all happen within a single transaction, all keys are disabled beforehand, and each csv file is only a few megabytes large.

The problem I've run into is that the test machine gets IO bound. It has enough ram available (12G) to hold the entire transaction in memory and do a single flush out the other end. As far as I understand the manual, the innodb tables shouldn't touch the hard drive until it flushes the dirty pages at transaction completion.

The total data to be loaded is about 1G, spread across the different tables. It ends up taking 37 minutes to load it all. Here's my current test settings for perusal. I'd also be happy to report the results from show engine innodb status or similar queries if necessary.

To recap, I need to know if 37 minutes is a fast insert speed for this data size, and what I can do to increase the insert speed. 

Edit:

Whoops! I forgot some important info.

Mysql version 5.5
Server has 12G total ram
Total rows inserted ~2,597,240
Your bulk insert buffer is 4G. That's great ... FOR MyISAM !!!

InnoDB does not use the bulk insert buffer.

You may need to have sqlalchemy throttle the load data infile calls into multiple transactions.

You may also want to disable innodb_change_buffering, setting it to inserts.
 Unfortunately, you cannot do SET GLOBAL innodb_change_buffering = 'inserts';. If you dom you may need to set it in my.cnf and restart mysql.

UPDATE 2012-07-13 16:53 EDT

I just noticed that you have two values in the my.cnf for innodb_buffer_pool_size. First one is 2385M, and the last one is 14G. If MySQL for Windows accepted 14G and you only have 12G of RAM, your server must be having a good old time swapping.

You can verify what the buffer pool size is with

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';


UPDATE 2012-07-13 16:58 EDT

You may also want to check how full the buffer pool is with this

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;