Home / server / Questions / 775672
Accepted
sysfiend
Asked: 2016-05-10 06:30:49 +0800 CST

MariaDB taking ages to restore backup

  • 772

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?

backup mysql database mariadb

2 Answers

  1. Best Answer

    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 or 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.

    • 4

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

    • 2