会话和锁信息查询视图 | 全方位认识 sys 系统库

本期内容先给大家介绍会话信息和锁等待信息查询视图,通过这些视图我们可以清晰地知道每个会话正在做什么事情,是否存在锁等待。

作者 罗小波·沃趣科技高级数据库技术专家
出品 沃趣科技

在上一篇《等待事件统计视图 | 全方位认识 sys 系统库》中,我们介绍了sys 系统库中的等待事件统计视图,本期的内容先给大家介绍会话信息和锁等待信息查询视图,通过这些视图我们可以清晰地知道每个会话正在做什么事情,是否存在锁等待。下面请跟随我们一起开始 sys 系统库的系统学习之旅吧~

PS:由于本文中所提及的视图功能的特殊性(DBA日常工作中除了排查慢SQL之外,另外一个可能需要占用大量精力的地方可能就是锁问题分析),所以下文中会列出相应视图中的select语句文本,以便大家更直观地学习它们。

01.innodb_lock_waits,x$innodb_lock_waits

InnoDB当前锁等待信息,默认按照发生锁等待的开始时间升序排序--wait_started字段即innodb_trx表的trx_wait_started字段。数据来源:information_schema的innodb_trx、innodb_locks、innodb_lock_waits(注:在8.0及其之后的版本中,该视图的信息来源为information_schema的innodb_trx、performance_schema的data_locks和data_lock_waits)

视图查询语句文本

# 不带x$前缀的视图的查询语句
SELECT r.trx_wait_started AS wait_started,
  TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
  TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
  rl.lock_table AS locked_table,
  rl.lock_index AS locked_index,
  rl.lock_type AS locked_type,
  r.trx_id AS waiting_trx_id,
  r.trx_started as waiting_trx_started,
  TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
  r.trx_rows_locked AS waiting_trx_rows_locked,
  r.trx_rows_modified AS waiting_trx_rows_modified,
  r.trx_mysql_thread_id AS waiting_pid,
  sys.format_statement(r.trx_query) AS waiting_query,
  rl.lock_id AS waiting_lock_id,
  rl.lock_mode AS waiting_lock_mode,
  b.trx_id AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_pid,
  sys.format_statement(b.trx_query) AS blocking_query,
  bl.lock_id AS blocking_lock_id,
  bl.lock_mode AS blocking_lock_mode,
  b.trx_started AS blocking_trx_started,
  TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
  b.trx_rows_locked AS blocking_trx_rows_locked,
  b.trx_rows_modified AS blocking_trx_rows_modified,
  CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
  CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
FROM information_schema.innodb_lock_waits w
  INNER JOIN information_schema.innodb_trx b    ON b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r    ON r.trx_id = w.requesting_trx_id
  INNER JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
  INNER JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
ORDER BY r.trx_wait_started;

# x$innodb_lock_waits:在8.0之前的版本,两者无区别

下面我们看看使用该视图查询返回的结果

# 不带x$前缀的视图
root@localhost : sys 12:41:45> select * from innodb_lock_waits\G;
*************************** 1. row ***************************
            wait_started: 2017-09-07 00:42:32
                wait_age: 00:00:12
          wait_age_secs: 12
            locked_table: `luoxiaobo`.`test`
            locked_index: GEN_CLUST_INDEX
            locked_type: RECORD
          waiting_trx_id: 66823
    waiting_trx_started: 2017-09-07 00:42:32
        waiting_trx_age: 00:00:12
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
            waiting_pid: 7
          waiting_query: select * from test limit 1 for update
        waiting_lock_id: 66823:106:3:2
      waiting_lock_mode: X
        blocking_trx_id: 66822
            blocking_pid: 6
          blocking_query: NULL
        blocking_lock_id: 66822:106:3:2
      blocking_lock_mode: X
    blocking_trx_started: 2017-09-07 00:42:19
        blocking_trx_age: 00:00:25
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 6
sql_kill_blocking_connection: KILL 6
1 row in set, 3 warnings (0.00 sec)

