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:
None 
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
Description:
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:
select `SPECIFIC_NAME` from `INFORMATION_SCHEMA`.`ROUTINES` where `ROUTINE_SCHEMA` = 'mydbname' and ROUTINE_TYPE = 'PROCEDURE' ;
-- 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"

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.044 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.