Database registering slow
mysqlUser
Posts: 3
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.
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.
Comments
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
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.
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?
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.
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.
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:
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/
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
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