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

on

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 😉

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

  1. Hi Gray,

    No, no reason 😉

    You can of course create one and call it.

    mysql> use sys
    mysql> delimiter //
    mysql> create procedure show_processlist()
        -> begin
        -> select * from sys.processlist where pid is not NULL;
        -> end //
    
    mysql> call show_processlist\G
    
  2. Care in using sys.processlist, I get below with 5.7, and I think blocking for more than 10 seconds to get the progress list should be considered a bug.

    mysql> pager cat > /dev/null
    PAGER set to ‘cat > /dev/null’
    mysql> SET SESSION MAX_EXECUTION_TIME=10000;
    Query OK, 0 rows affected (0.00 sec)

    mysql> show processlist;
    1989 rows in set (0.00 sec)

    mysql> SELECT * FROM information_schema.processlist;
    1982 rows in set (0.01 sec)

    mysql> select * from sys.processlist where pid is not NULL;
    ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

  3. Good information, thanks.
    However, your site has an issue. Keeping this page open in the background in Safari or Firefox uses 100% CPU.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

recent
categories