innodb: performance (bug?) breakdown using EXISTS
I think, on any MySQL 8 release until 8.0.20 there seems to be bug in the innodb engine.
We had some issues running tests against a cluster, where just simple selects where extremly slow. So we decided to test a single instance, with Percona 5.7 + 8.0, MySQL Community Server 5.7 + 8.0 and Mariadb 10.4.
Interessting was, on Mariadb 10.4, this "error" (in my eyes it is a bug) did not occur, also not in any 5.7 release. It only affects MySQL Community Server 8.0 and Percona Server and Xtradb Cluster in the 8.0 release tree.
We also tested around using different Kernel parameters, other innodb settings, we even compared all the over 500+ settings from Mariadb / MySQL and Percona, we run it in containers, we run it on AMD and XEON processors, all I was thinking about, but still the same issues.
Please find attached testsetup, which is self-explaining.
What is happening:
We have a large table, using EXISTS, on MySQL 5.7 and MariaDB 10.4, we have times < 1 s and it runs well without any impacts;
Indeed, using Percona or MySQL Community V8series, it slows down from 1s to 7s, and, that is the absolute nightmare, this query runs even a little longer each time it is executed. Means first run 7s, next around 20s, then 40s, and so on. Absolutly strange.
We rewrote the demo-query, and then, voila, we had the 1 s execution time "back". It must be something with "EXIST" and the behaviour on MySQL8, is this a bug, or any new way it is running on the engine? I missed up any information about it, but the internet / forums are full with same things where people complain about slowing down there queries on MySQL8 and some wrote about EXISTS in their queries, too.
Thanks for any ideas,