Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
how much cpu is mysql taking? If it's a low value, it probably means you're disk-bound –  Derek Downey Feb 18 '11 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 '11 at 3:21
add comment

3 Answers

up vote 20 down vote accepted

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.

share|improve this answer
4  
+1 because i just learned a new command (strace) :P Edit: well crap, not available on my mac by default. –  Derek Downey Feb 18 '11 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 '11 at 21:47
3  
Strace is Linux - the Solaris equiv is truss. Dtrace is available on the Mac. –  Gaius Feb 18 '11 at 21:48
    
so it is. going to go read up on it now. –  Derek Downey Feb 18 '11 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 '11 at 22:04
show 1 more comment

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 !!!

share|improve this answer
add comment

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

share|improve this answer
add comment

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.