| Bug #119052 | Performance regression in 8.0.40 | ||
|---|---|---|---|
| Submitted: | 22 Sep 9:22 | Modified: | 23 Sep 2:43 |
| Reporter: | zhiyang zhi | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 8.0.43 | OS: | Linux (5.10.134-19.al8.x86_64) |
| Assigned to: | CPU Architecture: | x86 (x86_64) | |
| Tags: | comparing 8.0.39 and 8.0.40, performance decreased by 15 times | ||
[22 Sep 17:45]
MySQL Verification Team
Hi, Thank you for the report but have you tried latest 8.0 ?
[23 Sep 2:15]
zhiyang zhi
We also tested the latest MySQL 8.0 version, and the results were largely consistent with version 8.0.40 but showed significant differences compared to version 8.0.39. # On 8.0.42 ，AvgRunSecSql=0.150 sec Benchmark Average number of seconds to run all queries: 0.150 seconds Minimum number of seconds to run all queries: 0.140 seconds Maximum number of seconds to run all queries: 0.189 seconds Number of clients running queries: 50 Average number of queries per client: 1 # On 8.0.43 ，AvgRunSecSql=0.142 sec Benchmark Average number of seconds to run all queries: 0.142 seconds Minimum number of seconds to run all queries: 0.133 seconds Maximum number of seconds to run all queries: 0.239 seconds Number of clients running queries: 50 Average number of queries per client: 1
[23 Sep 2:43]
MySQL Verification Team
Hi, I verified the behavior. This is a known perf change that is result of a bug fix in .40 that added some checks that are slowing things down. We will be making it faster but will not be at .39 level. Thank you for the test case
Description: For the bellow query，performance decreased by 15 times(0.010->0.150), comparing 8.0.39 and 8.0.40 # Query SELECT * FROM stress_table WHERE user_id=2222222222 LIMIT 1; The execution plan of 8.0.39 is the same as 8.0.40. *************************** 1. row *************************** id: 1 select_type: SIMPLE table: stress_table partitions: NULL type: ref possible_keys: idx_userid_id key: idx_userid_id key_len: 8 ref: const rows: 34704 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) How to repeat: 1) Create a database and test table CREATE DATABASE limittestdb; CREATE TABLE stress_table ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL DEFAULT '0', createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_userid_id (user_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; 2) Make data #!/bin/bash # Write data in a loop for i in {1..1000000} do # Write in the userid of the first rank if [ $(($i % 30 )) -eq 0 ]; then mysql -h xxx -P xxx -uxxx -pxxx -D limittestdb -e "insert into stress_table(user_select 1111111111;" fi # Write in the userid of the 2nd ranking if [ $(($i % 50 )) -eq 0 ]; then mysql -h xxx -P xxx -uxxx -pxxx -D limittestdb -e "insert into stress_table(user_select 2222222222;" fi # Write in the userid of the 3rd ranking if [ $(($i % 80 )) -eq 0 ]; then mysql -h xxx -P xxx -uxxx -pxxx -D limittestdb -e "insert into stress_table(user_select 3333333333;" fi # Randomly write other userids mysql -h xxx -P xxx -uxxx -pxxx -D limittestdb -e "insert into stress_table(user_id) selFLOOR(RAND()*50000)+1;" done # Test methods mysqlslap -h xxx -P xxx -uxxx -pxxx --concurrency=50 --iterations=500 --create-schema='limittestdb' --query=/tmp/stress_test.sql # 1 million rows of test data，data distribution, take the top 10 +------------+-------+ | user_id | cnt | +------------+-------+ | 1111111111 | 33333 | | 2222222222 | 20000 | | 3333333333 | 12500 | | 36481 | 39 | | 12254 | 39 | | 29477 | 39 | | 35972 | 38 | | 44853 | 38 | | 6752 | 37 | | 30803 | 37 | +------------+-------+ ... # On 8.0.39，AvgRunSecSql=0.010 sec Benchmark Average number of seconds to run all queries: 0.010 seconds Minimum number of seconds to run all queries: 0.008 seconds Maximum number of seconds to run all queries: 0.014 seconds Number of clients running queries: 50 Average number of queries per client: 1 # On 8.0.40，AvgRunSecSql=0.150 sec Benchmark Average number of seconds to run all queries: 0.150 seconds Minimum number of seconds to run all queries: 0.143 seconds Maximum number of seconds to run all queries: 0.179 seconds Number of clients running queries: 50 Average number of queries per client: 1 # On 8.0.40，when using force index，the query time is 0.006 sec，which is quite close to performance on 8.0.39 SELECT * FROM stress_table force index(idx_userid_id) WHERE user_id=2222222222 LIMIT 1; Benchmark Average number of seconds to run all queries: 0.006 seconds Minimum number of seconds to run all queries: 0.004 seconds Maximum number of seconds to run all queries: 0.017 seconds Number of clients running queries: 50 Average number of queries per client: 1