PROCESSLIST_INFO column of performance_schema.threads on v5.7.35

vrcsts · December 16, 2021, 3:36pm

Hello,

I have updated a MySQL instance from 5.7.33 to 5.7.35.

On 5.7.33, using MySQL Workbench I could check the current sessions and Workbench did show the last query that session ran (or was running if it had not finished yet). After the update, Workbench no longer shows such information, but instead shows “NULL” if that sessions was not running a query (same behavior as SHOW PROCESSLIST).

After investigation and checking the queries used by Workbench to get such information from performance_schema, I see that the column PROCESSLIST_INFO of the table performance_schema.threads is NULL unless a query is being processed at the time I query the table on v5.7.35 but It does have the last query ran on v5.7.33.

I have not been able to find any mention about this on the release notes (MySQL :: MySQL 5.7 Release Notes :: Preface and Legal Notices) so I dont know why nor when that changed.

Is it possible to restore the behavior of performance_schema.threads PROCESSLIST_INFO column from v5.7.33 to v5.7.35?

Thanks in advance.

Denis_Subbota · November 3, 2022, 7:28am

Hello Vrcsts,
Thank you for asking us.
Are you using the percona server or community?
BTW I check related metrics on freshly created MySQL community v5.7.33 and v5.7.35 and they look similar, so you should not face this issue.

mysql> select * from performance_schema.threads order by thread_id desc limit 1\G
*************************** 1. row ***************************
          THREAD_ID: 36
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 11
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Creating sort index
   PROCESSLIST_INFO: select * from performance_schema.threads order by thread_id desc limit 1
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 151
1 row in set (0.00 sec)

mysql> show  global variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.35                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 5.7.35                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.02 sec)

mysql>  select  * from performance_schema.setup_instruments where name like "%thread%";
+-------------------------------------------------------------------------------+---------+-------+
| NAME                                                                          | ENABLED | TIMED |
+-------------------------------------------------------------------------------+---------+-------+
| wait/synch/mutex/mysys/THR_LOCK_threads                                       | NO      | NO    |
| wait/synch/mutex/session/LOCK_srv_session_threads                             | NO      | NO    |
| wait/synch/mutex/sql/LOCK_thread_cache                                        | NO      | NO    |
| wait/synch/mutex/sql/LOCK_thread_ids                                          | NO      | NO    |
| wait/synch/mutex/innodb/fts_bg_threads_mutex                                  | NO      | NO    |
| wait/synch/mutex/innodb/srv_threads_mutex                                     | NO      | NO    |
| wait/synch/mutex/innodb/thread_mutex                                          | NO      | NO    |
| wait/synch/cond/mysys/THR_COND_threads                                        | NO      | NO    |
| wait/synch/cond/sql/COND_thread_cache                                         | NO      | NO    |
| wait/synch/cond/sql/COND_flush_thread_cache                                   | NO      | NO    |
| wait/synch/cond/myisam/keycache_thread_var::suspend                           | NO      | NO    |
| stage/sql/Waiting for the slave SQL thread to free enough relay log space     | NO      | NO    |
| stage/sql/Waiting for slave thread to start                                   | NO      | NO    |
| stage/sql/Waiting for the slave SQL thread to advance position                | NO      | NO    |
| memory/performance_schema/threads                                             | YES     | NO    |
| memory/performance_schema/events_waits_summary_by_thread_by_event_name        | YES     | NO    |
| memory/performance_schema/events_stages_summary_by_thread_by_event_name       | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name   | YES     | NO    |
| memory/performance_schema/events_transactions_summary_by_thread_by_event_name | YES     | NO    |
| memory/performance_schema/memory_summary_by_thread_by_event_name              | YES     | NO    |
| memory/performance_schema/thread_class                                        | YES     | NO    |
| memory/myisam/keycache_thread_var                                             | NO      | NO    |
+-------------------------------------------------------------------------------+---------+-------+
22 rows in set (0.03 sec)

