Database registering slow

Compares and synchronizes MySQL database schemas.

Moderators: Chris Auckland, eddie davis, Michael Christofides

Database registering slow

Postby mysqlUser » Thu Oct 04, 2012 3:53 pm

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.
mysqlUser
 
Posts: 2
Joined: Thu Oct 04, 2012 3:47 pm

Postby Michael Christofides » Tue Oct 09, 2012 10:19 am

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
Michael Christofides
 
Posts: 94
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Postby mysqlUser » Tue Oct 09, 2012 2:50 pm

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.
mysqlUser
 
Posts: 2
Joined: Thu Oct 04, 2012 3:47 pm

Postby Michael Christofides » Tue Oct 09, 2012 3:42 pm

No problem, thanks a lot for the additional information.
Michael Christofides
 
Posts: 94
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Postby bstewart » Tue Apr 30, 2013 10:50 am

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?
bstewart
 
Posts: 11
Joined: Tue Jul 31, 2012 9:40 am

Postby Michael Christofides » Tue Apr 30, 2013 11:38 am

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
Michael Christofides
 
Posts: 94
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Re:

Postby bstewart » Tue Apr 30, 2013 1:01 pm

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
 
Posts: 11
Joined: Tue Jul 31, 2012 9:40 am

Postby bstewart » Tue Apr 30, 2013 1:09 pm

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
bstewart
 
Posts: 11
Joined: Tue Jul 31, 2012 9:40 am

Postby bstewart » Tue Apr 30, 2013 1:10 pm

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
 
Posts: 11
Joined: Tue Jul 31, 2012 9:40 am

Postby bstewart » Tue Apr 30, 2013 1:22 pm

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.
bstewart
 
Posts: 11
Joined: Tue Jul 31, 2012 9:40 am

Re:

Postby Michael Christofides » Tue Apr 30, 2013 1:31 pm

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.
Michael Christofides
 
Posts: 94
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Postby bstewart » Tue Apr 30, 2013 1:34 pm

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:

Code: Select all
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/
bstewart
 
Posts: 11
Joined: Tue Jul 31, 2012 9:40 am

Postby Michael Christofides » Tue Apr 30, 2013 2:50 pm

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
Michael Christofides
 
Posts: 94
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software


Return to MySQL Compare

Who is online

Users browsing this forum: No registered users and 0 guests