MySQLをチューニング,そしてスケールアップ/スケールアウトへ

第5回 MySQLチューニング(4) SQLチューニング基礎

この記事を読むのに必要な時間:およそ 6 分

第5回はSQLチューニング基礎として,チューニング対象とすべきSQLの見つけ方と利用可能なツールについて解説します。これまでの連載でご紹介したMySQL WorkbenchのパフォーマンスレポートやMySQL Enterprise Monitorのクエリアナライザ以外にもチューニング時に利用する機能やツールがあります。

スロークエリログ

MySQLサーバで実行されたSQL文の中からチューニング対象とすべきものを見つけるシンプルな方法が,スロークエリログです。スロークエリログは設定された閾値(long_query_time)を超えたSQL文を記録するログです。long_query_timeは秒単位での指定ですが,精度はミリ秒単位となっています(250ミリ秒を指定する場合は「long_query_time=0.25」と設定します)⁠

スロークエリログはデフォルトではOFFになっています。設定にslow_query_logの項目を追加すると有効になります。slow_query_log_fileでログファイル名を指定しますが,ファイル名を指定しない場合のファイル名は「ホスト名-slow.log」となります。

スロークエリログで記録する管理系SQL文の制御

データベース管理者による実行が中心となると想定されている下記のSQL文に関してはデフォルトで,仮に閾値の時間を超えてもスロークエリログに記録されません。

以下のSQL文もスロークエリログに記録するには,log_slow_admin_statementsをONに設定します。

  • ALTER TABLE
  • ANALYZE TABLE
  • CHECK TABLE
  • CREATE INDEX
  • DROP INDEX
  • OPTIMIZE TABLE
  • REPAIR TABLE

インデックスを使用していないSQL文の記録

log_queries_not_using_indexesをONに設定すると,long_query_timeを超えていないSQL文でも,インデックスを使用せずフルテーブルスキャンをしたものがログに記録されます。フルテーブルスキャンは多くのディスクIOを発生させサーバ全体のSQLのレイテンシを増加させている要因になり得ます。

ただし,レコード件数が小さなテーブルなどの場合は,インデックスを走査してから実際のレコードを取得するよりも,始めからテーブルスキャンをして直接レコードを取得したほうが効率が良いこともあります。そのようなSQL文が多数実行されることが想定される場合は,min_examined_row_limitにてフルテーブルスキャンしたSQL文が最低何行アクセスしたらログに記録するかを設定します。

フルテーブルスキャンするSQL文が多くなることが想定されている場合には,フルテーブルスキャンしたSQL文を1分間にいくつまで記録するかをlog_throttle_queries_not_using_indexesにて設定可能です。なお,min_examined_row_limitとlog_throttle_queries_not_using_indexesはMySQL 5.6から利用可能となった設定です。

スロークエリログの集計

スロークエリログに出力されたSQL文の集計にはmysqldumpslowコマンドが利用できます。

図1 スロークエリログを集計するmysqldumpslowコマンドの例

$ mysqldumpslow svr01-slow.log
Count: 4999  Time=0.00s (4s)  Lock=0.00s (1s)  Rows=127.8 (638717), root[root]@localhost
  SELECT intcol1,charcol1 FROM t1 WHERE intcol1 = N
(省略)

mysqldumpslowの主なオプションは以下のとおりです。

mysqldumpslowの主なオプション

オプション 意味
-a 変数をNとしてまとめず,全て個別に値を表示し集計
-g grepの書式でSQL文を絞り込み
-l 総時間からロック時間を引かない
-s ソート順の指定
t:総実行時間
at:平均実行時間
l:総ロック時間
al:平均ロック時間
r:総送信行数
ar:平均送信行数
c:実行回数

スロークエリログの出力フォーマット

スロークエリログはデフォルトではログファイルに出力されます。log_outputをTABLEに設定すると,mysqlデータベースのslow_logテーブルに出力されます。カンマ区切りで「FILE,TABLE」と設定すると,slow_logテーブルとログファイルの両方に出力されます。なお,log_outputは一般ログ(General Log)とスロークエリログの両方に影響しますので注意してください。

slow_logテーブルはCSVストレージエンジンを利用しているため,CSV形式のデータファイルをコピーして各種のツールで集計も可能です。テーブルに出力している場合のmysqldumpslowに類似した集計は下記のSQL文で可能です。

図2 mysql.slow_logテーブルからmysqldumpslow同等の集計を行うSQL文

mysql> SELECT COUNT(*) AS QCount,
            AVG(query_time) AS Avg_QTime,
            SUM(query_time) AS Total_QTime,
            AVG(lock_time) AS Avg_LTime,
            SUM(lock_time) AS Total_LTime
            AVG(rows_sent) AS Avg_RSent,
            SUM(rows_sent) AS Total_RSent,
            user_host, sql_text
    FROM mysql.slow_log
    GROUP BY user_host, sql_text
    ORDER BY Avg_QTime DESC";

またmysqlクライアントプログラムのオプションと組み合わせると,mysqldumpslowに似た出力を得ることも可能です(下記コマンドは1行です。ユーザおよびパスワードは適宜指定して下さい)⁠

図3 mysqlクライアントプログラムによるmysql.slow_logテーブル集計例

$ mysql -uroot mysql -p -B -e "SELECT CONCAT('Count: ', COUNT(*), '
Time=', AVG(query_time), ' (', SUM(query_time), ')', '  Lock=',
AVG(lock_time), ' (', SUM(lock_time), ')', '  Rows=', AVG(rows_sent), '
(', SUM(rows_sent), ') ', user_host, '  ', sql_text) AS mysqldumpslow
FROM slow_log GROUP BY user_host, sql_text ORDER BY AVG(query_time) DESC"

mysqldumpslow
Count: 4999  Time=0 (4)  Lock=0 (1)  Rows=1.0000 (1) root[root] @ localhost
[]  SELECT intcol1,charcol1 FROM t1 WHERE intcol1 = N
(省略)

著者プロフィール

梶山隆輔

MySQL Sales Consulting Senior Manager。

日本オラクル(株)において,MySQLのお客様環境への導入支援や製品の技術解説を担当するセールスコンサルタントチームのアジア太平洋地域リーダー。多国籍なMySQL部門にて,オーストラリア,インド,台湾などに在籍するチームメンバーを束ね,アジア太平洋地域の25以上の国や地域でのMySQL普及やビジネスの拡大をミッションとする。

コメント

コメントの記入