mysql>  select  * from performance_schema.setup_instruments where name like "%process%";
+-------------------------------------------------------------+---------+-------+
| NAME                                                        | ENABLED | TIMED |
+-------------------------------------------------------------+---------+-------+
| stage/sql/Waiting for slave workers to process their queues | NO      | NO    |
| statement/sql/show_processlist                              | YES     | YES   |
| statement/com/Processlist                                   | YES     | YES   |
+-------------------------------------------------------------+---------+-------+
3 rows in set (0.01 sec)

mysql>

mysql> select * from performance_schema.threads order by thread_id desc limit 1\G
*************************** 1. row ***************************
          THREAD_ID: 34
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 9
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Creating sort index
   PROCESSLIST_INFO: select * from performance_schema.threads order by thread_id desc limit 1
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 133
1 row in set (0.00 sec)

mysql> show  global variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.33                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 5.7.33                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.02 sec)

mysql>  select  * from performance_schema.setup_instruments where name like "%thread%";
+-------------------------------------------------------------------------------+---------+-------+
| NAME                                                                          | ENABLED | TIMED |
+-------------------------------------------------------------------------------+---------+-------+
| wait/synch/mutex/mysys/THR_LOCK_threads                                       | NO      | NO    |
| wait/synch/mutex/session/LOCK_srv_session_threads                             | NO      | NO    |
| wait/synch/mutex/sql/LOCK_thread_cache                                        | NO      | NO    |
| wait/synch/mutex/sql/LOCK_thread_ids                                          | NO      | NO    |
| wait/synch/mutex/innodb/fts_bg_threads_mutex                                  | NO      | NO    |
| wait/synch/mutex/innodb/srv_threads_mutex                                     | NO      | NO    |
| wait/synch/mutex/innodb/thread_mutex                                          | NO      | NO    |
| wait/synch/cond/mysys/THR_COND_threads                                        | NO      | NO    |
| wait/synch/cond/sql/COND_thread_cache                                         | NO      | NO    |
| wait/synch/cond/sql/COND_flush_thread_cache                                   | NO      | NO    |
| wait/synch/cond/myisam/keycache_thread_var::suspend                           | NO      | NO    |
| stage/sql/Waiting for the slave SQL thread to free enough relay log space     | NO      | NO    |
| stage/sql/Waiting for slave thread to start                                   | NO      | NO    |
| stage/sql/Waiting for the slave SQL thread to advance position                | NO      | NO    |
| memory/performance_schema/threads                                             | YES     | NO    |
| memory/performance_schema/events_waits_summary_by_thread_by_event_name        | YES     | NO    |
| memory/performance_schema/events_stages_summary_by_thread_by_event_name       | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name   | YES     | NO    |
| memory/performance_schema/events_transactions_summary_by_thread_by_event_name | YES     | NO    |
| memory/performance_schema/memory_summary_by_thread_by_event_name              | YES     | NO    |
| memory/performance_schema/thread_class                                        | YES     | NO    |
| memory/myisam/keycache_thread_var                                             | NO      | NO    |
+-------------------------------------------------------------------------------+---------+-------+
22 rows in set (0.01 sec)

mysql>  select  * from performance_schema.setup_instruments where name like "%process%";
+-------------------------------------------------------------+---------+-------+
| NAME                                                        | ENABLED | TIMED |
+-------------------------------------------------------------+---------+-------+
| stage/sql/Waiting for slave workers to process their queues | NO      | NO    |
| statement/sql/show_processlist                              | YES     | YES   |
| statement/com/Processlist                                   | YES     | YES   |
+-------------------------------------------------------------+---------+-------+
3 rows in set (0.00 sec)

Could you please check from your end that you have the same instruments enabled?

Regards,
Denis Subbota.
Managed Services, Percona.

Topic		Replies	Views
Performance_schema replication table Percona Server for MySQL 8.0	1	569	October 12, 2023
Performance_schema not capturing prepared stements Percona Server for MySQL 5.6	1	985	May 23, 2023
Server stalled with "closing tables" state, 5.7.29 Percona Server for MySQL 5.7	2	1183	November 30, 2020
benchmark with sysbench0.5 Other MySQL® Questions	6	887	January 14, 2015
SHOW PROCESSLIST is deprecated? Percona Server for MySQL 8.0 new-release	7	1597	January 19, 2024

PROCESSLIST_INFO column of performance_schema.threads on v5.7.35

Related topics