SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

最新イベントはこちら!

Data Tech 2024

2024年11月21日(木)オンライン開催

EnterpriseZine Day Special

2024年10月16日(火)オンライン開催

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けの講座「EnterpriseZine Academy」や、すべてのITパーソンに向けた「新エバンジェリスト養成講座」などの講座を企画しています。EnterpriseZine編集部ならではの切り口・企画・講師セレクトで、明日を担うIT人材の育成をミッションに展開しております。

お申し込み受付中!

MySQLチューニング虎の巻

MySQL InnoDBストレージエンジンのチューニング(後編)

005


前回は、InnoDBのチューニングをするために知っておくべきInnoDBの特徴と基本的な構造についてごく簡単に紹介した。それらを踏まえた上で、今回はInnoDBをどのようにチューニングするかについて解説しよう。新しい用語も出てくるが、それらについては本稿において説明しているので安心して欲しい。まだ前回のエントリを読んでいないというひとは、まずそちらに目を通して頂きたい。

チューニングの基礎

 それでは、具体的にInnoDBでどこをチューニングするべきかを見ていこう。

バッファプール

 最も基本となるのがバッファサイズの調整だ。ワーキングセットが全てバッファに収まらない限り、バッファプールは大きければ大きいほど良い。その分ディスクアクセスが減るからだ。バッファサイズが小さいと、キャッシュミス時にディスクからReadするのに時間がかかり、I/Oがボトルネックになってしまう。予算のある限りメモリを目いっぱい搭載し、バッファプールに割り当てよう。InnoDBのバッファプールは、innodb_buffer_pool_sizeオプションで設定する。利用可能なメモリは、他の処理に必要な分を除いたすべてをInnoDBのバッファプールに割り当てよう。

 innodb_buffer_pool=32G

 ここで一つ注意がある。innodb_buffer_pool_sizeはバッファプールそのもののサイズを指定するオプションだが、InnoDBはバッファプールのサイズに比例してメモリを消費するオブジェクトが存在する。そのため、innodb_buffer_pool_sizeよりも5~10%ほど多くメモリを消費するということを覚えておこう。

ログの調整

 InnoDBでは、全ての変更はバッファプール上で行われ、後から遅れてバッファプールの内容がテーブルスペースへ書き込まれる。そして、永続性を保証するためにログに更新した内容を記録するようになっている。ログへの更新はシーケンシャルな書き込みになるためテーブルスペースへの書き込みに比べると高速だからだ。

 すると、必然的に「ログファイルおよびバッファプール上には存在するが、テーブルスペースには存在しない」というデータが生じることになる。そのようなデータを含んだページは「ダーティページ」と呼ばれる。当然、MySQLサーバーやOSがクラッシュすると、再起動後にはバッファプール内のデータは全て失われる。ここが重要なポイントなのだが、ダーティページのデータを復元するにはログにデータが存在している必要がある。そのため、どれだけのダーティページが存在できるかは、ログファイルのサイズにかかっている。ダーティページが増えすぎ、ログファイルの空き容量がなくなると、さらに更新を続けるためにはまずダーティページをテーブルスペースへフラッシュする必要がある。そうしなければ新たに更新によってダーティページを生成することができないからだ。

 どの道最終的にダーティページはテーブルスペースへ書きこまれなければならないので、ダーティページをたくさん保持出来ることはそれほどメリットがないと感じられるかも知れない。だが、ダーティページのサイズが大きいということは、次の2つの意味でメリットがある。

  • 一気にたくさんのデータが更新された場合、その更新によるディスクI/Oの負荷を先延ばしに(平滑化)できる
  • ダーティページがフラッシュされる前に再び更新される確率が上昇し、それにより必要なフラッシュの回数が減る

 従ってログファイルが大きい方が更新性能が高くなるというわけだ。ログファイルのサイズは、innodb_log_file_sizeおよびinnodb_log_files_in_groupで指定する。前者はログファイルひとつのサイズ、後者はログファイルの個数である。MySQL 5.5ではログファイルのサイズは合計で最大4GBまでという制限がある。だが、開発版ではMySQL 5.6.3から512GBまでログファイルを拡張することが可能になっている。大きなバッファプールを割り当てるなら、その分ログファイルも大きなものを割り当てよう。

 意外と見逃しがちなのがログバッファ。まだコミットされていないトランザクションは、最大限ログバッファ内にキャッシュしておくのが望ましい。従って、必要となるログバッファのサイズはワーキングセットサイズではなく、更新の量とディスクの速度によって変化する。アプリからの更新が多いようなら増やしてみて、効果を測定しよう。ログバッファのサイズはinnodb_log_buffer_sizeで指定する。

ログのフラッシュ方式

 トランザクションのコミット時にその内容はログファイルに書き込まれる。その際、デフォルトではディスクに確実に書き込まれるよう、fsyncによりディスクへの同期が行われる。ログの内容がディスクに残っていれば、マシンが突然クラッシュした場合などでもデータが消失することはない。ログへの書き込みはシーケンシャルなのでランダムアクセスと比較すると高速ではあるが、それでもディスクへの同期はとても時間のかかる処理だ。もし同期をしないで済むのなら、InnoDBの更新速度は飛躍的に向上する。

 例えば、レプリケーションのスレーブや準同期レプリケーションのマスターであれば、万が一クラッシュした場合でも他のサーバーからデータの復旧が可能であるため準同期レプリケーションを用いたHA化手法については、以前ブログで紹介したので興味があればそちらを見て頂きたい。参考:漢(オトコ)のコンピュータ道: 最強のMySQL HA化手法 - Semi-Synchronous Replication

 ディスクへの同期をOFFにするには、innodb_flush_log_at_trx_commitをデフォルトの1から変更しよう。

次のページ
ダブルライトバッファ

この記事は参考になりましたか?

  • Facebook
  • X
  • Pocket
  • note
MySQLチューニング虎の巻連載記事一覧

もっと読む

この記事の著者

奥野 幹也 (オクノ ミキヤ)

日本オラクル株式会社
MySQL Global Business Unitテクニカルアナリスト

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

EnterpriseZine(エンタープライズジン)
https://enterprisezine.jp/article/detail/3829 2015/08/25 08:13

Job Board

AD

おすすめ

アクセスランキング

アクセスランキング

イベント

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング