Bug #113761 Access performance_schema.data_locks causes SQL execution stuck
Submitted: 25 Jan 13:33 Modified: 19 Sep 9:07
Reporter: Brian Yue (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S5 (Performance)
Version:8.0.36 OS:Any (rhel-7.4)
Assigned to: CPU Architecture:Any (x86-64)
Tags: data_locks, Performace schema

[25 Jan 13:33] Brian Yue 
Description:
Hello,
  We found that, all DML sql statements will encouter giant performance regression if performance_schema.data_locks is access when there is a transation locks too many records.

How to repeat:
[yxx_sysbench@localhost sysbench]$ ./sysbench --test=db/update_index.lua --oltp-auto-inc=off --oltp-table-size=10000000 --oltp-tables-count=1 --mysql-table-engine=innodb --mysql-user=root  --mysql-password='db1x@NJ+1' --mysql-port=6632 --mysql-host=10.229.31.45 --mysql-db=sbtest --max-requests=0 --time=60000 --num-threads=100 --report-interval=1  prepare
WARNING: Both max-requests and max-time are 0, running endless test
sysbench 0.5:  multi-threaded system evaluation benchmark

Creating table 'sbtest1'...
Inserting 10000000 records into 'sbtest1'

[yxxdb_8031@localhost ~]$ mysql -uroot -p'db1x@NJ+1' -h10.229.31.45 -P6632
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.36 Source distribution

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use sbtest
Database changed
mysql>
mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
+------------------+
1 row in set (0.01 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.82 sec)

mysql> create table t1 like sbtest1;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 select * from sbtest1;
Query OK, 10000000 rows affected (1 min 36.55 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t1 for update;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (8.69 sec)

# Stress test using sysbench with update_index.lua, when access the data_locks table, QPS is stucked for about 20 seconds.
mysql> select count(*) from performance_schema.data_locks;
+----------+
| count(*) |
+----------+
| 10000001 |
+----------+
1 row in set (1 min 4.64 sec)

[yxx_sysbench@localhost sysbench]$ ./sysbench --test=db/update_index.lua --oltp-auto-inc=off --oltp-table-size=10000000 --oltp-tables-count=1 --mysql-table-engine=innodb --mysql-user=root  --mysql-password='db1x@NJ+1' --mysql-port=6632 --mysql-host=10.229.31.45 --mysql-db=sbtest --max-requests=0 --time=60000 --num-threads=100 --report-interval=1  run
WARNING: Both max-requests and max-time are 0, running endless test
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 100
Report intermediate results every 1 second(s)
Random number generator seed is 0 and will be ignored

Threads started!

[   1s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 106823.79, response time: 1.66ms (95%)
[   2s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 100478.27, response time: 2.40ms (95%)
[   3s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 104283.79, response time: 2.00ms (95%)
[   4s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 91875.97, response time: 2.69ms (95%)
[   5s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 101481.86, response time: 2.16ms (95%)
[   6s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 92669.33, response time: 2.53ms (95%)
[   7s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 96274.88, response time: 2.49ms (95%)
[   8s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 94500.01, response time: 2.65ms (95%)
[   9s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 98072.88, response time: 2.50ms (95%)
[  10s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 88131.00, response time: 2.80ms (95%)
[  11s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 94533.91, response time: 2.70ms (95%)
[  12s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 84443.16, response time: 3.14ms (95%)
[  13s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 82615.04, response time: 3.08ms (95%)
[  14s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 76597.94, response time: 3.41ms (95%)
[  15s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 74462.01, response time: 3.60ms (95%)
[  16s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 75878.08, response time: 3.80ms (95%)
[  17s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 73810.74, response time: 3.80ms (95%)
[  18s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 75343.39, response time: 3.71ms (95%)
[  19s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 77405.77, response time: 3.54ms (95%)
[  20s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 78194.05, response time: 3.67ms (95%)
[  21s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 79798.24, response time: 3.71ms (95%)
[  22s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 76681.94, response time: 3.85ms (95%)
[  23s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 83474.00, response time: 3.35ms (95%)
[  24s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 6275.41, response time: 4.22ms (95%)
[  25s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  26s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  27s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  28s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  29s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  30s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  31s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  32s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  33s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  34s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  35s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  36s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  37s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  38s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  39s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  40s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  41s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  42s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  43s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  44s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[  45s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 1.00, response time: 21873.25ms (95%)
[  46s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 75771.91, response time: 4.09ms (95%)
[  47s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 78265.37, response time: 3.81ms (95%)
[  48s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 78707.81, response time: 3.51ms (95%)
[  49s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 78178.61, response time: 3.39ms (95%)
[  50s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 82311.35, response time: 3.07ms (95%)
[  51s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 95207.86, response time: 2.62ms (95%)
[  52s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 96025.66, response time: 2.98ms (95%)
[  53s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 99769.90, response time: 2.99ms (95%)
[  54s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 126205.40, response time: 1.88ms (95%)
[  55s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 137148.27, response time: 2.14ms (95%)
[  56s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 132027.89, response time: 1.08ms (95%)
[  57s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 150508.23, response time: 1.41ms (95%)
[  58s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 168550.49, response time: 0.86ms (95%)
[  59s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 143021.71, response time: 1.77ms (95%)
[  60s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 167922.72, response time: 0.85ms (95%)
[  61s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 145551.35, response time: 1.85ms (95%)
[  62s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 176098.07, response time: 0.79ms (95%)
[  63s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 153824.74, response time: 1.44ms (95%)
[  64s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 166479.00, response time: 0.84ms (95%)
[  65s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 148929.29, response time: 1.53ms (95%)
[  66s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 171294.81, response time: 0.80ms (95%)
[  67s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 139725.18, response time: 2.07ms (95%)
[  68s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 165931.75, response time: 0.85ms (95%)
[  69s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 145030.94, response time: 1.63ms (95%)
[  70s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 168411.75, response time: 0.85ms (95%)
[  71s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 138392.77, response time: 1.71ms (95%)
[  72s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 169652.73, response time: 0.85ms (95%)
[  73s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 144586.80, response time: 1.72ms (95%)
[  74s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 167059.71, response time: 0.82ms (95%)
[  75s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 147443.23, response time: 1.73ms (95%)
[  76s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 159824.77, response time: 0.91ms (95%)
[  77s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 150273.38, response time: 1.51ms (95%)
[  78s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 161419.47, response time: 0.91ms (95%)
[  79s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 143844.51, response time: 1.73ms (95%)
[  80s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 161211.36, response time: 0.92ms (95%)
[  81s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 144535.97, response time: 1.65ms (95%)
[  82s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 162781.95, response time: 0.92ms (95%)
[  83s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 145414.65, response time: 1.51ms (95%)
[  84s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 167679.15, response time: 0.89ms (95%)
[  85s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 136585.89, response time: 1.88ms (95%)
[  86s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 158092.04, response time: 1.02ms (95%)
[  87s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 138610.41, response time: 2.05ms (95%)
[  88s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 37208.11, response time: 2.31ms (95%)
[  89s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 140891.90, response time: 1.67ms (95%)
[  90s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 163601.61, response time: 0.91ms (95%)
[  91s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 143685.73, response time: 1.68ms (95%)
[  92s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 163469.02, response time: 1.00ms (95%)
[  93s] threads: 100, tps: 0.00, reads/s: 0.00, writes/s: 139125.53, response time: 1.70ms (95%)
[25 Jan 14:09] MySQL Verification Team 
Hi Mr. Yue,

Thank you for your bug report.

We have carefully analysed your report and we do not see what bug are you reporting here. Definitely not those times, at the end of the report, that are in milliseconds.

However, if you are reporting the execution that is stuck as in:

"
Stress test using sysbench with update_index.lua, when access the data_locks table, QPS is stucked for about 20 seconds.
"

then this is expected behaviour.

Simply, there is a mutex lock that prevents that one container is accessed by two simultaneous threads.

This is, simply put, the expected behaviour.

Not a bug.
[3 Jun 7:02] Brian Yue 
Hello,
  I request to modify the Severity as "S5 (Performance)" and could this issue to be verified please ?
  In the test case, accessing performance_schema.data_locks system table causes concurrent DML operations on the same table to be stuck for tens of seconds, event longer. I thinks it's really dangerous to access data_locks table when there is a heavy workload on MySQL server, and there is no alternative methods. So it's pretty important to mitigate impact on concurrent DML performance of accessing data_locks system table.
  Thanks a lot.
[3 Jun 9:57] MySQL Verification Team 
Hi Mr. Yue,

In order to verify this bug report we need you to provide us with a query or queries that introduce the most of latency.

We can not search for 100 queries and see which one is causing the problem.

Also, profiling information is desirable  for the performance bugs.

Then, we can profile them ourselves and compare the problematic stacktraces ......
[3 Jun 10:36] MySQL Verification Team 
Hi Mr. Yue,

We had some consultation with our Development team and there could be a possibility that your report is verified. 

In order to know whether this is verifiable as a "Performance" bug, we need to know which two versions or releases have you compared and which one demonstrated a performance regression.

We truly need your feedback on this question .......
[3 Jun 12:22] MySQL Verification Team 
Hi Mr. Yue,

It turns out that this is a known performance issue.

Verified as reported for version 8.0 and higher.
[3 Jun 12:24] Marc ALFF 
Setting this issue as verified.

Please note that the following fix, available in MySQL 8.0.37:

Bug 35240825 - performance_schema.data_locks query increased RAM usage to OOM

should improve the situation.

See the MySQL 8.0.37 release notes for details:

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-37.html#mysqld-8-0-37-performance...
[7 Aug 14:18] MySQL Verification Team 
This bug is fixed now in our trunk.

It is yet to be decided the version and release where it will be pushed.

Until then, this bug report will not be closed.
[16 Aug 10:24] TAMILMARAN C 
Hello,
Similar performance issue is still occurring MySQL 8.0.37 too.
[16 Aug 10:31] MySQL Verification Team 
Hi,

We already wrote that this bug is fixed only in the development version.

It is yet to be decided to which stable versions will that patch be ported to.

We shall inform you when we ourselves have that information .......

Hence, all current production releases are affected by this bug, until we inform you which stable releases have been fixed.

Until then, you will just have to wait. It is a very long process, since a patch is quite big.
[16 Aug 10:35] TAMILMARAN C 
Thanks for the clarification MySQL Verification Team
[16 Aug 10:36] MySQL Verification Team 
You are truly welcome.
[22 Aug 9:40] MySQL Verification Team 
This is the original bug for the following one:

https://bugs.mysql.com/bug.php?id=115702
[19 Sep 9:07] Edward Gilmore 
Posted by developer:
 
Added the following note to the MySQL Server 8.0.40 release notes:
	
Redesigned the performance schema data_locks and data_lock_waits tables so
that querying them does not require an exclusive global mutex on the
transaction or lock system. It now iterates over buckets of hash tables
that hold the locks to only latch the actively processed shard, when
previously it iterated over the transactions. This also improves the
iteration logic complexity in terms of speed and memory to decrease the
impact of these queries on the rest of the system.

Note that the query result might show an incomplete list of transaction locks
if it committed, started, or otherwise changed the set of owned locks
in-between visiting two buckets. This differs from previous behavior which
always showed a consistent snapshot of locks held by individual
transactions, although two different transactions could have been
presented at different moments. In other words, the new approach gives a
consistent view of a single wait queue to show conflicting locks with a
waiting lock because they are always in the same bucket, while the old
approach could miss some of them because they belonged to other
transactions. The old approach would always show all the other locks held
by a reported transaction but could miss locks of other transactions even
if they were conflicting.
[19 Sep 9:22] MySQL Verification Team 
Thank you , Edward.
[24 Sep 12:57] Vasanth S 
Hi Edward. Do we have any ETA for the MySQL 8.0.40 release? It will really help us out in planning our next Version upgrade. TIA.