| Bug #118173 | The insert statement suffers a performance penalty after commit cd66c5c. | ||
|---|---|---|---|
| Submitted: | 13 May 6:42 | Modified: | 2 Jul 2:03 |
| Reporter: | jinze si | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S5 (Performance) |
| Version: | 8.0.42 | OS: | Ubuntu (22.04) |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | performance regression | ||
[13 May 12:46]
jinze si
update OS
[1 Jul 14:04]
MySQL Verification Team
Can you share also server details - number of cores and cpu's - amount of ram Thanks
[2 Jul 2:03]
jinze si
OK,here are the server details 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)
Description: After commit cd66c5c, we find that mysql suffers a performance degradation when executing some INSERT statements, here's an example statement: ``` INSERT INTO users SET id = 1, username = 'user1', email = 'user1@example.com' ON DUPLICATE KEY UPDATE email = 'updated_user1@example.com'; ``` 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=1000000 ``` The result of the test statement is as follows: After cd66c5c: Average number of seconds to run all queries: 31.304 seconds Minimum number of seconds to run all queries: 31.304 seconds Maximum number of seconds to run all queries: 31.304 seconds Number of clients running queries: 1 Average number of queries per client: 1000000 Before cd66c5c: Average number of seconds to run all queries: 30.052 seconds Minimum number of seconds to run all queries: 30.052 seconds Maximum number of seconds to run all queries: 30.052 seconds Number of clients running queries: 1 Average number of queries per client: 1000000 How to repeat: We provide our pre-populated data and simply execute the insert statement we provided above Suggested fix: We found that commit cd66c5c fixed a bug when updating row with empty_sql mode. It changed the logic of insert, so is it possible that this is causing a drop in performance.