1

So I did a mysqldump of a pretty huge db and I'm trying to restore it now using:

mysql db_test < db_test.sql

but it looks like it's not gonna end by the end of this year. It's been about an hour now and still "restoring". Making the backup took about 10 minutes so I'm afraid something bad is going on.

So far:

  • I've checked that mysqld is consuming my cpu (up to 80% sometimes).
  • There's nothing relevant to it in the logs.
  • I can see the database created and filled with a ton of tables (not everything tho). Also, when I execute use db_test;, I get the following message:

    Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

  • Original db files where around 25GB, after the whole hour, df -h returns the same free space as before. So I guess it's not doing anything on disk.

Something weird here is that, when checking top, I found out that kworker was consuming up to 100% of cpu sometimes, which shouldn't be happenning


Any idea or anything I could do to see what's going on?

  • 1
    Sounds like it's busy writing to disk. – Michael Hampton May 9 '16 at 14:31
  • For an hour already? The db is huge, not infinite lol. – sysfiend May 9 '16 at 14:36
  • Consider disabling keys and indexes until your restore job is complete. That will traditionally give a hefty performance boost. – Tim Brigham May 9 '16 at 14:37
  • I'll try that out @TimBrigham. Also, I updated the question with a bit more info provided by df -h – sysfiend May 9 '16 at 14:45
3

It sounds as the underlying disks are busy. The problem in restoring a large database is that each (group of) INSERT requires a flush/sync operation, which is very slow on mechanical disks (a 7200 RPM disk is in the order of ~100 IOPS).

To hasten the restore, you had to temporarily instruct MySQL/MariaDB to not issue flushes/syncs. To do that, interrupt the restore and edit your /etc/my.ini with the following two lines:

  • innodb_flush_method=nosync
  • innodb_flush_log_at_trx_commit=2

Then restart MariaDB and retry the restore. Things should go much faster now.

After the restore, REMOVE THE ABOVE LINES of your database will have a short and bad life: flushes/syncs exists for very important reasons. While it is acceptable to turn them off for a restore, a production database should never run without them.

2

If your underlying storage is plain old disks, for a 25GB-on-disk MySQL database, that's quite normal to experience hours-long restore - especially for some badly structured dbs (lots of tuples, indexes, etc.).

For a start compress your dump, that saves tons of I/Os and puts less stress on cache memory :

mysqldump foo |gzip >foo.sql.gz
zcat foo.sql.gz |mysql foo

In your case, since you already did the mysqldump :

gzip db_test.sql
zcat db_test.sql.gz |mysql db_test

Besides your system performance (storage, RAM, etc.), MySQL settings might have a huge impact. But that's a whole expertise there, nothing than can be handled thru a ServerFault issue...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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