MySQL: innotopとサンプリング

MySQL
以下、DBマガジン2010年7月号の草稿から

innotop

innotopを使えば、現在どんなSQLが実行されているかをリアルタイムで知ることができる。 ここでは簡単な使い方と応用編としてSQLのサンプリング、および解析スクリプトを紹介する。

ダウンロードは以下のURLから行う。 innotopはperlスクリプトなので、MySQLに接続するためのモジュール(DBIやDBD)を予めインストールしておくこと。

http://sourceforge.net/projects/innotop/
ダウンロードしたアーカイブを展開したら、以下の手順を実行する。デフォルトでは/usr/bin以下にインストールされる。 

$ tar xvfz innotop-1.6.0.tar.gz 
$ cd innotop-1.6.0
$ perl Makefile.PL 
$ make 
$ make install

初回実行時のみ初期設定を行う。
まず接続名を入力する。適当な名前でよい。ここでは"dbt2"とする。
次にDSNを入力する。データベース名やホスト名など適宜入力すること。 

DBI:mysql:database=dbt2:host=localhost:port=3306

あとはユーザ名やパスワードを入力すると、ホームディレクトリ以下に~/.innotop/というサブディレクトリが作られ、 イニシャルファイルinnotop.iniが作成される。


バージョン4.1以下の場合はinnotop.iniを編集する。 

global=0

これは、バージョン4.1以下のSHOW STATUS文やSHOW PROCESSLIST文ではGLOBALオプションが無効だからである。
innotopコマンドを起動すると、実行中のSQLのリストが周期的に表示される。 起動時に"-d"オプションでサンプリング周期を指定できる。 

$ innotop -d 1


SQLのサンプリング

innotopとMySQLの標準コマンドmysqldumpslowを組み合わせると、簡単にSQLのサンプリング検査ができる。

まず、innotopを"-n"オプション(Non-interactiveモード)で起動し、 SQLのサンプリング結果をファイル(ここでは/tmp/innotop.log)に書き込む。

次の例では、0.5秒間隔で3600回、およそ30分にわたってサンプリングしている。 DB本体の負荷にならない程度のサンプリング間隔で、なるべく長期間データをとるとよいだろう。 Ctl-Cを打ち込めば途中で終了する。 

$ innotop -d 0.5 --count 3600 -n | tee /tmp/innotop.log

得たサンプリング結果をmysqldumpslowコマンドが読み込めるように、 次のperlスクリプトを準備する。 

$ cat /usr/local/bin/innotop2slowlog.pl
#!/usr/bin/perl -w
use strict;
while (<>) {
    chop;
    my @row = split(" ", $_);
    printf ("# Time:\n# User\@Host: %s(%s) @ %s\n", $row[2], $row[2], $row[3]);
    my $i = 5;
    while ($row[$i] !~ /[0-9]{2}\:[0-9]{2}$/ && $i < $#row) { $i++;}
    printf ("# Query_time: %s Lock_time = x.x Rows_sent:x Rows_examined: x\n", $row[$i]);
    print join(" ", @row[($i+2)..$#row]) . "\;\n";
}


あとは、innotop2slowlog.plでスロークエリログ形式に変換したファイル(ここでは/tmp/innotop2slowlog.log)を mysqldumpslowに処理させるだけである。 

$ /usr/local/bin/innotop2slowlog.pl /tmp/innotop.log > /tmp/innotop2slowlog.log
$ mysqldumpslow -s c -r /tmp/innotop2slowlog.log