[MySQL]運用中のDBにバックアップしたデータを流し込む(INSERT INTO SELECTの分割)

なんでこんなアクロバティックな事してるんだろう。

バックアップデータのインポートにINSERT INTO SELECT文を使おうと思ったは良いものの、一度にやると本番運用中のDBへのINSERTが滞ってしまう。といった状況からなんとかしたメモ。

(追記)明らかに処理スピードが遅かったため、別の方法を取りました。一週間かかりそうなペースだったものが2時間で終わった。

流れ: テーブルAのINDEXなどに関するALTER TABLEをしたかったが、時間も無ければ停止させている間はWebの表示もできない。しかも数百万件のレコードが存在。→Webページの表示に求められているのはそのうちの少数&なければ常駐プログラムで大事な物から取ってこれる→ピコーン

ここで、テーブルAがWebページの稼働に大忙し(先のプログラムからのINSERTで順次表示できるようになってるよ!)、でもBからバックアップデータ読み込ませたい。

常駐プログラムがINSERTする順番待ちでデッドロック直前?危うく大惨事。とりあえずKill Query!

さあどうしよう。10000件くらいに分けてINSERTするプログラムでも書こうか。しかしJDBCとか使ったコードってちょちょっと書くには面倒臭い。なにかいい方法はない物だろうか。

シェルスクリプトって便利なんですね。こちらを参考にさせていただきました。上四つにはそれぞれ適切な数値を入れてあげてください。TABLE_LENGTHはバックアップ側でCOUNT(*)とかしてあげるとかで。また、フィールドは全て同一なのを前提に書いています。もし違うようであればSQL文書き換えてあげてください。最初に流す時はコメントアウトしてあるのを外してechoで様子を見ても良いかも。

もちろん、動作させるのは自己責任で。

(追記) 非力なサーバーならLIMITで絞る数をもう少し絞った方が良いのかもしれない。またはsleep入れるか。デッドロック起きてるよ!言われて調べてみたらこれみたいですね、10000件INSERTは重すぎたか…

(さらに追記)

この方法だと、ある程度無駄なリソースを食っていそうです。mysqlクライアントの起動のタイミングでDBのテーブル名などを洗う為?

テキストファイルに起こしてからsourceで読ませた方が早いかもと思い、実験してみたのですが、幾つか欠点があって恐らく最初の方法のほうがまだ’マシ’な方法。

テキストファイルに起こしてsourceで読み込み:重さが問題になった際、Ctrl+CでクエリのKillになり、押し続けるか別窓でコネクションごとKillしなければいけない。実行中のSQL文が表示されない等。(こちらは一回毎にSELECTを挟めば可能といえば可能)

最初の方法だとLIMIT句のOFFSETを表示しているので、次回起動時にその値をTに代入するよう書き換えて動かし始めれば負荷が軽い時間帯を狙ってゆっくり動かすことも出来るので。(と言う為にsleep 1; とか入れたりしてちょっとスクリプトを書き換えました。)

こんなアクロバティックな事する時くらいはサーバーをじーっと見ていると思うので、まあこのスクリプトのままでいいかなぁ…。

(追記3)

LIMIT句のOFFSETが大きすぎると読み飛ばす時間がかかりますね。当たり前のことなのに失念していました。

カテゴリー: サーバ関連, プログラミング関連 パーマリンク

[MySQL]運用中のDBにバックアップしたデータを流し込む(INSERT INTO SELECTの分割) への1件のフィードバック

  1. ピンバック: [MySQL][追記]運用中のDBにバックアップしたデータを流し込んだ | Gumu-Lab.

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です