MySQL Bugs: #114841: Performance degradation in MySQL 8.0.36 compared to MySQL 5.6

Bug #114841	Performance degradation in MySQL 8.0.36 compared to MySQL 5.6
Submitted:	2 May 2024 4:30	Modified:	6 Jul 2024 7:52
Reporter:	Arun Kumar Venugopal	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	8.0.36	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86

Description:
We recently upgraded from MySQL 5.6 to MySQL 8.0.36 and noticed performance drop in MySQL 8.0.36 compared to MySQL 5.6.
A query takes more time to run in V8.0.36 compared to v5.6 and also compared to V5.6 the updated V8.0.36 has more memory.

MySQL 5.6 - 4 GB RAM

MySQL 8.0.36 - 8 GB RAM

How to repeat:
Just posted one of the output here and will attach the test cases as a file later.

Example 1:
For MySQL 5.6:

select count(*) from a where mm BETWEEN "2019-12-31" AND "2022-02-09" group by aa;

O/P:
14493460 rows in set (1 min 26.37 sec)

For MySQL 8.0.36:

select count(*) from a where mm BETWEEN "2019-12-31" AND "2022-02-09" group by aa;

O/P:
14493460 rows in set (2 min 4.10 sec)

Also there is an increase in the performance if there is more memory.

For 8.0.36 in Local Machine with 16 GB RAM :
EX 1:
select count(*) from a where mm BETWEEN "2019-12-31" AND "2022-02-09" group by aa;

14493460 rows in set (40.99 sec)

Suggested fix:
Made every suggested innodb configs in our server but still there is no improvement in the performance. Not sure how the query runtime in V5.6 is more efficient with very less memory compared to the upgraded V8.0.36 even though it has more memory with all the tuning.

Hello Arun,

Thank you for the report and feedback.
Could you please attach test case to reproduce the issue at our end? Thank you.

regards,
Umesh

Thank you, Arun for providing the requested data.
I'll try to reproduce and get back to you if anything further needed.
Also, if possible please attach the conf file if not on default settings.

regards,
Umesh

We are experiencing very slow SELECTS on MySQL version 8.0.36.

Server 1:
 - 2GB memory server running Debian 12 (bookworm)
 - mysql Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL)

    SELECT COUNT(*) FROM table_a;
    Output: 1278116, 1 row in set (1.81 sec)

Server 2:
 - 8GB memory server running Ubuntu 20.04.6 LTS (Focal Fossa)
 - mysql Ver 8.0.36-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

    SELECT COUNT(*) FROM table_a;
    Output: 1278116, 1 row in set (16 min 18.17 sec)

The configurations on Server 1 and 2 are identical.

Upgrading MySQL from 

    Ver 8.0.36-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

to 

    Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL)

resolved the issue for us.

Hello Arun,

Could you please confirm if you are seeing the issue in 8.0.37? Bug #112767 (related Bug #97709, Bug #103213 etc.)fixed and confirmed that issue no longer seen. Thank you.

regards,
Umesh

Hi Team,

We tried executing our queries in MySQL V8.0.37 and we observed the datas are mismatching whereas we are gettting the proper results in V8.0.36 even though the query runtime is very slow.

Hello Arun,

Thank you for the details.
>>we observed the datas are mismatching whereas we are gettting the proper results in V8.0.36 

Please clarify here a bit, you mean 8.0.37 performance is better than 8.0.36 but data returned in 8.0.36 and 8.0.37 are different?  If possible please share exact test case, conf file if not on default and mark it as private if you prefer. I had not noticed this issue while testing hence I need the details to confirm. Also, it would be good to report a new bug for the data mismatch and close this one if performance is better in 8.0.37. Thank you.

regards,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".