免責事項
この記事はMorgan Tocker氏によるMySQL Server Blogの投稿「Using SYS.SESSION as an alternative to SHOW PROCESSLIST」(2016/1/26)をユーザが翻訳したものであり、Oracle公式の文書ではありません。
最近のMySQLサーバーのinformation_schemaやperformance_schemaには役に立つメタデータがたくさんあり、これによってデータベースサーバーの内部で何が起きているかがわかりやすくなります。しかし、このデータはときに細か過ぎて要点をつかむためにコツがいる場合があります :)
MySQL 5.7では、デフォルトで新しくできたSYSスキーマがインストールされています。SYSはデータベース管理者が共通のタスクを行うにあたり役に立つようにデータを集計するビュー、関数およびプロシージャのセットです。
Linuxのユーザーの方々にとっては、performance_schemaを/procと、SYSをvmstatと例えると良いと思います。
SHOW PROCESSLIST
SYSスキーマをDBAのタスク由来のビューとして導入するにあたっては、ログインして何が起こっているか確認するのが最もよくあるケースでしょう。SYSスキーマでは次のようにします。
mysql> SELECT * from sys.session\G
*************************** 1. row ***************************
thd_id: 29
conn_id: 4
user: root@localhost
db: test
command: Query
state: alter table (read PK and internal sort)
time: 6
current_statement: ALTER TABLE b add index(b)
statement_latency: 5.78 s
progress: 19.19
lock_latency: 203.70 ms
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 4.85 MiB
last_wait: wait/io/file/innodb/innodb_data_file
last_wait_latency: Still Waiting
source: fil0fil.cc:5623
trx_latency: 5.68 s
trx_state: ACTIVE
trx_autocommit: YES
pid: 23988
program_name: mysql
*************************** 2. row ***************************
thd_id: 28
conn_id: 3
user: root@localhost
db: test
command: Query
state: Sending data
time: 0
current_statement: insert into a select null, repeat('b', 255) from a
statement_latency: 303.54 ms
progress: NULL
lock_latency: 291.00 us
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 1
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 1.43 MiB
last_wait: wait/io/table/sql/handler
last_wait_latency: Still Waiting
source: handler.cc:3056
trx_latency: 410.06 ms
trx_state: ACTIVE
trx_autocommit: YES
pid: 23969
program_name: mysql
*************************** 3. row ***************************
thd_id: 27
conn_id: 2
user: root@localhost
db: mysql
command: Query
state: Sending data
time: 0
current_statement: SELECT * from sys.session
statement_latency: 23.01 ms
progress: NULL
lock_latency: 10.02 ms
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 4
tmp_disk_tables: 1
full_scan: YES
last_statement: NULL
last_statement_latency: NULL
current_memory: 3.25 MiB
last_wait: wait/synch/mutex/innodb/file_format_max_mutex
last_wait_latency: 31.69 ns
source: trx0sys.cc:781
trx_latency: 4.94 m
trx_state: ACTIVE
trx_autocommit: NO
pid: 23950
program_name: mysql
3 rows in set (0.20 sec)
ここで、SHOW PROCESSLISTには表示されない項目をいくつかご紹介しましょう。
- 1行目では、テーブルにインデックスを付与するために
ALTER TABLEコマンドを実行していることがわかります。SYSスキーマにはその進捗が表示されます(現在は「主キーのリードおよび内部のソート(read PK and internal sort)」であり、19%がその段階で終わっていることがわかります。現在の実行時間(5.78秒)に関する詳細な値の情報も含まれます。(訳注: state, progress, statement_latency)(※最下部訳注合わせて参照) - 2行目では、
INSERT .. SELECTコマンドを実行していることがわかります。これはオートコミットのトランザクションで410.06ミリ秒実行されています。このSQL文を実行するために一時テーブルが必要となっていることもわかります。(訳注: trx_autocommit, trx_latency, trx_state, tmp_tables) - 3行目では、4.94分継続しているトランザクションがあることがわかります。これは現在のコネクションで
SELECT * from sys.sessionを実行しているものです。長いトランザクションは、メンテナンス用の操作の実行を妨げるなどのパフォーマンス上の問題を引き起こすことがときたまあります。(訳注: trx_latency) - これらの3つのコネクションはMySQLのcliによって生成されているので(プログラム名: mysql、pid: osのpid)、これはシミュレーションではあるのですが、みなさんのcliでもこれらのコネクション属性を生成することが可能で、問題の原因を究明するのに役に立つことでしょう。
まとめ
願わくばこの実例で、SYSスキーマは簡単に利用できて、performance_schema内に隠れている多くの有用なメタデータを含んでいることを分かっていただきたいと思います。この記事を読んで、他の例を探したくなったら、お手元のMySQL 5.7上のSYSスキーマでSHOW TABLESを実行してみることをおすすめします。有用な情報がたくさんあります :)
訳注 : progressについて
SYS.SESSIONでprogressを参照する場合は、performance_schemaのevents_stages_currentコンシューマの設定を、事前に有効化する必要があります。※ yoku0825さんありがとうございます。
※インストゥルメントはMySQL 5.7.7からデフォルトで有効化されるように変更されています。
http://dev.mysql.com/doc/refman/5.7/en/performance-schema-stage-tables.html
下記コマンドで有効化可能です。
mysql> UPDATE performance_schema.setup_consumers SET enabled= 'YES' WHERE name= 'events_stages_current';
