MySQL Forums
Forum List  »  Perl

Load data is slow
Posted by: John Stricklen
Date: September 16, 2011 12:20PM

Greetings! I am loading data from a file to a table using "load data local infile" and have found that loading on the same server is quite a bit faster than if I load to a database that is across the network. The file is between 50 and 60 million records, almost 4 GB in size, pipe (|) delimited, database is 5.0.51a. I am using a Perl script (DBI module) to connect to the database and run the load statement.

I understand that loading over the network will always be a little slower, but this is slow by a factor of 2 to 3. Is this normal?

I have found the folloiwng (times are approximate and vary quite a bit):
Loading from server A to a database on server A: 12 to 15 minutes
Loading from server A to server B over Gigabit ethernet: 45 to 90 min. (the preferred production configuration)
Transferring the file from A to B using sftp: 2 min.
Loading server B to database on server B 12 to 15 min. (using MySQL command prompt).

There are no indexes on the table but there is a primary key. There are no rows in the table when the load starts.

Things I've tried so far:
Using the mysql command prompt. Over the network is still slow but is a little faster than the Perl DBI connection. I tried this in the Perl script using a qx() to run the command. Again, it is a little faster, but not significant.

Increasing BULK_INSERT_BUFFER_SIZE=256217728. No change.

Increasing MYISAM_SORT_BUFFER_SIZE=256217728. No change.

Using Disable Keys. No change.



Given the data above it seems this has to be the MySQL protocol, not the network and not either server.

Any suggestions what to look at in terms of variables to change, network monitoring, and troubleshooting would be appreciated.


Thanks!

John

Options: ReplyQuote


Subject
Written By
Posted
Load data is slow
September 16, 2011 12:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.