Bug #113761 Access performance_schema.data_locks causes SQL execution stuck
Submitted: 25 Jan 13:33 Modified: 3 Jun 12:22
Reporter: Brian Yue (OCA) Email Updates:
Status: Verified 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...