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
😉
Any reason you’re not just using a `show_processlist` procedure?
Hi Gray,
No, no reason 😉
You can of course create one and call it.
If you want only front end connections then use:
select * from sys.session;
This filters where pid != null for you (essentially)..
Hey Colleague !
Thank you for your comment 😉
Cheers
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
It’s upgrade time to MySQL 8.0 😉 but thx for the warning ⚠️ !!
Good information, thanks.
However, your site has an issue. Keeping this page open in the background in Safari or Firefox uses 100% CPU.
Hi Brian,
I don’t see any issue, which page did you have in background ?