sys schema 进度报告

• 从MySQL 5.7.9开始，sys schema视图提供查看长时间运行的事务的进度报告，通过processlist和session以及x$前缀的视图进行查看，其中processlist包含了后台线程和前台线程当前的事件信息，session不包含后台线程和command为Daemon的线程，如下：

# session视图是直接调用processlist视图过滤了后台线程和command为Daemon的线程（所以两个视图输出结果的字段完全相同），而processlist线程联结查询了threads、events_waits_current、
# events_stages_current、events_statements_current、events_transactions_current、sys.x$memory_by_thread_by_current_bytes、session_connect_attrs表，so，需要打开相应的instruments和consumers，
# 否则谁没打开谁对应的信息字段列就为NULL，对于trx_state字段为ACTIVE的线程，progress可以输出百分比进度信息(支持进度的事件才会被统计进来)
processlist
session
x$processlist
x$session

# 示例
## 查看当前正在执行的语句进度信息
admin@localhost : sys 06:57:21> select * from session where conn_id!=connection_id() and trx_state='ACTIVE'\G;
*************************** 1. row ***************************
                thd_id: 47
              conn_id: 5
                  user: admin@localhost
                    db: sbtest
              command: Query
                state: alter table (merge sort)
                  time: 29
    current_statement: alter table sbtest1 add index i_c(c)
    statement_latency: 29.34 s
              progress: 49.70
          lock_latency: 4.34 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.52 KiB
            last_wait: wait/io/file/innodb/innodb_temp_file
    last_wait_latency: 369.52 us
                source: os0file.ic:470
          trx_latency: 29.45 s
            trx_state: ACTIVE
        trx_autocommit: YES
                  pid: 4667
          program_name: mysql
1 row in set (0.12 sec)

## 查看已经执行完的语句相关统计信息
admin@localhost : sys 07:02:21> select * from session where conn_id!=connection_id() and trx_state='COMMITTED'\G;
*************************** 1. row ***************************
                thd_id: 47
              conn_id: 5
                  user: admin@localhost
                    db: sbtest
              command: Sleep
                state: NULL
                  time: 372
    current_statement: NULL
    statement_latency: NULL
              progress: NULL
          lock_latency: 4.34 ms
        rows_examined: 0
            rows_sent: 0
        rows_affected: 0
            tmp_tables: 0
      tmp_disk_tables: 0
            full_scan: NO
        last_statement: alter table sbtest1 add index i_c(c)
last_statement_latency: 1.61 m
        current_memory: 4.52 KiB
            last_wait: idle
    last_wait_latency: Still Waiting
                source: socket_connection.cc:69
          trx_latency: 1.61 m
            trx_state: COMMITTED
        trx_autocommit: YES
                  pid: 4667
          program_name: mysql
1 row in set (0.12 sec)

• 对于stage事件进度报告要求必须启用events_stages_current consumers，以及需要进度信息的instruments。例如：

stage/sql/Copying to tmp table
stage/innodb/alter table (end)
stage/innodb/alter table (flush)
stage/innodb/alter table (insert)
stage/innodb/alter table (log apply index)
stage/innodb/alter table (log apply table)
stage/innodb/alter table (merge sort)
stage/innodb/alter table (read PK and internal sort)
stage/innodb/buffer pool load

• 对于不支持进度的stage 事件，或者未启用所需的instruments或consumers的stage事件，则进度列为显示为NULL

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-schema-progress-reporting.html

---

上一篇： sys schema 初体验 | 下一篇： sys schema 目录列表