Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
3年以上前投稿 修正あり

Innotop - MySQLのためのリアルタイムで高機能な調査ツール

MySQL Performance Blogの翻訳。MySQLをリアルタイムなステータスを見るツールにinnotopがある。このシンプルだが非常に役立つツールの基本的な使い方や、複数のサーバの状態を一度に確認する方法などを解説する。
原文 Innotop - a real-time, advanced investigation tool for MySQL (English)
ライセンス CC BY-NC-SA
翻訳者 D98ee74ffe0fafbdc83b23907dda3665 doublemarket


MySQLのGUIモニタリングツールは、我々のあらゆるニーズや状況にいつでもぴったりくるものとは限らない。そういったツールの多くは、MySQLサーバの現在の状態をリアルタイムに表示してくれるというよりは、データベースに過去どのようなことが起きたかの歴史的な見方を提供してくれるよう設計されている。CactiZabbixGangliaNagiosといった素晴らしいフリーのツールもある。しかしこれらは、MySQLインスタンスで何が行われているかの詳細を見るためには正しく設定をする必要がある。そしてそういった設定は、素早くできるとは言い難く、面倒なものである(Gangliaは例外かもしれないが)。

MySQL WorkbenchはリアルタイムなMySQLのステータスを確認する一つの方法だが、非常に限られた機能しかない。また、GUI環境を持つ作業端末にインストールする必要もある。

つまるところ、たくさんあるテキストベースのツールを見つけられていないということだろう。広く使われているものとして、Innotopmytopがある。mytopは残念ながら2007年の更新を最後に捨てられたプロジェクトになっているようだ。また、mytopはInnotopよりも機能面で劣る。

innotop

innotopは、詳細な状態を得るのに難しいクエリを使うことなく、MySQLが何をしているのかの詳細を得るために素早く簡単に使えるツールだ。Percona Supportの顧客にとっても、複雑なSHOW文を実行してもらって、そこからスレーブのレプリケーション遅延や実行中のスレッドの数、InnoDBのヒストリの行数といった単純な事実を抽出するよりも簡単なツールだ。我々サポートエンジニアにとっても、SHOW文やInformation schema、あるいはさらに複雑な方法による詳細の調査の前に、ざっくりとしたシステムの概要をつかむにはinnotopを使う方が早いこともある。

しかし、innotopはこれだけのツールではない!この記事では、あなたが気になるであろうこの小さなツールの大好きな機能をご紹介しよう。全ての機能を紹介するつもりはないので、ツールの基本的な機能を知りたい場合、スクリーンショットを見てみるとよい。

設定

ツールの起動の仕方と5分で使い始めるのをお見せするために、簡単な設定例から始めてみよう。ところが、非常に有用なはずのドキュメントに書かれている設定例は、いくつかの観点で少しわかりにくい。

最新のinnotopのインストールに成功したら、ターミナルでシンプルに「innotop」コマンドを実行してみると、多くのユーザはこれを見るだろう。

[RO] Dashboard (? for help)                   localhost, 0.00, 0 QPS, 0/0/0 con/run/cac thds, !localhost

Uptime  MaxSQL  ReplLag  QPS  Cxns  Run  Miss  Lock  Tbls  Repl  SQL
                                         0.00                      
localhost: Access denied for user 'root'@'localhost' (using password: NO)

innotopがMySQLサーバに接続できるようにするには、少なくとも3つの方法がある。

とにかく動くようにするのに一番簡単なのは、MySQLクライアントに使うホスト、ユーザ名、パスワードを指定して起動する方法だ。

innotop -h 127.0.0.1 -u root -p mysecret

もう一つの方法は、ツールの中でコネクションの設定を入れてinnotopを実行することだ。ヘルプ画面('?'キー)を見ると、「@Select/create server connections」とあるので、'@'を押そう。

You can enter the name of a new connection to create it.

____________________________ Choose from ____________________________
localhost  DBI:mysql:;host=localhost;mysql_read_default_group=client

Choose connections for this mode:

