Mysql load from infile stuck waiting on hard drive
4
votes
2
answers
7344
views
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
Asked by Spencer Rathbun
(161 rep)
Jul 13, 2012, 07:42 PM
Last activity: Sep 4, 2017, 03:57 AM
Last activity: Sep 4, 2017, 03:57 AM