MySQL Bugs: #118192: Performance regression occurs with update statements after 6ba1fef

Bug #118192	Performance regression occurs with update statements after 6ba1fef
Submitted:	14 May 12:05	Modified:	9 Jul 15:23
Reporter:	jinze si	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	8.0.42	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86
Tags:	performance regression

Description:
After commit 6ba1fef, mysql performance appears to be degraded when executing the update statement: 
```
UPDATE comments SET post_id=post_id+1 WHERE id>1000;
```

We find commit 6ba1fef is used to fix performance regression in join for tables with many columns. Therefore it should not affect the performance of the update statement

We tested it using mysqlslap with the following test statement:
```
 ./mysqlslap --concurrency=1 --iterations=1 --create-schema=test --query="$mysql_inst" -uroot -S $(pwd)/bin/mysql.sock --number-of-queries=10000
```
The result of the test statement is as follows:

Before 6ba1fef: 
        Average number of seconds to run all queries: 799.309 seconds
	Minimum number of seconds to run all queries: 799.309 seconds
	Maximum number of seconds to run all queries: 799.309 seconds
	Number of clients running queries: 1
	Average number of queries per client: 10000

After 6ba1fef: 
        Average number of seconds to run all queries: 819.737 seconds
	Minimum number of seconds to run all queries: 819.737 seconds
	Maximum number of seconds to run all queries: 819.737 seconds
	Number of clients running queries: 1
	Average number of queries per client: 10000

How to repeat:
Execute the query statement that we provided above.

same as 118173 - I did not reproduce

OK,here are the server details (also available at 118173)

cat /proc/cpuinfo | grep processor | wc -l
128

cat /proc/cpuinfo | grep 'physical id' | sort | uniq | wc -l
2

cat /proc/meminfo | grep MemTotal
MemTotal:       527702240 kB

Also, our linux kernel is 5.15.0-76-generic(ubuntu~22.04.1)

I also provided the data preparation script earlier, can you see it?

Thanks. Yes, I have seen the script. The bug is forwarded to our perf. team as I can't reproduce the problem using regular server.

Okay, thank you for your attention. If you need any additional information, please let me know. I will also continue to try to find a simpler way to reproduce the issue.

The patch is https://github.com/mysql/mysql-server/commit/6ba1fef

As the commit message says, this is a revert of a performance bug fix. Therefore, it is expected that the performance returns to it's previous level.

Both the fix for bug#37510755 and this revert happened in the time span between two releases, so the fix for the performance issue was never released before it was reverted. Therefore, this is not a performance regression between released versions, and I'm closing this report as not a bug.

Bug#37510755 will stay open until resolved.

Thank you for your reply. 

In fact, I am not saying that there has been a performance degradation between the two release versions. The data I provided above is the performance of commit bf8c8bb and commit 6ba1fef. As you can see, there is indeed a performance degradation between these two commits. 

I believe that no commit should affect the system's (MySQL) performance unless the commit has a specific purpose or is explicitly stated in the commit message.

Therefore, I reported my findings on commit 6ba1fef. Would the official team consider providing some clarification for this commit to prevent such issues from occurring again?

Finally, I have a small question of my own. Has the official team confirmed that commit 6ba1fef caused a performance regression (not a bug)? Furthermore, based on the purpose of commit 6ba1fef, such a regression should not have occurred.  Therefore, what is the cause of this regression?

In addition, I would like to express my sincere gratitude to the MySQL team for their prompt response. As I have been conducting in-depth research on MySQL recently, I hope to thoroughly investigate this issue.