Pmm Mysql agent

Percona Monitoring and Management (PMM)
1

Description:

We have mysql instance,Version: 8.0.39-30.1 (Percona XtraDB Cluster (GPL), Release rel30, Revision 46271a0, WSREP version 26.1.4.3)

The Mysql instance is one of 3 nodes Xtradb cluster-

The pMM agent i configured to use performance_schema and NOT slow queries.

We notice the following query :slight_smile:

Time: 2026-09-23T04:47:45.914410Z
User@Host: pmm[pmm] @  [10.12.163.2]  Id: 17851549
Schema:   Last_errno: 0  Killed: 0
Query_time: 3.061418  Lock_time: 0.000001  Rows_sent: 478  Rows_examined: 478  Rows_affected: 0  Bytes_sent: 24971
Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
Filesort: No  Filesort_on_disk: No  Merge_passes: 0
No InnoDB statistics available for this query
Log_slow_rate_type: query  Log_slow_rate_limit: 100
SET timestamp=1790138862;
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.events_waits_summary_global_by_event_name;

Is taking 3.061418 sec.

How to Reproduce:

Configure mysql agent to use performance_schema

Version:

pmm-admin status
Agent ID : 2d95840e-abbb-463c-8da6-b532cc268c4d
Node ID : ac7dc9cb-a4d8-4229-a8e3-8075c56cc895
Node name: tspi1my-pkiot01.ad.aruba.it

PMM Server:
Version: 3.4.1

PMM Client:
Connected : true
Time drift : 74.513µs
Latency : 500.75µs
Connection uptime: 100
pmm-admin version: 3.4.1
pmm-agent version: 3.4.1

Logs:

No logs

Expected Result:

Is there any way to tune the above query in order to reduce execution time ?

What is the best reccomemended method to be used to configure mysql agent, performance_schem or slow quesries ?

Actual Result:

Not applicable

Additional Information:

2

Hi @stofa.kenida ,

Can you try the following:

innodb_stats_on_metadata = ON;

and run ANALYZE TABLE.

Thanks,
K

3

Hi Stofa,

The query should not take 3 seconds unless it is being affected by something else.

I would ask, If this query is affecting the instance. Or this performance schema query is affected by the other issues :slight_smile:

What happens when you manually run this query on a instance? Does that takes same amount of time when the instance is in good condition?

4

Currently, we have no critical performance issues on the istance. But only, because there is no heavy traffic and qierues on the database.

I guess, we will have problems if in the future we will havo more traffic.

I confirm. Also if we excute the query mannually from mysql client, it takes 3 secs.

The following status variables is :slight_smile:

Performance_schema_mutex_instances_lost = 114978096

The instance uptime :slight_smile:

Uptime: 184 days 55 min 0 sec

5

did you try config change and analyze?

6

Looking at your slow query log, the query examines only 478 rows. Even though it performs a full table scan, 3 seconds seems unusually high for such a small result set. We should investigate what is causing the delay.

You can try Kedar’s suggestion as well.

On top of that,

SELECT COUNT(*) AS total_mutex_instances FROM performance_schema.mutex_instances;

SELECT NAME, COUNT(*) AS instances FROM performance_schema.mutex_instances GROUP BY NAME ORDER BY instances DESC LIMIT 20;

A related Percona discussion reports growth in LOCK_ticket_store_ops mutex instances. We can try to determine whether the same pattern exists on PXC 8.0.39. MySQL documents mutex_instances as the table listing mutexes observed by Performance Schema.

There was bug reported for that

https://perconadev.atlassian.net/browse/PXC-5310

Also check this

SHOW GLOBAL STATUS LIKE 'Performance_schema_mutex_instances_lost';

SHOW GLOBAL VARIABLES LIKE 'performance_schema_max_mutex_instances';

This is to try to check whether the slow query is related to mutex-instance growth or whether another factor is responsible. Since you’re running PXC 8.0.39, the issue was reported for v8.0.42.

Also if you have any other instance (replica), does this query takes same amount of time on that instance as well?

Is there any possibility to restart the mysql in maintenance window and see if the query behaves same?

7

Hi,

The analyze comand on the table gives the following message :slight_smile:

ERROR 1142 (42000): SELECT, INSERT command denied to user ‘root’@‘localhost’ for table ‘events_waits_summary_global_by_event_name’

I Think, it is expected as analyze command is valid only on innodb or Myisam engines,

On another instance where the mysql versionis the same:

Before restart mysql;

root@localhost : (none) 08:47:08> SELECT count() FROM performance_schema.events_waits_summary_global_by_event_name;
±---------+
| count() |
±---------+
| 478 |
±---------+
1 row in set (1.23 sec)

After restart;

root@localhost : (none) 08:49:56> SELECT count() FROM performance_schema.events_waits_summary_global_by_event_name;
±---------+
| count() |
±---------+
| 478 |
±---------+
1 row in set (0.01 sec)

performance_schema_mutex_instances_lost = 115530431

performance_schema_max_mutex_instances = -1

Thanks

8

Hi @stofa.kenida,

I believe your restart test confirms what Yunus pointed to, the 478 rows are not the problem, it’s the mutex_instances table behind them ( PXC-5310, still open ).

One thing from the PMM side, as nobody mentioned it yet. This query is not coming from QAN, it’s the perf_schema.eventswaits collector of mysqld_exporter, and it runs every 10 sec. So switching query source from perfschema to slowlog will not remove it, you can keep perfschema.

You can disable only this collector and keep the rest, it needs remove and add of the service:

pmm-admin remove mysql <service-name>
pmm-admin add mysql --query-source=perfschema --disable-collectors=perf_schema.eventswaits --username=pmm --password=... --service-name=<service-name>

You will lose only the Performance Schema Wait Events panels on the MySQL dashboards. Till the fix is out, a restart on maintenance window is what clears it ( performance_schema_max_mutex_instances also needs restart, there is more on this in PXC 8.4.6: LOCK_ticket_store_ops mutex instances grow with cumulative connections ).

9

Ok,

Thanks for the analysis and support-