MySQL Bugs: #36694: bad information_schema performance in .NET app running stored routines

Bug #36694	bad information_schema performance in .NET app running stored routines
Submitted:	13 May 2008 16:20	Modified:	12 Nov 2010 14:26
Reporter:	Bogdan Degtyariov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:		OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	.net, information_schema, performance, stored procedure

Description:
.NET application demonstrates appalling delays when running stored procedures. These delays are not significant for a small number of stored procedures defined for a database. However, once the number is really big (>5000), every call takes not less than 800-1000 milliseconds. Also, the delays occur only for the first call of the procedure not yet executed within the current connection. If the application runs different procedures it has to wait for 1000 ms for every call, which is more than 1,5 hours. 

All this time Connector/NET is waiting on metadata from information_schema (actually it is a "SELECT * FROM INFORMATION_SCHEMA..." query). Before running the stored procedure C/NET has to check IN/OUT parameters and initialize hash structures using the metadata. Consequently, the server query log contains lots of slow queries selecting metadata as follows:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test9' AND ROUTINE_NAME='spa3470'

Execution time is 1020 ms.

The same data can be retrieved from mysql.proc table, but execution time is 20 ms - more than 50 times faster!

Even though there is a workaround that helps .NET applications overcoming the stored procedures performance issues, the problem is in the server implementation of information_schema (so, later this report has to be qualified as server/I_S bug).

How to repeat:
1. Create 10000 stored procedures. Procedures can do the same, but should have different names. For example:

DELIMITER $$

CREATE PROCEDURE `spa1001`(IN param1 INT, OUT param2 INT)
BEGIN 
SELECT COUNT(*) INTO param2 FROM testtab where id>param1;
END$$

DELIMITER ;

2. Run the procedure using MySqlCommand class instance:

  cmd = new MySqlCommand();
  cmd.Connection = conn;
  cmd.CommandText = "spa1001";
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Add("?param1", "3");
  cmd.Parameters["?param1"].Direction = ParameterDirection.Input;

  cmd.Parameters.Add("?param2", MySqlDbType.Int32);
  cmd.Parameters["?param2"].Direction = ParameterDirection.Output;

  cmd.ExecuteNonQuery(); // This line takes 1000 ms to accomplish

Suggested fix:
Connector/NET has an option "Use Procedure Bodies" indicating that the user connecting to the database havs the SELECT privileges for the mysql.proc (stored procedures) table. So, if the option is set to TRUE, it is safe to query mysql.proc directly and take the advantage of greater performance.

The patch is uploaded below.

Patch from "\tags\5.2.1\"

Attachment: patch36694.diff (application/octet-stream, text), 5.39 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47005

Fixed in 5.2.3.  Here is the change log entry

Changed how the procedure schema collection is retrieved.  If 'use procedure bodies=true' then we select on the mysql.proc table directly as this is up to 50x faster than our current IS implementation.  If 'use procedure bodies=false', then the IS collection is queried.

Are 'use procedure bodies=true' and 'use procedure bodies=false' new options? How are these set by the user? Is there a small piece of example code that can illustrate this please?

They are connection string options.  They are set by adding them to your connection string.  Here is an example:

string connStr = "server=localhost;user id=root;database=test;use procedure bodies=true";
MySqlConnection c = new MySqlConnection(connStr);
c.Open();

An entry has been added to the 5.2.3 changelog:

Changed how the procedure schema collection is retrieved. If the connection string contains “use procedure bodies=true” then a select is performed on the mysql.proc table directly, as this is up to 50 times faster than the current Information Schema implementation. If the connection string contains “use procedure bodies=false”, then the Information Schema collection is queried.

More comments and full test case in Bug#36376

Re-opening the bug because Connector/NET 6.3.4 is always using Information_Schema disregard "Use Procedure Bodies=true".

The fix is simple:

=== modified file 'MySql.Data/Provider/Source/ISSchemaProvider.cs'
--- MySql.Data/Provider/Source/ISSchemaProvider.cs	2010-08-18 19:48:34 +0000
+++ MySql.Data/Provider/Source/ISSchemaProvider.cs	2010-09-21 09:15:46 +0000
@@ -188,6 +188,9 @@
         /// <returns></returns>
         public override DataTable GetProcedures(string[] restrictions)
         {
+            if (connection.Settings.UseProcedureBodies)
+                return base.GetProcedures(restrictions);
+
             string[] keys = new string[4];
             keys[0] = "ROUTINE_CATALOG";
             keys[1] = "ROUTINE_SCHEMA";

Slow requests may still be in the following case:

If your user does not have permission to read from mysql.proc and the 'use procedure bodies = FALSE' then Connector / NET for each subsequent request to the stored procedure takes two additional requests.

The first is:
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'database_name' AND ROUTINE_NAME LIKE 'stored_procedure_name'

Reading it can do, but in this request, the body of a stored procedure is missing.

The second is:
SHOW CREATE PROCEDURE database_name '.' Stored_procedure_name '

Thus, not having received the body of a stored procedure, he can not save body stored procedure in cache, and for each subsequent request for it makes the SELECT and SHOW.

Sample from log:
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'buffer' AND ROUTINE_NAME LIKE 'put'
SHOW CREATE PROCEDURE `buffer`.`put`
call `buffer`.`put`()

If your user does not have permission to read from mysql.proc and the 'use procedure bodies = TRUE', you get the error message.
The error message I received just after a fix MySql.Data / Provider / Source / ISSchemaProvider.cs and recompiling.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122764

941 Reggie Burnett	2010-11-03
      - added code that will attempt to enumerate procs via mysql.proc the first time. If we get an 1142 error then we
        mark that we don't have access to that table and use IS from then on. (bug #36694)

fixed in 6.3.6 and 6.4+

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122765

941 Reggie Burnett	2010-11-03
      - added code that will attempt to enumerate procs via mysql.proc the first time. If we get an 1142 error then we
        mark that we don't have access to that table and use IS from then on. (bug #36694)

Changelog entry changed. Bug now fixed for 6.3.6 and 6.4.1.

Hi All

We are having a similar issue where we are having a lot of calls in the slow query logs file like the below.
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE '***********' AND ROUTINE_NAME LIKE '*************';

We are using mysql connector 6.8.3, so I believe this issue was fixed. But we are having occurrences and this is the most similar thread I found, they are taking over 10 seconds in our slow query logs. Also this does not always happen, but it happens quite often.

Any Idea on why this is happening?

Regards
Steve

@Steve Ritchie - Did you learn anything else about this? I have exactly the same symptoms on the same version of connector. I know my login has permission to select from mysql.proc so I'm surprised it's still using I_S.

@NOT_FOUND

What worked for us was adding ProcedureCacheSize=2000, basically what was happening due to the number of procedures we had, the system had to constantly read from the information schema to get the parameters of the stored procedure, what we also noticed was that if we pushed more parameters from our .net application to MySql it was seeing it as a cache miss and again it had to query the information schema so verify you are passing the correct amount of paramters.

Hope this helps

Thanks Steve, really appreciate the suggestion.

For now I've hacked Connector/Net to force use of mysql.proc. (HasProcAccess=True). It works great and given a huge performance boost in our app. 

I'm now suspecting a regression bug but haven't done a thorough analysis yet.

I am using 6.9.6. 
In my connection string I have added 
Use Procedure Bodies=true;ProcedureCacheSize=2500 ,
given all permissions on mysql.proc to my user but it doesn't work. I_S is being queried.

Is there something that I am missing ?