What is the best practice to get a list of all the queries running in MySQL (processlist) ?

If you are a MySQL DBA for a long time (like me), it’s very complicated to get rid of bad habits. One of them I really need to change is the way to retrieve the list of all the running queries (processlist).

Usually, I use SHOW FULL PROCESSLIST which is very convenient, but like querying the Information_Schema, this statement has negative performance consequences because it requires a mutex. Therefore, you should use Performance_Schema which doesn’t require a mutex and has minimal impact on server performance.

Let’s see the output of both commands:

mysql> show full processlist\G                                                                                                                   *************************** 1. row ***************************
     Id: 4
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 376338
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 88
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show full processlist
2 rows in set (0.00 sec)

mysql> SELECT PROCESSLIST_ID AS id, PROCESSLIST_USER AS user, PROCESSLIST_HOST AS host, 
       PROCESSLIST_DB AS db  , PROCESSLIST_COMMAND AS command, PROCESSLIST_TIME AS time, 
       PROCESSLIST_STATE AS state, LEFT(PROCESSLIST_INFO, 80) AS info   
       FROM performance_schema.threads  
       WHERE PROCESSLIST_ID IS NOT NULL AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump')
       ORDER BY PROCESSLIST_TIME ASC\G
*************************** 1. row ***************************
     id: 88
   user: root
   host: localhost
     db: NULL
command: Query
   time: 0
  state: Creating sort index
   info: SELECT PROCESSLIST_ID AS id, PROCESSLIST_USER AS user, PROCESSLIST_HOST AS host,
*************************** 2. row ***************************
     id: 6
   user: NULL
   host: NULL
     db: NULL
command: Daemon
   time: 376415
  state: Suspending
   info: NULL
2 rows in set (0.00 sec)

Of course you can also see the threads in sleep is you want. However this is not very convenient, this is a long query.

Sys schema provides a nice alternative to avoid such complicate query to remember:

mysql> select * from sys.processlist where pid is not NULL\G
*************************** 1. row ***************************
                thd_id: 178
               conn_id: 88
                  user: root@localhost
                    db: sys
               command: Query
                 state: NULL
                  time: 0
     current_statement: select * from sys.processlist where pid is not NULL
     statement_latency: 56.68 ms
              progress: NULL
          lock_latency: 2.49 ms
         rows_examined: 15457
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 4
       tmp_disk_tables: 2
             full_scan: YES
        last_statement: NULL
last_statement_latency: NULL
        current_memory: 1.89 MiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 54.19 ms
             trx_state: ACTIVE
        trx_autocommit: YES
                   pid: 32332
          program_name: mysql
1 row in set (0.06 sec)

As you can see it also provides more very useful information !

So if you want to see the processlist in a production server or if you want to monitor all the queries from the processlist output, I really encourage you to change your habits and use SYS 😉