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 😉

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.

To find out more, including how to control cookies, see here: Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.To find out more, including how to control cookies, see here: Cookie Policy