提供：MySQL Practice Wiki

Why?

mysqldumpでとったバックアップを戻すとき、InnoDBは非常に時間がかかってしまう。それはなぜか？もっとも大きな原因はI/Oのボトルネックであろう。InnoDBは完全にACID準拠するため、特にDurabilityを達成するためディスクへの書き込み回数（Flush回数）が多い。また、インデックスの更新はBツリーを検索・ページ単位で更新という処理が発生するため時間がかかってしまう。それは巨大なテーブルになると特に顕著になる。

対処法

対処は基本的にI/Oを減らすことである。バックアップからリストアをするということは、自明ではあるが、たとえ失敗してももう一度やり直せばいいということである。そのような状況ではDurabilityを犠牲にして性能を向上させることは理に適っている。I/Oを発生させる要因を潰していこう。

バイナリログ

ダンプがあるということは、バイナリログが必要ないということである。レプリケーションを行う場合でも、マスターとスレーブへ同じダンプを使ってリストアすれば良い。従って、リストア中はバイナリログを無効にしておくと良いだろう。無効にするには--log-binオプションを外すかOFFにしてmysqldを再起動すると良い。MySQL 5.1以降なら起動中に動的に変更も可能だ。

InnoDBを使っている場合、--sync-binlogオプションを1にすることで確実にInnoDBのデータとバイナリログの内容を同期できるというナイスな機能がある。が、トレードオフとしてI/Oの性能が格段に低下してしまう。もしバイナリログをどうしても無効に出来ない場合は、--sync-binlogを0に設定するといいだろう。

もちろん、一般クエリログを無効にしておくべきなのは言うまでもない。

Double Write

InnoDBは、テーブルスペース（データファイル）へデータを反映する際、2回書く。一見これは非常に無駄な処理に見えるが、この単純なアルゴリズムのおかげでクラッシュ時の耐性を高めている。他の策を弄するよりも、単純にDouble Write Bufferとテーブルスペースへ2回書き込みを行うという操作を行った方が、確実であるし高速ですらある。ただし、これもリストア中はオフにしておくといいだろう。--skip-innodb-doublewriteオプションをつけてmysqldを再起動しよう。

InnoDBログファイル

デフォルトでは、毎回コミットするごとにログのフラッシュを行い、確実にデータがディスクへ書き込まれるようになっている。もちろんそのような操作は非常に遅いので、リストア時は「失敗したらもう一度やり直す」ことを前提でコミット時の動作を変更すると良い。--innodb-flush-log-at-trx-commitを0か2にするといいだろう。

また、変速的ではあるが、ログファイルを一時的にメモリベースのファイルシステムへ移してしまうという荒技もある。Solarisのtmpfsなどである。そうするとログファイルの更新・読み込みにおいてディスクI/Oが一切発生しないのでリストアの処理をさらに高速化することが出来る。

InnoDBバッファプール

リストア時はインデックスの更新が必要になる。インデックスを全てキャッシュ出来たなら、リストアは非常に高速になるだろう。もし他のバッファ（MyISAM用キーバッファやクエリキャッシュなど）に多くのメモリを割り当てていたら、いったんそれらを解除し、バッファプールへ一時的に割り振るといいだろう。そうすることで、巨大なテーブルのリストアを高速化できる可能性がある。オプションは--innodb-buffer-pool-sizeである。

サンプルmy.cnf

my.cnfの最後に追加して再起動し、その後リストアしよう。

#sync_binlog=0
skip_innodb_doublewrite
innodb_flush_log_at_trx_commit=0

log_binの指定がある場合にはコメントアウトすること。

リストアが終わったらmy.cnfからこれらを削除して再起動すること。でないとDurabilityが保証されない。注意しよう。

InnoDBのリストアを高速化