MySQL Bugs: #60644: Slow startup caused by reading information for every schema

Bug #60644	Slow startup caused by reading information for every schema
Submitted:	25 Mar 2011 17:05	Modified:	26 May 2011 8:06
Reporter:	Tim L	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S5 (Performance)
Version:	5.2.32	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	connect, load, many schemas, slow, startup

Description:
SQL Editor is slow (worst i've encountered is 8 minutes) to connect and accept any input. The entire workbench locks while connecting.

A quick tcpdump of generated traffic from MySQL Workbench, shows that when you connect to a server in the SQL Editor, it first executes a "SHOW DATABASES" to get all schemas and then it sets some variables. This is to be expected, but the following is causing me some headaches, when working with workbench over slow internet connections:

SELECT 'table' AS 'OBJECT_TYPE', TABLE_CATALOG as 'CATALOG', TABLE_SCHEMA as 'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.tables WHERE table_type<>'VIEW' AND table_schema = '<SCHEMA_NAME>'

and:

SELECT 'view' AS 'OBJECT_TYPE', TABLE_CATALOG as 'CATALOG', TABLE_SCHEMA as 'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.views WHERE table_schema = '<SCHEMA_NAME>'

This is done for every schema available for the user. The old MySQL Query Browser didn't fetch this extra information on startup, and that made its connections almost instant.

We have about 150 schemas, so even over a fast connection (still through SSH tunnel), this takes quite a while, around 30 seconds.

I always work on our MySQL servers through tunneling, which is standard openssh tunnels. Since these information queries are executed sequentially, you need really fast response times. This is hardly where SSH tunnels shine, and sometimes i'm connected through double SSH tunnels. First to a secure jumphost, then through a SSH tunnel from that jumphost to the SQL server. But double SSH tunnels arent the worst of my setup, it's that my internet connection is a 3G modem, while i'm travelling on the train.

I have 2 workarounds available:

1: Connect with an underprivileged user, who only has access to a couple of schemas.
2: Disable "Show Schema Contents in Schema Tree".

I'm using the first, option 2 removes all my navigation of the tree so I'm left with SQL code to get an overview. Option 1 is fine, but I really need to have access to all our schemas, so my request is that you redesign the SQL Editors startup procedure.

How to repeat:
Open a connection to a server.

Suggested fix:
Disable the information fetching for all schemas, except the default or selected schema.
You could do it when a schema is expanded in the tree. This could introduce a slightly laggy UI experience, so you could make this optional.

"Read information about tables in schema only when user expands schema in the tree" sounds like a reasonable feature request.

What OS do you use?

OS is Ubuntu 10.10
WB Version 5.2.32
Ubuntu package version 5.2.32-1ubu1010

This already works as expected in osx and windows and is specific to Linux

Noted in 5.2.34 changelog.

On Linux, Workbench was overly aggressive about reading schema
information from INFORMATION_SCHEMA, leading to slowdowns when
connecting to the MySQL server. Now information is read only for the
default schema.

Ooh this is so beautiful. Thanks