Bug #118570 Performance regression occurs with INSERT statements after c17d86b
Submitted: 2 Jul 2:29 Modified: 2 Jul 21:57
Reporter: jinze si Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S5 (Performance)
Version:8.0.42 OS:Linux (22.04)
Assigned to: CPU Architecture:x86
Tags: performance regression

[2 Jul 2:29] jinze si
Description:
After commit c17d86b, mysql performance appears to be degraded when executing the insert statement: 
```
INSERT INTO eids(id, eid) VALUES (65536, 72004) ON DUPLICATE KEY UPDATE eid = 72004 ;
```

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

Before c17d86b: 
        Average number of seconds to run all queries: 33.879 seconds
	Average number of seconds to run all queries: 35.374 seconds
	Average number of seconds to run all queries: 34.401 seconds
	Average number of seconds to run all queries: 35.198 seconds
	Average number of seconds to run all queries: 34.298 seconds

After c17d86b: 
        Average number of seconds to run all queries: 34.471 seconds
	Average number of seconds to run all queries: 34.572 seconds
	Average number of seconds to run all queries: 36.266 seconds
	Average number of seconds to run all queries: 35.586 seconds
	Average number of seconds to run all queries: 38.208 seconds

How to repeat:
Execute the query statement that we provided above.
[2 Jul 2:29] jinze si
pre-populated data

Attachment: insert.sql (application/octet-stream, text), 6.04 MiB.