Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Schema Synchronization Slow With Large Record Count

forums forums SQLyog SQLyog: Bugs / Feature Requests Schema Synchronization Slow With Large Record Count

This topic contains 0 replies, has 0 voices, and was last updated by  peterlaursen 6 years, 5 months ago.

  • Author
    Posts
  • #12774

    mford
    Member

    I have several DB's that are to have the same schema. I have 1 as the master. I then do a Schema Synchronization between the Master and DB A or DB B.

    Assuming DB A and DB B require the same exact updates, I have noticed that the Schema Sych may take 90 seconds for DB A, but 2 hours for DB B.

    DB B is significantly larger (more records) than DB A, but how does that explain the huge time difference? Why does comparing schema's get so impacted by the records themselves?

  • #33717

    peterlaursen
    Participant

    You write “I have noticed that the Schema Sych may take 90 seconds for DB A, but 2 hours for DB B.”. This makes me uncertain what step actually takes time. Is it the 'compare' step or the 'execute' step?

    If it is 'compare' step then some of the SHOW statements (such as SHOW FULL FIELDS FROM ..) we execute may be slower with large data. Please tell: what is/are the exact server version(s) involved? It should at least be very recent MySQL 5.1 or 5.5. The slowness with some SHOW statements is a known issue with servers up to 5.0 and early 5.1 (and InnoDB in particular)

    If it is the 'execute' step then the problem is that an ALTER TABLE statement for instance requires that MySQL need to rewrite the table (data or indexes) on disk we cannot do anything about it. This is a server/OS/file system problem and not a client issue. The same statement will take same time from any client. You will find some improvements in recent Percona Server (and possibly MariaDB and MySQL 5.6) – often referred to as “online ALTER TABLE”. It will not shorten the time for execution of an ALTER TABLE, but the table will not go offline while the statement is running.

You must be logged in to reply to this topic.