link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Percona Events Hub

See all of Percona’s upcoming events and view materials like webinars and forums from past events

Percona Live 2023

Percona is an open source database software, support, and services company that helps make databases and applications run better.

Learn More

See Percona’s recent news coverage, press releases and industry recognition for our open source software and support.

News coverage Press Releases

MySQL 8.0.22: SHOW PROCESSLIST Version 2 – Now Available From PERFORMANCE_SCHEMA

January 15, 2021

Sri Sakthivel

SHOW PROCESSLIST Version 2 The “SHOW PROCESSLIST” command is very famous and very useful for MySQL DBAs. It will help you to understand the ongoing thread activities and their current states. By default, the “show processlist” output details will be collected from the thread manager, and it needs the global mutex. From MySQL 8.0.22, we have an alternative way to get the process details from the PERFORMANCE_SCHEMA. It doesn’t need the global mutex. 🙂

Note: We also have the non-blocking SYS schema views “processlist” and “x$processlist”, which provide more complete information than the SHOW PROCESSLIST statement and the INFORMATION_SCHEMA.PROCESSLIST and PERFORMANCE_SCHEMA.PROCESSLIST. But, we can’t integrate this with the “SHOW PROCESSLIST” command.

In this blog, I am going to explain the complete details about the new processlist implementation using PERFORMANCE_SCHEMA.

“SHOW PROCESSLIST” Using Thread Manager (default)

This is the default method.
The default “show processlist” implementation iterates across active threads from within the thread manager while holding a global mutex.
Negatively impacts performance.
Particularly impacts the busy systems quite badly.
The INFORMATION_SCHEMA.PROCESSLIST is one of the sources of process information. This will also use the thread manager to collect the metrics.
By default, “mysqladmin processlist” also uses the thread manager to get the details.

The following statements are equivalent:

SHOW FULL PROCESSLIST; SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; Mysqladmin processlist --verbose

“SHOW PROCESSLIST” Using Performance Schema

Available from MySQL 8.0.22.
It collects the thread details from the PERFORMANCE_SCHEMA>PROCESSLIST table.
Global mutex is not needed.
Helps to avoid the performance impact during querying the “show processlist”, particularly in busy systems.
The implementation also applies to “mysqladmin processlist”

The following statements are equivalent:

SHOW FULL PROCESSLIST; SELECT * FROM PERFORMANCE_SCHEMA.PROCESSLIST; Mysqladmin processlist --verbose

“PERFORMANCE_SCHEMA.PROCESSLIST” table has similar columns as “INFORMATION_SCHEMA.PROCESSLIST”

mysql > desc performance_schema .processlist ;

+ -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- - + -- -- -- -- - + -- -- -- - +

+ -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- - + -- -- -- -- - + -- -- -- - +

| ID | bigint unsigned | NO | PRI | NULL | |

| DB | varchar ( 64 ) | YES | | NULL | |

+ -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- - + -- -- -- -- - + -- -- -- - +

8 rows in set ( 0.00 sec )

mysql > desc information_schema .processlist ;

+ -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- - + -- -- -- -- - + -- -- -- - +

+ -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- - + -- -- -- -- - + -- -- -- - +

| ID | bigint unsigned | NO | | | |

| DB | varchar ( 64 ) | YES | | | |

| TIME | int | NO | | | |

+ -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- + -- -- - + -- -- -- -- - + -- -- -- - +

8 rows in set ( 0.00 sec )

Implementation

Make sure the PERFORMANCE_SCHEMA is enabled at the server startup.
Make sure MySQL was configured and built with the thread instrumentations enabled.

MySQL provides a variable “ performance_schema_show_processlist ” to enable this feature. Once we enable the variable, the “SHOW PROCESSLIST” command will start to show the details from the “PERFORMANCE_SCHEMA.PROCESSLIST” table instead of the thread manager.

The variable has a global scope, no need to restart the MySQL server.

mysql> show global variables like 'performance_schema_show_processlist'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | performance_schema_show_processlist | OFF | +-------------------------------------+-------+ 1 row in set (0.08 sec) mysql> set global performance_schema_show_processlist='ON'; Query OK, 0 rows affected (0.00 sec) mysql> \r Connection id: 23 Current database: *** NONE *** mysql> show global variables like 'performance_schema_show_processlist'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | performance_schema_show_processlist | ON | +-------------------------------------+-------+ 1 row in set (0.00 sec)

