Hello! I have a Percona xtradb Cluster 8.0.42-33.1 cluster installed using percona-operator 1.18.0. I’ve noticed a steady increase in the number of rows in performance_schema.mutex_instances with NAME=wait/synch/mutex/sql/MDL_ticket_store::LOCK_ticket_store_ops. It’s growing by 250K rows per day. There’s almost no load on the cluster. What could be causing this?
It seems this mutex keep tracks of MDL ticket and manage access to the metadata locks currently held by a thread. I don’t see any specific bugs report related to this behaviour.
NAME=wait/synch/mutex/sql/MDL_ticket_store::LOCK_ticket_store_ops.
Did you ran any DDL/Alter operations or using quite a high parallel threads ? Do you have the output of SHOW FULL PROCESSLIST and SHOW ENGINE INNODB STATUS\G ?
The MDL locks if held any can be monitored using below query.
select m.*, t.PROCESSLIST_ID
from performance_schema.metadata_locks m JOIN
performance_schema.threads t ON m.OWNER_THREAD_ID = t.THREAD_ID
where t.PROCESSLIST_ID = <PROCESSLIST_ID> ;
select * from sys.schema_table_lock_waits \G
Similarly for any innodb row locks you can use the below query to get the information.
SELECT
waiting_trx_id,
waiting_pid,
waiting_query,
blocking_trx_id,
blocking_pid,
blocking_query
FROM sys.innodb_lock_waits;
Anil.joshi, thank you for taking the time to address my issue! Alter table queries aren’t executed in large numbers (only when deploying multiple applications, if there are migrations, but these are rare per week). Currently, the SHOW FULL PROCESSLIST query doesn’t show any load (see screenshot). The select * from sys.schema_table_lock_waits \G and SELECT * FROM sys.innodb_lock_waits; queries also return nothing. I’ve attached the output of SHOW ENGINE INNODB STATUS \G to my message. Currently, insert attempts continue into the performance_schema.mutex_instances table (I added the performance_schema_max_mutex_instances = 20000 parameter to limit the constant growth of this table). I’ve also attached a screenshot from PMM, which shows that inserts into performance_schema.mutex_instances are ongoing.
innodb_status.txt (95.0 KB)