视图字段含义如下:

  • wait_started:发生锁等待的开始时间
  • wait_age:锁已经等待了多久,该值是一个时间格式值
  • wait_age_secs:锁已经等待了几秒钟,该值是一个整型值,MySQL 5.7.9中新增
  • locked_table:锁等待的表名称。此列值格式为:schema_name.table_name
  • locked_index:锁等待的索引名称
  • locked_type:锁等待的锁类型
  • waiting_trx_id:锁等待的事务ID
  • waiting_trx_started:发生锁等待的事务开始时间
  • waiting_trx_age:发生锁等待的事务总的锁等待时间,该值是一个时间格式
  • waiting_trx_rows_locked:发生锁等待的事务已经锁定的行数(如果是复杂事务会累计)
  • waiting_trx_rows_modified:发生锁等待的事务已经修改的行数(如果是复杂事务会累计)
  • waiting_pid:发生锁等待的事务的processlist_id
  • waiting_query:发生锁等待的事务SQL语句文本
  • waiting_lock_id:发生锁等待的锁ID
  • waiting_lock_mode:发生锁等待的锁模式
  • blocking_trx_id:持有锁的事务ID
  • blocking_pid:持有锁的事务processlist_id
  • blocking_query:持有锁的事务的SQL语句文本
  • blocking_lock_id:持有锁的锁ID
  • blocking_lock_mode:持有锁的锁模式
  • blocking_trx_started:持有锁的事务的开始时间
  • blocking_trx_age:持有锁的事务已执行了多长时间,该值为时间格式值
  • blocking_trx_rows_locked:持有锁的事务的锁定行数
  • blocking_trx_rows_modified:持有锁的事务需要修改的行数
  • sql_kill_blocking_query:执行KILL语句来杀死持有锁的查询语句(而不是终止会话)。该列在MySQL 5.7.9中新增
  • sql_kill_blocking_connection:执行KILL语句以终止持有锁的语句的会话。该列在MySQL 5.7.9中新增

PS:8.0中废弃information_schema.innodb_locks和information_schema.innodb_lock_waits,迁移到performance_schema.data_locks和performance_schema.data_lock_waits,详见链接:

https://dev.mysql.com/doc/refman/8.0/en/data-locks-table.html
https://dev.mysql.com/doc/refman/8.0/en/data-lock-waits-table.html

02.processlist,x$processlist

包含所有前台和后台线程的processlist信息,默认按照进程等待时间和最近一个语句执行完成的时间降序排序。数据来源:performance_schema的threads、events_waits_current、events_statements_current、events_stages_current 、events_transactions_current 、session_connect_attrs表和 sys 系统库的 x$memory_by_thread_by_current_bytess视图

这些视图列出了进程相关的较为详细的信息,比SHOW PROCESSLIST语句和INFORMATION_SCHEMA PROCESSLIST表更完整,且对该视图的查询是非阻塞的(因为不是从information_schema.processlist表中获取数据的,对processlist表查询是阻塞的)

视图查询语句文本

