dorivenの日記

気がついたら社会人。気になる技術的なことについて少しずつ書いていけたらと思っております。

【Sunrise2nd 復習】パフォーマンス・チューニング

どうも、またまたスノーボードに行ってきました。

前回の筋肉痛は服を着替えるのも、リュックサックを背負うのもしんどかったのですが、

それに比べると全然酷くないので少しは慣れてきたかな、なんて思ってます。

最近、こんなにスノーボードに行ったりするのもこれから離れる土地でしか出来ないことを沢山やりたいから、

ということでスノーボードに取り組んでたりします。

さて、それでは今回はMySQLのパフォーマンス・チューニングです!


「Sunrise2nd」で使用しているMySQLの参考書

参考書は上記の本を使用している。

あまり内容を書きすぎると色々と問題にもなるので、もっと内容を深く知りたい方は上の広告をわざわざクリックして飛ばなくてもいいので、買って読んでみることをオススメします。

現在まで4回の講義が行われているが、今回は4回目の最終回に行われた「パフォーマンスチューニング」について触れたいと思う。

パフォーマンス・チューニング


・チューニングの種類


RDBMSのチューニングでは、「単体チューニング」と「システムチューニング」の2つに大別できる。

  • 単体チューニング
    個々のSQL文の実行性能を改善する作業。
    インデックスの作成やSQL文やアプリケーションロジックの修正・見直しによってチューニングを行う。
  • システムチューニング
    単体チューニングに対して、システムチューニングは並列動作時(大規模な動作におけるパフォーマンス・チューニング)を指す。
    MySQLの設定(クエリキャッシュ、InnoDBバッファプールなど)、ディスク・ネットワークI/Oなどを対象としてチューニングを行う。

チューニングは以下の繰り返しを行う。

・単体チューニング


RDBMSではSQLの書き方によってパフォーマンスが大きく異る。

単体チューニングでは、どのような手段を用いてボトルネック特定し、改善するのかについて解説していく。

単体チューニングでの基本用語

まず手段を説明する前に前提知識として出てくる基本的な用語について説明を行う。

  • フルスキャン
    下記に示す2つの検索の総称をこのように呼ぶ。
  • 全表検索(フルテーブルスキャン)
    テーブルの中身をすべて読み込む検索
  • 全索引検索(フルインデックススキャン)
    インデックス部分だけを読み込む検索
スロークエリログ

MySQLには後述するフルスキャン、一定以上の時間がかかったSQL文をログに書き出す機能がある。

これを「スロークエリログ」と呼び、ボトルネック特定に役に立つ。

スロークエリログの吐き出し先や設定を確認するには以下の構文を用いる。

# スロークエリログの書き出しが有効、吐き出し先の確認
SHOW GLOBAL VARIABLES LIKE '%slow_query%';
# 結果
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

# スロークエリログに書き出される条件の時間を表示
SHOW GLOBAL VARIABLES LIKE '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

# 出力する形式
SHOW GLOBAL VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

これらの設定は /etc/my.cnf の中身を変更することで設定を変える事ができる。

# /etc/my.cnf
long_query_time = XX # 単位は秒
log-slow-queries = YYY # スロークエリログの出力先パスを設定

このスロークエリログの中身に書いてあるSQL文が主なボトルネックの原因になっているので、

そのSQL文が発行されているアプリケーションを特定し、改善することでパフォーマンスのチューニングが行える。

また、初期化パラメータに「"log-queries-not-using-indexes"」を指定することでフルスキャンを行った全てのSQL文を実行時間にかかわらずスロークエリログに出力するように出来る。

スロークエリログの解析

参考書にはスロークエリログの解析に二通りのやり方が示されている。

  • mysqldumpslow
    mysqlに付属されている解析ツール。
    スロークエリログ内のログを集計し、見やすい形に整形して表示してくれる。
  • EXPLAIN
    スロークエリログやmysqldumpslowを通して問題のあるSQL文を特定したら使用する。

今回は2番目のEXPLAIN文に関して説明を行っていく。

EXPLAINによる実行計画の確認

