Bug #40367 SHOW on a broad view is taking too long
Submitted: 28 Oct 2008 8:55 Modified: 22 Jan 2009 17:14
Reporter: Andreas Streichardt Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.28, 5.1.29, 5.0, 5.1 bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: performance, show, VIEW
Triage: Triaged: D3 (Medium)

[28 Oct 2008 8:55] Andreas Streichardt
Description:
I have created some VERY broad views and encountered serious performance issues when running a SHOW command on it. This applies to SHOW CREATE TABLE as well as to SHOW COLUMNS.

I have created a small testcase which shows this:

http://www.3uu.de/x.sql

Of course this is a VERY special use case but interestingly if i scale the number of tables/columns linearly the time needed to perform the query raises exponentially which indicates that there might be a issue somewhere which might hit others (and not so exotic use cases) as well. I tested with 61*2000 columns and the show command ran for about 40 minutes on my computer.

Select performance is perfectly okay. It is just the show which is really really slow. So are shows on the tables of the view.

Furthermore when performing this query SHOW PROCESSLIST tells me that it is checking permissions. I disabled permissions (compiled with NO_EMBEDDED_PERMISSIONS or so) and the result was that the processlist showed nothing so whichever operation is causing these issues doesn't report a status change to the server and thus the processlist is reporting a bogus status.

How to repeat:
Dump in the sql file.

mysql [thread 1]> SHOW COLUMNS FROM x_1;

mysql [thread 2]> show processlist;
+-----+---------+-----------+----------+---------+------+----------------------+------------------+
| Id  | User    | Host      | db       | Command | Time | State                | Info             |
+-----+---------+-----------+----------+---------+------+----------------------+------------------+
| 307 | gpadmin | localhost | opst_mop | Query   |    2 | checking permissions | desc x_1         |

mysql [thread 2 no permissions]> show processlist;
+-----+---------+-----------+----------+---------+------+----------------------+------------------+
| Id  | User    | Host      | db       | Command | Time | State                | Info             |
+-----+---------+-----------+----------+---------+------+----------------------+------------------+
| 307 | gpadmin | localhost | opst_mop | Query   |    2 |                      | desc x_1         |

[...]
[thread 1]38019 rows in set (2 min 39,42 sec)
[29 Oct 2008 9:52] Sveta Smirnova
Thank you for the report.

Verified as described.
[1 Dec 2008 0:00] Bugs System
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".
[1 Dec 2008 9:54] Andreas Streichardt
Ok. Thought the "Feedback required" status meant that the assigned developer should have provided some feedback. The bug is still open and i seriously can't provide more feedback :S The "bug" is simply there and should be reproducable on any mysql server :S At least i have seen it on several mysql servers.
[1 Dec 2008 10:02] Sveta Smirnova
Andreas,

sorry: this was for internal communication. Stay status as "Open" as I need an answer from another developer.