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 😉
follow me
Follow @lefred
Subscribe to Blog via Email
									
Enter your email address to subscribe to this blog and receive notifications of new posts by email.
							Email Address						
mysql> create procedure show_processlist()
    -> begin
    -> select * from sys.processlist where pid is not NULL;
    -> end //
mysql> call show_processlist\G
				
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
		
Leave a ReplyCancel Reply
Your email address will not be published. Required fields are marked *

			Name  *
				Email  *
				Website
			Add Comment *
 Notify me of follow-up comments by email.
 Notify me of new posts by email.
Post Comment 
	
This site uses Akismet to reduce spam. Learn how your comment data is processed.