MySQL のチューニング

中々すっきりしない天気が続きますね。早くスカっっっと晴れて夏本番突入してほしいものです。こんにちは、nakamura です。

さてさて今日は MySQL のパフォーマンスチューニングについて書かせて頂きます。先日、某案件にて簡単にですがチューニングを行う機会があり、備忘録的にいじくったパラメータとその説明を残しておこうと思います。

MySQL の設定パラメータは非常に多岐に渡りますが、パフォーマンスを改善したい際に主に修正するのはキャッシュやメモリ関連のパラメータです。という事で今回はキャッシュ・メモリ関連の設定項目をいくつかご紹介させて頂きます。

key_buffer_size

レコード数が膨大なテーブルで効率良く検索を行う為にインデックスを使用される方は多いと思いますが、この key_buffer_size を適切に設定しておかないとせっかく作成したインデックスも意味のないものになりかねません。key_buffer_size とは作成したインデックス情報をメモリ上に留めておく事が出来る最大バッファサイズの事です。マニュアルページでは搭載された物理メモリの 1/4 が一般的というような事が書かれていますね。実際には他のアプリケーションとの兼ね合いもあるので、メモリ使用率を確認しながら適切な値に調整するのが良いでしょう。

thread_cache_size

通常 MySQL はクライアントからの接続ひとつに対してスレッドをひとつ生成し、処理が終了して接続が切断されると生成したスレッドも同時に終了します。実はこのスレッドの生成、終了というのがアクセスが増えてきた際にバカにならない処理になってくるのです。thread_cache_size は指定した数のスレッドを終了せずに立ち上げっぱなしにして、次回以降の処理に再利用してくれるパラメータです。メモリに余裕があればある程度大きな値にしても問題ありませんが、あまり大きくしすぎると不要なスレッドが常に待機し続けるような状態になるので、サイトのアクセス規模や DB への接続数と照らし合わせて適宜調整しましょう。

read_buffer_size

インデックスを使用しない検索時に確保されるメモリバッファの容量です。ただし、このパラメータを上げるぐらいならインデックスを使用した方が効率が良いようなので、まずはインデックス作成によるパフォーマンス改善を試みてみましょう。

join_buffer_size

テーブル結合時に使用されるメモリバッファの容量です。JOIN 節を頻繁に使う場合には上げておいた方が良いですが、こちらも read_buffer_size 同様にインデックスを使わない処理にのみ参照されるパラメータです。まずはインデックス作成での解決を図りましょう。

sort_buffer_size

ORDER BY や GROUP BY の処理に使用されるメモリバッファの容量です。

query_cache_type, query_cache_size

クエリの実行結果をキャッシュしてくれる設定です。 query_cache_type は 0 なら無効、1 なら SELECT 以外の文字列から始まるクエリのみをキャッシュ、2 なら SELECT から始まるクエリのみをキャッシュしてくれます。query_cache_size はキャッシュに使用するメモリの最大容量です。あまり大きくしすぎると変更がすぐに反映されない事もあるようなので適宜調整しましょう。その他に query_cache_limit 等も必要に応じて設定しましょう。（指定したサイズよりも大きい実行結果はキャッシュしないようにする設定です）

WEB アプリケーションと言えばほとんどの場合データベースと連動しています。作ったアプリケーションが重たくてしょうがない！そんな時にちょっと目線を変えてデータベースや WEB サーバのチューニングを見直してみると、案外劇的にパフォーマンスが改善する事もありますよ！