Announcement

Announcement Module
Collapse
No announcement yet.

pt-online-schema-change in multi master , master slave env and fk issues

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • pt-online-schema-change in multi master , master slave env and fk issues

    I tested the pt-online schema tool on a standalone server
    and ran into an fk issue
    after the Online Alter which was an ADD COLUMN
    and option
    -alter-foreign-keys-method auto
    I got the following messages at the end of the process:

    Max rows for the rebuild_constraints method: 25216
    Determining the method to update foreign keys...
    `cvs_name3`.`ens_linename`: too many rows: 17206397; must use drop_swap
    Drop-swapping tables...
    Dropped and swapped tables OK.
    Dropping triggers...
    Dropped triggers OK.
    Successfully altered `cvs_name3`.`ens_cldname`.

    I can't believe the maximum number of rows for the rebuild_constraints method is 25216?!
    I am testing with rebuild_constraints now, but I am concerned that even this option is not going to work because its a large table.
    I am wondering if anybody has experience with this? as well as running this tool across a production multi master / multi slaves environment?

  • #2
    `cvs_name3`.`ens_linename`: too many rows: 17206397; must use drop_swap

    The child table has too many rows, as far as I understand - ptosc thinks it will take considerable amount of time for the ALTER TABLE on this child table to rebuild the FK constraints as such it decided on a drop swap.

    Max rows for the rebuild_constraints method: 25216

    This value is generated on the speed of copying a chunk of rows from the original table to the new, by default the average rate of copy and chunk-time affects this. In essence, pt-osc thinks this is the optimal number of rows on the child table which when ALTERed (via rebuild_constraints) will take only time ~chunk-time, because there are 17M rows it will not use rebuild_constraints instead.
    Our documentation has a lot of answers about common questions on Percona software, have you checked there before posting that question here? http://www.percona.com/forums/core/i...lies/smile.png

    Join us at the annual Percona Live MySQL Users Conference - http://www.percona.com/live/mysql-conference-2014/

    Comment

    Working...
    X