MySQL Bugs: #99601: Broken Performance using EXIST function, increasing execution time each loop

Bug #99601	Broken Performance using EXIST function, increasing execution time each loop
Submitted:	16 May 2020 13:29	Modified:	27 Aug 2020 14:10
Reporter:	Ronny Görner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	8.0.1-8.0.20	OS:	Linux (tested on Debian,Ubuntu,CentOS)
Assigned to:		CPU Architecture:	Other (tested on AMD+Intel)

Description:
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

How to repeat:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  test_value INT 
);

INSERT INTO test
(test_value)
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10);

INSERT INTO test (test_value)
SELECT test.test_value
FROM
  test,
  test AS one,  #       100
  test AS two,  #     1.000
  test AS three,#    10.000
  test AS four, #   100.000
  test AS five; # 1.000.000
  
DELIMITER ///
DROP FUNCTION IF EXISTS `exists_breaks`///
CREATE FUNCTION `exists_breaks`(IN_value INT UNSIGNED
 ) RETURNS BOOLEAN
    READS SQL DATA
    DETERMINISTIC
BEGIN

RETURN EXISTS(
  SELECT 1
  FROM test
  WHERE test.test_value = IN_value
);

END///
DELIMITER ;

DELIMITER ///
DROP FUNCTION IF EXISTS `no_exists`///
CREATE FUNCTION `no_exists`(IN_value INT UNSIGNED
 ) RETURNS BOOLEAN
    READS SQL DATA
    DETERMINISTIC
BEGIN

DECLARE result BOOLEAN;

SELECT 1
INTO result
FROM test
WHERE test.test_value = IN_value
LIMIT 1;

RETURN IFNULL(result, FALSE);

END///
DELIMITER ;

# query gets slower with each execution in the same session
SELECT *
FROM test
WHERE
  exists_breaks( test_value )
  AND id < 10000; # adjust value to a number which takes some time, so you can easier spot the longer run time

# has no slow down
SELECT *
FROM test
WHERE
  no_exists( test_value )
  AND id < 10000; # adjust value to a number which takes some time, so you can easier spot the longer run time

EXPLAIN SELECT *
FROM test
WHERE
  exists_breaks( test_value )
  AND id < 10000;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test		range	PRIMARY	PRIMARY	4		18950	100,00	Using where

It is not just a Innodb issue, but an issue in general. Mabye someone can change the topic. Problem occurs with MyISAM, too.

Reproduced using dbdeployer and the script in this report.

The server misbehaves starting from version 8.0.1. 
In 5.7.x and 8.0.0, the query (SELECT * FROM test WHERE  exists_breaks( test_value ) AND id < 10000) takes always the same time to run.

Minimal testcase
---------------- 
drop table if exists t;
create table t(b tinyint) engine=innodb;
insert t(b)values(1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
drop function if exists f;
create function f() returns int reads sql data return exists(select 1 from t where b=1);
set @a:=0,@n:=10000;
select (@a:=@a+1) cnt,benchmark(@n,f()),version();
select (@a:=@a+1) cnt,benchmark(@n,f()),version();
select (@a:=@a+1) cnt,benchmark(@n,f()),version();
select (@a:=@a+1) cnt,benchmark(@n,f()),version();
select (@a:=@a+1) cnt,benchmark(@n,f()),version();
select (@a:=@a+1) cnt,benchmark(@n,f()),version();
select (@a:=@a+1) cnt,benchmark(@n,f()),version();
select (@a:=@a+1) cnt,benchmark(@n,f()),version();
select (@a:=@a+1) cnt,benchmark(@n,f()),version();
select (@a:=@a+1) cnt,benchmark(@n,f()),version();

Hi Mr. Gorner,

Thank you for your performance regression report.

We used somewhat modified test case and got the timing of 1 (one) minute and 8 (eight) seconds for 8.0.20.

We then ran the same script on the latest 5.7 and got a result of 0 (zero) minutes and 12 (twelve) seconds.

This is truly a very serious performance regression !!!!

Verified as reported.

Thanks for confirmation,
also, DISTINCT is the very same result, seems to happen from 8.0.1

But, is it only S5? In my eyes, this makes MySQL 8 unusable for complex queries, because execution times increase each time you run this query, and are extremly slow from the beginning.

Maybe it is very important to add, that the query time increases each time you re-run it. This is 2nd bug on this issue itself.

Affected versions: 8.0.1-8.0.20

I´ve modified the description a little

Thank you, Mr. Görner.

@Sinisa Milivojevic 

Can you please so kind and re-run this test you did yesterday, and please, could you perform this test 3 times? You will see, that from run to run, the time increases. Means, this would block the execution and however, at the end, the whole server. This is not only S5 bug in my eyes.

Hi,

I have done that. I re-run it 5 times yesterday and I confirm your findings.

@Sinisa Milivojevic 

Ok, and so, it is only S5 for you?

For me, this is a big blocker to use V8 because it would even crash our system at the end :(

Hi,

Severity of this bug is not my opinion nor decision. It is severity for all performance regressions, no matter how terrible they are. Even a slowdown of thousand times is till S5.

However, don't be bothered by it. Since this bug is verified, it will be triaged by Development, who will set many parameters which will influence the scheduling of this bug.

I will personally contact them on this matter.

@Sinisa Milivojevic 

Are there any news for me? Thanks

Hi Mr. Gorner,

I can't have news for you.

I will know that the regression is fixed in the same moment when you find out. 

When the regression is fixed, this page will be updated.

3 months later and nobody seem to have a look on it ...

It will be fixed for next release, but we have not gotten around to update the bug report yet.

Thank you, Roy ......

This is fixed in MySQL 8.0.22 by WL#9384, which causes prepared statements to be prepared only once.

See same for docs info/changelog entry.

Closed.

Thank you, Jon.