見ての通り、「localhost」への接続設定が自動で作られている。これを選んで、新しいものを作ればよい。ただし、ここでそのまま「localhost」を選択してしまうとまた元の画面に戻ってしまう。どうやってこの既にある「localhost」の設定を変更するのか分からないので、新しい設定を作ろう。

Choose connections for this mode: local
There is no connection called 'local'. Create it?: y

次に行こう。

Typical DSN strings look like
  DBI:mysql:;host=hostname;port=port
The db and port are optional and can usually be omitted.
If you specify 'mysql_read_default_group=mysql' many options can be read
from your mysql options files (~/.my.cnf, /etc/my.cnf).

Enter a DSN string: DBI:mysql:;host=localhost

この「コネクションウィザード」で入力したのは「DBI:mysql:;host=hostname;port=port」だけだ。TCPポートやソケットをカスタマイズしているなら、それをここで指定しよう。

さらに次だ。

Optional: enter a table (must not exist) to use when resetting InnoDB deadlock information: test.deadarch
Do you want to specify a username for local?: y
Do you want to specify a password for local?: y
Enter username for local: root
Enter password for 'root' on local:
Save password in plain text in the config file?: y

この手順の後に、ダッシュボードへ表示が進み、正常に動作するようになる。

[RO] Dashboard (? for help)                    local, 1h59m, 0.40 QPS, 2/1/0 con/run/cac thds, 5.5.33-31.1-log

Uptime  MaxSQL  ReplLag  QPS   Cxns  Run  Miss  Lock  Tbls  Repl  SQL
 1h59m                   0.40     2       0.00     0    26  Off

この方法だと、-hや-u、-pオプションをつけて実行するより複雑で長くて面倒かい?ツールを起動するたびにコネクションの設定が消えてしまうしね!この方法だと何がいいかはこれから書いてみる。

まず、コネクションの設定を書き込もう。そうするには、以下のように実行する。

innotop --write

それから、コネクションの設定をしてツールを終了させる。これでinnotopは設定ファイルを作ってくれる。

[root@centos6-2 ~]# ls .innotop/
innotop.conf plugins

.innotop/innotop.confファイルを編集するわけだが、このファイルは巨大だ!全てのオプションが書かれている。その意味を知りたければドキュメントをチェックしよう。よく使うものだけ説明する。

ここでは、以下のセクションが一番重要だ。

[connections]

local=user=root have_user=1 pass=mypaSS have_pass=1 dsn=DBI:mysql:;host=localhost savepass=1
localhost=user= dsn=DBI:mysql:;host=localhost;mysql_read_default_group=client dl_table=test.innotop_dl

[/connections]

実際のところ、コネクションウィザードは、他のMySQLインスタンスへの接続設定を作る時に例として使う最初のコネクション定義を作る時だけ使うものだ。ドキュメントを読んで初めから設定ファイルを作るのは大変だ。最初から書いてある「localhost」設定に加えて、指定した権限で作られたローカル設定も書かれることになる。元の「localhost」は必要ないので消してしまってよい。

もう一つ重要なのがコネクションに名前をつけてツールの画面で見られるようにするための[active_connections]だ。例えばA=localは、ローカルに対するダッシュボードのデフォルト表示だ。ある設定で接続している時に、他のモードに移ると、自動的に新しい設定が割り当てられる。

複数のホストをモニタリングする

OK、設定のテンプレートができたことだし、ウィザードを使うより素早く追加のコネクション設定を作ってしまおう。テストのために、レプリケーションのサンドボックス環境を作ってみた(mysqlsandboxを使えば1分でセットアップできる)。コネクション設定は以下のようになる。

[connections]

master56=user=msandbox have_user=1 pass=msandbox have_pass=1 dsn=DBI:mysql:;host=127.0.0.1;port=19479; savepass=1
slave156=user=msandbox have_user=1 pass=msandbox have_pass=1 dsn=DBI:mysql:;host=127.0.0.1;port=19480; savepass=1
slave256=user=msandbox have_user=1 pass=msandbox have_pass=1 dsn=DBI:mysql:;host=127.0.0.1;port=19481; savepass=1

