第86回　MySQLのリアルタイム監視ツール

みなさんは、MySQLをリアルタイムで監視する時はどのように行いますか？自作のスクリプトを利用したり有償ツールを用いて監視することもあるかと思います。2018年の11月にMySQLのリアルタイム監視ツールの1つであるinnotopが、MySQLのバージョン8に対応しました。そこで今回は、MySQLのリアルタイム監視ツールの紹介としてinnotopとps-topについて紹介したいと思います。なお、今回使用するOSはCentOS 7となります。

innotopのインストールと起動

CentOSでinnotopを利用するにはyumのepel-releaseを利用するか、ソースコードを取得する必要があります。yumで取得する場合はepel-releaseパッケージをインストール後innotopをインストールします。ソースコードを利用する場合はgithubのinnotopから取得できます。中にあるinnotopをそのまま実行することもできますし、Makeファイルを利用して/usr/loca/bin配下に置くことができます。

# git clone https://github.com/innotop/innotop.git

#  perl Makefile.PL
Writing Makefile for innotop
Writing MYMETA.yml and MYMETA.json

# make
cp innotop blib/script/innotop
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/innotop
Manifying blib/man1/innotop.1

# make install
Appending installation info to /usr/lib64/perl5/perllocal.pod

# ls /usr/local/bin/innotop
/usr/local/bin/innotop

/usr/local/bin/innotopにinnotopがインストールされていることが確認できました。

innotopの起動はソケットファイルを利用するときはinnotop -S <ソケットファイル名> -u <ユーザー名> --askpassを実行します。リモートホストを利用する場合はinnotop -h <host名> -P <ポート> -u <ユーザー名> --askpassを実行します。実行すると、下記のようにUptimeやQPS,テーブルの数を確認することができます。

Uptime  MaxSQL  ReplLag  QPS     Cxns  Run  Miss  Lock  Tbls  Repl  SQL
   43d                   136.00     2       0.00     0   243

デフォルトでは10秒で更新されますが、-d オプションを利用することで、更新間隔を変更することができます。

各モードによる監視

innotopを起動後、?を押すと、各モードの説明と変更するキーを確認することができます。ここで筆者がよく使うモードをいくつか紹介します。

Query Listモード

Qでquery Listモードを実行することができます。Query Listモードでは現在実行中のクエリを確認することができます。実行中に流れているクエリが遅い場合などは対象のクエリのTimeカラムが増えていきます。ALTER文を実行するときや、バッチ処理などで大量の更新が実行される場合はこちらのモードでクエリが流れているか確認することができます。

When   Load  Cxns  QPS     Slow  Se/In/Up/De%  QCacheHit  KCacheHit  BpsIn   BpsOut
Now    0.66     4  130.09     0  28/28/ 0/ 0       0.00%    100.00%  15.28k  87.20k
Total  0.00   151    0.02     0  31/31/ 0/ 0       0.00%    100.00%   3.12    9.69

