MySQLのチューニングを戦う方へ

本連載もついに最終回となりました。

本連載では、MySQLクエリーチューニングことはじめで予告した通り、「チューニング箇所の洗い出しのテクニック」について説明してきましたが、「チューニングの方法」については一切触れませんでした。

「本連載ではチューニングそのものの方法については詳しく説明しません。それは見出しの通り「銀の弾丸」などはなく、MySQLのパフォーマンスチューニングは計測と改善を繰り返し行っていくべきものだからです。そのため、特定のケースにマッチする改善の手法よりも、繰り返し使われる計測の手法にフォーカスを当てて説明していきます。」

その理由としてこの一文が全てではありますが、今回は参考までに筆者が考えるチューニングの指標を紹介したいと思います。それがあなたの環境に当てはまるかどうかは、これまでに紹介してきたツールなどを利用して計測してみてください。

チューニングの基本方針

基本的には、スローログをベースにインデックス追加で対応します。MySQLクエリーチューニングことはじめ でも説明した通り、SQLチューニングは決まれば100倍以上の性能向上をもたらすのに対し、パラメーターチューニングは多くの場合で再起動が必要な上に、どんなに決まってもせいぜい数倍までです。丁寧にパラメーターを変更/計測/評価するよりも圧倒的に時間対効率に優れています。

innodb_buffer_pool_sizeに対してInnodb_data_reads（バッファプールだけで完結せずデータファイルを読んだ回数） 、sort_buffer_sizeに対してSort_merge_passes（ソートバッファが足りず、テンポラリーファイルを利用してソートした回数）など、指標が明確なものは時間対効率が良い（そして効果が目に見えれば次をチューニングするモチベーションにつながる）ため、積極的にパラメーターチューニングをしていきます。測定する項目も決めずにぼんやりチューニングを始めて、「時間はやたらかかったが本当に良くなったかわからない」ということにならないようにしましょう。

繰り返しますが、パラメーターを変えるだけで劇的に全てが速くなる銀の弾丸はありません。基本的にはスローログをベースにインデックス追加やクエリーの書き換えを行います。パラメーターチューニングは「パラメーターが悪いことが明らかな場合」や「どうしてもクエリーチューニングで辿り着いた以上にレイテンシーを低くする必要がある」場合に行います。

クエリーチューニングの測定

パラメーターチューニングよりも圧倒的に効果が出やすいクエリーチューニングですが、それでも効率の良い/悪いクエリーチューニングはあります。スローログから見つけたクエリーには何はなくともEXPLAINを実行しましょう。EXPLAINの出力結果を少し斜に構えて見るヒントはSQL実行計画の疑問解決には「とりあえずEXPLAIN」しようで紹介しました（あまり斜に構えて見るより、まずは一度素直に見て、一通りインデックスを追加するなど検討した後に戻ってきてもう一度見るくらいが良いだろう）。

インデックスが全くないような圧倒的テーブルスキャンであれば非常に判りやすいのですが、そもそもインデックスで十分に刈り込みをしてもデータのフェッチが遅いようなケースは、クエリーチューニングだけではどうにもなりません。どこにボトルネックがあるのかを調べるには再現性のあるスロークエリーには「SHOW PROFILE」を試してみようで紹介したSHOW PROFILEやperformance_schema.events_stages_historyが利用できます。"Sending data"に時間がかかっている場合は「必要以上の（クライアントに転送しない）行やカラムをフェッチしていないか」、「バッファプールミスヒットによるものではないか」を一通り疑った上で、クエリーそのものの書き換えや諦めてそっと閉じるなどの判断をします。

クエリーチューニングでスロークエリーが解消したかどうかはスローログの集計に便利な「pt-query-digest」を使ってみようで紹介したpt-query-digestの--sinceオプションと--untilオプションを利用して前後の結果を比較するのが良策です。グラフ化などが好きであれば、記事中でも紹介した拙作のanemoeaterを利用すると簡単にグラフ化できます（anemoeaterはAnemometerを利用しやすくするためのユーティリティーであり、Anemometerそのものに関して筆者は無関係）。

また、クエリーチューニングが綺麗に決まってスローログに出力されなくなったとしても、MySQL 5.6以降のパフォーマンススキーマなら追いかけることができます。performance_schemaをsysで使い倒すで紹介した通り、sys.statement_analysisの内容をどこかに保管してsys.ps_truncate_all_tablesで一度統計情報をクリアします。クエリーのダイジェストは（クエリーが同じ限り）変更されないので、JOIN statement_analysis USING(digest)とすることで簡単にチューニング前後を比較できます。

パラメーターチューニングの測定

再起動しなくてもSET GLOBALで変更可能なパラメーターにはMySQLのリアルタイムモニタリングに「innotop」で紹介したtmux + dstat + innotopの組み合わせが便利です。

