Database registering slow

mysqlUsermysqlUser Posts: 3
edited April 30, 2013 9:50AM in MySQL Compare
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.

Comments

  • 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
  • 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.
  • No problem, thanks a lot for the additional information.
  • 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?
  • 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, 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.
  • 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
  • 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.
  • 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 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.
  • 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/
  • 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
  • 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
Sign In or Register to comment.