Why Importing a 12 GB .sql File Takes Over 36 Hours in MySQL

mariadbMySQLmysql-5

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?

Best Answer

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.

Related Question