MySQLでは以下の順序でSQL文を実行している。

  • SQL文の構文解析(文法チェックなど)
  • SQL文の実行計画生成
  • 実行計画に従って処理

実行計画とはどのようなインデックスの使用の有無、どのインデックスを利用するのかといったテーブルへのアクセスの一連の流れ。

この実行計画はSQL文の書き方ひとつで大きく変わるため、パフォーマンスの解析に大きな役割を持つ。

EXPLAINはその実行計画を確認するためのMySQL独自の機構で、一般的にはこいつを使って単体チューニングを行う。

EXPLAINの実行方法は以下のように実行計画を知りたいSQL文の前にEXPLAINを付け足すだけである。

CREATE TABLE t1 (
    a INT, 
    b INT, 
    KEY(a)
) ENGINE=INNODB;

# SELECT a,b FROM t1 WHERE b = 10;の実行計画を知りたい場合

EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where with pushed condition
1 rows in set (0.00 sec)

色々なパラメータが沢山出てきて分からないだろうが、最初に見る点は下記の3つだけで十分。

  1. possible_keys
    検索に使えるキー(インデックスなど)の候補を出力する。
    このキーがなければそもそも一意な検索が出来ない。
  2. keys
    実際に検索に用いられたキーを出力する。
    ここにはオプティマイザに解釈によって意図しないキーが検索に用いられる場合があるので、それを確認するために見る。
  3. rows
    MySQLがこの検索にどれだけの行を参照するかを出力する。
    ここが多ければ多いほど負荷が大きくなり、処理時間が増える。
    ただし、これはあくまでMySQLの見積もりであり、実際の検索数ではない点に注意。

もっと細かいチューニングを施したい場合はtypeに書かれている動作種類を見るのがいいが、ここでそれは省略する。

これでMySQLオプティマイザやSQLのテーブルのインデックスの不備により低速になっていた場合はすぐに気づく事が出来る。

・システムチューニング



こちらはVOYAGE GROUPのSunriseでもかなり触れた部分なので感慨深いですね。

その時はシステムチューニングなんて言葉を知らずにやっていましたが(^^ゞ

システムチューニングといっても実際MySQL内部でやれるのはパラメータの変更で、

どちらかといえばネットワーク機器やハードウェアの見直しなどがメインになります。

そちらの部分は今回は触れませんが…

クエリキャッシュ

クエリキャッシュとはSELECT文の実行結果をそのままメモリ上に格納し、キャッシュする機能。

まったく同じ内容のSELECT文が送られてきた場合、キャッシュからすぐさま結果を返す。

これにより、ディスクアクセスによるオーバーヘッドがなくなる。

ただし、空白が入っているだけでも違うSQL文として見られる。

もちろん、WHERE句の中身が違うだけでも同じである。

my.cnfの設定で以下の値を変更することで、メモリ領域の割り当ての変更が可能。

query_cache_size=xxM # デフォルトでは32、100~200が推奨されているらしい
クエリバッファサイズ

InnoDBだけを使用する際、メモリバッファに格納する容量を変更することで高速化が見込める。

クエリキャッシュと同様にディスクアクセスを減らすことでオーバーヘッドが減り、高速化される。

my.cnfの設定で以下の値を変更することで、メモリ領域の割り当ての変更が可能。

innodb_buffer_pool_size=xxM

こちらに設定する値はメモリ全体の7~8割を割り当てるのがいいそうです。

ただし、あまりにも割り当てすぎるとメモリスワップが発生してしまい、結果的に遅くなってしまうので

大きな数値を割り当てればいい、という訳ではない点に注意してください。

・おわりに



みなさん、最後まで読んで頂きありがとうございました。

これでSunrise2ndで学んだMySQLの講座の復習を終わりたいと思います。

普段は中々知ることが出来ない知識を沢山知ることが出来たので、非常に面白い講義でした。

次回は前から言っていたCentOSについて本を参考にしながら順次書いていくか、javascriptで腹落ちしていなかった機能について

ある程度理解が深まったので、それのどちらかをやっていこうと思います!