[/connections]

ふむ、画面上で「n」キーを使って各サーバへの接続を切り替えることができる。あるいは、「@」を押して接続を選択してもよい。 下の例は、slave256というサーバの「Q」ueryビューだ。

[RO] Query List (? for help)                       slave256, 23h, 10.93k QPS, 8/5/0 con/run/cac thds, 5.6.14-log

When   Load  Cxns  QPS     Slow  Se/In/Up/De%  QCacheHit  KCacheHit  BpsIn    BpsOut
Now    0.00     6  10.93k     0  99/ 0/ 0/ 0       0.00%    100.00%    1.05M  787.03k
Total  0.00   151   8.72      0  92/ 0/ 0/ 0       0.00%    100.00%  977.10     3.14k

Cmd      ID      State               User      Host           DB      Time      Query
Connect       2  Slave has read all  system u                         23:24:35
Execute      23  Sending data        msandbox  localhost      sbtest     00:00  SELECT count(k) FROM sbtest WHERE k BETWEEN ? AND ?
Execute      24  Sending data        msandbox  localhost      sbtest     00:00  SELECT count(k) FROM sbtest WHERE k BETWEEN ? AND ?
Execute      25  preparing           msandbox  localhost      sbtest     00:00  SELECT count(k) FROM sbtest WHERE k BETWEEN ? AND ?
Execute      26  Sending data        msandbox  localhost      sbtest     00:00  SELECT count(k) FROM sbtest WHERE k BETWEEN ? AND ?

サーバグループ

しかしこれでは不十分だ! 100台のサーバがあって、10台のマスタから各スレーブにレプリケーションを張っているグループがあったら、全ての情報を一つのinnotopの画面で分かりやすく見ることができるのだ!これはサーバグループの機能のおかげだ。設定済みのコネクションから、各モードに対してのグループを簡単に設定できて、1つのコネクションもグループも選択できるようになる。グループを切り替えるには、タブを押せばよい。画面上部右側にどのグループあるいはどのサーバの情報を見ているかが表示される。

ではグループを作ってみよう。

[server_groups]

56=master56 slave156 slave256

[/server_groups]

これでグループ内の全てのサーバが同じ画面上で見えるようになった。レプリケーションモードでのグループはきっと気に入ってくれるに違いない。

以下があるスレーブが遅延している時の例だ。slave1が52秒遅延しており、どちらのスレーブもマスタ(ポジション596814971)に対してIOスレッドは待ち状態になっている。ここではExec_Master_Log_Posの情報が見えていないが、スレーブ1のSQLスレッドが追いついていないことを推測するには十分だろう。

[RO] Replication Status (? for help)                                                   Servers: 56
__________________________ Slave SQL Status ___________________________
CXN       Master     On?  TimeLag  Catchup  Temp  Relay Pos  Last Error
slave156  127.0.0.1  Yes    00:52     0.00     0  510811815
slave256  127.0.0.1  Yes    00:00     0.00     0  596815181
________________________________________ Slave I/O Status _________________________________________
CXN       Master     On?  File              Relay Size  Pos        State
slave156  127.0.0.1  Yes  mysql-bin.000003     569.17M  596814971  Waiting for master to send event
slave256  127.0.0.1  Yes  mysql-bin.000003     569.17M  596814971  Waiting for master to send event
__________________ Master Status __________________
CXN       File              Position   Binlog Cache
master56  mysql-bin.000003  596814971        99.82%
slave156  mysql-bin.000002  510811605        99.80%
slave256  mysql-bin.000002  596814971        99.82%

Iキーを押すと、InnoDB IOスレッドモードに入り、slave1について他のスレーブとは違う重要な情報を見ることができる。それによると、他のサーバと比べてたくさんのfsyncを行っており書き込みが多いことがわかる(File I/O Miscの項)。