# 不带x$前缀的视图
SELECT pps.thread_id AS thd_id,
  pps.processlist_id AS conn_id,
  IF(pps.name = 'thread/sql/one_connection',
      CONCAT(pps.processlist_user, '@', pps.processlist_host),
      REPLACE(pps.name, 'thread/', '')) user,
  pps.processlist_db AS db,
  pps.processlist_command AS command,
  pps.processlist_state AS state,
  pps.processlist_time AS time,
  sys.format_statement(pps.processlist_info) AS current_statement,
  IF(esc.end_event_id IS NULL,
      sys.format_time(esc.timer_wait),
      NULL) AS statement_latency,
  IF(esc.end_event_id IS NULL,
      ROUND(100 * (estc.work_completed / estc.work_estimated), 2),
      NULL) AS progress,
  sys.format_time(esc.lock_time) AS lock_latency,
  esc.rows_examined AS rows_examined,
  esc.rows_sent AS rows_sent,
  esc.rows_affected AS rows_affected,
  esc.created_tmp_tables AS tmp_tables,
  esc.created_tmp_disk_tables AS tmp_disk_tables,
  IF(esc.no_good_index_used > 0 OR esc.no_index_used > 0, 'YES', 'NO') AS full_scan,
  IF(esc.end_event_id IS NOT NULL,
      sys.format_statement(esc.sql_text),
      NULL) AS last_statement,
  IF(esc.end_event_id IS NOT NULL,
      sys.format_time(esc.timer_wait),
      NULL) AS last_statement_latency,
  sys.format_bytes(mem.current_allocated) AS current_memory,
  ewc.event_name AS last_wait,
  IF(ewc.end_event_id IS NULL AND ewc.event_name IS NOT NULL,
      'Still Waiting',
      sys.format_time(ewc.timer_wait)) last_wait_latency,
  ewc.source,
  sys.format_time(etc.timer_wait) AS trx_latency,
  etc.state AS trx_state,
  etc.autocommit AS trx_autocommit,
  conattr_pid.attr_value as pid,
  conattr_progname.attr_value as program_name
FROM performance_schema.threads AS pps
LEFT JOIN performance_schema.events_waits_current AS ewc USING (thread_id)
LEFT JOIN performance_schema.events_stages_current AS estc USING (thread_id)
LEFT JOIN performance_schema.events_statements_current AS esc USING (thread_id)
LEFT JOIN performance_schema.events_transactions_current AS etc USING (thread_id)
LEFT JOIN sys.x$memory_by_thread_by_current_bytes AS mem USING (thread_id)
LEFT JOIN performance_schema.session_connect_attrs AS conattr_pid
ON conattr_pid.processlist_id=pps.processlist_id and conattr_pid.attr_name='_pid'
LEFT JOIN performance_schema.session_connect_attrs AS conattr_progname
ON conattr_progname.processlist_id=pps.processlist_id and conattr_progname.attr_name='program_name'
ORDER BY pps.processlist_time DESC, last_wait_latency DESC;

# 带x$前缀的视图查询语句与不带x$前缀的视图查询语句相比,只是少了单位格式化函数
......

下面我们看看使用该视图查询返回的结果

# 不带x$前缀的视图
admin@localhost : sys 04:27:20> select * from processlist where program_name='mysql' and trx_state is not null limit 1\G
*************************** 1. row ***************************
            thd_id: 49
          conn_id: 7
              user: admin@localhost
                db: sbtest
          command: Sleep
            state: NULL
              time: 89
current_statement: NULL
statement_latency: NULL
          progress: NULL
      lock_latency: 157.00 us
    rows_examined: 1000
        rows_sent: 1000
    rows_affected: 0
        tmp_tables: 0
  tmp_disk_tables: 0
        full_scan: YES
    last_statement: select * from sbtest1 limit 1000
last_statement_latency: 2.06 ms
    current_memory: 0 bytes
        last_wait: idle
last_wait_latency: Still Waiting
            source: socket_connection.cc:69
      trx_latency: 1.49 ms
        trx_state: COMMITTED
    trx_autocommit: YES
              pid: 3927
      program_name: mysql
1 row in set (0.13 sec)

# 带x$前缀的视图
admin@localhost : sys 04:27:28> select * from x$processlist where program_name='mysql' and trx_state is not null limit 1\G;
*************************** 1. row ***************************
            thd_id: 49
          conn_id: 7
              user: admin@localhost
                db: sbtest
          command: Sleep
            state: NULL
              time: 150
current_statement: NULL
statement_latency: NULL
          progress: NULL
      lock_latency: 157000000
    rows_examined: 1000
        rows_sent: 1000
    rows_affected: 0
        tmp_tables: 0
  tmp_disk_tables: 0
        full_scan: YES
    last_statement: select * from sbtest1 limit 1000
last_statement_latency: 2055762000
    current_memory: 0
        last_wait: idle
