MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide

13.7.5.30 SHOW PROCESSLIST Statement

SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST shows which threads are running. If you have the PROCESS privilege, you can see all threads, even those belonging to other users. Otherwise (without PROCESS), nonanonymous users have access to information about their own threads but not threads for other users, and anonymous users have no access to thread information.

Without the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

The SHOW PROCESSLIST statement is very useful if you get the too many connections error message and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that have the SUPER privilege, to ensure that administrators should always be able to connect and check the system (assuming that you are not giving this privilege to all your users).

Threads can be killed with the KILL statement. See Section 13.7.6.4, “KILL Statement”.

Example of SHOW PROCESSLIST output:

mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1030455
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1004
  State: Has read all relay log; waiting for the slave
         I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
     Id: 3112
   User: replikator
   Host: artemis:2204
     db: NULL
Command: Binlog Dump
   Time: 2144
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 4. row ***************************
     Id: 3113
   User: replikator
   Host: iconnect2:45781
     db: NULL
Command: Binlog Dump
   Time: 2086
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 5. row ***************************
     Id: 3123
   User: stefan
   Host: localhost
     db: apollon
Command: Query
   Time: 0
  State: NULL
   Info: SHOW FULL PROCESSLIST

SHOW PROCESSLIST output has these columns:

Process information is also available from the mysqladmin processlist command (see Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”), the INFORMATION_SCHEMA PROCESSLIST table (see Section 21.16, “The INFORMATION_SCHEMA PROCESSLIST Table”), and the Performance Schema threads table (see Section 22.12.10.3, “The threads Table”). Access to the threads table does not require a mutex and has minimal impact on server performance, in contrast to the other thread information sources, which have negative performance consequences because they require a mutex. The threads table also shows information about background threads, which the other sources do not. This means that threads can be used to monitor thread activity the other sources cannot.