MySQL Bugs: #111082: Queries to the Performance Schema Lock up all transactions until KILL

Bug #111082	Queries to the Performance Schema Lock up all transactions until KILL
Submitted:	19 May 2023 0:37	Modified:	19 May 2023 16:54
Reporter:	Dathan Pattishall	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	8.0.25	OS:	Linux (RDS)
Assigned to:		CPU Architecture:	x86
Tags:	innodb, locks, performance, schema

Description:
An RDS server, db.r5.4xlarge running mysql 8.0.25 with peaks of 80K QPS, at around 1000 connections with 30 concurrent queries, of 90% reads 10% writes. Locked up from querying the performance schema. The following query issued every 10 seconds in this environment seem to trigger a mutex in the INNODB storage engine-locking ALL transactions bringing down the server from the application point of view.

SELECT
th.PROCESSLIST_ID,
th.NAME,
th.TYPE,
th.PROCESSLIST_STATE,
th.PROCESSLIST_INFO,
th.PROCESSLIST_TIME,
dl.OBJECT_SCHEMA,
dl.OBJECT_NAME,
dl.LOCK_MODE,
dl.LOCK_STATUS,
COUNT(dlw.REQUESTING_ENGINE_LOCK_ID) AS WAITING_THREADS
FROM
performance_schema.threads AS th
JOIN
performance_schema.data_locks AS dl ON th.THREAD_ID = dl.THREAD_ID
LEFT JOIN
performance_schema.data_lock_waits AS dlw ON dl.ENGINE_LOCK_ID = dlw.BLOCKING_ENGINE_LOCK_ID
WHERE
dl.LOCK_STATUS = 'GRANTED' AND th.PROCESSLIST_INFO IS NOT NULL
GROUP BY
dl.ENGINE_LOCK_ID

AWS Internal team sent this explanation

The internal team has completed their investigation and asked me to relay the following information.

On reviewing the MySQL source code (documents [1] and [2]) you can see that when using the performance_schema.data_locks table to get data from memory on the innodb engine, it requires a mutex. For this instance, the mutex used is the trx_sys mutex. This is done as the engine will scan through active transactions (scan_trx_list) while holding locks on trx_sys which is critical for transaction management. Contention on trx_sys can both cause poor query performance and also prevent transactions from starting and being committed. Their recommendation is to review how queries are run against the “performance_schema.data_locks” table and minimize the use of the query during high load. Information on this lock is listed in document [3]. Although this document mentions Aurora MySQL, this lock is the same for your MySQL instance.

They also mentioned reviewing the query with the support ID below:
6A5502885FE4161245C77118E3A46EEE0C016B19

As this query was running 2000 times per second with each call taking ~500ms.

I hope this information is helpful.

Please feel free to reach out with any other questions or concerns and I will do my best to assist you.

References:
[1] mysql-server/storage/innobase/trx/trx0i_s.cc - https://github.com/mysql/mysql-server/blob/mysql-8.0.25/storage/innobase/trx/trx0i_s.cc
[2] mysql-server/storage/innobase/handler/p_s.cc - https://github.com/mysql/mysql-server/blob/mysql-8.0.25/storage/innobase/handler/p_s.cc
[1] synch/mutex/innodb/trx_sys_mutex - https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/ams-waits.trxsysmutex.html

How to repeat:
Issue this query at high throughput of transactions.

START TRANSACTION
SELECT STATEMENT
COMMIT

START TRANSACTION
SELECT STATEMENT
INSERT/UPDATE
COMMIT

The transactions can last up to a second. About 10-25 concurrent transactions are happening, and the datasize of the tables are rather large, on the order of 1-4TB

The entire transaction engine freezes until the performance schema query is killed.

Suggested fix:
Take a sample of the transaction lock state and present that to the performance schema, to try to avoid grabbing the lock. The documentation indicates that the performance schema can be queried quite often yet, nothing mentions that the data_lock table causes a mutex across ALL of innodb's transactions. I would suggest adding a warning about querying that table in production.

Hi Mr. Pattishall,

Thank you for your bug report.

However, this is expected behaviour.

The columns `data_locks` and `data_lock_waits` are changed every microsecond. Since these are variables within the  volatile structures, we can not allow no parallel reads or writes to those fields. Hence, the mutex is absolutely necessary.

The only way to avoid this is to reorganise entirely huge parts of the MySQL code, for that purpose only. Hence, that is not doable .....

Not a bug.

Do you have a replacement table that I should use? Could I use
innodb_lock_waits ? Without the lock?

The goal is to find the query that is causing the lock and show how many queries are waiting on that query.

Unfortunately, the answer is no.

Locks, mutexes, waits on those and many other values in the realm of the transactional system, are changed at least every millisecond. Hence, there must be protected. You can have no locks only when you do not have any activity.

You might get lesser locks with READ COMMITTED concurrency model ...... Read about it in our Reference Manual.

Hello, I'd like to clarify one thing, as the report seems self-contradictory to me, so I am probably confused. 

A:
> the following query issued every 10 seconds

B:
> this query was running 2000 times per second with each call taking  ~500ms

1. Are A and B talking about the same query?
2. If so, then how to reconcile these two pieces of info? To me one sounds like 0.1Hz, and the other like 2kHz. And then this fragment about ~500ms, sounds like 2Hz unless thousand of threads execute it in parallel.

FYI in case anyone stumbles across this. Check out bug#112035 looks like there are optimization coming here in 8.0.40. 

Also related:
Bug #100537

Thank you, Mr. Reilly.