mysql > show global variables like 'performance_schema_show_processlist' ;

+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- - +

| Variable _ name | Value |

+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- - +

| performance_schema_show_processlist | OFF |

+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- - +

1 row in set ( 0.08 sec )

mysql > set global performance_schema_show_processlist = 'ON' ;

Query OK , 0 rows affected ( 0.00 sec )

mysql > \ r

Connection id : 23

Current database : * * * NONE * * *

mysql > show global variables like 'performance_schema_show_processlist' ;

+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- - +

| Variable _ name | Value |

+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- - +

| performance_schema_show_processlist | ON |

+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- - +

1 row in set ( 0.00 sec )

Yes, the PERFORMANCE_SCHEMA.PROCESSLIST table is activated for the “SHOW PROCESSLIST”.

“SHOW PROCESSLIST” output from “PERFORMANCE_SCHEMA” :

mysql> show processlist\G *************************** 1. row *************************** Id: 5 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 2461 State: Waiting on empty queue Info: NULL *************************** 2. row *************************** Id: 23 User: root Host: localhost db: NULL Command: Query Time: 0 State: executing Info: show processlist 2 rows in set (0.00 sec)

mysql > show processlist \ G

* * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *

Id : 5

User : event _ scheduler

Host : localhost

db : NULL

Command : Daemon

Time : 2461

State : Waiting on empty queue

Info : NULL

* * * * * * * * * * * * * * * * * * * * * * * * * * * 2. row * * * * * * * * * * * * * * * * * * * * * * * * * * *

Id : 23

User : root

Host : localhost

db : NULL

Command : Query

Time : 0

State : executing

Info : show processlist

2 rows in set ( 0.00 sec )

You can also query the “performance_schema.processlist” table to get the thread information.

mysql> select * from performance_schema.processlist\G *************************** 1. row *************************** ID: 5 USER: event_scheduler HOST: localhost DB: NULL COMMAND: Daemon TIME: 2448 STATE: Waiting on empty queue INFO: NULL *************************** 2. row *************************** ID: 23 USER: root HOST: localhost DB: NULL COMMAND: Query TIME: 0 STATE: executing INFO: select * from performance_schema.processlist 2 rows in set (0.00 sec)

mysql > select * from performance_schema .processlist \ G

* * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *

ID : 5

USER : event _ scheduler

HOST : localhost

DB : NULL

COMMAND : Daemon

TIME : 2448

STATE : Waiting on empty queue

INFO : NULL

* * * * * * * * * * * * * * * * * * * * * * * * * * * 2. row * * * * * * * * * * * * * * * * * * * * * * * * * * *

ID : 23

USER : root

HOST : localhost

DB : NULL

COMMAND : Query

TIME : 0

STATE : executing

INFO : select * from performance_schema .processlist

2 rows in set ( 0.00 sec )

“mysqladmin processlist” output from “performance_schema” :

[ root @ mysql8 vagrant ] # mysqladmin processlist

+ -- -- + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- +

+ -- -- + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- +

+ -- -- + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- + -- -- -- -- - + -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- +

Recommendations

To avoid having some threads ignored, leave the “performance_schema_max_thread_instances” and “performance_schema_max_thread_classes” system variables set to their default value (default = -1, meaning the parameter will be autosized during the server startup).
To avoid having some STATE column values be empty, leave the “performance_schema_max_stage_classes” system variable set to its default (default = -1, meaning the parameter will be autosized during the server startup).

Percona Events Hub

MySQL 8.0.22: SHOW PROCESSLIST Version 2 – Now Available From PERFORMANCE_SCHEMA

“SHOW PROCESSLIST” Using Thread Manager (default)

“SHOW PROCESSLIST” Using Performance Schema

Implementation

Recommendations

Related

Featured

Products

Services

Learn

Discover

About

Percona Events Hub

MySQL 8.0.22: SHOW PROCESSLIST Version 2 – Now Available From PERFORMANCE_SCHEMA

“SHOW PROCESSLIST” Using Thread Manager (default)

“SHOW PROCESSLIST” Using Performance Schema

Implementation

Recommendations

Related

Share This Post!

Follow us for updates

Featured

Products

Services

Learn

Discover

About