MySQL初級者を脱するために勉強してること -調査編-

テーブルを4,5個結合して表示するような管理画面や大量のデータを扱うようなバッチ処理を作成していると、
重くて画面が描画されるまでに時間がかかる、定期的にまわすバッチ処理が終わらない…なんて事に直面する事がよくある。
欲しいデータを取得するくらいにはSQL書けるし、システム要件を満たすくらいにはテーブル設計は出来る、そんな僕が中級者を脱するために勉強している内容を備忘録的に書き綴ります。

予約語は大文字 その他は小文字で記述しています。

間違ってるところやチューニング方法があればコメントで教えていただけると幸いです。

スロークエリ

まずはボトルネックとなっているSQLをあぶり出す事からはじめる。
MySQLには処理に時間がかかっているSQLを物理ファイルに書き出してくれる機能があるので、その機能自体を有効にする事からはじめる。
出力されたログファイルは実行に時間がかかっている順に出力されるため、ファイルの上位にあるSQLから見て行くと良い。

ただし下記設定を行うとログの書き出しなどに時間がかかったり、ハードディスクの容量を圧迫したりするので、本番のサーバでは行わず開発環境で行うようにする。
必要なログの集計が完了したら、必ずパラメータは元に戻すこと。

sql

-- スロークエリのログを取る
SET GLOBAL slow_query_log = 1;
-- ログ出力先
SET GLOBAL slow_query_log_file = '/tmp/slow_query.log';
-- ログを取るSQL実行時間 下記例だと1秒以上かかっているSQLのログを取得
SET GLOBAL long_query_time = 0;

SQLの分析

スロークエリが抽出出来たらそのSQLの先頭にEXPLAINを追加して実行してみる。

Using filesortとUsing temporaryは最悪な組み合わせ
Using filesortはソートに必要な領域がメモリ上に乗り切らずに物理ファイルに書き出しソートを行う。
Using temporaryはクエリを実行するのにテンポラリテーブルが作られる。
リアルタイム処理を行うようなシステムの場合はUsing filesortとUsing temporaryが表示されたら改善必須

対策としては、SQLがインデックスを利用するように修正するかINDEXをテーブルに作成する。
INDEXは別の記事にまとめます。

パラメータ

漢のコンピュータ道さんのMySQLを高速化する10の方法を参考にさせていただいたので、
内容はほぼほぼ同じ…何かあれば追記していきます。

innodb_buffer_pool_size

InnoDBだけを利用する場合は空きメモリの7〜8割程度を割り当てる最も重要なバッファである。
実際にはここで割り当てた値の5〜10%ぐらいを多めにメモリを使うので注意が必要。

引用 漢のコンピューター道

空きメモリを計算するにはvmstat -aコマンドを利用する。

freeとinactの値を足した物が空き容量でfreeは言葉通りだがinactはページキャッシュや無名ページのうち、ストレージとの同期も完了しているためすぐに解放される領域
activeはページキャッシュや無名ページのうちストレージとの同期が完了していないすぐには解放されない領域

上記の例だと51888 + 141024 = 192921Kバイトである。メガバイト表記にしたい場合は-SオプションでMを指定するとメガバイト表記になる。
vmstat -a -S M

時間帯によってトラフィック量が変わるようなシステムの場合は当然一番トラフィックが集中する時に計測しないと意味がない。
一つのマシンにDBとWebサーバを同居させている場合はなおさら。

key_buffer_size

INDEXをメモリにどれだけのせるかという値デフォルトでは8Mバイトらしいので、MyISAMだけを利用する場合は、空きメモリの3割程度割り当てると良い。

引用 漢のコンピューター道

一方InnoDBの場合はデータもインデックスもMySQLがキャッシュ管理するためinnodb_buffer_pool_sizeの方が重要っぽい。この辺は実際に試していないので、ちょっと不安

sort_buffer_size

ソート処理に利用するバッファである。OLTPでは256K〜1Mぐらいを割り当てると良い。これがあまり大きすぎると、メモリの割り当てのオーバーヘッドが大きくなるので注意しよう。DWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。

引用 漢のコンピューター道

read_buffer_size

全体スキャンする時に利用するバッファ。
OLTPでは128K〜512Kぐらいを割り当てると良い。

引用 漢のコンピューター道

read_rnd_buffer_size

ソート処理でインデックスを利用する場合に利用するバッファ。
OLTPでは256K〜1MぐらいをDWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。

引用 漢のコンピューター道

RDSの場合

RDSでMySQLサーバ(InnoDB)を作成している場合はチューニングはほぼほぼRDSが自動でやってくれるため、
パラメータの変更などは必要ないらしい。
唯一必要なのはlatin1から文字コードを変更するくらい。

参考にさせていただいたサイト

漢のコンピュータ道 MySQLのEXPLAINを徹底解説!!
漢のコンピュータ道 5.1のスロークエリログ
漢のコンピュータ道 やってはいけない!!MySQLに悲鳴をあげさせる10の方法