processlist,x$processlist

包含所有前台和后台线程的进程列表信息，默认按照进程等待时间和最近一个语句执行完成的时间降序排序。数据来源：performance_schema的threads、events_waits_current、events_statements_current、events_stages_current 、events_transactions_current 、sys.x$memory_by_thread_by_current_bytes、session_connect_attrs
- 这些视图列出了进程相关的较为详细的信息，比SHOW PROCESSLIST语句和INFORMATION_SCHEMA PROCESSLIST表更完整，且对该视图的查询是非阻塞的（因为不是从processlist表中获取数据的，对processlist表查询是阻塞的）
processlist,x$processlist视图字段含义如下：
- thd_id：内部线程ID
- conn_id：连接ID，即processlist id
- user：对于前台线程，该字段值为account名称，对于后台线程，该字段值为后台线程名称
- db：线程的默认数据库，如果没有默认数据库，则该字段值为NULL
- command：对于前台线程，表示线程正在执行的客户端代码对应的命令类型，如果会话处于空闲状态则该字段值为'Sleep ',对于后台超线程，该字段值为NULL
- state：表示线程正在做什么：什么事件或状态，与processlist表中的state字段值一样
- time：表示线程处于当前状态已经持续了多长时间(秒)
- current_statement：线程当前正在执行的语句，如果没有执行任何语句，该字段值为NULL
- statement_latency：线程当前语句已经执行了多长时间，该字段在MySQL 5.7.9中新增
- progress：在支持进度报告的阶段事件中统计的工作进度百分比。详见1.3. sys schema 进度报告，该字段在MySQL 5.7.9中新增
- lock_latency：当前语句的锁等待时间
- rows_examined：当前语句从存储引擎读取的数据行数
- rows_sent：当前语句返回给客户端的数据行数
- rows_affected：受当前语句影响的数据行数（DML语句对数据执行变更才会影响行）
- tmp_tables：当前语句创建的内部内存临时表的数量
- tmp_disk_tables：当前语句创建的内部磁盘临时表的数量
- full_scan：当前语句执行的全表扫描次数
- last_statement：如果在threads表中没有找到正在执行的语句或正在等待执行的语句，那么在该字段可以显示线程执行的最后一个语句(在events_statements_current表中查找，该表中会为每一个线程保留最后一条语句执行的事件信息，其他current后缀的事件记录表也类似)
- last_statement_latency：线程执行的最后一个语句执行了多长时间
- current_memory：当前线程分配的字节数
- last_wait：线程最近的等待事件名称
- last_wait_latency：线程最近的等待事件的等待时间(执行时间)
- source：线程最近的等待事件检测代码的源文件和行号
- 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中新增
视图定义语句

# processlist视图
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW processlist (
  thd_id,
  conn_id,
  user,
  db,
  command,
  state,
  time,
  current_statement,
  statement_latency,
  progress,
  lock_latency,
  rows_examined,
  rows_sent,
  rows_affected,
  tmp_tables,
  tmp_disk_tables,
  full_scan,
  last_statement,
  last_statement_latency,
  current_memory,
  last_wait,
  last_wait_latency,
  source,
  trx_latency,
  trx_state,
  trx_autocommit,
  pid,
  program_name
) AS
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$processlist视图
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$processlist (
  thd_id,
  conn_id,
  user,
  db,
  command,
  state,
  time,
  current_statement,
  statement_latency,
  progress,
  lock_latency,
  rows_examined,
  rows_sent,
  rows_affected,
  tmp_tables,
  tmp_disk_tables,
  full_scan,
  last_statement,
  last_statement_latency,
  current_memory,
  last_wait,
  last_wait_latency,
  source,
  trx_latency,
  trx_state,
  trx_autocommit,
  pid,
  program_name
) AS
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,
      pps.processlist_info AS current_statement,
      IF(esc.end_event_id IS NULL,
          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,
      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,
          esc.sql_text,
          NULL) AS last_statement,
      IF(esc.end_event_id IS NOT NULL,
          esc.timer_wait,
          NULL) AS last_statement_latency,
      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',
          ewc.timer_wait) last_wait_latency,
      ewc.source,
      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;

视图查询信息示例

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)

ERROR: 
No query specified

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)

参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-processlist.html

上一篇： metrics视图 | 下一篇： ps_check_lost_instrumentatio视图

验证、测试、整理：罗小波
QQ：309969177
提示：本系列文章的主体结构遵循Oracle MySQL 官方 5.7 手册中，关于information_schema、mysql schema、performance_schema、sys schema的章节结构体系，并额外添加了一些验证、测试数据。鉴于本人精力和能力有限，难免出现一些纰漏，欢迎大家踊跃指正！

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

processlist,x$processlist

Clone this wiki locally