時間の経過とともに遅くなる MySQL インポートのパフォーマンスを改善する方法はありますか?

Question

巨大な mysqldump ファイル (約 700GB) があり、それを別のサーバーに復元したいと考えています。最初の GB では、インポート速度は約 MB/秒と非常に高速ですが、数 GB のデータを超えると、速度は 50 kb/秒から 200 kb/秒の間に低下します。

mysqldump のパラメータは --skip-comments --no-create-info --no-autocommit --quick --extended-insert --insert-ignore --compress です。完了しました。速度が若干改善されました。

インポートは pv /file.sql | mysql -u user DB

を介して行われます。

mysqltuner も実行し、その設定の一部を改善しました。

知りたい: このような大規模な mysqldump がしばらくすると速度が低下するのはよくあることですか?それともさらに改善できる点はありますか?

Answer 1

大量のデータをインポートする場合は、次の機能を無効にする必要があります。

1. トリガーを無効にする
2. インデックスを無効にする
3. 制約またはチェックを無効にする
4. 外部キーを無効にする

挿入するたびに、データベースは他のテーブルに挿入されたレコードの関係をチェックするためです。テーブルが大きくなるにつれて、このプロセスは遅くなり始めます。各挿入中に、データベースは追加の挿入操作も実行するか、テーブルのインデックスへの挿入トリガーを実行します。

また、MySQL のトランザクションは弱いため、挿入のたびにコミットが機能しないようにする必要があります (たとえば、10,000 件のレコードが挿入されるたびに 1 回 commit コマンドを使用できます)。