last_wait_latency: Still Waiting
            source: socket_connection.cc:69
      trx_latency: 1490662000
        trx_state: COMMITTED
    trx_autocommit: YES
              pid: 3927
      program_name: mysql
1 row in set (0.14 sec)

视图字段含义如下:

  • thd_id:内部threqd ID
  • conn_id:连接ID,即processlist id
  • user:对于前台线程,该字段值为account名称,对于后台线程,该字段值为后台线程名称
  • db:线程的默认数据库,如果没有默认数据库,则该字段值为NULL
  • command:对于前台线程,表示线程正在执行的客户端代码对应的command名称,如果会话处于空闲状态则该字段值为'Sleep ',对于后台超线程,该字段值为NULL
  • state:表示线程正在做什么:什么事件或状态,与information_schema.processlist表中的state字段值一样
  • time:表示线程处于当前状态已经持续了多长时间(秒)
  • current_statement:线程当前正在执行的语句,如果当前没有执行任何语句,该字段值为NULL
  • statement_latency:线程当前语句已经执行了多长时间,该字段在MySQL 5.7.9中新增
  • progress:在支持进度报告的阶段事件中统计的工作进度百分比。 该字段在MySQL 5.7.9中新增
  • lock_latency:当前语句的锁等待时间
  • rows_examined:当前语句从存储引擎检查的数据行数
  • rows_sent:当前语句返回给客户端的数据行数
  • rows_affected:受当前语句影响的数据行数(DML语句对数据执行变更才会影响行)
  • tmp_tables:当前语句创建的内部内存临时表的数量
  • tmp_disk_tables:当前语句创建的内部磁盘临时表的数量
  • full_scan:当前语句执行的全表扫描次数
  • last_statement:如果在performance_schema.threads表中没有找到正在执行的语句或正在等待执行的语句,那么在该字段可以显示线程执行的最后一个语句(在performance_schema.events_statements_current表中查找,该表中会为每一个线程保留最后一条语句执行的事件信息,其他current后缀的事件记录表也类似)
  • last_statement_latency:线程执行的最近一个语句执行了多长时间
  • current_memory:当前线程分配的字节数
  • last_wait:线程最近的等待事件名称
  • last_wait_latency:线程最近的等待事件的等待时间(执行时间)
  • source:线程最近的等待事件的instruments所在源文件和行号
  • trx_latency:线程当前正在执行的事务已经执行了多长时间,该列在MySQL 5.7.9中新增
  • trx_state:线程当前正在执行的事务的状态,该列在MySQL 5.7.9中新增
  • trx_autocommit:线程当前正在执行的事务的提交模式,有效值为:'ACTIVE','COMMITTED','ROLLED BACK',该列在MySQL 5.7.9中新增
  • pid:客户端进程ID,该列在MySQL 5.7.9中新增
  • program_name:客户端程序名称,该列在MySQL 5.7.9中新增

03.session,x$session

查看当前用户会话的进程列表信息,与processlist&x$processlist视图类似,但是session视图过滤掉了后台线程,只显示前台(用户)线程相关的统计数据,数据来源:sys.processlist

该视图在MySQL 5.7.9中新增

视图查询语句

# 不带x$的视图查询语句
## 只需要在processlist视图的查询语句上加上如下条件即可
......
[WHERE] conn_id IS NOT NULL AND command != 'Daemon';

# 带x$前缀的视图查询语句与不带x$前缀的视图查询语句相比,只是少了单位格式化函数
......

下面我们看看使用该视图查询返回的结果

# 不带x$前缀的视图
admin@localhost : sys 12:44:22> select * from session where command='query' and conn_id!=connection_id()\G
*************************** 1. row ***************************
            thd_id: 48
          conn_id: 6
              user: admin@localhost
                db: xiaoboluo
          command: Query
            state: Sending data
              time: 72
current_statement: select * from test limit 1 for update
statement_latency: 1.20 m
          progress: NULL
      lock_latency: 169.00 us
    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: 461 bytes
        last_wait: wait/io/table/sql/handler
