Mysql – Importing large data into MySQL (MyISAM)

importMySQL

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

Best Answer

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