Description:
After upgrading the database from 5.7.37 to 8.0.41, we found the SQL queries using limit experienced performance regression and abnormal spikes in CPU usage during concurrent execution, failing to meet our business requirements.
However, when using force index on 8.0.41, it performs similarly to 5.7.37.
Test Result:
1、The execution plans for version 8.0.41 and 5.7.37 are the same. With 100,000 rows of data, the execution efficiency of 5.7.37 is an average of 0.003 seconds. Version 8.0.41 is 4 times slower than 5.7.37, and the CPU consumption is also higher (65% for 8.0.41 compared to 35% for 5.7.37).
When the number of test data rows increases to the million level, the execution efficiency of 5.7.37 is still an average of 0.003 seconds, with no performance degradation. However, 8.0.41 is 50 times slower than 5.7.37, and CPU consumption is around 90%.
2、After using the force index, the execution efficiency of 8.0.41 is an average of 0.006 seconds, with CPU consumption at around 42%, which is somewhat slower compared to 5.7.37, but the performance is barely acceptable.
3、When testing with 100,000 lines of test data, tests were also conducted on version 8.0 and above, and it was found that version 8.0 experienced performance degradation twice.
1>Starting from version 8.0.24, the average execution efficiency began to slow down (0.04 -> 0.007)
2>Starting from 8.0.40, the average execution efficiency has slowed down a bit (0.008 -> 0.013)
3>After reaching 8.4.5, the average execution efficiency became slower (0.013 -> 0.134)
+-----------+----------------------------+
| version | cpu | AvgRunSecSql |
| mysql5.7.37 | 35 | 0.003 |
| mysql8.0.15 | 43 | 0.004 |
| mysql8.0.18 | 44 | 0.004 |
| mysql8.0.24 | 60 | 0.007 |
| mysql8.0.27 | 58 | 0.008 |
| mysql8.0.28 | 60 | 0.008 |
| mysql8.0.31 | 59 | 0.009 |
| mysql8.0.35 | 60 | 0.009 |
| mysql8.0.39 | 60 | 0.009 |
| mysql8.0.40 | 65 | 0.013 |
| mysql8.0.41 | 65 | 0.013 |
| mysql8.0.42 | 65 | 0.013 |
| mysql8.0.43 | 65 | 0.013 |
| mysql8.4.5 | 80 | 0.134 |
| mysql9.3.0 | 85 | 0.134 |
+-----------+----------------------------+
How to repeat:
I. Preparation process
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)Continuously write data to ensure uneven distribution of user_id (simulate real online business)
bash /tmp/stress_test_insert.sh
#!/bin/bash
# Write data in a loop
for i in {1..100000}
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
# cpu check
while true
do
STATS=$(top -bn1 | grep "Cpu(s)")
echo "$(date '+%Y-%m-%d %H:%M:%S') - $STATS"
sleep 1
done
II. Testing process
#Test machines: 2 virtual machines with 4 cores and 16G each, tested 3 times to take the average value.
# Core parameters in my.cnf
innodb_print_all_deadlocks=1
innodb_thread_concurrency=128
innodb_io_capacity=1000
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_adaptive_hash_index=off
innodb_file_per_table
innodb_buffer_pool_size=7G
innodb_log_file_size= 1G
innodb_flush_method=O_DIRECT
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_flush_neighbors=0
sort_buffer_size=2M
join_buffer_size=4M
#Test SQL, query the SQL ranked 2nd.
#Default SQL
SELECT * FROM stress_table WHERE user_id=2222222222 LIMIT 1;
# use force index
SELECT * FROM stress_table force index(idx_userid_id) WHERE user_id=2222222222 LIMIT 1;
# Testing methods
mysqlslap -h xxx -P xxx -uxxx -pxxx --concurrency=50 --iterations=500 --create-schema='limittestdb' --query=/tmp/stress_test.sql
III. Test details
1) When the test table has 100,000 rows, the data distribution is as follows.
+------------+----------+
| user_id | count(0) |
+------------+----------+
| 1111111111 | 3333 |
| 2222222222 | 2000 |
| 3333333333 | 1250 |
| 10281 | 10 |
| 4355 | 10 |
| 9442 | 9 |
| 35568 | 9 |
| 38814 | 9 |
| 26267 | 9 |
| 38638 | 9 |
...
2) on 5.7.37，default sql，AvgRunSecSql=0.003 sec，cpu usage is 35%
Benchmark
Average number of seconds to run all queries: 0.003 seconds
Minimum number of seconds to run all queries: 0.003 seconds
Maximum number of seconds to run all queries: 0.012 seconds
Number of clients running queries: 50
Average number of queries per client: 1
3) on 8.0.41，default sql，AvgRunSecSql=0.013 sec，cpu usage is 65%
Benchmark
Average number of seconds to run all queries: 0.013 seconds
Minimum number of seconds to run all queries: 0.012 seconds
Maximum number of seconds to run all queries: 0.024 seconds
Number of clients running queries: 50
Average number of queries per client: 1
The execution plan of 8.0 is the same as 5.7.
*************************** 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: 2000
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
4) on 8.0.41，use force index，AvgRunSecSql=0.007 sec ，ccpu usage is 42%，A bit slower than 5.7.37, but the performance is barely acceptable.
Benchmark
Average number of seconds to run all queries: 0.007 seconds
Minimum number of seconds to run all queries: 0.004 seconds
Maximum number of seconds to run all queries: 0.028 seconds
Number of clients running queries: 50
Average number of queries per client: 1
The execution plan of 8.0 slightly differs from 5.7, with the number of scanned rows decreasing.
*************************** 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: 2
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
Description: After upgrading the database from 5.7.37 to 8.0.41, we found the SQL queries using limit experienced performance regression and abnormal spikes in CPU usage during concurrent execution, failing to meet our business requirements. However, when using force index on 8.0.41, it performs similarly to 5.7.37. Test Result: 1、The execution plans for version 8.0.41 and 5.7.37 are the same. With 100,000 rows of data, the execution efficiency of 5.7.37 is an average of 0.003 seconds. Version 8.0.41 is 4 times slower than 5.7.37, and the CPU consumption is also higher (65% for 8.0.41 compared to 35% for 5.7.37). When the number of test data rows increases to the million level, the execution efficiency of 5.7.37 is still an average of 0.003 seconds, with no performance degradation. However, 8.0.41 is 50 times slower than 5.7.37, and CPU consumption is around 90%. 2、After using the force index, the execution efficiency of 8.0.41 is an average of 0.006 seconds, with CPU consumption at around 42%, which is somewhat slower compared to 5.7.37, but the performance is barely acceptable. 3、When testing with 100,000 lines of test data, tests were also conducted on version 8.0 and above, and it was found that version 8.0 experienced performance degradation twice. 1>Starting from version 8.0.24, the average execution efficiency began to slow down (0.04 -> 0.007) 2>Starting from 8.0.40, the average execution efficiency has slowed down a bit (0.008 -> 0.013) 3>After reaching 8.4.5, the average execution efficiency became slower (0.013 -> 0.134) +-----------+----------------------------+ | version | cpu | AvgRunSecSql | | mysql5.7.37 | 35 | 0.003 | | mysql8.0.15 | 43 | 0.004 | | mysql8.0.18 | 44 | 0.004 | | mysql8.0.24 | 60 | 0.007 | | mysql8.0.27 | 58 | 0.008 | | mysql8.0.28 | 60 | 0.008 | | mysql8.0.31 | 59 | 0.009 | | mysql8.0.35 | 60 | 0.009 | | mysql8.0.39 | 60 | 0.009 | | mysql8.0.40 | 65 | 0.013 | | mysql8.0.41 | 65 | 0.013 | | mysql8.0.42 | 65 | 0.013 | | mysql8.0.43 | 65 | 0.013 | | mysql8.4.5 | 80 | 0.134 | | mysql9.3.0 | 85 | 0.134 | +-----------+----------------------------+ How to repeat: I. Preparation process 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)Continuously write data to ensure uneven distribution of user_id (simulate real online business) bash /tmp/stress_test_insert.sh #!/bin/bash # Write data in a loop for i in {1..100000} 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 # cpu check while true do STATS=$(top -bn1 | grep "Cpu(s)") echo "$(date '+%Y-%m-%d %H:%M:%S') - $STATS" sleep 1 done II. Testing process #Test machines: 2 virtual machines with 4 cores and 16G each, tested 3 times to take the average value. # Core parameters in my.cnf innodb_print_all_deadlocks=1 innodb_thread_concurrency=128 innodb_io_capacity=1000 innodb_read_io_threads=8 innodb_write_io_threads=8 innodb_adaptive_hash_index=off innodb_file_per_table innodb_buffer_pool_size=7G innodb_log_file_size= 1G innodb_flush_method=O_DIRECT innodb_buffer_pool_dump_at_shutdown=1 innodb_buffer_pool_load_at_startup=1 innodb_flush_neighbors=0 sort_buffer_size=2M join_buffer_size=4M #Test SQL, query the SQL ranked 2nd. #Default SQL SELECT * FROM stress_table WHERE user_id=2222222222 LIMIT 1; # use force index SELECT * FROM stress_table force index(idx_userid_id) WHERE user_id=2222222222 LIMIT 1; # Testing methods mysqlslap -h xxx -P xxx -uxxx -pxxx --concurrency=50 --iterations=500 --create-schema='limittestdb' --query=/tmp/stress_test.sql III. Test details 1) When the test table has 100,000 rows, the data distribution is as follows. +------------+----------+ | user_id | count(0) | +------------+----------+ | 1111111111 | 3333 | | 2222222222 | 2000 | | 3333333333 | 1250 | | 10281 | 10 | | 4355 | 10 | | 9442 | 9 | | 35568 | 9 | | 38814 | 9 | | 26267 | 9 | | 38638 | 9 | ... 2) on 5.7.37，default sql，AvgRunSecSql=0.003 sec，cpu usage is 35% Benchmark Average number of seconds to run all queries: 0.003 seconds Minimum number of seconds to run all queries: 0.003 seconds Maximum number of seconds to run all queries: 0.012 seconds Number of clients running queries: 50 Average number of queries per client: 1 3) on 8.0.41，default sql，AvgRunSecSql=0.013 sec，cpu usage is 65% Benchmark Average number of seconds to run all queries: 0.013 seconds Minimum number of seconds to run all queries: 0.012 seconds Maximum number of seconds to run all queries: 0.024 seconds Number of clients running queries: 50 Average number of queries per client: 1 The execution plan of 8.0 is the same as 5.7. *************************** 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: 2000 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) 4) on 8.0.41，use force index，AvgRunSecSql=0.007 sec ，ccpu usage is 42%，A bit slower than 5.7.37, but the performance is barely acceptable. Benchmark Average number of seconds to run all queries: 0.007 seconds Minimum number of seconds to run all queries: 0.004 seconds Maximum number of seconds to run all queries: 0.028 seconds Number of clients running queries: 50 Average number of queries per client: 1 The execution plan of 8.0 slightly differs from 5.7, with the number of scanned rows decreasing. *************************** 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: 2 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)