Red Gate forums :: View topic - Database registering slow
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
MySQL Compare
MySQL Compare forum

Database registering slow

Search in MySQL Compare forum
Post new topic   Reply to topic
Jump to:  
Author Message
mysqlUser



Joined: 04 Oct 2012
Posts: 2

PostPosted: Thu Oct 04, 2012 3:53 pm    Post subject: Database registering slow Reply with quote

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.
Back to top
View user's profile Send private message
Michael Christofides



Joined: 20 Apr 2011
Posts: 87
Location: Red Gate Software

PostPosted: Tue Oct 09, 2012 10:19 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
mysqlUser



Joined: 04 Oct 2012
Posts: 2

PostPosted: Tue Oct 09, 2012 2:50 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Michael Christofides



Joined: 20 Apr 2011
Posts: 87
Location: Red Gate Software

PostPosted: Tue Oct 09, 2012 3:42 pm    Post subject: Reply with quote

No problem, thanks a lot for the additional information.
Back to top
View user's profile Send private message
bstewart



Joined: 31 Jul 2012
Posts: 11

PostPosted: Tue Apr 30, 2013 10:50 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Michael Christofides



Joined: 20 Apr 2011
Posts: 87
Location: Red Gate Software

PostPosted: Tue Apr 30, 2013 11:38 am    Post subject: Reply with quote

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/display/SCO130/Logging+and+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
Back to top
View user's profile Send private message
bstewart



Joined: 31 Jul 2012
Posts: 11

PostPosted: Tue Apr 30, 2013 1:01 pm    Post subject: Re: Reply with quote

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/display/SCO130/Logging+and+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.
Back to top
View user's profile Send private message
bstewart



Joined: 31 Jul 2012
Posts: 11

PostPosted: Tue Apr 30, 2013 1:09 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
bstewart



Joined: 31 Jul 2012
Posts: 11

PostPosted: Tue Apr 30, 2013 1:10 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
bstewart



Joined: 31 Jul 2012
Posts: 11

PostPosted: Tue Apr 30, 2013 1:22 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Michael Christofides



Joined: 20 Apr 2011
Posts: 87
Location: Red Gate Software

PostPosted: Tue Apr 30, 2013 1:31 pm    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message
bstewart



Joined: 31 Jul 2012
Posts: 11

PostPosted: Tue Apr 30, 2013 1:34 pm    Post subject: Reply with quote

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:
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/
Back to top
View user's profile Send private message
Michael Christofides



Joined: 20 Apr 2011
Posts: 87
Location: Red Gate Software

PostPosted: Tue Apr 30, 2013 2:50 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group