last_wait_latency: Still Waiting
            source: handler.cc:3185
      trx_latency: NULL
        trx_state: NULL
    trx_autocommit: NULL
              pid: 3788
      program_name: mysql
1 row in set (0.15 sec)

# 带x$前缀的视图
admin@localhost : sys 12:45:09> select * from x$session where command='query' and conn_id!=connection_id()\G;
*************************** 1. row ***************************
            thd_id: 48
          conn_id: 6
              user: admin@localhost
                db: xiaoboluo
          command: Query
            state: Sending data
              time: 91
current_statement: select * from test limit 1 for update
statement_latency: 91077336919000
          progress: NULL
      lock_latency: 169000000
    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: 461
        last_wait: wait/io/table/sql/handler
last_wait_latency: Still Waiting
            source: handler.cc:3185
      trx_latency: NULL
        trx_state: NULL
    trx_autocommit: NULL
              pid: 3788
      program_name: mysql
1 row in set (0.13 sec)

视图字段含义

  • 与processlist,x$processlist视图相同,但session视图排除了后台线程和Daemon线程,只查询用户连接线程的信息

04.schema_table_lock_waits,x$schema_table_lock_waits

查看当前链接线程的MDL锁等待信息,显示哪些会话被MDL锁阻塞,是谁阻塞了这些会话,数据来源:performance_schema下的threads、metadata_locks、events_statements_current表

MDL锁的instruments默认没有启用,要使用需要显式开启,如下:
* 启用MDL锁的instruments:update setup_instruments set enabled='yes',timed='yes' where name='wait/lock/metadata/sql/mdl';
* 或者也可以使用sys 系统库下的辅助性视图操作:call sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl');

该视图在MySQL 5.7.9中新增

视图定义语句文本

# 不带x$的视图查询语句
SELECT g.object_schema AS object_schema,
  g.object_name AS object_name,
  pt.thread_id AS waiting_thread_id,
  pt.processlist_id AS waiting_pid,
  sys.ps_thread_account(p.owner_thread_id) AS waiting_account,
  p.lock_type AS waiting_lock_type,
  p.lock_duration AS waiting_lock_duration,
  sys.format_statement(pt.processlist_info) AS waiting_query,
  pt.processlist_time AS waiting_query_secs,
  ps.rows_affected AS waiting_query_rows_affected,
  ps.rows_examined AS waiting_query_rows_examined,
  gt.thread_id AS blocking_thread_id,
  gt.processlist_id AS blocking_pid,
  sys.ps_thread_account(g.owner_thread_id) AS blocking_account,
  g.lock_type AS blocking_lock_type,
  g.lock_duration AS blocking_lock_duration,
  CONCAT('KILL QUERY ', gt.processlist_id) AS sql_kill_blocking_query,
  CONCAT('KILL ', gt.processlist_id) AS sql_kill_blocking_connection
FROM performance_schema.metadata_locks g
INNER JOIN performance_schema.metadata_locks p
ON g.object_type = p.object_type
AND g.object_schema = p.object_schema
AND g.object_name = p.object_name
AND g.lock_status = 'GRANTED'
AND p.lock_status = 'PENDING'
INNER JOIN performance_schema.threads gt ON g.owner_thread_id = gt.thread_id
INNER JOIN performance_schema.threads pt ON p.owner_thread_id = pt.thread_id
LEFT JOIN performance_schema.events_statements_current gs ON g.owner_thread_id = gs.thread_id
LEFT JOIN performance_schema.events_statements_current ps ON p.owner_thread_id = ps.thread_id
WHERE g.object_type = 'TABLE';

# 带x$前缀的视图查询语句与不带x$前缀的视图查询语句相比,只是少了单位格式化函数
......

下面我们看看使用该视图查询返回的结果

