MySQL 5.7新增sys.session表查看系统运行状态

    在MySQL 5.6以前,我们通过show processlist\G命令查看系统中正在运行的所有进程,从5.7开始,我们又可以通过sys.session表来查看系统正在运行的所有进程,而且该表中的记录相processlist比较完善:

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)


     我们从第1行的输出可以看到,当前运行ALTER TABLE命令给一张表添加索引,还能读出目前该语

句的执行阶段(目前正在读PK和进行内部排序),并能显示该命令完成的进度(19%),还包括当前执行

的时间(5.78s)。

     我们从第2行的输出可以看到,当前在执行INSERT操作,这是一个自动提交的事务,已经运行了

410.06毫秒,我们甚至可以从中读到,需要一个临时表来执行该语句。

      我们从第3行的输出可以看到,从执行SELECT * FROM sys.session开始到现在有一个事务已经持

续了4.94分钟。

      从上可见,我们通过SELECT * FROM sys.session可以获得更多的信息。尝试熟悉并习惯使用

SELECT * FROM sys.session吧!


更多精彩免费学习资料:

wKiom1cfCSKiAVjSAAA5Qqq7ArI528.png

也可以加我qq

wKioL1cfCg3yghXVAACCBz5V3-g367.jpg