MySQL Bugs: #69390: Query is more than 2 orders of magnitude slower on MySQL 5.6.11

Bug #69390	Query is more than 2 orders of magnitude slower on MySQL 5.6.11
Submitted:	3 Jun 2013 18:27	Modified:	23 Apr 2018 5:50
Reporter:	Karl Nicoletti	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.11	OS:	Linux (Oracle Linux 6.4)
Assigned to:		CPU Architecture:	Any
Tags:	Optimizer, Poor Performance

Description:
A query involving several left joins on normal and view tables is more than TWO ORDERS MAGNITUDE SLOWER when running on MySQL 5.6.11 Enterprise Edition than when running on MySQL 5.5.20 EE.

Is there work around?

RESULTS: Same machine, same data set, different versions of MySQL...
   
   MySQL 5.5.20 - 0.64 sec   <== NOT a typo!
   MySQL 5.6.10 - 2 min 40.52 sec
   MySQL 5.6.11 - 2 min 40.52 sec

================================
MySQL 5.5.20 EE
================================
mysql> show variables like 'version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| version                 | 5.5.20-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select count(*) from v_media_health_detail;
+----------+
| count(*) |
+----------+
|    11287 |
+----------+
1 row in set (0.64 sec)

================================
MySQL 5.6.10 EE
================================
mysql> show variables like 'version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| version                 | 5.6.10-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
4 rows in set (0.01 sec)

mysql> select count(*) from v_media_health_detail;
+----------+
| count(*) |
+----------+
|    11287 |
+----------+
1 row in set (2 min 40.52 sec)

================================
MySQL 5.6.11 EE
================================
mysql> show variables like 'version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| version                 | 5.6.11-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select count(*) from v_media_health_detail;
+----------+
| count(*) |
+----------+
|    11287 |
+----------+
1 row in set (2 min 40.93 sec)

How to repeat:
Install and configure the MySQL 5.6.11 EE server.
Load the dumped database schema and data.
Run the query "select count(*) from v_media_health_detail;"

(Note: Database DUMP and SCHEMA files are private attachments)

TOP display on MySQL 5..6.11 while running query:

top - 13:16:45 up 11 days, 23:13,  3 users,  load average: 0.96, 0.62, 0.50
Tasks: 195 total,   1 running, 194 sleeping,   0 stopped,   0 zombie
Cpu0  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  :100.0%us,  0.0%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu2  :  0.3%us,  0.7%sy,  0.0%ni, 99.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu4  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu5  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu6  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu7  :  0.3%us,  0.0%sy,  0.0%ni, 99.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  16465500k total,  6751820k used,  9713680k free,   274380k buffers
Swap:  8232956k total,        0k used,  8232956k free,  4513076k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
32652 mysql     20   0 12.7g 1.2g 8840 S 101.1  8.0  11:08.86 mysqld
  641 root      20   0 15080 1172  832 R  0.3  0.0   0:01.06 top
    1 root      20   0 19408 1500 1188 S  0.0  0.0   0:03.21 init
    2 root      20   0     0    0    0 S  0.0  0.0   0:02.90 kthreadd
    3 root      20   0     0    0    0 S  0.0  0.0   0:09.60 ksoftirqd/0
                               .
                               .
                               .

Thank you for the bug report. Please check for duplicate or related with http://bugs.mysql.com/bug.php?id=68979 . Thanks.

Thank you for the report.

Unfortunately archive file which you uploaded got lost from our FTP server. Could you please re-upload again?

I was able to download all files just fine.  But am re-uploading them again.

MySQL 5.5.20 dump file.

Attachment: kkn_MySQL_EE_5.5.20_schema_2013.06.03.sql (application/octet-stream, text), 504.73 KiB.

MySQL 5.6.11 dump file.

Attachment: kkn_MySQL_EE_5.6.11_schema_2013.06.03.sql (application/octet-stream, text), 513.37 KiB.

Thank you for the feedback.

I meant "bug-data-69390.zip" file which you uploaded to our FTP server.

Sorry, Sveta, I don't have that file anymore either.  You might as well mark this bug CLOSED since you may not be able to reproduce the problem.

Casper,

if you have dump which can help us to repeat the problem we will really appreciate if you upload it.

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".