工場長のブログ

日々思ったことを書いてます。

innodbのチューニングとEC2のIO

AWS MySQL

ここでは、EC2の上でinnodbをチューニングして使うという観点でTIPSをまとめてみた。
RDS便利だから使おうぜってのは今回の話のスコープには含みません。
あと、innodbについて、割りとちゃんと調べてみたのは初めてだったりするので、間違ってる点など見つけたらぜひご指摘くださいませ。

innodb関連
バッファプール

    • ワーキングセットを乗せておくオンメモリのバッファ領域。読み書き共にこの領域を経由して実施される。
      • 参照時はバッファプール上でデータを探して、なければテーブルスペースから取得する。（そのデータはバッファプール上に格納される）
      • 書き込み時はリクエストを受け付けてワーキングセットを更新し、ログの書き込みへ移行する。
    • 設定はinnodb_buffer_pool_size
    • 監視はSHOW ENGINE INNODB STATUSか、mysqladmin extended-statusで取得できる「Free buffers」を見る。
    • 監視のポイントはFree buffersが枯渇(=ワーキングセットがページング)していないようにすること。

ログ

    • innodbでは、ログバッファに書いてからログの実ファイルが更新される。ログの更新が完了した時点で更新リクエストはコミット完了となる。（その先のテーブルスペースへの書き込みは非同期）
    • 実ファイルの更新時にデフォルトでは同期的にfsyncが走るようになっている。(ログの消失を防ぐため)
      • この設定を外すととても高速になる。が、ログの完全性が弱くなる(ACID属性が失われる)ので基本的に推奨されない。
        • 設定項目はinnodb_flush_log_at_trx_commit
    • ログの容量が大きいと、突然の大量の書き込みに対してログがバッファプール的に働いてくれるので、ディスクIOが平準化することができる。（ただし、大きすぎるとクラッシュ時の再起動時に時間が多くかかるようなので、ただ大きくすればいいわけではない）
      • 設定項目は以下の2つ
        1. innodb_log_file_size : ログファイルのサイズ
        2. innodb_log_files_in_group : ログファイルの個数
      • MySQL5.5まではログの容量が合計で4GBまでしか設定できない。5.6.3以降は512GBまで設定可能。
    • クエリ的にはログ書き込みが終了した時点でコミット完了が返る。
    • 監視項目としてはmysqladmin extended-statusで取れるdirtiy page(ログにだけ書き込まれて、テーブルスペースへの書き込みが終わっていないデータ)。これが線形に増えて行かないように監視する。これが増えてしまう場合には更新クエリの量を減らすかディスクのIOを改善するか検討する。
      • SHOW ENGINE INNODB STATUSでLog sequence numberからLast checkpoint atを引いた値を監視するのでもOK.

テーブルスペース

    • いわゆる実データが乗っているディスク領域。ログの書き込みとは別のディスクにしておくとベター。
    • ログの書き込み完了後、非同期に更新処理が走るので、理論的にはログが溢れてしまわない程度のIO速度があればOK。
    • ダブルライトバッファという仕組みがあり、これを使ってオールオアナッシングの書き込みを実現している。具体的には下記のようなフローとなる。
      1. ダブルライトバッファにデータを書き込む
      2. これに失敗したら書き込みは失敗とする（ナッシング）
      3. 次に実ファイルを書き込む
      4. ダブルライトバッファと比較して差異があれば書き込みは失敗とする（ナッシング）
      5. 一致していたら書き込みOK（オール）
      • zfsのようにfs側でオールオアナッシングな書き込みを提供してくれている環境の場合はオフってOK。設定項目はskip_innodb_doublewrite。

ここまで書いておいてアレですが、上記のフローとポイントを図にしてみた。
f:id:imai-factory:20121227210818p:plain

レプリケーション

  1. これだけでだいぶ長くなりそうなのでまた今度。。
  2. 今回ひとつだけ書いておきたいのは、参照専用マシンと更新専用マシンではワーキングセットの内容がぜんぜん異なったものになるはずなので、innodb的にもMasterとSlaveを分けると無駄なメモリを使わなくて済むようになると思う。

AWS関連

  1. EBSについて
      • Provisioned IOPSはブロックサイズ16KBで計算されている。そのため1000IOPSをプロビジョンした場合、ブロックサイズ16KB以下なら1000IOPS出るが、32KBのブロックサイズだと約500IOPSになる。
      • PIOPSは指定したIOPSを安定して発揮してくれるが、バーストはしない点に注意。
        • 性能を高めるよりも、性能を担保するイメージで利用するのがベター。
      • MySQLのデフォルトのページサイズは16KBなのでそのまま計算すればOK.
  2. インスタンスストアについて
    • EC2にはEBS以外に内蔵ディスクとしてインスタンスストアと呼ばれるブロックデバイスが付いている。
    • 現在USのリージョンで利用できるhi1などのインスタンスではこのインスタンスストアがSSDになっていたりするのでIOを求めるならこれを利用する。
    • インスタンスストアの注意点としては、インスタンスを落とすとデータがなくなること。
    • EBSとインスタンスストアの、一般的な特性の違いとしては、前者はランダムアクセスに強く、後者はシーケンシャルアクセスに強い。
  3. RDSについて
    • チューニングの話のスコープからは外してたけど、ついでにRDSの特徴も。
      • ストレージは多めに確保したほうがIOが出やすい。
      • Provisioned IOPSを利用する場合、EC2と違ってIOレートはストレージ容量の10倍に自動的に設定される。（変更不可）

まとめ

  1. ワーキングセットがページングしないようにinnodb_buffer_pool_sizeを設定しよう。監視すべきはfree_buffers。
  2. ログについてはdirty pagesが線形に増えないか監視。線形に増えるようなら更新クエリにディスクIOが追いついていない。
  3. ログのサイズは大きいほうが更新性能は上がるが、大きすぎるとクラッシュ時に復旧に時間がかかる。
  4. ログとテーブルスペースは別ディスクにするとベター。
  5. PIOPSは性能を高めるのではなく安定させるソリューション。
  6. RDS使うならディスク容量は大きめがベター。


参考にしたページ・資料など