We are in the process of in-place upgrading from 5.6 to 8 (with 5.7 serving as an intermediate step)
Specifically the versions are:
Server version: 5.6.51-91.0-log Percona Server (GPL), Release 91.0, Revision b59139e
to
Server version: 8.0.36-28 Percona Server (GPL), Release 28, Revision 47601f19
During our performance testing we have noticed extreme performance regressions with queries taking anywhere from 30% to 300% longer. Under moderately-high concurrency (50-100 threads) the problem seems to get even worse.
For a particularly bad (but still representative of our overall observations) read-only geospatial load:
- MySQL 5.6 MyISAM
- 1 threads: 5.2 ms
- 10 threads: 10.1 ms
- 50 threads: 71.5 ms
- MySQL 5.7 MyISAM
- 1 threads: 5.9 ms
- 10 threads: 25.7 ms
- 50 threads: 144.6 ms
- MySQL 5.7 InnoDB
- 1 threads: 34.8 ms
- 10 threads: not tested
- 50 threads: 216.3 ms
- MySQL 8 InnoDB (requires SRID rebuild)
- 1 threads: 44.2 ms
- 10 threads: 56.4 ms
- 50 threads: 258.9 ms
It seems to be primarily cpu-bound. For example, during one heavy read-write test we observed that cpu usage on 5.6 was around 20% whereas the same test on same hardware on 8.0 was pegged at 100% for the duration of the test.
We also tried the community version of MySQL with default config using comparable schema, data size, and queries. As expected, the performance was even worse than Percona Server with our config.
Has anyone seen similar performance regressions in 5.7 and 8.0? Any guidance on how to proceed? As it currently stands, this performance is completely unacceptable.