admin@localhost : sys 11:31:57> select * from schema_table_lock_waits\G;
*************************** 1. row ***************************
          object_schema: xiaoboluo
            object_name: test
      waiting_thread_id: 1217
            waiting_pid: 1175
        waiting_account: admin@localhost
      waiting_lock_type: EXCLUSIVE
  waiting_lock_duration: TRANSACTION
          waiting_query: alter table test add index i_k(test)
      waiting_query_secs: 58
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
      blocking_thread_id: 49
            blocking_pid: 7
        blocking_account: admin@localhost
      blocking_lock_type: SHARED_WRITE
  blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 7
sql_kill_blocking_connection: KILL 7
*************************** 2. row ***************************
          object_schema: xiaoboluo
            object_name: test
      waiting_thread_id: 1217
            waiting_pid: 1175
        waiting_account: admin@localhost
      waiting_lock_type: EXCLUSIVE
  waiting_lock_duration: TRANSACTION
          waiting_query: alter table test add index i_k(test)
      waiting_query_secs: 58
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
      blocking_thread_id: 1217
            blocking_pid: 1175
        blocking_account: admin@localhost
      blocking_lock_type: SHARED_UPGRADABLE
  blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 1175
sql_kill_blocking_connection: KILL 1175
2 rows in set (0.00 sec)

视图字段含义如下:

  • object_schema:发生MDL锁等待的schema名称
  • OBJECT_NAME:MDL锁等待监控对象的名称
  • waiting_thread_id:正在等待MDL锁的thread ID
  • waiting_pid:正在等待MDL锁的processlist ID
  • waiting_account:正在等待MDL锁的线程关联的account名称
  • waiting_lock_type:被阻塞的线程正在等待的MDL锁类型
  • waiting_lock_duration:该字段来自元数据锁子系统中的锁定时间。有效值为:STATEMENT、TRANSACTION、EXPLICIT,STATEMENT和TRANSACTION值分别表示在语句或事务结束时会释放的锁。 EXPLICIT值表示可以在语句或事务结束时被会保留,需要显式释放的锁,例如:使用FLUSH TABLES WITH READ LOCK获取的全局锁
  • waiting_query:正在等待MDL锁的线程对应的语句文本
  • waiting_query_secs:正在等待MDL锁的语句已经等待了多长时间(秒)
  • waiting_query_rows_affected:受正在等待MDL锁的语句影响的数据行数(该字段来自performance_schema.events_statement_current表,该表中记录的是语句事件,如果语句是多表联结查询,则该语句可能已经执行了一部分DML语句,所以哪怕该语句当前被其他线程阻塞了,被阻塞线程的这个字段也可能出现大于0的值)
  • waiting_query_rows_examined:正在等待MDL锁的语句从存储引擎检查的数据行数(同理,该字段来自performance_schema.events_statement_current表)
  • blocking_thread_id:持有MDL锁的thread ID
  • blocking_pid:持有MDL锁的processlist ID
  • blocking_account:持有MDL锁的线程关联的account名称
  • blocking_lock_type:持有MDL锁的锁类型
  • blocking_lock_duration:与waiting_lock_duration字段解释相同,只是该值与持有MDL锁的线程相关
  • sql_kill_blocking_query:生成的KILL掉持有MDL锁的查询的语句
  • sql_kill_blocking_connection:生成的KILL掉持有MDL锁对应会话的语句

本期内容就介绍到这里,本期内容参考链接如下:

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-lock-waits.html
https://dev.mysql.com/doc/refman/5.7/en/sys-innodb-lock-waits.html
https://dev.mysql.com/doc/refman/5.7/en/sys-processlist.html
https://dev.mysql.com/doc/refman/5.7/en/sys-session.html

| 作者简介

罗小波·沃趣科技高级数据库技术专家

IT从业多年,历任运维工程师,高级运维工程师,运维经理,数据库工程师,曾参与版本发布系统,轻量级监控系统,运维管理平台,数据库管理平台的设计与编写,熟悉MySQL的体系结构时,InnoDB存储引擎,喜好专研开源技术,追求完美。

发表评论

电子邮件地址不会被公开。 必填项已用*标注