Cmd     ID      State               User      Host       DB  Time   Query
Daemon       4  Waiting on empty q  event_sc  localhost        43+10:32:51
Query    28394  query end           root      localhost  d1  00:00  INSERT INTO t1(id, name, num1, num2, start_at, end_at) VALUES (28326, 'Dg8fKrvm34', 20062, 1,'2005-08-17',

Replication Statusモード

Replication Statusモードではレプリケーションの状況を確認することができます。Slave SQL StatusにはSHOW SLAVA STATUSの結果、Master StatusにはSHOW MASTER STATUSの結果が表示されます。

下記は第83回マルチソースレプリケーションのスレーブ環境をReplication Statusモードで確認した結果です。マルチソースレプリケーションでも表示することが確認できます。もしリアルタイムにスレーブ遅延が発生している時は、Slave SQL StatusのTimeLagが増えていきます。

________________________________________________________________________________ Slave SQL Status ________________________________________________________________________________
Channel      Master     Master UUID                           On?  TimeLag  Catchup  Temp  Relay Pos  Last Error  Retrieved GTID Set  Executed GTID Set
mydatabase1  127.0.0.1  7d2772a5-8987-11e8-a6a6-0201858272ab  Yes    00:00     0.00         42705960              N/A                 7d2772a5-8987-11e8-a6a6-0201858272ab:1-93916
mydatabase2  127.0.0.1  e18613f0-d5b4-11e8-9d11-0201858272ab  Yes    00:00     0.00              322              N/A                 7d2772a5-8987-11e8-a6a6-0201858272ab:1-93916
mydatabase3  127.0.0.1  3ed0c066-d5b5-11e8-9e88-0201858272ab  Yes    00:00     0.00              322              N/A                 7d2772a5-8987-11e8-a6a6-0201858272ab:1-93916

____________________________________________________________ Slave I/O Status _____________________________________________________________
Channel      Master     Master UUID                           On?  File              Relay Size  Pos       State
mydatabase1  127.0.0.1  7d2772a5-8987-11e8-a6a6-0201858272ab  Yes  mysql-bin.000023      40.73M  42706006  Waiting for master to send event
mydatabase2  127.0.0.1  e18613f0-d5b4-11e8-9d11-0201858272ab  Yes  mysql-bin.000022         538       368  Waiting for master to send event
mydatabase3  127.0.0.1  3ed0c066-d5b5-11e8-9e88-0201858272ab  Yes  mysql-bin.000022         538       195  Waiting for master to send event

___________________________________________________________ Master Status ___________________________________________________________
Channel  File              Position  Binlog Cache  Executed GTID Set                             Server UUID
         mysql-bin.000025  43426483         0.00%  7d2772a5-8987-11e8-a6a6-0201858272ab:1-93916  8234c235-d5ee-11e8-8f97-0201858272ab

Locksモード

現在のロックの状態を確認することができます。もしロック状態が存在する場合は、下記のような情報が出力されます。この値はSHOW ENGINE INNODB STATUSから取得しています。

_______________________________________ InnoDB Locks _______________________________________
ID      Type    Waiting  Wait   Active  Mode  DB  Table  Index    Ins Intent  Special
120760  RECORD        1  00:11   00:11  X     d1  t1     PRIMARY           1  gap before rec

この他にも、buffer_pool情報を表示するInnoDB Buffersモードやトランザクションの状態を表示するInnoDB Txnsモードがあります。詳細な使い方についてはman innotopを実行してマニュアルをご確認ください。

ps-top

ps-topはgo言語製のリアルタイム監視ツールです。こちらはMySQLのバージョンは5.6と5.7に対応しています。ps-topはperformance_schemaからデータを収集し、この情報をリアルタイムに表示することができます。

ps-topのインストール

すでにgo言語が使える環境であればgo get -u github.com/sjmudd/ps-top/cmd/ps-topを実行すことですぐに利用することができます。さっそくmysqlslapを実行しているローカルのMySQLにps-top --socket=/tmp/mysql.sock --user=root -password='****'を実行して接続してみます。

ps-top 0.7.9 -  3:15:58 MY-HOST / 5.7.22-log, up 16m 37s          [REL] 44 seconds
Table  (table_io_waits_summary_by_table) 1 rows
      Ops      %| Fetch Insert Update Delete|Table Name
       83 100.0%|       100.0%              |mysqlslap.t1
                |                           |
                |                           |

table_io_latencyが表示されました。ps-topには以下の7つのモードが搭載されています。

mode	説明
table_io_latency	テーブルごとのレイテンシーを表示
table_io_ops	テーブルごとの操作数を表示
file_io_latency	各ファイルのIOの時間を表示
table_lock_latency	テーブルごとのLock waitイベントを表示
user_latency	ユーザーごとのクエリの情報を表示
mutex_latency	mutex待ち時間を表示
stages_latency	イベントステージごとの情報を表示

これらの情報はtabキーを押すことによって各モードに切り替えることができます。また、zキーを押すことで統計情報をリセット、tキーでps-topを起動した時間からの集計かリセット以降の集計値かを切り替えることができます。

まとめ

今回はMySQLリアルタイムの状況を監視するツールとしてinnotopとps-topを紹介しました。これらのようなリアルタイムの状況を表示するツールは過去の情報を見ることはできませんが、常に今の状況がわかるので、ALTER構文の実行や大量な更新がある場合などは監視ツールを用いるなどして不測の事態に備える必要があります。