[RO] InnoDB I/O Info (? for help)                                Servers: 56
______________________________ I/O Threads _______________________________
CXN       Thread  Purpose               Thread Status
master56       0  insert buffer thread  waiting for completed aio requests
master56       1  log thread            waiting for completed aio requests
master56       2  read thread           waiting for completed aio requests
master56       3  read thread           waiting for completed aio requests
master56       4  read thread           waiting for completed aio requests
master56       5  read thread           waiting for completed aio requests
master56       6  write thread          waiting for completed aio requests
master56       7  write thread          waiting for completed aio requests
master56       8  write thread          waiting for completed aio requests
master56       9  write thread          waiting for completed aio requests
slave156       0  insert buffer thread  waiting for completed aio requests
slave156       1  log thread            waiting for completed aio requests
slave156       2  read thread           waiting for completed aio requests
slave156       3  read thread           waiting for completed aio requests
slave156       4  read thread           waiting for completed aio requests
slave156       5  read thread           waiting for completed aio requests
slave156       6  write thread          waiting for completed aio requests
slave156       7  write thread          waiting for completed aio requests
slave156       8  write thread          waiting for completed aio requests
slave156       9  write thread          waiting for completed aio requests
slave256       0  insert buffer thread  waiting for completed aio requests
slave256       1  log thread            waiting for completed aio requests
slave256       2  read thread           waiting for completed aio requests
slave256       3  read thread           waiting for completed aio requests
slave256       4  read thread           waiting for completed aio requests
slave256       5  read thread           waiting for completed aio requests
slave256       6  write thread          waiting for completed aio requests
slave256       7  write thread          waiting for completed aio requests
slave256       8  write thread          waiting for completed aio requests
slave256       9  write thread          waiting for completed aio requests
_________________________________ Pending I/O __________________________________
CXN       Async Rds  Async Wrt  IBuf Async Rds  Sync I/Os  Log Flushes  Log I/Os
master56                                     0          0            0         0
slave156                                     0          0            0         0
slave256                                     0          0            0         0
_____________________________ File I/O Misc ______________________________
CXN       OS Reads  OS Writes  OS fsyncs  Reads/Sec  Writes/Sec  Bytes/Sec
master56      1024     118562       4737       0.00        0.00          0
slave156       565     120586      20333       0.00      144.86          0
slave256       532     119388       4644       0.00        0.00          0
__________________________ Log Statistics __________________________
CXN       Sequence No.  Flushed To  Last Checkpoint  IO Done  IO/Sec
master56  929977732     929977732   929977732          22777    0.00
slave156  929560312     929560312   927035927          17145   95.00
slave256  935238232     935238232   935238232          22767    0.00

これで推測できただろうか。これは、sync_relay_log=1あるいはsync_binlog=1がslave1で設定されていることによって、書き込みが多いとことを表している。

このように、複数のサーバを同じ画面上で見られるのは非常に便利で、たくさんのサーバを素早く比較したり、問題のあるサーバを見つけるのに便利だ。

さらに高度なオプション

innotopの機能のうちあまり知られていないであろうモードの一つが、リアルタイムで変更されていくステータス変数を見られるというものだ。

以下がVariables & Statusモード(S)だ。標準的なモード(vmstat風)あるいはピボット、グラフ(tload)の種類があるが、これはpivotedだ。

[RO] Variables & Status (? for help)    master56, 2h33m, InnoDB 1s :-), 5.09k QPS, 5/4/0 con/run/cac thds, 5.6.14-log

name               set_0    set_1    set_2    set_3    set_4    set_5    set_6    set_7    set_8    set_9
QPS                5211.69  4478.65  5457.82  5057.26  5704.39  5522.03  5737.59  5800.76  5188.59  58.52
Commit_PS          260.91   223.28   272.94   252.86   285.74   275.35   286.97   290.46   258.44   2.92
Rollback_Commit    0        0        0        0        0        0        0        0        0        0
Write_Commit       17.97    18.05    17.99    17.99    17.96    18.05    17.98    17.96    18.07    18
R_W_Ratio          0.78     0.78     0.78     0.78     0.78     0.78     0.78     0.78     0.78     0.78
Opens_PS           0        0        0        0        0        0        0        0        0        0.01
Table_Cache_Used   0        0        0        0        0        0        0        0        0        0
Threads_PS         0        0        0        0        0        0        0        0        0        0
Thread_Cache_Used  0        0        0        0        0        0        0        0        0        0
CXN_Used_Ever      3.31     3.31     3.31     3.31     3.31     3.31     3.31     3.31     3.31     3.31
CXN_Used_Now       3.31     3.31     3.31     3.31     3.31     3.31     3.31     3.31     3.31     3.31

