I am using the 64-bit version of MySQL 5.5 on a Windows 7 machine.

I am trying to import some very large geospatial data files from geonames.org into MySQL so that I can run queries against it and generate a smaller set of data for my purposes.

The storage engine I am using is MyISAM and the input data is a 1 gigabyte text file with each row separated by tabs. I believe the text file contains about 8 million rows.

Initially, I imported the file into a database with the InnoDB engine. There were a lot of warnings, probably due to the geospatial data. The import took about 6.5 minutes, which was quite reasonable.

I then tried to import the data into a database using the MyISAM storage engine. The import is still running and I have spent more than 30 minutes waiting for the import to finish.

The server is configured with the stock "Developer" settings. I noticed that when I look at server status using MySQL Workbench, the Key efficiency runs at 100%, while the memory usage sits at about 40% (I have 12GB of RAM in the machine) and CPU usage sits at about 5%. Are there any particular settings/tweaks I should look at to speed up the import?

EDIT: So it just finished importing: Are there any ways to speed this up?

Query OK, 7836651 rows affected, 65535 warnings (50 min 20.89 sec)
Records: 7836651  Deleted: 0  Skipped: 0  Warnings: 5630783

If you were using LOAD DATA INFILE, you need to make sure you increase bulk_insert_buffer_size to something significant like 256M.

  • I went and increased the buffer size for quite a few settings. Since I had 12 gigs of memory, I gave mysql 2gigs in most cases. Importing the 1gig file with a few indexes only took about 5 minutes. – F21 Aug 11 '11 at 23:36

It sounds like the way to go is to use InnoDB. Also tune the relevant MySQL settings to use more RAM if you want, and get faster disks (if CPU and memory aren't pegged, then the disks are almost certainly the bottleneck).

  • Indeed. The harddisk seems to be running a lot. What parameters should I look at to increase the amount of RAM and CPU uses? – F21 Aug 11 '11 at 1:26
  • You can always give MySQL larger buffers all around. This will help it load more into memory at one time. Unfortunately, there will always be high disk activity with a file that large. How much memory do you have on the server to begin with? MySQL will only load into memory what you allow it to based on it's buffers. The bottleneck will always be on the disk though. – lilott8 Aug 11 '11 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.