Bug #50701 WB exhausts system memory for large schema with 1000s of tables.
Submitted: 28 Jan 2010 20:34 Modified: 12 May 2010 12:15
Reporter: J Jorgenson Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:5.2.14 OS:Any
Assigned to: Sergei Tkachenko CPU Architecture:Any
Tags: memory limits schema

[28 Jan 2010 20:34] J Jorgenson
My desktop system, Win XP pro with 2G ram, is nearly crashed when opening the  SQL Editor, because the 'Physical Schema' is always queried for the active database.  

I've got a single database schema with 10,000s of tables (normalization is not the issue, the data is disperate).  We use an automated system for creating/dropping tables and the quantity of tables is extremely large.

Additionally because of the excessively large schema, queries on the database can hang, when max_open_tables value is reached because the info-schema requires to open each table to full fill the WB schema query: 
   'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.tables WHERE 
  table_type<>'VIEW' AND table_schema = 'myschema'

How to repeat:
Create a schema with 10,000s of tables and let the WB do its automatic query of the schema.

Suggested fix:
A) Give user ability to DISABLE query of schema through the options/preferences.

B) Give user ability to NOT auto-query of schema through the options/preferences.

C) Give user ability to STOP the schema query for large schemas.

D) Limit the number of tables returned from the schema query to 1 or 2K.
   How would they view tables beyond the limit, if they purposely want to work with an large schema?
[29 Jan 2010 22:16] Johannes Taxacher
we'd ad disabling of overview/auto-querying in profile (or in preferences)
[23 Apr 2010 16:32] Alfredo Kojima
There should be 2 new options:
- Disable Live Schema Overview
- Show Schema Names Only in Catalog List
[7 May 2010 15:14] Sergei Tkachenko
Added 2 options mentioned by Alfredo. Works on all platforms.
[10 May 2010 7:48] Johannes Taxacher
confirmed in repository
[12 May 2010 12:15] Tony Bedford
An entry has been added to the 5.2.21 changelog:

When the SQL Editor was launched, MySQL Workbench became overloaded, due to a large number of tables (10,000+) in the schema.

MySQL Workbench has been changed to include two new options in the Workbench Preferences dialog: Disable Live Schema Overview and Show Schema Names Only in Catalog List. This allows the application to be configured to handle schemata with large numbers of tables.