Description:
The diff that adds instant add & drop column support in MySQL 8.0.29 also makes the update-index sysbench microbenchmark a lot slower as throughput drops almost in half in the worst case.
This is explained here:
https://smalldatum.blogspot.com/2024/11/too-many-performance-regressions-for.html
The diff is here:
https://github.com/mysql/mysql-server/commit/e8f422de
How to repeat:
I tested this on several servers. On two small PCs (AMD laptop-class CPU, 1 thread) there isn't a large regression. One two of my large servers (2-socket Intel, 48-core AMD EPYC) there is a small regression. On one of my large servers (32-core AMD EPYC) there is a huge regression. I have yet to figure out why this isn't a big problem on all of my large servers.
This occurs when I run sysbench and how I run sysbench is explained here:
https://smalldatum.blogspot.com/2023/03/how-i-run-sysbench.html
There are ~43 (or ~30) microbenchmarks when I run sysbench. The regression for this bug occurs in the update-index microbenchmark, but I did not try to reproduce this by only doing load and then update-index. So I am not sure whether it will repeat in that case -- hopefully it does as that would save time.
Suggested fix:
I don't have a suggestion yet. But from vmstat I see both more CPU overhead and more context switches so I assume the problem is either mutex or memory system contention.