一方、再起動が必要なパラメーターを変更して効果を測定するには、「PMP for Cacti」でMySQLのステータスを可視化するで紹介したPMP for Cactiを利用すると良いでしょう。ただし、各種バッファやログサイズを変更した場合でも、すぐに効果は出ないことがあります。InnoDBバッファプールなどはウォームアップの問題もありますし（それでもMySQL 5.6以降でInnoDBバッファプールのウォームアップ ができるようになったため、比較的「再起動以前と同じバッファプールの状態」まで持っていきやすくなった）、そもそも「今まではバッファプールが足りなくなって性能が頭打ちになっていた状況でも性能が落ちなくなる」ことを期待して上限を上げるケースでは「その状況」になるまでは変更前からパフォーマンスが良くなることはないはずです。長期的な目でも比較することを忘れないでください。個々のクエリー単位での変化を観察するには、前の段落でも紹介した `sys.statement_analysis`の中身を保管して比較するテクニックも利用できます。

なお、もっと雑に計るのであればmysqladmin -i 1 -r ex | grep Handler_commit（秒間コミット数を出力する）やwatch 'mysql -Ee "SHOW SLAVE STATUS" | grep Seconds_Behind_Master'（Seconds_Behind_Masterの値を毎秒表示する）など、ワンライナーで特定の数値にだけ注目する方法もあります。いずれにせよ、パラメーターチューニングはクエリーチューニングに比べて効果測定が面倒なので、お気に入りの測定方法をいくつか身に着けておくと良いでしょう。

パラメーターの別の側面

MySQLに限らず、パラメーターは「ハードウェアのリソースを使いきらないための安全弁」という側面があります。メモリーを4GBしか積んでいないサーバー上でinnodb_buffer_pool_size = 32GBと指定すれば、当然物理メモリーに全てが収まりきらずにスワップするか、アロケートに失敗して起動できません。逆にパラメーターを小さく設定しすぎるとハードウェアのリソースを使いきれなくなり、大きく設定しすぎるとハードウェアのリソース待ちが大きくなって性能が下がったり不安定になったりします。

パラメーターの値（割り当てリソース）をゼロから無限に大きくしていく場合、そのスループット（性能）は下記のようになります。

パラメーターが適正値より小さい場合はパラメーターを上げれば上げるほどスループットも上がっていきます。逆にパラメーターが適正値より大きすぎる場合は、パラメーターを上げれば上げるほどスループットは下がっていきます。ただし、この模式的なグラフ自体、並列するクエリーの数やちょっとしたカーディナリティーの違い、他のパラメーターとの兼ね合いで左右に揺れますので、「先週は最適だったパラメーターが今日は少し足りていない」ように変化することもあります。

パラメーターチューニングは、このようにゆらゆら揺れる台形の上でボール（パラメーター）のバランスを取るようなものです。パラメーターとは最適な「値」で考えるよりも最適な「レンジ」を考え、多少台形が左右に揺れてもボールが転がり落ちない状態を維持できるように調整します。計測しながらパラメーターを調整することで、「パラメーターを大きくしたら性能が上がった/下がった/変わらなかった」を基準に、現在の状態が台形のどの部分に位置するかを推測していきます。実際のスループットは複数のパラメーターに影響されたり、パラメーターを変更したためボトルネックが別のパラメーターに移っていく（例えば、InnoDBバッファプールを大きくするとInnoDBログファイルサイズが足りなくなり、ログファイルサイズを大きくするとダーティーページの閾値に引っかかってしまう、など）こともありますが、1つずつ丁寧に計測していくことが大事です。

2つ以上のパラメーターを一度に変えてしまうと、それぞれの影響が判らなくなるため（Aのパラメーターは割り当て過ぎのため増やすと本来性能が落ちるが、Bのパラメーターは割り当てが少なかったため増やして性能が上がった。足し合わせると変更前と変わっていないように見える）、原則2つ以上を同時に変えるのはやめておいた方が良いでしょう（再起動が必要なものもあり一度にやりたくなる気持ちもあるが、少なくとも検証フェーズでは1つずつやる）。

ハードウェアリソースとの兼ね合い

ハードウェアリソースはあるに越したことがないのは当然として、それがパラメーターチューニングにどのように関わってくるのかというと、足りない場合は以下のようにリソースの競合が始まる地点が左にズレることになります（ハードウェアリソースが十分に余っている場合は、逆、競合が始まる地点が右にズレる）。多少の左右ならば「パラメーターから余裕度が減る」だけで済みますが、要求される処理に対して著しくリソースが足りていない場合は「本来必要なパラメーターの値より前にリソースの奪い合いが発生する点が来てしまう」ことがあります。

