问题原文
原帖链接 · 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
mysqldis 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 -hreturns 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=nosyncinnodb_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.
摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。
