Database registering slow

mysqlUser · October 4, 2012 10:53AM

I am evaluating MySQL Compare.

When I tried it on a MySQL 5.1.54 server, database registering was really quick.

However, when trying on a MySQL 5.0.51 server, database registering takes minutes, but eventually works. This makes the product too slow to work with, since the problem affects every Refresh or Code deployment operation.

Is this a known issue?

Edit: I take it the silence from the RedGate support team means that this is an unknown issue.

Michael Christofides · October 9, 2012 5:19AM

Hi there, sorry for the radio silence on this, I meant to get back to you sooner.

Would it be possible to send through your schema by email? As you suspected, this isn't a known issue and we'll look into reproducing it.

You can email us at mysql@red-gate.com

Many thanks,
Michael

mysqlUser · October 9, 2012 9:50AM

Unfortunately I cannot send you the schema, due to business confidentiality reasons. However, I don't think the schema is relevant: I have several disparate schemas on the problematic MySQL server --- the delay persists no matter which ones I select.

Additional info which may help you reproduce the problem

The database drop-down list in the New Project window is populated quickly. The connection method to the MySQL server is TCP/IP. The user credentials are also used in MySQL Workbench 5.2.43 for developing code, without problems. MySQL version is 5.0.51a-24+lenny5.

Michael Christofides · October 9, 2012 10:42AM

No problem, thanks a lot for the additional information.

bstewart · April 30, 2013 5:50AM

I'm seeing this same problem. We have an internal MySQL server we use for development. The details of this server are as follows:

OS - Windows Server 2008 R2 (64-bit)
MySQL version - 5.1.46-community

That sits on the same network as my development machine. It has 57 databases on it but there are only about 10 of them being used in active development and even at that, there is a very light load on the server. For arguments sake, we'll call this Server A. If I have to use MySQL compare to compare the schema of a database on Server A to any other database, you're talking at least 80 seconds for MySQL compare to register that database. If you're comparing two databases on Server A then that minimum time would double. The complexity of the schema appears to have little effect on the length of time taken to register the database. Even if I create a test database which contains just a single table with two INT columns, it still takes > 80 seconds to register the db.

The strange thing is that with any other remote servers I've tried, the registering of a database is usually complete within 5 seconds. Is there any logging built into MySQL compare that I can switch on to try and work out why this is taking so long?

Michael Christofides · April 30, 2013 6:38AM

Hi, thank you for the details.

Very strange indeed. In terms of logging, there are instructions described for an equivalent product here:
https://documentation.red-gate.com/disp ... +log+files

I assume you are using the same mode of transport (TCP/IP, Named Pie, or SSH) in each case, so that couldn't be the issue?

Best regards,
Michael

bstewart · April 30, 2013 8:01AM

Michael Christofides wrote:

Hi, thank you for the details.

Very strange indeed. In terms of logging, there are instructions described for an equivalent product here:
https://documentation.red-gate.com/disp ... +log+files

I assume you are using the same mode of transport (TCP/IP, Named Pie, or SSH) in each case, so that couldn't be the issue?

Best regards,
Michael

Hi Michael,

Yes, it's TCP/IP connections. I'll have a look at the logging link you sent.

bstewart · April 30, 2013 8:09AM

bstewart · April 30, 2013 8:10AM

I should also note that my network connection to the server is generally good. Pings are always < 1ms and I can transfer a 900MB ISO file in < 20 seconds.

bstewart · April 30, 2013 8:22AM

Another quick update here. I've noticed that queries involving the information_schema database on the server in question are very slow which would suggest the problem doesn't lie with MySQL compare.

Michael Christofides · April 30, 2013 8:31AM

bstewart wrote:

Unfortunately, the log doesn't reveal much other than how long each part of the comparison is taking:

13:03:01.683|Debug |PopulationLogger |5 |ProgressTask:Populating DB test1
13:03:01.687|Debug |PopulationLogger |5 |ProgressTask:Populating Dependencies
13:03:08.793|Debug |PopulationLogger |5 |ProgressTask:Populating Tables
13:03:11.373|Debug |PopulationLogger |5 |ProgressTask:Populating Columns
13:03:11.549|Debug |PopulationLogger |5 |ProgressTask:Populating Indexes
13:03:14.031|Debug |PopulationLogger |5 |ProgressTask:Populating Constraints
13:03:14.031|Debug |PopulationLogger |5 |ProgressTask:Populating Index Constraints
13:03:41.769|Debug |PopulationLogger |5 |ProgressTask:Populating FK Constraints
13:04:31.104|Debug |PopulationLogger |5 |ProgressTask:Populating Views
13:04:31.155|Debug |PopulationLogger |5 |ProgressTask:Populating Routines
13:04:31.180|Debug |PopulationLogger |5 |ProgressTask:Populating Triggers
13:04:32.511|Debug |PopulationLogger |5 |ProgressTask:Populating Events
13:04:32.536|Debug |PopulationLogger |5 |ProgressTask:Populating DB test2
13:04:32.536|Debug |PopulationLogger |5 |ProgressTask:Populating Dependencies
13:04:37.949|Debug |PopulationLogger |5 |ProgressTask:Populating Tables
13:04:40.106|Debug |PopulationLogger |5 |ProgressTask:Populating Columns
13:04:40.263|Debug |PopulationLogger |5 |ProgressTask:Populating Indexes
13:04:43.214|Debug |PopulationLogger |5 |ProgressTask:Populating Constraints
13:04:43.214|Debug |PopulationLogger |5 |ProgressTask:Populating Index Constraints
13:05:08.606|Debug |PopulationLogger |5 |ProgressTask:Populating FK Constraints
13:05:58.547|Debug |PopulationLogger |5 |ProgressTask:Populating Views
13:05:58.582|Debug |PopulationLogger |5 |ProgressTask:Populating Routines
13:05:58.586|Debug |PopulationLogger |5 |ProgressTask:Populating Triggers
13:05:59.877|Debug |PopulationLogger |5 |ProgressTask:Populating Events
13:06:00.948|Debug |Event Aggregator |1 |:Sending message #Limb.#paD
13:06:01.285|Debug |Event Aggregator |1 |:Sending message #Limb.#pbD
13:06:02.674|Debug |Event Aggregator |1 |:Sending message #Limb.#obD

Thank you, it looks like the issue is somewhere around populating index and FK constraints, is there anything obviously different about this server in terms of constarints?

In the meantime I'll ask the team if there's any obvious reason this might be the case.

bstewart · April 30, 2013 8:34AM

Fixed:

And another update. I had a look at the server and innodb_stats_on_metadata was set to 1. I set this to 0 with the following:

SET GLOBAL innodb_stats_on_metadata=0;

Or you could set it in your my.conf. Now when I run a comparison, it takes about 6 seconds to register a database on that server which is much faster than I was getting before. Credit to this post for details on the effect that setting has on information_schema query peformance:

http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/

Michael Christofides · April 30, 2013 9:50AM

Fantastic, and thank you for taking the time to post the solution, I'm sure others will find this useful in future.

You may also wish to turn off logging now (if you haven't done so already) as this can also slow you down a little.

All the best,
Michael

Syn Gates · March 23, 2017 4:44AM

Hi all,
I'm not a developer but I own a team who develop some solution on Magento Cloud Edition, it is a Platform-as-a-service package. I wonder how to improve speed of my products? Is that I have to concern about MySQL or something related to database? Can someone show me which criteria that can enhance speed for web or apps.
Thanks

Database registering slow

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk