Road To Nowhere

主にWebまわりのエンジニア的なお仕事に関するようなことのあれこれ。

大きなサイズのデータをmyisamからinnodbに移行する検証をしたメモ

MySQL

ハマってなんとか解決したことや、現状困ったりしていることを書いてみる。

前置き

  • 対象のデータ（現在1テーブル）は、約1億レコード、50G。引き続き膨張していくことが予想される。
  • 今はmyisamで運用。更新処理が非常に高負荷。しかもロックが発生するため、オンライン中の更新ができない。1日1回新旧のテーブルを置き換えることで更新。
  • 目指すところは、参照のパフォーマンスを落とさずにリアルタイム更新。次の方向で検証中。
    • まずテーブルを参照時の条件に沿う形で分割。さらに更新する単位でパーティショニング。これにより参照＆更新のパフォーマンスアップを狙う。
    • myisamからinnodbにすることでロックなしでオンライン中の更新を実現にする。

検証環境

  • OS: CentOS 5.5
  • MySQL: 5.5.8
  • サーバの搭載メモリ: 32G

ibdata1、大きくなり過ぎ！

  • myisamと違ってinnodbのデータファイル（ibdata1）はデフォルトでは一つにまとまっていて、何もしないとこれが際限なく大きくなる。
  • db全体で100G程度のサイズ。でもデータの更新などを繰り返すうちに200G以上にサイズが膨れ上がった。つまりibdata1だけで100G以上に。
  • これを回避する方法は以下2つ
  • 際限のない膨張は回避できたが、既に膨れ上がったibdataのサイズを縮小するのはけっこう大変。
  • 最後もうひとつメモ。innodb_file_per_tableを設定した状態でテーブルのパーティショニングを行うと、パーティション単位でデータファイルができ、だいぶ扱いやすいサイズになった。

innodb_buffer_pool_sizeは欲張るな！

  • メモリの7割から8割のサイズを設定すると言われているが、とち狂って32Gに対して28G(約9割！)を設定してしまっていた。
  • 22G（7割程度）にしてかなりパフォーマンス改善。バランス大事。

更新処理時のデータのオーバーヘッドが大きい！

  • truncateしたあと、データの挿入を行うとデータファイルが500Mに。あれ？テーブルをoptimizeすると、300M程度のサイズまで減少。
    • innodbでは、「optimize」は「alter table t1 engine=innodb」と一緒！
  • 放っておくとdb全体の容量がどんどん大きくなるので、何らかの対応が必要。
  • 今回パーティショニングの単位でデータの入れ替えを行うことにしているので、さしあたり以下の流れ。
    • alter table t1 truncate partiton p1;
    • flush table t1;
    • insert into t1 〜
    • alter table t1 rebuild partiton p1
  • insertの後、テーブル全体でoptimizeするよりもパーティション単位にrebuildした方が効率がよさそう。

show table statusが遅い！

  • これがいまだに原因分からず、苦戦中。とても重い処理をやっているとは思えないshow table statusの実行に何秒もかかる場合がある。
  • show table statusに限らず、テーブルを参照するクエリなどを発行すると、プロセスの状態が「Opening tables」となっているのをよく見かける。
  • テーブル分割を行った結果、一つのスキーマに数百のテーブルができたが、これが原因かもしれない。
  • 引き続き検証必要。

というわけで

主に更新に関するあれこれを書いてみた。実際のところ更新に関しては、課題はある程度クリアできているのかなといった状況。
一方、参照の方のパフォーマンスについても少しづつ検証しているが、思ったようなパフォーマンスが出せず、けっこう前途多難な感じ。
innodbはけっこうじゃじゃ馬ですなぁ。
しかしまぁ良さを引き出してナンボなので、もっと突っ込んでやってみようと思う。楽しみつつ！


進展があればまた書いてみようと思います。