大网数据广告活动
服务器运维问答

MariaDB taking ages to restore backup

· 52CE 我爱测

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 …

问题原文

原帖链接 · sysfiend · CC BY-SA 3.0

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?

已采纳解答

原帖链接 · shodanshok · CC BY-SA 4.0

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.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2016-05-09)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0 / CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。