Information Schema PROCESSLIST Table

Contents

  1. Example
  2. See Also

The Information Schema PROCESSLIST table contains information about running threads.

Similar information can also be returned with the SHOW [FULL] PROCESSLIST statement, or the mariadb-admin processlist command.

It contains the following columns:

ColumnDescription
IDConnection identifier.
USERMariaDB User.
HOSTThe hostname from which this thread is connected.

For Unix socket connections, localhost. For TCP/IP connections, the TCP port is appended (e.g. 192.168.1.17:58061 or other-host.company.com:58061). For system user, this column is blank ('').
DBDefault database, or NULL if none.
COMMANDType of command running, corresponding to the Com_ status variables. See Thread Command Values.
TIMESeconds that the thread has spent on the current COMMAND so far.
STATECurrent state of the thread. See Thread States.
INFOStatement the thread is executing, or NULL if none.
TIME_MSTime in milliseconds with microsecond precision that the thread has spent on the current COMMAND so far (see more).
STAGEThe stage the process is currently in.
MAX_STAGEThe maximum number of stages.
PROGRESSThe progress of the process within the current stage (0-100%).
MEMORY_USEDMemory in bytes used by the thread.
MAX_MEMORY_USEDMaximum memory in bytes used by the thread.
EXAMINED_ROWSRows examined by the thread. Only updated by UPDATE, DELETE, and similar statements. For SELECT and other statements, the value remains zero.
SENT_ROWSNumber of rows sent by the statement being executed. From MariaDB 11.3.0.
QUERY_IDQuery ID.
INFO_BINARYBinary data information
TIDThread ID (MDEV-6756)
TMP_SPACE_USEDSee Limiting Size of Created Disk Temporary Files and Tables Overview. From MariaDB 11.5.

Note that as a difference to MySQL, in MariaDB the TIME column (and also the TIME_MS column) are not affected by any setting of @TIMESTAMP. This means that it can be reliably used also for threads that change @TIMESTAMP (such as the replication SQL thread). See also MySQL Bug #22047.

As a consequence of this, the TIME column of SHOW FULL PROCESSLIST and INFORMATION_SCHEMA.PROCESSLIST can not be used to determine if a slave is lagging behind. For this, use instead the Seconds_Behind_Master column in the output of SHOW SLAVE STATUS.

Note that the PROGRESS field from the information schema, and the PROGRESS field from SHOW PROCESSLIST display different results. SHOW PROCESSLIST shows the total progress, while the information schema shows the progress for the current stage only.. To retrieve a similar "total" Progress value from information_schema.PROCESSLIST as the one from SHOW PROCESSLIST, use

SELECT CASE WHEN Max_Stage < 2 THEN Progress ELSE (Stage-1)/Max_Stage*100+Progress/Max_Stage END 
  AS Progress FROM INFORMATION_SCHEMA.PROCESSLIST;

Example

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST\G
*************************** 1. row ***************************
           ID: 9
         USER: msandbox
         HOST: localhost
           DB: NULL
      COMMAND: Query
         TIME: 0
        STATE: Filling schema table
         INFO: SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
      TIME_MS: 0.351
        STAGE: 0
    MAX_STAGE: 0
     PROGRESS: 0.000
  MEMORY_USED: 85392
EXAMINED_ROWS: 0
     QUERY_ID: 15
  INFO_BINARY: SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
          TID: 11838
*************************** 2. row ***************************
           ID: 5
         USER: system user
         HOST: 
           DB: NULL
      COMMAND: Daemon
         TIME: 0
        STATE: InnoDB shutdown handler
         INFO: NULL
      TIME_MS: 0.000
        STAGE: 0
    MAX_STAGE: 0
     PROGRESS: 0.000
  MEMORY_USED: 24160
EXAMINED_ROWS: 0
     QUERY_ID: 0
  INFO_BINARY: NULL
          TID: 3856
...

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.