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

[2 May 4:30] Arun Kumar Venugopal
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.
[2 May 11:59] MySQL Verification Team
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
[16 May 6:02] MySQL Verification Team
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
[26 May 0:57] Swaroop S
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.
[27 May 18:42] Swaroop S
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.
[3 Jun 9:52] MySQL Verification Team
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
[6 Jun 7:25] Arun Kumar Venugopal
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.
[6 Jun 7:52] MySQL Verification Team
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