performance_schema を top っぽく見るツール ps-top

とある企業のDBA @yoku0825 さんに ps-top というものを教えてもらったので試してみました。 ps は 「Performance Schema」のps です。MySQL の パフォーマンス統計である performance_schema の情報をtopコマンドのように表示してくれる。

インストール

ps-top は go で書かれている。 自分の管理外のサーバだと、ソフトウェアをインストールするのに躊躇するので、ポータブルな（バイナリをコピーすれば動く）goなのはありがたいですね。

$ export GOPATH=$HOME/go
$ mkdir $GOPATH
$ go get -u github.com/sjmudd/ps-top/cmd/ps-top

使い方

$ $HOME/go/bin/ps-top --help
pstop - Copyright (C) 2014-2015 Simon J Mudd 
Top-like program to show MySQL activity by using information collected
from performance_schema.
Usage: pstop 
Options:
--anonymise=                 Anonymise hostname, user, db and table names
--count=                          Set the number of times to watch
--defaults-file=/path/to/defaults.file   Connect to MySQL using given defaults-file
--help                                   Show this help message
--host=                        MySQL host to connect to
--interval=                     Set the default poll interval (in seconds)
--limit=                           Limit the number of lines of output (excluding headers)
--password=                    Password to use when connecting
--port=                            MySQL port to connect to
--socket=                          MySQL path of the socket to connect to
--user=                            User to connect with
--use-environment                        Connect to MySQL using a go dsn collected from MYSQL_DSN e.g. MYSQL_DSN='test_user:test_pass@tcp(127.0.0.1:3306)/performance_schema'
--version                                Show the version
--view=                            Determine the view you want to see when pstop starts (default: table_io_latency
                                         Possible values: table_io_latency table_io_ops file_io_latency table_lock_latency user_latency mutex_latency stages_latency

--userが無視されるのに惑わされました。。。 --host もしくは --socket を指定しないと、--user や --port が無視される仕様のようです。

// --user は無視される・・・
$ $HOME/go/bin/ps-top --user=perf
2016/12/20 15:37:59 Error 1045: Access denied for user 'mita2'@'localhost' (using password: NO)

// --host を指定すると--userが有効になる。
$ $HOME/go/bin/ps-top --user=perf --host localhost.localdomain
2016/12/20 15:37:56 Error 1045: Access denied for user 'perf'@'localhost.localdomain' (using password: NO)

MySQL5.7 以降だとカジュアルにSIGSEGVで落ちます。。。原因はおいておいて、とりあえず、show_compatibility_56 をONにすれば動く。

$ $HOME/go/bin/ps-top --socket /var/lib/mysql/mysql.sock --user root --password password
panic: runtime error: invalid memory address or nil pointer dereference
        panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x20 pc=0x4c86c6]
goroutine 1 [running]:

mysql> SET GLOBAL show_compatibility_56=on;
Query OK, 0 rows affected (0.00 sec)

操作方法

タブで項目切り替え。

Table (table_io_waits_summary_by_table)

IO待ちが大きいテーブル一覧

ps-top 0.7.5 - 16:35:40 localhost / 5.7.15-9, up 16h 52m 14s      [REL] 3 seconds
Table  (table_io_waits_summary_by_table) 1 rows
   Latency      %| Fetch Insert Update Delete|Table Name
    1.16 s 100.0%| 72.9%   5.0%  19.8%   2.3%|sbtest.sbtest
                 |                           |
                 |                           |
                 |                           |
                 |                           |

File I/O Latency (file_summary_by_instance)

IO待ちの大きいテーブル

ps-top 0.7.5 - 16:36:29 localhost / 5.7.15-9, up 16h 53m 3s       [REL] 52 seconds
File I/O Latency (file_summary_by_instance)    6 row(s)
   Latency      %|  Read  Write   Misc|Rd bytes Wr bytes|     Ops  R Ops  W Ops  M Ops|Table Name
   34.28 s  73.8%|         0.9%  99.1%|          35.06 M| 17.39 k         50.0%  50.0%|
    9.47 s  20.4%|       100.0%       |          25.56 M|294.00 k        100.0%       |/localhost.log
    1.46 s   3.1%|  3.1%  28.6%  68.3%| 21.16 M 148.86 M| 10.74 k  12.3%  86.6%   1.1%|sbtest.sbtest
 868.59 ms   1.9%|        72.0%  28.0%|         169.17 M|     244         50.0%  50.0%|/xb_doublewrite
 392.50 ms   0.8%|        18.9%  81.1%|          20.31 M|  1.32 k         96.5%   3.5%|
 113.28 us       |       100.0%       |          48.00 k|       3        100.0%       |