この状態になっていると、「一番性能が出る地点」にパラメーターを落ち着かせるのは非常に困難です（現実でもゆらゆら揺れる三角形の頂点にボールを乗せることは困難だろう）。「パラメーターを大きくしたら性能が上がった/下がった/変わらなかった」を基準にパラメーター調整を考える場合でも、「パラメーターが少し足りていない状態」（三角形の頂点の少し左側）からパラメーターを大きくして、三角形の頂点を飛び越えて「パラメーターを大きくしすぎた状態」（三角形の頂点の少し右側）に推移してしまうケースが考えられます。こうなると「パラメーターを大きくして性能が下がったから少し小さくしよう」と考えても、もともとの位置は頂点の少し左側なので「パラメーターを下げると性能が下がる」状態になってしまいます。

こうなってしまうと、一度に上げ下げするパラメーター量を十分に小さくして計測しなければなりませんが、それでも（左右に揺れる性質上）最適な値を探すのは困難ですし、計測のための工数が圧倒的に増えてしまいます。筆者としてはこの状態（パラメーターを上げても下げても性能が下がる）になった場合は時間対効果の面から「そっとしておく」ことにしています。また、難易度が非常に高いので、このケースはパフォーマンスチューニングの上級者でも難しいでしょう。

逆に考えると、ハードウェアリソースを潤沢にすることで「台形の上底を長くできる」ため、パラメーターチューニングの難易度が下がります（時間対効果が上がる）。筆者は、特にパラメーターチューニングに自信がないうちは、リソースに多少余裕を持たせて経験値を積むことで感覚を掴むのが良いと考えています。

グローバルスコープとセッションスコープのパラメーター

MySQLでは、パラメーターに「グローバルスコープ」と「セッションスコープ」の2つのスコープがあります。グローバルスコープのパラメーターはSET GLOBALで設定し、SHOW GLOBAL VARIABLESやSELECT @@global.xxxの形でアクセスします。セッションスコープは GLOBALキーワードの代わりにSESSIONキーワードを使うか、または省略した場合にセッションスコープの値を設定・確認できます。グローバルスコープしか持たないパラメーター（innodb_buffer_pool_sizeなど）とグローバルスコープとセッションスコープの両方を持つパラメーター（ sort_buffer_sizeなど）の両方が存在しますが、注意しなければならないのは後者です。

誤解されがちですが、このタイプのパラメーターは「セッションスコープの値が実効値」であり、「グローバルスコープの値はセッション確立時にセッションスコープにコピーされるデフォルト値」です。この動作は「SET GLOBALでパラメーターを変更しても、接続済みのセッションには影響を及ぼさない。セッションが次に接続された時から有効になる」という形で目に見えてきます。アプリケーションが問い合わせのたびにMySQLへ都度接続し直すタイプのものであれば些細な違い（既存のセッションはやがて破棄され、新しく接続されることが期待できる）ですが、コネクションプールを利用している環境ではこの動作は無視できません（特にJavaを利用しているアプリケーションエンジニアで、「パラメーターを変更したからアプリケーションを再起動してくれ」と言われた経験はないだろうか。 それは「アプリケーションを再起動してセッションを再接続してもらわないとパラメーター変更が有効にならない」ということだ）。

このように（人によっては）あまり直観的でないグローバルスコープとセッションスコープを両方持つパラメーターですが、「実効値はセッションスコープである」ことを逆手に取ることもできます。例えばオンラインのトラフィックではsort_buffer_size = 256kで十分ですが、大量のソートが必要な1日1回実行されるバッチのようなソートバッファを大きく確保しておきたいケースでは、バッチのSQLの前にSET SESSION sort_buffer_size = 12Mと追加することで、そのセッションでだけソートバッファを大きくできます。バッチが終わったセッションがそのまま破棄されるのであればそのまま特にすることはありませんし、バッチ終了後はオンラインのトラフィック処理に回るのであればSET SESSION sort_buffer_size = DEFAULTのようにDEFAULTキーワードを利用することで元の（グローバルスコープの）値に戻すことができます。

まとめ

チューニングは原則クエリーチューニングから行い、クエリーチューニングのみで解決できない場合にパラメーターチューニングを行う方が時間対効率が良くなります（明らかにパラメーターが悪い兆候が出ている場合はそのパラメーターを先にする）。パラメーターは「リソースを使いきらないための安全弁」であることに注意してください。ハードウェアリソースが潤沢にある方がパフォーマンスチューニングの難易度は下がります。パラメーターの最適値は「点」ではなく、パフォーマンスが平坦化する「面」と考えてください。

パフォーマンスチューニングは「原因を推測し」「原因を修正し」「計測して原因の推測が正しかったかどうかを確認する」ことの繰り返しです。特にパラメーターチューニングでは長いスパンでの計測が必要なことが少なくありません。自分の手に馴染む計測手法をいくつか揃えておくことは、今後のスキルアップにもつながってきます。本連載で紹介した手法が、みなさんのカードの1枚になってもらえれば幸いです。