MySQLは,シンプルな機能と高速性が特徴である。そのため,無造作に使用していても高速に動作すると思われがちである。しかし,MySQLといえども高速で動作させるためには,それなりのテクニックが必要である。MySQLの利用者の増加とともにこれらのテクニックを求める声が多くなっている。今回は,高速化を行うための解析にスポットを当てる。
SQL文のチューニング
SQL文を改善するには,2つのステップで考えるといいだろう。どのSQL文が遅いのかを発見するのが最初のステップだ。発見したSQL文を改善することにより高速化が実現する。もし,ターゲットとなるSQL文がSELECT文なら,EXPLAINキーワードによって解析することが可能だ。
遅いSQL文の発見
データベースに対する処理のすべてが遅いのであれば,それはデータベース構造やデータベース・サーバー(ハードウエアやネットワークも含む)を疑った方がいいだろう。
しかし,実際には一部のデータベース処理だけが遅いという症状が多い。特に長時間の処理の中で,どのSQL文が遅いのか判らない場合には厄介である。
MySQLでは,実行時間が設定時間以上経過したSQL文を記録するためにSlowLogと呼ぶ機能を用意している。
今回は,以下のようなテーブルを使用した。
|
||||||||
|
||||||||
|
SlowLogの設定
環境設定ファイル(Windowsではmy.ini,Linuxではmy.cnf)に次のような設定を加えるとSlowLogが有効になる。
log-slow-queries SlowLogの有効化(ログファイル名を指定可能) long-query-time=2 SlowLogに記録する処理時間の上限 log-long-format インデックスを使用しないSQL文の記録long-query-timeパラメータは,SlowLogに記録するしきい値を秒単位で設定する。この場合には,2秒超える処理時間を費やしたSQL文を記録する。また,log-long-formatを指定すると,インデックスを使用しないSQL文もSlowLogに記録する。
SlowLogの確認
SlowLogが動作しているかどうかは,次のコマンドで確認できる。log_slow_queriesがONであれば有効となっている。デフォルトのファイル名は,「サーバー名-slow.log」となる。
mysql> show variables like 'log%'; +-------------------+------------------+ | Variable_name | Value | +-------------------+------------------+ | log | OFF | | log_update | OFF | | log_bin | ON | | log_slave_updates | OFF | | log_slow_queries | ON | | log_warnings | OFF | | log_error | .\zend-satou.err | +-------------------+------------------+ 7 rows in set (0.03 sec)long-query-timeの設定内容は,以下のコマンドで確認できる。
mysql> show variables like 'long%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | long_query_time | 2 | +-----------------+-------+ 1 row in set (0.00 sec)
SlowLogの表示例
SlowLogは,テキスト形式のファイルだ。処理時間がlong-query-timeを超えたSQL文が記録される。以下のような4行で1セットだ。
# Time: 040624 1:25:24 # User@Host: [ODBC] @ localhost [127.0.0.1] # Query_time: 5 Lock_time: 0 Rows_sent: 30670 Rows_examined: 38828 select * from city ,country ,language where country.code=city.country and city.country=language.country;1行目 記録日時
2行目 ユーザーIDとリクエストした端末
3行目 Query_time(実行時間) Lock_time(ロック時間) Rows_sent(送信行数) Rows_examined(処理対象となった行数)
4行目 SQL文
このように設定時間を超えるSQL文が克明に記録される。
また,log-long-formatを指定していれば,インデックスを使用しなかったSQL文も記録される。Query_timeに関係なく記録されるので,見分けられるだろう。
# Time: 040623 22:53:30 # User@Host: [ODBC] @ localhost [127.0.0.1] # Query_time: 0 Lock_time: 0 Rows_sent: 239 Rows_examined: 239 select * from country;SELECT文だけなく,DELETE文などの更新系のSQL文も記録対象である。
# Time: 040623 23:28:39 # User@Host: [ODBC] @ localhost [127.0.0.1] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 delete from city where country="USA";このようにSlowLogを活用すれば,極端に処理時間を費やすデータベース処理を探し出すことができる。データベースのチューニングだけでなく,実運用フェイズでの監視にも利用できる。
【訂正】
掲載当初、「SlowLogが動作しているかどうかは,次のコマンドで確認できる。log_slow_queriesがNOであれば有効となっている。」とありましたが誤りで、正しくは「log_slow_queriesがONであれば有効となっている。」です。お詫びして訂正します。