このモードの本当にすごいのは、表示する変数を選べるところだ。このモードでcを押すと、以下のようなリストが表示される(幅広なので横は切り捨て)。

________________________________________________________ Choose from _________________________________________________________
          commands   Uptime, Questions, Com_delete, Com_delete_multi, Com_insert, Com_insert_select, Com_replace, Com_replace_
cxns_files_threads   Uptime,Aborted_clients,Aborted_connects,Bytes_received,Bytes_sent,Compression,Connections,Created_tmp_dis
           general   set_precision(Questions/Uptime_hires) as QPS, set_precision(Com_commit/Uptime_hires) as Commit_PS, set_pr
           handler   Uptime,Handler_read_key,Handler_read_first,Handler_read_next,Handler_read_prev,Handler_read_rnd,Handler_r
            innodb   Uptime,Innodb_row_lock_current_waits,Innodb_row_lock_time,Innodb_row_lock_time_avg,Innodb_row_lock_time_m
     innodb_health   dulint_to_int(IB_tx_trx_id_counter) - dulint_to_int(IB_tx_purge_done_for) as OldVersions,IB_sm_mutex_spin
    innodb_health2   percent(1-((Innodb_buffer_pool_pages_free||0)/($cur->{Innodb_buffer_pool_pages_total}||1))) as BP_page_ca
         key_cache   Uptime,Key_blocks_not_flushed,Key_blocks_unused,Key_blocks_used,Key_read_requests,Key_reads,Key_write_req
         prep_stmt   Uptime,Com_dealloc_sql,Com_execute_sql,Com_prepare_sql,Com_reset,Com_stmt_close,Com_stmt_execute,Com_stmt
       query_cache   percent((Qcache_hits||0)/(((Com_select||0)+(Qcache_hits||0))||1)) as Hit_Pct,set_precision((Qcache_hits||
      query_status   Uptime,Select_full_join,Select_full_range_join,Select_range,Select_range_check,Select_scan,Slow_queries,S
      slow_queries   set_precision(Slow_queries/Uptime_hires) as Slow_PS, set_precision(Select_full_join/Uptime_hires) as Full
               txn   Uptime,Com_begin,Com_commit,Com_rollback,Com_savepoint,Com_xa_commit,Com_xa_end,Com_xa_prepare,Com_xa_rec
Choose a set of values to display, or enter the name of a new one:

何がすごいって、変数のセットを作れて、それらの中から計算をしたりする関数を作れてしまう!

受け身の監視ツールってだけじゃない!

さらにここで言っておくべきこととして、innotopは監視のためだけのものではないということだ! ここでサーバの管理もしてしまえる(ある程度制限されてはいるが)。

例えば、レプリケーションモードの例を見てみよう。Oを押してみよう。

[RO] Replication Status (? for help)                                                   Servers: 56
__________________________ Slave SQL Status ___________________________
CXN       Master     On?  TimeLag  Catchup  Temp  Relay Pos  Last Error
slave156  127.0.0.1  Yes    00:00     0.00     0   57271325
slave256  127.0.0.1  Yes    00:00     0.00     0   57271325
________________________________________ Slave I/O Status ________________________________________
CXN       Master     On?  File              Relay Size  Pos       State
slave156  127.0.0.1  Yes  mysql-bin.000005      54.62M  57271115  Waiting for master to send event
slave256  127.0.0.1  Yes  mysql-bin.000005      54.62M  57271115  Waiting for master to send event
_________________ Master Status __________________
CXN       File              Position  Binlog Cache
master56  mysql-bin.000005  57271115         0.00%
slave156  mysql-bin.000004  57271115         0.00%
slave256  mysql-bin.000005  57271155         0.00%
_________________________________ Choose from _________________________________
master56   DBI:mysql:;host=127.0.0.1;port=19479;
slave156   DBI:mysql:;host=127.0.0.1;port=19480;
slave256   DBI:mysql:;host=127.0.0.1;port=19481;mysql_read_default_group=client
Which servers?: slave156
Command to send: stop slave SQL_thread;
Success on slave156
Press any key to continue

この機能は、innotopがMySQLに接続するユーザが実行できるあらゆるコマンドを実行することができる窓になっている。

(...)
Which servers?: slave156
Command to send: DROP TABLE test.t1;
Success on slave156

Press any key to continue

従って、innotopクライアントに必要以上に権限の強いユーザを使うのは注意すべきだろう。上の例だと、スーパーユーザで接続しているので、read_only=1が設定されていたにもかかわらずテーブルの削除ができてしまった。

innotopとMySQL 5.6

mysqlsandboxのおかげで、超高速かつ簡単に色々なバージョンのMySQLや各種設定をテストすることができる。そこで、サンドボックス環境でGTIDを有効にして(enable_gtidスクリプトを実行するだけ)レプリケーションをした後、innotopのレプリケーションモードにしてみたが、残念ながら追加の情報は表示されなかった。これに関しては既に機能リクエストが出ており、もう少しの愛情が必要なようだ。

innotopのバージョンが1.9.1よりも古い場合、MySQL 5.6の環境でInnoDBステータスに関するモードを使うとクラッシュするので、1.9.1以上にアップしよう。

innotopとGalera replication

innotop上でwsrepのステータスが見られるのは非常に便利だろう。現時点では、Percona XtraDB Cluster (PXC)のノードの監視は可能で、クラスタをグループとして設定することができるが、クラスタ部分のレプリケーション、ノードのステータスだけが見られない。私はこの点に関して、誰かPerlマスターが気にしてくれるんじゃないかという希望の元に、機能リクエストを出している。

とはいえ、innotopでGaleraのステータスが全く監視できないという意味ではない!変数をカスタムして見るモード(S)を思い出しただろうか?

簡単な方法はこうだ。Sを押してそのモードに入って、見たいPXCのノードを選び、cを押す。

Choose a set of values to display, or enter the name of a new one: wsrep1

Enter variables for wsrep1: wsrep_cluster_conf_id,wsrep_cluster_size,wsrep_local_recv_queue

じゃーん!

[RO] Variables & Status (? for help)      pxc1, 3h, InnoDB 1s :-), 1.90 QPS, 202/1/0 con/run/cac thds, 5.5.29-log

name                    set_0  set_1  set_2  set_3  set_4  set_5  set_6  set_7  set_8  set_9
wsrep_cluster_conf_id   3      3      3      3      3      3      3      3      3      3
wsrep_cluster_size      3      3      3      3      3      3      3      3      3      3
wsrep_local_recv_queue  0      0      0      0      0      0      0      0      0      0

innotop.confでリードオンリーのオプションが無効になっていた場合、innotopを抜けた後、新しい変数は以下のように保存されているだろう。

[varsets]
wsrep=wsrep_cluster_conf_id,wsrep_cluster_size,wsrep_local_recv_queue
[/varsets]

残念なことに、ここで許可されているのは数値だけで、また、Sモードはサーバグループではまだちゃんと動かない。

innotopには、あなたの気づいていないであろう、そしてとても使いやすい隠された昨日がたくさんある。私は皆さんに書くモードでヘルプ(?を押す)を詳しく見ることを強くお勧めしたい。

バグ発見、助けて!

残念なことに、私の大好きな機能である1画面でサーバグループを監視する機能には、2つの重大な問題がある。

アクティブなバグや機能リクエストの一覧はこちら : https://code.google.com/p/innotop/issues/list

リンク

次の記事
FacebookはGoogleのようにMySQLを捨ててMariaDBへ移行しようとしているのか?
前の記事
SQLに対するMySQLと、NoSQLに対するMongoDBは似ている――主に有害な意味で

Feed small 記事フィード