SHOEISHA iD

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

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

最新イベントはこちら!

Security Online Day 2025 春の陣(開催予定)

2025年3月18日(火)オンライン開催

Enterprise IT Women's Forum

2025年1月31日(金)17:00~20:30 ホテル雅叙園東京にて開催

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

お申し込み受付中!

EnterpriseZine(エンタープライズジン)

EnterpriseZine編集部が最旬ITトピックの深層に迫る。ここでしか読めない、エンタープライズITの最新トピックをお届けします。

『EnterpriseZine Press』

2024年秋号(EnterpriseZine Press 2024 Autumn)特集「生成AI時代に考える“真のDX人材育成”──『スキル策定』『実践』2つの観点で紐解く」

MySQLチューニング虎の巻

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

005


ダブルライトバッファ

 InnoDBは、テーブルスペースを更新中にOSがクラッシュした場合などにデータの不整合が起きないよう、ダブルライトバッファという仕組みを用いている。ダブルライトバッファは、バッファという名前が付いているが、テーブルスペース内に存在する領域だ。InnoDBはテーブルスペースへ実際の更新を行う前に、いったんダブルライトバッファに同じデータを書き込む。その後、改めて該当するページを書き換えるのだ。そうすることで、もしテーブルスペースの更新が中途半端になってしまっても次のようなロジックで不整合を解消することができる。

  • ダブルライトバッファへの更新が中途半端になってしまった場合、ダブルライトバッファの内容を破棄する。(テーブルスペースでは不整合はそもそも起きていない。)
  • テーブルスペースへの更新が中途半端になってしまった場合、ダブルライトバッファの内容で該当するページを更新する。

 当然ながら、ダブルライトバッファへの更新は余計なI/Oを生じさせることになる。ZFSのようにファイルへの更新がアトミック(オール・オア・ナッシング)であることが保証されているファイルシステムを使用している場合には、ダブルライトバッファをOFFにすることでI/Oの帯域を節約することを考えよう。ダブルライトバッファを無効化するには、skip_innodb_doublewriteオプションを使う。

ファイルシステム

 InnoDBのI/O性能は、当然ながらその下にあるストレージやファイルシステムの影響を受ける。特に意識すべきなのは、InnoDBが16KBのページ単位でデータを管理しているという点である。そのため、ファイルシステムやストレージのI/Oの単位が16KBになるのが望ましい。RAIDカードやストレージ装置を使用している場合などには、ストライプサイズを16KBに設定するといいだろう。大きなストライプサイズにするとシーケンシャルアクセスは高速になるかも知れないが、16KB単位のI/Oが必要な場合には余分なデータを読み込むことになってしまう。だが、ストレージ装置によっては内部の構造に起因して、ストライプサイズを16KBより大きくした方が性能が出るものもあるかも知れない。もしそういう話があるならストライプサイズを変更してベンチマークをとってみよう。最終的にはInnoDBを利用してベンチマークをとるべきだが、bonnie++などを利用したベンチマークもある程度指標になるだろう。

 また、ファイルシステムにもI/Oの単位(ストライプサイズ、チャンクサイズ)を調整するパラメータがあるので調整を行うと良いだろう。また、そもそもファイルシステムの種類によって性能が変化するので、異なるファイルシステムを検証することも必要だ。

 普段のInnoDBのI/O性能とは関係ないが、ファイルを削除する速さが重要な場合もある。(innodb_file_per_table使用時にDROP TABLEをした場合など)ext3ではファイルの削除に時間がかかることが知られているので、削除の性能が重要な場合にはext4やxfsの使用を検討すると良い。

 ファイルシステムを使わずにRAWデバイス上にテーブルスペースを作成することも可能なので、バリエーションの一つとして検討するのも良いだろう。

 どのファイルシステム(またはRAWデバイス)を使用すれば最適な性能が出るかという問いに対して唯一の回答は存在しない。使用するストレージによって最適な組み合わせは異なるからだ。最適な組み合わせを見つけるのは少々骨が折れるかも知れないが、ワーキングセットが大きくI/O性能が問われそうな場合には、チューニングする価値はあるだろう。

I/Oの調整

 InnoDBがディスクの性能をフルに活かし切るためには、I/Oまわりのパラメータを調整するようにしよう。もし、あなたがMySQL 5.0以前のバージョンまたはMySQL 5.1のビルトインを使っているならば、今すぐMySQL 5.1のInnoDB PluginかMySQL 5.5へアップグレードしよう。話はそれからだ。

 InnoDBは専用のスレッドでI/Oを行う。Plugin以降のバージョンでは、I/O用のスレッド数を調整できるようになっている。(古いバージョンにはそのような機能はない。)I/O用にいくつのスレッドがあるかということは、最大で何多重でI/Oリクエストを発行するかということに言い換えることができる。何多重でI/Oリクエストを行うと最もパフォーマンスが出るのか?というのはInnoDB側からは分からない。OS、ストレージ、ファイルシステム次第だからだ。Read/Writeがそれぞれ独立して多重度を決められるようになっていて、デフォルトは双方4だ。I/Oスレッド数は次のように指定する。

 innodb_read_io_threads=8
 innodb_write_io_threads=8

 I/Oが少ないならデフォルトのまま使っていても良いが、I/Oがボトルネックになりそうな場合には、ベンチマークで最適値を算出しておくといいだろう。

 もうひとつ検討すべきパラメータにinnodb_io_capacityがある。これはダーティページのフラッシュ、挿入バッファのマージといったバックグラウンド処理に対するI/Oの上限を設定するパラメータだ。デフォルト値は200。innodb_io_capacityが小さいとバックグラウンド処理がたまってしまう。バックグラウンド処理が溜まっている状態とは、すなわちダーティページが増えている状態のことだ。innodb_max_dirty_pages_pctで決まる許容量までダーティページが増えるか、ログを使いきってしまうと、古いダーティページをフラッシュするまで一切の更新処理が止まってしまう。従って、安定した運用をするには適切なバックグラウンドI/Oが必要となる。ただしinnodb_io_capacityが大きすぎると、現在進行中のトランザクションの性能に影響が出てしまうので注意しよう。

 また、当然ではあるが、たくさんの更新処理を捌くには速いディスクが必要になる。innodb_io_capacityはディスクの速度(IOPS)以上に設定することは意味がないからだ。更新処理が多い場合には、パラメータの調整以前にハードウェアの選定の時点で考慮が必要となるわけである。innodb_io_capacityの効果を測定するには、ログを使い切る、またはダーティページの許容量を使い切るような更新の負荷を掛けなけれならない点に注意しよう。目いっぱい更新の負荷をかけてもログを使い切らない、ダーティーページが増えないという状況ならオッケーだ。

 innodb_io_capcity=1000

 デフォルトではテーブルスペースが自動拡張するようになっているが、これはキャパシティ管理およびI/O性能の観点から好ましくない場合がある。安定した運用を突き詰めるなら、自動拡張ではなくデータファイルのサイズを決め打ちにするといいだろう。ファイルシステムによってはフラグメンテーションを防ぐ効果も期待できる。

 innodb_data_file_path=ibdata1:200G

 また、テーブルスペースへの更新のポリシーはDirect I/Oが良いとされている。ファイルシステムキャッシュに余分なメモリが割り当てられなくなるからだ。その分バッファプールにメモリを割り当てるといいだろう。

 innodb_flush_method=O_DIRECT
 ※Windowsでは変更不可

次のページ
NUMA

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

  • 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つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

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

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

メールバックナンバー

アクセスランキング

アクセスランキング