Why does importing a 12 GB .sql file take more than 36 hours?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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