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

SYS.SESSIONをSHOW PROCESSLISTの代わりに使う(MySQL Server Blogより)

MySQL 5.7から利用可能となったSYSスキーマ。おなじみのSHOW PROCESSLISTよりも多くの属性が取得可能で、DBAの方々にとって、問題発生時の切分けに大活躍することでしょう。クエリの進捗、一時テーブルの発生、コネクション属性などが取得できます。

原文
Using SYS.SESSION as an alternative to SHOW PROCESSLIST | MySQL Server Blog (English)
翻訳依頼者
B5aa4f809000b9147289650532e83932
翻訳者
B5aa4f809000b9147289650532e83932 taka-h
翻訳レビュアー
D98ee74ffe0fafbdc83b23907dda3665 doublemarket 0deae06ab5d86b39feeec2e23a30b88a yoku0825
原著者への翻訳報告
未報告


免責事項

この記事は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';

次の記事
TICKスタック最新情報: InfluxDB RC, KapacitorおよびChronograf v0.10(InfluxData Blogより)
前の記事
MySQL 5.7以後のレプリケーションのデフォルト値(MySQL High Availabilityより)

Feed small 記事フィード

新着記事Twitterアカウント