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.

  • Are you able to modify the third party script? Does the third party script execute the actual sql queries or does it just prepare the data? – James May 12 '11 at 4:28
  • Yes the script can be modified. The script execute actual sql queries. – David May 12 '11 at 20:38

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

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.