MySQL Bugs: #116014: Performance Degradation in Query Execution Plans

Bug #116014	Performance Degradation in Query Execution Plans
Submitted:	5 Sep 2024 15:43	Modified:	25 Sep 2024 10:56
Reporter:	Bin Wang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	8.0.27+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	performance degradation

Description:
Starting from version 8.0.27, there has been a noticeable degradation in execution plans for BenchmarkSQL TPC-C tests, with throughput during 8-hour stability tests dropping to half of what it was before. This issue was not present in earlier versions.For more detailed analysis, refer to: 
https://advancedmysql.github.io/blogs/performance_degradation.html

How to repeat:
Using BenchmarkSQL TPC-C tests, the issue can be easily reproduced.

Hello Bin Wang,

Thank you for the report and detailed analysis.
May I request you to please share configuration file used, hardware details if any as I would like to reproduce this issue at our end before developers investigate further? Also, really appreciate if you share any pointers that would help me on running BenchmarkSQL TPC-C tests. Thank you once again for the nice and detailed write-up.

Sincerely,
Umesh

This is a general issue that is not necessarily related to configuration; it is a problem that many users encounter when running BenchmarkSQL TPC-C tests for 100% reproducibility. If configuration is needed, you can visit the following link for testing configuration information: https://github.com/advancedmysql/mysql_8.0.27. You can also use the improved version at https://github.com/advancedmysql/mysql-8.0.39 to compare with the official release of version 8.0.39. Simply running the BenchmarkSQL TPC-C test for 8 hours will reveal the differences.

Intel(R) Xeon(R) Gold 6238 CPU @ 2.10GHz
cache_alignment	: 64
cpu MHz		: 3700.000
cache size	: 30976 KB

Node details in a NUMA environment on x86 architecture:
numactl --hardware
available: 4 nodes (0-3)
node 0 cpus: 0 4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 64 68 72 76 80 84 88 92 96 100 104 108 112 116 120 124 128 132 136 140 144 148 152 156 160 164 168 172
node 0 size: 63713 MB
node 0 free: 10649 MB
node 1 cpus: 1 5 9 13 17 21 25 29 33 37 41 45 49 53 57 61 65 69 73 77 81 85 89 93 97 101 105 109 113 117 121 125 129 133 137 141 145 149 153 157 161 165 169 173
node 1 size: 64496 MB
node 1 free: 8371 MB
node 2 cpus: 2 6 10 14 18 22 26 30 34 38 42 46 50 54 58 62 66 70 74 78 82 86 90 94 98 102 106 110 114 118 122 126 130 134 138 142 146 150 154 158 162 166 170 174
node 2 size: 64481 MB
node 2 free: 7646 MB
node 3 cpus: 3 7 11 15 19 23 27 31 35 39 43 47 51 55 59 63 67 71 75 79 83 87 91 95 99 103 107 111 115 119 123 127 131 135 139 143 147 151 155 159 163 167 171 175
node 3 size: 64496 MB
node 3 free: 6749 MB
node distances:
node   0   1   2   3 
  0:  10  21  21  21 
  1:  21  10  21  21 
  2:  21  21  10  21 
  3:  21  21  21  10 

The disks are all NVMe SSDs.

Thank you for the details.

regards,
Umesh