Bug #55475 Poor performance of `INFORMATION_SCHEMA`.`ROUTINES` table
Submitted: 22 Jul 2010 9:33 Modified: 21 Aug 2010 17:01
Reporter: Giles McArdell Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.49 OS:Linux
Assigned to: CPU Architecture:Any
Tags: information_schema, procedures, routines

[22 Jul 2010 9:33] Giles McArdell
We have over 400 databases, with as many as 50 Procedures in each.

A simple query on `INFORMATION_SCHEMA`.`ROUTINES` to find the procedures in one DB can now take several minutes.

The same query on `mysql`.`proc` takes less than 1 sec.

I suspect there is a full table scan going on as the ROUTINES query seems to take about the same time no matter which DB is requested. Same goes for Functions too.

How to repeat:
You would need a similar setup to the one described above then try:
-- takes at least 3 minutes

select `specific_name` from mysql.proc where db = 'mydbname' and `type` = 'PROCEDURE';
-- take <1sec

Suggested fix:
Possibly modify the function that handles the ROUTINES table to make better use the index on the mysql.proc table?
[22 Jul 2010 10:11] Sveta Smirnova
Thank you for the report.

But version 5.1.39 is old. Also there is bug #49501 fix for which can fix your problem too. Please upgrade to current version 5.1.48, try with it and inform us if problem still exists.
[19 Aug 2010 8:26] Giles McArdell
Sorry for the delay, had to schedule the update around our workload.

I can report the exact same results on v5.1.49
[21 Aug 2010 17:01] Sveta Smirnova
Thank you for the feedback.

I created similar environment as yours with 400 databases and 50 procedures in each, but can not consistently repeat described behavior: select from INFORMATION_SCHEMA takes 2,5 - 3 sec each time and select from mysql.proc takes 0.01 sec most time, but sometimes takes 16.5 - 17 sec. Also it is known issue what queries on INFORMATION_SCHEMA are slow. Therefore closing report as "Can't repeat"