Not the answer you need?
Register and ask your own question!

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

KaSaRiKaSaRi EntrantCurrent User Role Beginner
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?

Comments

  • revinrevin Contributor Current User Role Beginner
    `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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.