MySQL Bugs: #35654: Long pause when saving stored procedure or table in Query Browser

Bug #35654	Long pause when saving stored procedure or table in Query Browser
Submitted:	28 Mar 2008 16:27	Modified:	9 Jun 2009 12:51
Reporter:	Yvan Rodrigues	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S5 (Performance)
Version:	1.2.13	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any
Tags:	enumerating, performance, query browser, slow, stored procedure

Description:
When saving a stored procedure in Query Browser to a schema with many SP's it takes 15-60 seconds before the cursor changes back to the normal pointer and control of the UI is handed back to the user.

How to repeat:
In query browser, connect to a server and make the default schema one that has many stored procedures. Mine has 45 tables and 125 stored procedures/functions.
Right click a stored procedure and choose edit or create a new one.
Save the SP by clicking Execute.
Observe time until UI returned to user.

Now do the same in a different schema with few tables/SPs-- it can even be on the same server and visible in the right window pane at the same time.
Observer time is about 5 seconds.

Suggested fix:
Probably has something to do with re-enumerating the tables and/or SPs for the UI after executing the original script.

I notice that this problem applies to just altering tables as well. Edit a table and apply changes... wait up to a minute before being able to do anything. This supports the theory that this relates to enumerating of tables, functions and sp's.

The long wait seems to have two parts.

The first part takes about 1/2 the time. During this time the UI doesn't let you do anything.

During the the second half the user gets partial UI functionality. The cursor is the "busy" cursor. It execute button may become available. If you have a script tab open you can move around and type. But if you do anything that requires interaction with the server, e.g. execute a query, something bad will happen, ranging from error messages to total application crash. It is very common to get an error message that the stored procedures list could not be fetched. When this happens the Schemata window shows tables but no functions or SPs. Refreshing this window fixes that but will take 30 seconds to a minute.

Actually I just noticed that even if you put aside everything I wrote in this bug report, just refreshing the Schemata window duplicates this problem, so this supports my theory that the problem is with the task of enumerating the functions and sps. I don't think enumerating the tables is a big task because if you do try to do something while it's busy and you get that fetching stored procedures error, the tables are present in the window.

Also if I didn't mention it previously, I am running Vista. I have not tried this on another computer yet.

I just tried it on another computer.

- Windows XP Home
- Celeron 2.4GHz, 1GB RAM
- stage 1 (no UI) = 23 seconds after editing a SP
- stage 2 (partial UI)

- just refresh schemata window = 22 seconds (I think this is what stage 2 is doing above)

Server is on a remote ISP's host, running server 5.0.45 community

Probably related: 
In MySQL Administrator if I click Catalogs, then my big database it takes ~ 30 seconds before showing anything in Schema Tables tab.

Also here are stats about my database:
- 50 tables, most/all are InnoDB
- 112 indices
- 990,000 rows in all tables
- 110.8MB according to MySQL Administrator
- No views
- About 170 stored procedures.

Sorry, I can't upload the database. There is too much confidential data.

In Query Browser this issue is only evident if the default schema is the big one. If I make a small database the default schema, a Refresh takes 4 seconds.

By the way I am a developer so if you want me to build this in VS and run it with symbols I could take a look. I don't have much time right now though.

1. I am confirming that the problem is still present in 1.2.13

2. I have a theory that this relates to (not) having permission to SELECT on mysql.proc, and the connection string setting "Use Procedure Bodies"

Could you please a dump script of database sample which allows to repeat the behavior reported?. Thanks in advance.

Script to create a database with 200 stored procedures

Attachment: backup enumerate 20081120 1609.sql (application/octet-stream, text), 95.59 KiB.

I've attached a database creation script.

Run the script to create a database with 200 tables.
Start Query Browser and make this database the default schema.
Right click on an SP and choose edit.
Make a trivial change and click Execute.
Hover over the SPs and note the amount of time from clicking Execute to when the cursor changes from the "Busy" cursor to the "Ready" cursor.

On this setup, this took 12 seconds:
- MySql Server 5.1.22-rc on a remote server running
  Core2Duo 2.4, 2048MB RAM, 2x 160gig SATA
- Query Browser 1.2.14 running
  Dell Inspiron 6400 (Core2Duo 1.8GHz)

On a production server on older hardware, low load, dataabse with 500MB of data and about 100 SP'd this takes 30-60 seconds.

Thank you for the feedback. I enabled the general query log to track what QB does when editing a procedure, below partial log:

		      2 Query       use `enumerate_bug`
081230 18:04:09	      4 Query       DROP PROCEDURE IF EXISTS `enumerate_bug`.`foo_sp107`
		      4 Query       CREATE DEFINER=`mabel`@`%` PROCEDURE `foo_sp107`(IN pParam INT, IN pParam2 DATETIME, OUT pParam3 TEXT)

BEGIN SELECT 5; SELECT 5; SELECT 1; SELECT 1; SELECT 1; SELECT 1; SELECT 1; SELECT 1; SELECT 1; SELECT 1; END
		      1 Query       show databases
		      1 Init DB     enumerate_bug
		      1 Query       SHOW FULL TABLES
		      1 Init DB     enumerate_bug
		      1 Init DB     enumerate_bug
		      1 Query       SHOW PROCEDURE STATUS
		      1 Query       SHOW CREATE PROCEDURE `enumerate_bug`.`foo_sp1`
		      1 Query       SHOW CREATE PROCEDURE `enumerate_bug`.`foo_sp10`
		      1 Query       SHOW CREATE PROCEDURE `enumerate_bug`.`foo_sp100`
		      1 Query       SHOW CREATE PROCEDURE `enumerate_bug`.`foo_sp101`
		      1 Query       SHOW CREATE PROCEDURE `enumerate_bug`.`foo_sp102`

and so on.

Can't repo using:

-Vista32
-MySQL 5.1.30-GA
-Query Tools 1.2.15

This problem is mainly visible with remote databases. Local ones can be accessed faster, so they don't suffer from that so much.

Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Query Browser into MySQL Workbench. We won't fix this anymore.

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/