Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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

1 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

share|improve this answer
    
Thank you I will take a look at your suggestion –  David May 12 '11 at 20:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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