how to speed up mySQL import

[Japster]

I'm having a 1.7GB mySQL data dump, and are busy importing it to a new database. The problem I'm having at the moment, is that this import has been running for 3 days non-stop. Is there anyway that a person can speed-up this import, as I really cannot sit till the end of next week and wait for this import.

i did set the ini file:
xampp/mysql/bin/my.ini

Set the following:
[mysqld]
max_allowed_packet = 512M

[mysqldump]
max_allowed_packet = 512M

but this did not really help anything to improve the import speed.

 

[creeper]

There are just too many factors. How fast is your computer. Are you doing it over LAN. Are there indexes on the tables?

I suggest reading this: https://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/

 

[patrict]

SSD

 

[Japster]

Well I have been running it on an i5, with 8 gig RAM, but the import does not really use more RAM, I do not even use 3gig of RAM at the moment.

@Creeper, thanks for the article, will see if there is some improvements if I try some of the settings

 

[rrh]

There are just too many factors. How fast is your computer. Are you doing it over LAN. Are there indexes on the tables?

^^^ this, plus many others e.g. what software are you using for the ETL process, is transaction logging enabled, what does the schema look like, etc. ?

It may be a plan do go "old-fashioned" by exporting to CSV, then importing the CSV file.

 

[gkm]

I notice you are using xampp. Are you importing via phpmyadmin or from the command line? I have imported much bigger databases in an hour or so, so I am wondering if you are maybe using phpmyadmin, which I would not recommend for anything big.

http://stackoverflow.com/questions/4546778/how-can-i-import-a-database-with-mysql-from-terminal

 

[gkm]

^^^ this, plus many others e.g. what software are you using for the ETL process, is transaction logging enabled, what does the schema look like, etc. ?

It may be a plan do go "old-fashioned" by exporting to CSV, then importing the CSV file.

I would not recommend the csv option. It gives hassles with datatypes and unicode. If what you have is a csv, then it is the only option, but a mysql dump is better where available.

 

[rrh]

I would not recommend the csv option. It gives hassles with datatypes and unicode. If what you have is a csv, then it is the only option, but a mysql dump is better where available.

I agree. That said, if the data is appropriate, it can be very fast.

Generally I prefer using an ETL tool.

 

[Japster]

I use the command line. I log into mySQL then switch to the database, and then use the source command. For example;
cd c:\xampp\mysql\bin
mysql -h localhost -u root -p
use databaseName
source dataFile.sql

 

[gkm]

Maybe have a look at some of these suggestions:
http://dba.stackexchange.com/questions/83125/mysql-any-way-to-import-a-huge-32-gb-sql-dump-faster

 

[Japster]

Tx guys, will set those Inno_DB settings and test this tomorrow

 

[Japster]

Just have to say thanks guys, you helped a lot, database imported in roughly 15min where it took 3 days before and did not finish.

 

[gkm]

That is great news. Glad you got it sorted.