Not the answer you need?
Register and ask your own question!

innodb: performance (bug?) breakdown using EXISTS

Ronny GörnerRonny Görner Current User Role Supporter
Hi,
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,
Ronny

Answers

  • Ronny GörnerRonny Görner Current User Role Supporter
    Could anyone from percona´s staff assist me in debugging, that we can reproduce it together, and maybe can deeper analyze it?
  • Ronny GörnerRonny Görner Current User Role Supporter
  • PeterPeter Percona CEO Percona Moderator Role
    Hi Ronny,
    This is great!  As you have found MySQL bug  bugs.mysql.com  is right place to report it!
    At Percona we do not tend to change Optimizer as it tends to introduce incompatibilities so wast majority of Optimizer bugs in Percona Server for MySQL are really upsteam bugs.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.