MySQL - view running threads - SHOW PROCESSLIST

1: The goal is clear, which can be achieved by the following commands. The specific effect is shown in the figure below

show full processlist;
select * from information_schema.processlist;

 

SELECT * FROM sys.processlist order by conn_id;

 

2: The meaning of the field can also be guessed from the name. The specific information is as follows

CREATEALGORITHM = TEMPTABLEDEFINER = `mysql.sys` @`localhost` SQLSECURITYINVOKERVIEW `sys`.`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`
) ASselect `pps`.`THREAD_ID` AS `thd_id`,
`pps`.`PROCESSLIST_ID` AS `conn_id`,
if(
  (
    `pps`.`NAME` in(
      'thread/sql/one_connection',
      'thread/thread_pool/tp_one_connection'
    )
  ),
  concat(
    `pps`.`PROCESSLIST_USER`,
    '@',
    convert(`pps`.`PROCESSLIST_HOST` usingutf8mb4)
  ),
  replace(`pps`.`NAME`, 'thread/', '')
) AS `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` isnull),
  `sys`.`format_time`(`esc`.`TIMER_WAIT`),
  NULL
) AS `statement_latency`,
if(
  (`esc`.`END_EVENT_ID` isnull),
  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` isnotnull),
  `sys`.`format_statement`(`esc`.`SQL_TEXT`),
  NULL
) AS `last_statement`,
if(
  (`esc`.`END_EVENT_ID` isnotnull),
  `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` isnull)
    and(`ewc`.`EVENT_NAME` isnotnull)
  ),
  'Still Waiting',
  `sys`.`format_time`(`ewc`.`TIMER_WAIT`)
) AS `last_wait_latency`,
`ewc`.`SOURCE` AS `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``pps` leftjoin `performance_schema`.`events_waits_current``ewc` on((`pps`.`THREAD_ID` = `ewc`.`THREAD_ID`))
              ) leftjoin `performance_schema`.`events_stages_current``estc` on((`pps`.`THREAD_ID` = `estc`.`THREAD_ID`))
            ) leftjoin `performance_schema`.`events_statements_current``esc` on((`pps`.`THREAD_ID` = `esc`.`THREAD_ID`))
          ) leftjoin `performance_schema`.`events_transactions_current``etc` on((`pps`.`THREAD_ID` = `etc`.`THREAD_ID`))
        ) leftjoin `sys`.`x$memory_by_thread_by_current_bytes``mem` on((`pps`.`THREAD_ID` = `mem`.`thread_id`))
      ) leftjoin `performance_schema`.`session_connect_attrs``conattr_pid` on(
        (
          (
            `conattr_pid`.`PROCESSLIST_ID` = `pps`.`PROCESSLIST_ID`
          )
          and(`conattr_pid`.`ATTR_NAME` = '_pid')
        )
      )
    ) leftjoin `performance_schema`.`session_connect_attrs``conattr_progname` on(
      (
        (
          `conattr_progname`.`PROCESSLIST_ID` = `pps`.`PROCESSLIST_ID`
        )
        and(`conattr_progname`.`ATTR_NAME` = 'program_name')
      )
    )
  ) orderby `pps`.`PROCESSLIST_TIME` desc,
  `last_wait_latency` desc

 

3: The detailed meaning of each field, official documents and other blog posts are quite clear, so I will not repeat them. For details, please refer to the following link

https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html

https://dev.mysql.com/doc/refman/5.6/en/thread-commands.html

https://zhuanlan.zhihu.com/p/30743094

https://www.jb51.net/article/156313.htm

 

4: What I want to know at the core is what are the links in? In order to observe the connection status information of the database connection pool, when is the connection created? When is the connection destroyed? When will the connection be recreated? What is the lifetime of the connection? And so on can be observed through this command, and you can filter the database connection information you want to see through the database.

Tags: MySQL Database SQL

Posted on Thu, 07 May 2020 10:45:44 -0400 by ianr