I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB.

Considering the triggers and stored procedures are at the end of the .sql, I only think MySQL should be adding data and key indexes.

The site.sql was generated using this command from another server:

mysqldump -R -e --databases site --add-drop-database --add-create-database --add-drop-table -C --single-transaction --triggers

What's taking so long?

  • 3
    how much cpu is mysql taking? If it's a low value, it probably means you're disk-bound Feb 18, 2011 at 18:50
  • 2
    the .sql files really aren't that fast to import ... it's actually faster to dump to tab delim or CSV, then build the empty database and use LOAD DATA INFILE. Also, as you're moving a whole database, see my answer re: moving databases between servers if you're staying within the same major version. (especially if you have to abort & restart)
    – Joe
    Feb 21, 2011 at 3:21

3 Answers 3


Try this:

$ ps -ef|grep [m]ysql

Identify the process id then

$ strace -cp <pid>

Leave it 10 seconds or a minute then ^C. That will tell you where the process is spending its time, e.g. it could just be waiting for the disk if you seen read and write dominate.

  • 4
    +1 because i just learned a new command (strace) :P Edit: well crap, not available on my mac by default. Feb 18, 2011 at 21:20
  • 2
    It's a fantastic tool, along with gdb. I don't tell people their app has hung anymore; I tell them exactly what it's stuck on or spinning on, or from a core tell them the line of code and the name of the source file. Even more powerful is dtrace.
    – Gaius
    Feb 18, 2011 at 21:47
  • 3
    Strace is Linux - the Solaris equiv is truss. Dtrace is available on the Mac.
    – Gaius
    Feb 18, 2011 at 21:48
  • so it is. going to go read up on it now. Feb 18, 2011 at 23:24
  • Beware: Nice command to know, but be careful, this command crashed my MySQL instance. Don't know why. Before MySQL crashed, the server became unresponsive for few minutes.
    – dabest1
    Oct 25, 2011 at 22:04

Do you have any InnoDB tables with a Primary Key

  1. containing multiple columns ?
  2. having a wide VARCHAR ?
  3. and a lot of non-Unique indexes ?
  4. one or more non-Unique indexes that has a wide key ?

Any of these conditions can probably cause large BTREE nodes in your indexes to have very few leaves in each BTREE node. The cluster key in the Primary Key is also attached to each non-Unique key entry in nonclustered keys.

Another consideration: Are the sum of InnoDB data pages significantly less than InnoDB index pages ?

You can find that out with this query (in MB):

SELECT SUM(data_length)/POWER(1024,2) InnoDBData,
SUM(index_length)/POWER(1024,2) InnoDBIndexes
FROM information_schema.tables WHERE engine='InnoDB';

Additional consideration: Do you have binary logging enabled in the DB server you are loading ? If you are, please do this on the server you are loading:

mysql -h... -u... -p... -A -e"SET sql_log_bin=0; source site.sql"

I hope this helps !!!


Are you sure that the tables where you are reading in are without triggers and indexes and constraints? What hardware and OS are you running on? How is your storage configured?

I am more familiar with oracle but 12G importing on tables without triggers, indexes and constraints should easily go with 200GB/h. One single trigger can make the process to a snail, depending on what that triggers does ...

I hope this helps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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