MySQL Bugs: #53582: slowness "Opening SQL Editor" whenserver has many databases

Bug #53582	slowness "Opening SQL Editor" whenserver has many databases
Submitted:	11 May 2010 20:16	Modified:	23 Jul 2010 12:56
Reporter:	Ray Rizzuto	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	performance, slow opening sql editor

Description:
When I open a connection to a server that has many dbs (9577 according to show databases), the MySQL Workbench takes minutes before it presents a list, and even then it takes a long time to expand the specific db I want to work with to see the tables and fields of a specific table.

This was not an issue with the MySQL Query Browser.

How to repeat:
get a server, create a couple thousand db's, and just connect to it.

Thank you for the bug report. Which is the exactly MySQL server version are you connecting?. Thanks in advance.

MySQL server version is 5.0.77

This occurs to me on server 5.2.35 with < 100 databases.

5.2.19 seemed to work great, but the slowness has returned on 5.2.20 and 5.2.21.

Here's the console output during the slowness:

(mysql-workbench-bin:17963): Gtk-WARNING **: gtk_scrolled_window_add(): cannot add non scrollable widget use gtk_scrolled_window_add_with_viewport() instead
** Message: query.explain built-in command is being overwritten
The font "-*-Bitstream Vera Sans Mono-medium-r-*-*-*-90-*-*-*-*-iso10646-1,-*-*-medium-r-*-*-*-90-*-*-*-*-iso8859-*" does not support all the required character sets for the current locale "en_US.UTF-8"
  (Missing character set "ISO8859-13")
  (Missing character set "JISX0208.1983-0")
  (Missing character set "KSC5601.1987-0")
  (Missing character set "GB2312.1980-0")
  (Missing character set "JISX0201.1976-0")

(mysql-workbench-bin:17963): Gtk-CRITICAL **: gtk_tree_view_unref_tree_helper: assertion `node != NULL' failed

(mysql-workbench-bin:17963): Gtk-CRITICAL **: gtk_tree_view_unref_tree_helper: assertion `node != NULL' failed

(mysql-workbench-bin:17963): Gtk-CRITICAL **: gtk_box_pack: assertion `child->parent == NULL' failed

(mysql-workbench-bin:17963): Gtk-CRITICAL **: gtk_box_pack: assertion `child->parent == NULL' failed
** Message: TreeViewImpl::add_column: handle width!
** Message: TreeViewImpl::add_column: handle width!
The font "-*-Inconsolata-medium-r-*-*-*-110-*-*-*-*-iso10646-1,-*-*-medium-r-*-*-*-110-*-*-*-*-iso8859-*" does not support all the required character sets for the current locale "en_US.UTF-8"
  (Missing character set "ISO8859-13")
  (Missing character set "KSC5601.1987-0")
  (Missing character set "GB2312.1980-0")
  (Missing character set "JISX0201.1976-0")

Is there a way to produce more verbose output?

Related, if I attempt to use the --force-sw-render option, I get a segmentation fault at the same point. See attached.

Software Render Segfault

Attachment: sw-render-segfault.log (application/octet-stream, text), 58.05 KiB.

Verified as described.

Here is my sql test script for creating schemas

Attachment: create_databases.sql (text/x-sql), 661 bytes.

You can go to Edit -> Preferences -> SQL Editor and turn off either or both of:
Show Live Schema Overview
Show Schema Contents in Schema Tree

I do not see these options in the 5.2.20 OSS Beta Revision 5783 I am running:

Show Live Schema Overview
Show Schema Contents in Schema Tree

Check for updates says I have the latest.

I would rather see the workbench not hang loading all the schemas.  I.e. either it could load in the background, or maybe not load them at all if the # schemas exceeds some threshold.

Apparently there is a bug that causes all schema contents to be loaded in startup, in Linux,
when it should load them on-demand.

Fixed loading of all schema contents when Opening SQL Editor

I just installed the 5.2.22 version of workbench, and the symptoms are different.  The editor is responsive more quickly when connecting to a server with a large number of databases, but oddly the object browser pane is blank for several minutes.  I hope the final fix improves on this.  I think the object browser should display all the databases, but not load schemas until the specific db is selected.

Hi Ray,

could you give us a summary of how many schemas (including contained tables/views/routines) are on your server?  i dont see such a huge lag on our system.
thanks in advance

show databases returns 9807 rows, i.e. db's.   I can't easily do a 'show tables from' against all of them, but the ones I spot checked had no more than 3 tables.

You can run:

select table_schema, count(*) from information_schema.tables  group by table_schema;
select table_schema, count(*) from information_schema.views group by table_schema;
select routine_schema, count(*) from information_schema.routines  group by routine_schema;

To get the summaries for everything. Omit table_schema/routine_schema from the column list if you don't want to reveal the schema names.

I tried the first query, and after an hour still had no results.

Can you try disabling the Overview in Preferences and see if it makes things better?

Also, how long does a simple SHOW DATABASES query take for you in the cmdline shell?

And (sorry for spamming), how long does a query like
SELECT * from information_schema.tables WHERE table_schema = 'some_schema_name' take?

1) I turned off Overview in preferences.  No noticeable improvement.
2) I don't have the mysql command line tool - where can I get it?
3) I tried SELECT * from information_schema.tables WHERE table_schema = 'one of my schemas' and it didn't complete even after waiting 10 minutes.

for 2) You can just run the query in WB itself (or in QB), doesn't need to be in the cmdline client.

Also, from the results you're getting it seems to be more of a server issue than WB itself, but we will investigate if it's possible to do something about it from the WB side.

MySQL Command Line Client (CLI) ...

On Linux it is just which you are using on bash:

$ mysql WHATEVER_OPTIONS

I did  SELECT * from information_schema.tables WHERE table_schema = 'gsusex_20100621'; from the mysql command prompt on linux.  It teturned 3 rows in 5.44 seconds.

Do you have a special sql-mode activated. Something like ansi_quotes or so?

The database server configuration and content are not under my control.  If there is a way to determine this information from the mysql client, I would be glad to try to find that.

The real issue, I believe, is the fact that db server has ~9000 schemas, and that the Workbench loads all the schema information at startup, even if I only want to work with 1 specific schema.

You can get information about sql-mode via CLI:

SHOW VARIABLES LIKE '%sql%';

Here's what that produced:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| sql_big_selects | ON    | 
| sql_mode        |       | 
| sql_notes       | ON    | 
| sql_warnings    | OFF   | 
+-----------------+-------+

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".