Long Wait On Refresh Of Database With Many Tables

[bschonec]

SQLyog Enterprise v5.02

MySQL server 5.0.18-standard

Two databases on server, lightly loaded.

Database has 1597 tables as of Feb 20, 2006.

I get to the server (on another machince) just fine but when I expand the + next to the database, it takes 90 seconds to display all of the tables. Using Navicat (a competing MySQL client GUI) it takes only 3-4 seconds. Clearly there's some SNAFU on the SQLyog side.

Anything I can do to debug this? I just registered 5 licenses and am hoping that I didn't evaluate the product fully. Waiting 90 seconds is making my DB programmers angry.

Brian

[Ritesh]

This is VERY VERY strange!

SQLyog is supposed to be several times faster that any of the competing products.

I need some information about your configuration:

1) Are you using HTTP tunnelling while connecting from SQLyog?

2) Can you cut-n-paste the SQL commands from the History tab after expanding the database? If you dont want them to be displayed at the public forums, please send a mail to [email protected] specifying your order no.

Last but not the least, dont worry about your purchase. After helping us pin-point the exact problem, if you dont find SQLyog to be “several” times faster than any of the competing products, you can claim a full refund. No questions asked!

[Ritesh]

I just checked with 1500 tables in our local network and here are the statistics:

1) Direct connection: It takes less than 3 seconds.

2) HTTP Tunneling: It takes less than 10 seconds.

There will always be a slight difference in speed between Direct connection and HTTP Tunneling.

[bschonec]

I am using a Cicco VPN client version 4.6.00.0049 to connect to a remote server.

When I go to the command prompt and type:

mysql -u -p -h my_database

> show tables;

(tables show)

1794 rows in set (0.54 sec)

mysql>

Pretty fast!

If I start up SQLyog Enterprise, connect to the database server and then click on the (+) sign to expand the database in question, it takes about two minutes. I am starting to think this may be a MySQL issue because I typed all of the stuff in the SQLyog history and MySQL took a long time to return the resultant tables.

Evidently SQLyog is not doing simply a “show tables”. It's doing

Navicat MySQL must be doing a “show tables” which is why it returns the table names so fast.

Here's the history log from SQLyog.

/*[8:09:22 AM][ 94 ms]*/ show variables like '%character%'

/*[8:09:22 AM][ 62 ms]*/ Set character_set_connection=latin1

/*[8:09:22 AM][ 47 ms]*/ Set character_set_results=latin1

/*[8:09:22 AM][ 63 ms]*/ Set character_set_client=latin1

/*[8:09:22 AM][ 46 ms]*/ set sql_mode=''

/*[8:09:22 AM][ 63 ms]*/ show databases

/*[8:09:26 AM][ 32 ms]*/ use 'my_database'

/*[8:13:01 AM][215000 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'my_database' and `TABLE_TYPE` = 'BASE TABLE'

[peterlaursen]

Those are created at connection:

/*[8:09:22 AM][ 94 ms]*/ show variables like '%character%'

/*[8:09:22 AM][ 62 ms]*/ Set character_set_connection=latin1

/*[8:09:22 AM][ 47 ms]*/ Set character_set_results=latin1

/*[8:09:22 AM][ 63 ms]*/ Set character_set_client=latin1

/*[8:09:22 AM][ 46 ms]*/ set sql_mode=''

This is executed when Object Browser is 'filled'

/*[8:09:22 AM][ 63 ms]*/ show databases

This is executed when you select a DB (clicking on it or choosing from drop-down

/*[8:09:26 AM][ 32 ms]*/ use 'my_database'

This is executed when you expand by clicking the plus (+) to the left of the DB.

/*[8:13:01 AM][215000 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'my_database' and `TABLE_TYPE` = 'BASE TABLE'

[peterlaursen]

So it is the SELECT from INFORMATION_SCHEMA who is the 'sinner' here.

It it posible for you to export the STRUCTURE only (no data needed) and let Ritesh and maybe even me try?

Did you run all update scripts when MySQL was updated ?

Tried the Mysqlcheck program ?

[Ritesh]

What happens when you execute:

Code:

select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'my_database' and `TABLE_TYPE` = 'BASE TABLE'

from the MySQL command line?

[Ritesh]

show tables will display all the tables/views but our query i.e.

Code:

select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'my_database' and `TABLE_TYPE` = 'BASE TABLE'

will only show the tables.

[Ritesh]

I had a talk with the MySQL developers on this issue and this is what they have to say.

Quote:

The above is a known issue with INFORMATION_SCHEMA.  It's especially harmful if there are many InnoDB tables, and if the server is using innodb-file-per-table option.  The reason is that the InnoDB statistics are being updated each time.

In fact, this slowdown is related to http://bugs.mysql.com/bug.php?id=13762 (SHOW CREATE TABLE slowdown with InnoD:cool:.

The bottom line is some optimization of INFORMATION_SCHEMA may be done in MySQL 5.1 and 5.2, but not planned in 5.0 as far as I know.

This is NOT AN SQLyog issue but rather MySQL's.

Navicat executes SHOW TABLES to get the table LIST but this method has been officially deprecated by MySQL AB for v5.x.

BTW, SQLyog will still execute SHOW TABLES for MySQL versions < 5.0.

[Ritesh]

peterlaursen wrote on Feb 24 2006, 02:19 PM:

Those are created at connection:

/*[8:09:22 AM][ 94 ms]*/ show variables like '%character%'

/*[8:09:22 AM][ 62 ms]*/ Set character_set_connection=latin1

/*[8:09:22 AM][ 47 ms]*/ Set character_set_results=latin1

/*[8:09:22 AM][ 63 ms]*/ Set character_set_client=latin1

/*[8:09:22 AM][ 46 ms]*/ set sql_mode=''

This is executed when Object Browser is 'filled'

It should be *before Object Browser* is filled.

These are some connection specific queries. Not related to filling up of the Object Browser.

[Ritesh]

We are doing some profiling for SHOW TABLES and INFORMATION_SCHEMA

If SHOW TABLES is really faster than SELECTing from INFORMATION_SCHEMA, we might give the user an option to continue using SHOW TABLES.

This is a funny situation created by MySQL AB. The deprecated syntax works faster compared to the bleeding edge INFORMATION_SCHEMA!

[peterlaursen]

@ritesh

What part of your anatomy are you using for reading? 😛

Was exactly what I wrote I guess!

Code:

Those are created at connection:

/*[8:09:22 AM][ 94 ms]*/ show variables like '%character%'
/*[8:09:22 AM][ 62 ms]*/ Set character_set_connection=latin1
/*[8:09:22 AM][ 47 ms]*/ Set character_set_results=latin1
/*[8:09:22 AM][ 63 ms]*/ Set character_set_client=latin1
/*[8:09:22 AM][ 46 ms]*/ set sql_mode=''

[peterlaursen]

Quote:

If SHOW TABLES is really faster than SELECTing from INFORMATION_SCHEMA, we might give the user an option to continue using SHOW TABLES.

That would also indirectly give users an option to display VIEWs along with TABLES. Some might prefer this. Maybe even I in some situations. If this option could be connection-specific it would be just fine!

[Author]

Posts