CODEWITHSUNDEEP
mysqlsqlconsolewamp
Kennethvr
Kennethvr

Reputation: 2680

MySQL console slow on import of huge SQL files

My import of SQL via the MySQL console is rather slow, and, as our SQL file is increasing every day, I would like to know if there are any alternatives on how to import an SQL file faster.

Changing to Oracle or other systems is not an option, the configuration has to stay the same.

Currently the SQL file is: 1.5 GB. I'm on WAMP with Apache 2.2.14, PHP 5.2.11 and MySQL 5.1.41.

Perhaps the issue is here, import is done by a simple:

mysql -u username -p dbname < sqlfilename.sql

Any suggestions?

Upvotes: 8

Views: 18020

Answers (5)

Bart Mensfort
Bart Mensfort

Reputation: 1088

Following works on a slow POS device to save 90% time:

When the .sql file has one insert statement per line,this takes 1-30 msec. When I put 500 commands in one sql statement separated with semicolons ; then it takes a second. Disadvantage is that the sql is not really readable anymore, since every line is 20000 characters or more.

With mysqldump you can create a file without the linefeeds.

Upvotes: 0

Jpsy
Jpsy

Reputation: 20862

Switching autocommit off is the first of a series of recommendations given in Bulk Data Loading for InnoDB Tables to speed up restore operations for MySQL.

Instead of switching autocommit off manually at restore time you can already dump your MySQL data in a way that includes all necessary statements right into your SQL file.

The command line parameter for mysqldump is --no-autocommit. You might also consider to add --opt which sets a combination of other parameters to speed up restore operations.

Here is an example for a complete mysqldump command line as I use it, containing --no-autocommit and --opt:

mysqldump -hlocalhost -uMyUser -p'MyPassword' --no-autocommit --opt --default-character-set=utf8 --quote-names  MyDbName  >  dump.sql

For details of these parameters see the reference of mysqldump

Upvotes: 4

UpTheCreek
UpTheCreek

Reputation: 32391

I'm not sure if this will solve your problem, becuase I'm not sure where the bottleneck is... However, I've have been really impressed with the free Toad for MySQL tool as a replacement MySQL management console. It's got great import tools, and is generally miles better IMO than the standard MySQL management console.

Upvotes: 0

tomriddle_1234
tomriddle_1234

Reputation: 3213

Try these http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

It's maybe an autocommit issue, turn that off then see what happends.

SET autocommit=0 ; source <your dump file> ; COMMIT ;

Upvotes: 8

mr.b
mr.b

Reputation: 4962

Having indexes enabled during import will slow your server down to a crawl. ALTER TABLEtablenameDISABLE KEYS; and using ..ENABLE KEYS prior to and after import, will improve import speed, but will take some time to re-create indexes, so it might not be a big speed gain after all.

Also, perhaps using myisam tables (in contrast to innodb with referential integrity options) usually gives better performance, as there is no referential integrity overhead involved.

Personally, I don't use import statement from mysql console, but import sql files using mysql -uUSER -pPASS DBNAME < file.sql, and it works well for me.

Hope it helps.

Upvotes: 5

Related Questions