Locks by Table Name (table_lock_waits_summary_by_table)

lock の大きいテーブル

ps-top 0.7.5 - 16:37:12 localhost / 5.7.15-9, up 16h 53m 46s      [REL] 95 seconds
Locks by Table Name (table_lock_waits_summary_by_table)
   Latency      %|  Read  Write|S.Lock   High  NoIns Normal Extrnl|AlloWr CncIns    Low Normal Extrnl|Table Name
 719.38 ms 100.0%| 69.6%  30.4%|                             69.6%|                             30.4%|sbtest.sbtest
Activity by Username (processlist)
ユーザごとのアクティビティ（SELECT数など）
ps-top 0.7.5 - 16:38:29 localhost / 5.7.15-9, up 16h 55m 3s
Activity by Username (processlist) 2 rows
Run Time      %|Sleeping      %|Conn Actv|Hosts DBs|Sel Ins Upd Del Oth|User
               |00:00:03 100.0%|   1     |    1    |                   |perf
               |               |   6    6|    1   2|  4                |root

Mutex Latency (events_waits_summary_global_by_event_name)

mutex 一覧

ps-top 0.7.5 - 16:39:02 localhost / 5.7.15-9, up 16h 55m 36s      [REL] 205 seconds
Mutex Latency (events_waits_summary_global_by_event_name) 27 rows
   Latency   MtxCnt        %|Mutex Name
 874.87 ms 121.15 k    28.0%|log_sys_write_mutex
 630.11 ms   8.84 M    20.2%|trx_mutex
 462.33 ms 958.76 k    14.8%|lock_mutex
 444.83 ms   1.03 M    14.2%|log_sys_mutex
 335.51 ms 942.34 k    10.7%|fil_system_mutex
 258.67 ms 562.83 k     8.3%|trx_sys_mutex
  33.28 ms 281.07 k     1.1%|trx_undo_mutex
  26.75 ms 224.85 k     0.9%|redo_rseg_mutex
  16.67 ms  56.21 k     0.5%|purge_sys_pq_mutex
  15.09 ms  86.95 k     0.5%|buf_pool_LRU_list_mutex
   5.65 ms  60.98 k     0.2%|flush_list_mutex
   5.43 ms  52.51 k     0.2%|srv_sys_mutex

SQL Stage Letency

ps-top 0.7.5 - 16:23:43 localhost / 5.7.15-9, up 16h 40m 17s      [REL] 28 seconds
SQL Stage Latency (events_stages_summary_global_by_event_name) 16 rows
   Latency      %  Counter|Stage Name
  93.97 ms  59.6%       73|Sending data
  22.59 ms  14.3%      157|starting
  17.45 ms  11.1%       73|executing
   7.55 ms   4.8%      115|Opening tables
   7.19 ms   4.6%       31|freeing items
   2.26 ms   1.4%       73|init
   1.60 ms   1.0%       73|statistics
   1.26 ms   0.8%       73|preparing
 877.32 us   0.6%      115|checking permissions
 747.29 us   0.5%       73|optimizing
 622.61 us   0.4%       73|System lock
 546.47 us   0.3%       85|removing tmp table
 404.06 us   0.3%       73|query end
 269.76 us   0.2%       73|closing tables
 231.15 us   0.1%      157|cleaning up
 136.90 us   0.1%       73|end

Memory Usage

ps-top 0.7.5 - 16:24:42 localhost / 5.7.15-9, up 16h 41m 16s      [REL] 9223372037 seconds
Memory Usage (memory_summary_global_by_event_name)
CurBytes         %  High Bytes|MemOps          %|CurAlloc       %  HiAlloc|Memory Area
   13.66 M   10.0%     13.66 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_history_long
    9.77 M    7.1%      9.77 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_history_long.sqltext
    9.77 M    7.1%      9.77 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_history_long.tokens
    9.77 M    7.1%      9.77 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_digest.tokens
    9.03 M    6.6%      9.03 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_thread_by_event_name
    7.03 M    5.1%      7.03 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/memory_summary_by_thread_by_event_name
    4.88 M    3.6%      4.88 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_digest
    4.51 M    3.3%      4.51 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_account_by_event_name
    4.51 M    3.3%      4.51 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_host_by_event_name
    4.51 M    3.3%      4.51 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/events_statements_summary_by_user_by_event_name
    4.00 M    2.9%      4.00 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/table_shares
    3.52 M    2.6%      3.52 M|         1   1.3%|       1    1.3%         1|memory/performance_schema/memory_summary_by_account_by_event_name