Bug #58891 Queries for INFORMATION_SCHEMA take hours and are unkillable
Submitted: 12 Dec 2010 19:03 Modified: 27 Jan 2011 19:28
Reporter: [ name withheld ] Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Information schema Severity:S5 (Performance)
Version:5.1.51 OS:Linux
Assigned to: CPU Architecture:Any

[12 Dec 2010 19:03] [ name withheld ] 
Description:
Query:
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'blabla' takes hours, I tried to kill it after 300 seconds, but
show processlist displays:
5127195 | blabla        | 192.168.1.1:35838 | NULL                                 | Killed         | 6788 | checking permissions | SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'blabla'
There are ten thousand of databases on the host.
open_files_limit is 65000
table_cache_size is 55000

Is it a bug?
How can I speed up such queries?
Or at least kill them effective?

How to repeat:
Query:
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'blabla' takes hours, I tried to kill it after 300 seconds, but
show processlist displays:
5127195 | blabla        | 192.168.1.1:35838 | NULL                                 | Killed         | 6788 | checking permissions | SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'blabla'
There are ten thousand of databases on the host.
open_files_limit is 65000
table_cache_size is 55000

Is it a bug?
How can I speed up such queries?
Or at least kill them effective?
[13 Dec 2010 10:20] Valeriy Kravchuk 
I think this is a duplicate of Bug #56178, to some extent...
[13 Dec 2010 15:58] MySQL Verification Team 
Thank you for the bug report. Which it is the average # of tables for each database in that host with ten thousand of databases?. Thanks in advance.
[13 Dec 2010 17:07] [ name withheld ] 
I lied a bit. There is 148596 databases.
Every database has around 100 tables, but this number is rather a guess than calculation.
[27 Dec 2010 19:28] MySQL Verification Team 
Please see prior comment regarding Duplicate from Valery. Thanks in advance.
[28 Jan 2011 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".