How to optimize ingestion of large amounts of data into MySQL database?

Question

I have a database that is roughly 100GB in size. Everyday the database must be updated with about 8GB of data. The data is ingested into the database using a script(python) written by a third party(Apple). The data is a collection of files pertaining to different tables in the database.

Currently it takes roughly 15 hours to update the database everyday. I am running the update on my local machine (Quad Core 2.6GHz, 6GB Ram, 32 bit Ubuntu 11 and MySQL 5.1).

Eventually this process will be offloaded to the Amazon EC2 service. What is the best way to optimize this process in order to significantly reduce the time required to ingest all the data on a daily basis?

Your suggestions would be greatly appreciated. Thank you.

Answer 1

Use a CSV file and LOAD DATA INFILE, it's much faster than running SQL.

Another option is to import the SQL files in parallel by starting multiple clients or using Maatkit's mk-parallel-restore

Answer 2

@sreimer gave good suggestions (+1 for him). In addition, don't forget to adjust the option bulk_insert_buffer_size. It's default is 8M. You can make it 1G or so.

With regard to mk-parallel-dump and mk-parallel-restore, Percona considers them deprecated, but you can still download them. I also trust those tools.

Make sure when doing bulk inserts with LOAD DATA INFILE not to let replication move the data from master to slave because the actual CSV file is written through the binaries logs. This can pile up relay logs on slave in a heartbeat. Slaves would extract the CSV data in /tmp and then perform the LOAD DATA INFILE on the materialized CSV file. I have seen this cause replication to fall behind 10's of thousands of seconds with just 2 or 3 bulk inserts.

In a replication topology using MySQL Replication, always use SET SQL_LOG_BIN=0; before LOAD DATA FILE. That way, you could bulk insert into masters and slave in parallel.